HP Unload Question re: Date Fields

Mike Dransfield

HP Unload Question re: Date Fields
Hey, All,

I’m running HP Unload against an image copy to produce a flat file. The
output data is delimited with x’1D’ (chosen because that’s no present in the
data).

In the output dataset, date data are being preceded by x’00’ for some
reason, and I can’t seem to eliminate them. Here’s what I’ve tried:

In my first attempt, I just did the normal

UNLOAD TABLESPACE…
SELECT * FROM…
OUTDDN (SYSREC)
FORMAT USER (reformatting 4 decimal columns to alpha)
FORMAT DELIMITED SEP X'1D'

Result: date shows up in mm-dd-yyyy format as desired, but with x’00’ after
previous column’s delimiter and before the date.

Next attempt:

In my FORMAT USER statement, I tried explicitly specifying the column as
TYPE DATE.

Result: no more hex ‘00’, but the date data now shows up in the flat file in hex
format.

Next attempt:

In my FORMAT USER statement, I specified the column as TYPE CHAR(10)

Result: date shows up in mm-dd-yyyy format as desired, but again with the
leading x’00’.

Next attempt:

Eliminated date column from FORMAT USER statement, and added
OPTIONS DATE DATE_A before the SELECT statement.

Result: date shows up mm-dd-yyyy format as desired, but again with the
leading x’00’.

I would prefer to not handle this by running it through a program afterwards to
strip out the x’00’, as I have many tables to unload in this manner.

Environment specifics:

z/OS 1.9
DB2 V8 c/m
DB2 HP UNLOAD V2.2

Any help, suggestions, or advice would be greatly appreciated and
graciously accepted. If more info is needed, please let me know.


Kind Regards,
Mike D.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

jack fernicola

Re: HP Unload Question re: Date Fields
(in response to Mike Dransfield)
MIke,
 
Just a side note, you will need to upgrade HP Unload to V3.2 when going to DB2 V8 NFM. 

Jack
 

--- On Tue, 12/22/09, Mike Dransfield <[login to unmask email]> wrote:


From: Mike Dransfield <[login to unmask email]>
Subject: [DB2-L] HP Unload Question re: Date Fields
To: [login to unmask email]
Date: Tuesday, December 22, 2009, 10:53 AM


Hey, All,

I’m running HP Unload against an image copy to produce a flat file. The
output data is delimited with x’1D’ (chosen because that’s no present in the
data).

In the output dataset, date data are being preceded by x’00’ for some
reason, and I can’t seem to eliminate them. Here’s what I’ve tried:

In my first attempt, I just did the normal

UNLOAD TABLESPACE…
SELECT * FROM…
OUTDDN (SYSREC)
FORMAT USER (reformatting 4 decimal columns to alpha)
FORMAT DELIMITED SEP X'1D'

Result: date shows up in mm-dd-yyyy format as desired, but with x’00’ after
previous column’s delimiter and before the date.

Next attempt:

In my FORMAT USER statement, I tried explicitly specifying the column as
TYPE DATE.

Result: no more hex ‘00’, but the date data now shows up in the flat file in hex
format.

Next attempt:

In my FORMAT USER statement, I specified the column as TYPE CHAR(10)

Result: date shows up in mm-dd-yyyy format as desired, but again with the
leading x’00’.

Next attempt:

Eliminated date column from FORMAT USER statement, and added
OPTIONS DATE DATE_A before the SELECT statement.

Result: date shows up mm-dd-yyyy format as desired, but again with the
leading x’00’.

I would prefer to not handle this by running it through a program afterwards to
strip out the x’00’, as I have many tables to unload in this manner.

Environment specifics:

z/OS 1.9
DB2 V8 c/m
DB2 HP UNLOAD V2.2

Any help, suggestions, or advice would be greatly appreciated and
graciously accepted. If more info is needed, please let me know.


Kind Regards,
Mike D.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *  http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html -  home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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





_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

Dan Chatt

Re: HP Unload Question re: Date Fields
(in response to jack fernicola)
Is the DATE field NULLABLE? If so, the one byte NULL INDICATOR would be
before the DATE field.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

David Seibert

Re: HP Unload Question re: Date Fields
(in response to Dan Chatt)
Hello Mike.

I suspect Dan has a good guess at the explanation.

What is your intent for the output flat file? That will help us understand your concern with eliminating the x'00's.

Dave

The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Mike Dransfield
Sent: Tuesday, December 22, 2009 10:54 AM
To: [login to unmask email]
Subject: [DB2-L] HP Unload Question re: Date Fields

Hey, All,

I’m running HP Unload against an image copy to produce a flat file. The
output data is delimited with x’1D’ (chosen because that’s no present in the
data).

In the output dataset, date data are being preceded by x’00’ for some
reason, and I can’t seem to eliminate them. Here’s what I’ve tried:

In my first attempt, I just did the normal

