We’re long past the days where a single on-site server could handle all the IT needs of a typical business. 
Today it’s likely your IBM i system is supplemented by a myriad of separate servers — perhaps even additional IBM i boxes or partitions — each housing its own database. These isolated machines contain valuable information pertinent to the departments they serve, whether it be for shipping, quality assurance, customer service, or what-have-you. So if you haven’t already, it’s likely at some point in the future you’ll find a need to access these remote databases to show information that is ancillary to your core business data. With Valence 5.1 it’s now possible to tap into those external databases to bring their islands of data into your IBM i-based Valence apps, whether they be Nitro Query apps or custom developed apps using the Valence RPG Toolkit. This tip explains how.
IMPORTANT: To access the Remote DB functionality in Valence 5.1 you must be running on build 5.1.20170714.0 or greater. You can download the latest build here. Please note that as of this writing the remote DB functionality within Valence is still considered a Beta feature. We expect to formally lift the Beta designation in a forthcoming build.
Step 1: Set up your remote database credentials
In order for Valence to access remote databases on your network, you must first provide a server type, server address and login credentials for each connection. This setup is handled inside the Portal Admin app, in the “Remote Databases” section. Once you’re in that section, click the pink plus icon in the lower right to add a new connection. You will then be presented with a page resembling this:
The fields on this page and their meanings are as follows:
- Name – the name of your remote database or server
- JDBC Driver – Select the type of connection (IBM i/DB2, MySQL or SQL Server)
- IP Address / Port – the address (or DNS name) of the remote server and the port to use to connect
- User / Password – The user ID and password that should be used to establish the remote DB connection
Step 2: Ensure your Valence instance’s Java classpath is updated
In order to access the new remote database logic, the Java classpath associated with your Valence instance(s) must be updated to include the new Java-based remote DB classes. Go into Portal Admin > Settings, scroll down to the IBM i Settings section and click inside the “Java CLASSPATH setting” field, as shown here.
If you do not see any “remotedb” text in the classpath, then append the following text into the field and click Save: :[ROOT_PATH]java/remotedb/vvJavaRemote.jar:[ROOT_PATH]java/remotedb/mysql-connector-java-5.1.39-bin.jar:[ROOT_PATH]java/remotedb/jt400.jar:[ROOT_PATH]java/remotedb/json.jar:[ROOT_PATH]java/remotedb/log4j-1.2.17.jar:[ROOT_PATH]java/remotedb/jtds-1.2.5.jar
Step 3a: Access your remote data through RPG (vvOut_execSQLtoJSON)
For developers creating programs that use VVOUT from the Valence RPG Toolkit, you can access your remote database by putting a new field in the VVOUT data structure to use – VVOUT.REMOTEDB. When this field is populated with a non-zero value corresponding to the numeric remote database ID established in step 1 above, the SQL statement will be executed against the remote connection. Note that your SQL statement must be in the “flavor” of the remote database from which you’re pulling data (i.e., the “FETCH FIRST” clause is okay for IBM i/DB2 connections, but on SQL Server connections it must be specified as “LIMIT”).
Step 3b: Access your remote data through Nitro Query App Builder
For developers using Nitro Query to access data, remote DB support is now built into the Data Sources panel. When you go to create a new data source, if you have at least one remote database set up (as described in step 1) then you will notice a “Remote Database” checkbox. If you check it then you will be presented with a combo box from which you can select your desired remote database, followed by a list of schemas (or libraries in the case of an IBM i/DB2 connection). Finally you specify your table or file name and proceed as normal through the rest of the data source setup.
The technical details…
Whenever an RPG program or Nitro Query data source requests remote database content through Valence, a special remote DB job is activated to service the CGI job making the request. The remote DB job name is always VRMTxxxxxx, where xxxxxx is the job number of the calling Valence CGI job. These jobs are dedicated to JDBC-specific tasks and launch automatically into the QHTTPSVR subsystem as needed, communicating through data queues with the Valence CGI jobs. Should the Valence instance that initially launched any VRMT jobs terminate — i.e., by a server instance shutdown or restart — then the VRMT jobs will likewise terminate within a couple minutes.
If for some reason you need the VRMT jobs to run in another subsystem besides QHTTPSVR, you can override the job queue for the jobs to be submitted. This is done in Portal Admin > Settings > Hidden Settings > REMOTEDB_JOBQ, where the job queue is specified in LIBRARY/JOBQ format. In the event you encounter problems connecting to a remote database, the first step for troubleshooting the problem is to activate logging for the VRMT job. This logging function is activated through a checkbox in Portal Admin > Settings > Hidden Settings > LOG_REMOTEDB_JDBC. If checked, the various JDBC functions involved in making the remote connection will be logged in a file on the IFS called log4j-vvjavaremote.log, located in your Valence instance directory under /java/remotedb. Presently Valence’s remote DB functionality is limited to three popular connection types — IBM i, MySQL and SQL Server. If you have another type of server you would like to access through Valence, please contact CNX Support and we’ll look into getting it set up in a future build.