IBM Unload / Load Utility Formats

Mark Labby

IBM Unload / Load Utility Formats
You have a secure email message waiting for you from Labby at AES/PHEAA with the subject: IBM Unload / Load Utility Formats.

How to Retrieve Your Message

To retrieve your message from Labby with the subject: IBM Unload / Load Utility Formats, go to:
https://securemail.aessuccess.org/messenger/msg?x=d-67434-183MnWvRSPpGVFRi

This message will be available until 02/03/2006.

Why You Are Receiving this Email

AES/PHEAA has sent you a message that may include sensitive information or personal data, as defined by federal law. To protect the privacy of this information, the email has been encrypted.


Personal data protected by federal law includes:
Social Security numbers
Driver's license numbers
credit reports/scores


If you have questions regarding the authenticity of this message, please
visit us online at www.aesSuccess.org/securemail, or contact us:

Phone: 1-717-720-2649
Email: [login to unmask email]
Postal mail: AES/PHEAA
1200 N 7th St
Harrisburg, PA 17102

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mark Labby

IBM Unload / Load Utility Formats
(in response to Mark Labby)
I am having some issues in setting up jobs using the IBM Unload and Load.
We have used the CA Fastunload/load for years and I can get them to work,
but I am looking to start using the IBM utilities to take advantage of the
listdef.

What I want to do is to Unload data from a table where it has a column =
'Y", and while I unload it, change that column to "N" and another one to
"Y". I will then run a reorg with discard to get all of the ones that I
unloaded out of the table, and then use a load resume to put the changed
data back. The columns that are being changed are used to determine which
partitions the data goes into, so when the data gets reloaded, the data
unloaded from partition 1 goes into partition 17, 2 goes into 18, 3, goes
into 19, etc..

I can get the data unloaded and can look at the datasets and see that it
has changed the 2 columns, but when I try to load the data everything gets
rejected. So it looks like I am mixing up the formats and creating unload
datasets that are incompatible with the load format. I've tried various
combinations, but keep getting wrapped around the axle. Examples of the
unload and load jobs are below, so if anyone is more familiar with these
utilities and can help me out, I will appreciate it.


TEMPLATE ULDDDN
DSN(DB.WORK.FULL.&DB..&TS..P&PA.)
UNIT(SYSDA) SPACE(50,100) CYL DISP(NEW,CATLG,DELETE)
UNLOAD TABLESPACE DLNSTPC1.TLN10LNS PART 1:16
UNLDDN ULDDDN
SHRLEVEL REFERENCE
FROM TABLE TPC1.LN10_LON HEADER NONE
( BF_SSN, LN_SEQ, LC_STA_LON10, LI_FGV_PGM, LF_LON_SLE_PND,
UL_DRM CONSTANT 'Y',
UL_PND_DRM CONSTANT 'N')
WHEN (LI_PND_DRM = 'Y')
;



//PART17 DD DSN=DB.WORK.DLNSTPC1.TLN10LNS.P00001,
// DISP=(OLD,KEEP,KEEP)
//PART18 DD DSN=DB.WORK.DLNSTPC1.TLN10LNS.P00002,
// DISP=(OLD,KEEP,KEEP)
//PART19 DD DSN=DB.WORK.DLNSTPC1.TLN10LNS.P00003,
// DISP=(OLD,KEEP,KEEP)
//SYSIN DD *
LOAD DATA LOG NO RESUME YES
ERRDDN ERRORS
WORKDDN (WRKUTS,SORTOUTS)
MAPDDN MAPS
SORTKEYS 10000
FORMAT UNLOAD
INTO TABLE TPC1.LN10_LON PART 17 INDDN PART17 DISCARDDN DISCXX
INTO TABLE TPC1.LN10_LON PART 18 INDDN PART18 DISCARDDN DISCXX
INTO TABLE TPC1.LN10_LON PART 19 INDDN PART19 DISCARDDN DISCXX

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Bell

