Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
large scale upload of ORACLE export to DB2
- Thread starter blom0344
- Start date
Hi,
Depends to some extent on the platform...
OS/390 could use DB2 load and NT/Unix etc could use either load or import.
If you adopt the approach of using load then you will need the control statements to map the Oracle generated flat-file to the DB2 table.
A few things to be aware of here...
1) It's generally easier if there are no varchar fields if there are then it raises the question as to whether you want to load them as fixed length or genuine varchar fields.
2) Again, it is easier if the flat-file data is in 'external' format, rather than as binary fields.
3) Be wary of date/time fields as the Oracle derived format may be significantly different from those that are accepted by the DB2 utilities.
Hope this helps,
Stoggers.
Depends to some extent on the platform...
OS/390 could use DB2 load and NT/Unix etc could use either load or import.
If you adopt the approach of using load then you will need the control statements to map the Oracle generated flat-file to the DB2 table.
A few things to be aware of here...
1) It's generally easier if there are no varchar fields if there are then it raises the question as to whether you want to load them as fixed length or genuine varchar fields.
2) Again, it is easier if the flat-file data is in 'external' format, rather than as binary fields.
3) Be wary of date/time fields as the Oracle derived format may be significantly different from those that are accepted by the DB2 utilities.
Hope this helps,
Stoggers.
- Thread starter
- #3
Hello Stoggers,
" If you adopt the approach of using load then you will need the control statements to map the Oracle generated flat-file to the DB2 table."
Can you explain? I take it that exportfile .exp from ORACLE is binary and not suitable for direct load/import to DB2
Do you mean with external format a export as a ' save as ' to a comma seperated .txt file?
From a white paper I gathered that varchar2 (ORACLE) relates to varchar (DB2) , but for small fields it is better to use CHAR (DB2) of fixed length as it is more efficient.
I suppose the mapping exercise should be done for each and every table script??
T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com
" If you adopt the approach of using load then you will need the control statements to map the Oracle generated flat-file to the DB2 table."
Can you explain? I take it that exportfile .exp from ORACLE is binary and not suitable for direct load/import to DB2
Do you mean with external format a export as a ' save as ' to a comma seperated .txt file?
From a white paper I gathered that varchar2 (ORACLE) relates to varchar (DB2) , but for small fields it is better to use CHAR (DB2) of fixed length as it is more efficient.
I suppose the mapping exercise should be done for each and every table script??
T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com
Hi,
The method that I have most familiarity with is the mainframe load utility.
The input to this consists of two files: a set of fixed length records and a control file that specifies that start position, length & type of the data in the flat file.
The issue with VARCHAR is that this format requires a binary prefix of the actual length of the string. The DB2 unload facility writes the data in this format but I doubt if the Oracle utilities will do the same.
If you are on NT/Unix then you have more options...
Import:
Using the command
IMPORT FROM table.csv OF DEL INSERT INTO table
will load the comma delimited file called table.csv into table. Date values must be ISO format YYYYMMDD. It is possible to override the template using DATEFORMAT=<template>. VARCHAR fields would be handled correctly by this method.
Bulk Load:
LOAD FROM table.csv OF DEL INTO table
Very fast!
However, there may be concurrency & recovery implications if you use the LOAD command.
Hope this helps,
Stoggers.
The method that I have most familiarity with is the mainframe load utility.
The input to this consists of two files: a set of fixed length records and a control file that specifies that start position, length & type of the data in the flat file.
The issue with VARCHAR is that this format requires a binary prefix of the actual length of the string. The DB2 unload facility writes the data in this format but I doubt if the Oracle utilities will do the same.
If you are on NT/Unix then you have more options...
Import:
Using the command
IMPORT FROM table.csv OF DEL INSERT INTO table
will load the comma delimited file called table.csv into table. Date values must be ISO format YYYYMMDD. It is possible to override the template using DATEFORMAT=<template>. VARCHAR fields would be handled correctly by this method.
Bulk Load:
LOAD FROM table.csv OF DEL INTO table
Very fast!
However, there may be concurrency & recovery implications if you use the LOAD command.
Hope this helps,
Stoggers.
- Status