Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2009
    Posts
    36

    Unanswered: Load options in db2

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  4. #4
    Join Date
    Mar 2009
    Posts
    36
    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..

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Mar 2009
    Posts
    36
    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.

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by db2pro
    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..
    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

  8. #8
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by db2pro
    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.
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

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