If you want InterFormNG2 to connect to a database in order to get input files for processing, then you need to configure the database as below.
This section contains these topics:
4.Setup SQL via library list on the IBM i
5.Example connection to IBM i database.
7.How to reduce database connections
A prerequisite for a database connection is, that you have installed a JDBC driver, that InterFormNG2 is able to access. This is covered in the section, Database driver.
In order to setup a database connection, you first need to sign in as described here.
Then you click Database on the left below Workflow:
The options are these:
You can create multiple database connections, if you click the '+' icon in the top. Here two connections has been created: Production and Test:
Name
The name to reference to in the InterFormNG2 workflow for database input.
Connection
Database
The type of database, that you want to connect to. These types are currently available:
Apache Derby, Firebird SQL, HSQLDB, IBM DB2, IBM DB2 (iSeries/IBM i), IBM Informix, MariaDB, MS SQL Server, MySQL, MySQL (InnoDB), Oracle, PostgreSQL, Teradata and Other.
JDBC driver class
The JDBC driver class for the connection.
For IBM i this can e.g. be: com.ibm.as400.access.AS400JDBCDriver
Connection URL
The URL of the database.
For "connection URL" specify the JDBC protocol URL used to connect with the database. The URL should be without username and password, since these are specified separately. The URL typically looks like this: jdbc:<DATABASE-TYPE>://<IP>:<PORT>/<DATABASE_ID>
For instance for MySQL it could be: jdbc:mysql://localhost:3306/interformdatabase
For IBM i the connection could be:jdbc:as400:MyIBMi/MYLIB;naming=system;translate binary=true
,where MyIBMi is the host name or IP-address of the IBM i and MYLIB is the library in which you want to create the table/file.
Note for Database as workflow input
For IBM i: If you want to allow InterFormNG2 to change the contents of files e.g. for using a database as workflow input: Please notice, that you need to either start journaling for the file/tables involved (JOB and METADATA) or you can also chose to turn off commitment control on the connection by adding this to the connection URL:
;transaction isolation=none
If you want to add journaling for the two files (with the fixed names: JOB and METADATA), then you can do it in this way (assuming that the library is MYLIB):
1.Create a journal receiver (if you do not already have one). You can do that with the command: CRTJRNRCV JRNRCV(MYLIB/MYJOURNAL)
2.Create a journal (if you do not already have one). CRTJRN JRN(MYLIB/MYJOURNAL) JRNRCV(MYLIB/MYJOURNAL)
3.Start journaling for the two files: STRJRNPF FILE(MYLIB/JOB) JRN(MYLIB/MYJOURNAL), STRJRNPF FILE(MYLIB/METADATA) JRN(MYLIB/MYJOURNAL)
How to select files in SQL via a library list on IBM i
This section concerns database lookups to files, that are found on the IBM i platform. On the IBM i it is possible to dynamically select a file via a library list. In short a library list on the IBM i is the search path, if you refer to an object without specifying a specific library. The library list is searched until the first object with the specified name (and type) is found. This can be very useful for customers, that e.g. are running an ERP solution, where different environments (e.g. a production and a test environment) are selected via different library lists.
This SQL statement is an example of an select, which queries the file, TESTFILE via the library list:
ng:databaseLookup('PMK250', 'select * from TESTFILE where NUMBER=?', '1001')/result/row[1]/TEXT1
You can specify the library list in two ways as described below:
1. Set the library list on the user profile used for the connection.
One way to set a specific library list is to use the library list used, for the user profile used for the connection, which is selected here:
This user profile refers to a job description and you can set the library list on this job description (all jobs referring to this job description will get this library list).
Here is an example:
The user profile refers to a specific job description:
And here is the library list setup in the job description:
2. Setup the library list on the connection URL
You can also directly setup the library list for the file search directly on the connection URL. The format is: "libraries=mylib1,mylib2", where mylib1 and mylib2 are the libraries in the library list.
So here is an example of a connection URL, that use this option:
jdbc:as400:192.168.250.250;naming=sql;libraries=kse2,kse3;translate binary=true;transaction isolation=none
If you specify the library list on the connection URL, then this will overrule the library list of the user profile (job description) as described above.
User name
Credential for signing on to the database.
Password
Credential for signing on to the database. After you save the configuration the password will be encrypted, so no one can see what you entered here, so you might need to store a copy of the password elsewhere.
Test connection
Test the connection to the database.
Create the tables
Create necessary tables in the database. It will create the tables JOB and METADATA.
An example of a database connection to an IBM i (iSeries or AS400) is shown below:
The connection URL is here:
jdbc:as400:192.168.250.250;naming=system;translate binary=true
When the connection is tested a successful connection can result in this to be shown in the bottom, if you are using an older jt400.jar file as the jdbc driver:
A database connection to an MS SQL Server can be setup like below:
The connection URL is in this case:
jdbc:sqlserver://MyOwnServer;databaseName=USR183SQL;encrypt=true;trustServerCertificate=true
The ”encrypt=true” option is necessary, if the database require an encrypted connection. ”trustServerCertificate=true” is required, if the database certificate is from a CA, that is not included in the Java trust store.
Database driver
Hint for the JDBC database driver are found in the section, Database driver.
How to reduce the number of database connections
InterFormNG2 may have multiple database connections and in e.g. a multi-tenant setup it may be necessary to reduce the number of active database connections. For that it is possible to setup settings for min and max pool size added, plus the possibility to combine pools in one pool (per tenant).
The following options can be added to the configuration file, application-default.properties:
The tenants can resuse same pool connection and combine if combine is true:
ng2.poolProvider=com.interform400.connectionPool.InterFormConnectionPool
If true, all pools will use the ng2db.dbdatasource.* settings
ng2.combinePools=true
Sets the min pool size and max pool size pr tenant
ng2.minPoolSize=0
ng2.maxPoolSize=5
Seconds before closing an idle database connection, 0 is keep alive forever
ng2.maxIdleTime=10
If none of these are set, these default values are used:
ng2.minPoolSize=1
ng2.maxPoolSize=5
ng2.maxIdleTime=0
ng2.poolProvider=org.hibernate.connection.C3P0ConnectionProvider
ng2.combinePools=false
