Generate for DB2

This document covers the following topics:

Common Parameters for All DB2 Types
DB2 Database
DB2 Procedure/Function
DB2 Table/View
DB2 Storagegroup
DB2 Tablespace

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
Keywords
Sample Output

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
Keywords

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

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
Keywords
Sample Output

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

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
Keywords
Sample Output

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

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
Keywords
Sample Output

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.

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;