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