Stumped by dbload and incoming date format
To import data from a mainframe application I'm using dbload to populate the non-delimited data into tables where I can
then use application programs to process then next step. All appears to go well with the exception of date and time.
My incoming data resembles the following:
name CCYYMMDDHHSS
I've defined the informix table to look like:
create table date_test
(
my_name char(10)
, my_datetime datetime year to minute
)
I'm using a dbload script that looks like:
FILE date_test.dat
(
my_name 1-10
, my_datetime 11-22
)
;
INSERT INTO date_test
(
my_name
, my_datetime
)
VALUES
(
my_name
, my_datetime
);
What I see is the following:
informix@citation:/u/informix/sql> cat date_test.dat
PROSE 199901271119
informix@citation:/u/informix/sql> dbload -d fred -c date_test.in -l errlog
DBLOAD Load Utility INFORMIX-SQL Version 7.22.UC2
In INSERT statement number 1 of raw data file date_test.dat.
Row number 1 is bad.
PROSE 199901271119
Too many digits in the first field of datetime or interval.
Table date_test had 0 row(s) loaded into it.
informix@citation:/u/informix/sql>
---------------------------------
If I define the input as three fields, name, year, and time and define three fields in the table I can at least load the
date portion by setting the DBDATE environment variable. But I was stumped with the time field.
Note: from the dbload version number, you can see I'm on 7.22 so the 7.3 date functions aren't available.
I goofed on my format example. The data displayed in the dbload example is real. The format is:
XXXXXXXXXXCCYYMMDDHHMM
FProse wrote:
>
> To import data from a mainframe application I'm using dbload to populate the non-delimited data into tables where I can
> then use application programs to process then next step. All appears to go well with the exception of date and time.
>
> My incoming data resembles the following:
>
> name CCYYMMDDHHSS
[SNIP]
How about an awk or perl script to reformat the data to ANSI datetime
format. Here is a simple awk script to accomplish this:
{
name = substr( $0, 1, 10 );
year = substr( $0, 11, 4 );
month= substr( $0, 15, 2 );
day = substr( $0, 17, 2 );
hour = substr( $0, 19, 2 );
min = substr( $0, 21, 2 );
printf "%s%s-%s-%s %s:%s\\n", name, year, month, day, hour,
minute;
}
This will output:
NameXXXXXXCCYY-MM-DD HH:MM
Then the dbload script can become, in part:
FILE date_test.dat2
(
my_name 1-10,
my_datetime 11-26
)
;
INSERT ....
Art S. Kagel