External Table

[SprintFlunky]

I have to move a large amount of data (6.8 million records) from an Oracle (Unix server) database to a DB2 (Unix server) database. The current process utilizes a DB2 Connect PL\SQL routine to select from\insert into loop to load the records, but it takes days. We have some performance tuning we can perform, but I'm afraid the process will never achieve the results that I really need, so I'm researching alternatives.

Oracle has the capability to point to an external file as the underlying data source for a table and I was wondering if DB2 has the same capabilities. If it does then I can simply transfer the file and "point" the DB2 table to the new file. Does anyone know if DB2 has the equivalent of the external table?

Thanks in advance.

Chris

 

[LKBrwnDBA]

I would create some type of CSV (or DELimited) file from Oracle DB and then load that data into DB2 with the db2import or db2load utilities.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SprintFlunky]

LKBrwnDBA,

Thanks for the reply. Using a load utility is my fallback option but the Oracle External file is a really nice feature. I can simply move the new file to the server (taking all the time I need) and then just point the table to new file. The swap is instant so there is no user outage while I load the table.

So far, I have not been able to find the same option in DB2.

 

[LKBrwnDBA]

Oracle external tables are read-only; therefore, no DML operations are possible, and no index can be created on them.





----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb