1 IDUGDB2-L.ORG /home/listserv/home/db2-l July 2010, week 3 2 30 152_AUTO: Norbert Jenninger/Germany/IBM is out of the office with no access to his mailbox, will be back in the office Friday July 16 (returning 07/16/2010)17_Norbert Jenninger14_JEN@DE.IBM.COM31_Thu, 15 Jul 2010 10:03:04 +0200384_US-ASCII I am out of the office until 07/16/2010.

I am out of office with no access to my mailbox and will be back in the
office Friday July 16,

Note: This is an automated response to your message "DB2-L Digest - 14 Jul
2010 to 15 Jul 2010 (#2010-189)" sent on 15/7/10 7:00:00.

This is the only notification you will receive while this person is away. [...]68_OFD574CDCE.E156417D-ONC1257761.002C39F4-C1257761.002C39F5@de.ibm.com 33 27 52_Finding out Dynamic SQLs running on Particular Table21_Devyani Sahasrabuddhe30_devyanisahasrabuddhe@GMAIL.COM31_Thu, 15 Jul 2010 04:15:43 -0400674_UTF-8 Hello List,

We can find out all the static SQLs which run for a particular table by running either SUbquery on SYSPACKDEP and SYSPKSTMT. However, is there any way to find out dynamic SQLs which run on a particular table.

Thanks,
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! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________ [...]62_7994655817755086.WA.devyanisahasrabuddhegmail.com@www.idug.org 61 212 56_Re: Finding out Dynamic SQLs running on Particular Table11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 15 Jul 2010 12:29:55 +0200356_ISO-8859-1 snap the DSC nearly every day for, possibly, ever and, with luck, you
might get all the dynamic SQL that runs in your shop.....however your
mileage may vary and the results will not be 100% accurate as data can
come into the cache and leave the cache very fast

or

run traces all the time and pull out the dynamic SQL [...]64_OF05D9B4BA.3BF88BC4-ONC1257761.00396E8F-C1257761.0039ABB7@seg.de 274 27 29_ADMIN out of office reminders0_19_galambos@ROGERS.COM31_Thu, 15 Jul 2010 12:29:30 +0000354_- This is a reminder that if you go on vacation and set an 'out of office' on your email, the VERY next thing you should do, is set 'NOMAIL' on your subscription of your listserv.

I am sure you are tired of seeing these 'out of office' notices all the time, they take up bandwidth, and make clutter so the 'real' message may get drowned out. [...]105_1182669750-1279196971-cardhu_decombobulator_blackberry.rim.net-1597695899-@bda274.bisx.prod.on.blackberry 302 63 33_Re: ADMIN out of office reminders13_Crissy, Frank28_Frank_N_Crissy@HOMEDEPOT.COM31_Thu, 15 Jul 2010 09:38:54 -0500458_us-ascii Then how do you do that?

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of galambos@ROGERS.COM
Sent: Thursday, July 15, 2010 7:30 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] ADMIN out of office reminders

This is a reminder that if you go on vacation and set an 'out of office' on your email, the VERY next thing you should do, is set 'NOMAIL' on your subscription of your listserv. [...]73_8F09E4A93B2EAE4DBB31891A893D6BA909DB1CB231@025-NAMSG-04.025d.mgd.msft.net 366 121 33_Re: ADMIN out of office reminders16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM31_Thu, 15 Jul 2010 10:47:28 -0400309_UTF-8 There are two ways

1) Sin on to the web interface (go to idug.org and click on the listserv tab) from there you can go to your subscription where you can set your status to NOMAIL (and you come back reset it)

2) Or send an email to listserv@idug.org and in the body enter the command [...]70_551C24F0D5EA4E47BA9B76E14D016DD5067D0BC5@nasa-dtw-ex005.nasa.cpwr.corp 488 116 33_Re: ADMIN out of office reminders13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Thu, 15 Jul 2010 10:48:15 -0400617_iso-8859-1 You send SET NOMAIL DB2-L to LISTSERV@IDUGDB2-L.ORG

And send SET DB2-L MAIL to it when you get back

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

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Crissy, Frank
Sent: 15 July 2010 15:39
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] ADMIN out of office reminders [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8F7A56A@MAILR004.mail.lan 605 57 33_Re: ADMIN out of office reminders0_19_galambos@ROGERS.COM31_Thu, 15 Jul 2010 14:48:45 +0000305_- There are two ways

1) Sin on to the web interface (go to idug.org and click on the listserv tab) from there you can go to your subscription where you can set your status to NOMAIL (and you come back reset it)

2) Or send an email to listserv@idug.org and in the body enter the command [...]105_1551263795-1279205326-cardhu_decombobulator_blackberry.rim.net-2117428638-@bda274.bisx.prod.on.blackberry 663 122 33_Re: ADMIN out of office reminders15_Schade, Jeffrey15_JSchade@ISO.COM31_Thu, 15 Jul 2010 10:50:37 -0400662_iso-8859-1 Use the link at the bottom of the DB2-L e-mails and select Subscribe or Unsubscribe option to change your account settings: www.idug.org/cgi-bin/wa?A0=DB2-L

Jeff
_________________________________________
Jeffrey Schade
Systems Consultant, Technology Engineering

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

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Crissy, Frank
Sent: Thursday, July 15, 2010 10:39 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] ADMIN out of office reminders [...]59_DB138660B6B01D458B16F9205C0C2634184761C2@ISOEMAILP3.iso.com 786 115 33_Re: ADMIN out of office reminders20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM31_Thu, 15 Jul 2010 17:53:57 +0300715_US-ASCII Please send

SET DB2-L NOMAIL Command to

Regards, Cuneyt

> -----Original Message-----
> From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of
> Crissy, Frank
> Sent: Thursday, July 15, 2010 5:39 PM
> To: DB2-L@IDUGDB2-L.ORG
> Subject: Re: [DB2-L] ADMIN out of office reminders
>
>
> Then how do you do that?
>
>
> -----Original Message-----
> From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of
> galambos@ROGERS.COM
> Sent: Thursday, July 15, 2010 7:30 AM
> To: DB2-L@IDUGDB2-L.ORG
> Subject: [DB2-L] ADMIN out of office reminders
>
> This is a reminder that if you go on vacation and [...]42_032401cb242d$8e5f4e50$920aa8c0@cuneytgoksu 902 86 23_DB2 v9 and BUILD2 phase15_Dave Petronella24_dpetronella@PERSHING.COM31_Thu, 15 Jul 2010 11:37:35 -0400516_US-ASCII Hi,

We are just beginning our migration to DB2 v9. We understand that for
REORGs v9 eliminates the BUILD2 phase for NPIs. We do a fair amount of
partition-level REORGs so our understanding is that the REORG of each
partition will rebuild the entire index. With the assumed increase in CPU
and Elapsed times for this, as well as contention when multiple parts are
REORGed, we are interested in knowing how other shops are dealing with
this change? Is our understanding correct? [...]71_OF5D2D90DB.C33851F6-ON85257761.00554847-85257761.0055D7AF@bnymellon.com 989 35 19_Re: invalid package13_Rahul Chandra20_adminrahul@GMAIL.COM31_Thu, 15 Jul 2010 11:48:14 -0400499_- Thanks Kirk.. for your valuable input...But IBM should also do something to address this issue..I mean adding few columns in catalog can solve whole things .

As a good practice, we can create a daily report of Invalid Packages..And track them further.

Problem with many shops is that they are running for decade with thousand invalid version of packages with OPERATIVE = 'Y'. So, we cannot guess since how long that version of package is sitting idle and take necessary action. [...]52_0318970977249908.WA.adminrahulgmail.com@www.idug.org 1025 407 27_Re: DB2 v9 and BUILD2 phase14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 15 Jul 2010 12:02:20 -0400417_us-ascii Dave,

Two solutions to recommend, both of them dealing with the case of sequentially REORGing multiple partitions in a tablespace: (1) Just REORG the entire tablespace, thus combining the multiple index REORGs; (2) if you have a bunch of adjacent or near-adjacent partitions in scope, combine them (PART 1:4 for the first four parts of the tablespace), again combining the multiple index REORGs. [...]64_1BA00CBB414DA34AA25ED82F4647CDE20349B975F0@MAIL02.fisalan.nycnet 1433 140 56_Re: Finding out Dynamic SQLs running on Particular Table12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 15 Jul 2010 09:03:21 -0700625_iso-8859-1 Or get a product that will do that for you. There are several commercial products that do capture all the SQL, including dynamic. ________________________________ From: Roy Boxwell To: DB2-L@IDUGDB2-L.ORG Sent: Thu, July 15, 2010 6:29:55 AM Subject: Re: [DB2-L] Finding out Dynamic SQLs running on Particular Table snap the DSC nearly every day for, possibly, ever and, with luck, you might get all the dynamic SQL that runs in your shop.....however your mileage may vary and the results will not be 100% accurate as data can come into the cache and leave the cache very fast or run traces [...]44_259867.87356.qm@web112112.mail.gq1.yahoo.com 1574 34 33_Re: ADMIN out of office reminders11_Ted MacNEIL18_eamacneil@YAHOO.CA31_Thu, 15 Jul 2010 16:41:59 +0000412_- >Then how do you do that?

With LOTUS NOTES it's quite simple.
You can either set a switch so that nothing responds to external users, or you can customise your o-o-o response so that it does not send to listed users.
This can be customised by each user.

With Exchange, the switch is all users or no users.
The rest is not as flexible, and I've never got the rules to work properly. [...]104_107924905-1279212082-cardhu_decombobulator_blackberry.rim.net-1969138100-@bda026.bisx.prod.on.blackberry 1609 193 33_Re: ADMIN out of office reminders16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM31_Thu, 15 Jul 2010 13:27:25 -0400426_UTF-8 fat finger (IE blackberry's fault)

And wondering who would catch it (and others)

The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. [...]70_551C24F0D5EA4E47BA9B76E14D016DD5067D0BC7@nasa-dtw-ex005.nasa.cpwr.corp 1803 50 33_Re: ADMIN out of office reminders11_Ted MacNEIL18_eamacneil@YAHOO.CA31_Thu, 15 Jul 2010 17:42:44 +0000511_- I know it's bad form to reply to one's own post.

But, after reading other responses, I realised I answered the wrong question.

Sorry.
-
I'm a SuperHero with neither powers, nor motivation!
Kimota!

-----Original Message-----
From: Ted MacNEIL
Sender: IDUG DB2-L
Date: Thu, 15 Jul 2010 16:41:59
To:
Reply-To: IDUG DB2-L
Subject: Re: [DB2-L] ADMIN out of office reminders [...]104_123983137-1279215726-cardhu_decombobulator_blackberry.rim.net-1254259305-@bda026.bisx.prod.on.blackberry 1854 71 33_Re: ADMIN out of office reminders14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 15 Jul 2010 13:46:21 -0400475_us-ascii Ted:

It was, however, a useful extension of the discussion.

--Phil Sevetson

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ted MacNEIL
Sent: Thursday, July 15, 2010 1:43 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] ADMIN out of office reminders

I know it's bad form to reply to one's own post.

But, after reading other responses, I realised I answered the wrong question. [...]64_1BA00CBB414DA34AA25ED82F4647CDE20349B975F3@MAIL02.fisalan.nycnet 1926 105 33_Re: ADMIN out of office reminders10_Mark Labby21_mlabby@AESSUCCESS.ORG31_Thu, 15 Jul 2010 14:00:23 -0400555_us-ascii Robert,

I thought bringing Sin on the web was your way to increase traffic and
drive up the usage of www.IDUG.org and DB2-L.

I was about to smack myself on the head and say, "Now why didn't I think of
that? It is a classic marketing ploy!"

Just a typo, huh? I guess I can cancel the new banners with hot young
babes and boys, sitting at their terminals, staring at each other with
twinkles in their eyes and our new slogan. "For a sinfully delightful
experience, visit www.IDUG.org, where users group..." [...]67_OF4D89FA71.53E91B3C-ON85257761.0061ACC4-85257761.0062E55E@pheaa.org 2032 52 33_Re: ADMIN out of office reminders0_19_galambos@ROGERS.COM31_Thu, 15 Jul 2010 18:05:03 +0000493_- I say go for. It could not hurt.

-----------Original Message-----------
From: Mark Labby
To: IDUG DB2-L
ReplyTo: IDUG DB2-L
Date: Jul 15 14:01:21
Subject: Re: [DB2-L] ADMIN out of office reminders

Robert,

I thought bringing Sin on the web was your way to increase traffic and
drive up the usage of www.IDUG.org and DB2-L.

I was about to smack myself on the head and say, "Now why didn't I think of
that? It is a classic marketing ploy!" [...]104_1387497664-1279217107-cardhu_decombobulator_blackberry.rim.net-637798437-@bda274.bisx.prod.on.blackberry 2085 553 27_Re: DB2 v9 and BUILD2 phase13_David Simpson22_dsimpson@THEMISINC.COM31_Thu, 15 Jul 2010 15:12:26 -0400462_us-ascii If you need to reorg non-contiguous partitions there is a workaround. I
understand the permanent solution is in DB2 10. This subject has come
up before on the list... see the link below for some discussion:

http://tinyurl.com/3676pf4

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson,
Phil
Sent: Thursday, July 15, 2010 11:02 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] DB2 v9 and BUILD2 phase [...]67_7EC87535E7581C4C890F5BF1CF9A9374029B7662@THEMISSRV1.themisinc.local 2639 455 64_Re: What is DB2DIST doing?!?! - A Performance Quandary - z\OS V832_Humphris,Richard P. (Contractor)25_RichardP.Humphris@CNA.COM31_Thu, 15 Jul 2010 23:51:13 -0500617_us-ascii Hi Jay,

Shouldn't matter whether it was db2dist or tso but:

If you look in SYS1.PARMLIB(SMFPRFM00) and you see DDCONS(YES) and DETAIL in effect for DBM1... that is most likely your problem. See DDCONS informational APAR II07124 (very old but still critical).
1) If DDCONS is not specified in SMFPRFM00, the default is still DDCONS(YES).
2) Symptoms are: db2dbm1 running at 100% of a cpu engine during same time period that db2 is reporting extremely long i/o response times.
3) But the RMF cpu reporting interval can mask the DBM1 cpu spike. For example, if RMF has a 100 seconds [...]60_10B866DA3B4BC44FA723C2C315C8FE6F04CC95FAE4@E2K7CLSTA.cna.com 3095 53 54_DB2 v7.1 in VM/CMS-Simplified Chinese char usage fails4_Amar18_amar_moh@YAHOO.COM31_Fri, 16 Jul 2010 01:08:21 -0400451_UTF-8 Hi,

We have a DB2 v7.1 database in z/VM-CP-CMS environment. The requirement is to now allow Simplified Chinese characters to be input into some tables and columns.

So far, I have updated the SYSOPTIONS table to make DBCS as YES and restarted the database server. Database default charcode is still English.
Then created a table with char and varchar columns with CCSID as 935(for simplified Chinese as per SYSCCSID table). [...]49_8398973685325631.WA.amarmohyahoo.com@www.idug.org 3149 546 35_SV: [DB2-L] DB2 v9 and BUILD2 phase13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE31_Fri, 16 Jul 2010 08:47:47 +0200425_iso-8859-1 For those who are interested in REORGing multiple partitions in one REORG statement in DB2 9 I can recommend that you follow APAR PK87762 , this APAR will give us the possibility to specify an arbitrary amount of partitions to be REORGed in one statement. This feature will also be supported in DB2 Automation Tool in APAR PM03078 with PTF UK58293.
With this REORG enhancement you can specify for example: [...]70_B0000573F0F67C438DC58043C06F4CAB49D3A9991A@FSPAS01EV011.fspa.myntet.se 3696 30 23_REORG issue during SORT9_SrinivasG21_SRINIVASG@INFOSYS.COM31_Fri, 16 Jul 2010 14:28:14 +0530494_utf-8 Hi,

I am doing a REORG of a big table space. The Job abends during the unload phase.

IEA995I SYMPTOM DUMP OUTPUT 118
SYSTEM COMPLETION CODE=04E REASON CODE=00E40005
TIME=14.09.59 SEQ=14653 CPU=0000 ASID=0023

DSNU044I 197 14:09:59.12 DSNUGSRT - ERROR FROM SORT COMPONENT RC=16, UTILITY
DSNU016I 197 14:09:59.13 DSNUGBAC - UTILITY BATCH MEMORY EXECUTION ABENDED, REASON=X'00E40005'

Even if I allocate explicit SORTWORK datasets , I get this error. [...]69_E5D4741C0D67254EA3601CFEC7810DFA0FBDEED3DF@BLRKECMBX05.ad.infosys.com 3727 257 27_Re: REORG issue during SORT16_Christian Michel18_cmichel@DE.IBM.COM31_Fri, 16 Jul 2010 11:09:07 +0200585_ISO-8859-1 For these 00E40005 errors you always need to look at the output of the
failing DFSORT task. I assume that was an ICE046A SORT CAPACITY EXCEEDED
ERROR, so look for this message and then take a look at the full DFSORT
output. Usually the estimate in FILSZ is way below what is actually
sorted. The most likely reason for that is that you haven't been running
RUNSTATS in quite a while. This can also have an effect on the AVGROWLEN
parameter passed to DFSORT for the REORG data sort, which is passing the
records with variable length. Maybe you have [...]68_OF624D4CCF.6F07AF99-ONC1257762.00319B16-C1257762.0032408F@de.ibm.com 3985 623 27_Re: REORG issue during SORT9_SrinivasG21_SRINIVASG@INFOSYS.COM31_Fri, 16 Jul 2010 14:46:23 +0530334_iso-8859-1 Thanks for replying , Mike.

Sorry. I forgot to update another error. My mistake ..

ICE201I G RECORD TYPE IS V - DATA STARTS IN POSITION 5
ICE175I A ATTEMPT TO REALLOCATE VIO WORK DATA SETS TO REAL RESULTED IN VIO
ICE083A D RESOURCES WERE UNAVAILABLE FOR DYNAMIC ALLOCATION OF WORK DATA SETS (4714) [...]69_E5D4741C0D67254EA3601CFEC7810DFA0FBDEED3F6@BLRKECMBX05.ad.infosys.com 4609 150 27_Re: REORG issue during SORT0_37_ibasivireddy@DSTWORLDWIDESERVICES.COM31_Fri, 16 Jul 2010 15:11:08 +0530620_ISO-8859-1 You can try with

//DSNUPROC. DFSPARM DD *
DSPSIZE=MAX, HIPERMAX=OPTIMAL,SIZE=MAX
/*

Thanks,
Basivi R. Inaganti
DST Worldwide Services
Office +1 877-289-5154 Extn: 25543
+91 40-66125555 Extn: 25543
Cell +91 900-042-7770
ibasivireddy@dstworldwideservices.com

From: SrinivasG

To: DB2-L@IDUGDB2-L.ORG

Date: 07/16/2010 02:47 PM

Subject: Re: [DB2-L] REORG issue during SORT

Sent by: IDUG DB2-L

Thanks for replying , Mike.

Sorry. I forgot to update another error. My mistake .. [...]72_OF3D85DDB9.F90252A2-ON65257762.00352BD4-65257762.003536F5@dstsystems.com 4760 225 27_Re: REORG issue during SORT11_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 16 Jul 2010 12:15:11 +0200741_ISO-8859-1 check the DFSORT survival guide for useful tips about errors/problems just
like this!!

I will send it offline to you!

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: Gerhard Schubert

SrinivasG
Gesendet von: IDUG DB2-L
16.07.2010 10:58
Bitte antworten an
IDUG DB2-L

An
DB2-L@IDUGDB2-L.ORG
Kopie

Thema
[DB2-L] REORG issue during SORT [...]64_OFF9107E87.1DB3A922-ONC1257762.003843B7-C1257762.00385295@seg.de 4986 243 27_Re: REORG issue during SORT9_Mike Bell21_mbell11a1@VERIZON.NET31_Fri, 16 Jul 2010 07:55:37 -0500511_iso-8859-1 The basic problem is that sort does not accept VIO datasets for sortwork -
ever -

Your problem is that somehow the SMS rules are forcing the sortwork datasets
to vio.

You can change the VIO allocation by making the sortwork files bigger - at
some point they will stop going to VIO or you can override the sort parms
with DFSPARMS as already mentioned. This is really a SMS/storage management
problem with small sort works and you should pass it over to their group. [...]43_007AE6CBBBC04F0E9385BBF79D59053C@mikelaptop 5230 450 64_Re: What is DB2DIST doing?!?! - A Performance Quandary - z\OS V812_Reavill, Jay27_Jay.C.Reavill@FISGLOBAL.COM31_Fri, 16 Jul 2010 08:35:47 -0500525_us-ascii Hi Richard,

This is very interesting information. However, I don't see a SMFPRM00
member in our SYS1.PARMLIB. And I'm guessing the second F in the member
name was a typo since it makes the name too long. These are the SMF
members that we have...

SMFPRMDH
SMFPRM1R
SMFPRM1T
SMFPRM1Z
SMFPRM10
SMFPRM19
SMFPRM2R
SMFPRM2Z
SMFPRM20
SMFPRM29
SMFPRM3Z
SMFPRM30

Of those only members 10, 20, and 30 have DDCONS specified in them and
they are set to NO. [...]62_C90B7164E1511D499B3C479D8B7CEC92021C25D9@CMBFISLTC07.FNFIS.COM 5681 487 64_Re: What is DB2DIST doing?!?! - A Performance Quandary - z\OS V812_McLaren Phil32_Phil.McLaren@AXAWINTERTHUR.CO.UK31_Fri, 16 Jul 2010 15:06:57 +0100659_us-ascii Jay,

For the definitive list on your current SMF settings, just issue /D SMF,O from the console.

Regards
Phil

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Reavill, Jay
Sent: 16 July 2010 14:36
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] What is DB2DIST doing?!?! - A Performance Quandary - z\OS V8

Hi Richard,

This is very interesting information. However, I don't see a SMFPRM00
member in our SYS1.PARMLIB. And I'm guessing the second F in the member
name was a typo since it makes the name too long. These are the SMF
members that we have... [...]72_1454A0984F246445950CB759E249F50F468A9DD414@csis564.wlp.uk.winterthur.com 6169 511 27_Re: REORG issue during SORT14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 16 Jul 2010 10:37:33 -0400620_iso-8859-1 Roy,
Link?

________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell
Sent: Friday, July 16, 2010 6:15 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] REORG issue during SORT

check the DFSORT survival guide for useful tips about errors/problems just like this!!

I will send it offline to you!

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 [...]64_1BA00CBB414DA34AA25ED82F4647CDE20349B975F6@MAIL02.fisalan.nycnet 6681 669 27_Re: REORG issue during SORT9_Ford Wong14_fordie@SHAW.CA31_Fri, 16 Jul 2010 08:45:58 -0600381_iso-8859-1 Hello,

As Michael mentioned earlier. Sometimes, it may be because RUNSTATS are out of date. I'm not familiar with using VIO. At our shop sometimes when we get a SORT CAPACITY EXCEEDED message, this can fixed by doing a RUNSTATS and then trying the REORG again. We also have specified SORTNUM 72 (72 is a magic number at our shop which someone came up with). [...]30_f318f1658613e.4c401c46@shaw.ca 7351 280 64_Re: What is DB2DIST doing?!?! - A Performance Quandary - z\OS V811_Ted MacNEIL18_eamacneil@YAHOO.CA31_Fri, 16 Jul 2010 17:05:05 +0000519_- I believe if you issue the operator command:

D SMF

You will find out which SMFPRMxx is in use.

-
I'm a SuperHero with neither powers, nor motivation!
Kimota!

-----Original Message-----
From: "Reavill, Jay"
Sender: IDUG DB2-L
Date: Fri, 16 Jul 2010 08:35:47
To:
Reply-To: IDUG DB2-L
Subject: Re: [DB2-L] What is DB2DIST doing?!?! - A Performance Quandary - z\OS V8 [...]105_1714486905-1279299867-cardhu_decombobulator_blackberry.rim.net-1989408095-@bda026.bisx.prod.on.blackberry 7632 660 64_Re: What is DB2DIST doing?!?! - A Performance Quandary - z\OS V832_Humphris,Richard P. (Contractor)25_RichardP.Humphris@CNA.COM31_Fri, 16 Jul 2010 12:06:34 -0500420_us-ascii Hi Jay,

After you issue the command Phil suggested, then look in the SMFPRMxx member. DDCONS if omitted, this it will be set to DDCONS(YES) by default.

Otherwise, you could look at the MVS initialization and tuning reference to see how your shop might decide to set it to NO (you have several options). In my opinion, you'd probably want to get your MVS sysprogs involved in this discussion. [...]60_10B866DA3B4BC44FA723C2C315C8FE6F04CC95FE5A@E2K7CLSTA.cna.com 8293 61 35_Re: SQLCODE -30082 with REASON 15 ?15_Hagedorn, Linda24_Linda_Hagedorn@MEDCO.COM31_Fri, 16 Jul 2010 14:13:28 -0400538_us-ascii Nenad,

The beginning of your message was truncated. Please repost.
How is DB2BDWP configured in CDB? Please display the column values:

SYSIBM.LUNAMES
SYSIBM.IPNAMES
SYSIBM.USERNAMES
SYSIBM.LOCATIONS

And /DISPLAY NET,APPLS to list the VTAM applids.

Linda

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Nenad Vidak
Sent: Monday, July 12, 2010 6:32 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] SQLCODE -30082 with REASON 15 ? [...]64_D318CB00744E3D46BCEBED74B3F3DE3107ECF404@fr1-mx-usr04p.medco.com 8355 582 64_Re: What is DB2DIST doing?!?! - A Performance Quandary - z\OS V812_Reavill, Jay27_Jay.C.Reavill@FISGLOBAL.COM31_Fri, 16 Jul 2010 13:32:36 -0500799_us-ascii Hi Rich,

Yes, I have spoken to them and we do have it set to NO. Interesting
that we are seeing some very similar behavior tho.

Thanks again!

-------------------------------------------------------------
Jay Reavill
DBA
Fidelity National Information Services, Inc.
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
Mobile (727) 215-5794
jay.c.reavill@fnisglobal.com
-------------------------------------------------------------

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of
Humphris,Richard P. (Contractor)
Sent: Friday, July 16, 2010 1:07 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] What is DB2DIST doing?!?! - A Performance Quandary
- z\OS V8 [...]62_C90B7164E1511D499B3C479D8B7CEC92021C2702@CMBFISLTC07.FNFIS.COM 8938 799 64_Re: What is DB2DIST doing?!?! - A Performance Quandary - z\OS V832_Humphris,Richard P. (Contractor)25_RichardP.Humphris@CNA.COM31_Fri, 16 Jul 2010 15:36:48 -0500484_us-ascii Hi Jay,

Another suggestion would be to look for evidence of DSMAX being set too low and of tablespaces being closed (and potentially reopened) too often resulting in thrashing. If it is set too low and too many datasets need to be opened at one time you can see repetitive closure of files (oldest 3%) followed by reopening of many of the files that were just closed. Note: db2 will even close db2 system tablespaces (which were the first ones opened of course). [...]60_10B866DA3B4BC44FA723C2C315C8FE6F04CC9600D3@E2K7CLSTA.cna.com 9738 205 26_Z/os AUX tablespace Sizing15_Campbell, Wayne28_Wayne.Campbell@COURTS.WA.GOV31_Fri, 16 Jul 2010 14:49:32 -0700409_us-ascii I'm currently getting ready to implement a Lob table into production. The Aux tablespace will start at about 100 gigs. It will grow to about 500 gigs in 3 to 4 years where with the purge process, it should level out. Since this will be by far our largest tablespace (our current largest table is 42 gigs which is partitioned for contention reasons). I run most utilities at the partition level. [...]66_2A1B28C5CC983C4F8253CF1FA1218532159A38D8FC@EXCHMAIL1.courts.wa.gov 9944 27 35_Re: SQLCODE -30082 with REASON 15 ?11_Nenad Vidak21_nenad.vidak@GMAIL.COM31_Sat, 17 Jul 2010 17:58:23 -0400752_UTF-8 Hello Linda,

thank for your post. In the meanwhile we discovered that the problem is with unavailable CSF service in the target system. According to DB2 doc. connecting via TCP/IP should be possible even without those services, but in practice the target DB2 reports DSNLTSEC ICSF NOT ENABLED .

Regards, Nenad

_____________________________________________________________________
* 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! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________ [...]53_4118769490473598.WA.nenad.vidakgmail.com@www.idug.org 9972 529 27_Re: REORG issue during SORT24_Ravichandran Krishnasamy26_ravi.krishnasamy@GMAIL.COM31_Sun, 18 Jul 2010 00:03:43 -0500344_ISO-8859-1 You can try using "SORTDEVT SYSDA SORTNUM nn" in your SYSIN card (where nn
is the number of Sort Datasets. I normally use 12 or 16 for the values of
'nn' ) to increase the number of Sortwork datasets which is 4 by default.

Thanks,
-Ravi.

On Fri, Jul 16, 2010 at 9:45 AM, Ford Wong wrote: [...]60_AANLkTimtMPzG1qkt_YW2GBMxwypd89WuJSvUK9a685Yu@mail.gmail.com 10502 316 27_Re: REORG issue during SORT9_SrinivasG21_SRINIVASG@INFOSYS.COM31_Sun, 18 Jul 2010 22:32:59 +0530699_iso-8859-1 Hi,

Thanks to everyone who replied. This worked :

SORTDEVT SYSDA SORTNUM 16

Regards,
Srinivas G
________________________________
From: IDUG DB2-L [DB2-L@IDUGDB2-L.ORG] On Behalf Of Ford Wong [fordie@SHAW.CA]
Sent: Friday, July 16, 2010 8:15 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] REORG issue during SORT

Hello,

As Michael mentioned earlier. Sometimes, it may be because RUNSTATS are out of date. I'm not familiar with using VIO. At our shop sometimes when we get a SORT CAPACITY EXCEEDED message, this can fixed by doing a RUNSTATS and then trying the REORG again. We also have specified SORTNUM 72 (72 is a magic number [...]69_E5D4741C0D67254EA3601CFEC7810DFA0FBDF36A9C@BLRKECMBX05.ad.infosys.com 10819 45 61_Get the SQL Cache Anyway=?UTF-8?Q?=E2=80=93Even_?=Without OSC11_Dave Beulke19_dave@DAVEBEULKE.COM31_Mon, 19 Jul 2010 00:19:13 -04000_51_8840205822505991.WA.davedavebeulke.com@www.idug.org 10865 398 26_Question :: AIX containers13_Robert Knight28_bknight@REMOTEDBAEXPERTS.COM31_Mon, 19 Jul 2010 07:56:16 -0400350_us-ascii I was reviewing a procedure for backing up a database and restoring it to another.

I believe it's called a redirected restore.

Anyway I did a review of each command that was executed and things made sense until I ran into this:

Before the restore of the target from the source the following commands were executed: [...]75_8902ECFB06C73440874A90F82900BA0E1B4E34BB77@osgood.rdba.remotedbaexperts.com 11264 140 34_z/Journal (June - July 2010 issue)10_DB2usa !!!19_db2usa3@HOTMAIL.COM31_Mon, 19 Jul 2010 08:12:04 -0400405_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, July 19th 2010

Articles from Juin - Juillet 2010 issues of z/Journal are available online:

- DB2 for z/OS SQL Performance Choices
by Dan Luksetich

- Reorganize DB2 on z/OS Objects to Save Time and Money
by Peter Plevka [...]42_SNT118-W6D00D8B10884B83BC703AAFBF0@phx.gbl 11405 574 30_Re: Question :: AIX containers12_Travis, John25_john.travis@CAPGEMINI.COM31_Mon, 19 Jul 2010 13:16:51 +0100623_us-ascii Bob,
A minimal database contains 3 tablespaces (0=SYSCATSPACE, 1=USERSPACE1, 2=TEMPSPACE1). A backup does not backup anything from temporary tablespaces (TEMPSPACE1) so only contains obects from tablespaces 0 and 1.

Displaying the containers for a tablespace shows the path to the physical datasets for the objects which may need to be changed when doing a redirected restore if the existing paths do not exist on the target system. The "containers for 0" means then "containers for tablespace SYSCATSPACE" and since tablespace 2 was not backed up then it does not need to be overridden on restore. [...]71_B5F7E128A29C7B4CAF780CD2A648BF4B0EBB812C@MISTOLXVS31.corp.capgemini.com 11980 232 74_DB2 Tech Talk - DB2 10 Is Coming - A First Look from Available Information13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Mon, 19 Jul 2010 09:48:34 -0400380_us-ascii Well, it looks as though it will be difficult to create a presentation on DB2 10, include all the available information, and still manage to fit it into a 1 hour slot

So we've taken the decision to make TWO webinars instead

Part 1 will be on Thursday August 5th and will look at Migration issues, together with the Performance and Availability changes [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8F7AA88@MAILR004.mail.lan 12213 596 56_Behaviour of Identity Column!(?) OR Understanding of it?19_Srikakolapu, Dinesh28_dinesh.srikakolapu@UCONN.EDU31_Mon, 19 Jul 2010 11:41:50 -0400564_us-ascii Greetings:

DB2 V7 z/OS

I am doing a test on a table with one of the columns defined as IDENTIY (please see below).

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
CREATE TABLE "DINESHS"."UPDATE_TEST2"
( "INDIV_KEY" CHAR (13) WITH DEFAULT NULL
,"EFF_START_DT" DATE WITH DEFAULT NULL
,"EFF_END_DT" DATE WITH DEFAULT NULL
,"ROW_ID_COL" INTEGER NOT NULL
GENERATED ALWAYS
AS IDENTITY
( START WITH 1, MAXVALUE 100)
)
IN "DIS001DB"."DISTESTS"
AUDIT NONE;
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Sequence of events: [...]71_01DE4625CCDFB349924BAE09DCD4512C2F1801119F@EXCHANGE4.grove.ad.uconn.edu 12810 207 65_Re: Get the SQL Cache Anyway=?utf-8?Q?=E2=80=93Even_?=Without OSC18_Satish Srikakulapu26_Satish.Srikakulapu@APS.COM31_Mon, 19 Jul 2010 08:57:59 -0700415_utf-8 Hello Dave,

Isn’t it true that IBM has deprecated OSC and wants all the users of OSC to go to IBM Data Studio?

Thanks,
Satish Srikakulapu

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Dave Beulke
Sent: Sunday, July 18, 2010 9:19 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] Get the SQL Cache Anyway–Even Without OSC

I have a new post on my blog... [...]55_D42E68651088914B9B327A4EEA8433ACACB58D50@VM251.apsc.com 13018 933 60_Re: Behaviour of Identity Column!(?) OR Understanding of it?16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Mon, 19 Jul 2010 12:09:07 -0400441_us-ascii Running utilities or doing stop commands will empty the cache of
identity columns so they cannot be re-used.

You can use the CYCLE parameter on the identity column in order to start
over since the SQL-error below indicated that MAXASSIGNEDVAL in the
catalog was reached.

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect

IBM Certified Database Associate - DB2 9 Fundamentals [...]56_0A18F096E689AC43BB2F52DAE5674D6008D4270E@USILMS14.ca.com 13952 184 65_Re: Get the SQL Cache Anyway=?utf-8?Q?=E2=80=93Even_?=Without OSC12_Myron Miller22_myronwmiller@YAHOO.COM31_Mon, 19 Jul 2010 09:17:23 -0700682_utf-8 That's true. It doesn't work on V10. Data studio is their new tool. It's just too bad that Data Studio doesn't have the user-friendliness of OSC nor the usefulness. Theoretically same features, but???? Myron ________________________________ From: Satish Srikakulapu To: DB2-L@IDUGDB2-L.ORG Sent: Mon, July 19, 2010 11:57:59 AM Subject: Re: [DB2-L] Get the SQL Cache Anyway–Even Without OSC Hello Dave, Isn’t it true that IBM has deprecated OSC and wants all the users of OSC to go to IBM Data Studio? Thanks, Satish Srikakulapu From:IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Dave Beulke Sent: Sunday, July 18, 2010 9:19 PM To: [...]44_861088.98792.qm@web112101.mail.gq1.yahoo.com 14137 39 56_Re: Finding out Dynamic SQLs running on Particular Table13_Jack Campbell23_jackrcampbell@YAHOO.COM31_Mon, 19 Jul 2010 12:37:20 -0400406_UTF-8 One point to note when taking a snapshot of the dynamic SQL cache.................you only get the most frequently execute SQL (not all SQL executed).

For SQL which may be run just once or twice a day for example, it will be flushed from the cache fairly quickly. Also depending on when you take the snapshot, you could easily miss SQL that runs in a fixed time slot each day............ [...]55_3296321479059879.WA.jackrcampbellyahoo.com@www.idug.org 14177 1051 60_Re: Behaviour of Identity Column!(?) OR Understanding of it?19_Srikakolapu, Dinesh28_dinesh.srikakolapu@UCONN.EDU31_Mon, 19 Jul 2010 13:22:10 -0400482_us-ascii Steen,

That's a good point you made, 'usage of CYCLE parameter'.

I'm sure it works in most of the situations, but in my case, I forgot to mention that this testing is part of a bigger exercise. Application people want to include this identity column on a table to ensure there is a unique value for every row (SID - Surrogate ID). So, CYCLE may not serve the purpose here. But, again, I agree that usage of CYCLE parameter does let you start over again. [...]71_01DE4625CCDFB349924BAE09DCD4512C2F180111A0@EXCHANGE4.grove.ad.uconn.edu 15229 345 27_Re: REORG issue during SORT14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Mon, 19 Jul 2010 13:30:46 -0400335_iso-8859-1 Srinivas and Ravi and Ford:
If you know the number of _index keys_ being REORGed (Total number of rows in table times number of indexes, summed over each table in the tablespace), you an use SORTKEYS n instead of SORTNUM and get a more accurate preallocation of sort DASD. Thus saith the recent APARs on DB2V9 z/OS. [...]64_1BA00CBB414DA34AA25ED82F4647CDE2036225E40F@MAIL02.fisalan.nycnet 15575 1380 60_Re: Behaviour of Identity Column!(?) OR Understanding of it?16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Mon, 19 Jul 2010 13:33:31 -0400368_us-ascii Since you didn't specify CACHE on the IDENTITY column, the default is 20
I believe. Then - the first INSERT will put the first 20 values in the
CACHE (to allow quicker inserts). This come for a price - utilities and
STOP commands will not reuse the values left in the cache - these are
lost, which explains why the next round starts with 21. [...]56_0A18F096E689AC43BB2F52DAE5674D6008D427A9@USILMS14.ca.com 16956 1491 60_Re: Behaviour of Identity Column!(?) OR Understanding of it?13_George Toolan19_gtoolan@COMCAST.NET31_Mon, 19 Jul 2010 13:36:39 -0400592_us-ascii You are getting the default value for the cache parameter:

CACHE or NO CACHE

Specifies whether to keep some preallocated values in memory. Preallocating
and storing values in the cache improves the performance of inserting rows
into a table. The default is CACHE 20.

NO CACHE

Specifies that values for the identity column are not preallocated and
stored in the cache, ensuring that values will not be lost in the case of a
system failure. In this case, every request for a new value for the identity
column results in synchronous I/O. [...]35_00e501cb2768$f19a89b0$d4cf9d10$@net 18448 83 50_Row level locking and Data sharing ???/ discussion9_Dee Reins21_rcwingman@COMCAST.NET31_Mon, 19 Jul 2010 17:55:19 +0000376_utf-8 We have 2 way data sharing environment on Z/OS DB2 Ver 9.1. My understanding is that the Coupling Facility can only lock at the page level. Therefore row level locking can tie up DB2 in a hurry, especially if the same page is being accessed from two or more DB2 regions. The application does access the same pages/rows from different DB2 data sharing environments. [...]82_287116422.90078.1279562119934.JavaMail.root@sz0025a.emeryville.ca.mail.comcast.net 18532 38 42_IBM DB2 Information Integrator performance12_George White25_george_white@VANGUARD.COM31_Mon, 19 Jul 2010 14:14:51 -0400449_UTF-8 As most large organizations, we are faced with the need to integrate numerous data stores. There are generally two approaches to this challenge; integrate the data as a result of updates(message replication and data synchs) or federate the data at run time(readers are responsible for understanding integration rules). The theory of the prior is that there are many more readers than updaters. The later requires an approach such as SOA. [...]56_4537348477468735.WA.georgewhitevanguard.com@www.idug.org 18571 247 60_Re: Behaviour of Identity Column!(?) OR Understanding of it?16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Mon, 19 Jul 2010 14:31:58 -0400673_utf-8 Sorry - forgot to address the MAXVALUE question. The max value depnds on the column attribue - meaning smallint will hit the ma sooner than integer or decimal(11).

Steen
------Original Message------
From: Srikakolapu, Dinesh
To: DB2-L@IDUGDB2-L.ORG
ReplyTo: IDUG DB2-L
Subject: Re: [DB2-L] Behaviour of Identity Column!(?) OR Understanding of it?
Sent: Jul 19, 2010 12:22 PM

Steen,
 
That’s a good point you made, ‘usage of CYCLE parameter’.
 
I’m sure it works in most of the situations, but in my case, I forgot to mention that this testing is part of a bigger exercise. Application people want to include [...]56_0A18F096E689AC43BB2F52DAE5674D600864509A@USILMS14.ca.com 18819 929 27_Re: REORG issue during SORT16_Christian Michel18_cmichel@DE.IBM.COM31_Mon, 19 Jul 2010 20:34:08 +0200687_ISO-8859-1 Sorry Phil, but I need to make a small correction: "SORTKEYS n" can only
be used for LOAD, not for REORG. I recommend looking at "NUMRECS n"
though, which is available now since PK88970/PK88974.

Mit freundlichen Grüßen / Kind regards
Christian Michel

Christian Michel
E-mail: cmichel@de.ibm.com
Phone (49)-7031-16-4293
DB2 for z/OS Utilities Development
Information Management Development Boeblingen, Germany

IBM Deutschland Research & Development GmbH
Vorsitzender des Aufsichtsrats: Martin Jetter
Geschäftsführung: Dirk Wittkopp
Sitz der Gesellschaft: Böblingen / Registergericht: Amtsgericht Stuttgart,
HRB 243294 [...]68_OF51AF528D.E7488B67-ONC1257765.0065D498-C1257765.00660043@de.ibm.com 19749 161 26_z/OS 1.11 DB2 v9 (someday)15_Chris Hoelscher21_choelscher@HUMANA.COM31_Mon, 19 Jul 2010 15:24:11 -0400561_US-ASCII a co-worker asked me to post this:

while we are still on V8, we are planning future processes with V9 in
mind, thus this question

In V9 when you have a table based partitioned tablespace, can PARTITIONS
BE LOADED IN PARALLEL or must they be single threaded? I know in V8 you
can do them in Parallel but I believe I had heard with partition
independence improvements (in V 9) that you may no longer do this. My
specific case I am doing a LOAD utility to empty out the partitions before
we alter the table to Rotate. [...]68_OFF217F99E.6A07CD9C-ON85257765.006A565D-85257765.006A958B@humana.com 19911 1459 60_Re: Behaviour of Identity Column!(?) OR Understanding of it?19_Srikakolapu, Dinesh28_dinesh.srikakolapu@UCONN.EDU31_Mon, 19 Jul 2010 15:51:30 -0400488_us-ascii George & Steen:

That was helpful about pointing at default of CACHE being 20. That straightaway answers the question of why 21 instead of 9.

Thank you for helping me understand.

Dinesh Srikakolapu
DB2 DBA for z/OS
UCONN

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of George Toolan
Sent: Monday, July 19, 2010 1:37 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Behaviour of Identity Column!(?) OR Understanding of it? [...]71_01DE4625CCDFB349924BAE09DCD4512C2F180111B4@EXCHANGE4.grove.ad.uconn.edu 21371 400 60_Re: Behaviour of Identity Column!(?) OR Understanding of it?19_Srikakolapu, Dinesh28_dinesh.srikakolapu@UCONN.EDU31_Mon, 19 Jul 2010 15:52:16 -0400495_utf-8 Got it, Steen. Thanks much.

Dinesh Srikakolapu
DB2 DBA for z/OS
UCONN

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Rasmussen, Steen
Sent: Monday, July 19, 2010 2:32 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Behaviour of Identity Column!(?) OR Understanding of it?

Sorry - forgot to address the MAXVALUE question. The max value depnds on the column attribue - meaning smallint will hit the ma sooner than integer or decimal(11). [...]71_01DE4625CCDFB349924BAE09DCD4512C2F180111B5@EXCHANGE4.grove.ad.uconn.edu 21772 108 54_Re: Row level locking and Data sharing ???/ discussion14_Larry Kintisch17_LKint@VERIZON.NET31_Mon, 19 Jul 2010 15:52:35 -0400488_us-ascii Hi Dee,
I am not a Data Sharing specialist. I have done some study of it,
though, and the text below should help you decide. Use of MAXROWS
on the Tablespaces has a number of performance tradeoffs, including
less effective use of the bufferpools. But a TS with a page that
has, say 10 rows 400 bytes long that becomes LOCKSIZE PAGE with
MAXROWS 5 should reduce Global lock contention as the data would be
spread over twice as many pages. Larry Kintisch [...]40_0L5T00B9NLW6O7H0@vms173003.mailsrvcs.net 21881 24 66_Auto Reply: DB2-L Digest - 19 Jul 2010 - Special issue (#2010-194)13_Chris Economy28_christine.economy@ORACLE.COM31_Mon, 19 Jul 2010 12:57:26 -0700678_utf-8 This is an auto-replied message. I am out of the office.
I will respond to your E-mail when I return.
Thank you.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* 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!
_____________________________________________________________________ [...]44_c2926d89-b6c4-4292-a68d-d5fe1816754e@default 21906 89 38_Luke R Tetreault is out of the office.6_Luke T28_Luke_R_Tetreault@KEYBANK.COM31_Mon, 19 Jul 2010 17:56:03 -0400724_us-ascii I will be out of the office starting 07/19/2010 and will not return until
07/21/2010.


This communication may contain privileged and/or confidential information. It
is intended solely for the use of the addressee. If you are not the intended
recipient, you are strictly prohibited from disclosing, copying, distributing
or using any of this information. If you received this communication in error,
please contact the sender immediately and destroy the material in its entirety,
whether electronic or hard copy. This communication may contain nonpublic personal
information about consumers subject to the restrictions of the
Gramm-Leach-Bliley Act. You may not directly [...]69_OF3452A964.51EF2BDB-ON85257765.00787D2B-85257765.00787D2B@keybank.com 21996 731 60_Re: Behaviour of Identity Column!(?) OR Understanding of it?13_Case, Missy J24_Missy.Case@FIRSTDATA.COM31_Mon, 19 Jul 2010 16:09:38 -0500572_us-ascii The jump in identity numbers are the ones you specified be loaded in to
cache ahead of time via your table column create (generated always
increment by 1 cache 500). In this case it will go to cache and
allocate as unavailable the NEXT 500 numbers. So if your application
times out our your numbers are gone, your next chance will be 500 away
from where you stopped. (we had an interesting one where 10 numbers
went away randomly in prod - they were hitting the idle thread timeout
value & invalidating those 1 or 4 or 9 values.....) it [...]64_3C8EB6E657433F439F22BB22A069206802A01707@WFDROMPXMASRV04.1DC.COM 22728 239 54_Re: Row level locking and Data sharing ???/ discussion16_Robert Catterall21_rfcatterall@GMAIL.COM31_Mon, 19 Jul 2010 21:05:36 -0400661_ISO-8859-1 Dee,

It is absolutely NOT true that the coupling facility can only lock at the
page level. Coupling facility control code (and the system lock manager
component of z/OS) will lock whatever a lock-service-requesting subsystem
(such as DB2) wants locked, at whatever granularity (row, page,
tablespace,...).

Here's what's different about page- versus row-level locking in a DB2 data
sharing environment: when data is locked at a row level, in addition to the
logical lock on the target row (owned by the application process that's
accessing the data), you get a physical lock on the page in which the row is
[...]60_AANLkTilI6hrR7x01q0i1wxLoK8dJPu9yiDolnw2Sf9rK@mail.gmail.com 22968 42 20_archive log size....15_Smartcurl Zhang19_smartcurl@GMAIL.COM31_Tue, 20 Jul 2010 14:32:13 +0800627_ISO-8859-1 Hi all,

I came with a strange item.I set archive log parameter to (1000,100) cyl in
ZPARM.But,my archive log datasets which DB2 give out occupy only 760
tracks.Have anyone met this situation ?

_____________________________________________________________________
* IDUG EMEA * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________ [...]60_AANLkTikIBEgKohDXHOGGUTdO_j1ofmVFe32_Z2tJbbEC@mail.gmail.com 23011 81 34_Betr: [DB2-L] archive log size....15_Marcel Harleman31_mam.harleman@BELASTINGDIENST.NL31_Tue, 20 Jul 2010 09:28:36 +0200730_us-ascii --Hi all,

--I came with a strange item.I set archive log parameter to (1000,100) cyl
in ZPARM.But,my archive log datasets which DB2 give out occupy only 760
tracks.Have anyone met this situation ?

Hi,

SMS probably strips the free space after the archive log has been closed.
How big are your online logs? 760 tracks?

Marcel.

------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl. [...]76_OF5D884BE1.73500AEC-ONC1257766.0028EFC0-C1257766.002914BA@belastingdienst.nl 23093 123 38_Re: Betr: [DB2-L] archive log size....15_Smartcurl Zhang19_smartcurl@GMAIL.COM31_Tue, 20 Jul 2010 15:53:59 +0800656_ISO-8859-1 Hi,Marcel.

YES.online logs are 760 tracks.
But the management class for archive logs be configured with PARTIAL
RELEASE=NO, isn't that supposed to be *don't strips *?
Do i have to configure online and archive logs to same size ?

Smartcurl Zhang.

On Tue, Jul 20, 2010 at 3:28 PM, Marcel Harleman <
mam.harleman@belastingdienst.nl> wrote:

> --Hi all,
>
> --I came with a strange item.I set archive log parameter to (1000,100) cyl
> in ZPARM.But,my archive log datasets which DB2 give out occupy only 760
> tracks.Have anyone met this situation ?
>
> Hi,
>
> SMS probably strips [...]60_AANLkTikQ2yidmGHbPB9CqJNlAxQbBUfcaXSFUgAetmwK@mail.gmail.com 23217 84 52_Betr: Re: [DB2-L] Betr: [DB2-L] archive log size....15_Marcel Harleman31_mam.harleman@BELASTINGDIENST.NL31_Tue, 20 Jul 2010 10:07:55 +0200734_us-ascii --Hi,Marcel.

--YES.online logs are 760 tracks.
--But the management class for archive logs be configured with PARTIAL
RELEASE=NO, isn't that supposed to be don't strips ?
--Do i have to configure online and archive logs to same size ?

Hi Zhang,

just an extra question: have those archive logs been migrated and recalled?

Marcel.

------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl. [...]76_OFB5CDDD48.7DE48C90-ONC1257766.002C91A0-C1257766.002CAE42@belastingdienst.nl 23302 281 38_AW: [DB2-L] z/OS 1.11 DB2 v9 (someday)35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Tue, 20 Jul 2010 10:31:27 +0200833_iso-8859-1 Chris

AFAIK, there has nothing changed in this arena in V9. So your mentioned syntax is still valid.

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40477 Düsseldorf
mailto:walter.janissen@itergo.com

Vorsitzender des Aufsichtsrates: 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 Chris Hoelscher
Gesendet: Montag, 19. Juli 2010 21:24
An: DB2-L@IDUGDB2-L.ORG
Betreff: [DB2-L] z/OS 1.11 DB2 v9 (someday) [...]43_DB2-L%201007200431381924.247E@IDUGDB2-L.ORG 23584 38 61_z/OS: Software for generating DDL for existing views/synonyms14_Daniel Erdmann21_edh@VOLKSWOHL-BUND.DE31_Tue, 20 Jul 2010 11:41:09 +0200646_iso-8859-1 Hello,

we need to change the CCSIDs of several thousand tablespaces within our DB2
subsystems.
AFAIK it's not possible to alter a tablespace while there are views existent
which refer to tables within the tablespace.

At all we need some software to generate the DDL for views, synonyms etc.
which refer to the tablespace to be altered to be able to drop the views,
alter the tablespace and then recreate the views etc. (incl. everything
which depended on it like views, synonyms etc. which might have
automatically been dropped because they refered to the first view which has
been dropped). [...]36_07DED101F46A439DB1F0B4995354D269@edh 23623 124 56_Re: Betr: Re: [DB2-L] Betr: [DB2-L] archive log size....15_Smartcurl Zhang19_smartcurl@GMAIL.COM31_Tue, 20 Jul 2010 19:30:06 +0800726_ISO-8859-1 Marcel,

No,coz we have not implement HSM yet.

Smartcurl Zhang

On Tue, Jul 20, 2010 at 4:07 PM, Marcel Harleman <
mam.harleman@belastingdienst.nl> wrote:

>
> --Hi,Marcel.
>
>
> --YES.online logs are 760 tracks.
> --But the management class for archive logs be configured with PARTIAL
> RELEASE=NO, isn't that supposed to be *don't strips *?
> --Do i have to configure online and archive logs to same size ?
>
> Hi Zhang,
>
> just an extra question: have those archive logs been migrated and recalled?
>
>
> Marcel.
>
> ------------------------------------------------------------------------
> De Belastingdienst gebruikt e-mail [...]60_AANLkTikfMp_bSPPKR00aGBX_MOe4rg44l-yui4oPpzV_@mail.gmail.com 23748 92 70_Betr: Re: [DB2-L] Betr: Re: [DB2-L] Betr: [DB2-L] archive log size....15_Marcel Harleman31_mam.harleman@BELASTINGDIENST.NL31_Tue, 20 Jul 2010 13:48:07 +0200452_us-ascii Hi Zhang,

ok, PARTIAL RELEASE=NO does indeed mean "do not release unused space", so
also the space management cycles will not touch it for this purpose.

I would not know anything more than the obvious "have you checked via the
LISTCAT command if the management class is indeed picked up by the ACS
routines for your archive log datasets?" But I assume you have, so for the
moment I do not know anything else ... [...]76_OF3E489A67.5EBEFEB9-ONC1257766.003FCC23-C1257766.0040D73A@belastingdienst.nl 23841 176 33_z/OS v9 (someday): planning ahead15_Chris Hoelscher21_choelscher@HUMANA.COM31_Tue, 20 Jul 2010 08:26:48 -0400567_US-ASCII if this made it through the first time - my apologies

a co-worker asked me to post this:

while we are still on V8, we are planning future processes with V9 in
mind, thus this question

In V9 when you have a table based partitioned tablespace, can PARTITIONS
BE LOADED IN PARALLEL or must they be single threaded? I know in V8 you
can do them in Parallel but I believe I had heard with partition
independence improvements (in V 9) that you may no longer do this. My
specific case I am doing a LOAD utility to empty out [...]68_OF1C9E40A2.454138F9-ON85257766.004438C1-85257766.00445F69@humana.com 24018 132 74_Re: Betr: Re: [DB2-L] Betr: Re: [DB2-L] Betr: [DB2-L] archive log size....15_Smartcurl Zhang19_smartcurl@GMAIL.COM31_Tue, 20 Jul 2010 21:52:45 +0800654_ISO-8859-1 Marcel,

Honestly,i don't know how my DB2 figured out it's active logs' size.I have
check panel DSNTIPL,and cann't find *active log size *item.
Could you please explain that for me?
Thank you.

Smartcurl Zhang

On Tue, Jul 20, 2010 at 7:48 PM, Marcel Harleman <
mam.harleman@belastingdienst.nl> wrote:

> Hi Zhang,
>
> ok, PARTIAL RELEASE=NO does indeed mean "do not release unused space", so
> also the space management cycles will not touch it for this purpose.
>
> I would not know anything more than the obvious "have you checked via the
> LISTCAT command if the management class [...]60_AANLkTinp5jRdHwCoDMfswLScefz4O3cLDpt32bC5jwZX@mail.gmail.com 24151 227 78_[AD] Re: [DB2-L] z/OS: Software for generating DDL for existing views/synonyms11_Roy Boxwell16_R.Boxwell@SEG.DE31_Tue, 20 Jul 2010 15:54:43 +0200691_ISO-8859-1 Changed to AD - Yes! Just ask me/us for our DDLGEN - Handles recursive
VIEWs, ALIASs, GRANTS etc etc etc (Best bet is a a mail to
techsupport@segus.de
!)

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: Gerhard Schubert

Daniel Erdmann
Gesendet von: IDUG DB2-L
20.07.2010 11:41
Bitte antworten an
IDUG DB2-L [...]64_OFD830F94F.EE227EFA-ONC1257766.004C5162-C1257766.004C6BBD@seg.de 24379 244 40_Re: Antwort: [DB2-L] DB2 z/OS encryption4_Rods19_custodior@GMAIL.COM31_Tue, 20 Jul 2010 11:07:38 -0300497_ISO-8859-1 Hi Gregg,

Will you use IBM Data Encryption for IBM Data Encryption for IMS and DB2? If
so, please check apar PM08638 and PK71816 for DB2 and PM08556 for IBM Data
Encryption. It will avoid some storage overlay. (I heard it may cause a
problem (SQL -652) when moving to z/OS 1.11)

__________________________________________________________
Rodolfo Custodio
"Those who do not learn from their mistakes are destined to repeat them."
(Winston Churchill) [...]60_AANLkTimOjYs9Bt8nFN_FY7g2mxG-TzhlkKCAFy0n_VZn@mail.gmail.com 24624 165 74_Re: Betr: Re: [DB2-L] Betr: Re: [DB2-L] Betr: [DB2-L] archive log size....15_Smartcurl Zhang19_smartcurl@GMAIL.COM31_Tue, 20 Jul 2010 22:33:41 +0800593_ISO-8859-1 I find out the active logs size is determined by DB2, and that always be
documented in *Administration Guide...*
*Back to my original question,It seems to archive action has nothing to do
with MC.The archive log datasets size are determined according to how many
records need to be wrote out if there are much enouth log records DB2 will
allocate archive log datasets with size parameters in ZPARM,if not,DB2 take
disk space whatever it actually need. *
BTW,*ARCHIVE LOG FREQ* item in installation panel is not recorded in
ZPARM,and where is it keeped? [...]60_AANLkTilFMgoquGlY6GdrX5406OYSTVcKSTobGB7B7c6B@mail.gmail.com 24790 1015 60_Re: Behaviour of Identity Column!(?) OR Understanding of it?10_Sudhir Raj26_sudhir.raj@REALTIMEDBA.COM31_Tue, 20 Jul 2010 11:09:18 -0400376_ISO-8859-1 Dinesh, You could also look at using Sequences, it's the same concept
as Identity, but it's a stand alone object, not tied to any one table,
for your surrogate ID requirement, you create the sequence value once
and use the unique number to tie multiple tables.

Other considerations for CACHE and CYCLE etc.. are the same as Identity
column. [...]32_4C45BC1E.4030805@realtimedba.com 25806 1282 60_Re: Behaviour of Identity Column!(?) OR Understanding of it?13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Tue, 20 Jul 2010 11:25:37 -0400392_us-ascii One question I didn't see addressed was the one about "ALWAYS" versus "BY DEFAULT"

Although GENERATED ALWAYS seems like the best choice, it's only the best until you need to override it (such as when unloading and reloading the table)

So BY DEFAULT might be better, so long as you can guarantee that no-one will try inserting their "own" value when they shouldn't [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8F7AB57@MAILR004.mail.lan 27089 1142 60_Re: Behaviour of Identity Column!(?) OR Understanding of it?19_Srikakolapu, Dinesh28_dinesh.srikakolapu@UCONN.EDU31_Tue, 20 Jul 2010 11:33:03 -0400483_us-ascii Hello Sudhir:

I agree. SEQUENCE is indeed a better option. But unfortunately, we are still in V7 and I understand that it is not available until V8. But, thanks for the suggestion anyway.

Dinesh Srikakolapu
DB2 DBA for z/OS
UCONN

From: Sudhir Raj [mailto:sudhir.raj@realtimedba.com]
Sent: Tuesday, July 20, 2010 11:09 AM
To: IDUG DB2-L
Cc: Srikakolapu, Dinesh
Subject: Re: Behaviour of Identity Column!(?) OR Understanding of it? [...]71_01DE4625CCDFB349924BAE09DCD4512C2F180111BB@EXCHANGE4.grove.ad.uconn.edu 28232 1504 60_Re: Behaviour of Identity Column!(?) OR Understanding of it?19_Srikakolapu, Dinesh28_dinesh.srikakolapu@UCONN.EDU31_Tue, 20 Jul 2010 11:43:51 -0400519_us-ascii Hello Phil:

That's correct.

I am kind of sure, now, which one to go with when it goes to PRODUCTION. I asked the apps group to provide me with information as in (just to reconfirm), if EVER they would need to insert manually (still awaiting the response). So far, my understanding with them is NEVER an insert will take place. It is always going to be LOAD (and perhaps unload and load in the event of a problem). So I am inclined to believe that (in my case) BY DEFAULT is a better option. [...]71_01DE4625CCDFB349924BAE09DCD4512C2F180111BC@EXCHANGE4.grove.ad.uconn.edu 29737 85 65_Re: z/OS: Software for generating DDL for existing views/synonyms13_Bright, Randy20_Randy_Bright@BMC.COM31_Tue, 20 Jul 2010 11:18:40 -0500547_us-ascii Both Catalog Manager and Change Manager for DB2 from BMC Software can generate the DDL for the Views and Synonyms on the subsystem.

With Change Manager you can request that CCSIDs be changed and the product will create an all inclusive worklist that will do everything needed to make it happen. You can even use the automation piece of the product to request the CCISD change via SQL type language. If you have several thousand TSs that would probably be far easier that trying to request the alter for each TS one at a time. [...]69_476996CBBE9AF14285E09E63C370072A147D456091@PHXCCRPRD01.adprod.bmc.com 29823 58 65_Re: z/OS: Software for generating DDL for existing views/synonyms0_19_galambos@ROGERS.COM31_Tue, 20 Jul 2010 16:33:40 +0000319_- Well, given once again the vendors have chimed, I will do so as well.

Compuware's product (called DBA-Xpert) can do this and a whole lot more. It is one product that handles the entire gamet of functions that DBA need to do on a regular bases.

So if you want more info, feel free in contacting me. [...]104_1132589103-1279643623-cardhu_decombobulator_blackberry.rim.net-255097203-@bda274.bisx.prod.on.blackberry 29882 62 54_Re: Row level locking and Data sharing ???/ discussion13_Jack Campbell23_jackrcampbell@YAHOO.COM31_Tue, 20 Jul 2010 12:47:31 -0400425_UTF-8 Dee,

I have encountered similar issues with both vendor and in-house applications using multiple concurrent queues. Whilst row level locking (RLL) may alleviate these problems in some cases, it is not without a cost (even if you are not data-sharing). There will be an increase in the number of lock requests to the IRLM and an increase in the amount of memory consumed to track the locks at the row-level. [...]55_2873354644240731.WA.jackrcampbellyahoo.com@www.idug.org 29945 120 65_Re: z/OS: Software for generating DDL for existing views/synonyms11_Diana Nagel16_dknagel@BEPC.COM31_Tue, 20 Jul 2010 11:56:25 -0500348_us-ascii Advanced Query Tool can be used to generate any and all alter statements tablespaces, indexes etc, etc ...also generate ddl for tablespaces, tables, views, indexes etc...
Works great and easy to learn and use. Cost is minimal....Free trial for a month.

I love it....I have had for I think 7 years....and use all day long... [...]56_6454C4DAE553484980557DB37B06BC030D4DF99B@HDQ153.bepc.net 30066 129 65_Re: z/OS: Software for generating DDL for existing views/synonyms16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Tue, 20 Jul 2010 12:57:09 -0400343_us-ascii And from CA we offer RC/Migrator. You can specify a Global Change for
the entire subsystem, a database or any number of selected objects.
Everything that needs to be done in order to get the objects back in
good shape is generated.

As Compuware and BMC mentioned - please feel free to contact me off the
list. [...]56_0A18F096E689AC43BB2F52DAE5674D6008D42C83@USILMS14.ca.com 30196 1641 60_Re: Behaviour of Identity Column!(?) OR Understanding of it?19_Srikakolapu, Dinesh28_dinesh.srikakolapu@UCONN.EDU31_Tue, 20 Jul 2010 13:51:20 -0400411_us-ascii Hello:

I just noticed another thing w.r.t Identity column.

Load input file has 6 records.

1. Table is empty. After 1st load, 1 thru 6 values have been generated.

2. Load another 6 records, now there are the 6 new values that are generated, 1001, 1002, 1003, 1004, 1005, 1006

3. Now, load another 6 records and similar result - 2001, 2002, 2003, 2004, 2005, 2006 [...]71_01DE4625CCDFB349924BAE09DCD4512C2F180111C0@EXCHANGE4.grove.ad.uconn.edu 31838 558 60_Re: Behaviour of Identity Column!(?) OR Understanding of it?12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 20 Jul 2010 11:07:34 -0700540_utf-8 Dinesh, In V7, one option that more or less works like SEQUENCE is to create a "identity column table", ie a table that just has one row in it - the identity column. Then create a SP that is used to return the next value. Have the application call the SP to retrieve the next value rather than coding it directly and store that value into their table. Other than the overhead of calling the SP, this works really well and really isolates the basic user tables from 99% of the issues with identity columns. Now you've only got to [...]44_949995.48318.qm@web112105.mail.gq1.yahoo.com 32397 1646 60_Re: Behaviour of Identity Column!(?) OR Understanding of it?12_Isaac Yassin20_yassini@BEZEQINT.NET31_Tue, 20 Jul 2010 21:56:26 +0300535_us-ascii Hi

While you are not required to have unique index with GENERATED ALWAYS it
can be very time consuming to search on that column without an index L

So you end up with defining unique index for GENERATED ALWAYS same as you do
for GENERATED BY DEFAULT.

Isaac Yassin

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Grainger
Sent: Tuesday, July 20, 2010 6:26 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Behaviour of Identity Column!(?) OR Understanding of
it? [...]35_010701cb283d$419e2370$c4da6a50$@net 34044 63 50_Tom Glaser is out of the office Wednesday morning.10_Tom Glaser25_tom_glaser@MASTERCARD.COM31_Tue, 20 Jul 2010 16:52:50 -0500577_US-ASCII I will be out of the office starting 07/20/2010 and will not return until
07/21/2010.

CONFIDENTIALITY NOTICE
This e-mail message and any attachments are only for the use of the intended recipient and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient, any disclosure, distribution or other use of this e-mail message or attachments is prohibited. If you have received this e-mail message in error, please delete and notify the sender immediately. Thank you. [...]72_OFD47B4B8E.AA77B1E6-ON86257766.0078318E-86257766.0078318E@mastercard.com 34108 299 54_Re: Row level locking and Data sharing ???/ discussion9_Dee Reins21_rcwingman@COMCAST.NET31_Tue, 20 Jul 2010 22:13:06 +0000391_utf-8 Thank you all for the information on Row level locking and Data sharing.
I will use this to selectively add row level locking to tables where it is needed.
Before I go to any RLL I will make sure that the sql is tuned correctly, Buffers and sized properly, and system resources(Rid pool, lock pool) are sized properly. I figure that will show the real tables that need RLL. [...]83_299206384.161448.1279663986674.JavaMail.root@sz0025a.emeryville.ca.mail.comcast.net 34408 376 74_Re: Betr: Re: [DB2-L] Betr: Re: [DB2-L] Betr: [DB2-L] archive log size....16_Gregory Palgrave28_gpalgrave@STAFF.IINET.NET.AU31_Wed, 21 Jul 2010 07:36:25 +0800545_us-ascii Disclaimer: I haven't work on z/OS for a long time, but:

The active logs are a type of VSAM linear dataset, and can be defined with IDCAMS - I think there is a DSNTIJxx job in the installation set somewhere that does this. (Google tells me it might be DSNTIJID)

Sizing the logs depends on the workload on your system. We used to have a 'rule of thumb' that we should have the logs big enough that they would archive every 15 minutes or so under normal workload - but part of that was dictated by DR procedures at the [...]73_8522673947DF7D4394C962BFE441CD270509CA68B5@PER-EXCHMBX.win2k.iinet.net.au 34785 314 74_Re: Betr: Re: [DB2-L] Betr: Re: [DB2-L] Betr: [DB2-L] archive log size....15_Smartcurl Zhang19_smartcurl@GMAIL.COM31_Wed, 21 Jul 2010 15:20:08 +0800638_windows-1252 hi,Greg
hi,Marcel

I have enlarge my active log datasets to 1000 CYL,and archive log became
15000trk also.

thank you!but i still wonder where DB2 keep *ARCHIVE LOG FREQ=24?*

Smartcurl Zhang

On Wed, Jul 21, 2010 at 7:36 AM, Gregory Palgrave <
gpalgrave@staff.iinet.net.au> wrote:

> Disclaimer: I haven't work on z/OS for a long time, but:
>
>
>
> The active logs are a type of VSAM linear dataset, and can be defined with
> IDCAMS – I think there is a DSNTIJxx job in the installation set somewhere
> that does this. (Google tells me it might be DSNTIJID)
[...]60_AANLkTinzKCxBk-rmk49VW6GXSy1H1+tL-r8Xk8QgdBvX@mail.gmail.com 35100 82 70_Re: [AD] z/OS: Software for generating DDL for existing views/synonyms11_Enrico Haak21_eh@INSOFT-SOFTWARE.DE31_Wed, 21 Jul 2010 09:34:36 +0200628_ISO-8859-1 Here's another vendor offering help:

www.insoft-software.de

Our product "Migration Aid" will give you all the features you are
looking for
(and much more).

Have a look.

Regards,
Enrico

Mit freundlichen Grüßen / Best Regards

Enrico Haak

___________________________________________________________________

InSoft Software GmbH, Derendorfer Str. 70, 40479 Düsseldorf / Germany
Tel. +49 211 44 03 166, Fax +49 211 48 80 33
Geschäftsführer: Colin Oakhill, Günther Wichmann
HRB 20486 DÜSSELDORF * UST-IDNR:DE 119253977 * www.insoft-software.de [...]35_4C46A30C.3010305@insoft-software.de 35183 25 74_Re: Betr: Re: [DB2-L] Betr: Re: [DB2-L] Betr: [DB2-L] archive log size....12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Wed, 21 Jul 2010 07:07:06 -0400662_UTF-8 Archive Log Frequency (Panel DSNTIPL) is only used by the install clist to calculate archive log size. The only place that you'll be able to see this variable is in your DSNTID in variable NUMHRARC.

Regards, Adam

_____________________________________________________________________
* 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 best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________ [...]54_3429625850491790.WA.adambaldwines.ibm.com@www.idug.org 35209 32 65_Re: z/OS: Software for generating DDL for existing views/synonyms14_Peter Vanroose17_pvanroose@ABIS.BE31_Wed, 21 Jul 2010 07:52:27 -0400314_UTF-8 For those without 3rd party vendor tools: there is a freely downloadable IBM program called DB2PLI8 which is able to do this (with a little bit of additional manual work).
In our next issue of "Exploring DB2" (appearing end of August), there will be a contribution explaining how to do exactly this. [...]49_3327902773625810.WA.pvanrooseabis.be@www.idug.org 35242 101 88_Betr: Re: [DB2-L] Betr: Re: [DB2-L] Betr: Re: [DB2-L] Betr: [DB2-L] archive log size....15_Marcel Harleman31_mam.harleman@BELASTINGDIENST.NL31_Wed, 21 Jul 2010 14:49:36 +0200421_iso-8859-1 -- .... where DB2 keep ARCHIVE LOG FREQ=24?

Zhang,

if you know CLIST then have a look at SDSNCLST(DSNTINS*) and SDSNCLST
(DSNTCALC). That's where the values you specify through the panels are
being checked and used in calculations and stuff. There you can track down
(if you're really interested) what happens with those values (ARCHIVE LOG
FREQ a.k.a. NUMHRARC is one of them). [...]76_OF49E13479.58B61728-ONC1257767.0045E5A1-C1257767.004675C3@belastingdienst.nl 35344 85 65_Re: z/OS: Software for generating DDL for existing views/synonyms16_Daniel Luksetich18_danl@DB2EXPERT.COM31_Wed, 21 Jul 2010 07:52:30 -0500617_utf-8 If you have DB2 connect you can use the DB2LOOK command to generate DDL, but not table spaces.

db2look -d DSN1 -e -x -u SCHEMA -i AUTHID -w PASSWORD

Cheers,
Dan

Daniel L Luksetich
IBM Information Champion
IBM Certified Database Administrator - DB2 9 for z/OS
IBM Certified System Administrator - DB2 9 for z/OS
IBM Certified Solutions Expert - DB2 Universal Database V7.1 Database Administration for UNIX, Windows, and OS/2
IBM Certified Solutions Expert - DB2 UDB V7.1 Family Application Development
IBM Certified Advanced Technical Expert - DB2 Data Replication [...]35_009201cb28d3$94e6df40$beb49dc0$@com 35430 437 74_Re: Betr: Re: [DB2-L] Betr: Re: [DB2-L] Betr: [DB2-L] archive log size....10_Roger Hecq18_Roger.Hecq@UBS.COM31_Wed, 21 Jul 2010 09:25:27 -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_1A700EEF49343148A08879B1E3CA5BA615A396BA@NSTMC101PEX1.ubsw.net 35868 234 74_Re: Betr: Re: [DB2-L] Betr: Re: [DB2-L] Betr: [DB2-L] archive log size....15_Smartcurl Zhang19_smartcurl@GMAIL.COM31_Wed, 21 Jul 2010 22:15:18 +08001012_ISO-8859-1 But this setting actually are taking effect.let me show you something:
*ACTIVE LOG COPY 1 DATA SETS
*
* START RBA/LRSN/TIME END RBA/LRSN/TIME DATE LTIME DATA SET
INFORMATION *
* -------------------- -------------------- -------- -----
-------------------- *
* 00BA17B70000 00BA19EF2FFF 2009.180 16:56
DSN=DSNDU01L.DU11.LOGCOPY1.DS08*
* C645B71057DE C646F8EF5F9B PASSWORD=(NULL)
STATUS=TRUNCATED, REUSABLE *
* 2010.194 21:00:21.5 2010.195 21:00:23.1
*
* 00BA19EF3000 00BA1C1CFFFF 2009.180 16:56
DSN=DSNDU01L.DU11.LOGCOPY1.DS01*
* C646F8EF5F9B C6483AC86BBC PASSWORD=(NULL)
STATUS=TRUNCATED, REUSABLE *
* 2010.195 21:00:23.1 2010.196 21:00:18.5
*
* 00BA1C1D0000 00BA1E4DBFFF 2009.180 16:56
DSN=DSNDU01L.DU11.LOGCOPY1.DS02*
* C6483AC86BBC C6497CA1C1A9 PASSWORD=(NULL)
STATUS=TRUNCATED, REUSABLE *
* 2010.196 21:00:18.5 2010.197 21:00:14.2
*
* 00BA1E4DC000 00BA207E0FFF 2009.180 16:56
DSN=DSNDU01L.DU11.LOGCOPY1.DS03*
* [...]60_AANLkTinuNbVjnXbg21VZdFsyYLqMSCt=tcc0EQHkVXyR@mail.gmail.com 36103 88 74_Re: Betr: Re: [DB2-L] Betr: Re: [DB2-L] Betr: [DB2-L] archive log size....15_Smartcurl Zhang19_smartcurl@GMAIL.COM31_Wed, 21 Jul 2010 22:32:22 +0800659_ISO-8859-1 OK,Let me change my question : if I want change archive frequence from 24
hour to 12 hour for a existing DB2,what should I do?

On Wed, Jul 21, 2010 at 7:07 PM, Adam Baldwin wrote:

> Archive Log Frequency (Panel DSNTIPL) is only used by the install clist to
> calculate archive log size. The only place that you'll be able to see this
> variable is in your DSNTID in variable NUMHRARC.
>
> Regards, Adam
>
> _____________________________________________________________________
> * IDUG Australasia * Sydney, Australia * 1-3 September 2010 *
> http://IDUG.ORG/AU *
> * If you [...]60_AANLkTikT2NDJHTq8hNUTjPHtEFCKQxRDs1csMB+L2c9c@mail.gmail.com 36192 464 74_Re: Betr: Re: [DB2-L] Betr: Re: [DB2-L] Betr: [DB2-L] archive log size....11_Mike Turner19_mike.turner@GMX.COM31_Wed, 21 Jul 2010 15:40:18 +0100724_iso-8859-1 The status shows TRUNCATED,REUSABLE. The TRUNCATED part is almost certainly the result of an -ARCHIVE LOG command. Do you have automation issuing -ARCHIVE LOG every 24 hours?

Regards
Mike Turner
Email: mike.turner@gmx.com
Tel: +44 (0)1565-873702
Web: www.michael-turner.ltd.uk
Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159 44.
----- Original Message -----
From: Smartcurl Zhang
Newsgroups: bit.listserv.db2-l
To: DB2-L@IDUGDB2-L.ORG
Sent: Wednesday, July 21, 2010 3:15 PM
Subject: Re: [DB2-L] Betr: Re: [DB2-L] Betr: Re: [DB2-L] Betr: [DB2-L] archive log size.... [...]44_12847A7CE8134CCBA4CB709507B93C47@ToshibaA300 36657 25 36_[OT] Is there an I-series Listserve?14_Larry Kintisch17_LKint@VERIZON.NET31_Wed, 21 Jul 2010 10:44:23 -0400684_us-ascii Hi Listers,

A friend who works with I-Series [AS/400] systems is learning DB2
for I-series. He asks me if there is a general Listserve for I-series
Q & A [not only about DB2/400? Any suggestions?

Thanks, Larry Kintisch

_____________________________________________________________________
* 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 best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________ [...]40_0L5W00J18WYAGR80@vms173019.mailsrvcs.net 36683 389 74_Re: Betr: Re: [DB2-L] Betr: Re: [DB2-L] Betr: [DB2-L] archive log size....15_Smartcurl Zhang19_smartcurl@GMAIL.COM31_Wed, 21 Jul 2010 22:51:44 +0800693_ISO-8859-1 ...
You are right.
I use control-m daily backup and delete archive logs from disk.
Everybody ,I am sorry.
I should pay more attention...

On Wed, Jul 21, 2010 at 10:40 PM, Mike Turner wrote:

> The status shows TRUNCATED,REUSABLE. The TRUNCATED part is almost
> certainly the result of an -ARCHIVE LOG command. Do you have automation
> issuing -ARCHIVE LOG every 24 hours?
>
> Regards
> Mike Turner
> Email: mike.turner@gmx.com
> Tel: +44 (0)1565-873702
> Web: www.michael-turner.ltd.uk
> Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered
> Office: 131 King Street, Knutsford, [...]60_AANLkTi=N+ST_RcZcJeGSy5iqpBb_7xkgkRHak4sYTv7x@mail.gmail.com 37073 52 40_Re: [OT] Is there an I-series Listserve?18_McBride, Catherine18_CMcbride@KABLE.COM31_Wed, 21 Jul 2010 09:52:52 -0500507_us-ascii Sure.

midrange-l@midrange.com

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Larry
Kintisch
Sent: Wednesday, July 21, 2010 9:44 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] [OT] Is there an I-series Listserve?

Hi Listers,

A friend who works with I-Series [AS/400] systems is learning DB2 for
I-series. He asks me if there is a general Listserve for I-series Q & A
[not only about DB2/400? Any suggestions? [...]70_615630E3C14F2B49809C366A8BFD288D057FE9BE@il-exch1.Illinois.Kable.local 37126 95 74_Re: Betr: Re: [DB2-L] Betr: Re: [DB2-L] Betr: [DB2-L] archive log size....10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 21 Jul 2010 17:08:49 +0200385_US-ASCII What about scheduling an -archive log command every 12 hours ? So you're
sure about 12-hours interval, if it matters.

Max Scarpa

'Time for DB2 recovery is directly proportional to the people around your
desk.'

> OK,Let me change my question : if I want change archive frequence
> from 24 hour to 12 hour for a existing DB2,what should I do? [...]66_OF9513C7FE.38CE2FAA-ONC1257767.005244B5-C1257767.0053332A@cesve.it 37222 89 65_Re: z/OS: Software for generating DDL for existing views/synonyms10_Sudhir Raj26_sudhir.raj@REALTIMEDBA.COM31_Wed, 21 Jul 2010 11:13:43 -0400622_UTF-8 I have used this in the past, unfortunately db2look does not generate
synonyms as required in this case.

Regards,
Sudhir Muthuraj | Principal DBA | RealTime-DBA | www.RealtimeDBA.com

On 7/21/2010 8:52 AM, Daniel Luksetich wrote:
> If you have DB2 connect you can use the DB2LOOK command to generate DDL, but not table spaces.
>
> db2look -d DSN1 -e -x -u SCHEMA -i AUTHID -w PASSWORD
>
>
> Cheers,
> Dan
>
> Daniel L Luksetich
> IBM Information Champion
> IBM Certified Database Administrator - DB2 9 for z/OS
> IBM Certified System Administrator - DB2 9 [...]32_4C470EA7.7080804@realtimedba.com 37312 51 65_Re: z/OS: Software for generating DDL for existing views/synonyms10_Sudhir Raj26_sudhir.raj@REALTIMEDBA.COM31_Wed, 21 Jul 2010 11:25:54 -0400360_UTF-8 DB2PLI8 does a good job with table-space, table,index and statistics
of-course. But I have been unsuccessful getting this to create views and
synonyms. And it's cumbersome to use this as it's tied to access path
rows in plan_table.

Please let me know if there is a way to generate views and synonyms
using DB2PLI8 sample program. [...]32_4C471182.8010208@realtimedba.com 37364 64 40_Re: [OT] Is there an I-series Listserve?11_Enrico Haak21_eh@INSOFT-SOFTWARE.DE31_Wed, 21 Jul 2010 17:26:26 +0200810_ISO-8859-1 Hi Larry,

did he already visited this site?
http://www-947.ibm.com/systems/support/i/forums/index.html

Enrico

Mit freundlichen Grüßen / Best Regards

Enrico Haak

___________________________________________________________________

InSoft Software GmbH, Derendorfer Str. 70, 40479 Düsseldorf / Germany
Tel. +49 211 44 03 166, Fax +49 211 48 80 33
Geschäftsführer: Colin Oakhill, Günther Wichmann
HRB 20486 DÜSSELDORF * UST-IDNR:DE 119253977 * www.insoft-software.de

Am 21.07.2010 16:44, schrieb Larry Kintisch:
> Hi Listers,
>
> A friend who works with I-Series [AS/400] systems is learning DB2
> for I-series. He asks me if there is a general Listserve for I-series
> Q & A [not only about DB2/400? Any suggestions?
[...]35_4C4711A2.4030208@insoft-software.de 37429 76 51_[AD] Training: Relational Design; Appl. Programming14_Larry Kintisch17_LKint@VERIZON.NET31_Wed, 21 Jul 2010 13:13:47 -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_0L5X000O93VB41E0@vms173019.mailsrvcs.net 37506 104 29_Z/OS installation of DB2 V9.112_Micheal Butz25_michealbutz@OPTONLINE.NET31_Wed, 21 Jul 2010 14:25:59 -0400506_us-ascii Is anyone familiar on Z/OS with the installation OF db2 v9.1

_____________________________________________________________________
* 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 best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________ [...]35_003301cb2902$2b159820$8140c860$@net 37611 102 24_Z/OS install of DB2 V9.112_Micheal Butz25_michealbutz@OPTONLINE.NET31_Wed, 21 Jul 2010 14:28:00 -0400498_us-ascii Is anyone familiar with the Z/OS install of DB2 V9.1

_____________________________________________________________________
* 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 best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________ [...]35_003801cb2902$72ec8e10$58c5aa30$@net 37714 232 33_Re: Z/OS installation of DB2 V9.113_Campbell, Jim26_Jim.Campbell@COURTS.WA.GOV31_Wed, 21 Jul 2010 11:52:50 -0700517_us-ascii What would you like to know, we installed DB2 V9 about a year ago and have been through several updates.

Jim Campbell
Sr. Database Administrator
360-704-4015
Jim.Campbell@courts.wa.gov

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Micheal Butz
Sent: Wednesday, July 21, 2010 11:26 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] Z/OS installation of DB2 V9.1

Is anyone familiar on Z/OS with the installation OF db2 v9.1 [...]66_2A1B28C5CC983C4F8253CF1FA1218532159A0617AC@EXCHMAIL1.courts.wa.gov 37947 25 48_real-time replication DB2 zOS to SQLServer 2005?13_Alma Belfield31_Alma.Belfield@GMACINSURANCE.COM31_Wed, 21 Jul 2010 17:43:11 -0400755_UTF-8 We're considering our options for replication from mainframe DB2 zOS V8 to SQLServer 2005 on a remote Wintel platform.
Hope to achieve near real-time, a couple of minutes lag for the insert/update/delete to flow thru to SQLServer.

Anyone have suggests as to suitable software, hopefully not too costly?

_____________________________________________________________________
* 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 best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________ [...]63_8204642556655338.WA.Alma.BelfieldGMACInsurance.com@www.idug.org