Re: IBM Unload / Load Utility Formats
(in response to Mark Labby)
You do realize that literals from the unload statement will default to
varchar unless you spcifically cast it fixed length char? The other choice
is to use the control cards generated from the unload which will specify the
offsets as varchar.

Mike Bell
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Mark Labby
Sent: Monday, December 05, 2005 9:59 AM
To: [login to unmask email]
Subject: [DB2-L] IBM Unload / Load Utility Formats

I am having some issues in setting up jobs using the IBM Unload and Load.
We have used the CA Fastunload/load for years and I can get them to work,
but I am looking to start using the IBM utilities to take advantage of the
listdef.

What I want to do is to Unload data from a table where it has a column =
'Y", and while I unload it, change that column to "N" and another one to
"Y". I will then run a reorg with discard to get all of the ones that I
unloaded out of the table, and then use a load resume to put the changed
data back. The columns that are being changed are used to determine which
partitions the data goes into, so when the data gets reloaded, the data
unloaded from partition 1 goes into partition 17, 2 goes into 18, 3, goes
into 19, etc..

I can get the data unloaded and can look at the datasets and see that it
has changed the 2 columns, but when I try to load the data everything gets
rejected. So it looks like I am mixing up the formats and creating unload
datasets that are incompatible with the load format. I've tried various
combinations, but keep getting wrapped around the axle. Examples of the
unload and load jobs are below, so if anyone is more familiar with these
utilities and can help me out, I will appreciate it.


TEMPLATE ULDDDN
DSN(DB.WORK.FULL.&DB..&TS..P&PA.)
UNIT(SYSDA) SPACE(50,100) CYL DISP(NEW,CATLG,DELETE)
UNLOAD TABLESPACE DLNSTPC1.TLN10LNS PART 1:16
UNLDDN ULDDDN
SHRLEVEL REFERENCE
FROM TABLE TPC1.LN10_LON HEADER NONE
( BF_SSN, LN_SEQ, LC_STA_LON10, LI_FGV_PGM, LF_LON_SLE_PND,
UL_DRM CONSTANT 'Y',
UL_PND_DRM CONSTANT 'N')
WHEN (LI_PND_DRM = 'Y')
;



//PART17 DD DSN=DB.WORK.DLNSTPC1.TLN10LNS.P00001,
// DISP=(OLD,KEEP,KEEP)
//PART18 DD DSN=DB.WORK.DLNSTPC1.TLN10LNS.P00002,
// DISP=(OLD,KEEP,KEEP)
//PART19 DD DSN=DB.WORK.DLNSTPC1.TLN10LNS.P00003,
// DISP=(OLD,KEEP,KEEP)
//SYSIN DD *
LOAD DATA LOG NO RESUME YES
ERRDDN ERRORS
WORKDDN (WRKUTS,SORTOUTS)
MAPDDN MAPS
SORTKEYS 10000
FORMAT UNLOAD
INTO TABLE TPC1.LN10_LON PART 17 INDDN PART17 DISCARDDN DISCXX
INTO TABLE TPC1.LN10_LON PART 18 INDDN PART18 DISCARDDN DISCXX
INTO TABLE TPC1.LN10_LON PART 19 INDDN PART19 DISCARDDN DISCXX

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mark Labby

Re: IBM Unload / Load Utility Formats
(in response to Mike Bell)
Hi Mike,

That sounded good to me, but didn't pan out. The use of CONSTANT to get
the literal values I want in the last two columns generate them as a fixed
length string. It was a while since I had started this, but that explains
why I have the variations on the real column names for the literals.


CONSTANT
Specifies that the output records will have an extra field containing a
constant value. The field name associated with the CONSTANT keyword must
not coincide with a table column name (the field name is for clarification
purposes only). A CONSTANT field always has a fixed length equal to the
length of the given string.

'string'
Specifies the character string inserted in the output records at the
specified or at the default position. A string is the required operand of
the CONSTANT option. If the given string is in the form 'string', it is
assumed to be an EBCDIC SBCS string. The output string for a CONSTANT field
is, however, in the specified or the default encoding scheme, that is, if
the encoding scheme used for output is not EBCDIC, the SBCS CCSID
conversion is applied to the given string before it is placed in output
records.


