FADB2

Description

The FADB2 command accesses File-AID for Db2.

You can enter the FADB2 command with the keyword or one-character number or letter shown as follows. Abbreviations are not allowed.

The FADB2 command is not valid in a batch test.

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

Input

Entering the FADB2 command without a keyword or one-character number or letter displays the File-AID for Db2Primary Option Menu shown in File-AID for DB2 Primary Option Menu. Bypass the Primary Option Menu and access a specific File-AID for Db2 facility by entering the command and the appropriate keyword or one-character number or letter. For example, to access the File-AID for Db2Browse facility, enter the following on the command line:

The following are the parameter descriptions for the FADB2 command:

0/PARMS

Displays the User PARMS menu, allowing you to specify the parameters you want to modify.

1/BROWSE

Lets you display a table or view, either in its entirety or by selected rows.

2/EDIT

Lets you make changes to a table or view.

3/UTILITY

Provides access to various utilities that let you create, populate, authorize, drop, alter, and obtain information about Db2 objects.

4/ANALYSIS

An interactive tool for SQL source development and analysis, SQL plan analysis, Plan Table maintenance, and review or printing of a Db2 Explain report.

T/TUTORIAL

Provides online information about File-AID for Db2.

EXPLAIN statement-number (COBOL and PL/I)

Displays and collects information about the execution of SQL statements in your program. It can be used for SQL statements that contain the DECLARE CURSOR, DELETE, INSERT, SELECT, or UPDATE verbs. The XP line command performs the same functions and can also be used for PL/I.

When the FADB2 EXPLAIN command is entered, an SQL EXPLAIN call is executed for the specified statement and the Explain report is displayed at your terminal.

The Explain data is also stored in a Db2 table called xyz.Plan_Table, where xyz is the authorization ID. You can generate reports with this data, using the File-AID for Db2 Reports facility, or view the xyz.Plan_Table with the File-AID for Db2 Browse facility.

HELP

Provides online help for negative SQL return codes and messages from Code Debug Db2 Extension.

File-AID for DB2 Primary Option Menu

The File-AID for Db2 Primary Option Menu shown in the following figure provides immediate access to all product functions.

File-AID for DB2 Primary Option Menu

DEFAULTS and USER PARMS

The DEFAULTS and USER PARMS option allows you to set the following parameters:

• 
  
  • Browse/Edit/Analysis display options
  • Data set allocation for SQL
  • Batch options
  • PF key labels and definitions
  • Index, tablespace, and database default values
  • Logging options
  • Print options.

BROWSE

The BROWSE option lets you display a table, alias, or view, either in its entirety or by selected rows or columns. You can specify which information you want to see, or you can indicate that you want certain data excluded. You cannot, however, make any changes to the table or view. You can browse a single table at one time, or you can browse multiple related or unrelated tables.

EDIT

The EDIT option lets you display a table, alias, or view. In addition, you can display the table in its entirety or by selected rows or columns.

Optionally, you can save your edit template selection criteria in a data set for convenient recall in a future edit or browse session. With Edit, however, you can make changes to a table and most views.

There are some views that cannot be edited. If you attempt to modify one of these, Db2 locks out the change and File-AID for Db2 notifies you that you are not allowed to make this change. You can edit a single table at one time, or you can edit multiple related or unrelated tables.

The Related Table Edit function enables you to edit tables related through Db2 referential integrity or through an application relationship. Application relationships can be created using File-AID for Db2 or File-AID/Related Data XPERT. An unlimited number of related tables may be edited.

UTILITIES

The UTILITIES option provides access to a number of different utilities. These utilities allow you to easily manage your Db2 work environment. The utilities are:

• 
  
  • Create, Drop, Alter DB2 Objects: Allows you to create and drop six different Db2 object types: tables, tablespaces, indexes, views, synonyms, and aliases. Tables, tablespaces, and indexes can be altered. Synonyms and aliases can be modified.
  • Copy Rows Between Tables: Allows you to copy data between tables. All rows and columns can be copied, or selected rows and columns can be copied.
  • Object List: Lets you display object lists and perform functions such as Browse and Edit.
  • SQL Command Manager: Lets you connect with IBM’s SPUFI without leaving File-AID for Db2.
  • Display, Grant, Revoke, or Modify DB2 Privileges: You can easily display, grant, revoke, or modify privileges for tables, views, plans, and tablespaces by entering simple mnemonics on a screen.
  • Extract and Load Tables: You can extract a table, alias, or view to a data set, or load a data set to a table, alias, or view. Optionally, you can also extract data in delimited file format. Both ONLINE and BATCH processing are available.
  • DBA-XPERT Column Impact Analysis: You can view or print a batch report that helps you determine the impact of dropping or modifying a column by identifying views, aliases, synonyms, indexes, plans/packages, and referentially related columns.

SQL ANALYSIS

The SQL ANALYSIS option is an interactive tool for SQL source development and analysis. It also serves as an interactive tool for SQL plan analysis, Plan Table maintenance, and review or printing of a Db2 Explain report.

With the SQL Development and Analysis feature, you can:

• 
  
  • Dynamically execute SQL statements
  • Browse and edit the Db2 results table
  • Obtain Explain information about how SQL statements will perform
  • Obtain help information for any SQL coding errors.

With SQL Plan Analysis, you can explain a plan, DBRM, collection ID, or package. A rules-based analysis of the Explain is available in plain language. Predicates can be analyzed to determine if they are indexable, stage I, or both. Based upon the analysis, recommendations for improving the performance of the SQL are provided.

The print option allows you to review or print a Db2 Explain report from a specified plan table. The report gives you vital information about the execution of selected SQL statements in your application program. The Db2 Explain report is also available when compiling with Code Debug and Abend-AID.

Plan Table maintenance allows you to delete rows from a plan table.

In addition to ISPF, File-AID for Db2 provides an interface to both CA-Panvalet and CA-Librarian. The SQL Source Analysis and Development facility can be used to analyze members stored in either CA-Panvalet or CA-Librarian without the need to copy the member into a sequential or partitioned data set.

PRINT

The PRINT option on the Primary Option Menu allows you to print selected data from a Db2 table or an audit trail data set. If the audit trail facility is enabled, an audit trail data set is created each time an object is edited with File-AID for Db2.

FILE-AID/RDX

The File-AID/RDX (File-AID/Related Data XPERT) option lets you easily extract and load a complete subset of related Db2 and MVS data. You can copy data related by referential integrity or application-defined relationships within the Db2 and MVS environments with full support for all relational constraints.

File-AID/RDX gives programmers ISPF-like facilities to create and populate test tables and data sets that accurately reflect production relationships, yet contain only a portion of the data, without coding SQL or writing programs. DBAs can migrate related sets of data from one Db2 subsystem and/or MVS environment to another, maintaining all parent/child data relationships without extensive manual procedures.

Relationships defined to File-AID/RDX can be shared with File-AID for Db2 Related Table Edit function.

Usage Notes

1. Enter the FADB2 EXPLAIN command as follows:
   • On the primary command line, enter FADB2 EXPLAIN with the SQL statement number.
   • Enter the XP line command on the first line of the SQL statement to be explained.
2. In the case of a DECLARE statement, the command is performed on the SELECT portion of the DECLARE statement. If the DECLARE statement is hard coded in working storage, the XP line command cannot be used. Instead, use the FADB2 EXPLAIN primary command with the statement number.
3. If the UPDATE statement contains a WHERE CURRENT OF clause, the UPDATE statement will not be explained. It is more meaningful to issue the FADB2 EXPLAIN command or XP line command on the corresponding DECLARE statement.
4. The FADB2 EXPLAIN command is not supported for the C language.

Examples

1. 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,

   When the XP line 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 *****************************

2. In the following figure, the XP line command is entered on the EXEC SQL DECLARE CURSOR line.
   Using the FADB2 EXPLAIN Line Command

    
    ------
    ------   /***$$$
     XP ---         EXEC SQL DECLARE DIRECTORY CURSOR FOR
     ------                  SELECT *
     ------                  FROM   VTRIDB2
     ------                  WHERE  LASTNAME  LIKE :LNAME_WORK
     ------                     AND  FIRSTNAME LIKE :FNAME_WORK
   

   When the XP line 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
   DECLARE DIRECTORY CURSOR FOR
   SELECT *
   FROM VTRIDB2
   WHERE LASTNAME LIKE :LNAME_WORK
   AND FIRSTNAME LIKE :FNAME_WORK
   
   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    1     0      R     0     N     IS NNNN  NNNN    1     S         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 *****************************