Many of you are familiar with the capabilities provided with the green-screen product called Query/400. As you know, businesses use Query/400 to generate reports by selecting and sequencing files and fields using DB2/400 files. With the world moving away from green screen toward browser-based Web interfaces, wouldn't it be great if there were a way to have Query/400 available on the Web? Well something very similar is available -- and it's free.
This tip will show you how to build a Web application that accesses iSeries data in fewer than 15 minutes using a tool called the Web Object Wizard (WOW). Included are step-by-step instructions on how to build applications and how you can download a free Edition to use on your system.
What is WOW?
WOW is a rapid application development (RAD) tool used for creating Web applications that access iSeries files and programs. WOW runs on top of IBM's WebSphere Application Server or Apache's Tomcat. Most programs require no or very little manual coding. For more complex applications, WOW can provide a base functionality, which can be further customized by application designers using Java. Web applications are created by specifying SQL/JDBC statements. WOW generates and displays the results in HTML to the user's browser.
WOW application example
For the demonstration, the iSeries data used is stored in the EMPLOYEE table in the PJDATA library. For reference, use the table and field names in the below screen shot.
You can download WOW or run it from PlanetJ's servers. I recommend first running an example from PlanetJ's servers and then installing a copy on your servers.
Running WOW from your servers (optional)
See instructions on the bottom of this document for details on downloading.
Running WOW from PlanetJ's site
Open an Internet Explorer browser and log on to
This will open the WOW builder application. This is the IDE environment used by WOW programmers to create Web applications. Once you have started WOW, you will see the sign-on screen in your browser:
If you have already registered, enter your e-mail address and password to log in. If you have not registered yet, click the "Sign Up Now" button to go to the registration screen:
Required fields on the registration screen are indicated with a red asterisk. After you have entered your information, click the insert button to store your new account into the database. You can now log in using the e-mail address and password you specified in the registration information.
Creating a Connection Definition
After signing on, you should see the main screen of WOW:
There is a set of four wizard steps that guide WOW programmers through the process of creating Web applications.
Step 1: Create a connection
Before creating an application, first define a database connection that the application will use.
If using the WOW from PlanetJ's site, you may skip this step and proceed to Step 2 ("Setup applications"). A connection will be automatically supplied for you.
If running the WOW locally (on your own servers), you will set up the connection now. The process is described below.
Click on the "1 Setup Connection(s)" link along the top to view a list of all the database connections. Unless a connection has been defined, this screen will be blank. Click the "insert" button to create a new connection definition:
To define a database connection, specify the following attributes:
- Connection Alias -- Any text uniquely identifying this database connection.
- URL -- The JDBC URL of the database. The URL requires the format: "jdbc:as400:YourIPAddress" . In the above example, the iSeries IP address is 192.168.0.10. Specify your iSeries IP address or iSeries system name.
- JDBC Driver -- The type of JDBC driver to use. For connecting to the iSeries from a Windows server, use the "AS/400 Remote" driver.
- User ID -- The user ID to use when connecting to the database. This must be a valid OS/400 user profile.
- Password -- The password to use when connecting to the database. This must be a valid password for the user profile specified.
When done, click the "insert" button to save the new connection definition. This will return you to a listing of all the connection definitions that have been created, which will now include the newly created connection definition.
Setup 2: Setup the Application
After creating a connection definition, select the "2 Setup Application(s)" link along the top of the page. Note: If running from PlanetJ's servers, a connection will be available for you to use. To insert a new application, click the "Create Application" menu item on the left hand of the screen. This brings up the Application Creation screen:
This screen allows information to be entered, which will define the application. The properties of a WOW Application include the following:
- Name -- The name of the application.
- Description -- A description of the application.
- Connection Alias -- An alias for the system on which the application's data will be kept. This must be one of the connection definitions that you have created or one that was supplied to you, such as *DB2_IBM_SHARED.
- Sign On Type -- The type of sign-on security that the application will use. Leave the default as "Unsecured".
- Initial SQL Operation -- The type of operation the application should display initially. (This will be covered in more detail later.)
- Theme -- The display theme that the application will use. The display theme determines the background colors and images that your application will use.
There are additional advanced application properties that can be set for advanced programming.
It is only necessary to fill in three of the fields: name, description and connection alias. After filling in the values as shown above, click the insert button and then the cancel button to return to the main WOW screen. The application just defined should be listed in the view of all applications:
Adding an SQL operation to the sample application
A WOW application contains one or more operations. Although WOW can create various operations, in this example focus will be on the production of SQL operations. By using SQL, various data inquiry operations can be built in to the application. In this sample application, we will retrieve data from the EMPLOYEE table.
To add an SQL operation to the application just created, select the application and click on the "3 Setup Operations" link along the top. This brings up a list of all the operations in the application. (Since the application was just created, it won't contain any operations.) Choose the "Create an Operation" menu item to create a new SQL operation.
The Create Operation screen allows you to specify the attributes of an SQL operation. When it first appears, it will contain several default values:
The attributes of an operation include the following:
- Label -- Menu item text that identifies this operation on the application's main screen.
- Title -- The title that will be displayed when the user is viewing the operation.
- Operation Type -- Type of operation you want to create. (SQL is most common and used in this example but there are many more described in the builder's guide.)
- Description -- A description of the operation.
- Operation Code -- The statement that will be run when the user selects this operation.
- Instructions -- Text that will be shown to the user when the user runs this operation.
For this example it is necessary to fill in only four additional fields. For the label field, enter "Employees". This is the text that will link to this SQL operation when the user is running the application. For the title field, enter "Employees", and for the description, enter "Get all employees in company".
For the SQL field, enter the SQL statement
SELECT * from pjdata.employee
This will select all of the fields and employees from the employee table in the pjdata library. Note: You can specify any valid file and library if you don't have access to the sample data. The sample SQL statement is fairly simple, but a more complex statement could be entered. This is what the screen should look like after entering in the values:
Click insert, and that's it -- the sample WOW application is complete with a single operation shown below.
Running the application
To see the application in action, click the application name or select the application, then click on the step 4 Run button under Web Application Creation Steps.
A new browser window will open with the running application.
The labels of all the application's operations (in this case there is only one SQL operation) are shown in a column on the left side of the screen. Clicking on the "Employees" link brings up the screen of the SQL operation:
This operation shows all the employee records in the EMPLOYEE table. Insert additional operations using SQL statements to try out the various features. You may directly specify field names, as shown in the EMPLOYEE data file image above. For example, repeat the previous steps adding a new operation called "Employees by Dept" and use SQL "Select lastname, workdept, phoneno from PJDATA.EMPLOYEE order by lastname". Experiment with your own files if you wish. WOW can access any data.
Creating field descriptors
A Field Descriptor describes a field in the database (such as "FIRSTNME", or "HIREDATE" in the EMPLOYEE table). A Field Descriptor contains information such as the external name of a field, whether or not the field is required, or the type of data a field can hold (numeric, time, etc). Field Descriptors can be created for files to allow the column headers and various attributes of target fields to be customized.
To create Field Descriptors for the EMPLOYEE table, click on the small "FD" icon above the result data table. Note: If running from PlanetJ's site, these will have been already created.
This will bring up the Field Descriptor Manager in a new window.
Click the "Create FDs" button (under the table functions). This will read the database and create Field Descriptors for every field in the EMPLOYEE table. The list of Field Descriptors will then be displayed (scroll down to see the whole list):
Here, you could edit a Field Descriptor's external name by typing text into the "External Name" column. Note: DO NOT change the field name; this must match the field actual name in database. When complete, click the "Update Table" on the bottom of the screen to apply your changes.
Now go back to the window containing the application and rerun the query. Now the column headers will show the new external names you specified:
Deploying your application to users
Once an application has been built, it is ready to be run by users. A WOW application can be run by any user on your network or Internet/intranet/extranet. WOW applications can be run by specifying a URL in the form: http://IP://wow60/run?id=x where IP is the TCP/IP address of the WOW server and x is the WOW application id. The IP can be found by opening a DOS window on the WOW server and issuing an IPCONFIG command as shown below:
In this example, WOW applications can be run using
where 0 is the WOW application id that is found in Step 2 Setup Applications in the WOW Builder. Each new WOW application gets the next sequential number.
Uses of WOW
As you have seen, customizable Web applications can be built in minutes. Building a similar application in Java or CGI could take weeks or months.
You can put WOW to use to build applications such as these:
- Management Reports. WOW can be used to query order and sales files bring real time data to management with a single browser click.
- Employee directories
- Customer inquiries
- Product and inventory viewing
- Insurance claims
- CRM, help desks, FAQs, Q &A databases, etc.
For additional information, check out these Web sites:
Get a copy of WOW
To get the free entry-level WOW download link, send an e-mail to email@example.com. Download links and instructions will then be emailed to you. Again, the functionality is very similar to Query/400 -- only browser based. Check it out!
About the authors: Paul Holm is a former IBM-Rochester WebSphere, Java, and DB2 developer/consultant. He specializes in helping iSeries RPG shops develop Web-based applications. Paul can be reached at firstname.lastname@example.org.