DB2 V7 REORG With DISCARD and no SYSREC???

Adam Baldwin

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

Peter Backlund

Re: DB2 V7 REORG With DISCARD and no SYSREC???
(in response to Adam Baldwin)
Adam,

have you tried

//SYSREC DD DUMMY

Just and idea

Peter

Adam Baldwin wrote: 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
No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.176 / Virus Database: 270.10.7/1893 - Release Date: 2009-01-14 06:59
-- Attend IDUG 2009, North America -- 11-15 May in Denver, Colorado Attend IDUG 2009, Europe -- 5- 9 October in Rome, Italy Learn more at http://www.idug.org +-------------------------------+---------------------------------+ | Peter G Backlund | Telephone: 08 - 38 69 08 | | Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 | | Rasundavagen 94 | Country Code (Sweden): 46 | | S-169 57 SOLNA | Skype: BacklundDB2 | | Sweden | E-mail: [login to unmask email] | +-------------------------------+---------------------------------+ | IBM Database GOLD Consultant | +-------------------------------+---------------------------------+ | Working with DB2 since 1981 ... and forever! | +-----------------------------------------------------------------+

IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia

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 here.

Adrian Collett

Re: DB2 V7 REORG With DISCARD and no SYSREC???
(in response to Peter Backlund)
Adam,

shot in the dark...but try putting NOSYSREC SORTKEYS instead of
SORTKEYS NOSYSREC

By requiring a SYSREC DD card it is obviously not "seeing" the NOSYSREC
keyword...I've been bitten in the past with something similar and the
reason was not followiing the syntax diagram in the same sequence...

Hope it works,

Ciao,
Adrian


Adam Baldwin ha scritto:
> 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 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

Adam Baldwin

Re: DB2 V7 REORG With DISCARD and no SYSREC???
(in response to Adrian Collett)
Peter, unfortunately DUMMY isn't allowed for the SYSREC DD card - it will give
a:
DSNU034I DSNURORG - DATA SET TYPE INVALID FOR DDNAME SYSREC

(I just ran the job with dummy because I couldn't remember the exact
message.)

I really don't want to have to go the SYSREC route but I can't find a work
around.

Cheers, 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

Adam Baldwin

Re: DB2 V7 REORG With DISCARD and no SYSREC???
(in response to Adam Baldwin)
Adrian... having read your post I went back to the manual and realized that I
didn't have SORTDATA.... I also changed the SORTKEYS to be after
NOSYSREC. The manual actually says:

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.

Thanks - you made me go back to the manual (again)... the job is now
working.

That's what I love about the list! Someone, somewhere always has their head
working!

Cheers, Adam RTFM.

______________________________________________________________________

* 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

Hanne Lyssand

Re: DB2 V7 REORG With DISCARD and no SYSREC???
(in response to Adam Baldwin)
disp=(new,delete,delete) will work, but I guess you still have to have the space for a short time. That’s what we are doing. Don't remember why anymore and I made the JCL in version 7 and haven't done a check if it would be an idea to change it.

hanne

-----Opprinnelig melding-----
Fra: DB2 Data Base Discussion List [mailto:[login to unmask email] På vegne av Adam Baldwin
Sendt: 14. januar 2009 15:28
Til: [login to unmask email]
Emne: Re: [DB2-L] DB2 V7 REORG With DISCARD and no SYSREC???

Peter, unfortunately DUMMY isn't allowed for the SYSREC DD card - it will give
a:
DSNU034I DSNURORG - DATA SET TYPE INVALID FOR DDNAME SYSREC

(I just ran the job with dummy because I couldn't remember the exact
message.)

I really don't want to have to go the SYSREC route but I can't find a work
around.

Cheers, 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 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