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?
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"
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?
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".
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
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?
Edited by: 971235 on 24-Nov-2012 04:33
Edited by: 971235 on 24-Nov-2012 14:02
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.
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?
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.
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?
Edited by: 971235 on 26-Nov-2012 07:55
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.