DB2 Bulk Load

[prasanna2100]

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

[docdiesel]

Hi,

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

Regards,

Bernd

[prasanna2100]

Source and Target are in same servers,its in UNIX environment

[prasanna2100]

the DB2 which we r using is version 9.1

[docdiesel]

Hi,

the following is a standard approach:

db2 connect to mydb1
db2 "export to dumpedtable.ixf of ixf select * from [I]schema.mytable[/I] "

db2 connect to mydb2
db2 "import from dumpedtable.ixf of ixf [I]insert[/I] into [I]schema.mytable[/I] "

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

[prasanna2100]

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."

[docdiesel]

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:

myshell$ db2  export to dump.ixf of ixf  select \* from ${schema}.${table}
myshell$ db2 "export to dump.ixf of ixf  select  * from ${schema}.${table}"

Both should work. Does this fit to your needs?

Regards,

Bernd

[prasanna2100]

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:

myshell$ db2  export to dump.ixf of ixf  select \* from ${schema}.${table}
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_e vent_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

[prasanna2100]

Hi Bernd,

I tried
export to dumpedtable.ixf of ixf select \* from omdstgd.omd_d_e vent_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_e vent_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:ERR OR: load from dumpedtable.ixf of ixf insert into OMDD.OMD_D_EVEN T_TRACK - Failed.

[docdiesel]

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

[prasanna2100]

Hi Bernd,

Iam trying to execute export and load from shell script

##################################################
# Connect to OMDSTG
##################################################

db2 connect to ${OMD_DB} user ${OMD_USER} using ${OMD_PASS} >> ${LOCAL_LOG_FILE}
if [ $? -ne 0 ]
then
   echo "`date +%Y-%m-%d_%H:%M:%S`:ERROR: DB2 Connect to ${OMD_DB} - Failed." >> ${LOCAL_LOG_FILE}
   exit ${OMD_DB2_CONNECT_ERROR}
else
   echo "`date +%Y-%m-%d_%H:%M:%S`:INFO: DB2 Connect to ${OMD_DB} - Success." >> ${LOCAL_LOG_FILE}
fi

##################################################
#Export data to IXF file
##################################################

QUERY_STRING="export to dumpedtable.ixf of ixf select '*' from ${SRC_SCHEMA}.${TABLE_NAME}"
db2 ${QUERY_STRING} >> ${LOCAL_LOG_FILE}
if [ $? -ne 0 ]
then
   echo "`date +%Y-%m-%d_%H:%M:%S`:ERROR: ${QUERY_STRING} - Failed." >> ${LOCAL_LOG_FILE}
   exit ${OMD_DB2_SQL_ERROR}
else
   echo "`date +%Y-%m-%d_%H:%M:%S`:INFO: ${QUERY_STRING} - Success." >> ${LOCAL_LOG_FILE}
fi

##################################################
# Load Source Data to target
##################################################

QUERY_STRING="Load from dumpedtable.ixf of ixf insert into OMDD.OMD_M_OBJECTS_TEST"
db2 ${QUERY_STRING} >> ${LOCAL_LOG_FILE}
if [ $? -ne 0 ]
then
   echo "`date +%Y-%m-%d_%H:%M:%S`:ERROR: ${QUERY_STRING} - Failed." >> ${LOCAL_LOG_FILE}
   exit ${OMD_DB2_SQL_ERROR}
else
   echo "`date +%Y-%m-%d_%H:%M:%S`:INFO: ${QUERY_STRING} - Success." >> ${LOCAL_LOG_FILE}
fi

this is the part

[docdiesel]

typo

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:

QUERY_STRING="export to dumpedtable.ixf of ixf select * from ${SRC_SCHEMA}.${TABLE_NAME}"

db2 -v [B]"[/B]${QUERY_STRING}[B]"[/B] >> ${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