A number of our DB2 tables are sourced from IMS data. Most of
these
tables are populated as LOAD REPLACE and allow for a certain number
of
discards (not sure why, but they do). Herein lies the problem,
because as
all DBA?s know, the data should be correct at its source. However,
there
are at times a handful of records that are discarded due to
date
conversion errors. IMS does not check to see that a value populated
into
a date field is actually formatted properly. I?ve found everything
from
spaces to zeros to invalid dates such as 20040431, and other
packed
garbage in columns that DB2 expects to see defined as DATE.
And,
according to their definition, they cannot be NULL either.

How do your shops handle these situations?

1- One approach I am thinking of taking is to define these columns
in the
LOAD control statement with a DEFAULTIF ERROR clause, which in the
case of
a date will default to the date of the load. I will do this only
after
notifying as many users of the table as I can find. As least the
row of
data won?t be lost at the cost of a bad date.

2- If the DB2 column does allow NULL?s, I am not comfortable
loading using
the NULLIF ERROR clause because of how nulls are treated by DB2. In
this
case I am more prone to have someone examine the offending source
programs
and correct the data prior to presenting it to the DB2 load.

3- Are there any other criteria I can use in the LOAD statement to
tailor
the values I want populated in DB2? Can I have DB2 load a date
of
2004-04-30 every time I encounter a value of 2004-04-31? Am I
limited to
the DEFAULTIF and NULLIF clauses to initialize values?

Please note: The information contained in this message may be
privileged
and confidential and protected from disclosure. If the reader of
this
message is not the intended recipient, or an employee or agent
responsible
for delivering this message to the intended recipient, you are
hereby
notified that any dissemination, distribution or copying of
this
communication is strictly prohibited. If you received this
communication
in error, please notify us immediately by replying to this message
and
deleting it from your computer. Thank you. Pitney Bowes, Inc.

The IDUG DB2-L Listserv is only part of your membership in IDUG.
DB2-L list archives, the 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

This seems to be a case of over enginered table design.
A field that seems to contain a date in a non DB2 platform like IMS
should not
be automaticly maped to a timestamp field in DB2.

It was only yesterday that there was a discussion about DB2 EXPLAIN
and the
Plan Table where a lister pointed out that in some older releases
of DB2 fields
that appeared to be TIMESTAMPS were not because DB2 did not
support
TIMESTAMPS from the get go.

How are these problem matic fields defined to IMS via the DBD, if
at all, Note
in general it is not an IMS requirement that all fields be defined
... only
Sequence and Key fields.

If the fields are not defined in the DBD how are the defined in the
copy books
or DSECTS that reference them? a COBOL structure like
07 DATE.
09 YEAR PIC X(4).
09 MONTH PIC XX.
09 DAY PIC XX.

Should not be mapped to a time stamp column no matter how much you
think
that it sort of looks like a date.

Regards
Avram Friedman

On Fri, 25 Jan 2008 15:36:29 -0500, [login to unmask email] wrote:

