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
11 14628
Hi,
are source and destination on the same system or on different server? Which OS, which DB2 version are you using?
Regards,
Bernd
Source and Target are in same servers,its in UNIX environment
the DB2 which we r using is version 9.1
Hi,
the following is a standard approach: - db2 connect to mydb1
-
db2 "export to dumpedtable.ixf of ixf select * from schema.mytable "
-
-
db2 connect to mydb2
-
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
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."
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 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_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 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.
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
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
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 "${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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Clive Page |
last post by:
I am trying to use the geometry data types introduced in version 4.1-alpha
and the spatial indexing seems to work well. But I have a large volume of
data to load, and from the documentation all I...
|
by: gazmob |
last post by:
Hi
I am new to working with XML and am trying Bulk load data into MS SQL
2000 using VB.net on Visual Studio 2003.
I add a ref to XBLKld4.ll
The example code seems to be fairly easy to...
|
by: Kiran |
last post by:
Hi,
I want to back up my data in some table in SQL server and import it back
using Bulk Load of SQL server 2K.
I can use the following code to backup the data in XML
...
|
by: MSSQLServerDeveloper |
last post by:
I am trying to do a SIMPLE Bulk load using version 4.0 - msxml4r.dll.
Unfortunately, I cannot find any examples of how to do this. For example, I
see many examples of using version 3, but not...
|
by: Andy Hunt |
last post by:
Hi,
I am having a problem with SQLXML Bulk Load inside of a web service ( also
inside of a basic aspx page). I have a WebMethod in a web service that
makes a call to bulk load. This works...
|
by: Sumanth |
last post by:
Hi,
I am running into a problem ( only in this particular instance).
I insert one row into a table - takes a fraction of a second
try to do a bulk load of the same --takes 10 seconds.
...
|
by: Vinny |
last post by:
i'm not sure if this is the right group for this message, if not, i
apologize.
i have an application i'm writing which needs to bulk-load several
million records into an sql server table.
i'm...
|
by: DTV12345 |
last post by:
Hello!
I have been assigned to bulk-load data into several typed tables of our ODS. I am not clear as to whether it can be done.
Here's my homework ===>>>
The easy way is to perform INSERT...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |