(Stored Procedure Test Environment)

Index

Index............................................................................................................................................... 2

Installation of STE..................................................................................................................... 3

Using STE........................................................................................................................................ 4

Processing the Stored Procedure......................................................................................... 7

Displaying result sets.............................................................................................................. 8

Viewing the prepared statement........................................................................................... 10

Displaying the Output parameters....................................................................................... 11

Installation of STE

Install STE on iSeries. This will install the STE licensed product on your iSeries. STE will be installed into a library called #STELIB. After clicking on start, you will be prompted for a system name, user id, and password. (i.e. 10.1.1.7 QSECOFR PASSWORD).

If problems occur contact our support team.

Using STE

To use STE, perform the following command:

#STELIB/STE <F4>

Your screen will look like this:

Stored Procedure Name – This is a required parameter. Enter the procedure name of the stored procedure and the library in which it resides. This should be the same used in the Create Procedure SQL command. The stored procedure must be defined in SQL on the iSeries.

Display Size – The size of the screen to be used for displaying parameters and result set(s).

1 – 24X80

2 – 27X132

Output Type – The output method for the result set(s).

*DSP – The result set(s) will be displayed to the current display device.

*PRT – The result set(s) will be printed to a spool file.

If *PRT is chosen, the following extended command display will be shown:

Extended parameters:

Page Size - Specifies the length and width of the printer forms used to display the result set(s). The length is specified in lines per page. The width is specified in print positions (characters) per line.

Lines per inch - Specifies the line spacing setting on the printer, in lines per inch, used to display the result set(s).

Characters per inch - Specifies the printer character density (in characters per inch).

Overflow line number - Specifies the line number on the page at which overflow to a new page occurs. Generally, after the specified line is printed, the printer overflows to the next page before printing continues.

Output queue name - Specifies the qualified name of the output queue used.

Degree of page rotation - Specifies the degree of rotation of the text on the page with respect to the way the form is loaded into the printer.

*AUTO - Indicates that automatic rotation of output is done to fit the printed data on the form.

*DEVD - The operating system sends a device default rotation value to the printer.

*COR - Computer output reduction is done.

0 - No rotation is done.

90 - Rotation of the text is done 90 degrees clockwise from the 0 degree writing position.

180 - Rotation of the text is done 180 degrees clockwise from the 0 degree writing position.

270 - Rotation of the text is done 270 degrees clockwise from the 0 degree writing position.

Hold spooled file – Specifies whether the spooled file is to be held.

*NO - The spooled output file is not held on the output queue.

*YES - The spooled output file is held until it is released by the Release Spooled File (RLSSPLF) command.

Save spooled file – Specifies whether the spooled file is to be saved.

*NO - The spooled file data is not kept (saved) on the output queue after it is produced.

*YES - The spooled file data is kept on the output queue until the file is deleted.

Form Type - Specifies the type of forms used in the printer for printed output.

*STD - The standard printer form for your computer system is used.

Form-type - Specify a form type identifier, having 10 characters or less, for the printer forms used.

Processing the Stored Procedure

After entering the required parameters in the STE command, an initial screen will be displayed to allow input of parameters. STE knows the parameters for your stored procedure and will display them to you.

At the top of the screen is the name of the stored procedure to be called. The next line displays the number of input parameters, output parameters, in/out parameters, and result sets. Stored procedures, written in high level languages, are only allowed to have one result set. To have multiple result, the stored procedure would need to be written in the SQL language.

The rest of the screen displays the parameters for this stored procedure. Output parameters will be blocked from entry. The parameter type and length are displayed on the left side of the screen, followed by the parameter name as defined in the CREATE PROCEDURE SQL command. If more parameters are required, than can fit on one screen, more… will be displayed in the bottom right corner. You may use the page up and page down keys to scroll through the parameters.

To process the stored procedure, press the <F6> key.

Displaying result sets

Data Width – The width of a single row from the stored procedure call.

Shift to column – Type a number from 1 through 99999 that identifies the number of the column position you want to appear as the first column on the left side of the display. Type a number preceded by a plus or minus sign (+n or -n) for relative positioning to the right or left on the display.

For example, if you:

· Type 50 and press Enter, column 50 will become the first column on the left side of the display.

· Type +50 and press Enter, the report is moved 50 columns toward the right.

· Type -50 and press Enter, the report is moved 50 columns toward the left.

The column indicators in the scale line at the top of the display provide column position information. Pluses (+) indicate the fives position, and the number (for example, 1, 2, 3, and so on) identifies the tens position.

The <F19> and <F20> keys may also be used to shift left and right, respectively.

You can display a maximum of 32,767 column positions of information.

The field names, at the top of each column, are the record set field names returned from the stored procedure. If calling this stored procedure from Java via JDBC, these would be the names of the columns to be used when accessing the record set.

The records displayed are in the order returned from the stored procedure. If more records exist, than can be displayed on one screen, more… will be displayed in the lower right corner of the display. You may see the additional records by pressing the <Page Down> key. Once you have paged down, you will not be able to page back up. This is because the record set cursor on an iSeries is single directional only. The record set can only be read forward not in reverse.

If multiple result sets exist for this procedure, you may press <F7> to view the next result set. Once you have closed a result set and viewed another, you can not return to the previous result set.

Viewing the prepared statement

The prepared statement can be viewed by pressing the <F9> key from the result set screen. A window will be displayed showing the prepared statement.

Displaying the Output parameters

The output and in/out parameters can be display by pressing the <F10> key from the display result set screen.

The returned parameters will be displayed in the order they were defined in the CREATE PROCEDURE SQL command. If more parameters are available, than can fit on one screen, more… will be displayed in the bottom right corner. You may use the page up and page down keys to scroll through the parameters.