In this section: |
This chapter describes differences between the MODIFY and Maintain facilities when these differences affect adapter processing.
Your FOCUS documentation contains a detailed discussion of file maintenance with the MODIFY and Maintain facilities. Read the MODIFY and Maintain information carefully before developing procedures to use with RDBMS tables.
Note: You can maintain up to 64 tables in a single MODIFY or Maintain procedure. The limit for a MODIFY COMBINE or a Maintain procedure is 16 Master Files. However, each Master File can describe more than one table, for a total of 64 segments per procedure minus one for the artificial root segment created by the COMBINE command. In addition, a Maintain procedure can call other Maintain procedures that reference additional tables.
Maintaining IDMS network tables is not supported. The MODIFY facility supports true IDMS/SQL tables only.
Maintain provides a graphical user interface and event-driven processing. In a Maintain procedure, temporary storage areas called stacks collect data, transaction values, and temporary field values. You can use the Maintain Window Painter facility to design Winforms, which are windows that display stack values, collect transaction values, and invoke triggers. A trigger implements event-driven processing by associating an action (such as performing a specific case in the Maintain procedure) with an event (such as pressing a particular PF key). Maintain also provides set-based processing through enhanced NEXT, UPDATE, DELETE, and INCLUDE commands.
Prerequisites for running MODIFY and Maintain requests include:
You can update some RDBMS views in accordance with data source rules. In general, the RDBMS permits updates to views that are subsets of columns, rows, or both. It does not permit updates to views that perform joins or involve aggregation. For rules regarding the maintenance of RDBMS views, see the SQL Reference Manual for the appropriate RDBMS.
You can maintain DB2 views as long as they are updateable, as defined in the IBM DB2 SQL Reference.
The examples in this chapter refer to the EMPINFO, COURSE, PAYINFO, ECOURSE, and EMPPAY Master and Access Files. File Descriptions and Tables contains a complete listing of Master and Access Files.
You can process incoming transactions by comparing (or matching on):
In MODIFY, a MATCH on a partial key or on a non-key may retrieve more than one row. MATCH returns only the first row of this answer set. Subsequent sections demonstrate how to use NEXT to retrieve the remaining rows.
In Maintain, MATCH always matches on the full primary key and retrieves at most one row. To match on a partial key or non-key in Maintain, you use the NEXT command without a prior MATCH. The Maintain implementation of the NEXT command fetches the entire answer set returned by the RDBMS directly into a stack. It also includes three optional phrases:
See your FOCUS documentation on maintaining databases for complete syntax.
In a table, the primary key is the column or combination of columns whose values uniquely identify a row in a table. Such columns may not contain null data.
The Master and Access Files for a table identify its primary key. Describing Tables to FOCUS explains how to describe primary key columns.
You can implement RDBMS referential integrity by defining primary and foreign keys in SQL CREATE TABLE statements (see Referential Integrity). Defining the primary key to the RDBMS is optional. Most tables have primary keys (and unique indexes created to support them) whether or not the CREATE TABLE statement explicitly identifies them. In this chapter, the term primary key or key refers to those columns that compose the unique identifier for each row.
Indexes enhance the performance of data maintenance routines, especially indexes created on a table's primary key. Without an index, the RDBMS must read the entire table to locate particular rows. With an index, the RDBMS can access rows directly when given search values for the indexed columns. A table can have several associated indexes. Indexes created for performance reasons can be unique or non-unique. To use either RDBMS or FOCUS referential integrity, create indexes on foreign keys.
You can define a unique index on one or more columns in a table. When an index is unique, the concatenated values of the indexed columns in one row cannot be duplicated in any other row. A unique index is generally defined on a primary key. Once you create it, the RDBMS automatically prevents the insertion of duplicate index values. Any attempt to insert a duplicate row generates an error message.
An example of a unique index on a primary key is the employee ID (EMP_ID) in the sample EMPINFO table. Since no two employees can have the same employee number, the value in the EMP_ID column makes each row unique:
EMP_ID LAST_NAME FIRST_NAME --------- ----------- ---------- 111111111 SMITH JON 123456789 JONES ROBERT 222222222 GARFIELD THOMAS 234567890 SMITH PETER
You cannot add another row with EMP_ID 111111111 to this table.
| Information Builders |