Thanks!
Mark Labby





"Mike Bell"
<[login to unmask email]
N.NET> To
Sent by: "DB2 [login to unmask email]
Data Base cc
Discussion List"
<[login to unmask email] Subject
ORG> Re: IBM Unload / Load Utility
Formats

12/05/2005 11:49
AM


Please respond to
"DB2 Database
Discussion list
at IDUG"
<[login to unmask email]
2-L.ORG>






You do realize that literals from the unload statement will default to
varchar unless you spcifically cast it fixed length char? The other choice
is to use the control cards generated from the unload which will specify
the
offsets as varchar.

Mike Bell
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Mark Labby
Sent: Monday, December 05, 2005 9:59 AM
To: [login to unmask email]
Subject: [DB2-L] IBM Unload / Load Utility Formats

I am having some issues in setting up jobs using the IBM Unload and Load.
We have used the CA Fastunload/load for years and I can get them to work,
but I am looking to start using the IBM utilities to take advantage of the
listdef.

What I want to do is to Unload data from a table where it has a column =
'Y", and while I unload it, change that column to "N" and another one to
"Y". I will then run a reorg with discard to get all of the ones that I
unloaded out of the table, and then use a load resume to put the changed
data back. The columns that are being changed are used to determine which
partitions the data goes into, so when the data gets reloaded, the data
unloaded from partition 1 goes into partition 17, 2 goes into 18, 3, goes
into 19, etc..

I can get the data unloaded and can look at the datasets and see that it
has changed the 2 columns, but when I try to load the data everything gets
rejected. So it looks like I am mixing up the formats and creating unload
datasets that are incompatible with the load format. I've tried various
combinations, but keep getting wrapped around the axle. Examples of the
unload and load jobs are below, so if anyone is more familiar with these
utilities and can help me out, I will appreciate it.


TEMPLATE ULDDDN
DSN(DB.WORK.FULL.&DB..&TS..P&PA.)
UNIT(SYSDA) SPACE(50,100) CYL DISP(NEW,CATLG,DELETE)
UNLOAD TABLESPACE DLNSTPC1.TLN10LNS PART 1:16
UNLDDN ULDDDN
SHRLEVEL REFERENCE
FROM TABLE TPC1.LN10_LON HEADER NONE
( BF_SSN, LN_SEQ, LC_STA_LON10, LI_FGV_PGM, LF_LON_SLE_PND,
UL_DRM CONSTANT 'Y',
UL_PND_DRM CONSTANT 'N')
WHEN (LI_PND_DRM = 'Y')
;



//PART17 DD DSN=DB.WORK.DLNSTPC1.TLN10LNS.P00001,
// DISP=(OLD,KEEP,KEEP)
//PART18 DD DSN=DB.WORK.DLNSTPC1.TLN10LNS.P00002,
// DISP=(OLD,KEEP,KEEP)
//PART19 DD DSN=DB.WORK.DLNSTPC1.TLN10LNS.P00003,
// DISP=(OLD,KEEP,KEEP)
//SYSIN DD *
LOAD DATA LOG NO RESUME YES
ERRDDN ERRORS
WORKDDN (WRKUTS,SORTOUTS)
MAPDDN MAPS
SORTKEYS 10000
FORMAT UNLOAD
INTO TABLE TPC1.LN10_LON PART 17 INDDN PART17 DISCARDDN DISCXX
INTO TABLE TPC1.LN10_LON PART 18 INDDN PART18 DISCARDDN DISCXX
INTO TABLE TPC1.LN10_LON PART 19 INDDN PART19 DISCARDDN DISCXX

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

-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
.
The IDUG List Admins can be reached at [login to unmask email]
Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


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

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mark Labby

Re: IBM Unload / Load Utility Formats
(in response to Mark Labby)
Thanks Mike.

