Data migration from DB2 to Oracle using SQL Loader

Easy and fast way to migrate data from DB2 to Oracle using SQL Loader

–function to export data from DB2
CALL SYSPROC.ADMIN_CMD( ‘EXPORT TO “C:exportphotos.ldr” OF DEL MODIFIED BY LOBSINSEPFILES NOCHARDEL COLDEL| MESSAGES ON SERVER SELECT empno, photo_format, picture FROM ARTUR.EMP_PHOTO’ );

LOBSINSEPFILES – lob files in separated files
NOCHARDEL – without ” ”
COLDEL| – ‘|’ is used as columns separator
SELECT empno, photo_format, picture FROM ARTUR.EMP_PHOTO – data that we want to export, it could be join between tables or other function ‘select some_function(empno) from table;’

–control file for sqlLoader
LOAD DATA
INFILE ‘C:exportphotos.ldr’
APPEND
INTO TABLE “EMP_PHOTO”
FIELDS TERMINATED BY’|’
TRAILING NULLCOLS (
“EMPNO” CHAR (17),
“PHOTO_FORMAT” CHAR (17),
L_0 FILLER char,
“PICTURE” LOBFILE( L_0) TERMINATED BY EOF NULLIF L_0 = ‘null’ )

–sql loader command
sqlldr migracja/aaa control=C:exporttable_export_DATA.ctl parallel=true direct=true

parallel=true direct=true – much faster then usual insert’s