I have 2 csv files that contains 4 cols(c1,c2,c3,c4) and created a table that contain 5 columns (a1,a2,a3,a4,a5). Now I want to load those two files into the tables separately such that for each time I can have a contant value that goes in a1 column of the table.
Values in csv file 1
c1,c2,c3,c4
............
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
Values in csv file 2
c1,c2,c3,c4
............
5 6 7 8
5 6 7 8
5 6 7 8
5 6 7 8
resulting table should be
a1, a2, a3, a4 ,a5
..................
my_value1 1 2 3 4
my_value1 1 2 3 4
my_value1 1 2 3 4
my_value1 1 2 3 4
my_value2 5 6 7 8
my_value2 5 6 7 8
my_value2 5 6 7 8
my_value2 5 6 7 8
I tried this but clearly doesn't work and I read the load documentation from the IBM site but I wasn't able to find anything.
load from path\file1 of del insert into table_name(my_value1, c1,c2,c3,c4)
load from path\file2 of del insert into table_name(my_value2, c1,c2,c3,c4)
You have got some good suggestions in the comments, so this is just another way to do it:
db2 "create table target(a1 varchar(20) not null, a2 int, a3 int, a4 int, a5 int)"
db2 "alter table target alter column a1 set default 'my_value1'"
db2 "load from ./f1.csv of del insert into target (a2,a3,a4,a5)"
db2 "alter table target alter column a1 set default 'my_value2'"
db2 "load from ./f2.csv of del insert into target (a2,a3,a4,a5)"
db2 "alter table target alter column a1 drop default"
db2 "select * from target"
A1 A2 A3 A4 A5
-------------------- ----------- ----------- ----------- -----------
my_value1 1 2 3 4
my_value1 1 2 3 4
my_value1 1 2 3 4
my_value1 1 2 3 4
my_value2 5 6 7 8
my_value2 5 6 7 8
my_value2 5 6 7 8
my_value2 5 6 7 8
8 record(s) selected.
You may also have a look at INGEST
-- create restart table
CALL SYSPROC.SYSINSTALLOBJECTS('INGEST', 'C', NULL, NULL);
INGEST FROM FILE f1.csv
FORMAT DELIMITED (
$a2 INT EXTERNAL,
$a3 INT EXTERNAL,
$a4 INT EXTERNAL,
$a5 INT EXTERNAL
)
INSERT INTO target (a1,a2,a3,a4,a5)
VALUES ('my_value1',$a2,$a3,$a4,$a5);
From my understanding it is almost as fast as load, but much more flexible. There is a comparision at:
Finally, there is a new kid in town named EXTERNAL TABLE. You have to enable a path for EXTBL_LOCATION in your db cfg. I.e:
db2 update db cfg using EXTBL_LOCATION /tmp
Then you can declare a curor:
db2 "declare c1 cursor for select 'myval1', a2, a3, a4, a5 from external '/tmp/f1.csv' (a2 int, a3 int, a4 int, a5 int) using (delimiter '|')"
For reasons unknown to me I could not get it to work with ',' as delimiter so I changed it in the file.
Now, you can load from that cursor:
db2 "load from c1 of cursor insert into target"
Redefine the cursor and load another file (I used the same one)
db2 "declare c1 cursor for select 'myval2', a2, a3, a4, a5 from external '/tmp/f1.csv' (a2 int, a3 int, a4 int, a5 int) using (delimiter '|')"
db2 "load from c1 of cursor insert into target"
db2 "select * from target"
A1 A2 A3 A4 A5
---------------------------------------- ----------- ----------- ----------- -----------
myval1 1 2 3 4
myval1 1 2 3 4
myval1 1 2 3 4
myval1 1 2 3 4
myval2 1 2 3 4
myval2 1 2 3 4
myval2 1 2 3 4
myval2 1 2 3 4
8 record(s) selected.