>A question for a Friday...
>
>At the present time we are running DB2 V7.1 with z/OS V1.7.
>
>A number of our DB2 tables are sourced from IMS data. Most of
these
>tables are populated as LOAD REPLACE and allow for a certain
number of
>discards (not sure why, but they do). Herein lies the problem,
because as
>all DBA?s know, the data should be correct at its source.
However, there
>are at times a handful of records that are discarded due to
date
>conversion errors. IMS does not check to see that a value
populated into
>a date field is actually formatted properly. I?ve found
everything from
>spaces to zeros to invalid dates such as 20040431, and other
packed
>garbage in columns that DB2 expects to see defined as DATE.
And,
>according to their definition, they cannot be NULL either.
>
>How do your shops handle these situations?
>
>1- One approach I am thinking of taking is to define these
columns in the
>LOAD control statement with a DEFAULTIF ERROR clause, which in
the case
of
>a date will default to the date of the load. I will do this
only after
>notifying as many users of the table as I can find. As least
the row of
>data won?t be lost at the cost of a bad date.
>
>2- If the DB2 column does allow NULL?s, I am not comfortable
loading using
>the NULLIF ERROR clause because of how nulls are treated by
DB2. In this
>case I am more prone to have someone examine the offending
source
programs
>and correct the data prior to presenting it to the DB2
load.
>
>3- Are there any other criteria I can use in the LOAD statement
to tailor
>the values I want populated in DB2? Can I have DB2 load a date
of
>2004-04-30 every time I encounter a value of 2004-04-31? Am I
limited to
>the DEFAULTIF and NULLIF clauses to initialize values?
>
>Thanks for your input.
>
>
>Mark L. Kerner
>
> Tech Central/ Database Services
> 1 Elmcroft Road
> Mail Drop 54-24
> Stamford, CT 06926-0700
> Phone: (203) 351-6637 (Internal 440-6637)
> Fax: (203) 546-4833
> Remedy Group: TC.EDSS.Database
>
>Please note: The information contained in this message may be
privileged
>and confidential and protected from disclosure. If the reader
of this
>message is not the intended recipient, or an employee or agent
responsible
>for delivering this message to the intended recipient, you are
hereby
>notified that any dissemination, distribution or copying of
this
>communication is strictly prohibited. If you received this
communication
>in error, please notify us immediately by replying to this
message and
>deleting it from your computer. Thank you. Pitney Bowes,
Inc.
>
>
>The IDUG DB2-L Listserv is only part of your membership in
IDUG. DB2-L list
archives, the 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 athttp://www.idug.org/lsms

The IDUG DB2-L Listserv is only part of your membership in IDUG.
DB2-L list archives, the 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

In our data warehouse we handle situations like this by writing
an
intermediate program to "fix" data. We experience problems with
dates
(among other things) from IDMS so if we encounter 2004-04-31 we
change
it to 2004-04-30 in our transformation program before we LOAD.
Sorry, I
don't have any good LOAD utility tricks for handling this.
-----------------------------------------------
Mark E. Finnell
Database Administration Team Lead
Information Technology Services Division
State of Missouri
1621 East Elm Street
Jefferson City, MO 65101
573-751-2167 (voice)
573-751-2026 (fax)
[login to unmask email]
<blocked::mailto:[login to unmask email]>

A number of our DB2 tables are sourced from IMS data. Most of
these
tables are populated as LOAD REPLACE and allow for a certain number
of
discards (not sure why, but they do). Herein lies the problem,
because
as all DBA's know, the data should be correct at its source.
However,
there are at times a handful of records that are discarded due to
date
conversion errors. IMS does not check to see that a value
populated
into a date field is actually formatted properly. I've found
everything
from spaces to zeros to invalid dates such as 20040431, and other
packed
garbage in columns that DB2 expects to see defined as DATE.
And,
according to their definition, they cannot be NULL either.

How do your shops handle these situations?

1- One approach I am thinking of taking is to define these columns
in
the LOAD control statement with a DEFAULTIF ERROR clause, which in
the
case of a date will default to the date of the load. I will do
this
only after notifying as many users of the table as I can find. As
least
the row of data won't be lost at the cost of a bad date.

2- If the DB2 column does allow NULL's, I am not comfortable
loading
using the NULLIF ERROR clause because of how nulls are treated by
DB2.
In this case I am more prone to have someone examine the
offending
source programs and correct the data prior to presenting it to the
DB2
load.

3- Are there any other criteria I can use in the LOAD statement
to
tailor the values I want populated in DB2? Can I have DB2 load a
date
of 2004-04-30 every time I encounter a value of 2004-04-31? Am
I
limited to the DEFAULTIF and NULLIF clauses to initialize
values?

