iSeries EGL

All things EGL for the iSeries / i5 / Power System

Posts Tagged ‘Dynamic DB Connections with EGL

Making a DB Connection during runtime

leave a comment »

Using Tomcat’s JVM parameter values to facilitate DB connections

This post will illustrate how a database connection can be established to one of two separate iSeries machines.

Before we get going, for some background you should probably read this post first. Its is about the configuration of the server’s (Tomcat) JVM property value for run-time environment ‘localhost’, ‘TEST’ and ‘PROD’.


The application’s externally defined property values

The application’s configuration data is contained in an external XML file, a common practice in web application development. For this discussion it will suffice to say the application’s configuration data has already been read during the application’s initialization phase conducted when the application is started. During this phase the data is cached into an EGL BasicRecord type i.e. ‘configDataRecord’ which can then be passed to the application’s services as a parameter. In this manner all services can gain access to the application’s configuration data.

Shown below are the entries placed in our XML configuration file that describe connection criteria for MACH1 and MACH2 used for the developer’s (localhost) server, the test (TEST) machine and the production (PROD) machine. (User id’s and passwords are obfuscated.) Library entries, of which there is only one in this example, are separated by spaces. Note the libraries established for each machine for each development environment. In this way, each connection for each machine is assigned to the correct library ensuring the proper set of data is used for each environment. This method ensures that the developer does not have to prefix or qualify SQL statements with embedded library names, which does not translate well when moving the application from a TEST server to a PROD server.

<entry key="localhost.MACH1MachineId">MACH1</entry> <entry key="localhost.MACH1UserID">**********</entry> <entry key="localhost.MACH1Password">**********</entry> <entry key="localhost.MACH1URL">jdbc:as400:MACH1;prompt=false;naming=system;libraries=DEVLIB;transaction isolation=none;</entry> <entry key="localhost.MACH2MachineId">MACH2</entry> <entry key="localhost.MACH2UserID">**********</entry> <entry key="localhost.MACH2Password">**********</entry> <entry key="localhost.MACH2URL">jdbc:as400:MACH2;prompt=false;naming=system;libraries=DEVLIB;transaction isolation=none;</entry> <entry key="TEST.MACH1MachineId">MACH1</entry> <entry key="TEST.MACH1UserID">**********</entry> <entry key="TEST.MACH1Password">**********</entry> <entry key="TEST.MACH1URL">jdbc:as400:MACH1;prompt=false;naming=system;libraries=TESTLIB;transaction isolation=none;</entry> <entry key="TEST.MACH2MachineId">MACH2</entry> <entry key="TEST.MACH2UserID">**********</entry> <entry key="TEST.MACH2Password">**********</entry> <entry key="TEST.MACH2URL">jdbc:as400:MACH2;prompt=false;naming=system;libraries=TESTLIB;transaction isolation=none;</entry> <entry key="PROD.MACH1MachineId">MACH1</entry> <entry key="PROD.MACH1UserID">**********</entry> <entry key="PROD.MACH1Password">**********</entry> <entry key="PROD.MACH1URL">jdbc:as400:MACH1;prompt=false;naming=system;libraries=PRODLIB;transaction isolation=none;</entry> <entry key="PROD.MACH2MachineId">MACH2</entry> <entry key="PROD.MACH2UserID">**********</entry> <entry key="PROD.MACH2Password">**********</entry> <entry key="PROD.MACH2URL">jdbc:as400:MACH2;prompt=false;naming=system;libraries=PRODLIB;transaction isolation=none;</entry>

Fig.1 – The application’s XML configuration file


Making the Connection

Shown below is the EGL code that resides in an EGL service program. The variable ‘server’, passed to the function containing this code, is used to govern which set of logic is to be executed. The value determines the values for the variables ‘machineID’, ‘userid’ and ‘password’.

Once these values are obtained the value for ‘runtimeEnvironment’ is used to obtain the correct connection string or URL. For localhost, this value will come from the XML configuration data. For TEST or PROD, the connection URL is obtained from the JNDI-defined connection criteria established in Tomcat’s context.xml file. See this post for information about that.

//Obtain the runtimeEnvrionment variable from the configuration data cache runtimeEnvironment = StrLib.clip(configDataRecord.runtimeEnvironment); //The program defined variable 'server' contains either 'MACH1' or 'MACH2' if (server == configDataRecord.MACH1MachineId) machineID = configDataRecord.MACH1MachineId; userid = configDataRecord.MACH1UserID; password = configDataRecord.MACH1Password; if (runtimeEnvironment == CommonLibrary.CONST_LOCALHOST) connectionString = configDataRecord.MACH1URL; end if (runtimeEnvironment == CommonLibrary.CONST_TEST) connectionString = "jdbc/MACH1"; end if (runtimeEnvironment == CommonLibrary.CONST_PROD) connectionString = "jdbc/MACH1"; end end if (server == configDataRecord.MACH2MachineId) machineID = configDataRecord.MACH2MachineId; userid = configDataRecord.MACH2UserID; password = configDataRecord.MACH2Password; if (runtimeEnvironment == CommonLibrary.CONST_LOCALHOST) connectionString = configDataRecord.MACH2URL; end if (runtimeEnvironment == CommonLibrary.CONST_TEST) connectionString = "jdbc/MACH2"; end if (runtimeEnvironment == CommonLibrary.CONST_PROD) connectionString = "jdbc/MACH2"; end end //Make the connection sqlLib.connect(connectionString, userid, password);

Fig.2 – Tomcat’s context.xml


Problem? What problem?

While everything explained so far is accurate, there is one problem, if it can be called that. It’s more of a developmental artifact than anything else and can be spotted in the configuration file. Everything will still work, but there are entries which have been rendered superfluous. Can you spot the problem? Hint: see the JNDI entries for comparison. Post your thoughts on this in the comments section below this post.



So, there we have it. We’ve seen how Tomcat configuration and the application’s externally defined property values can facilitate connections to a given database for a whole list of different machines. The value of this effort should be self-evident, that is, it does not promote hard-coded connection criteria in the application’s code which, without modifications, would prohibit deployment across multiple run time environments represented by the developer’s machine, the test or QA machine and finally the production machine.