DB2 AS400 Connector Guide

The DB2 AS400 connector provides an API to DB2 databases deployed on AS400 servers through which query results can be obtained and distributed to all consumer channels.

The DB2 AS400 connector data flow consists of the following steps:

First, the data consumer sends a REST request specified in JSON to the OpenLegacy DB2 AS400 microservice.
The asset then converts the JSON data query to [binary? RPC?].
The [binary?] request is sent to the DB2 AS400 server.
The DB2 AS400 server returns the query result set to the service.
Finally, the service converts the response binary code to JSON and returns it to the data consumer.

The connector configuration is done in the OpenLegacy CLI and the OpenLegacy Hub. First, use the CLI to generate an asset containing a model of the API input and output parameters and upload the asset to an OpenLegacy Hub project. Next, generate a microservice on top of your uploaded asset in the hub. Finally, deploy the microservice on the cloud or on-prem and test the service to confirm that it provides a working API to your DB2 tables.

The DB2 AS400 connector can access the AS400 DB2 database by calling stored procedures or executing prepared statements. The following options determine which access method is used:

Stored Procedures: Set the --executable option to the name and location of the stored procedure.

Prepared Statements: Set the --query option to the SQL statement.

Create DB2 AS400 module and assets

First, create a DB2 AS400 module to contain Temenos assets.
Second, test the connection to the remote DB2 AS400 server.
Third, create a JSON-based DB2 AS400 asset.
Finally, test the created asset by sending a test query to the remote DB2 AS400 data source.

Step 1. Create a DB2 AS400 module

First, create the DB2 AS400 module.

[module-name]> ol create module m-db2-as400-db --connector db2-as400-db

[module-name]$ ol create module m-db2-as400-db --connector db2-as400-db

Step 2. Test the connection to the remote DB2 AS400 server

Run the ol test-connection command to verify that the connection properties are correct and hence that the DB2 AS400 server on the remote data source is accessible.

Navigate to the module folder

[module-name]> cd m-db2-as400-db

[module-name]$ cd m-db2-as400-db

From under the module directory, display the help menu of the ol test connection command to review the DB2 AS400 connector connection properties.
```
> ol test connection --help
```
```
$ ol test connection --help
```

The ol test connection help menu displays the connector connection properties.

--verbose                     Enables verbose mode
--verbose-debug               Enables debug mode
--verbose-trace               Enables trace mode  
--url TEXT                    Specify JDBC url (required)
--username TEXT               Specify database user (required)
--password TEXT               Specify database password
--query-timeout TEXT          Specify query timeout (default: 30000)
--connection-test-query TEXT  Specify connection test query (default: VALUES current date)
--pool-max-size TEXT          (default: 5)
--profile TEXT                Configuration profile
-h, --help                    Show this message and exit

To learn more about the ol test connection properties see below.

Run ol test connection.

[module-name]> ol test connection --url "jdbc:as400://as400.openlegacy.com;libraries=TEST;" --username "OPLSECOFR" --password "OPENLEGA"

[module-name]$ ol test connection --url "jdbc:as400://as400.openlegacy.com;libraries=TEST;" --username "OPLSECOFR" --password "OPENLEGA"

Step 3. Add an Asset to the DB2 AS400 Module

After verifying the connection parameters, you can create a DB2 AS400 asset by executing the ol add command. This command can be used in two ways:

SQL Query: Provide an SQL query statement, which the connector analyzes to extract the SELECT column names and data types. This data is used to define the input metadata of the asset. The connector then executes the query on the DB2 AS400 system, analyzing the result set to define the output metadata of the asset.
Stored Procedure: Point to a stored procedure, which the connector will analyze to extract its input and output structures, using this data to define the asset's metadata.

After verifying the connection parameters, you can create a DB2 AS400 asset by executing the ol add command. The command receives the SQL query statement and extracts the SELECT column names and data types. The connector uses this data to compose the input metadata parameters of the asset. Next, the connector applies the query statement on the DB2 AS400 and analyses the returned result set to compose the asset output metadata parameters.

Review the properties of ol add command.

[module-name]> ol add --help

[module-name]$ ol add --help

The ol add help menu displays the connector property list.

Parser options | Executable:
  --advanced-result-set-query TEXT  Specify advanced query for fetching a result set
  --executable TEXT                 Specify database executable path to procedure or function (required)

Parser options | Query:
  --query TEXT                      Specify query value (required)
  --execute-query                   Execute query for analyze
  --input-columns <text>            Provide input column info

Parser options:
  --operation-name <text>           Specify operation name
  