It wasn't the varchar, but the second part of your reply solved it.
Because of the nulls in the data, it needs the control cards to give it the
layout. Otherwise the null indicators throw off the data so it doesn't
match the DB2 columns. I was hoping to avoid that since the job is loading
many partition in parallel and it seems to require that the cards be
repeated for each partition that it is loading. or at least, i haven't
found a way for it to be able to have one copy of the control cards that
they can all share since it is the same layout over and over.






"Mike Bell"
<[login to unmask email]
N.NET> To
Sent by: "DB2 [login to unmask email]
Data Base cc
Discussion List"
<[login to unmask email] Subject
ORG> Re: IBM Unload / Load Utility
Formats

12/05/2005 11:49
AM


Please respond to
"DB2 Database
Discussion list
at IDUG"
<[login to unmask email]
2-L.ORG>






You do realize that literals from the unload statement will default to
varchar unless you spcifically cast it fixed length char? The other choice
is to use the control cards generated from the unload which will specify
the
offsets as varchar.

Mike Bell
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Mark Labby
Sent: Monday, December 05, 2005 9:59 AM
To: [login to unmask email]
Subject: [DB2-L] IBM Unload / Load Utility Formats

I am having some issues in setting up jobs using the IBM Unload and Load.
We have used the CA Fastunload/load for years and I can get them to work,
but I am looking to start using the IBM utilities to take advantage of the
listdef.

What I want to do is to Unload data from a table where it has a column =
'Y", and while I unload it, change that column to "N" and another one to
"Y". I will then run a reorg with discard to get all of the ones that I
unloaded out of the table, and then use a load resume to put the changed
data back. The columns that are being changed are used to determine which
partitions the data goes into, so when the data gets reloaded, the data
unloaded from partition 1 goes into partition 17, 2 goes into 18, 3, goes
into 19, etc..

I can get the data unloaded and can look at the datasets and see that it
has changed the 2 columns, but when I try to load the data everything gets
rejected. So it looks like I am mixing up the formats and creating unload
datasets that are incompatible with the load format. I've tried various
combinations, but keep getting wrapped around the axle. Examples of the
unload and load jobs are below, so if anyone is more familiar with these
utilities and can help me out, I will appreciate it.


TEMPLATE ULDDDN
DSN(DB.WORK.FULL.&DB..&TS..P&PA.)
UNIT(SYSDA) SPACE(50,100) CYL DISP(NEW,CATLG,DELETE)
UNLOAD TABLESPACE DLNSTPC1.TLN10LNS PART 1:16
UNLDDN ULDDDN
SHRLEVEL REFERENCE
FROM TABLE TPC1.LN10_LON HEADER NONE
( BF_SSN, LN_SEQ, LC_STA_LON10, LI_FGV_PGM, LF_LON_SLE_PND,
UL_DRM CONSTANT 'Y',
UL_PND_DRM CONSTANT 'N')
WHEN (LI_PND_DRM = 'Y')
;



//PART17 DD DSN=DB.WORK.DLNSTPC1.TLN10LNS.P00001,
// DISP=(OLD,KEEP,KEEP)
//PART18 DD DSN=DB.WORK.DLNSTPC1.TLN10LNS.P00002,
// DISP=(OLD,KEEP,KEEP)
//PART19 DD DSN=DB.WORK.DLNSTPC1.TLN10LNS.P00003,
// DISP=(OLD,KEEP,KEEP)
//SYSIN DD *
LOAD DATA LOG NO RESUME YES
ERRDDN ERRORS
WORKDDN (WRKUTS,SORTOUTS)
MAPDDN MAPS
SORTKEYS 10000
FORMAT UNLOAD
INTO TABLE TPC1.LN10_LON PART 17 INDDN PART17 DISCARDDN DISCXX
INTO TABLE TPC1.LN10_LON PART 18 INDDN PART18 DISCARDDN DISCXX
INTO TABLE TPC1.LN10_LON PART 19 INDDN PART19 DISCARDDN DISCXX

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

-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
.
The IDUG List Admins can be reached at [login to unmask email]
Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


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

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm