The WOW i5 Gateway add-on product allows end users to view i5 spool files from the web with a single click. Advanced features also allow for conversion of spool file data into MS Excel and PDF formats. Spool files can be produced with any language or command. The WOW i5 Gateway Repository is used to register, view, and manage existing spool files. Advanced WOW security allows for spool files to be viewable only by authorized users.

The i5 Gateway add-on offers the following features:

• CL Command Operation – Lets a user create operations that run 1 or more i5 CL commands. The results from the operation call con consist of the spooled file data, job messages and/or the complete i5 joblog.
• Spooled File Manager – Lets a user view i5 spooled files as inline data, a PDF file, or as an Excel file.
• i5 External API – Lets a user invoke a URL from a native i5 program (e.g. RPG). The URL can then call a WOW operation which can be used to insert or update data on any DB accessible from the WOW server. The WOW operation can also be used to copy data from operation's results back to a table on the i5.

Installation

Setup

Creating a CL Command Operation

The Spooled File Manager

Spooled File Manager Operation

Spooled File Viewer Operation

Using the Spooled File Manager

Utilizing the i5 External API

Calling an Operation that Alters Data

Calling an Operation that Copies Data Back to the i5

Installation

PlanetJ's WOW i5 Gateway add-on is very simple to install, especially if you already have tomcat and WOW installed on your system already.

There are really only 2 steps to install the i5 Gateway:

1. Restore the WOW metadata library (WOWI5GTWY, provided in your download package):

1. FTP the save file provided (wowi5gtwy.savf) to your i5. Assuming the save file created has the same name, then
2. RSTLIB SAVLIB(WOWI5GTWY2) DEV(*SAVF) SAVF(WOWI5GTWWOW Y) MBROPT(*ALL) ALWOBJDIF(*ALL) RSTLIB(WOWI5GTWY)

2. Add the jars to your existing WOW webcontext in Tomcat:

1. Copy the jars found (pd4ml_demo.jar, wow-i5-gateway.jar) in the wow-i5-gateway folder and paste it into your existing webapp's lib folder: ~\Tomcat 6.0\webapps\wow65\WEB-INF\lib
2. Restart Tomcat to pickup the new jar files.

Setup

Included with the install, is a WOW metadata library (similar to PJUSER64), which will provide basic i5 Gateway functionality, to set up this metadata library, please follow these instructions:

• Follow the instructions from the ReadMe.txt (or HERE) for WOW i5 Gateway installation, which will have you  install the needed jars in Tomcat and restore a WOW metadata library (WOWI5GTWY).
• Sign into the WOW Builder and update the connection with the correct system configuration.
• Run the WOW Builder for i5 Gateway: http://localhost:8080/wow65/wow?_pj_lib=wowi5gtwy

The link assumes the browser is run from the local WOW server (localhost) and the web context is wow65.

• Sign in with the userid=i5@planetj.com, password=gateway.
• Click on connections and update the I5CONN connection with your metadata system configuration values.

• If you run the application, the signon is your i5 signon:

Creating a CL Command Operation

With this add-on, a user can create an operation that runs a i5 CL command. After the command has completed, feedback from the command call can be displayed as the operation results. The feedback can include spooled files, the AS400 joblog and any error messages encountered.

To create a CL Command operation, set the following operation fields from the WOW Builder:

• Operation Type – The type must be set to either HTML Code or HTML Code Association.
• Operation Code – Set this field to the CL command to be run. NOTE: Most substitution parameters are supported here, such as user (???), global (??!), request attribute(??*), runtime(??:), etc. If the operation is HTML Code Association, Row parameters (??) are supported.

NOTE: If you need to run more than 1 CL command, separate them with a semi-colon. For example:

• ADDLIBLE LIB1; CALL PGM(RPGPGM1)

• Properties - there is an optional property group that can be used to alter the behavior/output of the CL Command operation. The property group is i5Command. Below is a list of properties supported by the i5Command property group:

• messages - TRUE, FALSE. Should error messages from the command call be displayed, when the command fails? Default is TRUE;
• joblog - FALSE, TRUE. Should the i5 joblog be displayed? Default is FALSE; This option is useful for debugging problems with the CL command execution.
• alias - connection alias name. Allows the user to override alias, in case the application connection alias is not desired.
• splfnum - LAST, FIRST, ALL, NONE. If more than 1 spooled file is generated, which spooled files should be displayed? Default is LAST.
• clrsplf - TRUE, FALSE. Should the spooled file(s) be deleted after they are displayed? Default is TRUE;

• Operation Class – You must set this field to => planetj.dataengine.i5gateway.CLCommandOperation

An example of the output from a CL Command operation is:

The Spooled File Manager

With this add-on, a user can display any spooled file registered in the WOW spooled file metadata table, WSPLF. The spooled file data can be displayed in the browser, sent to a PDF, or sent to an Excel file.

To create a spooled file manager operation, 2 operations will be needed. One for displaying WSPLF, and one for displaying the spooled files. This document will also show you how to use the spooled file manager as well.

Spooled File Manager Operation

To create a spooled file manager set the following operation fields from the WOW Builder:

• Operation Type – Set the type to SQL.
• Operation Code – Set the SQL code to query the spooled file table, WSPLF:

SELECT * FROM planetj_usr.WSPLF

• Row Class – Set the Row class file to use:

planetj.dataengine.i5gateway.SpooledFileRow

• JSP File – Set the jsp file to use this jsp:

/wow/plugins/i5gateway/jsp/pw_results.jsp

Spooled File Viewer Operation

To create the spooled file viewer operation set the following operation fields from the WOW Builder:

• Operation Type – Set the type to Execution Group.

• Display Rule – Set the rule to hide so the operation is not visible from the menu.

• JSP File – Set the jsp file to be used to view spooled files with:

/wow/plugins/i5gateway/jsp/splf.jsp

• Usage ID – Set the operation's Usage ID to 801, so WOW can identify which operation should be used to display the spooled files.

Using the Spooled File Manager

To use the spooled file manager, first run the operation. Once WSPLF is populated with spooled file metadata, you will see several row actions for handling spooled files:

• View Spooled File () – The first icon lets you view the spooled file within the browser.
• Convert to Excel () – The second icon lets you convert the spooled file to an excel file.
• Convert to PDF () – The third icon lets you convert the spooled file to a PDF file.

Below is sample output from viewing a spooled file:

Utilizing the i5 External API

This API allows any i5 program with database access the capability to call a URL, which can then be used to call a WOW operation. The WOW operation can then perform any needed function, such as a) inserting or updating a row of data, or b) copying remote data back to a table on the i5. The WOW operation can be run using any connection configured within WOW (SQL Server, MySQL, Oracle, i5, etc.).

All that is needed to invoke the URL, is a COMMAND (WOW table in WOWI5GTWY) row with the required fields populated. The URL can be invoke by either inserting the COMMAND row or updating it. The COMMAND file should be included with the WOWI5GTWY library. When the row is inserted or updated, a trigger program attached to the COMMAND file (via an ADDPFTRG command) is run, which sends the command and parameters to the WOW server. The result depends on which WOW operation is run and what URL parameters are included.

The following is a step by step example of how to use the API:

• Make sure the WOW Server is running.
• Make note of the IP address (e.g. 100.12.5.32), port (e.g. 8080) and web context name (e.g. wow65).
• Add a trigger to the COMMAND file. You will need to decide how you want the COMMAND file to be triggered. For our example, the trigger is only called for an update. The CL command to do this is:

ADDPFTRG FILE(WOWI5GTWY/COMMAND)

TRGTIME(*AFTER) TRGEVENT(*UPDATE)

PGM(WOWI5GTWY/APITRIG)

ALWREPCHG(*YES)

Calling an Operation that Alters Data

In this section we will walk through an example call of an operation to alter data.

• Use the WOW Builder to create the WOW Operation that will be invoked by the trigger.

• Operation Type: SQL
• Operation Code Example:
• INSERT INTO mylib.triggertst (TDESC,TID) VALUES(??*DESC,?)

• “??*DESC” tells WOW to set field TDESC to a parameter value whose name is DESC.

NOTE: Parameter names are case sensitive.

• “?” tells WOW to set this field value with the default defined in TID's FD.
• The insert operation should have it's execution rule (Advanced section) set to “Execute Only”, so WOW knows that no prompting is involved.

• Make note of the operation ID for the next step.

• Use the i5 Gateway application to add a URL entry to the COMMAND file:

• Sign on to the WOW i5 Gateway application:

• URL would look something like: http://10.2.0.10:8080/wow65/runApp?id=6&_pj_lib=wowi5gtwy
• Use your i5 user ID and password

• Trigger URL Call > COMMAND File > Insert

• Set the fields as needed and insert the command entry:

• Active – Is this command entry active?
• Description – Description of entry.
• IP of WOW Server – The WOW server's IP address.
• Port of WOW Server – Normally 8080 or 80.
• Java Class Name and Path – In most cases, use the default class provided by WOW. This class tells WOW to call a WOW operation:
  planetj.dataengine.i5gateway.RunOpRequest.
• URL Parameters – These are the parameters to be used to tell WOW which operation to run, plus any user data you need to pass to the operation. The parameters will be appended to the end of the URL and can be specified in any order. All parameters must begin with an “&”. The value before the “=” sign is the parameter name and the value after the “=” is the parameter value. The parameter values cannot contain any blanks. If blank spaces are needed, use the “+” sign for each space:

• id – This parameter tells WOW the ID of the application that contains the WOW operation. This parameter is required.
• lvid - This parameter tells WOW the ID of the operation to be run. This parameter is required.
• _pj_lib – Use this parameter to override the WOW user library. The default WOW user library is defined in the web.xml file. This parameter is optional.
• All other parameters – Any other parameter names will be passed on to the operation as a user parameter. To reference the user parameters in the WOW operation, preface the parameter value with “??*”. For example, if your parameter is “DESC=i5+Gateway”, then in the operation, you can specify the parameter as “??*DESC”.

NOTE: The parameter names are case sensitive.

• Change your i5 program to update that same record/row in WOWI5GTWY/COMMAND, which will trigger the call to the designated WOW operation.

Calling an Operation that Copies Data Back to the i5

• Use the WOW Builder to create the WOW Operation that will be invoked by the trigger.

• Operation Type: SQL
• Operation Code:

• “??*FNAME” tells WOW to replace that parameter in the WHERE clause with the URL parameter value for FNAME. The same applies for ??*LNAME.
  NOTE: Parameter names are case sensitive.
• When using copyOption FIELDNAME (the default, see the next section for details), if the source and destination field names are not the same, you can use a derived field name as a way to rename the source field so that it matches with the destination field. For example, if the source field name is ADDRESS and the destination field name is ADDR1, then specify the field name as the following in the list of SELECT fields: ADDRESS as ADDR1

• Properties:

You must specify the CopyTable property group in the Properties field. The CopyTable property must be specified, since the source and destination tables cannot be the same. The CopyTable properties are as follows:

• table - Destination table name. Defaults to the same table name as the source table.
• schema - Schema to use to locate the destination table. Defaults to the same schema name as the source table.
• alias - Destination table's WOW connection alias name. Defaults to the same alias as the operation (source table system).
• copyOption - Determines how fields are matched up between the source and target tables. Defaults to use field names. Supported values:

• USAGEID - If a source field and destination field have the same usage ID (defined in field descriptors), data for that field is copied.
• FIELDNAME - Default. Use field names to determine how source and destination fields match up. If a field name from the source query matches with a field name in the destination table, data for that field is copied.

• append - FALSE, TRUE. FALSE means replace the data in the table. TRUE means append data to the table. The default is FALSE (replace).

• Use the i5 Gateway application to add a URL entry to the COMMAND file:

• Sign on to the WOW i5 Gateway application:

• URL would look something like: http://10.2.0.10:8080/wow65/runApp?id=6&_pj_lib=wowi5gtwy
• NOTE: The URL may be slightly different based on the IP address that you are running the i5 Gateway application from, though everything past the web context ('/wow65/') is accurate.
• Use your i5 user ID and password

• Trigger URL Call > COMMAND File > Insert

• Set the fields as needed and insert the command entry:

• Active – Is this command entry active?
• Description – Description of entry.
• IP of WOW Server – The WOW server's IP address.
• Port of WOW Server – Normally 8080 or 80.
• Java Class Name and Path – Use the below class which is designed to run an operation and then copy the operation's result data back to an i5 table:
  planetj.dataengine.i5gateway.CopyTableDataRequest
• URL Parameters – These are the parameters to be used to tell WOW which operation to run, plus any user data you need to pass to the operation. The parameters will be appended to the end of the URL and can be specified in any order. All parameters must begin with an “&”. The value before the “=” sign is the parameter name and the value after the “=” is the parameter value. The parameter values cannot contain any blanks. If blank spaces are needed, use the “+” sign for each space:

• id – This parameter tells WOW the ID of the application that contains the WOW operation. This parameter is required.
• lvid - This parameter tells WOW the ID of the operation to be run. This parameter is required.
• _pj_lib – Use this parameter to override the WOW user library. The default user library is WOWI5GTWY. This parameter is optional.
• All other parameters – Any other parameter names will be passed on to the operation as a user parameter. To reference the user parameters in the WOW operation, preface the parameter value with “??*”. For example, if your parameter is “FNAME=JOHN”, then in the operation, you can specify the parameter as “??*FNAME”.

NOTE: The parameter names are case sensitive.

• Ensure that the destination table exists on the i5.
• Change your i5 program to update that same record/row in WOWI5GTWY/COMMAND, which will trigger the call to the designated WOW operation.