0

I'm new to Db2. I'm trying to send data from remote Db2 server A to remote Db2 server B using a Java based application. I was able to fetch the data from server A and get it stored in the control/data files; but when I try to send the data to server B, I get following exception.

com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=EXTERNAL;T_DATA SELECT * FROM;<table_expr>, DRIVER=4.26.14

The control file has the command:

INSERT INTO <TABLE_NAME> SELECT * FROM EXTERNAL '<PATH_TO_DATAFILE>'
USING (DELIMITER '\t' FORMAT TEXT SOCKETBUFSIZE 100 REMOTESOURCE 'JDBC')

The data file contains records where each value separated by tab per record.

Both server A and B are using Db2 v9.5

Improve this question
2
  • Does the target Db2-server version support EXTERNAL tables? EDIT your question to add Db2-platform and Db2-version details for the target server-B. Commented May 26, 2021 at 12:06
  • Db2 server B has v9.5. And I just read that EXTERNAL was introduced in the v11.5. Thanks @mao. Any idea what will be the alternative for EXTERNAL for v9.5 and/or v10.5? Commented May 26, 2021 at 12:14

1 Answer 1

Reset to default
1

The failure was caused by the target server-B being an out of support version of Db2 (v9.5) that does not have any ability to understand external tables. Hence it reported (correctly) sqlcode -104 on the token EXTERNAL which it did not understand.

So the design is incorrect for the available Db2-versions at your site. You can only use external tables in Db2-LUW versions that are recent (v11.5).

Depending on the tools available, you can use commands (external tools, not SQL) to export data from the source, and load it into the target. Additionally, if there is network connectivity directly between server-A and server-B then an administrator can arrange federation between them allowing direct inserts.

Db2 v9.5 also supported load from cursor, and load from remote cursor (although there were problems, long since fixed in newer versions).

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Is there any tool/command that I could use in my control file to send the records from data file. My Java application executes this control file to send data across.
please ask a new question. Do not ask follow up questions inside comments.
I did some research and found two ways - IMPORT and LOAD. I leaned over LOAD as it is more useful to transfer bulk data. I'm trying to use LOAD command along with JDBC but keep on getting error. I've asked a separate question: stackoverflow.com/questions/67721450/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.