Options:
  --verbose                         Enables verbose mode
  --verbose-debug                   Enables debug mode
  --verbose-trace                   Enables trace mode
  --additional VALUE                Additional properties
  --url TEXT                        Specify JDBC url (required)
  --username TEXT                   Specify database user (required)
  --password TEXT                   Specify database password
  --query-timeout TEXT              Specify query timeout (default: 30000)
  --connection-test-query TEXT      Specify connection test query (default: VALUES current date)
  --pool-max-size TEXT              (default: 5)
  --profile TEXT                    Configuration profile
  -h, --help                        Show this message and exit

Examples:

with a prepared statement (using the --query option):

[module-name]> ol add --query 'SELECT * FROM TEST.ACCOUNT_TEST_IN WHERE ID=?' --operation-name by_id

[module-name]$ ol add --query 'SELECT * FROM TEST.ACCOUNT_TEST_IN WHERE ID=?' --operation-name by_id

with a stored procedure (using the --executable option):

[module-name]> ol add --executable DEMO.BANK_EXAMPLE.GET_ACCOUNT_BY_ID --advanced-result-set-query CALL DEMO001.GET_ACCOUNT_ORDERS('A100054321')

[module-name]$ ol add --advanced-result-set-query CALL DEMO001.GET_ACCOUNT_ORDERS('A100054321')

To learn more about the ol add properties see below.

Step 4. Test the DB2 AS400 asset

After adding the asset, you can test it by executing the ol test asset command to verify that you can request and receive actual business data from the data source.

From your module folder root go to assets/by_id/test_data/case1/.

[module-name]> cd assets/by_id/test_data/case1/

[module-name]$ cd assets/by_id/test_data/case1/

To view and edit the test input data open in.json.
```
case-1> notepad in.json
```
```
case-1$ notepad in.json
```

in.json contains the information sent to the remote business operation. It consists of the input keys and values that the business operation requires.

{
  "id" : 0
}

In in.json, type the values of the input properties. In the example above, for the id field, type 4.
Now you are ready to run test-asset.
```
> ol test asset by_id
```
```
$ ol test asset by_id
```

The response from the remote DB2 AS400 server is displayed:

{
  "select" : [ {
    "id" : 4,
    "accountNumber" : 12346,
    "balance" : "10500",
    "polled" : "true",
    "creationDate" : "2022-01-13 08:26:26.000000 +00:00"
  } ]
}

Step 5: Push the module to the OL Hub

Now that you are sure that the DB2 AS400 assets are operating as they should, that the connecting properties are accurate, and that the DB2 AS400 database responds with the correct data, you can push the finalized module to the hub.

❗️
Make sure you are logged in to the OL Hub before executing ol push module.

Run the ol push module command from the module folder root.
```
[module-name]> ol push module
```
```
[module-name]$ ol push module
```

The CLI displays the command success status:

Module demo_module pushed successfully to the HUB

Step 6: Continue in OL Hub

Your module is now on the OL Hub and can serve as a foundation for generating services for your data consumer.

Review the new module with assets in the OL Hub.

Next generate a microservice on top of your uploaded asset in the OL Hub.

Connector Options

--executable (required for stored procedure data fetching)

The stored procedure name

--executable GET_ACCOUNT_ORDERS

--advanced-result-set-query (for stored procedure data fetching)

The advanced query for fetching a result set

--advanced-result-set-query CALL DEMO001.GET_ACCOUNT_ORDERS('A100054321')

--query (required for prepared-statement data fetching)

The SQL statement querying the DB2 AS400 database

--query "SELECT \* FROM ACCOUNTS WHERE ID = ?"

--operation-name (required)

The name of the generated asset

--operation-name mySpecialName

--additional

key-value pairs of dynamic options

--additional [missing_value]

--url

The URL of the Java interface to the db2 database

--url jdbc:as400://as400.openlegacy.com;libraries=TEST/p>

--username

The database user

--username OPLSECOFR

--password

The database password

--password OPENLEGA

--query-timeout

The query timeout (default: 30000)

--query-timeout 10000

--connection-test-query

This query objective is to validate that a given connection to the database is still alive. It is executed before the connection is assigned from the connection pool. If your driver supports JDBC4, we strongly recommend not setting this property.

--connection-test-query "SELECT * FROM ACCOUNTS WHERE ID = 'A100000001'"

--pool-max-size

Thread pool maximal size (default: 5)

--pool-max-size 7

--profile

The connection profile of ol add or ol test connection (see: Connection Profiles).

Create DB2 AS400 module and assets

Step 1. Create a DB2 AS400 module

Step 2. Test the connection to the remote DB2 AS400 server

Step 3. Add an Asset to the DB2 AS400 Module

Review the properties of ol add command.

Examples:

Step 4. Test the DB2 AS400 asset

Step 5: Push the module to the OL Hub

❗️Make sure you are logged in to the OL Hub before executing ol push module.

Step 6: Continue in OL Hub

Connector Options

❗️
Make sure you are logged in to the OL Hub before executing ol push module.