How do you handle DB2 discards?

Mark Kerner

How do you handle DB2 discards?
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 at http://www.idug.org/lsms

Avram Friedman

Re: How do you handle DB2 discards?
(in response to Mark Kerner)
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 at
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

Mark Finnell

Re: How do you handle DB2 discards?
(in response to Avram Friedman)
Mark,

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]>


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: Friday, January 25, 2008 2:36 PM
To: [login to unmask email]
Subject: [DB2-L] How do you handle DB2 discards?



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 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

Randy Bright

Re: How do you handle DB2 discards?
(in response to Mark Finnell)
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

David Seibert

Re: How do you handle DB2 discards?
(in response to Randy Bright)
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.


From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: Friday, January 25, 2008 3:36 PM
To: [login to unmask email]
Subject: [DB2-L] How do you handle DB2 discards?



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 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

Mark Kerner

Re: How do you handle DB2 discards?
(in response to David Seibert)
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).


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.




"Seibert, Dave" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/28/2008 10:47 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] How do you handle DB2 discards?






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.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of [login to unmask email]
Sent: Friday, January 25, 2008 3:36 PM
To: [login to unmask email]
Subject: [DB2-L] How do you handle DB2 discards?


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 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