Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    58

    Question Unanswered: Loading data from Oracle to DB2 table

    I need to load data from Oracle tables into DB2 tables.
    What are the procedures? Thanks in advance!

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Take a look at the IMPORT/LOAD command, and try to export it into one of the formats listed.

    I don't think Oracle supports IXF so one of the ascii formats should do it.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Jan 2004
    Location
    Tallahassee, FL, USA
    Posts
    96
    you can load data from Oracle to DB2 ,

    get data in dec file by using SQL statement, if table has date , put date in Db2 format , to_char( date,'YYYY:MMD') function





    example Otacle table is emp

    that has following structure


    id number
    name varchar2(30)
    sal number(7,2)

    data is like
    1 elr 1000
    2 msr 2000
    3 est 3000



    spool emp.dat
    select to_char(id)||chr(36)||chr(39)||name||chr(39)||to_c har(sal)
    from emp
    spool off

    emp.dat , will look like below
    1,"elr",1000
    2,"msr",2000
    3,"est",3000


    use emp.dat , load into db2 by using column delimeter,char delimeter

    load from emp.dat of del MODIFIED BY CHARDEL" COLDEL, replace into db2.emp


    not sure ascci value of " , number

    Let me know , if any questions


    Thank You


    Lekharaju Ennam

  4. #4
    Join Date
    Jan 2013
    Posts
    1
    hi

    can you tell me what shud be done if oracle table has blob DATATYPE. how will it be loaded to db2 table??


    thanks in advance

    Quote Originally Posted by famudba View Post
    you can load data from Oracle to DB2 ,

    get data in dec file by using SQL statement, if table has date , put date in Db2 format , to_char( date,'YYYY:MMD') function





    example Otacle table is emp

    that has following structure


    id number
    name varchar2(30)
    sal number(7,2)

    data is like
    1 elr 1000
    2 msr 2000
    3 est 3000



    spool emp.dat
    select to_char(id)||chr(36)||chr(39)||name||chr(39)||to_c har(sal)
    from emp
    spool off

    emp.dat , will look like below
    1,"elr",1000
    2,"msr",2000
    3,"est",3000


    use emp.dat , load into db2 by using column delimeter,char delimeter

    load from emp.dat of del MODIFIED BY CHARDEL" COLDEL, replace into db2.emp


    not sure ascci value of " , number

    Let me know , if any questions


    Thank You


    Lekharaju Ennam

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Check this out: IBM Database Conversion Workbench - Information Management - Wikis (UAT Theme)

    You might also enable federation on the DB2 side, if your license allows it, and pull data from Oracle tables directly.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Larry makes it difficult to get data out of Oracle so that it can be loaded into a different database. This is not an accident.

    In addition to DB2 Federation, there are third party products available. Some of them may allow a free trial if you have a small one-time project. I have used SQLWays in the past. Try Google.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2013
    Posts
    15
    You could always try using apatar it is an open source ETL and is straight forward to use.

  8. #8
    Join Date
    Jan 2013
    Posts
    2
    Look at IBM data movement tool (use google for thal!)
    There is a developer works article on tha and a link to download the tool...

Posting Permissions

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