Question on connection to remote DB - ccsid's

[ThierryC]

hi.. i 'm struggling to setup the connection to a remote DB (which is also a DB2 machine)

the drda connection is working - so i can connect from my machine to the remote machine via normal Sql ..
one remark though :
i have to change the ccsid from my job into ccsid 037..
with the default one it gives me a message (conversion betweel ccsid 13488 & ccsid 65535 is invalid)


i've configured the remote db in Valence(6) but when i try to access it
it returns me 'Remote DB - Attempt to connect to remote server has timed out'

when looking in the http sbs - i couldn't find any errors: (i noticed 2 new http jobs have been launched) - i guess to load the JVM - but no errors

don't know where to look ..
not sure if this is ccsid related, or if the valence remotedb is using another port that might be blocked...? (ps. what is the port that is used?)

thierry

[ThierryC]

this is the java log file

2021-04-09 16:03:14 DEBUG RemoteHandler:1460 - appender has been set
2021-04-09 16:03:14 INFO RemoteHandler:549 - ***** Start getRmtSchemas
2021-04-09 16:03:14 INFO RemoteHandler:550 - ***** dbType DB2/400
2021-04-09 16:03:14 INFO RemoteHandler:551 - ***** jdbcDriver com.ibm.as400.access.AS400JDBCDriver
2021-04-09 16:03:14 INFO RemoteHandler:552 - ***** jarPath /valence-60P/jt400.jar
2021-04-09 16:03:14 INFO RemoteHandler:553 - ***** logPath /valence-60P/java/remotedb/log4j-vvjavaremote.log
2021-04-09 16:03:14 INFO RemoteHandler:554 - ***** rmtDbUrl jdbc:as400://PROD01;naming=system;translate binary=true;
2021-04-09 16:03:14 INFO RemoteHandler:555 - ***** rmtDbUser tco
2021-04-09 16:03:14 INFO RemoteHandler:556 - ***** rmtDbPwd **********
2021-04-09 16:03:14 INFO RemoteHandler:506 - Start getRemoteConnection
2021-04-09 16:03:14 INFO RemoteHandler:507 - ***** dbType DB2/400
2021-04-09 16:03:14 INFO RemoteHandler:508 - ***** jdbcDriver com.ibm.as400.access.AS400JDBCDriver
2021-04-09 16:03:14 INFO RemoteHandler:509 - ***** jarPath /valence-60P/jt400.jar
2021-04-09 16:03:14 INFO RemoteHandler:510 - ***** rmtDbUrl jdbc:as400://PROD01;naming=system;translate binary=true;
2021-04-09 16:03:14 INFO RemoteHandler:511 - ***** rmtDbUser tco
2021-04-09 16:03:14 INFO RemoteHandler:512 - ***** rmtDbPwd **********
2021-04-09 16:03:14 INFO RemoteHandler:514 - **** classpath:
2021-04-09 16:03:14 INFO RemoteHandler:521 - /
2021-04-09 16:03:14 INFO RemoteHandler:521 - /
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/itext-2.1.7.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/documenttype.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/activation.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/mail.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/MessageType.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/vvJavaRemote.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/mysql-connector-java-5.1.39-bin.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/jtds-1.2.5.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/sqljdbc41.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/jt400.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/json.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/log4j-1.2.17.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/tls_email.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/commons-codec-1.10.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/commons-collections4-4.1.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/commons-compress-1.18.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/commons-logging-1.2.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/commons-math3-3.6.1.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/curvesapi-1.04.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/opencsvsrc.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/poi-3.16.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/poi-excelant-3.16.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/poi-ooxml-3.16.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/poi-ooxml-schemas-3.16.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/poi-scratchpad-3.16.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/xmlbeans-2.6.0.jar
2021-04-09 16:03:14 INFO RemoteHandler:521 - /valence-60P/java/remotedb/ojdbc7.jar
2021-04-09 16:03:14 INFO RemoteHandler:530 - End getRemoteConnection
2021-04-09 16:05:06 DEBUG RemoteHandler:1460 - appender has been set
2021-04-09 16:05:06 INFO RemoteHandler:549 - ***** Start getRmtSchemas
2021-04-09 16:05:06 INFO RemoteHandler:550 - ***** dbType DB2/400
2021-04-09 16:05:06 INFO RemoteHandler:551 - ***** jdbcDriver com.ibm.as400.access.AS400JDBCDriver
2021-04-09 16:05:06 INFO RemoteHandler:552 - ***** jarPath /valence-60P/jt400.jar
2021-04-09 16:05:06 INFO RemoteHandler:553 - ***** logPath /valence-60P/java/remotedb/log4j-vvjavaremote.log
2021-04-09 16:05:06 INFO RemoteHandler:554 - ***** rmtDbUrl jdbc:as400://PROD01;naming=system;translate binary=true;
2021-04-09 16:05:06 INFO RemoteHandler:555 - ***** rmtDbUser tco
2021-04-09 16:05:06 INFO RemoteHandler:556 - ***** rmtDbPwd **********
2021-04-09 16:05:06 INFO RemoteHandler:506 - Start getRemoteConnection
2021-04-09 16:05:06 INFO RemoteHandler:507 - ***** dbType DB2/400
2021-04-09 16:05:06 INFO RemoteHandler:508 - ***** jdbcDriver com.ibm.as400.access.AS400JDBCDriver
2021-04-09 16:05:06 INFO RemoteHandler:509 - ***** jarPath /valence-60P/jt400.jar
2021-04-09 16:05:06 INFO RemoteHandler:510 - ***** rmtDbUrl jdbc:as400://PROD01;naming=system;translate binary=true;
2021-04-09 16:05:06 INFO RemoteHandler:511 - ***** rmtDbUser tco
2021-04-09 16:05:06 INFO RemoteHandler:512 - ***** rmtDbPwd **********
2021-04-09 16:05:06 INFO RemoteHandler:514 - **** classpath:
2021-04-09 16:05:06 INFO RemoteHandler:521 - /
2021-04-09 16:05:06 INFO RemoteHandler:521 - /
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/itext-2.1.7.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/documenttype.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/activation.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/mail.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/MessageType.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/vvJavaRemote.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/mysql-connector-java-5.1.39-bin.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/jtds-1.2.5.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/sqljdbc41.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/jt400.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/json.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/log4j-1.2.17.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/tls_email.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/commons-codec-1.10.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/commons-collections4-4.1.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/commons-compress-1.18.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/commons-logging-1.2.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/commons-math3-3.6.1.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/curvesapi-1.04.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/opencsvsrc.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/poi-3.16.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/poi-excelant-3.16.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/poi-ooxml-3.16.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/poi-ooxml-schemas-3.16.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/poi-scratchpad-3.16.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/xmlbeans-2.6.0.jar
2021-04-09 16:05:06 INFO RemoteHandler:521 - /valence-60P/java/remotedb/ojdbc7.jar 

