resources: podcast

Coders have always needed to query a database to view a particular set of records. On the System i, Query/400 has been the standard tool for browsing a database, and I've always been impressed by how quickly programmers can slap together a Query/400 report. However, when you're developing in WebSphere Development Studio Client (WDSc), switching to a green screen to write a query doesn't make sense, particularly when you consider that WDSc has its own built-in query facility: The Data Perspective (Figure 1) provides an SQL-based view of your database, along with a complete database-development environment. (I believe you should be using SQL — not Query/400 — to browse your database, and the most convenient place to do that is in the Data Perspective.)

But what if you don't feel familiar enough with SQL to forgo Query/400? Familiarize yourself! Of all the languages you should learn, SQL is the most practical. Besides, the Data Perspective has an SQL Builder that walks you through the creation of a query. And after you're comfortable with creating ad hoc SQL queries, you'll be more likely to use SQL in your applications. So let's take a look at the Data Perspective and embark on a tutorial that shows you how to use its SQL Builder to write custom queries.

Configuring the Data Perspective

Before you can use the Data Perspective, you need to create a Data Development Project. You'll use the Data Development Project to store your SQL statements and stored procedures. Here are the steps to follow:

1. From WDSc's application menu, select Window| Open Perspective|Other|Data.
2. In the Data Project Explorer view, right-click anywhere in the view and select New|Data Development Project.
3. In the Data Development Project panel, enter a Project Name (e.g., dataDevel) and select the Specify current schema in SQL format check box. Enter a library name for the Current schema input, then click Next.
4. In the Select Connection panel, click Next.
5. In the Connection Parameters panel (Figure 2), within the Select a database manager list on the left, expand DB2 UDB iSeries and choose your i5/OS version. Enter your IP address in the Host field. In the Class location field, click Browse and find a local copy of jt400.jar. (You can download it from your System i IFS from the /qibm/proddata/http/public/jt400/lib directory.) In the User information section, enter your user ID and password. Next, click Test Connection, and you should see a pop-up message that states, "Connection to DB2 UDB iSeries is successful." Click OK. Now, back in the Connection Parameters panel, click Next.
6. In the Specify Schema Filter panel, choose the Selection radio button, then select the check boxes of the libraries you want to work with. For the purposes of this example, choose the QIWS library, as well as one or two of your more interesting application libraries. Note that you can change this filter at any time. Click Next. In the next panel, the Set JDK Home option should default to the correct JDK; if it doesn't, browse to C:\Program Files\IBM\SDP70\jdk. Finally, click Finish.

Now, your WDSc environment is ready for you to develop ad hoc SQL queries to your System i.

Writing Dynamic SQL Queries

For this tutorial, I use the QCUSTCDT file (usually resides in the QIWS library). In the Data Project Explorer view, right-click the SQL Scripts node (as in Figure 1) and select New|SQL Statement. In the resulting Specify a Project panel, select your Data Development Project from the drop-down list and click Next. In the Statement type panel, enter listCustomers for the statement name, accept the default statement template SELECT, and click Finish. A blank SQL statement will appear in an SQL Editor window with this syntax:

SELECT * FROM

Follow the onscreen instructions, To add a table, right-click in this pane and use the pop-up menu, and in the pop-up menu, select Add Table. Expand the QIWS node, select the QCUSTCDT file, and click OK. The SQL Editor window will show

SELECT *
    FROM QIWS.QCUSTCDT

To specify fields, select your choices from the list in Figure 3. (Using an asterisk will show all fields.) The code in the SQL Editor window will change to

SELECT CUSNUM, LSTNAM
    FROM QIWS.QCUSTCDT 

To specify selection logic, open the Conditions tab (Figure 4), double-click the first entry on the Columns tab, select the state field in the drop-down list, and specify your criterion (e.g., 'TX') in the first Value entry. The SQL Editor window will show

SELECT CUSNUM, LSTNAM

   FROM QIWS.QCUSTCDT

   WHERE QCUSTCDT.STATE = 'TX'

To run your SQL code, you right-click in the SQL Editor window and select Run SQL. The output appears in the Data Output tab that you see in Figure 1.

Linking Files

In most application databases, you need to build queries to show data from two or more tables. Figure 5 shows the join operation between a job master (AJMJOBM) and detailed job item (AJMJITM). The SQL code necessary to join tables can be a bit daunting, but the SQL Builder makes it a snap.

First, add the two tables. Second, right-click in the view that lists the two files and their fields, then right-click again and select Create Join. In the Create Join dialog box (Figure 6), select the join fields and click OK. Notice in Figure 5 that a visual link provides an indication of the join criteria.

Calling Stored Procedures

The best way to integrate web front-end applications with RPG is through stored procedures. (For more information, see "Developing Reusable RPG APIs," February 2006, article ID 20395 at SystemiNetwork.com.) However, testing stored procedures can be a problem unless you have the Data Perspective available. As an example, I'll use one of the RPG-based stored procedures from Scott Klement's Order Entry application covered in "RPG and the Web: Technologies to Get There" (April 2008, article ID 21218).

The ACME Orders application's ORDER_CHKITEM stored procedure — as with all the procedures in the application — returns a result set. When you test the Check Item stored procedure in the green-screen STRSQL interface, you get the following useless response:

CALL RPGWEB/ORDER_CHKITEM ('WD671YO')
CALL statement complete.

To see the result set, test the procedure from the Data Perspective: In the Database Explorer view, expand the RPGWEB schema (aka library), then expand the Stored Procedures node. Next, right-click ORDER_CHKITEM and select Run. In the Specify Parameter Values panel (Figure 7), enter a value of WD671YO and click OK. The resulting tab will then show Yellow-Orange-Widget.

Note that the Data Perspective's Connection Properties GUI doesn't have an interface where you can specify library lists. Here's a way to work around that limitation: Right-click your System i connection in the Database Explorer view. In the Host field, append your System i with a semicolon, the word libraries, an equals sign (=), and a comma-separated list of libraries. For example, to enable the Check Item stored procedure to file the order application tables, I added RPGWEB to the library list with

172.16.1.4;libraries=QIWS,DGD,RPGWEB

More to Discover

Creating ad hoc queries will get you into the Data Perspective; once you're there, you'll find a number of other useful features. One interesting item is the tree view that lists details of your library (Figure 8). For example, to see the attributes of the fields of a particular table, I regularly traverse the nodes of the tree view and expand the Columns node of the table I'm working with. It's also handy to view the node that lists the indexes of a table. However, an SQL index isn't quite the same thing as a logical view. The Index list shows indexes created with the SQL CREATE INDEX command. The View list displays all logical views but not the keys of a logical view.

Another handy Data Perspective feature is the ability to generate database-creation and database-configuration commands (called Data Description Language — DDL) from System i files created with DDS. Some industry experts believe you should convert all your files from DDS to DDL. I won't belabor that point, but I suggest you consider using the generated DDL to create a local PC-based development environment with the tables in MySQL or DB2 for Windows that match your production System i database.

Another useful Data Perspective feature is the Table Builder, which walks you through the process of writing SQL CREATE TABLE commands. And just last week, I used yet another Data Perspective feature that assisted me with the creation of a moderately complex stored procedure — not a stored procedure wrapper for RPG but a true stored procedure with file manipulation, variables, and iteration.

As you can see, the Data Perspective is useful for more than just ad hoc queries. However, for that capability alone, it's worth the few minutes necessary to fire it up and take a spin around your database.

Don Denoncourt is a System iNEWS technical editor and a trainer, mentor, and consultant for Java, Ruby, and Grail.