UNLOAD TABLESPACE…
SELECT * FROM…
OUTDDN (SYSREC)
FORMAT USER (reformatting 4 decimal columns to alpha)
FORMAT DELIMITED SEP X'1D'

Result: date shows up in mm-dd-yyyy format as desired, but with x’00’ after
previous column’s delimiter and before the date.

Next attempt:

In my FORMAT USER statement, I tried explicitly specifying the column as
TYPE DATE.

Result: no more hex ‘00’, but the date data now shows up in the flat file in hex
format.

Next attempt:

In my FORMAT USER statement, I specified the column as TYPE CHAR(10)

Result: date shows up in mm-dd-yyyy format as desired, but again with the
leading x’00’.

Next attempt:

Eliminated date column from FORMAT USER statement, and added
OPTIONS DATE DATE_A before the SELECT statement.

Result: date shows up mm-dd-yyyy format as desired, but again with the
leading x’00’.

I would prefer to not handle this by running it through a program afterwards to
strip out the x’00’, as I have many tables to unload in this manner.

Environment specifics:

z/OS 1.9
DB2 V8 c/m
DB2 HP UNLOAD V2.2

Any help, suggestions, or advice would be greatly appreciated and
graciously accepted. If more info is needed, please let me know.


Kind Regards,
Mike D.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

Michael Dransfield

Re: HP Unload Question re: Date Fields
(in response to David Seibert)
Dave,

Yes, Dan was correct.

The data is being FTPed to an accounting firm, who is likely loading it
into a non-DB2 platform. It could be Sybase, Oracle or MS SQL Server. I
don't have direct contact with them.

The accounting firm has less appetite than I do for writing a program to
strip off the extra x'00' occurrences.

Thanks to you and Dan for your help.

Best,
Mike

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Seibert, Dave
Sent: Tuesday, December 22, 2009 2:33 PM
To: [login to unmask email]
Subject: Re: [DB2-L] HP Unload Question re: Date Fields

Hello Mike.

I suspect Dan has a good guess at the explanation.

What is your intent for the output flat file? That will help us
understand your concern with eliminating the x'00's.

Dave

The contents of this e-mail are intended for the named addressee only.
It contains information that may be confidential. Unless you are the
named addressee or an authorized designee, you may not copy or use it,
or disclose it to anyone else. If you received it in error please notify
us immediately and then destroy it.

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Mike
Dransfield
Sent: Tuesday, December 22, 2009 10:54 AM
To: [login to unmask email]
Subject: [DB2-L] HP Unload Question re: Date Fields

Hey, All,

I'm running HP Unload against an image copy to produce a flat file. The
output data is delimited with x'1D' (chosen because that's no present in
the
data).

In the output dataset, date data are being preceded by x'00' for some
reason, and I can't seem to eliminate them. Here's what I've tried:

In my first attempt, I just did the normal

UNLOAD TABLESPACE...
SELECT * FROM...
OUTDDN (SYSREC)
FORMAT USER (reformatting 4 decimal columns to alpha)
FORMAT DELIMITED SEP X'1D'

Result: date shows up in mm-dd-yyyy format as desired, but with x'00'
after
previous column's delimiter and before the date.

Next attempt:

In my FORMAT USER statement, I tried explicitly specifying the column as

TYPE DATE.

Result: no more hex '00', but the date data now shows up in the flat
file in hex
format.

Next attempt:

In my FORMAT USER statement, I specified the column as TYPE CHAR(10)

Result: date shows up in mm-dd-yyyy format as desired, but again with
the
leading x'00'.

Next attempt:

Eliminated date column from FORMAT USER statement, and added
OPTIONS DATE DATE_A before the SELECT statement.

Result: date shows up mm-dd-yyyy format as desired, but again with the
leading x'00'.

I would prefer to not handle this by running it through a program
afterwards to
strip out the x'00', as I have many tables to unload in this manner.

Environment specifics:

z/OS 1.9
DB2 V8 c/m
DB2 HP UNLOAD V2.2

Any help, suggestions, or advice would be greatly appreciated and
graciously accepted. If more info is needed, please let me know.


Kind Regards,
Mike D.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions
Journal
Technical atricles from world famous authors in DB2's most prestigious,
peer reviewed
magazine now on-line!
_____________________________________________________________________

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

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions
Journal
Technical atricles from world famous authors in DB2's most prestigious,
peer reviewed
magazine now on-line!
_____________________________________________________________________

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

This e-mail may contain information that is confidential, privileged or otherwise protected from disclosure. If you are not an intended recipient of this e-mail, do not duplicate or redistribute it by any means. Please delete it and any attachments and notify the sender that you have received it in error. Unless specifically indicated, this e-mail is not an offer to buy or sell or a solicitation to buy or sell any securities, investment products or other financial product or service, an official confirmation of any transaction, or an official statement of Barclays. Any views or opinions presented are solely those of the author and do not necessarily represent those of Barclays. This e-mail is subject to terms available at the following link: www.barcap.com/emaildisclaimer. By messaging with Barclays you consent to the foregoing. Barclays Capital is the investment banking division of Barclays Bank PLC, a company registered in England (number 1026167) with its registered office at 1 Churchill Place, London, E14 5HP. This email may relate to or be sent from other members of the Barclays Group.
_______________________________________________

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

Philip Sevetson

Re: HP Unload Question re: Date Fields
(in response to Michael Dransfield)
Mike, DFSORT/Syncsort can be used to reformat records in the way you desire without more complex programming. And it's blindingly fast.

--Phil

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Mike Dransfield
Sent: Tuesday, December 22, 2009 2:51 PM
To: [login to unmask email]
Subject: Re: [DB2-L] HP Unload Question re: Date Fields

Dave,

Yes, Dan was correct.

The data is being FTPed to an accounting firm, who is likely loading it
into a non-DB2 platform. It could be Sybase, Oracle or MS SQL Server. I
don't have direct contact with them.

The accounting firm has less appetite than I do for writing a program to
strip off the extra x'00' occurrences.

Thanks to you and Dan for your help.

Best,
Mike

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Seibert, Dave
Sent: Tuesday, December 22, 2009 2:33 PM
To: [login to unmask email]
Subject: Re: [DB2-L] HP Unload Question re: Date Fields

Hello Mike.

I suspect Dan has a good guess at the explanation.

What is your intent for the output flat file? That will help us
understand your concern with eliminating the x'00's.

Dave

The contents of this e-mail are intended for the named addressee only.
It contains information that may be confidential. Unless you are the
named addressee or an authorized designee, you may not copy or use it,
or disclose it to anyone else. If you received it in error please notify
us immediately and then destroy it.

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Mike
Dransfield
Sent: Tuesday, December 22, 2009 10:54 AM
To: [login to unmask email]
Subject: [DB2-L] HP Unload Question re: Date Fields

Hey, All,

I'm running HP Unload against an image copy to produce a flat file. The
output data is delimited with x'1D' (chosen because that's no present in
the
data).

In the output dataset, date data are being preceded by x'00' for some
reason, and I can't seem to eliminate them. Here's what I've tried:

In my first attempt, I just did the normal

UNLOAD TABLESPACE...
SELECT * FROM...
OUTDDN (SYSREC)
FORMAT USER (reformatting 4 decimal columns to alpha)
FORMAT DELIMITED SEP X'1D'

Result: date shows up in mm-dd-yyyy format as desired, but with x'00'
after
previous column's delimiter and before the date.

Next attempt:

In my FORMAT USER statement, I tried explicitly specifying the column as

TYPE DATE.

Result: no more hex '00', but the date data now shows up in the flat
file in hex
format.

Next attempt:

In my FORMAT USER statement, I specified the column as TYPE CHAR(10)

Result: date shows up in mm-dd-yyyy format as desired, but again with
the
leading x'00'.

Next attempt:

Eliminated date column from FORMAT USER statement, and added
OPTIONS DATE DATE_A before the SELECT statement.

Result: date shows up mm-dd-yyyy format as desired, but again with the
leading x'00'.

I would prefer to not handle this by running it through a program
afterwards to
strip out the x'00', as I have many tables to unload in this manner.

Environment specifics:

z/OS 1.9
DB2 V8 c/m
DB2 HP UNLOAD V2.2

Any help, suggestions, or advice would be greatly appreciated and
graciously accepted. If more info is needed, please let me know.


Kind Regards,
Mike D.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions
Journal
Technical atricles from world famous authors in DB2's most prestigious,
peer reviewed
magazine now on-line!
_____________________________________________________________________

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

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions
Journal
Technical atricles from world famous authors in DB2's most prestigious,
peer reviewed
magazine now on-line!
_____________________________________________________________________

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

This e-mail may contain information that is confidential, privileged or otherwise protected from disclosure. If you are not an intended recipient of this e-mail, do not duplicate or redistribute it by any means. Please delete it and any attachments and notify the sender that you have received it in error. Unless specifically indicated, this e-mail is not an offer to buy or sell or a solicitation to buy or sell any securities, investment products or other financial product or service, an official confirmation of any transaction, or an official statement of Barclays. Any views or opinions presented are solely those of the author and do not necessarily represent those of Barclays. This e-mail is subject to terms available at the following link: www.barcap.com/emaildisclaimer. By messaging with Barclays you consent to the foregoing. Barclays Capital is the investment banking division of Barclays Bank PLC, a company registered in England (number 1026167) with its registered office at 1 Churchill Place, London, E14 5HP. This email may relate to or be sent from other members of the Barclays Group.
_______________________________________________

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

Adam Baldwin

Re: HP Unload Question re: Date Fields
(in response to Philip Sevetson)
Mike, if the x'00' is as result of a nullable column you can use the NULLID or
NULL keywords in your USER block to handle that. The default for NULLID is
NO, so you shouldn't be getting a preceding null indicator unless you've
maybe got it set to yes in your Options or elsewhere.

Regards, Adam

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

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