通过InfoSphere Federation Server从DB2向PostgreSQL增量加载数据遇SQL1822N错误求助

Let’s break down how to resolve this issue—your error points to compatibility or configuration mismatches between your Federation Server, PostgreSQL 12, and the outdated JDBC driver you’re using. Here’s a step-by-step fix:

1. Update Your PostgreSQL JDBC Driver (Critical First Step) #

You’re using postgresql-8.1-415.jdbc3.jar, a driver built for PostgreSQL 8.1—this is decades old and completely incompatible with PostgreSQL 12. This version mismatch is almost certainly causing the ResultSet closure error.

• Download a JDBC driver compatible with PostgreSQL 12 and Java 1.8 (JDBC 4.2 standard). The stable postgresql-42.2.18.jar is a perfect fit for your environment.
• Replace the old driver path in your server configuration with this new JAR file.

2. Adjust Federation Server Configuration #

Some of your current settings might conflict with the outdated driver. Let’s clean up and reconfigure with safer parameters first:

-- Remove existing server and user mapping
DROP USER MAPPING FOR SANAGARW SERVER FEDSER;
DROP SERVER FEDSER;

-- Recreate server with new driver and adjusted settings
CREATE SERVER FEDSER TYPE JDBC VERSION '12' WRAPPER JDBC OPTIONS(
 ADD DRIVER_PACKAGE 'E:\Sandhya\postgresql-42.2.18.jar', -- Path to your new driver
 URL 'jdbc:postgresql://localhost:5432/SCOPEDB',
 DRIVER_CLASS 'org.postgresql.Driver',
 DB2_IUD_ENABLE 'N', -- Disable incremental load temporarily to test basic queries
 db2_char_blankpadded_comparison 'Y',
 db2_varchar_blankpadded_comparison 'Y',
 VARCHAR_NO_TRAILING_BLANKS 'Y',
 JDBC_LOG 'Y'
);

-- Recreate user mapping
CREATE USER MAPPING FOR SANAGARW SERVER FEDSER OPTIONS (REMOTE_AUTHID 'postgres',REMOTE_PASSWORD '*****');

• The JDBC_LOG 'Y' setting will generate detailed logs of JDBC interactions. Check these logs (or the db2diag.log file) if issues persist—they’ll show exactly when/why the ResultSet is closing.

3. Use Nicknames for Federated Queries #

Federation Server requires nicknames to interact with remote tables—you can’t query the remote table directly.

• First, create a nickname for your PostgreSQL EMPLOYEE table:

  CREATE NICKNAME EMPLOYEE_FED FOR FEDSER."SCOPE".EMPLOYEE;
  

• Now run your count query against the nickname:

  SELECT COUNT(*) FROM EMPLOYEE_FED;
  

4. Verify Java Environment & Driver Access #

• Confirm the Federation Server uses your Java 1.8.0_241 installation. Check the db2dj.ini file (in your DB2 instance directory) to ensure JAVA_HOME points to this version.
• Make sure the DB2 process has read access to the JDBC driver JAR. For reliability, copy the driver to your DB2 instance’s sqllib/function directory and update the DRIVER_PACKAGE path to match.

5. Re-enable Incremental Load (Once Queries Work) #

After confirming basic queries run successfully, you can set DB2_IUD_ENABLE 'Y' again to reactivate incremental loads. Test this step carefully to avoid new compatibility issues.

内容的提问来源于stack exchange，提问作者sandhya agarwal