Hi All,
I have a file that has data from Oracle in a spreadsheet. and that spread sheet has 4 columns of data. where as the table in db2 has 7 columns with the 3 other columns given as not null. so i have to load the data from spreadsheet to the db2 table.

In the 3 other columns I have to give the default user id and current timestamp.

So, can anyone please give me solutions to load the data into db2 table.

Thanks much.

DB2 Version: DB2 v9.1.0.5

OS LEVEL: AIX 5.3

It looks like you have several options:

1) re-export the data from Oracle with 7 columns instead of 4
2) edit the export file to have 3 more columns
3) create a new table in DB2 with 4 columns, import into that, then insert from there to your other table.

Andy

If those 3 more columns take default user id and current timestamp at the time of loading, why not rebuild the table and make those columns NOT NULL WITH DEFAULT .....

Problem solved.

Hi Andy,
Thanks for your reply.
1) The oracle table has only 4 fileds. These 4 fileds should go into my new db2 table and the rest 3 should be unique default value of userid name and current timestamp of the time when the data is getting inserted.

2)How can I add the current timestamp of the system in the exported file as for the inserts in db2 if values are given with current_timestamp this value is generated by the system.

3)The target table in db2 has 7 columns so I have to do this at once..

Please let me know your thoughts. I think the above points makes sense..

Thanks Much..

I'd personally also go for option (4), i.e., Cougar's suggestion, where you just make sure that those three additional columns have the appropriate NOT NULL WITH DEFAULT.

Thanks much for ur replies..

I agree with that.. but these tables are created long back.. and also I have to follow similar steps for alll the tables (approx 15 tables ) which is like dropping everything creating new..

Please let me know your thoughts..

Thanks.

1) you would supply the other 3 columns during the export. This assumes you know what the values are. For "Current timestamp", can't it just be some value, instead of the time the rows are inserted?

2) Does it have to be current timestamp? Can't it be something close?

3) I think you missed what I said. I meant this:
A) Create a new table in DB2 with the 4 columns like the table in Oracle
B) Insert the data int this 4 column table
C) Run the following Command insert into Table_With_7_Columns select Col1,col2,col3,col4,Some_Value_you_Supply, current timestamp, some_other_Value_you_supply from new_table_with_4_columns.

Andy

Look at it from the cost perspective. rebuild those 15 tables with new 3 columns that are defined as NOT NULL WITH DEFAULT should take you no longer then 2 hours of a one time investment. And problem is solved once and for all.

Or you can go ahead and spend more time trying to find a solution that will surely be more costly; or you can build more new table as Andy suggested, not that there is anything wrong with it. I just think that would add more complexity and maintenance and other not so fun stuff that goes with it in the long run.