only 1 SYSPUNCH via TEMPLATE for DB2 Unload utility

Kapil Mathur

only 1 SYSPUNCH via TEMPLATE for DB2 Unload utility
Hello DB2-L members,

I am in a DB2 v8 NFM on z/OS environment.

I am using DB2 UNLOAD utility with a LISTDEF and a TEMPLATE to unload
multiple tables from our Production DB2 subsystem as a prelude to later
loading all the data into identically named DB2 tables in our Test DB2
subsystem. Both Test and Production DB2 subsystems share the same DASD.

I would like the UNLOAD utility to generate only one 'concatenated' PUNCHDDN
control card, regardless of the number of tables unloaded ... so that later I
can feed this one control card to a DB2 Load utility JCL that runs against the
Test subsystem and have it load all corresponding Test tables in one step.

I am trying to get the DB2 UNLOAD utility to create only one PUNCHDDN
dataset containing all the Load control cards by using a DISP of
MOD,CATLG,CATLG but the UNLOAD utility keeps error-ing out and insists that
I MUST use the &DB. and &SN. as part of the PUNCHDDN template - is there a
way to get Unload utility to "bypass" this edit-check (associated with error
message DSNU1222I) since I am trying to create this 'constant' dataset-name
intentionally, in the first place ... if no way exists, is there any freeware/MVS
utility that can concatenate all datasets that match a certain name-
mask/pattern into one output dataset .... thank you for your help

The unload control cards are:-
OPTIONS EVENT (ITEMERROR,SKIP,WARNING,RC4)

LISTDEF L1
INCLUDE TABLE DPOLP001.*

TEMPLATE TUNLDDN
UNIT SYSDA
DISP(NEW,CATLG,DELETE)
SPACE CYL
PCTPRIME 50
MAXPRIME 1000
DSN('&US..&DB..&SN..DATA')
BUFNO 99

TEMPLATE TPUNCH
UNIT SYSDA
DISP(MOD,CATLG,CATLG)
SPACE(1,1)
DSN('&US..DBAT.SYSPUNCH')

UNLOAD LIST L1
PUNCHDDN TPUNCH
UNLDDN TUNLDDN
EBCDIC
SHRLEVEL CHANGE ISOLATION UR

and the error messages are:-

DSNU1035I DSNUJTDR - TEMPLATE STATEMENT PROCESSED
SUCCESSFULLY
DSNU050I DSNUGUTC - TEMPLATE TPUNCH UNIT SYSDA DISP(MOD, CATLG,
CATLG) SPACE
DSNU1035I DSNUJTDR - TEMPLATE STATEMENT PROCESSED
SUCCESSFULLY
DSNU050I DSNUGUTC - UNLOAD LIST L1 PUNCHDDN TPUNCH UNLDDN
TUNLDDN EBCDIC SHR
DSNU1033I DSNUGULM - PROCESSING LIST ITEM: TABLESPACE
DPOLP001.SPOL#01
DSNU1222I DSNUULND - TEMPLATE TPUNCH SPECIFIED FOR PUNCHDDN IS
INVALID FOR MULTIPLE TABLE SPACES
DSNU1027I DSNUGULM - PROCESSING CONTINUES DUE TO OPTIONS
ITEMERROR SKIP

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Lockwood Lyon

Re: only 1 SYSPUNCH via TEMPLATE for DB2 Unload utility
(in response to Kapil Mathur)
Here's a totally un-tested idea:

