Error on loading data to remote DB2 server

0
votes

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

1
db2remote-serverdb2-luw
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. – mao
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? – dev-eloper

1 Answers

1
votes

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