DB2 8.2 Win/DB2 8.2 AIX

I'm writing a Java converter for dates expressed like DDMMYYYY in DD/MM/YYYY to make them Load compilant. However sometimes dates in my source data files are expressed in 00000000 that means that the date is NULL. What must I write to make the Load utility read a NULL date, in respect of the positional map of the file? Maybe a 'NULL ' (with 4 blanks) to fill the position of the date field?

You are of course free to write any converter but I'd suggest you look at the following LOAD modifiers: "dateformat" and "nullindchar"; it may save you few days of work.

thank you n_i... i found it now that you told me how these modifiers are named... i feel a great need of a reference to the IBM references thank you

a last question...

some date fields in my source data file are expressed in DDMMYY (assuming that all years are between 2000 and 2100, so that 251205 is 25 of dicember 2005). there is some way to make this digestive for the dateformat load modifier?...

the pattern DDMMYY is invalid... i'm dreaming something about DDMM20YY...

Somebody seems to be getting ready for the "Y2100 problem" and the associated consulting revenues :-)

It looks like you _will_ have to doctor your input files. Being on AIX I guess your best choice in terms of simplicity and performance would be awk or perl rather than Java though.

i only think that expressing an year with only YY it's a loss of necessary information. but it seems that someone doesn't think so... i'll try to repair with java, and if i'll see that performance decreases dramatically, i'll turn on awk or perl. thank you once again n_i, you're becoming my personal idol :-)

clarification in "nullindchar" needed. i have to set up some dates to null. in my source file are expressed in DDMMYYYY with value 00000000. whithout specifing any nullindchar, Load util give me a warning, telling me that "the value for date/time is too low, a NULL will be inserted". it's my final goal, but i don't like to rely on an error to reach my goal... so i've tried to change my 00000000 value to a Y0000000, specifing NULLINDCHAR=Y and the NULL INDICATORS list for the offset of the first char of the date... but it doesn't work... it gave me a SQL0180N SQLSTATE=22007 error (date systax). what is the correct sintax?...

At the address http://www-128.ibm.com/developerwork...ml#nullindchar I found the following example:

Given import data file namesdata.asc with content:
Rosemary+++++++++++++++n:
create table names (firstname varchar(12), lastname varchar(12));
import from namesdata.asc of asc modified by nullindchar=N method l (1 12, 13 24) null indicators (12, 24) messages import.msg insert into names;

So I've tried the following:

Given import data file EF.txt with content:
2005-11-24 14:07:09.461000 EF05437A249318102005 0000001 0000039 +++++++n
Warn: blanks are trimmed in the post, but in the original content there are some fillers, and the map 143 150 is correct for the last value.

CREATE TABLE NCH_RND_SMCC_CODA (
TSFLUSSO TIMESTAMP,
MITTENTE CHAR(5),
RICEVENTE CHAR(5),
DATACREAZIONE DATE,
NOMESUPPORTO CHAR(20),
NUMRENDICONTAZIONI INTEGER,
NUMRECORD INTEGER,
GIORNAPPLICAT DATE
);

load from D:\Sviluppo\DESTINATION\EF.txt of asc MODIFIED BY DATEFORMAT="DDMMYYYY" NULLINDCHAR=N method L (1 26, 30 34, 35 39, 40 47, 48 67, 74 80, 111 117, 143 150) null indicators (47, 150) insert into NCH_RND_SMCC_CODA (TSFLUSSO, MITTENTE, RICEVENTE, DATACREAZIONE, NOMESUPPORTO, NUMRENDICONTAZIONI, NUMRECORD, GIORNAPPLICAT);

where the field GIORNAPPLICAT (the last one) is the NULL DATE. But when Load "meets" the value at "143 150" it gives me a sintax error for invalid date. I know, it's an Import versus a Load, but the usage of the NULLINDCHAR documented by IBM is the same for the two commands.

Any idea?

Most people use a "-" as the null indicator value because that is what is output for null on an SQL query from the command line.

Have you tried filling the null dates with spaces instead of "+"s?

right now: the error changes but the concept is the same. Now the error is

SQL3191N The field in the row "1", column "143" that starts with "<SEVEN BLANKS>n" does not correspond at the format DATEFORMAT, TIMEFORMAT or TIMESTAMPFORMAT specified by the user. The row will be discard.

(I don't know if I wrote the error correctly, because I have a f*****g DB2 in Italian and not in English)

I think the concept is the same: Load is ignoring my NULLINDCHAR settings...

What about this:Besides, isn't NULLINDCHAR case-sensitive?

That is. Thanks once again.

besides, Load should recognize this kind of error giving a message more comprehesible like "the numer of null indicators must be equal to the number of columns"... don't you agree?...