[DB2-L] DB2 for z/OS - error with UNLOAD utility

Thomas Weber

[DB2-L] DB2 for z/OS - error with UNLOAD utility
Hi Jim,

according to the listdef syntax diagram you can code BASE to exclude LOBs.
Is that what you wanted?

HTH

Thomas

________________________________
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jim McAlpine
Gesendet: Freitag, 10. Dezember 2010 12:51
An: [login to unmask email]
Betreff: Re: [DB2-L] DB2 for z/OS - error with UNLOAD utility

On Fri, Dec 10, 2010 at 11:48 AM, Jim McAlpine <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Raymond, I ran that query but it returned zero rows which is what I expected but not in line with the error message I am seeing. However I did add &DB to the template dsname and that got round the problem.
Very strange why the unload didn't like the generated list when there should be no dsname conflict.

Anyway, onwards and upwards and thanks to all who contributed and got me a resolution.

Jim McAlpine


One further question. I need to exclude any lob tablespaces. How do I find out which of my tablesspaces are lobs.

Thanks again.

Jim McAlpine

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Jim McAlpine

Re: AW: [DB2-L] DB2 for z/OS - error with UNLOAD utility
(in response to Thomas Weber)
On Fri, Dec 10, 2010 at 12:56 PM, Thomas Weber <[login to unmask email]>wrote:

> Hi Jim,
>
> according to the listdef syntax diagram you can code BASE to exclude LOBs.
> Is that what you wanted?
>
> HTH
>
> Thomas
>
>
>
>
Thomas, that's exactly what I want. I'll give it a go.

Thanks

Jim McAlpine

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Raymond Bell

Re: DB2 for z/OS - error with UNLOAD utility
(in response to Jim McAlpine)
Jim,

That is indeed odd. I'd have sworn the TS was appearing twice. Or maybe Unload has a problem; dunno. But as you say, you have a resolution so onwards and upwards.

And yes, Herr Weber is right; adding BASE sounds like what you need.

Cheers,


Raymond

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Thomas Weber
Sent: 10 December 2010 12:56
To: [login to unmask email]
Subject: [DB2-L] AW: [DB2-L] DB2 for z/OS - error with UNLOAD utility

Hi Jim,

according to the listdef syntax diagram you can code BASE to exclude LOBs.
Is that what you wanted?

HTH

Thomas

________________________________
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jim McAlpine
Gesendet: Freitag, 10. Dezember 2010 12:51
An: [login to unmask email]
Betreff: Re: [DB2-L] DB2 for z/OS - error with UNLOAD utility
On Fri, Dec 10, 2010 at 11:48 AM, Jim McAlpine <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Raymond, I ran that query but it returned zero rows which is what I expected but not in line with the error message I am seeing. However I did add &DB to the template dsname and that got round the problem.
Very strange why the unload didn't like the generated list when there should be no dsname conflict.

Anyway, onwards and upwards and thanks to all who contributed and got me a resolution.

Jim McAlpine


One further question. I need to exclude any lob tablespaces. How do I find out which of my tablesspaces are lobs.

Thanks again.

Jim McAlpine

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Michael Turner

Re: AW: [DB2-L] DB2 for z/OS - error with UNLOAD utility
(in response to Raymond Bell)
Hi all

I don't think that is how the BASE keyword works. It controls the addition of extra objects to the already generated list. It will ensure that for any LOB tablespace in the list the related base tablespace is also included. It will not exclude LOB tablespaces already included by the name mapping. Of course, I could be wrong. It's a while since I have looked at LOBs.

Regards
Mike Turner
Email: [login to unmask email]
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: Thomas Weber
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Friday, December 10, 2010 12:56 PM
Subject: [DB2-L] AW: [DB2-L] DB2 for z/OS - error with UNLOAD utility


Hi Jim,

according to the listdef syntax diagram you can code BASE to exclude LOBs.
Is that what you wanted?

HTH

Thomas



----------------------------------------------------------------------------
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jim McAlpine
Gesendet: Freitag, 10. Dezember 2010 12:51
An: [login to unmask email]
Betreff: Re: [DB2-L] DB2 for z/OS - error with UNLOAD utility


On Fri, Dec 10, 2010 at 11:48 AM, Jim McAlpine <[login to unmask email]> wrote:


Raymond, I ran that query but it returned zero rows which is what I expected but not in line with the error message I am seeing. However I did add &DB to the template dsname and that got round the problem.
Very strange why the unload didn't like the generated list when there should be no dsname conflict.

Anyway, onwards and upwards and thanks to all who contributed and got me a resolution.

Jim McAlpine


One further question. I need to exclude any lob tablespaces. How do I find out which of my tablesspaces are lobs.

Thanks again.

Jim McAlpine



----------------------------------------------------------------------------



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.



------------------------------------------------------------------------------



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Jeffrey Schade

Re: AW: [DB2-L] DB2 for z/OS - error with UNLOAD utility
(in response to Michael Turner)
You can code an exclude with the LOB parameter of the LISTDEF to
eliminate any LOBS.



Jeff

_________________________________________
Jeffrey Schade

Systems Consultant, Technology Engineering


ISO
545 Washington Boulevard
Jersey City, NJ 07310
Voice: (201) 469-3738

FAX: (201) 748-1500
[login to unmask email] <mailto:[login to unmask email]>



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Mike Turner
Sent: Friday, December 10, 2010 9:06 AM
To: [login to unmask email]
Subject: Re: [DB2-L] AW: [DB2-L] DB2 for z/OS - error with UNLOAD
utility



Hi all



I don't think that is how the BASE keyword works. It controls the
addition of extra objects to the already generated list. It will ensure
that for any LOB tablespace in the list the related base tablespace is
also included. It will not exclude LOB tablespaces already included by
the name mapping. Of course, I could be wrong. It's a while since I have
looked at LOBs.



Regards
Mike Turner
Email: [login to unmask email] <mailto:[login to unmask email]>
Tel: +44 (0)1565-873702
Web: www.michael-turner.ltd.uk < http://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: Thomas Weber <mailto:[login to unmask email]>

Newsgroups: bit.listserv.db2-l

To: [login to unmask email]

Sent: Friday, December 10, 2010 12:56 PM

Subject: [DB2-L] AW: [DB2-L] DB2 for z/OS - error with UNLOAD
utility



Hi Jim,



according to the listdef syntax diagram you can code BASE to
exclude LOBs.

Is that what you wanted?



HTH



Thomas




________________________________


Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag
von Jim McAlpine
Gesendet: Freitag, 10. Dezember 2010 12:51
An: [login to unmask email]
Betreff: Re: [DB2-L] DB2 for z/OS - error with UNLOAD
utility

On Fri, Dec 10, 2010 at 11:48 AM, Jim McAlpine
<[login to unmask email]> wrote:



Raymond, I ran that query but it returned zero rows
which is what I expected but not in line with the error message I am
seeing. However I did add &DB to the template dsname and that got round
the problem.

Very strange why the unload didn't like the generated
list when there should be no dsname conflict.



Anyway, onwards and upwards and thanks to all who
contributed and got me a resolution.



Jim McAlpine





One further question. I need to exclude any lob
tablespaces. How do I find out which of my tablesspaces are lobs.



Thanks again.



Jim McAlpine



________________________________

Independent, not-for-profit, User Run - the IDUG
difference! < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership
in IDUG. If you are not already an IDUG member, please register here.
< http://www.idug.org/register >



________________________________

Independent, not-for-profit, User Run - the IDUG difference!
< http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG.
If you are not already an IDUG member, please register here.
< http://www.idug.org/register >



________________________________

Independent, not-for-profit, User Run - the IDUG difference!
< http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >


_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Joe Geller

Re: AW: [DB2-L] DB2 for z/OS - error with UNLOAD utility
(in response to Jeffrey Schade)
The way I read the manual, either will work -
you can Include with the BASE option, or
Exclude with the LOB option.

If BASE is coded, and the list item is a base tablespace,
the LOB ts will not be included. If the list item is a
LOB ts, then the base ts will be included but not the
LOB ts.

If LOB is coded, the opposite happens, the LOB is included
but not the base (so exclude will exclude the LOB).

The third option is ALL which says to include both the
base and LOB ts if one is in the list.

Joe



You can code an exclude with the LOB parameter of the LISTDEF to eliminate any LOBS.



Jeff

_________________________________________
Jeffrey Schade

Systems Consultant, Technology Engineering


ISO
545 Washington Boulevard
Jersey City, NJ 07310
Voice: (201) 469-3738

FAX: (201) 748-1500
[login to unmask email]



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Mike Turner
Sent: Friday, December 10, 2010 9:06 AM
To: [login to unmask email]
Subject: Re: [DB2-L] AW: [DB2-L] DB2 for z/OS - error with UNLOAD utility



Hi all



I don't think that is how the BASE keyword works. It controls the addition of extra objects to the already generated list. It will ensure that for any LOB tablespace in the list the related base tablespace is also included. It will not exclude LOB tablespaces already included by the name mapping. Of course, I could be wrong. It's a while since I have looked at LOBs.



Regards
Mike Turner
Email: [login to unmask email]
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: Thomas Weber

Newsgroups: bit.listserv.db2-l

To: [login to unmask email]

Sent: Friday, December 10, 2010 12:56 PM

Subject: [DB2-L] AW: [DB2-L] DB2 for z/OS - error with UNLOAD utility



Hi Jim,



according to the listdef syntax diagram you can code BASE to exclude LOBs.

Is that what you wanted?



HTH



Thomas




--------------------------------------------------------------------------------

Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jim McAlpine
Gesendet: Freitag, 10. Dezember 2010 12:51
An: [login to unmask email]
Betreff: Re: [DB2-L] DB2 for z/OS - error with UNLOAD utility

On Fri, Dec 10, 2010 at 11:48 AM, Jim McAlpine <[login to unmask email]> wrote:



Raymond, I ran that query but it returned zero rows which is what I expected but not in line with the error message I am seeing. However I did add &DB to the template dsname and that got round the problem.

Very strange why the unload didn't like the generated list when there should be no dsname conflict.



Anyway, onwards and upwards and thanks to all who contributed and got me a resolution.



Jim McAlpine





One further question. I need to exclude any lob tablespaces. How do I find out which of my tablesspaces are lobs.



Thanks again.



Jim McAlpine

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Jim McAlpine

Re: AW: [DB2-L] DB2 for z/OS - error with UNLOAD utility
(in response to Joe Geller)
On Fri, Dec 10, 2010 at 2:40 PM, Joe Geller <[login to unmask email]> wrote:

> The way I read the manual, either will work -
> you can Include with the BASE option, or
> Exclude with the LOB option.
>
> If BASE is coded, and the list item is a base tablespace,
> the LOB ts will not be included. If the list item is a
> LOB ts, then the base ts will be included but not the
> LOB ts.
>
> If LOB is coded, the opposite happens, the LOB is included
> but not the base (so exclude will exclude the LOB).
>
> The third option is ALL which says to include both the
> base and LOB ts if one is in the list.
>
> Joe
>
>
>
>
>
Unfortunatley neither of these techniques does what I want. If I include
base tables for a lob set then I get the following because the base table
contains the lob column definition and is therefore too large for the unload
utility.

DSNU1033I 344 16:27:46.47 DSNUGULM - PROCESSING LIST ITEM: TABLESPACE
Z30DDA01.SFILES
DSNU1218I * 344 16:27:46.47 DSNUULIA - LOGICAL RECORD LENGTH OF OUTPUT
RECORD EXCEEDED THE LIMIT FOR TABLE Z30D.FILES
DSNU012I 344 16:27:46.48 DSNUGBAC - UTILITY EXECUTION TERMINATED, HIGHEST
RETURN CODE=8
So I want to be able exclude any base table that contains a lob column. If
that can't be done via listdef then I need to be able to identify those
tables so I can put exclude control cards in the job for them.

