Hi John,
John O'Gorman [mailto:john.ogorman@stripped] wrote:
>
> Thank you Steffen. That answers my question perfectly. Some small
> examples just
> like yours would be a valuable addition to the documentation!
>
Yes, I know. And I've already promised to do. But the time goes
by and no time is left for documentation. It'll get a more
important issue.
> Topic 2 - loading aberrant DATE fields
>
> Informix supply a toy database (in their meaning of database:
> collection of tables
> with data) called STORES. This allows you to load data and play with
> SQL.
> The stores data is UNLOADed in America, where they apparently
> represent dates in
> MM/DD/YY format. To get these into SAPDB, I have been writing Perl
> scripts to
> substite the re patterns for MM/DD/YY into (19|20)YYMMDD. Is
> there a way to
> get repmcli DATALOAD to do this for me? (If so a minimal example would
> be much appreciated!).
>
Hmm, the problem is the 19|20 stuff.
Date patterns in general may be
given to commands in 2 ways (this is true for time and timestamp, too):
1. Predefined patterns can be used like USA (MM/DD/YYYY) or ISO (YYYY-MM-DD).
Those can either be specified using the 'SET DATE <pattern name>' command
(for instance SET DATE ISO) and are valid for the whole command file until
reset.
Or they can be specified for a certain LOAD/EXTRACT command where they
overwrite the global setting like this
DATALOAD TABLE <table name>
<col name> <pos>
...
INFILE '<infile name>'
DATE ISO
2. Date patterns different from predefined may only be specified in commands
directly. So you would use this command to load MM/DD/YY dates:
DATALOAD TABLE <table name>
<col name> <pos>
...
INFILE '<infile name>'
DATE 'MM/DD/YY'
Ok, now the problem. Using a 2-digit year representation will always be transformed
into 20YY by the Loader server (only partially true - if you're using 7.3
version there is still a bug and Loader server will transform it into 19YY).
So is there any possibility to 'convince the Informix tools' to store the dates
as 4-digit representation? How did they handle the Y2K bug?
> If the date load would be possible except for the 2 digit
> year, would it be possible
> to load dates represented as ASCII MM/DD/YYYY ?
>
Hmm, I should read to the very end before answering in detail.
Yes it is. Here is the command example from above:
DATALOAD TABLE <table name>
<col name> <pos>
...
INFILE '<infile name>'
DATE USA
Regards,
Steffen
--
Steffen Schildberg
SAP DB Team
SAP Labs Berlin
