Results 1 to 13 of 13
  1. #1
    Join Date
    May 2006
    Posts
    9

    Lightbulb Unanswered: DB2 Load Utility and Default Values for some columns

    DB2 Load Utility and Default Values for some columns

    --------------------------------------------------------------------------------

    Hi All,

    I am new to DB2 and need some information on DB2 LOAD utility.

    I have a table with 2 extra columns more then the file Specification and I need to specify a CONSTANT to pass to this 2 extra columns
    1) a SYSTEM Date and
    2) a Constant String 'FILE Z' and this constant depends on the file I am going to loading.

    Also, One of the Column has a Date FORMAT of 'DD/MM/YYYY', while the datatype of the column is TIMESTAMP.


    Any Help is appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Arrow Load

    1. Specify the table including the extra colums
    2. LOAD into the (-2) columns using the REPLACE keyword
    3. UPDATE your remaining columns

    Good luck
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  3. #3
    Join Date
    May 2006
    Posts
    9

    Lightbulb

    Thank you for the information...

    Can you please provide me with a example....

    Thank you....

  4. #4
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Arrow Remembrance

    I forgot you can also use the DATEFORMAT keyword to specify
    your input date format

    Concerning examples i have tonnes - but shouldn't you rather read
    the LOAD statement syntax at IBM's homepage ?
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  5. #5
    Join Date
    May 2006
    Posts
    9

    Lightbulb

    I am very very new to DB2 and have no idea about how to set the default string text on a VARCHAR column and
    system date on a timestamp column.

  6. #6
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Arrow

    update table myschema.mytable set mycolumn = values(current timestamp)
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  7. #7
    Join Date
    May 2006
    Posts
    9
    I am sorry... it is possible to have

    1) a current timestamp set in the control file( file Specification ) and not am update after the data is loaded.
    2) a Constant String 'FILE Z' in the control file ( file Specification ) and not an update after the data is loaded.

  8. #8
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Arrow Hmmm

    Per principle you can load anything

    But what is you 'control file' ?
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  9. #9
    Join Date
    May 2006
    Posts
    9
    example of a control file
    ________________________

    load data
    infile 'example.dat' "fix 11"
    into table example
    fields terminated by ',' optionally enclosed by '"'
    (col1 char(5) "CURRENT TIMESTAMP" ,
    col2 char(7) "Z FILE)

  10. #10
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Arrow Example

    LOAD FROM "/saswork/tmp/531.csv" OF DEL MODIFIED BY COLDEL; decpt, DATESISO FASTPARSE
    METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33
    34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67
    68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90)
    WARNINGCOUNT 20 MESSAGES "/home/db2inst2/autlog.txt" INSERT INTO kso.t_sr_531
    (v_cpr, v_navn1, v_navn2, c_cprstatus, c_statsborger,
    d_adrbeskyt, d_cprdato, v_adr1, v_adr2, v_adr3,
    v_postnr, v_autid, c_uddland, c_autlandgrp, d_fraskriv,
    d_fratag, d_generhverv, d_autdato, c_skole) NONRECOVERABLE INDEXING MODE DEFERRED@
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  11. #11
    Join Date
    May 2006
    Posts
    9
    is it not possible to have some like this as show below to set the DEFAULT value

    LOAD FROM "/saswork/tmp/531.csv" OF DEL MODIFIED BY COLDEL; decpt, DATESISO FASTPARSE
    METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33
    34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67
    68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90)
    WARNINGCOUNT 20 MESSAGES "/home/db2inst2/autlog.txt" INSERT INTO kso.t_sr_531
    (v_cpr, v_navn1, v_navn2, c_cprstatus, c_statsborger,
    d_adrbeskyt, d_cprdato, v_adr1, v_adr2, v_adr3,
    v_postnr, v_autid, c_uddland, c_autlandgrp, d_fraskriv,
    d_fratag, d_generhverv,
    d_autdato "CURRENT TIMESTAMP"
    , c_skole "Z FILE")

    NONRECOVERABLE INDEXING MODE DEFERRED@

  12. #12
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Arrow Good question !!

    I actually don't know !

    I can see it would have been nice !!

    Lets ask the super moderator :-)
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  13. #13
    Join Date
    May 2006
    Posts
    9
    thank you...

Posting Permissions

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