Thanks again in advance for your help.

Jim McAlpine

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Jim McAlpine

Re: AW: [DB2-L] DB2 for z/OS - error with UNLOAD utility
(in response to Jim McAlpine)
Well, I eventually got this to run to completeion an was able to unload some
2000 tablespaces ............... only to find out that the load utility does
not include the same ability to use listdef. What a bummer.

Jim McAlpine

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
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!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Jeffrey Schade

Re: AW: [DB2-L] DB2 for z/OS - error with UNLOAD utility
(in response to Jim McAlpine)
Jim,



If you use the TEMPLATE for the unload datasets and the punch control
for the unload control files you can run the load with a SYSIN which
concatenates the unload punch control datasets. The nice thing is the
load control dataset contains a reference to the unloaded dataset so it
is not needed in the JCL. See my sample unload below.



Try it and you will see what I am talking about. I do this all the time.



LISTDEF UNLDLIST INCLUDE TABLESPACE DWC0002P.TWC0260P

INCLUDE TABLESPACE DWC0002P.TWC0261P

INCLUDE TABLESPACE DWC0002P.TWC0262P

INCLUDE TABLESPACE DWC0002P.TWC0263P

TEMPLATE WCDATA

UNIT SYSDA

DISP(NEW,CATLG,DELETE)

SPACE(10,10) CYL

DSN 'I71012.&DB..&TS..SYSREC'

BUFNO=95

VOLCNT (255)

TEMPLATE WCCNTL

UNIT SYSDA

DISP(NEW,CATLG,DELETE)

SPACE(1,1) TRK

DSN 'I71012.&DB..&TS..SYSPUNCH'

BUFNO=95

VOLCNT (255)

UNLOAD LIST UNLDLIST UNLDDN WCDATA PUNCHDDN WCCNTL SHRLEVEL REFERENCE



Jeff

_________________________________________
Jeffrey Schade

Systems Consultant, Technology Engineering


ISO
545 Washington Boulevard
Jersey City, NJ 07310
Voice: (201) 469-3738

FAX: (201) 748-1500
[login to unmask email] <mailto:[login to unmask email]>



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Saturday, December 11, 2010 6:12 AM
To: [login to unmask email]
Subject: Re: [DB2-L] AW: [DB2-L] DB2 for z/OS - error with UNLOAD
utility



Well, I eventually got this to run to completeion an was able to unload
some 2000 tablespaces ............... only to find out that the load
utility does not include the same ability to use listdef. What a
bummer.

Jim McAlpine



________________________________

Independent, not-for-profit, User Run - the IDUG difference!
< http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >


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

Thomas Weber

AW: [DB2-L] DB2 for z/OS - error with UNLOAD utility
(in response to Jeffrey Schade)
Jim,

sorry for causing any confusion, I just tested it with both options (BASE and LOB) and for me it does work.
But I have to admit that I rarely use listdefs/templates.
Could you provide at least an extract of what you used or could you please check whether you coded the BASE keyword with any INCLUDE?
Just an idea.

Thomas


________________________________
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jim McAlpine
Gesendet: Freitag, 10. Dezember 2010 12:51
An: [login to unmask email]
Betreff: Re: [DB2-L] DB2 for z/OS - error with UNLOAD utility

On Fri, Dec 10, 2010 at 11:48 AM, Jim McAlpine <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Raymond, I ran that query but it returned zero rows which is what I expected but not in line with the error message I am seeing. However I did add &DB to the template dsname and that got round the problem.
Very strange why the unload didn't like the generated list when there should be no dsname conflict.

Anyway, onwards and upwards and thanks to all who contributed and got me a resolution.

Jim McAlpine


One further question. I need to exclude any lob tablespaces. How do I find out which of my tablesspaces are lobs.

Thanks again.

Jim McAlpine

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv