ISPF/PDF SQL source development and analysis

You can access SQL Source Development and Analysis function through File-AID for Db2, as well as an edit session in ISPF/PDF, Librarian, or Panvalet. This section describes accessing the function through option 4.3 from the File-AID for Db2 Primary Option menu. However, once you begin an edit session from the other sources, all the procedures described in this section are available, with the exception of the EX (Execute) line command. It can only be used when accessed directly through File-AID for Db2.

Select option 3 from the Analysis and Development screen (SQL Analysis and Development Menu). The SQL Analysis Edit Entry Screen displays. Enter the dataset name that contains the SQL you want to analyze.

SQL Analysis uses a 2 phase processing approach for SQL Analysis:

1. Summary data from the Explain Tables is obtained from Db2 and is used to build the SQL Analysis Summary panel (SQL Analysis Summary).
2. Detail data from the Explain Tables is obtained from Db2 when you select an SQL statement for further detail.

The current SQLID value is obtained prior to the summary data query being issued (phase 1). This SQLID is used for both the summary data and detail data queries of the Explain tables until one or both of the following occurs:

• You change the current SQLID to a new value and issue an SQL command in Access Path Analysis or Predicate Analysis and change the SQL statement text. This results in a new Db2 EXPLAIN being performed. In this situation, the new SQLID value is used for the newly explained SQL statement.
• You change the current SQLID to a new value before issuing a new explain of an SQL statement. The new SQLID value is used from this point forward.

The dataset name can be a sequential dataset or a partitioned dataset (PDS). The source code can be COBOL, PL/I, or C. Data sets entered on this screen cannot be CA Panvalet or CA Librarian. For information on accessing the interface to these packages, see PANVALET-source-development-and-analysis and LIBRARIAN-source-development-and-analysis.

SQL Analysis Edit Entry Screen

ISPF LIBRARY Area

If the data set to be analyzed is an ISPF library, enter the required dataset name in the PROJECT, GROUP, TYPE, and MEMBER fields, and press Enter. If you omit the member name, a member list displays from which you can select a member.

OTHER PARTITIONED OR SEQUENTIAL DATASET Area

A dataset name specified in the OTHER PARTITIONED OR SEQUENTIAL DATASET fields takes precedence over a data set specified in the ISPF LIBRARY fields.

DATASET NAME

If the data set to be analyzed is not an ISPF library, enter the dataset name, enclosed in single quotes; otherwise, your user ID is prefixed to the name.

VOLUME SERIAL

Optional. If the volume serial is specified, the system catalog is not used.

DATASET PASSWORD

Optional. Used only with password protected data sets. RACF or ACF uses your TSO logon password for user identification. If entered, the password is not displayed on the screen.

INITIAL MACRO

Optional. Used only when you want to use your own macro, instead of the one supplied by File-AID for Db2, F2EDNOTE. An initial macro can contain any ISPF editing commands, and is executed after the data is read but before the data is displayed. Using an initial macro allows several commands to be executed together. The initial macro allows you to group commands used to set ISPF variables so that the same options are always used.

After you enter the dataset name on the SQL Analysis Edit Entry screen, the Edit screen (see the following figure) displays.

Edit Screen

From the Edit screen, you can use the full power of the ISPF editor. For example, use the FIND command to move to the SQL statement you want to analyze, or use the I (Insert) line command to insert a new SQL statement to be analyzed.

With the introduction of long names, the bounds setting of the data set type you are editing are very important in determining when names span multiple lines. File-AID for Db2 uses the default bounds as defined in the IBM publication, ISPF Edit and Edit Macros. To check your bounds setting, enter the FADB2 BOUNDS command. To change the current bounds setting, enter FADB2 BOUNDS left_col,right_col. (e.g., FADB2 BOUNDS 1,72).

Using the SQL Analysis Line Commands

In addition to the ISPF/PDF commands, File-AID for Db2 provides four line commands that enable you to analyze SQL. When the SQL statement to be analyzed displays, select the action for the statement by entering the desired line command in the line number area. The valid commands are:

The EX, XP, BR, and ED line commands must be placed on an SQL statement that begins with either an EXEC SQL statement or an SQL verb and ends with either a semicolon (;) or END-EXEC statement. Any SQL call can be selected for processing except the following:

• BEGIN DECLARE
• END DECLARE
• INCLUDE
• WHENEVER
• EXECUTE
• EXECUTE IMMEDIATE
• DESCRIBE
• PREPARE

To initiate the action after entering a line command on an SQL statement, type FADB2 on the command line and press Enter, or use the associated PF key. See Primary Commands for more information on the FADB2 command.

The following figure shows an example of executing a DECLARE CURSOR SQL statement where the EX line command is entered for statement number 351, the FADB2 command is entered at the command line, and the Enter key is pressed.

Executing a DECLARE CURSOR SQL Statement Dynamically

Entering Host Variable Values

If the SQL statement contains host variables, and you enter an EX, BR, or ED line command, you must enter values for the host variables before you can complete the action.

You can provide values for the host variables in one of two ways:

• Enter the actual values on the Edit screen as shown in the following figure. When you use this method, you are making an actual change to the SQL. If you do not want to save the change, you must enter the CANCEL primary command to exit the Edit session.

Entering Host Variable Values on the Edit Screen

or

• Use the Host Variable Resolution screen (see the following figure). This is a temporary method of providing values. The SQL is not modified.

  The Host Variable Resolution screen displays if you enter an EX, BR, or ED line command on the Edit screen without entering values for the host variables in the SQL statement. This screen enables you to see and enter values for the host variables in the SQL statement.

Host Variable Resolution Screen Prompting for Values

OPTION

Enter the desired option.

Selected SQL Statement

The selected SQL statement displays on the screen, along with its associated host variables. Each host variable is presented on a separate line.

Host Variable Values

The question marks (?) indicate that a value for the host variable must be specified. You can scroll to the right and left to enter host variable values for character strings with more than 30 bytes; maximum length is 256 bytes. Enter NULL to indicate NULL values.

Enter values for the host variables (see the following figure), then enter the option associated with the action you want to take. For example, enter 4 to execute the SQL DECLARE statement shown in the figure. You must enter values for all options except option 3, Explain.

When entering values, single quotation marks should be used to delimit character data. No quotation marks should be used for numeric values.

Entering Values on the Host Variable Resolution Screen

Prototyping New SQL Statements

You can use ISPF/PDF Edit functions from the SQL Analysis Edit screen to insert and analyze new SQL statements. An SQL statement can be added in any of the following formats:

• Precede the SQL statement with EXEC SQL and follow it with END-EXEC., as if it was embedded in a COBOL program. For example,

  EXEC SQL
       SELECT * FROM VSQLSAMP
  END-EXEC.

• Precede the SQL statement with EXEC SQL and follow it with a semicolon, as if it was imbedded in a PL/1 program. For example:

     EXEC SQL SELECT * FROM VSQLSAMP;

• Follow the SQL statement with a semicolon. For example:

     SELECT * FROM VSQLSAMP;

You may use a host variable within your inserted SQL, but it must be preceded by a colon.

Once the new statement has been inserted, you can perform any of the valid analysis line commands on the statement: EX (Execute), XP (Explain), BR (Browse), or ED (Edit).

Prototyping Cursor Processing

In order to prototype cursor processing, each of the following SQL statements must be executed in order:

Executing a DECLARE CURSOR SQL Statement

To execute the DECLARE CURSOR SQL statement shown at line 351 in the following figure, you must enter values for the host variables. Enter the required host variable values on the Edit screen or the Host Variable Resolution screen as described in Entering Host Variable Values.

Entering Host Variable Values on the Edit Screen

SQL Analysis dynamically executes the SQL statement and displays the proper SQL return code at the message line (see the following figure).

Message Indicating Successful Execution of the SQL Statement

Executing an OPEN CURSOR SQL Statement

You can select other statements to be executed by returning to the Edit screen and selecting them.

For example, you can select the OPEN SQL statement for execution by entering the EX line command at line 357, (see the following figure), typing FADB2 in the command field, and pressing Enter.

Executing an OPEN CURSOR SQL

If a DECLARE CURSOR was previously executed, SQL Analysis dynamically executes the OPEN CURSOR and displays the proper SQL return code. If a DECLARE CURSOR was not previously executed, the CURSOR cannot be opened, and an error message results.

In this case, we have already executed the DECLARE CURSOR, indicated by the .EX in the statement number area for statement 351.

Executing a FETCH SQL Statement

You can also execute a FETCH SQL statement. For example, you can select a FETCH for execution (see the following figure) for line 366.

Executing an SQL FETCH

SQL Analysis dynamically executes the FETCH statement and automatically displays the row retrieved (fetched) from the Db2 results table. The following figure shows both the FETCH statement and the row that is fetched into storage.

Display Showing SQL FETCH Statement and Table Data

You can continue executing the FETCH statement using the EXECUTE command until you receive a message indicating there are no more rows to fetch. The following figure shows the second row of data fetched into storage.

Press END to return to the Edit screen.

Second Row Fetched Into Storage

Browsing the Results Table

To browse the Db2 results table generated by a SELECT or DECLARE CURSOR statement, do one of the following:

• From the Edit screen, replace the host variables with actual values, enter the BR line command on the SQL statement referencing the table to be browsed, then type FADB2 on the command line and press Enter.

  Important

  If you use this method, you are actually editing the SQL source. If you don’t want to permanently replace the host variables, enter the CANCEL command before ending from the SQL Analysis Edit screen, or use the Host Variable Resolution screen as outlined below.

• From the Host Variable Resolution screen, enter option 1 for Browse after you enter the required host variable values.

You are taken directly to File-AID for Db2 Browse for the results table (see the following figure). See Browse-and-Edit-function for information on using the Browse function.

Press END to return to the ISPF/PDF SQL Source Development and Analysis screen from which you requested the Browse function. The SQL statement is marked with .BR in the line number area.

Browsing the Results Table

Editing the Results Table

To edit the Db2 results table generated by a SELECT or DECLARE CURSOR statement, do one of the following:

• From the Edit screen, replace the host variables with actual values, enter the ED line command on the SQL statement referencing the table to be edited, then type FADB2 on the command line and press Enter.

  Important

  If you use this method, you are actually editing the SQL source. If you do not wish to permanently replace the host variables, enter the CANCEL command before ending from the SQL Analysis Edit screen, or use the Host Variable Resolution screen as outlined below.

• From the Host Variable Resolution screen, enter option 2 for Edit after you enter the required host variable values.

You are taken directly to File-AID for Db2 Edit for the results table. See Browse-and-Edit-function for information on using the Edit function. See Browse-Edit-SQL-Analysis-Display-Options for more information on Edit display options.

Press END to return to the ISPF/PDF SQL Source Development and Analysis screen from which you requested the Edit function. The SQL statement is marked with .ED in the line number area.

Dynamically Changing and Testing SQL Statements

From the SQL Analysis Edit screen, you can dynamically change the SQL statement and execute it to see the results of the change.

For example, you can change the SQL DECLARE CURSOR statement to add the “ZIPCODE” variable (see the following figure).

Dynamically Changing an SQL Statement

You can also enter values for the host variables on the Edit screen (see the following figure). There you can dynamically execute, explain, browse, or edit the SQL statement, without being prompted to enter values on the Host Variable Resolution screen. If you use this method, you are actually editing the SQL source. If you do not want to permanently replace the host variables, enter the CANCEL command before ending from the Edit screen. In this example, the EX line command was entered to execute the DECLARE CURSOR statement.

Entering Host Variable Values on the Edit Screen

When the execution label (the line command preceded by a period) displays on the selected line, you can enter another line command to perform a different action or dynamically change and test other SQL statements.

Explaining SQL Statements

From the SQL Analysis Edit screen, you can dynamically explain SQL statements with the XP line command. Enter XP on the SQL statement you wish to explain, then type FADB2 on the command line as shown in Explaining SQL Statements with the XP Line Command and press Enter to process the command.

Explaining SQL Statements with the XP Line Command

Press Enter as shown above to display the Plan Table Analysis screen (see the following figure). For information on using this screen, see Plan-Table-Analysis.

Plan Table Analysis Screen Resulting from an XP Line Command.

Host Variable Substitution

File-AID for Db2 substitutes host variables with literals, which are generated by using data type information obtained from the Db2 catalog. Substitution may involve use of default lengths established by File-AID for Db2. LOB data types result in generation of the Db2 CAST function to cast a default value to the appropriate LOB data type.

Parameter markers remain in the SQL statement if they are explicitly specified by the user.

Resolving SQL Coding Errors

If an SQL error is encountered, an error message displays in the message area and the action is aborted. For example, suppose you entered the ED (Edit Table command) on an SQL statement, but Db2 returned an SQL return code of -206 (see the following figure).

Error Was Encountered During Execution of the SQL Statement

The HELP/TUTORIAL screen gives you information when you encounter an error. Type HELP and press Enter, or press HELP to access this screen. Entering HELP for the -206 return code gives you the Help/Tutorial (Help Screen for an SQL Return Code of -206). This Help screen provides the Db2 SQL error text returned from Db2.

Help Screen for an SQL Return Code of -206

Unqualified Objects Window

If you issued one of the FADB2 line commands, EX, XP, ED, or BR, and your SQL contains an unqualified object, this window displays pre-filled with the last saved profile variable value. If you leave the field blank, the SQLID will be used and stored in the profile.

Unqualified Objects Window

Qualified Columns

File-AID for Db2 considers any column that consists of two or more parts as a qualified column. The following is a list of rules File-AID for Db2 uses to process qualified columns:

1. If the column is qualified further than its associated table, then the column qualification remains as specified.
2. If the associated table has a correlation name and the qualified column qualification is other than the correlationname.columnname, then the column qualification is modified to qualify the column with the exposed correlation name (in other words, correlationname.columnname).

Modifying Column Boundaries

You can modify the column boundaries from which the FADB2 command obtains an SQL statement to be processed so the line numbers, comments, etc. are eliminated. Enter FADB2 col1 col2 on the command line of the SQL Analysis Edit screen, where col1 is the beginning boundary and col2 is the ending boundary. You will receive the following message:

Any data outside those boundaries is ignored by the FADB2 line commands (BR, ED, EX, and XP).

COMMIT/ROLLBACK Prompt

If you executed any SQL while in SQL Analysis and Development, you receive the COMMIT/ROLLBACK prompt (see the following figure).

COMMIT/ROLLBACK Prompt

You may have database changes pending when you exit the Edit screen. You must either COMMIT or ROLLBACK those changes. Type SAVE to commit your database changes, or CANCEL to roll back your database changes. CANCEL is the default.

Your database changes are committed or rolled back to the last commit or rollback point.