Please note: The information contained in this message may be
privileged
and confidential and protected from disclosure. If the reader of
this
message is not the intended recipient, or an employee or agent
responsible for delivering this message to the intended recipient,
you
are hereby notified that any dissemination, distribution or copying
of
this communication is strictly prohibited. If you received this
communication in error, please notify us immediately by replying to
this
message and deleting it from your computer. Thank you. Pitney
Bowes,
Inc.

The IDUG DB2-L Listserv is only part of your membership in IDUG.
DB2-L
list archives, the FAQ, and delivery preferences are at
www.idug.org
< 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
< http://www.idug.org/lsconf > ,
and much more.
If you have not yet signed up for Basic Membership in IDUG,
available at
no cost, click on Member Services < http://www.idug.org/lsms >

The IDUG DB2-L Listserv is only part of your membership in IDUG.
DB2-L list archives, the 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

If the data item that is causing the discard when loading into the
DB2
table is not an important piece of information, then you should be
able
to set a default value as you describe with DEFAULTIF or NULLIF.
But,
if it isn't important, why do you even have it in the database?
Since
the data is bad in IMS, and applications using the IMS data do not
seem
to have any problem with it, maybe it is unused or unimportant data
and
setting it to a default value in DB2 is perfectly fine.

If the information is critical to the DB2 applications, then you
have a
more severe problem. What value is correct for "20040431"? Should
it
be changed to "20040430" or "20040501"? Is it significant which
month
the data is recorded in? A data cleansing program may need to
be
written that intelligently resets the bad values based on other
information in the record. For example if the "20040431" date
is
supposed to be 10 days after another date in the same record and
that
date is "20040421", then it becomes obvious the corrected value
should
be "20040501". Likewise, if it is supposed to be 10 days before
another
date that is "20040510", then the obvious correct value is
"20040430".

Your best solution is to determine what application is putting the
bad
values in the IMS data and fix it. Then cleanse the IMS database of
the
bad data using an intelligent program like I described above. Now
your
DB2 problems are gone. If cleansing the IMS data is too costly,
then
the cleansing program will have to be a pre-DB2 load process.
Either
way, I think you are going to have to write code based on your
application's requirements to fix the data. If it is important
data,
you may not be able to just set an arbitrary value when an error
occurs.

This is just my opinion, by the way.

Randy Bright

Development Architect

DB2 Utilities

BMC Software, Inc.

[login to unmask email]

The IDUG DB2-L Listserv is only part of your membership in IDUG.
DB2-L list archives, the 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

I'm curious, Mark.
What makes you uncomfortable about the way DB2 handles nulls?

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.

A number of our DB2 tables are sourced from IMS data. Most of
these
tables are populated as LOAD REPLACE and allow for a certain number
of
discards (not sure why, but they do). Herein lies the problem,
because
as all DBA's know, the data should be correct at its source.
However,
there are at times a handful of records that are discarded due to
date
conversion errors. IMS does not check to see that a value
populated
into a date field is actually formatted properly. I've found
everything
from spaces to zeros to invalid dates such as 20040431, and other
packed
garbage in columns that DB2 expects to see defined as DATE.
And,
according to their definition, they cannot be NULL either.

How do your shops handle these situations?

1- One approach I am thinking of taking is to define these columns
in
the LOAD control statement with a DEFAULTIF ERROR clause, which in
the
case of a date will default to the date of the load. I will do
this
only after notifying as many users of the table as I can find. As
least
the row of data won't be lost at the cost of a bad date.

2- If the DB2 column does allow NULL's, I am not comfortable
loading
using the NULLIF ERROR clause because of how nulls are treated by
DB2.
In this case I am more prone to have someone examine the
offending
source programs and correct the data prior to presenting it to the
DB2
load.

3- Are there any other criteria I can use in the LOAD statement
to
tailor the values I want populated in DB2? Can I have DB2 load a
date
of 2004-04-30 every time I encounter a value of 2004-04-31? Am
I
limited to the DEFAULTIF and NULLIF clauses to initialize
values?

