Generate for DB2

This document covers the following topics:

Prerequisites

Generation of DB2 objects is subject to DB2 security mechanisms. See the section DB2 and SQL/DS in the Predict and Other Systems documentation for more information.


Common Parameters for All DB2 Types

The following parameters can be set in all DB2 object types.

Parameters
SQL Server

This parameter is defined on page 2 of the schema generation wizard. This page does not appear when only the default server can be used.

The SQL server, the object will be implemented in. The value is preset with the value specified in the user or system profile.
Use SQLID To switch from the logon user ID to a group ID in DB2, you can enter a different SQL ID in this field. The statement SET CURRENT SQLID will then be submitted.
List DB2 statement
Y List the generated DB2 SQL statements.
Protocol saved in member SQL statements generated for the implementation of the database can be saved in a Natural source member (protocol) on the FDIC file. A member name for the protocol is only required when the DB2 object is generated for the first time. SQL statements generated in subsequent generation runs are concatenated to the protocol. The protocol can be displayed using the Display function of the Administration Implemented object-type menu of Predict.
Protocol saved in library Library where the protocol is to be saved.

DB2 Database

A DB2 database can be implemented with Schema Generation by generating the necessary SQL statements. The generated SQL statements can be saved in a generation protocol.

Note:
This function is only available if DB2 is installed in your server environment. The function is not applicable to SQL/DS.

The following topics are covered below:

Parameters

Parameters for DB2 database

Values for fields which have been locked by your data dictionary administrator cannot be overwritten. These fields are skipped when positioning the cursor with the TAB key. See Generation Defaults.

Common parameters which are not included in the table below are described under Common Parameters for All DB2 Types.

Presetting
The parameter below can be changed in the Modify DB2 Database Defaults screen of Predict. See Generation Defaults.
Count of saved actions Number (0-99) of generation actions which are stored in the protocol specified under Protocol saved in member/library. If 99 is entered, all generation actions are stored. This is the default setting at installation.
Parameters
Add database
Y A new DB2 database is created. If the DB2 database already exists, this parameter must be set to N.
Update database
Y If the database is already implemented, the differences of the implemented database and the Database object in Predict are determined and update commands are generated to modify the implemented database according to the Predict object.

Keywords

The schema generation wizard uses the following keywords when showing the command that is to be processed:

Field Keyword
Database ID DB
List DB2 statement LIST
Protocol saved in member MEM
Protocol saved in library LIB
Add database ADD-DB
Update database UPD-DB
Use SQLID SQLID
SQL Server SQL-SERVER

If a parameter is not specified, the default value is taken.

Sample Output

Database-ID: EXAM-DATABASE

CREATE DATABASE EXAMDB
    STOGROUP EXAM001
    BUFFERPOOL BP1;

DB2 Procedure/Function

A DB2 procedure or function requires an object of type Program as input, from which then either a procedure or a function is generated.

Note:
This function is only available if DB2 is installed in your server environment. The function is not applicable to SQL/DS.

The following topics are covered below:

Parameters

Parameters for DB2 procedure/function

Values for fields which have been locked by your data dictionary administrator cannot be overwritten. These fields are skipped when positioning the cursor with the TAB key. See Generation Defaults.

Common parameters which are not included in the table below are described under Common Parameters for All DB2 Types.

Presetting
The parameter below can be changed in the Modify DB2 Proc/Function Defaults screen of Predict. See Generation Defaults.
Count of saved actions Number (0-99) of generation actions which are stored in the protocol specified under Protocol saved in member/library. If 99 is entered, all generation actions are stored. This is the default setting at installation.
Parameters
Add procedure/function
Y A new DB2 procedure/function is created. If the DB2 procedure/function already exists, this parameter must be set to N.
Replace
Y An existing procedure/function in DB2 is dropped and created newly.
Update procedure/function
Y If the procedure/function is already implemented, the differences of the implemented procedure/function and the program object in Predict are determined and update commands are generated to modify the implemented procedure/function according to the Predict object.
Comment on
Y The first eight abstract lines of Predict field and file objects are used as DB2 comments for the columns and the table (SQL statement COMMENT ON).

Note:
Links concerning the associations IN (Input FI) and RE (Returns FI) define the input and output parameters of the procedure or function.

Keywords

The schema generation wizard uses the following keywords when showing the command that is to be processed:

Field Keyword
Program ID PROGRAM-ID
Add procedure/function ADD-PROCEDURE
Update procedure/function UPD-PROCEDURE
Replace REPLACE
Comment on COMMENT
List DB2 statement LIST
Protocol saved in member MEM
Protocol saved in library LIB
Use SQLID SQLID
SQL Server SQL-SERVER

If a parameter is not specified, the default value is taken.

DB2 Table/View

The Generate DB2 Table / View function:

  • Generates DB2 and SQL/DS Table/View/Indexes. If a table contains a LOB column, the function generates a SET CURRENT RULE='STD' statement which allows DB2 to create the necessary auxiliary tablespaces, tables and indexes for these objects.

  • Generates distinct types.

  • Generates check expressions. A check expression that differs from the documentation in Predict is dropped and recreated.

  • Generates triggers. If a trigger is linked to a DB2 view, an INSTEAD OF trigger is generated. A trigger that differs from the documentation in Predict is dropped and recreated.

  • Modifies existing DB2 and SQL/DS Table/Indexes, replaces DB2 and SQL/DS Views/Indexes or

  • Purges indexes from DB2 or SQL/DS tables if no longer documented with a corresponding Predict file object of file type D (DB2 Table).

  • Purges masks or permissions from DB2 tables if no longer documented with a corresponding Predict file object of file type D (DB2 Table).

Note:
If an XML column (Field format LO and Character Set X) is added for the first time to a DB2 table, you are recommended to run the function Compare DB2 table after the generation of the DB2 table. The function Compare DB2 table must be executed on the server with the update options Add fields and Field attributes set to Y.

The following topics are covered below:

Parameters

Parameters for DB2 table/view

Values for fields which have been locked by your data dictionary administrator cannot be overwritten. These fields are skipped when positioning the cursor with the TAB key. See Generation Defaults.

Common parameters which are not included in the table below are described under Common Parameters for All DB2 Types.

Presetting
The parameter below can be changed in the Modify DB2 Table/View Defaults screen of Predict. See Generation Defaults.
Count of saved actions Number (0-99) of generation actions which are stored in the protocol specified under Protocol saved in member/library. If 99 is entered, all generation actions are stored. This is the default setting at installation.
Parameters
Add table/view
Y A new DB2 table/view is created. If the DB2 table already exists, this parameter must be N.
Replace view
Y If the DB2 view already exists, the DB2 statements DROP VIEW and COMMIT are generated first, followed by the CREATE VIEW statement.
Add index If DB2 indexes are defined for the DB2 table in Predict, set this option to Y to generate the CREATE INDEX statements.
Update table
Y If the DB2 table/view is already implemented, commands are generated to adapt the DB2 table/view to the documentation (if differences were found). This includes the generation of necessary ALTER TABLE DROP COLUMN statements for fields that are no longer documented.
Update index
Y

If the DB2 index is already implemented, update commands are generated to adapt the DB2 index to the documentation (if differences were found).

Updating DB2 indexes is only possible for differences of the following attributes: buffer pool, close, freepage, pctfree, VSAM catalog, storage group, primary allocation, secondary allocation, erase, index type, GBPCACHE, piece size.

If any other attributes differ, Update index and Purge index must be set to Y. Predict will then update the index by issuing the statements DROP INDEX, COMMIT and CREATE INDEX.

Purge index
Y A DROP INDEX and a COMMIT statement are generated if a DB2 index exists and no corresponding index is defined in Predict.

Note:
Partitioned indexes cannot be purged.

Column masks
Options for column masks. Possible values:
E Enable
D Disable
A Activate
X Deactivate
EA Enable & Activate
EX Enable & Deactivate
DA Disable & Activate
DX Disable & Deactivate
Row permissions Options for row permissions. Possible values: See Column masks option above.
Defer option for indexes
Y The SQL statement CREATE INDEX is generated with the option DEFER YES.
N The SQL statement CREATE INDEX is generated without the option DEFER YES.
Label on
Y The string specified in the Predict field attribute Header1 is used as DB2 label for the columns (SQL statement LABEL ON).

Note:
If Label on is set to Y, existing labels are generally overwritten even if Header1 is not specified in Predict for each column in the table.

Comment on
Y The first eight abstract lines of Predict field and file objects are used as DB2 comments for the columns and the table (SQL statement COMMENT ON).

Note:
Comment on is set to Y, existing comments are generally overwritten even if no abstract is specified in Predict.

Special Considerations for Temporal Tables and Archive-Enabled Tables

When generating temporal tables, Predict only requires the name of the history or archive table to be used. This history or archive table is then automatically generated and connected to the base table. The history or archive table name can be provided when documenting a temporal table in Predict.

If you want to use a history or archive table with an index of its own then this history or archive table must be documented separately in Predict. Indexes can then be defined manually where required.

Such a separate history or archive table must then also be generated separately. The connection between the base table and the history or archive table, that is normally maintained automatically in Predict, will then be dropped. You will have to manually and individually apply changes made to the base table also in the history or archive table.

After the changes have been applied, you will have to generate both, the base table and the history or archive table, individually in order for the changes to take effect.

A third generation process is then required to reestablish the connection between the base table and the history or archive table.

Keywords

The schema generation wizard uses the following keywords when showing the command that is to be processed:

Field Keyword
File ID FILE-ID
Add table/view ADD-TABLE
Add index ADD-INDEX
Update table UPD-TABLE
Update index UPD-INDEX
Purge index PURGE-INDEX
Replace view REPLACE
List DB2 statements LIST
Protocol saved in member MEM
Protocol saved in library LIB
Label on LABEL
Comment on COMMENT
Defer option for indexes DEFER
Use SQLID SQLID
SQL Server SQL-SERVER
Column masks MASK
Row permissions PERMISSION

If a parameter is not specified, the default value is taken.

Sample Output

File-ID: EXAM-GEN_TABLE
CREATE TABLE EXAM.GEN_TABLE
   (EMPNO                    CHAR(6)          NOT NULL,
    FIRSTNME                 VARCHAR(12)      NOT NULL,
    MIDINIT                  CHAR(1)          NOT NULL,
    LASTNAME                 VARCHAR(15)      NOT NULL,
    WORKDEPT                 CHAR(3)          NOT NULL WITH DEFAULT,
    PHONENO                  CHAR(4)          NOT NULL WITH DEFAULT,
    HIREDATE                 DATE             NOT NULL WITH DEFAULT,
    JOB                      CHAR(8)          NOT NULL WITH DEFAULT,
    EDLEVEL                  SMALLINT         NOT NULL WITH DEFAULT,
    SEX                      CHAR(1)          NOT NULL WITH DEFAULT,
    BIRTHDATE                DATE             NOT NULL WITH DEFAULT,
    SALARY                   DECIMAL(9, 2)    NOT NULL WITH DEFAULT,
    BONUS                    DECIMAL(9, 2)    NOT NULL WITH DEFAULT,
    COMM                     DECIMAL(9, 2)    NOT NULL WITH DEFAULT,
    PRIMARY KEY (EMPNO))
   IN DATABASE EXAMDB
   EDITPROC PROGDB
   AUDIT NONE;
CREATE UNIQUE INDEX PRDDBA.EMPNO
   ON EXAM.GEN_TABLE
      (EMPNO                       ASC)
   SUBPAGES 4
   BUFFERPOOL BP0
   CLOSE YES;
CREATE INDEX DSN8210.XEMP2
   ON EXAM.GEN_TABLE
      (WORKDEPT                    ASC)
   SUBPAGES 4
   BUFFERPOOL BP0
   CLOSE YES;