TEMPLATE TPUNCH
DISP(NEW,CATLG,CATLG)
... other parms required for GDG entries ...
DSN('&US..&DB..&TS..SYSPUNCH(+&SEQ.)'

This (hopefully) creates multiple GDG entries [(+1),(+2),...], one for
each table unloaded from the LISTDEF list. You can then use IEBGENER /
Whatever to copy the 'base' (i.e., complete collection of concatenated
datasets) to your final destination.

Assumes that you have already created the GDG base entry with a high
enough LIMIT. Oh, and this is un-tested!

Lock Lyon, Principal DBA
Fifth Third Bancorp

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Kapil Mathur
Sent: Wednesday, November 19, 2008 1:27 PM
To: [login to unmask email]
Subject: [DB2-L] only 1 SYSPUNCH via TEMPLATE for DB2 Unload utility

Hello DB2-L members,

I am in a DB2 v8 NFM on z/OS environment.

I am using DB2 UNLOAD utility with a LISTDEF and a TEMPLATE to unload
multiple tables from our Production DB2 subsystem as a prelude to later
loading all the data into identically named DB2 tables in our Test DB2
subsystem. Both Test and Production DB2 subsystems share the same DASD.

I would like the UNLOAD utility to generate only one 'concatenated'
PUNCHDDN control card, regardless of the number of tables unloaded ...
so that later I can feed this one control card to a DB2 Load utility JCL
that runs against the Test subsystem and have it load all corresponding
Test tables in one step.

I am trying to get the DB2 UNLOAD utility to create only one PUNCHDDN
dataset containing all the Load control cards by using a DISP of
MOD,CATLG,CATLG but the UNLOAD utility keeps error-ing out and insists
that I MUST use the &DB. and &SN. as part of the PUNCHDDN template - is
there a way to get Unload utility to "bypass" this edit-check
(associated with error message DSNU1222I) since I am trying to create
this 'constant' dataset-name intentionally, in the first place ... if no
way exists, is there any freeware/MVS utility that can concatenate all
datasets that match a certain name- mask/pattern into one output dataset
.... thank you for your help

The unload control cards are:-
OPTIONS EVENT (ITEMERROR,SKIP,WARNING,RC4)

LISTDEF L1
INCLUDE TABLE DPOLP001.*

TEMPLATE TUNLDDN
UNIT SYSDA
DISP(NEW,CATLG,DELETE)
SPACE CYL
PCTPRIME 50
MAXPRIME 1000
DSN('&US..&DB..&SN..DATA')
BUFNO 99

TEMPLATE TPUNCH
UNIT SYSDA
DISP(MOD,CATLG,CATLG)
SPACE(1,1)
DSN('&US..DBAT.SYSPUNCH')

UNLOAD LIST L1
PUNCHDDN TPUNCH
UNLDDN TUNLDDN
EBCDIC
SHRLEVEL CHANGE ISOLATION UR

and the error messages are:-

DSNU1035I DSNUJTDR - TEMPLATE STATEMENT PROCESSED
SUCCESSFULLY
DSNU050I DSNUGUTC - TEMPLATE TPUNCH UNIT SYSDA DISP(MOD, CATLG,
CATLG) SPACE
DSNU1035I DSNUJTDR - TEMPLATE STATEMENT PROCESSED
SUCCESSFULLY
DSNU050I DSNUGUTC - UNLOAD LIST L1 PUNCHDDN TPUNCH UNLDDN
TUNLDDN EBCDIC SHR
DSNU1033I DSNUGULM - PROCESSING LIST ITEM: TABLESPACE
DPOLP001.SPOL#01
DSNU1222I DSNUULND - TEMPLATE TPUNCH SPECIFIED FOR PUNCHDDN IS
INVALID FOR MULTIPLE TABLE SPACES
DSNU1027I DSNUGULM - PROCESSING CONTINUES DUE TO OPTIONS
ITEMERROR SKIP

This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Philip Sevetson

Re: only 1 SYSPUNCH via TEMPLATE for DB2 Unload utility
(in response to Lockwood Lyon)
Kapil,

I don't think you'll like my solution... but if you unload with a
separate UNLOAD statement for each tablespace, you can use the same
SYSPUNCH in the way you have described. I've done this successfully as
far back as DB2V7.

If you have a variable number of tablespaces, contact me offlist and
I'll send you an approximation of the JCL and SQL which we used to
generate the variable list of UNLOAD statements.

--Phil Sevetson

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Kapil Mathur
Sent: Wednesday, November 19, 2008 1:27 PM
To: [login to unmask email]
Subject: [DB2-L] only 1 SYSPUNCH via TEMPLATE for DB2 Unload utility

Hello DB2-L members,

I am in a DB2 v8 NFM on z/OS environment.

I am using DB2 UNLOAD utility with a LISTDEF and a TEMPLATE to unload
multiple tables from our Production DB2 subsystem as a prelude to later
loading all the data into identically named DB2 tables in our Test DB2
subsystem. Both Test and Production DB2 subsystems share the same DASD.

I would like the UNLOAD utility to generate only one 'concatenated'
PUNCHDDN
control card, regardless of the number of tables unloaded ... so that
later I
can feed this one control card to a DB2 Load utility JCL that runs
against the
Test subsystem and have it load all corresponding Test tables in one
step.

I am trying to get the DB2 UNLOAD utility to create only one PUNCHDDN
dataset containing all the Load control cards by using a DISP of
MOD,CATLG,CATLG but the UNLOAD utility keeps error-ing out and insists
that
I MUST use the &DB. and &SN. as part of the PUNCHDDN template - is there
a
way to get Unload utility to "bypass" this edit-check (associated with
error
message DSNU1222I) since I am trying to create this 'constant'
dataset-name
intentionally, in the first place ... if no way exists, is there any
freeware/MVS
utility that can concatenate all datasets that match a certain name-
mask/pattern into one output dataset .... thank you for your help

The unload control cards are:-
OPTIONS EVENT (ITEMERROR,SKIP,WARNING,RC4)

LISTDEF L1
INCLUDE TABLE DPOLP001.*

TEMPLATE TUNLDDN
UNIT SYSDA
DISP(NEW,CATLG,DELETE)
SPACE CYL
PCTPRIME 50
MAXPRIME 1000
DSN('&US..&DB..&SN..DATA')
BUFNO 99

TEMPLATE TPUNCH
UNIT SYSDA
DISP(MOD,CATLG,CATLG)
SPACE(1,1)
DSN('&US..DBAT.SYSPUNCH')

UNLOAD LIST L1
PUNCHDDN TPUNCH
UNLDDN TUNLDDN
EBCDIC
SHRLEVEL CHANGE ISOLATION UR

and the error messages are:-

DSNU1035I DSNUJTDR - TEMPLATE STATEMENT PROCESSED
SUCCESSFULLY
DSNU050I DSNUGUTC - TEMPLATE TPUNCH UNIT SYSDA DISP(MOD, CATLG,
CATLG) SPACE
DSNU1035I DSNUJTDR - TEMPLATE STATEMENT PROCESSED
SUCCESSFULLY
DSNU050I DSNUGUTC - UNLOAD LIST L1 PUNCHDDN TPUNCH UNLDDN
TUNLDDN EBCDIC SHR
DSNU1033I DSNUGULM - PROCESSING LIST ITEM: TABLESPACE
DPOLP001.SPOL#01
DSNU1222I DSNUULND - TEMPLATE TPUNCH SPECIFIED FOR PUNCHDDN IS
INVALID FOR MULTIPLE TABLE SPACES
DSNU1027I DSNUGULM - PROCESSING CONTINUES DUE TO OPTIONS
ITEMERROR SKIP


=========
Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
=========

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Dee Reins

Re: only 1 SYSPUNCH via TEMPLATE for DB2 Unload utility
(in response to Philip Sevetson)
I have used IEBGENER to concatenate the separate datasets into one dataset.


Dee Reins




-------------- Original message ----------------------
From: "Sevetson, Phil" <[login to unmask email]>
> Kapil,
>
> I don't think you'll like my solution... but if you unload with a
> separate UNLOAD statement for each tablespace, you can use the same
> SYSPUNCH in the way you have described. I've done this successfully as
> far back as DB2V7.
>
> If you have a variable number of tablespaces, contact me offlist and
> I'll send you an approximation of the JCL and SQL which we used to
> generate the variable list of UNLOAD statements.
>
> --Phil Sevetson
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
> Behalf Of Kapil Mathur
> Sent: Wednesday, November 19, 2008 1:27 PM
> To: [login to unmask email]
> Subject: [DB2-L] only 1 SYSPUNCH via TEMPLATE for DB2 Unload utility
>
> Hello DB2-L members,
>
> I am in a DB2 v8 NFM on z/OS environment.
>
> I am using DB2 UNLOAD utility with a LISTDEF and a TEMPLATE to unload
> multiple tables from our Production DB2 subsystem as a prelude to later
> loading all the data into identically named DB2 tables in our Test DB2
> subsystem. Both Test and Production DB2 subsystems share the same DASD.
>
> I would like the UNLOAD utility to generate only one 'concatenated'
> PUNCHDDN
> control card, regardless of the number of tables unloaded ... so that
> later I
> can feed this one control card to a DB2 Load utility JCL that runs
> against the
> Test subsystem and have it load all corresponding Test tables in one
> step.
>
> I am trying to get the DB2 UNLOAD utility to create only one PUNCHDDN
> dataset containing all the Load control cards by using a DISP of
> MOD,CATLG,CATLG but the UNLOAD utility keeps error-ing out and insists
> that
> I MUST use the &DB. and &SN. as part of the PUNCHDDN template - is there
> a
> way to get Unload utility to "bypass" this edit-check (associated with
> error
> message DSNU1222I) since I am trying to create this 'constant'
> dataset-name
> intentionally, in the first place ... if no way exists, is there any
> freeware/MVS
> utility that can concatenate all datasets that match a certain name-
> mask/pattern into one output dataset .... thank you for your help
>
> The unload control cards are:-
> OPTIONS EVENT (ITEMERROR,SKIP,WARNING,RC4)
>
> LISTDEF L1
> INCLUDE TABLE DPOLP001.*
>
> TEMPLATE TUNLDDN
> UNIT SYSDA
> DISP(NEW,CATLG,DELETE)
> SPACE CYL
> PCTPRIME 50
> MAXPRIME 1000
> DSN('&US..&DB..&SN..DATA')
> BUFNO 99
>
> TEMPLATE TPUNCH
> UNIT SYSDA
> DISP(MOD,CATLG,CATLG)
> SPACE(1,1)
> DSN('&US..DBAT.SYSPUNCH')
>
> UNLOAD LIST L1
> PUNCHDDN TPUNCH
> UNLDDN TUNLDDN
> EBCDIC
> SHRLEVEL CHANGE ISOLATION UR
>
> and the error messages are:-
>
> DSNU1035I DSNUJTDR - TEMPLATE STATEMENT PROCESSED
> SUCCESSFULLY
> DSNU050I DSNUGUTC - TEMPLATE TPUNCH UNIT SYSDA DISP(MOD, CATLG,
> CATLG) SPACE
> DSNU1035I DSNUJTDR - TEMPLATE STATEMENT PROCESSED
> SUCCESSFULLY
> DSNU050I DSNUGUTC - UNLOAD LIST L1 PUNCHDDN TPUNCH UNLDDN
> TUNLDDN EBCDIC SHR
> DSNU1033I DSNUGULM - PROCESSING LIST ITEM: TABLESPACE
> DPOLP001.SPOL#01
> DSNU1222I DSNUULND - TEMPLATE TPUNCH SPECIFIED FOR PUNCHDDN IS
> INVALID FOR MULTIPLE TABLE SPACES
> DSNU1027I DSNUGULM - PROCESSING CONTINUES DUE TO OPTIONS
> ITEMERROR SKIP
>
>
> =========
> Confidentiality Notice: This e-mail communication, and any attachments, contains
> confidential and privileged information for the exclusive use of the
> recipient(s) named above. If you are not an intended recipient, or the employee
> or agent responsible to deliver it to an intended recipient, you are hereby
> notified that you have received this communication in error and that any review,
> disclosure, dissemination, distribution or copying of it or its contents is
> prohibited. If you have received this communication in error, please notify me
> immediately by replying to this message and delete this communication from your
> computer. Thank you.
>
> Any opinions, expressed or implied, presented are solely those of the author and
> do not necessarily represent the opinions of the agency or the City.
> =========
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
> ______________________________________________________________________
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list
> archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under
> the Listserv tab. While at the site, you can also access the IDUG Online
> Learning Center, Tech Library and Code Place, see the latest IDUG conference
> information and much more. If you have not yet signed up for Basic Membership
> in IDUG, available at no cost, click on Member Services at
> http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Kapil Mathur

Re: only 1 SYSPUNCH via TEMPLATE for DB2 Unload utility
(in response to Dee Reins)
Thank you for all the responses ...

I need to clarify .. the tables being unloaded are dynamically determined by an
external program (prior to every run of the unload JCL) and then the
appropriate INCLUDE TABLE <tablename> statements are built via DSNTIAUL
and then concatenated within the Unload JCL's SYSIN just at the LISTDEF
statement declaration ..... so, the exact names of tables to be unloaded is
variable and not known in advance and (if I was to use IEBGENER) the
IEBGENER SYSUT1 DD statements cannot be built in advance to concatenate
all the SYSPUNCH datasets
(If the tables to be unloaded did not vary from one execution to another, I
could definitely use IEBGENER )

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Lockwood Lyon

Re: only 1 SYSPUNCH via TEMPLATE for DB2 Unload utility
(in response to Kapil Mathur)
To clarify,

Concatenating *all* the SYSPUNCH datasets is simple, regardless of the
number.

1. Create a Generation Data Group entry, e.g., 'HLQ.DB.TS.SYSPUNCH'
with sufficient Limit, e.g. LIMIT(100).
2. Use a TEMPLATE to create +n generations of the SYSPUNCH dataset, as
specified previously.
3. Execute your Unloads.
4. Use IEBGENER, specifying //SYSUT1 DD DSN=HLQ.DB.TS.SYSPUNCH <===
Note *NO* Generation indicator

By specifying the GDG without the "(+n)" suffix, you are specifying
concatenation of all existing generations.


Lock, Principal Database Administrator
Enterprise Data Management
Fifth Third Bancorp."

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Kapil Mathur
Sent: Friday, November 21, 2008 1:27 PM
To: [login to unmask email]
Subject: Re: [DB2-L] only 1 SYSPUNCH via TEMPLATE for DB2 Unload utility

Thank you for all the responses ...

I need to clarify .. the tables being unloaded are dynamically
determined by an external program (prior to every run of the unload JCL)
and then the appropriate INCLUDE TABLE <tablename> statements are built
via DSNTIAUL and then concatenated within the Unload JCL's SYSIN just at
the LISTDEF statement declaration ..... so, the exact names of tables to
be unloaded is variable and not known in advance and (if I was to use
IEBGENER) the IEBGENER SYSUT1 DD statements cannot be built in advance
to concatenate all the SYSPUNCH datasets (If the tables to be unloaded
did not vary from one execution to another, I could definitely use
IEBGENER )

___

This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms