1 IDUGDB2-L.ORG /home/listserv/home/db2-l July 2010, week 5 2 588 13_Reorg Problem12_Jose Antonio17_jamorcillo@CAM.ES31_Thu, 29 Jul 2010 09:09:55 +0200491_iso-8859-1 Hi all!

I'm running a REORG utility against a big table but an error arraises and I don't have any idea! Any help!?!?! Please!

1DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = UMDC040.DBCAMRET

DSNU1044I DSNUGTIS - PROCESSING SYSIN AS EBCDIC

0DSNU050I DSNUGUTC - TEMPLATE COPIA UNIT VTSA DSN 'DBREUD.CF.&DB(4,1).&DB(7,2)..&SN..P&PART(3,3)..D&JDATE(3,5)

..T&TIME.'

DSNU1035I DSNUJTDR - TEMPLATE STATEMENT PROCESSED SUCCESSFULLY [...]59_78E5EB2B7326D240BBB670236211F9D301667360@SRVMAIL3.redcam.es 591 236 74_z/OS db2 v9 datasharing two members; strange consequence of PCTFREE change37_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ31_Thu, 29 Jul 2010 09:41:38 +0200489_iso-8859-2 Hello ,

I have a very simple program which reads account number (3M accounts) from table A and for each account, it inserts a row into another table B. Table B has 50M rows, three columns, dec(10),date,dec(11), all columns in unique index. It takes about 5 minutes when PCTFREE is 0 for TS B and IX B and 2 minutes when PCTFREE is 12 for TS B and 15 for IX B (slight RO activity on another member in both cases, many attempts done). So far intelligible even for me. [...]58_0675446363E09A4EBE4CCF8D3AD7814F045480E4@MAIL1.cen.csin.cz 828 33 167_AUTO: Norbert Jenninger/Germany/IBM is out of the office (short vacation) w/o access to my mailbox, I will be back in the office Monday August 2 (returning 08/02/2010)17_Norbert Jenninger14_JEN@DE.IBM.COM31_Thu, 29 Jul 2010 10:06:21 +0200383_US-ASCII I am out of the office until 08/02/2010.

I am out of office with no access to my mailbox and will be back in the
office Monday Aug 2nd

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

This is the only notification you will receive while this person is away. [...]68_OFE8C67FDA.40A98D20-ONC125776F.002C8711-C125776F.002C8712@de.ibm.com 862 628 17_Re: Reorg Problem16_Gregory Palgrave28_gpalgrave@STAFF.IINET.NET.AU31_Thu, 29 Jul 2010 17:22:10 +0800520_iso-8859-1 It looks to be a problem with the RID pool sizing - did you see any of the DSNT500I messages in the DB2 MSTR syslog?
00C900BC
Explanation
The statement depleted the record identifier (RID) storage available.
Problem determination
SQL return code -904 and/or message DSNT500I is issued. For more information, refer to the explanation of message DSNT500I.
You may need to increase the size of the RID pool - I've long forgotten how to do that...but it will be in the manuals somewhere... [...]73_8522673947DF7D4394C962BFE441CD27050A84431C@PER-EXCHMBX.win2k.iinet.net.au 1491 26 17_Re: Reorg Problem12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Thu, 29 Jul 2010 05:47:23 -0400745_UTF-8 Hi Jose Antonio. If you've got RID Pool problems you need to change the value of MAXRBLK in your ZPARMs. Parameter MAXRBLK is one that you can change dynamically.

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! *
_____________________________________________________________________
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!
_____________________________________________________________________ [...]54_9036941141106800.WA.adambaldwines.ibm.com@www.idug.org 1518 40 78_Re: z/OS db2 v9 datasharing two members; strange consequence of PCTFREE change12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Thu, 29 Jul 2010 06:20:23 -0400337_UTF-8 Hi Dusan.

You say "if I add an update of table A for every 10th row...". Can you provide more details of this update. Is the index on Table A on Account Number? Have you repeated your tests varios times with the same results?

Changing the free space on TSB and IXB aren't going to effect the updates on table A. [...]54_3533580374096487.WA.adambaldwines.ibm.com@www.idug.org 1559 256 59_Problem with Partitioned Tablespaces and Cluster Percentage11_Forbis Mike22_James.M.Forbis@IRS.GOV31_Thu, 29 Jul 2010 07:06:14 -0400554_us-ascii This past year we changed a couple of table definitions to have the
partitioned values inside of the table instead of a separate index
space. We are also using limit keys to control the amount of data in
each partition to be consistent. We do reorgs as needed. We have a lot
of tables and this seemed to work very well until this year. We have 3
tables that I can not get the cluster ratio high enough for them to be
clustered. In the past using the traditional separate index - this was
never a problem. We have tried [...]69_0F233E85C02C234296DF820256A74899098951CC@NCT0010CP3MB01.ds.irsnet.gov 1816 316 78_Re: z/OS db2 v9 datasharing two members; strange consequence of PCTFREE change37_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ31_Thu, 29 Jul 2010 14:34:36 +0200585_iso-8859-2 Hi Adam, thanks for your reply.

I agree with you that "Changing the free space on TSB and IXB aren't going to effect the updates o n table A." but the experimentations seem to show something else...

The program does the same updates regardless of "free space"/"no free space" and actually set coll2=coll2, not in index (so needless update)
All four versions of described program was run at least three times each. And there was always necessary to run reorg on TS B to create / delete "free space". (test time could well be skewed by the fact that the [...]58_0675446363E09A4EBE4CCF8D3AD7814F045480E9@MAIL1.cen.csin.cz 2133 419 63_Re: Problem with Partitioned Tablespaces and Cluster Percentage13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Thu, 29 Jul 2010 08:38:10 -0400514_us-ascii Hi Mike

Silly question (perhaps) but your CLUSTERING index IS on the same key that you are partitioning on isn't it?

Because with table based partitioning you can "cluster" on any key you like, BUT if you pick something that is NOT the partitioning key, that is likely to show strange clusterratio figures - because although the rows can be clustered IN a partition they have to be PARTITIONED by the partitioning key first, so across ALL partitions 100% clustering may not be possible [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8FDBB13@MAILR004.mail.lan 2553 46 24_Re: Removing runstats ??13_Terry Purcell18_tpurcel@US.IBM.COM31_Thu, 29 Jul 2010 09:28:54 -0400605_UTF-8 Hello Dee,

There is no capability in RUNSTATS to remove COLGROUP (or HISTOGRAM) statistics - although there is a way to remove FREQVAL stats (using COUNT 0) - although your question is for COLGROUP.

You need to delete these directly from SYSCOLDIST (and potentially SYSCOLDISTSTATS). You are looking for TBOWNER = ?, TBNAME = ?, TYPE='C', and COLGROUPCOLNO related to those 4 columns (and the subset in the sequence of the COLGROUP). Note this is a concatenated list of the COLNO values in HEX - you can find the COLNO values from SYSCOLUMNS. For example, if the COLNOs for those [...]50_5451152918242104.WA.tpurcelus.ibm.com@www.idug.org 2600 268 78_Re: z/OS db2 v9 datasharing two members; strange consequence of PCTFREE change35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 29 Jul 2010 09:33:42 -0400580_iso-8859-2 Stopping the objects and restarting them before a test will invalidate any pages in the BP.
Then you are sure to have a better comparison.

Regards,
Joel

Joel Goldstein
Responsive Systems
IBM Gold Consultant
Buffer Pool Tool for DB2, the worldwide industry standard
Performance software that works...... Predicts IO Rate !!
Predicts Group Buffer Pool performance too
www.responsivesystems.com

Buffer Pool Tool for DB2 on www.LinkedIn.com
Watch the 3-Minute Buffer Pool Tool Movie at: www.responsivesystems.com/Movie1 [...]46_E1FA6E654D4F4D39BC7A9BE699DD2707@DellNotebook3 2869 541 63_Re: Problem with Partitioned Tablespaces and Cluster Percentage11_Forbis Mike22_James.M.Forbis@IRS.GOV31_Thu, 29 Jul 2010 10:05:41 -0400512_us-ascii Phil;

Thanks for the suggestion. I did verify that when we create the
table we have a limit key of decimal (17,0) defined. We later say in
the table definition to partition the table by the limit key and give
values for each partition. We have a primary key defined on the table
that is the same as the the unique clustering index. Not sure why but
for these 3 tables - reorg just does not work the same as it does for
all the other partitioned and non-partitioned tables. [...]69_0F233E85C02C234296DF820256A74899098951D2@NCT0010CP3MB01.ds.irsnet.gov 3411 631 71_AW: [DB2-L] Problem with Partitioned Tablespaces and Cluster Percentage35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Thu, 29 Jul 2010 16:16:53 +0200440_iso-8859-1 Mike

I am not sure, if you do not miss the point. Did your alter your tablespace, that it got table controlled or did you drop and recreate it specfying limitkeys (ending values) in the table definition?

When you have a table controlled partitioned tablespace, you still can have NPIs, one of them is the primary key, one of them is the clustering index, but the partitioning colunms are completely different. [...]43_DB2-L%201007291017315799.289A@IDUGDB2-L.ORG 4043 220 25_Re: DB2 V9 NFM, z/os 1.107_Ed Long19_rdhm99a@PRODIGY.NET31_Thu, 29 Jul 2010 07:27:33 -0700371_iso-8859-1 There was a web conference on Data Studio on Tuesday.

The highlight, besides the recurring technical difficulties, was the 'Data Studio now can do about 70% of what Control Center does" comment from one of the presenter's.

Since Control Center pales compared to Toad, EM, or the MicroSoft tools, that comment constitutes self incrimination. [...]42_907540.3284.qm@web80206.mail.mud.yahoo.com 4264 42 63_Re: Problem with Partitioned Tablespaces and Cluster Percentage13_Terry Purcell18_tpurcel@US.IBM.COM31_Thu, 29 Jul 2010 10:39:32 -0400380_UTF-8 Mike,

I believe what Phil is trying to say is that if you partition a table by "YEAR", but then you CLUSTER by an index on ACCT_NO, then you may see something like this:

INDEX1 (ACCTNO) NPI - poor clusterratio (index is defined as CLUSTER)
INDEX2 (ACCTNO) DPSI - good clusterratio
INDEX3 (YEAR, ACCTNO) either partitioned or not - good clusterratio [...]50_1460376713523558.WA.tpurcelus.ibm.com@www.idug.org 4307 785 75_Re: AW: [DB2-L] Problem with Partitioned Tablespaces and Cluster Percentage11_Forbis Mike22_James.M.Forbis@IRS.GOV31_Thu, 29 Jul 2010 10:47:06 -0400565_iso-8859-1 The table, tablespace and index were dropped and then created with the table controlled partitioning. The data was loaded back into the table. I have unloaded and reloaded this data as well as done individual reorgs on the partitions and a reorg on the whole table space with no improvement. The primary key and the clustering index both have the same columns and are in the same order. We have done the same process for all of our other partitioned tablespaces and they seem to be working ok. I mentioned the limit key only to show how the data is [...]69_0F233E85C02C234296DF820256A74899098951D4@NCT0010CP3MB01.ds.irsnet.gov 5093 822 83_AW: [DB2-L] AW: [DB2-L] Problem with Partitioned Tablespaces and Cluster Percentage35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Thu, 29 Jul 2010 17:46:49 +0200876_iso-8859-1 Mike

Can you provide the definitions of one of your trouble tablespace, table and indexes?

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 Forbis Mike
Gesendet: Donnerstag, 29. Juli 2010 16:47
An: DB2-L@IDUGDB2-L.ORG
Betreff: Re: [DB2-L] AW: [DB2-L] Problem with Partitioned Tablespaces and Cluster Percentage [...]43_DB2-L%201007291147002128.28A0@IDUGDB2-L.ORG 5916 1224 87_Re: AW: [DB2-L] AW: [DB2-L] Problem with Partitioned Tablespaces and Cluster Percentage11_Forbis Mike22_James.M.Forbis@IRS.GOV31_Thu, 29 Jul 2010 12:07:01 -0400782_iso-8859-1 Here is the tablespace, table and index definition. I deleted some of the table columns to help shorten the email.

CREATE TABLESPACE TSCF34
IN TRDB2009
BUFFERPOOL BP3
DSSIZE 16G
NUMPARTS 30
LOCKSIZE PAGE
CLOSE YES COMPRESS YES
USING STOGROUP SMSMAN
PRIQTY 792000
SECQTY 360000
ERASE NO
PCTFREE 0
FREEPAGE 0;

CREATE TABLE TRDB2009.XXN_VALIDATION
(TSCF34_09_LIMITKEY DECIMAL(17, 0) NOT NULL GENERATED ALWAYS
AS IDENTITY
(START WITH 1, INCREMENT BY 1, CACHE 2000, NO CYCLE,
NO ORDER, MAXVALUE 99999999999999999, MINVALUE 1),
FK_DIN CHAR(19) NOT NULL,
FK_FORM_TYPE CHAR(10) NOT NULL,
FK_FORM_SEQ_NUM INTEGER NOT NULL,
VER_SEQ_NUM SMALLINT NOT NULL WITH DEFAULT,
TYPE_SEQ_NUM SMALLINT NOT NULL [...]69_0F233E85C02C234296DF820256A74899098951DC@NCT0010CP3MB01.ds.irsnet.gov 7141 68 22_Re: Recovery Scenarios0_22_DB2information@AOL.COM29_Thu, 29 Jul 2010 12:17:52 EDT382_US-ASCII Anil,
You may wish to review _www.recoveryknowledge.com_
(http://www.recoveryknowledge.com) and request a free trial so you can get a complete feel for DB2
recovery scenarios in z/OS. If you decide not to purchase any of the
three products you will have a good idea of what must take place than you can
write your in-house code to automate the process. [...]31_74593.25c7ce8b.398303b0@aol.com 7210 381 75_Re: AW: [DB2-L] Problem with Partitioned Tablespaces and Cluster Percentage10_Dave Nance16_dav1mo@YAHOO.COM31_Thu, 29 Jul 2010 09:42:53 -0700564_utf-8 Hi Mike,     Its been a few years. Hope everything is going well up in Martinsburg. Please tell everyone I said "Hi".    I see that you didn't really answer Phil's question below. Is the Limitkey the same as your primary key/unique/clustering index? Or is this one of those tables where you have the Limitkey, that is not used by the app and is just used for partitioning? It might help us if you supplied some of the DDL, not the whole table, but the partitioning key and part definition for first couple of parts, primary key and for the index. [...]43_592326.76629.qm@web57803.mail.re3.yahoo.com 7592 1752 78_Re: z/OS db2 v9 datasharing two members; strange consequence of PCTFREE change37_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ31_Thu, 29 Jul 2010 19:07:28 +0200526_iso-8859-2 Hi Joel,
thanks for your suggestion, but no much difference. I have added some statistics from the thread of program (from DB.PM)
no "free space" "free space"
Class 1 Class 2 Class 1 Class 2
In Appl In DB2 Outside DB2 In Appl In DB2 Outside DB2
Elapsed time . . . . . . : 45:18.991824 45:07.540207 11.451618 Elapsed time . . . . . . : 2:49.912766 2:40.048986 9.863779
CPU time . . . . . . . . : 3:13.458252 3:03.044480 10.413772 CPU time . . . . . . . . : 58.466884 49.878817 8.588067
TCB [...]58_0675446363E09A4EBE4CCF8D3AD7814F045480EA@MAIL1.cen.csin.cz 9345 880 75_Re: AW: [DB2-L] Problem with Partitioned Tablespaces and Cluster Percentage11_Forbis Mike22_James.M.Forbis@IRS.GOV31_Thu, 29 Jul 2010 13:16:58 -0400532_iso-8859-1 Great to hear from you. Yes everything is still going. I supplied the DDL in my last message. Yes this is one of those tables that use the limit key just for partitioning the data as it comes in to partitions based on a ending value. Otherwise we would end up with partitions with 2 records and others with 2million records. I have the primary key and a unique index that are defined with the same fields. In the table we have what we used to have as the limit key index with the partition numbers and end values. I [...]69_0F233E85C02C234296DF820256A74899098951DF@NCT0010CP3MB01.ds.irsnet.gov 10226 495 75_Re: AW: [DB2-L] Problem with Partitioned Tablespaces and Cluster Percentage10_Dave Nance16_dav1mo@YAHOO.COM31_Thu, 29 Jul 2010 11:19:08 -0700523_utf-8    As Terry and a few others have already mentioned, you are not getting the clusterratio you want, because you may have 1 record in partition 1 and the next could be in partition 20 or vice versa. This is one of the items we talked about years ago, when you all first went to V8, that you could get rid of those indexes that only existed to define your partitions.     The thing I am not sure of and maybe someone else could answer is using the keyword CLUSTER on an NPI. I think, DB2 will do what it can [...]43_890232.26789.qm@web57802.mail.re3.yahoo.com 10722 1996 78_Re: z/OS db2 v9 datasharing two members; strange consequence of PCTFREE change35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 29 Jul 2010 15:02:48 -0400434_iso-8859-2 Hi Dusan,

That simply leveled the playing field.
The huge difference in elapsed time is more than 1/2 hour of Synch IO - look at your class 3 times.
The number of synch I/O is 21x times larger with no free space.
Also look at the number of locking events.

It appears that DB2 has to perform a huge number of synch I/O to get pages for your inserts when pctfree=0.
What is your row length.... [...]46_D8C2D19D88D94F178DAB4B9365965B08@DellNotebook3 12719 1087 75_Re: AW: [DB2-L] Problem with Partitioned Tablespaces and Cluster Percentage11_Forbis Mike22_James.M.Forbis@IRS.GOV31_Thu, 29 Jul 2010 16:18:16 -0400551_iso-8859-1 I am not following your logic. The index is for the primary key that is used by the application and not for the partitioning The partitioning is handled by the limit key and the values defined in the table. Based on your recommendation (Dave Nance) years ago we did implement the partitioning by doing away with that separate index we had for the limit key. By putting the the partitioning values in the table - we essentially took them out of the clustering index and made a NPI out of it. I can't explain why but this works fine for [...]69_0F233E85C02C234296DF820256A74899098951E5@NCT0010CP3MB01.ds.irsnet.gov 13807 33 45_z/OS DB2v9 CM - REORG with LEAFNEAR & LEARFAR9_Sam Baugh24_sam.baugh@MOTION-IND.COM31_Thu, 29 Jul 2010 17:44:48 -0400582_UTF-8 I have reorg'd a clustering index several times in a test system and the index statistics table always shows that I have 1331 "leaf near" pages. A non clustering index on the same table shows 199 "leaf near" pages after reorg, and these values remain consistent regardless of how many times i have run the reorg, and even after running a reorg on the tablespace. The LEAFFAR value went to zero for both of the indexes. Shouldn't the LEARNEAR value go to zero also? I'm assuming that LEAFNEAR and LEAFFAR values indicate that index page splits have occurred. REORG's were [...]55_1938149639331854.WA.sam.baughmotionind.com@www.idug.org 13841 37 26_z/os CM Runstats V8 and V913_Wayne Stevens29_wayne.stevens@DHR.ALABAMA.GOV31_Thu, 29 Jul 2010 22:39:41 -0400956_UTF-8 Currently we run index runstats in v8 with the following options:
RUNSTATS INDEX(PSINCE.XC0031,PSINCE.XC0032,PSINCE.XC0033) SHRLEVEL CHANGE

Currently we run index runstats in v8 with the following options:
RUNSTATS TABLESPACE PSDBCE.PSTSC003
TABLE(PSTBCE.CEACHSTT)
COLUMN(ALL)
INDEX(ALL)
KEYCARD
SHRLEVEL CHANGE

Should I change/add any options to V9 runstats?

_____________________________________________________________________
* 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!
_____________________________________________________________________ [...]61_1464533847878213.WA.wayne.stevensdhr.alabama.gov@www.idug.org 13879 448 30_Re: z/os CM Runstats V8 and V911_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 30 Jul 2010 06:53:09 +0200581_ISO-8859-1 yes! TS runstats should look like

RUNSTATS TABLESPACE LEUDB.MIVPS15 TABLE(ALL) INDEX(ALL KEYCARD
FREQVAL NUMCOLS 0001 COUNT 10 MOST
FREQVAL NUMCOLS 0002 COUNT 10 MOST
FREQVAL NUMCOLS 0003 COUNT 10 MOST
FREQVAL NUMCOLS 0004 COUNT 10 MOST
FREQVAL NUMCOLS 0005 COUNT 10 MOST
FREQVAL NUMCOLS 0006 COUNT 10 MOST
FREQVAL NUMCOLS 0007 COUNT 10 MOST
FREQVAL NUMCOLS 0008 COUNT 10 MOST
FREQVAL NUMCOLS 0009 COUNT 10 MOST
FREQVAL NUMCOLS 0010 COUNT 10 MOST)
SHRLEVEL CHANGE
UPDATE ALL REPORT NO
SORTDEVT SYSALLDA
SORTNUM 0004 [...]64_OFFA7BF844.C2E2C4CD-ONC1257770.001A290A-C1257770.001AD6DA@seg.de 14328 225 25_Re: DB2 V9 NFM, z/os 1.1011_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 30 Jul 2010 06:55:29 +0200381_ISO-8859-1 There are a few RUNSTATs bugs out there that also cause this. I track a
whole bunch of RUNSTATS APARs and with SEGs
StatisticsHealthCheck you can also see the "bad data" in the Catalog. Once
the full Runstats has been run if the "bad data" is
still there then I check the APARs and normally see one that comes near or
directly fixes "the problem"... [...]64_OF24C6CBD5.BC6FDE4A-ONC1257770.001AE407-C1257770.001B0DAF@seg.de 14554 25 78_Re: z/OS db2 v9 datasharing two members; strange consequence of PCTFREE change12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Fri, 30 Jul 2010 02:38:16 -0400646_UTF-8 Hi Dusan. I see that Joel has already asked you to post the row length of table B. Additionaly, how many pages are in the tablespace and index prior to running the test? Are your inserts random or sequential?

Regards, Adam

_____________________________________________________________________
* 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
_____________________________________________________________________ [...]54_5498403382821863.WA.adambaldwines.ibm.com@www.idug.org 14580 1289 95_AW: [DB2-L] AW: [DB2-L] AW: [DB2-L] Problem with Partitioned Tablespaces and Cluster Percentage35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Fri, 30 Jul 2010 08:51:43 +0200343_iso-8859-1 Mike

That's what Terry, Phil and the others mean. You partition by column TSCF34_09_LIMITKEY , but your clustering index starts with column FK_DIN. So per partition you will get 100% clusterratio, but over the entire tablespace, you won't , because you partition by a different column (That's what Terry tried to show). [...]43_DB2-L%201007300251552841.28B9@IDUGDB2-L.ORG 15870 2450 78_Re: z/OS db2 v9 datasharing two members; strange consequence of PCTFREE change37_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ31_Fri, 30 Jul 2010 11:06:49 +0200296_iso-8859-2 Hi Joel,
thanks for your answer. 99% of synch I/O are on IX of table B - (BP3). The row length (insert) is 24 (not sure it matters much).
Updates to table A really doesn't matter, but essential is if there is inter-DB2 read/write interest (IDRWI) - at least I think so now. [...]58_0675446363E09A4EBE4CCF8D3AD7814F045480EB@MAIL1.cen.csin.cz 18321 87 78_Re: z/OS db2 v9 datasharing two members; strange consequence of PCTFREE change37_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ31_Fri, 30 Jul 2010 11:23:29 +0200599_iso-8859-2 Hi Adam,
Length of row is 24B. However from statistics I posted before, it seems to me, that the problem (with pctfree 0/0) is mostly on IX and Data Sharing Locking.

# of rows before inserting =46686375
# of inserted rows = 2979192
The inserts are strictly sequential

Regards,
dusan

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Adam Baldwin
Sent: Friday, July 30, 2010 8:38 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] z/OS db2 v9 datasharing two members; strange consequence of PCTFREE change [...]58_0675446363E09A4EBE4CCF8D3AD7814F045480EC@MAIL1.cen.csin.cz 18409 37 78_Re: z/OS db2 v9 datasharing two members; strange consequence of PCTFREE change12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Fri, 30 Jul 2010 06:51:15 -0400557_UTF-8 Hi Dusan. When you say that the inserts are sequential, do you mean that you're always inserting at the end of the table or that the inserts are in clustering sequence?

Inserts into the middle of the key range (if you're in V9 NFM) can benefit from asymmetric page splits. With no freespace you're probably suffering from a lot of index page splits. Also, in datasharing, the page splits have the overhead of two physical log writes.
Reducing page splits will defnitely help. What size page does the index have? Bigger could be better. [...]54_1815152060548200.WA.adambaldwines.ibm.com@www.idug.org 18447 1608 75_Re: AW: [DB2-L] Problem with Partitioned Tablespaces and Cluster Percentage13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Fri, 30 Jul 2010 07:08:10 -0400544_iso-8859-1 AhA

As others have already pointed out - your CLUSTERING index is on
(FK_DIN ASC,
FK_FORM_TYPE ASC,
FK_FORM_SEQ_NUM ASC,
VER_SEQ_NUM ASC,
TYPE_SEQ_NUM ASC,
SEQ_NUM ASC)

But you are partitioning on TSCF34_09_LIMITKEY

SO, DB2 will try and maintain clustering sequence (by FK_DIN, FK_FORM_TYPE, FK_FORM_SEQ_NUM, VER_SEQ_NUM, TYPE_SEQ_NUM and SEQ_NUM) WITHIN EACH PARTITION, but cannot maintain clustering across THE WHOLE TABLE as each partition is defined by the value in TSCF34_09_LIMITKEY [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8FDBB8E@MAILR004.mail.lan 20056 601 75_Re: AW: [DB2-L] Problem with Partitioned Tablespaces and Cluster Percentage10_Dave Nance16_dav1mo@YAHOO.COM31_Fri, 30 Jul 2010 05:31:44 -0700553_utf-8 Mike,    I did give you the reasonable explanation as did a few others on the list here. You are not going to get a higher cluster ratio unless you made this into a DPSI, which, again, you DO NOT want to do (explained in yesterday's mail). The reason your clusterratio is low on the index in question is the same reason it was low prior to making this a table controlled partitioned table. This NPI, even though it is defined as clustering, is going to have rows in many different partitions. As I explained yesterday, the index could have [...]43_260232.92467.qm@web57806.mail.re3.yahoo.com 20658 98 78_Re: z/OS db2 v9 datasharing two members; strange consequence of PCTFREE change37_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ31_Fri, 30 Jul 2010 15:06:04 +0200549_iso-8859-2 Hi Adam,
I was not accurate enough. The inserts goes in ascending order but not at the end of the table. Index is account, month, value and program inserts the rows for accounts which are in the table but the values for the last month (so pctfree is needed). Performance with non-zero pctfree is good enough and I think that load leads to (at least short time) inconsistency.
The main reason for mine inquiry was the surprisingly great difference of execution times with/without "free space".
Thanks for your help,
dusan [...]58_0675446363E09A4EBE4CCF8D3AD7814F045480F0@MAIL1.cen.csin.cz 20757 247 30_[AD] IDUG EMEA and Schnitzels!13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Fri, 30 Jul 2010 10:01:29 -0400476_iso-8859-1 Hi all

Just a quick Friday diversion

If you are planning to attend IDUG EMEA in November, don't forget to leave time for a visit to Figlmüller restaurant (http://www.figlmueller.at/index.php)

To quote their website "Right from the start, the Figlmüller schnitzel was what it still is today: a bit bigger, a bit thinner and a bit crisper than all the others that call themselves a schnitzel. And, that is why it is also a bit more popular" [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8FDBBB7@MAILR004.mail.lan 21005 374 34_Re: [AD] IDUG EMEA and Schnitzels!10_Roger Hecq18_Roger.Hecq@UBS.COM31_Fri, 30 Jul 2010 11:15:46 -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_1A700EEF49343148A08879B1E3CA5BA615B8340E@NSTMC101PEX1.ubsw.net 21380 381 34_Re: [AD] IDUG EMEA and Schnitzels!35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Fri, 30 Jul 2010 11:22:13 -0400438_iso-8859-1 I ate there back in '02, the schnitzel was hanging off the plate a few inches all around.
One schnitzel is enough for 2 or more normal people.

Joel

Joel Goldstein
Responsive Systems
IBM Gold Consultant
Buffer Pool Tool for DB2, the worldwide industry standard
Performance software that works...... Predicts IO Rate !!
Predicts Group Buffer Pool performance too
www.responsivesystems.com [...]46_F47F9BA53CF54EA9A1CD28173BF12126@DellNotebook3 21762 663 34_Re: [AD] IDUG EMEA and Schnitzels!13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Fri, 30 Jul 2010 13:01:56 -0400481_iso-8859-1 Georg Peter just said the same to me

My plan is usually "skip lunch THEN PIG OUT"!

And you don't have to eat fast after all
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 [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8FDBBE7@MAILR004.mail.lan 22426 39 33_LEFT function and VARCHAR columns13_Donna Domovic28_Donna.Domovic@EXELONCORP.COM31_Fri, 30 Jul 2010 14:11:54 -0400564_UTF-8 We've run in to a strange situation and was wondering if anyone else has seen this behavior or if I'm misinterpreting the DB2 SQL v8 manual.

We executed the query below expecting the GRANTOR column to be displayed as an 8 character column instead of 128 characters. Unfortunately, when the query was run, the column was still displayed as 128 characters. We were able to get the desired results by using the SUBSTR function instead of the LEFT function but I'm curious why LEFT didn't work. Shouldn't LEFT have resulted in an 8 character column? [...]60_4407561804661287.WA.Donna.Domovicexeloncorp.com@www.idug.org 22466 113 37_Re: LEFT function and VARCHAR columns14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 30 Jul 2010 14:35:25 -04001070_iso-8859-1 Donna,
I have reproduced your results, see below, in SPUFI and QMF. I recommend using the CHAR function instead; the difference in QMF is shown at bottom.
--Phil

---------+---------+---------+---------+---------+---------+
SELECT LENGTH(A), A, '**' FROM
(SELECT LEFT('ABCDEFGHIJKLMNOP',8) AS A
FROM SYSIBM.SYSDUMMY1
) T1
;
---------+---------+---------+---------+---------+---------+
A
---------+---------+---------+---------+---------+---------+
8 ABCDEFGH **
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+
SELECT LENGTH(A), A, '**' FROM
(SELECT LEFT(CAST('ABCDEFGHIJKLMNOP' AS VARCHAR(16)),8) AS A
FROM SYSIBM.SYSDUMMY1
) T1
;
---------+---------+---------+---------+---------+---------+
A
---------+---------+---------+---------+---------+---------+
8 ABCDEFGH **
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE [...]64_1BA00CBB414DA34AA25ED82F4647CDE2036225E446@MAIL02.fisalan.nycnet 22580 84 37_Re: LEFT function and VARCHAR columns10_Roger Hecq18_Roger.Hecq@UBS.COM31_Fri, 30 Jul 2010 15:04:21 -0400440_iso-8859-1 It certainly looks like it should have worked. Try CAST(GRANTOR AS CHAR (8)) AS GRANTOR. That is what we do and it works.

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Donna Domovic
Sent: Friday, July 30, 2010 2:12 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] LEFT function and VARCHAR columns [...]62_1A700EEF49343148A08879B1E3CA5BA615B83511@NSTMC101PEX1.ubsw.net 22665 121 37_Re: LEFT function and VARCHAR columns14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 30 Jul 2010 15:29:36 -0400862_iso-8859-1 Roger's suggestion works:

---------+---------+---------+---------+---------+---------+-
SELECT LENGTH(A), A, '**', CAST(A AS CHAR(8)) AS B, '**'
FROM
(SELECT LEFT(CAST('ABCDEFGHIJKLMNOP' AS VARCHAR(16)),8) AS A
FROM SYSIBM.SYSDUMMY1
) T1
;
---------+---------+---------+---------+---------+---------+-
A B
---------+---------+---------+---------+---------+---------+-
8 ABCDEFGH ** ABCDEFGH **
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+-

--Phil

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roger Hecq
Sent: Friday, July 30, 2010 3:04 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] LEFT function and VARCHAR columns [...]64_1BA00CBB414DA34AA25ED82F4647CDE2036225E447@MAIL02.fisalan.nycnet 22787 142 37_Re: LEFT function and VARCHAR columns9_Sam Baugh24_Sam.Baugh@MOTION-IND.COM31_Fri, 30 Jul 2010 14:40:51 -0500968_iso-8859-1 Or just CHAR(GRANTOR,8). Apparently, you must change the type of the column. Same behavior is in DB2v9 z/OS.

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil
Sent: Friday, July 30, 2010 2:30 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] LEFT function and VARCHAR columns

Roger's suggestion works:

---------+---------+---------+---------+---------+---------+-
SELECT LENGTH(A), A, '**', CAST(A AS CHAR(8)) AS B, '**'
FROM
(SELECT LEFT(CAST('ABCDEFGHIJKLMNOP' AS VARCHAR(16)),8) AS A
FROM SYSIBM.SYSDUMMY1
) T1
;
---------+---------+---------+---------+---------+---------+-
A B
---------+---------+---------+---------+---------+---------+-
8 ABCDEFGH ** ABCDEFGH **
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+- [...]73_1A20EF32A1A1CA44B36AF5D69203972216463D62E3@MIHQPEXMB1.corp.motion-ind.com 22930 108 70_Project Planning Insights for Enterprise Datawarehouse (EDW) Migration9_Ramu Iyer18_ramu_asm@YAHOO.COM31_Fri, 30 Jul 2010 12:53:30 -0700598_utf-8 I am embarking on a project to upgrade and migrate an enterprise data warehouse (EDW) from data center A to data center B. The EDW has organically evolved in data center A over a span of a decade. There are also various system dependencies (push and pull) on EDW. I am looking for twin sources of knowledge: a) Data center migration project planning b) EDW migration project planning From a big picture point of view, the goal is to "forklift" the EDW from A to B (while upgrading the hardware/software infrastructure, database and the application layers).The business cannot afford any [...]43_311540.29517.qm@web65613.mail.ac4.yahoo.com 23039 130 37_Re: LEFT function and VARCHAR columns14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 30 Jul 2010 15:29:36 -0400862_iso-8859-1 Roger's suggestion works:

---------+---------+---------+---------+---------+---------+-
SELECT LENGTH(A), A, '**', CAST(A AS CHAR(8)) AS B, '**'
FROM
(SELECT LEFT(CAST('ABCDEFGHIJKLMNOP' AS VARCHAR(16)),8) AS A
FROM SYSIBM.SYSDUMMY1
) T1
;
---------+---------+---------+---------+---------+---------+-
A B
---------+---------+---------+---------+---------+---------+-
8 ABCDEFGH ** ABCDEFGH **
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+-

--Phil

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roger Hecq
Sent: Friday, July 30, 2010 3:04 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] LEFT function and VARCHAR columns [...]64_1BA00CBB414DA34AA25ED82F4647CDE2036225E447@MAIL02.fisalan.nycnet 23170 463 74_Re: Project Planning Insights for Enterprise Datawarehouse (EDW) Migration14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 30 Jul 2010 16:26:11 -0400576_us-ascii Um. Not to be pushy but there are distinct forums elsewhere for Teradata and Informatica, which will probably yield you more information than a DB2-specific forum. And for data center migration, you might want to look in the MVS-L mailing list (if you have IBM mainframes).
--Phil Sevetson

________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ramu Iyer
Sent: Friday, July 30, 2010 3:54 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] Project Planning Insights for Enterprise Datawarehouse (EDW) Migration [...]64_1BA00CBB414DA34AA25ED82F4647CDE2036225E449@MAIL02.fisalan.nycnet 23634 122 37_Re: LEFT function and VARCHAR columns14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 30 Jul 2010 14:35:25 -04001070_iso-8859-1 Donna,
I have reproduced your results, see below, in SPUFI and QMF. I recommend using the CHAR function instead; the difference in QMF is shown at bottom.
--Phil

---------+---------+---------+---------+---------+---------+
SELECT LENGTH(A), A, '**' FROM
(SELECT LEFT('ABCDEFGHIJKLMNOP',8) AS A
FROM SYSIBM.SYSDUMMY1
) T1
;
---------+---------+---------+---------+---------+---------+
A
---------+---------+---------+---------+---------+---------+
8 ABCDEFGH **
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+
SELECT LENGTH(A), A, '**' FROM
(SELECT LEFT(CAST('ABCDEFGHIJKLMNOP' AS VARCHAR(16)),8) AS A
FROM SYSIBM.SYSDUMMY1
) T1
;
---------+---------+---------+---------+---------+---------+
A
---------+---------+---------+---------+---------+---------+
8 ABCDEFGH **
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE [...]64_1BA00CBB414DA34AA25ED82F4647CDE2036225E446@MAIL02.fisalan.nycnet 23757 52 15_Re: COPY Status0_22_DB2information@AOL.COM29_Fri, 30 Jul 2010 19:47:57 EDT592_US-ASCII Health Check Interface for DB2 will automate this process.

Ed

_____________________________________________________________________
* 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
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv31_d72f1.4a50cad9.3984bead@aol.com