Using the DB2 reorg unload file to load a table ??

Dee Reins

Using the DB2 reorg unload file to load a table ??

I was doing a reorg on a tablespace in test. the utility failed on the reload step.

I terminated the utility, thinking that using "LOG YES" would roll back.
I am thinking that I can use the SYSREC file to load the data, but is seems that all the columns are shifted 4 bytes to the right.
I think the 4 bytes is for a variable length record.
My thoughts are to use the load control cards and move every thing over 4 bytes.

Has anyone done this, Am I on the right track.

Thanks
Dee Reins
Unload JCL is below
//DB2PUPRC.SYSREC DD DSN=TMCP.I.DBCPDD.REOGUNLD,
// DISP=(NEW,DELETE,CATLG),
// UNIT=(SYSDA,10),
// SPACE=(CYL,(150,200),RLSE)
//DB2PUPRC.SYSUT1 DD DSN=TMCP.I.DBCPDD.SYSUT1,
// DISP=(NEW,DELETE,CATLG),
// UNIT=(SYSDA,10),
// SPACE=(CYL,(150,200),RLSE)
//DB2PUPRC.SORTOUT DD DSN=TMCP.I.DBCPDD.SORTOUT,
// UNIT=(SYSDA,10),
// DISP=(NEW,DELETE,CATLG),
// SPACE=(CYL,(150,200),RLSE)
//* -----------------------------------------------
//DB2PUPRC.SYSIN DD *
LISTDEF REORGLIST INCLUDE TABLESPACE DBCPDD.T0000902
----------------------------------------
REORG TABLESPACE LIST REORGLIST
LOG YES
STATISTICS TABLE (ALL)
UNLOAD CONTINUE UNLDDN (SYSREC) SORTDEVT SYSDA SORTNUM 4
WORKDDN (SYSUT1, SORTOUT)



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

Mike Bell

Re: Using the DB2 reorg unload file to load a table ??
(in response to Dee Reins)
Is there a reason you don't want to use LOAD FORMAT UNLOAD?

If you have varchar fields, you have to use format unload because sysrec
doesn't expand them to maximum length with fixed position.

Mike
HLS Technologies

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dee Reins
Sent: Thursday, December 30, 2010 5:20 PM
To: [login to unmask email]
Subject: [DB2-L] Using the DB2 reorg unload file to load a table ??


I was doing a reorg on a tablespace in test. the utility failed on the
reload step.

I terminated the utility, thinking that using "LOG YES" would roll back.
I am thinking that I can use the SYSREC file to load the data, but is seems
that all the columns are shifted 4 bytes to the right.
I think the 4 bytes is for a variable length record.
My thoughts are to use the load control cards and move every thing over 4
bytes.

Has anyone done this, Am I on the right track.

Thanks
Dee Reins
<file:///C:/DOCUME%7E1/HDR007%7E1/LOCALS%7E1/Temp/moz-screenshot-14.png>
Unload JCL is below //DB2PUPRC.SYSREC DD DSN=TMCP.I.DBCPDD.REOGUNLD,
// DISP=(NEW,DELETE,CATLG),
// UNIT=(SYSDA,10),
// SPACE=(CYL,(150,200),RLSE)
//DB2PUPRC.SYSUT1 DD DSN=TMCP.I.DBCPDD.SYSUT1,
// DISP=(NEW,DELETE,CATLG),
// UNIT=(SYSDA,10),
// SPACE=(CYL,(150,200),RLSE)
//DB2PUPRC.SORTOUT DD DSN=TMCP.I.DBCPDD.SORTOUT,
// UNIT=(SYSDA,10),
// DISP=(NEW,DELETE,CATLG),
// SPACE=(CYL,(150,200),RLSE)
//* -----------------------------------------------
//DB2PUPRC.SYSIN DD *
LISTDEF REORGLIST INCLUDE TABLESPACE DBCPDD.T0000902
----------------------------------------
REORG TABLESPACE LIST REORGLIST
LOG YES
STATISTICS TABLE (ALL)
UNLOAD CONTINUE UNLDDN (SYSREC) SORTDEVT SYSDA SORTNUM 4
WORKDDN (SYSUT1, SORTOUT)



________________________________

Introducing IBM® DB2® 10 for z/OS
< http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

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

Dee Reins

Re: Using the DB2 reorg unload file to load a table ??
(in response to Mike Bell)
I don't have any varchar fields in the record. I do have the LOAD FORMAT UNLOAD.
Could I use that to load the data?


“Coming together is a beginning. Keeping together is progress. Working together is success"



----- Original Message -----
From: "Mike Bell" <[login to unmask email]>
To: [login to unmask email]
Sent: Thursday, December 30, 2010 3:28:52 PM
Subject: Re: [DB2-L] Using the DB2 reorg unload file to load a table ??

Is there a reason you don't want to use LOAD FORMAT UNLOAD?

If you have varchar fields, you have to use format unload because sysrec
doesn't expand them to maximum length with fixed position.

Mike
HLS Technologies

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dee Reins
Sent: Thursday, December 30, 2010 5:20 PM
To: [login to unmask email]
Subject: [DB2-L] Using the DB2 reorg unload file to load a table ??


I was doing a reorg on a tablespace in test. the utility failed on the
reload step.

I terminated the utility, thinking that using "LOG YES" would roll back.
I am thinking that I can use the SYSREC file to load the data, but is seems
that all the columns are shifted 4 bytes to the right.
I think the 4 bytes is for a variable length record.
My thoughts are to use the load control cards and move every thing over 4
bytes.

Has anyone done this, Am I on the right track.

Thanks
Dee Reins
<file:///C:/DOCUME%7E1/HDR007%7E1/LOCALS%7E1/Temp/moz-screenshot-14.png>
Unload JCL is below //DB2PUPRC.SYSREC DD DSN=TMCP.I.DBCPDD.REOGUNLD,
// DISP=(NEW,DELETE,CATLG),
// UNIT=(SYSDA,10),
// SPACE=(CYL,(150,200),RLSE)
//DB2PUPRC.SYSUT1 DD DSN=TMCP.I.DBCPDD.SYSUT1,
// DISP=(NEW,DELETE,CATLG),
// UNIT=(SYSDA,10),
// SPACE=(CYL,(150,200),RLSE)
//DB2PUPRC.SORTOUT DD DSN=TMCP.I.DBCPDD.SORTOUT,
// UNIT=(SYSDA,10),
// DISP=(NEW,DELETE,CATLG),
// SPACE=(CYL,(150,200),RLSE)
//* -----------------------------------------------
//DB2PUPRC.SYSIN DD *
LISTDEF REORGLIST INCLUDE TABLESPACE DBCPDD.T0000902
----------------------------------------
REORG TABLESPACE LIST REORGLIST
LOG YES
STATISTICS TABLE (ALL)
UNLOAD CONTINUE UNLDDN (SYSREC) SORTDEVT SYSDA SORTNUM 4
WORKDDN (SYSUT1, SORTOUT)



________________________________

Introducing IBM® DB2® 10 for z/OS
< http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

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

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

Dee Reins

Re: Using the DB2 reorg unload file to load a table ??
(in response to Dee Reins)
I used the LOAD FORMAT UNLOAD, and it worked like a charm.

Thank you

Dee


“Coming together is a beginning. Keeping together is progress. Working together is success"



----- Original Message -----
From: "Mike Bell" <[login to unmask email]>
To: [login to unmask email]
Sent: Thursday, December 30, 2010 3:28:52 PM
Subject: Re: [DB2-L] Using the DB2 reorg unload file to load a table ??

Is there a reason you don't want to use LOAD FORMAT UNLOAD?

If you have varchar fields, you have to use format unload because sysrec
doesn't expand them to maximum length with fixed position.

Mike
HLS Technologies

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dee Reins
Sent: Thursday, December 30, 2010 5:20 PM
To: [login to unmask email]
Subject: [DB2-L] Using the DB2 reorg unload file to load a table ??


I was doing a reorg on a tablespace in test. the utility failed on the
reload step.

I terminated the utility, thinking that using "LOG YES" would roll back.
I am thinking that I can use the SYSREC file to load the data, but is seems
that all the columns are shifted 4 bytes to the right.
I think the 4 bytes is for a variable length record.
My thoughts are to use the load control cards and move every thing over 4
bytes.

Has anyone done this, Am I on the right track.

Thanks
Dee Reins
<file:///C:/DOCUME%7E1/HDR007%7E1/LOCALS%7E1/Temp/moz-screenshot-14.png>
Unload JCL is below //DB2PUPRC.SYSREC DD DSN=TMCP.I.DBCPDD.REOGUNLD,
// DISP=(NEW,DELETE,CATLG),
// UNIT=(SYSDA,10),
// SPACE=(CYL,(150,200),RLSE)
//DB2PUPRC.SYSUT1 DD DSN=TMCP.I.DBCPDD.SYSUT1,
// DISP=(NEW,DELETE,CATLG),
// UNIT=(SYSDA,10),
// SPACE=(CYL,(150,200),RLSE)
//DB2PUPRC.SORTOUT DD DSN=TMCP.I.DBCPDD.SORTOUT,
// UNIT=(SYSDA,10),
// DISP=(NEW,DELETE,CATLG),
// SPACE=(CYL,(150,200),RLSE)
//* -----------------------------------------------
//DB2PUPRC.SYSIN DD *
LISTDEF REORGLIST INCLUDE TABLESPACE DBCPDD.T0000902
----------------------------------------
REORG TABLESPACE LIST REORGLIST
LOG YES
STATISTICS TABLE (ALL)
UNLOAD CONTINUE UNLDDN (SYSREC) SORTDEVT SYSDA SORTNUM 4
WORKDDN (SYSUT1, SORTOUT)



________________________________

Introducing IBM® DB2® 10 for z/OS
< http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

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

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