This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Nov 26, 2012 7:57 AM by 974238 RSS

AS/400 TO ORACLE

974238 Newbie
Currently Being Moderated
HI,
I need to extract the data from AS/400 into oracle DB using ODI.

If i need to create the file from AS/400 then load it into oracle DB how to do it in ODI?

If i need to directly loading from as/400 into oracle db how to do that in ODI?

Cheers
  • 1. Re: AS/400 TO ORACLE
    A. Drieux Pro
    Currently Being Moderated
    Do you have created AS/400 physical server in topology ? (in IBM DB2/400 technology)

    Then, you have to use the appropriate LKM to extract data and load it into Oracle database. Ex : "LKM SQL to Oracle"
    If you create a file from AS/400, use the "LKM File to Oracle"
  • 2. Re: AS/400 TO ORACLE
    974238 Newbie
    Currently Being Moderated
    Yes Topology is there.
    Do you think performence will be good with those KM's? or any known bugs in those KM's as iam using 10g.
    How about using sqlunload and then load into oracle db?Do you think sqlunload will increase the performence?
    Any ideas..
  • 3. Re: AS/400 TO ORACLE
    A. Drieux Pro
    Currently Being Moderated
    It depends on your volumetry.
    Sure that LKM Sql to Oracle is not very fast (it process the data row by row).

    You can try to load a file, then use the "LKM file to Oracle SQL LOADER" or the "LKM file to Oracle EXTERNAL Table" that are faster than simple "file to sql".
  • 4. Re: AS/400 TO ORACLE
    974238 Newbie
    Currently Being Moderated
    Thanks mate.
    Is sqlunload is reliable and fast?
    how to use that?any idea?
  • 5. Re: AS/400 TO ORACLE
    A. Drieux Pro
    Currently Being Moderated
    You mean the Odi Tool SQL Unload that loads SQL results in a flat file ?
    Yes, I think it's reliable.

    But the main issue will be to load this file as fast as possible in your Oracle DB. And for that, you will have to use the SQLLDR or EXTERNAL TABLE functionnalities.

    Step 1 : unload your AS400 SQL statement results into a flat file
    Step 2 : create an interface that reads this file and load it in Oracle DB
    Step 3 : compare with an interface that use LKM SQL to Oracle
  • 6. Re: AS/400 TO ORACLE
    974238 Newbie
    Currently Being Moderated
    thanks mate.. first i will try lkm sql to oracle, as it is direct load from as/400 to oracle
    next i will try, unload and load though sql loader/external table and compare the results.
    cheers
  • 7. Re: AS/400 TO ORACLE
    A. Drieux Pro
    Currently Being Moderated
    Tell me the results. It's interesting ;-)

    Do not hesitate to mark posts as helpful or answered if it helped you
  • 8. Re: AS/400 TO ORACLE
    974238 Newbie
    Currently Being Moderated
    Just one query regarding Agent,we have here AS/400 source Server and oracle db target server
    Where we need to install agent? incase if i sqlunload and create file and load using sql loader what is the best place to install agent?
    if we have one more server(not source and not target) and if we install agent there and unload files into that server, can we still load the files(across network) into oracle target db using sql loader from the server where agent installed?
    Cheers

    Edited by: 971235 on 24-Nov-2012 04:33

    Edited by: 971235 on 24-Nov-2012 14:02
  • 9. Re: AS/400 TO ORACLE
    A. Drieux Pro
    Currently Being Moderated
    I recommand you to install the agent on the same host than your Oracle DB

    If you use another serveur (no source host, no target host), you'll still be able to load your data, but you may have more network issues. It may be slower.
  • 10. Re: AS/400 TO ORACLE
    974238 Newbie
    Currently Being Moderated
    ok,install agent on source server and unload the data in the form of files into source server and load them using sqlloader(which again needs to be there on source server) using same agent,right?
  • 11. Re: AS/400 TO ORACLE
    A. Drieux Pro
    Currently Being Moderated
    Here :

    install agent on TARGET server (= Oracle DB) and unload the data of DB2 in the form of files into FOLDER (which can be read by AGENT, why not in the same place than your Oracle DB) and load them using sqlloader(which again needs to be there on source server) using same agent,right?

    ie :
    You create a package that launch and SQL statement on DB2 database and unload it into a file.
    Then, copy this file in a folder located in the same host than your Oracle DB.
    Then launch an interface that use LKM SQL LOADER to load the file into an Oracle DB table.
    Using the agent that is installed on the same host than your Oracle DB.
  • 12. Re: AS/400 TO ORACLE
    974238 Newbie
    Currently Being Moderated
    So basically agent needs to copy the files from source server to target server using ftp etc before it loads them though sqlloader from target server,is that right?
    Cheers

    Edited by: 971235 on 26-Nov-2012 07:55
  • 13. Re: AS/400 TO ORACLE
    A. Drieux Pro
    Currently Being Moderated
    I think it's the best solution.
    You can also use a shared folder between the 2 serveurs, but it's better if ODI will read the file directly on the agent host. And it's better if the agent host is located on the same server than your Oracle Database.
  • 14. Re: AS/400 TO ORACLE
    974238 Newbie
    Currently Being Moderated
    ok,do you think when we unload the files can be created directly on target server or do we need to do any FTP from source to target though same agent?
    Cheers
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points