date/time conversion during load

Freddy Van der Elst

date/time conversion during load
Hello,

I want to load information from an IMS logrecord 07 into a DB2 table.
All goes fine except for the date and time. Those fields are defined in
the logrecord as following:
LOG7DATE DS 0CL4 date YYYYDDDF
LOG7TIME DS 0CL6 time HHMMSSTHMIJU
LOGSTIME DS 0CL4 time HHMMSSTH

an example of the data in logrec7: x"2003360F141531597028"

I want to get the date in a usable format (ddmmyyyy). I tried several
load's with several definitions of the date/timestamp but without any
succes. Any idea how to get the date during LOAD in a readable format
and how to define the field in the loadstmnt and the DDL.

Thanks,
Freddy Van der Elst

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Avram Friedman

Re: date/time conversion during load
(in response to Freddy Van der Elst)
You are correct,
This timestamp has an unusual format and requires special handling.
Options are
Write your own load program
Write an EDITPROC for the timestamp field
Treat the date portion as packed decimal and time as an integer convert after load
Preprocess the log file converting the field
Use a third part product
the old IBM SLR had a special programs to handle this TS for example
Probally code in MXG to use SAS in converting this value


At any rate the whole issue of using this data caused a a large number of software companyies to develop very profitable businesses. Customers are rarly happy with using the type07 (program accounting record) for accounting because it reperesents all the transactions dequed by a single scheduling and not a particular transactions.

I designed a lot of IMS log postprocessors (Candle's RTA for example). If you want to exchange ideas about log post processing feel free to write me out side this mailling list, I am concerned that a on going IMS thread may be a bit too far off topic for the board.

Freddy Van der Elst wrote:

> Hello,
>
> I want to load information from an IMS logrecord 07 into a DB2 table.
> All goes fine except for the date and time. Those fields are defined in
> the logrecord as following:
> LOG7DATE DS 0CL4 date YYYYDDDF
> LOG7TIME DS 0CL6 time HHMMSSTHMIJU
> LOGSTIME DS 0CL4 time HHMMSSTH
>
> an example of the data in logrec7: x"2003360F141531597028"
>
> I want to get the date in a usable format (ddmmyyyy). I tried several
> load's with several definitions of the date/timestamp but without any
> succes. Any idea how to get the date during LOAD in a readable format
> and how to define the field in the loadstmnt and the DDL.
>
> Thanks,
> Freddy Van der Elst
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

--
NOTICE: If received in error, please destroy and notify sender. Sender does not waive confidentiality or privilege, and use is prohibited.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm