Space announcement This documentation space provides the same content as before, but the organization of the content has changed. The content is now organized based on logical branches instead of legacy book titles. We hope that the new structure will help you quickly find the content that you need.

Using Code Debug Db2 Extension

This section describes how to:

  • Browse and edit Db2 table data while testing your program
  • Analyze SQL statement execution with the FADB2 EXPLAIN command
  • Prototype SQL logic by inserting SQL statements.

See the File-AID for DB2 Reference Manual for information about using File-AID for Db2.

Important

Code Debug TSO supports debugging of any Db2 program within all execution environments selectable under the Environments Menu, with the following exceptions:

  • The first exception to this Code Debug TSO support rule is the Code Debug TSO dialog box environment.
  • The second exception to normal debugging is that Code Debug Db2Extension cannot be accessed when using the Batch Connect facility.

Browsing and Editing Db2 Table Data

When debugging Db2 programs, you can access File-AID for Db2 to inspect and manipulate the Db2 table data associated with SQL statements being executed.

For example, you can experiment with program SQL statements and then enter FADB2 EDIT to access File-AID for Db2 and change the test data associated with the statements.

The FADB2 EDIT command transfers control directly to the File-AID for Db2 Edit function. Once in File-AID for Db2 Edit, you can enter the information to access the table data you want to view. See the File-AID for DB2 Reference Manual for information about using the Browse and Edit functions.

Analyzing SQL Statement Execution

The FADB2 EXPLAIN command (XP line command) can be entered for any SELECT, DELETE, INSERT, or UPDATE SQL statement in your program to display information about the execution of the statement.

The Explain data is also stored in a Db2 table called user id.Plan_Table. The stored Explain data can be used to generate reports using the File-AID for Db2 Reports facility or view the Plan_Table using the File-AID for Db2 Browse facility.

Important

The Plan_Table must exist before you use the FADB2 EXPLAIN command.

For example, in the following figure, the XP line command is entered on the EXEC SQL INSERT statement number 579.

Using the FADB2 EXPLAIN Line Command

  000570           END-EXEC
 XP 579           EXEC SQL INSERT INTO VTRIDB2
 000580            (SSNR,LASTNAME,FIRSTNAME,STREETADR,CITY,STATE,ZIPCODE,
 000581             PHONENR,LICENSENO)


When the FADB2 EXPLAIN command is entered, File-AID for Db2 is accessed and the SQL Source Analysis screen shown in the following figure is displayed.

SQL Source Analysis Screen

File-AID for DB2 -------------- SQL Source Analysis ------------ ROW 1 TO 1 OF 1
COMMAND ===>                                                    SCROLL ===> PAGE
                                                                     SSID: DSNG
SQL Statement: 1 OF 1

INSERT
INTO VTRIDB2 (SSNR,LASTNAME,FIRSTNAME,STREETADR,CITY,STATE,ZIPCODE,
PHONENR,LICENSENO)
VALUES ( .SSNR,:LASTNAME,:FIRSTNAME,:STREETADR,:CITY,:STATE,:ZIPCODE,:PHON
ENR,:LICENSENO)


Line Commands:
  T - Table Information   I - Index Information   F - Formatted Display

   Qblk Plan        Access Match Index  TS  SortN SortC Table  Pre  Col  Mix
CMD  No   No  Method  Type  Cols  Only  Lock UJOG  UJOG   No   Fetch Eval Seg
-     1    0     0            0     N     IX NNNN  NNNN    1               0
   Access  Access     Join    Join       Table: FLGJXY1.VTRIDB2
   Degree  Pgroup ID  Degree  Pgroup ID  Index: <NONE>
    N/A      N/A       N/A      N/A
******************************* BOTTOM OF DATA *****************************

Inserting Program SQL Statements

You can dynamically insert and execute SQL statements from within your Code Debug TSO source. SQL statements are inserted with the INSERT command. See Modifying Program Logic for detailed information about the INSERT command.

The inserted statements are executed after the last logical statement as if they are part of the source code. The capability to insert SQL statements lets you test fixes before you update the source code and recompile the program.

