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:

1.Prerequisite

2.Configuration

3.Connection

4.Setup SQL via library list on the IBM i

5.Example connection to IBM i database.

6.Example MS SQL connection.

7.How to reduce database connections

Prerequisite

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.

Configuration

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.

Connect to IBM i database

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:

MS SQL connection

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