[robert.swanson]

Hi Thierry,

Here's a link to the driver being used by Valence for IBM i JDBC access:

https://docs.oracle.com/cd/E19509-01/820-3497/agqji/index.html

I believe it's using port 446. Perhaps that's being blocked in your network?

If not, check the job log of the batch job that's handling the connection (should be VBCHxxxxxx, where xxxxxx is the job number of the Valence CGI job requesting the data).

-Rob

[ThierryC]

Hi Rob..

thx for the answer although it is not helping a lot..
the VBCHxxxx jobs do not show any errors.. the java remotedb log shows below.. a connection error'

i'm sure that port 446 which is drda is open..

could it be that also other ports are needed as well?

what i've found on the internet below.. maybe another port is required to initiate the setup
my elbows tell me that *DTAQ (8472) or Database (8471)might be needed?
any experience on that?

This table outlines the ports that JT400 requires in order for specific functions to communicate with the host.

STRHOSTSVR SERVER	*SVRMAP		*CENTRAL	*DATABASE	*DTAQ	*FILE	*NETPRT	*RMTCMD	*SIGNON
Port	449	446	8470	8471	8472	8473	8474	8475	8476
SSL Port	-	448	9470	9471	9472	9473	9474	9475	9476

2021-04-09 16:09:32 ERROR RemoteHandler:606 - ERROR OCCURRED
java.sql.SQLException: The application requester cannot establish the connection. (Connection timed out (Connection timed out))
at com.ibm.as400.access.JDError.createSQLExceptionSub Class(JDError.java:820)
at com.ibm.as400.access.JDError.throwSQLException(JDE rror.java:549)
at com.ibm.as400.access.AS400JDBCConnection.setProper ties(AS400JDBCConnection.java:3350)
at com.ibm.as400.access.AS400JDBCDriver.prepareConnec tion(AS400JDBCDriver.java:1419)
at com.ibm.as400.access.AS400JDBCDriver.initializeCon nection(AS400JDBCDriver.java:1256)
at com.ibm.as400.access.AS400JDBCDriver.connect(AS400 JDBCDriver.java:395)
at java.sql.DriverManager.getConnection(DriverManager .java:675)
at java.sql.DriverManager.getConnection(DriverManager .java:258)
at com.cnx.java.rmt.RemoteHandler.getRemoteConnection (RemoteHandler.java:532)
at com.cnx.java.rmt.RemoteHandler.getRmtSchemas(Remot eHandler.java:562)

...
Caused by: java.net.ConnectException: Connection timed out (Connection timed out)
at java.net.AbstractPlainSocketImpl.doConnect(Abstrac tPlainSocketImpl.java:380)
at java.net.AbstractPlainSocketImpl.connectToAddress( AbstractPlainSocketImpl.java:236) 

[robert.swanson]

The JDBC API that the RemoteDB routine uses is a bit of a black box, so your research on additional ports may be revealing things we never knew ourselves about it.

Can you find out if those ports in the 8470-8476 range are being blocked in your network? There are plenty of Valence sites using RemoteDB to connect successfully to other IBM i servers today, so I'm inclined to think the connection issue you're encountering is very likely network-related.