469,352 Members | 1,694 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,352 developers. It's quick & easy.

DB2 Bulk Load

Hi All,
I need to use the DB2 Bulk Load option for loading DB2 table where source is also a DB2 table .

Kindly provide some suggestions.

Thanks in Advance
Prasanna
Mar 13 '08 #1
11 14115
docdiesel
297 Expert 100+
Hi,

are source and destination on the same system or on different server? Which OS, which DB2 version are you using?

Regards,

Bernd
Mar 13 '08 #2
Source and Target are in same servers,its in UNIX environment
Mar 13 '08 #3
the DB2 which we r using is version 9.1
Mar 14 '08 #4
docdiesel
297 Expert 100+
Hi,

the following is a standard approach:

Expand|Select|Wrap|Line Numbers
  1. db2 connect to mydb1
  2. db2 "export to dumpedtable.ixf of ixf select * from schema.mytable "
  3.  
  4. db2 connect to mydb2
  5. db2 "import from dumpedtable.ixf of ixf insert into schema.mytable "
For big amounts of data you could use "load from ..." instead of import. This avoids usage of the logs and usually is a lot faster. A runstats afterwards is recommended. Make sure if you have to insert into the destination table or replace its content.

Regards,

Bernd
Mar 14 '08 #5
Hi ,
When i Use the command "export to dumpedtable.ixf of ixf select * from ${SRC_SCHEMA}.${TABLE_NAME}"

iam getting an error message

"SQL3022N An SQL error "-104" occurred while processing the SELECT string in
the Action String parameter."
Mar 19 '08 #6
docdiesel
297 Expert 100+
Hi prasanna,

seems to me like you're operating on a shell (using ${variables}). Here you'll have to escape the * unless you want the shell to interpret this as "list all files in current dir".

Try one of the following:
Expand|Select|Wrap|Line Numbers
  1. myshell$ db2  export to dump.ixf of ixf  select \* from ${schema}.${table}
  2. myshell$ db2 "export to dump.ixf of ixf  select  * from ${schema}.${table}"
Both should work. Does this fit to your needs?

Regards,

Bernd
Mar 20 '08 #7
Hi prasanna,

seems to me like you're operating on a shell (using ${variables}). Here you'll have to escape the * unless you want the shell to interpret this as "list all files in current dir".

Try one of the following:
Expand|Select|Wrap|Line Numbers
  1. myshell$ db2  export to dump.ixf of ixf  select \* from ${schema}.${table}
  2. myshell$ db2 "export to dump.ixf of ixf  select  * from ${schema}.${table}"
Both should work. Does this fit to your needs?

Regards,

Bernd
Hi Bernd,

I tried
export to dumpedtable.ixf of ixf select \* from omdstgd.omd_d_event_track

and the error iam getting is
SQL3022N An SQL error "-7" occurred while processing the SELECT string in the
Action String parameter.

SQL0007N The character "\" following "select " is not valid. SQLSTATE=42601
Mar 20 '08 #8
Hi Bernd,

I tried
export to dumpedtable.ixf of ixf select \* from omdstgd.omd_d_event_track

and the error iam getting is
SQL3022N An SQL error "-7" occurred while processing the SELECT string in the
Action String parameter.

SQL0007N The character "\" following "select " is not valid. SQLSTATE=42601
Hi,
i tried export to dumpedtable.ixf of ixf select '*' from omdstgd.omd_d_event_track

it worked .
but now the
load is troubling

SQL3025N A parameter specifying a filename or path is not valid.

2008-03-20_09:34:35:ERROR: load from dumpedtable.ixf of ixf insert into OMDD.OMD_D_EVENT_TRACK - Failed.
Mar 20 '08 #9
docdiesel
297 Expert 100+
Hi,

are you trying to execute the export statement from shell or from db2 command line? Perhaps you could provide us with your shell or sql script, or, if it's too big, at least with the important lines.

Regards,

Bernd
Mar 20 '08 #10
Hi Bernd,

Iam trying to execute export and load from shell script

Expand|Select|Wrap|Line Numbers
  1. ##################################################
  2. # Connect to OMDSTG
  3. ##################################################
  4.  
  5. db2 connect to ${OMD_DB} user ${OMD_USER} using ${OMD_PASS} >> ${LOCAL_LOG_FILE}
  6. if [ $? -ne 0 ]
  7. then
  8.    echo "`date +%Y-%m-%d_%H:%M:%S`:ERROR: DB2 Connect to ${OMD_DB} - Failed." >> ${LOCAL_LOG_FILE}
  9.    exit ${OMD_DB2_CONNECT_ERROR}
  10. else
  11.    echo "`date +%Y-%m-%d_%H:%M:%S`:INFO: DB2 Connect to ${OMD_DB} - Success." >> ${LOCAL_LOG_FILE}
  12. fi
  13.  
  14. ##################################################
  15. #Export data to IXF file
  16. ##################################################
  17.  
  18. QUERY_STRING="export to dumpedtable.ixf of ixf select '*' from ${SRC_SCHEMA}.${TABLE_NAME}"
  19. db2 ${QUERY_STRING} >> ${LOCAL_LOG_FILE}
  20. if [ $? -ne 0 ]
  21. then
  22.    echo "`date +%Y-%m-%d_%H:%M:%S`:ERROR: ${QUERY_STRING} - Failed." >> ${LOCAL_LOG_FILE}
  23.    exit ${OMD_DB2_SQL_ERROR}
  24. else
  25.    echo "`date +%Y-%m-%d_%H:%M:%S`:INFO: ${QUERY_STRING} - Success." >> ${LOCAL_LOG_FILE}
  26. fi
  27.  
  28. ##################################################
  29. # Load Source Data to target
  30. ##################################################
  31.  
  32. QUERY_STRING="Load from dumpedtable.ixf of ixf insert into OMDD.OMD_M_OBJECTS_TEST"
  33. db2 ${QUERY_STRING} >> ${LOCAL_LOG_FILE}
  34. if [ $? -ne 0 ]
  35. then
  36.    echo "`date +%Y-%m-%d_%H:%M:%S`:ERROR: ${QUERY_STRING} - Failed." >> ${LOCAL_LOG_FILE}
  37.    exit ${OMD_DB2_SQL_ERROR}
  38. else
  39.    echo "`date +%Y-%m-%d_%H:%M:%S`:INFO: ${QUERY_STRING} - Success." >> ${LOCAL_LOG_FILE}
  40. fi
this is the part
Mar 20 '08 #11
docdiesel
297 Expert 100+
Hi,

thank you for posting the code. (Please use code tags (the # button in the editor) when posting code.) I think I found why the shell is replacing the * with the list of files in the current dir, letting the db2 command fail. Some " added to the db2 line should do the trick:

Expand|Select|Wrap|Line Numbers
  1. QUERY_STRING="export to dumpedtable.ixf of ixf select * from ${SRC_SCHEMA}.${TABLE_NAME}"
  2.  
  3. db2 -v "${QUERY_STRING}" >> ${LOCAL_LOG_FILE}
The "-v" will let db2 show you what command it's working on, so if there's still something wrong, you'll see it in the logs.

Regards,

Bernd
Mar 20 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Clive Page | last post: by
reply views Thread by gazmob | last post: by
3 posts views Thread by Kiran | last post: by
reply views Thread by MSSQLServerDeveloper | last post: by
2 posts views Thread by Andy Hunt | last post: by
5 posts views Thread by Sumanth | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.