Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2005
    Posts
    21

    Unanswered: DB2 Load - Dates and nulls

    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?
    Last edited by gmesturini; 11-21-05 at 05:56.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Nov 2005
    Posts
    21
    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

  4. #4
    Join Date
    Nov 2005
    Posts
    21
    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...

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  6. #6
    Join Date
    Nov 2005
    Posts
    21
    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 :-)

  7. #7
    Join Date
    Nov 2005
    Posts
    21
    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?...
    Last edited by gmesturini; 11-24-05 at 06:01.

  8. #8
    Join Date
    Nov 2005
    Posts
    21
    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?

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Have you tried filling the null dates with spaces instead of "+"s?

  11. #11
    Join Date
    Nov 2005
    Posts
    21
    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...
    Last edited by gmesturini; 11-24-05 at 10:36.

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What about this:
    There must be one entry in the null indicator list for each data field defined in the METHOD L parameter.
    Besides, isn't NULLINDCHAR case-sensitive?

  13. #13
    Join Date
    Nov 2005
    Posts
    21
    There must be one entry in the null indicator list for each data field defined in the METHOD L parameter.
    That is. Thanks once again.

    This modifier is case sensitive for EBCDIC data files, except when the character is an English letter. For example, if the NULL indicator character is specified to be the letter N, then n is also recognized as a NULL indicator.

  14. #14
    Join Date
    Nov 2005
    Posts
    21
    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?...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •