SV: [DB2-L] DB2 V7 REORG With DISCARD and no SYSREC???

Olle Brostrom

SV: [DB2-L] DB2 V7 REORG With DISCARD and no SYSREC???
Hi Adam,
If the table lack a clustering index Reorg utility require a sysrec DD card or template because of internal design.
Best regards, Olle


-----Ursprungligt meddelande-----
Från: DB2 Data Base Discussion List [mailto:[login to unmask email] För Adam Baldwin
Skickat: den 14 januari 2009 13:38
Till: [login to unmask email]
Ämne: [DB2-L] DB2 V7 REORG With DISCARD and no SYSREC???

Fellow Listers:

It it possible to run a REORG TABLESPACE SHRLEVEL REFERENCE with DISCARD but with no DISCARDDDN and no SYSREC?

In the manual it states:

NOSYSREC
Specifies that the output of sorting (if there is a clustering index and you specify SORTDATA) is the input to reloading, without using an unload data set. You can specify this option only if you specify REORG TABLESPACE, SORTDATA, SHRLEVEL REFERENCE, or SHRLEVEL NONE, and only if you do not specify UNLOAD PAUSE or UNLOAD ONLY.

My reorg is SHRLEVEL REFERENCE, I have specified NOSYSREC and I do not have UNLOAD PAUSE or UNLOAD ONLY.

The utility abends with:

DSNU047I DSNURULD - A REQUIRED DD CARD OR TEMPLATE IS MISSING.
NAME=SYSREC

I haven't specified a DISCARD dataset as I'm not interested in keeping the discarded rows.

Why is the SYSREC necessary? I can't see why DB2 would need to unload the entire tablespace to a file in order to handle the discard processing.

My SYSIN for the reorg is as follows:

TEMPLATE DD1
DSN('&SS.UD.C&IC..&DB..&TS..D&DT..T&TI.')
DISP (NEW,CATLG,DELETE)
UNIT VTS
VOLCNT 99
STACK YES
REORG TABLESPACE DBADAM.TSADAM
LOG NO
FASTSWITCH NO
COPYDDN(DD1)
KEEPDICTIONARY
SORTKEYS NOSYSREC
SORTDEVT 3390 SORTNUM 29
SHRLEVEL REFERENCE
DEADLINE CURRENT TIMESTAMP
+ 900 MINUTES
TIMEOUT TERM
STATISTICS UPDATE ALL TABLE(ALL)
INDEX(ALL KEYCARD FREQVAL NUMCOLS 3 COUNT 10 )
DISCARD FROM TABLE ADAM.MYTABLE
WHEN (TIMEST < CURRENT TIMESTAMP - 6 MONTHS)

What am I missing? I'm probably just being dense!!

Thanks, Adam

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events * ______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Mark McCormack

DB2 V7 REORG With DISCARD and no SYSREC
(in response to Olle Brostrom)
Adam,

I wish to expand a bit on what Olle wrote.

Under DB2v7, tablespace reorg SHRLEVEL REFERENCE or SHRLEVEL CHANGE
requires a sysrec DD stmt if the table in question has no clustering
index.

Under DB2v8, it is somewhat different. Sysrec is required only if the
table in question has no index at all. The clustering requirement
disappears. Also, if sysrec is not required, it will not be opened.

Also, this has nothing to do with DISCARD.

Mark


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html