DIC1880 SUMMARY:     14 FIELD(S) PROCESSED

DB2 Storagegroup

A DB2 storagegroup can be implemented with Schema Generation by generating the necessary SQL statements. The generated SQL statements can be saved in a generation protocol.

The following topics are covered below:

Parameters

Parameters for DB2 storagegroup

Values for fields which have been locked by your data dictionary administrator cannot be overwritten. These fields are skipped when positioning the cursor with the TAB key. See Generation Defaults.

Common parameters which are not included in the table below are described under Common Parameters for All DB2 Types.

Presetting
The parameter below can be changed in the Modify DB2 Storagegroup Defaults screen of Predict. See Generation Defaults.
Count of saved actions Number (0-99) of generation actions which are stored in the protocol specified under Protocol saved in member/library. If 99 is entered, all generation actions are stored. This is the default setting at installation.
Parameters
Add storagegroup
Y A new DB2 storagegroup is created. If the DB2 storagegroup already exists, this parameter must be set to N.
Update storagegroup
Y If the DB2 storagegroup is already implemented, commands are generated to adapt the DB2 storagegroup to the documentation (if differences were found).

Keywords

The schema generation wizard uses the following keywords when showing the command that is to be processed:

Field Keyword
Storagespace ID STORAGESPACE-ID
Add Storagegroup ADD-STORAGEGROUP
Update Storagegroup UPD-STORAGEGROUP
List DB2 statements LIST
Protocol saved in member MEM
Protocol saved in library LIB
Use SQLID SQLID
SQL Server SQL-SERVER

If a parameter is not specified, the default value is taken.

Sample Output

Storagespace-ID: EXAM-STORAGEGROUP

CREATE STOGROUP EXAM001
       VOLUMES (VOL05 )
       VCAT DB2;

DB2 Tablespace

A DB2 tablespace can be implemented with Schema Generation by generating the necessary SQL statements. The generated SQL statements can be saved in a generation protocol.

The following topics are covered below:

Parameters

Parameters for DB2 tablespace

Values for fields which have been locked by your data dictionary administrator cannot be overwritten. These fields are skipped when positioning the cursor with the TAB key. See Generation Defaults.

Common parameters which are not included in the table below are described under Common Parameters for All DB2 Types.

Presetting
The parameter below can be changed in the Modify DB2 Tablespace Defaults screen of Predict. See Generation Defaults.
Count of saved actions Number (0-99) of generation actions which are stored in the protocol specified under Protocol saved in member/library. If 99 is entered, all generation actions are stored. This is the default setting at installation.
Parameters
Add tablespace
Y A new DB2 tablespace is created. If the DB2 tablespace already exists, this parameter must be set to N.
Update tablespace
Y If the tablespace is already implemented, the differences of the implemented tablespace and the documented dataspace are determined and update commands are generated to modify the implemented tablespace according to the documented dataspace.

Note:
Certain parameters may be modified with the ALTER TABLESPACE statement only when the database is active, other parameters only when the database is stopped. For this reason it may be necessary to submit the generation job twice.

Define
N A DEFINE NO clause is generated
Partitions initialized Only for tablespaces with type G (Partition by growth). Specifies the number of partitions to be initialized (corresponds to the NUMPARTS option in DB2) and must not exceed the total number of partitions defined for that tablespace.

Keywords

The schema generation wizard uses the following keywords when showing the command that is to be processed:

Field Keyword
Dataspace ID DATASPACE-ID
Add tablespace ADD-TABLESPACE
Update tablespace UPD-TABLESPACE
List DB2 statements LIST
Protocol saved in member MEM
Protocol saved in library LIB
Define DEFINE
Use SQLID SQLID
SQL Server SQL-SERVER
Partitions initialized NUMPARTS

If a parameter is not specified, the default value is taken.

Sample Output

Dataspace-ID: EXAM-TABLESPACE

CREATE TABLESPACE EXAMTS IN EXAMDB
       NUMPARTS 4
       BUFFERPOOL BP0
       LOCKSIZE ANY
       CLOSE NO;