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.

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

Thank you for the information...

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

Thank you....

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 ?

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.

update table myschema.mytable set mycolumn = values(current timestamp)

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.

Per principle you can load anything

But what is you 'control file' ?

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)

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@

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@

I actually don't know !

I can see it would have been nice !!

Lets ask the super moderator :-)

thank you...