It would be nice to give your users picklists for all their displays, but who has time? Well, the picklist builder can create a generic picklist over any file. All you need to do is pass it a file name, a list of fields you would like returned, and a list of key fields. The picklist builder displays a formatted list of records from which your users can choose file values. When a record is selected, the picklist returns the selected record’s field values to your program. Figure 1 shows a formatted list selection screen built using the PDM option file as an example.

The picklist uses SQL to retrieve data. SQL statements are embedded in a separate module that opens dynamically prepared statements and fetches records. This module also uses SQL to provide information about fields used on the picklist display. Field information is used to locate individual field values and format records shown in the picklist subfile.

Figure 2 contains a list of source members that make up this utility. There is far too much source code to print here, but you can download all the source code for the utility from the Midrange Computing Web site at www.midrangecomputing.com/mc/. If you want to compile the code for a release prior to V4R2, you will need to make a few changes to the source code. If you do not have the SQL RPG compiler, you can still use the picklist utility by downloading the save file containing the code compiled for V4R2.

There is a performance trade-off when you use SQL to dynamically retrieve records. The initial file open via SQL takes longer than a native file open in RPG. The difference in performance between the two types of reads is negligible; but before you use SQL in your programs, you need to weigh the trade-off between performance and flexibility. In the case of the picklist program, the flexibility of SQL allows the program to work with any file, which is definitely worth the wait.

Embedded SQL

Embedded SQL makes it easier to create dynamic applications. Using embedded SQL in a separate shared module has several advantages. The main advantage is that you can share SQL procedures and avoid some of the complexity associated with using embedded SQL. Putting SQL statements in their own module also gets around another limitation. New RPG IV features are often not supported by the SQL version of the RPG compiler when they are

first announced. If you confine SQL statements to one module, that module can’t use new language features. However, the rest of the modules in the program can.

When using embedded SQL to select records, you have several alternatives. The first alternative is to select records from specific files into specific fields or a predefined data structure. This method allows SQL to store an access plan that speeds up processing, but the method is not very flexible because the Select statement and input area must be defined when the SQL module or program is compiled. The second alternative is to use a dynamically prepared Select statement to select records into specific fields or a predefined data structure. This alternative is more flexible, but the format of the data selected must be defined at compile time. The last alternative, and the one that the picklist SQL module uses, is to use a dynamically prepared statement combined with an SQL description area (SQLDA). Using an SQLDA, you can wait until runtime to determine the format of a Select statement’s input area.

The picklist service program’s SQL statements are embedded in DYNSQL. This module has several procedures to open an SQL cursor and read (fetch in SQL parlance) records. To open a file and retrieve records, a program calls the Create SQL Handle (CRTSQLHND) procedure, which is located in DYNSQL. You pass an SQL Select statement and a pointer to the input buffer that receives fetched records.

When the CRTSQLHND procedure receives a request to open a file, it creates an executable form of the passed Select statement string by using Prepare. The result is a prepared statement. Information about the statement is then retrieved by using Describe. This loads information about the prepared statement into an SQLDA. The SQLDA has a header area followed by a field array containing information about each field in the Select statement. The next step is to allocate storage for input records if the calling program has not allocated storage for them. Finally, pointers to the input area for each field are set in the SQLDA. These pointers determine where the field’s data is loaded as records are fetched.

After an SQL handle is created, call DYNSQL’s Execute SQL Select (EXCSQLSEL) procedure to open a cursor for the handle. The Open statement contains a cursor name. When the open is complete, the cursor is ready to fetch records.

DYNSQL supports use of scrollable and nonscrollable cursors to fetch records. A scrollable cursor allows records to be read more than once. Records can also be retrieved by relative position, making it simpler for the picklist program to load its page-at-a-time subfile. The cursor in DYNSQL is not declared dynamic; DYNSQL may not see changes made to the file by programs in other activation groups or jobs. If this is a problem in your environment, add the Dynamic keyword to the Cursor Declaration statement.

Getting Field Information

When you use DYNSQL to retrieve records, you probably need information about selected fields. Information about fields processed can be retrieved from the SQLDA by using the SQLDA Field Information (SQLDAFLDINF) procedure, which allows your program to retrieve the type, length, and name of selected fields.