Please note: The information contained in this message may be
privileged
and confidential and protected from disclosure. If the reader of
this
message is not the intended recipient, or an employee or agent
responsible for delivering this message to the intended recipient,
you
are hereby notified that any dissemination, distribution or copying
of
this communication is strictly prohibited. If you received this
communication in error, please notify us immediately by replying to
this
message and deleting it from your computer. Thank you. Pitney
Bowes,
Inc.

The IDUG DB2-L Listserv is only part of your membership in IDUG.
DB2-L
list archives, the FAQ, and delivery preferences are at
www.idug.org
< 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
< http://www.idug.org/lsconf > ,
and much more.
If you have not yet signed up for Basic Membership in IDUG,
available at
no cost, click on Member Services < http://www.idug.org/lsms >

The IDUG DB2-L Listserv is only part of your membership in IDUG.
DB2-L list archives, the 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

Hi Dave,
Thanks for your response.
I may have misspoken.
It's not the way that DB2 handles nulls, but the way programmers
will or
won't get data returned to them if a date in a row happens to be
null.
(Or, perhaps it's MY understanding of what will happen).

Please note: The information contained in this message may be
privileged
and confidential and protected from disclosure. If the reader of
this
message is not the intended recipient, or an employee or agent
responsible
for delivering this message to the intended recipient, you are
hereby
notified that any dissemination, distribution or copying of
this
communication is strictly prohibited. If you received this
communication
in error, please notify us immediately by replying to this message
and
deleting it from your computer. Thank you. Pitney Bowes, Inc.

I'm curious, Mark.
What makes you uncomfortable about the way DB2 handles nulls?
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.

A number of our DB2 tables are sourced from IMS data. Most of
these
tables are populated as LOAD REPLACE and allow for a certain number
of
discards (not sure why, but they do). Herein lies the problem,
because as
all DBA?s know, the data should be correct at its source. However,
there
are at times a handful of records that are discarded due to
date
conversion errors. IMS does not check to see that a value populated
into
a date field is actually formatted properly. I?ve found everything
from
spaces to zeros to invalid dates such as 20040431, and other
packed
garbage in columns that DB2 expects to see defined as DATE.
And,
according to their definition, they cannot be NULL either.

How do your shops handle these situations?

1- One approach I am thinking of taking is to define these columns
in the
LOAD control statement with a DEFAULTIF ERROR clause, which in the
case of
a date will default to the date of the load. I will do this only
after
notifying as many users of the table as I can find. As least the
row of
data won?t be lost at the cost of a bad date.

2- If the DB2 column does allow NULL?s, I am not comfortable
loading using
the NULLIF ERROR clause because of how nulls are treated by DB2. In
this
case I am more prone to have someone examine the offending source
programs
and correct the data prior to presenting it to the DB2 load.

3- Are there any other criteria I can use in the LOAD statement to
tailor
the values I want populated in DB2? Can I have DB2 load a date
of
2004-04-30 every time I encounter a value of 2004-04-31? Am I
limited to
the DEFAULTIF and NULLIF clauses to initialize values?

Please note: The information contained in this message may be
privileged
and confidential and protected from disclosure. If the reader of
this
message is not the intended recipient, or an employee or agent
responsible
for delivering this message to the intended recipient, you are
hereby
notified that any dissemination, distribution or copying of
this
communication is strictly prohibited. If you received this
communication
in error, please notify us immediately by replying to this message
and
deleting it from your computer. Thank you. Pitney Bowes, Inc.

The IDUG DB2-L Listserv is only part of your membership in IDUG.
DB2-L
list archives, the FAQ, and delivery preferences are at
www.idug.org 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

The IDUG DB2-L Listserv is only part of your membership in IDUG.
DB2-L
list archives, the FAQ, and delivery preferences are at
www.idug.org 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

The IDUG DB2-L Listserv is only part of your membership in IDUG.
DB2-L list archives, the 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