Oppolzer - Informatik / Blog

LOAD UTILITY has a NULLIF clause, so you could load date values from CHAR(10)
text fields and assign NULL to the date columns, if you have blanks there (for
example).

No need to write a COBOL program to do the load (IMO) ... if that was the only
problem with the LOAD.

LOAD has many other options to deal with many different input sources; I would
always first check all these options and before writing a COBOL program to do
the load, I would consider writing a program to re-format the input files to
make them suitable for LOAD.

Kind regards

Bernd



Am 29.01.2015 um 13:34 schrieb T.W.:
>
> Thanks Bernd.
>
> Well there is quite a story about these tables and data. We have date columns
> in tables defined as CHAR(10) and not DATE as you would expect. At the start of
> this project we got these table layouts and ER diagram so our data analyst
> reverse engineered the DDL to create tables from. Then we find out that wasn't
> correct. So the application area was developing the COBOL programs to load the
> data because I could never get a DB2 utility load to work. Just a mess. I wrote
> this process where I took the COBOL FD's of the file layouts and I set up a
> matrix table to convert for example PIC X(10) to CHAR(10) and all of the decimal
> fields and we finally got the tables built, about 300. Then one day in a meeting
> I hear this chatter well a date could be nulls or spaces but somewhere in the
> file was the date in character format. By then my head is just spinning. Okay
> what other surprises do we have forth coming. I really can't say who the
> originaltor of the data is because I might get into trouble. It is wha t it is I
> guess and we deal with things as they come along. I don't know if this data
> source company converted everything from flat files to DB2 tables or what they
> did. For some tables the row length uncompressed only 1 row per 4K page would
> fit. Even compressed and going to 32K there aren't many rows per page.
>
> I was looking at one query the application area sent me and it was a simple
> join on client_no and ln_no but as soon as you added another result column where
> DB2 had to touch a data page to get the value the cost jumped maybe 10X or so.
> Both tables the row length was very long. Re-designing and normalizing tables
> isn't an option we have.