Overview of File-AID for Db2

File-AID for Db2 is a self-contained Db2 database management and testing tool that lets you create, populate, customize and refresh data in your Db2 database with ease. The File-AID for Db2 interface is very similar to that of ISPF. The product’s familiar look and feel helps you learn and use it quickly.

This section provides information about File-AID for Db2, a BMC database management tool that lets you interactively edit, browse, and manipulate Db2 tables, views, and other objects. The section consists of the following topics:

• introduces File-AID for Db2 and briefly describes its features and functions.
• Accessing-and-navigating-the-File-AID-for-Db2-UI presents information that is common throughout File-AID for Db2 such as PF key settings.
• Primary Commands describes the File-AID for Db2 primary commands.
• Line Commands describes the File-AID for Db2 line commands.
• Defaults-and-user-parameters-function describes the File-AID for Db2 user-definable parameters.
• Browse-and-Edit-function describes the File-AID for Db2 Browse and Edit functions.
• Related-Table-Browse-and-Edit-function describes the File-AID for Db2 Related Table Browse and Edit function.
• Object-Administration-and-Objects-Utilities describes the utilities used to create, model, drop, or alter Db2 objects.
• Copy-Utility-function describes the copy utility used to copy rows between tables.
• Object-List-Utility-function describes the utility that allows you to display object lists and manipulate them simply by placing a mnemonic beside them.
• SQL-Command-Manager-Utility-function describes File-AID for Db2’s SPUFI connection.
• Privileges-Utility-function describes the utility used to display, grant, or revoke object privileges.
• Impact-Analysis-Utility-function describes the utility used to produce a report that helps you determine the impact of dropping or modifying a column. It identifies views, aliases, synonyms, indexes, plans/packages, and referentially related columns.
• Extract-and-Load-Utility-function describes the utility used to extract table data to a file or load data into a table.
• Batch-Facility-function describes the utility used to execute SQL in batch mode under the control of File-AID for Db2.
• SQL-Development-and-Analysis-function describes four facilities of the File-AID for Db2 SQL Development and Analysis function: Plan table maintenance, SQL plan analysis, Review or Print Db2 Explain reports, and SQL Source Development and Analysis.
• Print-Table-Data-or-Audit-Trail-function describes the table data and audit trail print facilities.
• Relationship-Facilitator describes the utility used to view and manipulate the relationship file.
• Data-Disguise-function describes the Data Disguise function which is used to disguise extracted data, and works in conjunction with File-AID/Data Solutions.
• Predicate-Analysis-Rules-Abbreviations describes the short and long messages you may receive when using Predicate Analysis.
• Modifying-Access-Path-Analysis-Text Procedures for modifying the text in the Access Path Analysis report. This report is produced through File-AID for Db2’s SQL Analysis function.
• Action-Bar-and-Pull-Downs-Summary provides a summary of the available action bar items and pull-down menus.
• File-AID-for-DB2-Interface Procedures for accessing File-AID for Db2 Browse and Edit from external sources other than BMC products.

z/OS UNIX Environment

A z/OS UNIX environment is only required to execute batch jobs using File-AID Data Privacy. The job submitter must have a RACF record with an OMVS segment for z/OS UNIX (or equivalent if using a different security package). This can be done with BPX.DEFAULT.USER, but note that z/OS v1.13 is the last release to support BPX.DEFAULT.USER. The CPUTIMEMAX value in the OMVS segment must be large enough to accommodate the File-AID Data Privacy jobs being run.

Product Capabilities

File-AID for Db2 is designed for the application programmer. It lets you attain self-sufficiency by removing barriers and helping you obtain the data needed to get your job done. Everything you need to create an effective Db2 test environment — without coding SQL — is included in one self-contained product. You can follow a logical work flow from start to finish without relying on IBM utilities.

You can create a table modeled from a DBA-designed table, copy data into it from an existing table, and then customize the data to represent exactly the conditions needed to test your program. Valuable test time is not spent working with awkward utilities or coding SQL.

During the application test cycle, you can review a concisely formatted list of privileges for an object. This helps avoid authorization failures and lets authorized users grant and revoke privileges.

You can interactively execute SQL statements and receive analysis of Explain information indicating how the statements perform and suggestions for improvement. This eliminates the need to recompile and rebind in order to verify the SQL. This feature also allows you to prototype SQL code before incorporating it into your application.

When extracting sensitive production data, it can also be disguised to protect privacy. Data disguise requires installation of BMC Compuware’s File-AID/Data Solutions 4.4 or higher.

Creating Objects

Simple ISPF-like panels guide you through Db2 object definition, modification, and deletion, without ever requiring that you code SQL. A comprehensive set of Db2 objects is supported, including tables, tablespaces, indexes, views, synonyms, and aliases. When creating tables, you can use the modeling option to help you build duplicates of existing tables, right down to primary and foreign key definitions, without knowing what the SQL DDL statements look like. File-AID for Db2 automatically assists in creating a primary key index.

Alter and drop capabilities, equivalent to those found in SQL, are provided so you can manage your own test tablespace environments.

Creating Test Tables

The easiest way to create test data is to start with data that already exists and then customize it. File-AID for Db2 gives you the capability to populate test tables with data copied from existing test or production tables without coding SQL. Using the Copy Rows Between Tables utility, just specify the From and To tables. If the To table does not exist, File-AID for Db2 provides the option of automatically creating it. Templates based on Db2 table definitions are displayed with like-named columns already matched. Instead of cumbersome SQL subselects, you indicate the columns where data is to be copied, even columns with different table definitions. You can further customize the copy request with selection criteria based on data content and row counts. Extended WHERE clauses and SQL Boolean operators are both supported.

Customizing Data

File-AID for Db2 gives programmers the power to quickly customize table data to test all layers of program logic. You do not have to know Db2, just ISPF, to perform full-screen browse and edit of Db2 tables. The Db2 table definition is presented to you, so there is no need to remember specific column name spellings and partial column names may be entered when desired.

Both column and row selections are available so that you can work with a subset of the table that is tailored just for you. Row selection supports all SQL Boolean operators and extended WHERE clauses. Selection criteria, once developed, may be saved and reused. Using SPUFI-like input, File-AID for Db2 also offers table data selection that leverages your existing SQL.

Once in Browse/Edit, table data is displayed using the existing Db2 table definition. Column names, length, and format are always displayed and a column ruler is shown to indicate the start of column data.

Table data can be scrolled and sorted, and rows can be excluded, copied, inserted, deleted, and repeated individually or in blocks — all using standard ISPF commands. Any number of columns can be held, hidden and redisplayed. The FIND and CHANGE commands can be applied globally or limited to a specific column.

Rapid Data Generation techniques with the REPEAT and CHANGE commands let you specify a key range to quickly create large amounts of unique records.

Authorizing Objects

File-AID for Db2 provides a clear, concise listing of authorizations on a given object, without coding SQL. Programmers can use this information to avoid frustrating reruns due to authorization failures. As objects are created or staff reassigned, DBAs, project leaders, or other authorized users can grant and revoke privileges using a familiar ISPF-like format. Familiar line commands like Insert, Repeat, and Delete make it easy. A Privilege edit capability lets you enter changes directly from an object authorization list.

Reducing the Db2 Learning Curve

There is much to learn in Db2, including SQL. File-AID for Db2 provides a familiar ISPF-like interface that lets you easily see, interpret, and change Db2 data and delete, define, authorize, and alter objects without coding SQL. You can choose to see the SQL that File-AID for Db2 uses to complete a request. This generated SQL can be edited or saved, then copied into a program or reused by File-AID for Db2 or even IBM’s SPUFI. The ability to review SQL and to see and work with the results of calls to Db2 can rapidly increase understanding of the Db2 environment.

Reducing Test Time

Easy ISPF-like screens walk you through the process of creating, populating, and customizing a table. Having your own test table eliminates the frustration of being locked out of a table by other testers or having other testers overwrite your carefully planned test conditions. The combination of no contention and effective test data helps reduce test time without sacrificing quality in production.

Restoring Programmer Self-Sufficiency

Constant requests to manage the Db2 test data environment can delay high priority projects. DBAs define the initial rules and environment. Subsequently, with File-AID for Db2, programmers can independently proceed to develop test data because File-AID for Db2 combines all the basic requirements into one self-contained product.

Programmers can work through a logical flow from start to finish without ever leaving File-AID for Db2 or relying on IBM utilities. As an added benefit, this frees DBAs to focus on their own priority assignments.

Improving Testing

You do not need to test in production. File-AID for Db2’s full-screen editor lets you customize table data easily so that you can test the full range of conditions that can occur in the production environment.

Prototyping SQL Code

The SQL Source Development and Analysis facility lets you insert and analyze new SQL statements in your source code, as well as modify existing SQL statements. This is made easy with the full power of ISPF edit. The cursor logic validation functions allow you to execute the statements, and browse or edit the results. This feature can be accessed through File-AID for Db2 or through an ISPF/PDF Edit session.

Enhancing SQL Efficiency

You can analyze your SQL statements, Plan, DBRM, Collection ID, or Package and maintain your Plan_Table with File-AID for Db2. You can also obtain an Explain report. This gives you vital information about the execution of your SQL statements to save you valuable execution time. Rules-based analysis of the Explain is provided in plain language combined with object attributes and catalog statistics. This text provides conditions, consequences, and recommendations relating to the performance of the SQL statement. You can generate an Explain report for SQL statements containing the DECLARE CURSOR, DELETE, INSERT, SELECT, and UPDATE verbs.

Producing an Audit Trail

Producing an audit trail in File-AID for Db2 is an installation option. An audit trail can be produced automatically for every edit session or Batch Execute SQL request, or it can be requested by an individual user. An audit trail dataset can be printed by selecting an option from the Primary Option Menu. When the SMF audit option has been installed, audit records are written to the SMF log file whenever an object is browsed or edited or updated with a Batch Execute SQL request.

DBCS (Double-Byte Character Set) and Uppercase-Only Support

You can install double-byte character set (DBCS) and/or uppercase-only support. DBCS allows editing and browsing of international languages that require two bytes of storage for each character. Uppercase-only support is provided for terminal types that do not support the display of lowercase characters.

Sharing Disguised Data with Other File-AID Products

You can disguise your Db2 extracted data for greater data security. The Data Disguise function in File-AID for Db2 is the same as in File-AID/Related Data XPERT (File-AID/RDX) and in File-AID for IMS. File-AID for Db2 can share the Disguise Control File and Disguise Criteria File with File-AID/RDX and File-AID for IMS. File-AID for Db2 can also share disguise rules with File-AID/RDX.

Product Functions

File-AID for Db2 provides you with functions for creating, updating, maintaining objects in your Db2 environment, as well as disguising extract files and developing and analyzing SQL statements. File-AID for Db2’s Primary Option Menu (see the following figure) provides immediate access to all product functions. Press PF7 or PF8 to scroll up and down in this menu.

File-AID for DB2 Primary Option Menu

User Parameters

The User Parameters option allows you to set the following parameters:

• Browse/Edit/SQL Analysis display options
• Dataset allocation
• Batch options
• Object Administration utility default values
• Logging options
• Print options
• Special register values
• Trigger terminator

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 also lets you display a table, alias, or view. Again, 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/RDX. 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:

• Object Administration: Without coding SQL or JCL, you can create, alter and drop Db2 objects and much more. The user interface for Object Administration is a window-like interface within ISPF, whereas the rest of  File-AID for Db2 is an ISPF user interface. For more information, refer to the File-Aid for Db2 Object Administration User Reference manual.
• Create, Model, Drop, Alter DB2 Objects: Allows you to create, model and drop different Db2 object types: tables, tablespaces, indexes, views, synonyms, aliases, distinct types (UDT), user defined functions (UDF), stored procedures, triggers, materialized query tables (MQT), clone tables, history tables, and temporal tables. Tables, tablespaces, indexes, UDFs, stored procedures, and MQTs can be altered, and tables can be renamed.
• 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 Processing: 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, or Revoke DB2 Privileges: You can easily display, grant, or revoke privileges for tables, views, plans, tablespaces, collections, packages, schemas, stored procedures, UDTs, and UDFs 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 extracted data to a table, alias, or view. Optionally, you can also extract data in delimited file format. Both online and batch processing are available.

  The extract process can also disguise the extracted data to provide security for sensitive data. The disguise criteria can be defined by either using the File-AID for Db2 Option 7, Data Disguise, or the File-AID Data Privacy component of the Topaz Workbench.

  Note

  Topaz Workbench: The BMC Compuware Workbench has been replaced by the Topaz Workbench. The Topaz Workbench provides all of the functionality of the previous BMC Compuware Workbench as well as any additional chargeable functionality.

• 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.
• Batch Execute SQL with Auto-COMMIT Processing: You can execute SQL in batch mode under control of File-AID for Db2. Any SQL that can be executed dynamically can execute in File-AID for Db2 batch. When the SQL contains UPDATE or DELETE processing, File-AID for Db2 will add automatic commit processing at intervals which you specify.

• SQL Development and Analysis: The SQL Development and Analysis option is an interactive tool for SQL source development and analysis, SQL plan analysis, Plan table maintenance, and review or printing of a Db2 Explain report.

  SQL Source Analysis and Development is accessed through File-AID for Db2, or it can be invoked directly from an ISPF Edit session. With this feature, you can:

  • Dynamically execute SQL statements
  • Browse and edit the Db2 results table
  • Obtain Explain information about how SQL statements 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 Xpediter 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 dataset.

Print Table Data or Audit Trail

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

Relationship Facilitator

The Relationship Facilitator function allows you to view and manipulate the relationship file that is used in the Related Table Browse and Edit functions. This function allows you to create a new relationship file that File-AID for Db2 uses as a repository to store the RI and AR relationship definition. You can view all relationships defined to File-AID for Db2, load, refresh, and delete Db2 referential integrity relationships, and add and delete application relationships. File-AID for Db2 also provides column subfield support for application relationships.

Visualizer

The Visualizer plugin for Topaz Workbench provides another way to view the relationship definitions in a relationship file. Refer to the Topaz Workbench documentation for more information on the Visualizer.

Data Disguise

The Data Disguise function allows you to disguise sensitive extracted data prior to moving it to a test environment. In this context, disguising data means to change the identifying characteristics from the data, such as name and address information, while still maintaining the structure and relationships of the original data.

You have the option to define disguise criteria by either using the File-AID for Db2 Option 7, Data Disguise, or the File-AID Data Privacy component of the Topaz Workbench. Data Disguise requires installation of BMC Compuware’s File-AID/Data Solutions and, optionally, File-AID Data Privacy products.

Xpediter for DB2 Extension

The Xpediter for DB2 Extension option lets you focus on interactive testing without interruption. You can dynamically enter and experiment with SQL statements during an Xpediter interactive test session without stopping to compile or bind. You can immediately proceed with the test and see the impact of a new statement in your program’s logic flow. Most File-AID for Db2 data management tools can be accessed from the Xpediter test session, including the SQL analysis capability.

File-AID/Related Data XPERT

File-AID/Related Data XPERT (File-AID/RDX) 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’s Related Table Edit function.