Another procedure, Get Column Headings (GETCOLHDG), allows you to retrieve the column headings for fields. This information is retrieved from the AS/400’s field information file, Syscolumns. Syscolumns is one of several SQL catalog files on the AS/400 that allow you to retrieve information about files and fields. To retrieve a field’s column headings, pass the field name, the file, and a library (which is optional).

If you pass either of the supported values *CURLIB or *LIBL or do not pass a library, the Retrieve Object Description (RTVOBJD) procedure is called to set the library name. This procedure uses the Retrieve Object Description API (QUSROBJD) to retrieve object information. You can use the RTVOBJD procedure to return other information for an object, such as its text, size, creation date, and owner. You may want to look at this procedure to see whether it would be useful in your applications.

Requesting a List

To add a picklist to your programs, call the PCKLST subprocedure defined in the PCKLST module. In your program, you need to bring in the prototype definition of the picklist by using the statement D/COPY PROTOSRC,PCKLST. Next, you need to code a call to the picklist procedure by using the EVAL op code. One of the parameters you pass to the picklist is a pointer containing the field address to which you would like the selected value returned when a record is selected. The picklist procedure returns a data structure containing two flags. The first flag is an error flag, and the second is turned on if a selection is made.

The other parameters passed to the picklist tell the picklist what file and fields to display, what key to use when the list is built, the selection criteria, the screen title, the optional column headings, and whether information is joined from multiple files. You can pass the special value “*” in the first element of the field list if you would like to display all the fields. Field column headings are optional. If they are not passed, the picklist retrieves the default column headings from the system catalog.

To display a subset of information from a file, pass your selection criteria in the Where parameter. This parameter, if passed, is used by the picklist to add a Where clause to the SQL statement. Any valid SQL selection expression can be passed. With this parameter, you can pass a simple selection, you can pass a wild-card selection by using Like, or you can test for the existence of records in another file by using In.

Information can be joined from more than one file. To join information from multiple files, pass the “from” and “to” files being joined. You also need to specify the type of join; valid types are I, L, E, and C for inner, left, exception, and cross, respectively. The “join,” “from,” and “to” fields and join field count are used to build the On part of the Join clause. You can find a complete description of the Join parameters and their use in the header of the /COPY prototype definition member PCKLST.

Building a List

When the picklist procedure is called, it validates the parameters that have been passed. The picklist then uses the passed parameters to build an SQL Select statement. After the SQL Select statement is built, it is passed to the DYNSQL procedures that prepare the statement and open the file.

Once the SQL statement has been prepared and opened, the picklist builds the subfile column headings. If the calling program does not specify headings, it calls a procedure in DYNSQL to retrieve a default heading. As the calling program builds the headings, it adjusts the column widths to accommodate the larger of the column headings or fields.

The next step is to set editing information used to apply an edit for the numeric fields passed. The picklist does not use the built-in numeric editing functions %EDITC and %EDITW, because these functions allow only edit codes and edit words that have been either defined as constants or passed as literal values to be passed. To get around this limitation, the picklist calls the Convert Edit Code (QECCVTEC) or Convert Edit Word (QECCVTEW) API to retrieve an edit mask, which is then passed to the Edit (QECEDT) API to edit numeric values. You may want to modify the picklist to pass edit codes as a parameter for numeric fields, especially if your database files contain numeric date fields.

The picklist uses a page-at-a-time subfile. This type of subfile is created when the SFLPAG value is equal to the SFLSIZ value. Because of this, the picklist procedure must handle rolling in both directions. Using a scrollable cursor makes this type of processing much easier. The picklist cursor is always positioned at the last record in the subfile. With a scrollable cursor, you can request records relative to the current subfile position.

The picklist also allows users to scroll to the left or right if the information on the list does not fit in the display. Function keys F19 and F20 allow the user to view information to the left and right of the information currently displayed. When the user

presses one of these keys, the records on the display shift to the left or right. This feature allows the picklist to show more information without use of a fold line.

Positioning the List

If key fields are passed to the picklist, a position to field appears. When a user enters a value in the position to field, the list is repositioned to the nearest key value. Rather than close and reopen the SQL cursor, the picklist scans the records displayed. In testing, I found this method faster than closing and reopening the file if the file did not contain many values. To begin scanning, the first record’s key value is compared with the position to value. If the position to value is greater, a scan begins that locates the first record with a key greater than the position to value. Once this record is located, the subfile is reloaded from this position. If Page Up or Page Down is pressed when a position to value is entered, the page before or after the page containing the key value appears. If you use the picklist on large files, you may want to close and reopen the file specifying the position to value in the Where clause of the Select statement.

Hastening the List

In some cases, the picklist may not display a list as quickly as you would like. The first thing to do in these cases is to start a debug and call the picklist program. Next, enter the Display Job Log (DSPJOBLOG) command and press F10 to see the low-level messages generated. These messages contain information about access paths considered when the dynamic SQL procedures open the file. There may also be messages that describe how an access path can be built to improve performance.

Having proper access paths in place is the best way to ensure good performance. If you are still not satisfied with the performance, look in the Data Management and Query Optimizer Tips section of the DB2 for AS/400 SQL Programming manual for more information on optimizing SQL performance. There, you can find information that describes how to optimize your Select statement further and take better advantage of features such as encoded vector indices and symmetric multiprocessing (SMP).

Taking a Test Run

If you would like to try out the picklist, download the modules and display files at www.midrangecomputing.com/mc/ and compile them. Next, create service programs and a Test Picklist program. The heading comment of each source member contains compilation instructions. Once you have created the programs, call the Test Picklist program (TSTPCKLST) from a command line. To display a list by using the file and display criteria at the bottom of the prompt screen, press F4 with the cursor on the return value.

The Test Picklist program passes the values shown at the bottom of the screen to the picklist procedure. You can change these values to pass any file and selection criteria on your system. Because the test program requests all fields, you may run into problems if you pass a file with more fields than the picklist can handle. This should not be a problem when you call the picklist from your applications.

Embedded SQL, the Sequel

The picklist is just one example that shows how dynamic SQL can be used in an interactive application. This application demonstrates that the performance of SQL can be quite good, even when it is used to retrieve records interactively.

Embedded SQL is another tool you can use to make your programming easier. SQLDAs allow SQL to be used more efficiently and in more types of applications. The picklist demonstrates that embedded SQL can be used to create very flexible applications. You can also use DYNSQL in your interactive applications to provide support in your own inquiry programs to allow your users both to specify more sophisticated record selections

and to specify their own sorting. The techniques used in DYNSQL can also be used for dynamic reporting.

REFERENCES AND RELATED MATERIALS

• DB2 for AS/400 SQL Programming (SC41-5611-03, CD-ROM QB3AQ803)
• DB2 for AS/400 SQL Reference (SC41-5612-03, CD-ROM QB3AQ903)

Figure 1: This picklist was built using PDM’s option file.

Source File Member Type Description QCLSRC CRTPCKLST CLP Create picklist utility QDDSSRC PCKLSTZ DSPF Dynamic file picklist QDDSSRC TSTPCKLSTZ DSPF Demonstrate dynamic file picklist QPROTOSRC CEEDOD RPGLE Display operational descriptors CEE API QPROTOSRC CEETSTA RPGLE Test for omitted argument CEE API QPROTOSRC DYNSQL RPGLE Dynamic SQL procedures
QPROTOSRC EDTVAR RPGLE Edit numeric variable
QPROTOSRC MSGTKT RPGLE Message toolkit
QPROTOSRC PCKLST RPGLE Dynamic file picklist
QPROTOSRC RTVOJBD RPGLE Retrieve object description QPROTOSRC STGTKT RPGLE Dynamic storage prototypes QRPGLESRC APIERRDEF RPGLE API error-handling data structure QRPGLESRC DYNSQL SQLRPGLE Dynamic SQL selection procedures QRPGLESRC EDTVAR RPGLE Edit numeric variable
QRPGLESRC MSGTKT RPGLE Message toolkit
QRPGLESRC PCKLST RPGLE Dynamic file picklist
QRPGLESRC RTVOBJD RPGLE Retrieve object description QRPGLESRC TSTPCKLST RPGLE Demonstrate dynamic file picklist

Figure 2: The picklist utility is created from these source members.