Each inserted SQL statement must be prefixed by EXEC SQL and suffixed by END-EXEC. Otherwise, Code Debug TSO issues a syntax error message. Any data entered after the END-EXEC statement, on the same line, is ignored. Error messages regarding inserted SQL statements are displayed in the same manner as error messages regarding other inserted statements. Additional help is available for negative SQL return codes by entering the primary command FADB2 HELP after the error message is displayed.

SQL statements are allowed within an inserted IF construct. For example,

IF A = B
   EXEC SQL
    SELECT
   END-EXEC
   MOVE 1 TO A
   EXEC SQL
     SELECT
   END-EXEC
 END-IF

Inserted SQL statements appear in the Code Debug TSO session log and script in the same manner as other inserted statements. You can keep or display host variables in inserted SQL statements.

SQL Statements That Can Be Inserted in Your Program

The following SQL statements are supported (i.e., the statements can be inserted in your source and are valid in a debugging session):

ALTER INDEX

Changes the description of an index.

ALTER STOGROUP

Changes the description of a storage group.

ALTER TABLE

Changes the description of a table.

ALTER TABLESPACE

Changes the description of a table space.

BEGIN DECLARE

Marks the beginning of a host variable declaration section.

CLOSE

Closes the cursor and deletes the temporary application-specific result table.

COMMENT ON

Replaces or adds a comment to the description of a table, view, or column.

COMMIT

Terminates a unit of recovery and commits the Db2 table changes made by that unit of recovery.

CREATE DATABASE

Defines a database.

CREATE INDEX

Creates an index on a table.

CREATE STOGROUP

Defines a storage group or set of volumes, controlled by a VSAM catalog, on which storage can later be allocated for table spaces and indexes.

CREATE SYNONYM

Defines an alternate name for a table or view.

CREATE TABLE

Creates a table.

CREATE TABLESPACE

Allocates and formats table spaces.

CREATE VIEW

Defines a view of one or more tables.

DECLARE CURSOR

Associates a cursor name with OPEN, FETCH, and CLOSE statements, which declare and retrieve data from an application specific result table row-by-row. DECLARE CURSOR can be inserted into the executable portion of a program.

DECLARE STATEMENT

Declares a statement for dynamic SQL.

DECLARE TABLE

Declares a table. The DECLARE TABLE declarative statement can be inserted into the application program. It causes host variable locations to be defined in accordance to the columns of the declared table. You can reference these columns in subsequent SQL statements.

DELETE

Deletes one or more rows from a table.

DESCRIBE

Provides a description of the columns in a table or view.

DROP

Removes an object and its description in the Db2 catalog.

END DECLARE

Marks the ending of a host variable declaration section.

EXECUTE

Executes a prepared SQL statement.

EXECUTE IMMEDIATE

Prepares and executes an SQL statement.

EXPLAIN

Obtains information about how an SQL statement will be executed. An example showing the use of EXPLAIN is given in Analyzing SQL Statement Execution.

FETCH

Positions the cursor on the next row of the application-specific result table and assigns the values of that row to host variables in the application program.

GRANT

Grants privileges.

INSERT

Inserts rows into a table or view.

LABEL ON

Adds or replaces labels in the catalog descriptions of tables, views, columns, or sets of columns.

LOCK TABLE

Acquires a shared or exclusive lock on a table.

OPEN

Opens a cursor so that it can be used by FETCH to fetch rows from the application-specific result table.

PREPARE

Dynamically prepares an SQL statement for execution.

REVOKE

Revokes privileges.

ROLLBACK

Terminates a unit of recovery and backs out database changes made by that unit of recovery.

SELECT

Specifies a result table and selects rows to view.

SET

Changes the value of the authorization ID.

UPDATE

Updates the values of specified columns in rows of a table or view.

Important

The syntax for all SQL statements is explained in the IBM Database 2 Reference manual.

Host variables can be used within inserted SQL. Code Debug TSO requires that each host variable be preceded by a colon even though Db2 does not always require it.

Within the BTS/DLI setup, recovery of Db2 tables and IMS databases is uncoordinated. The SQL COMMIT and ROLLBACK commands commit and rollback changes made to Db2 tables only; they do not affect your IMS databases.


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*