DB2

DB2 tables and views can be documented in Predict with file objects of type D and E respectively. These file objects can be used to generate DDMs or CREATE TABLE/VIEW statements.

This document covers the following topics:

Naming Conventions

The following naming conventions apply to files documenting DB2 tables and views.

Upper / lower case

File IDs must be entered in upper case. If the Predict parameter General Defaults > Miscellaneous > Upper/lower case / Object ID is set to L, lower-case IDs are not converted to upper case and an error message is given.

Hyphens

  • A hyphen is used to delimit the creator from the table/view name.

  • Only one hyphen is permitted in the ID of a DB2 table/view object.

  • When a table or view is generated from the Predict file object, the hyphen is converted to a period.

Length

  • Table/View names for DB2 objects can have up to 18 characters.

  • A fully qualified ID (Creator + Hyphen + Table/View name) must not exceed 27 characters.

Permitted characters

See overview of permitted characters in the section Naming Conventions.

DB2 Table, File Type D

Attributes

Note:
Parameters not listed below are described in other sections of this documentation: Parameters common to all object types, for example Keys, are described under Global Attributes. Parameters common to all file types, for example Literal name, are described under Common File Attributes. See also Common Parameters for SQL File Types.

Parameters
Number of partitions The number of partitions of the table.
Edit program The name of an edit routine for the table.
Row attributes
Specifies whether the edit procedure parameter list contains an address for the description of a row.
Y Yes. This is the default.
N No.
Validation program The name of a validation routine for the table.
Audit
The type of access to this table that will cause auditing to be performed. Valid values:
A All
C Changes
N None
OBid Identifies the OBID to be used for the table. An OBID is the identifier for an object's internal descriptor in DB2.

Note:
This parameter is required if parameter DB2 ROSHARE parm of the database object containing the table is set to R. See Database Type D - DB2.

See your DB2 documentation for more information.
Data capture
Y Data changes are passed to a user exit.
Compress
Specifies whether data compression applies to the rows of the implicitly created tablespace.
blank Not specified. This is the default setting.
Y Yes.
N No.
Restrict on drop
Y The DB2 table cannot be dropped. To drop a table with this setting, this parameter must be set explicitly to N.
Partition size Specifies that the table is to be partitioned by growth, every n GBytes. Where n is to be replaced by the desired integer value.
Hash size Specifies the amount of fixed hash space to preallocate for the partition that is associated. Hash size is n KBytes. Where n is to be replaced by the desired integer value.
Logged
Specifies whether changes that are made to the data in the implicitly created tablespace are recorded in the log.
blank Not specified. This is the default setting.
Y Yes.
N No.
Check constraint name If a table check expression has been defined and the name of a check constraint is entered here, the following clause is generated in the CREATE TABLE statement: 
CONSTRAINT constraint_name
CHECK (check_expression)
History/Archive Table
Only for system-period temporal tables. Name of the history or archive table linked to the DB2 base table. If this option is selected, the following values can be set:
blank Not specified. This is the default setting.
A Archive table.
E History add extra row. This is required for a history table using the ON DELETE ADD EXTRA ROW option.
H History table.
CCSID
Encoding scheme. Valid values:
blank not specified
A ASCII
E EBCDIC
Temporary
Y Global temporary table
N not temporary.
Volatile
Specifies how DB2 is to choose access to the table. Valid values:
Y Specifies that index access should be used on this table whenever possible for SQL operations.
N Specifies that SQL access to this table should be based on the current statistics. This is the default.
Append
Specifies whether append processing is used for the table.
Y Yes.
N No.
Check expression This is defined on the Check Expression tab.

DB2 View, File Type E

Attributes

Note:
Parameters common to all object types, for example Keys, are described under Global Attributes. Parameters common to all file types, for example Literal name, are described under Common File Attributes. See also Common Parameters for SQL File Types.

Intermediate View, File Type IV

The intermediate view can be used to specify subselects, joined tables and table functions in the from clause of DB2 views. The intermediate view defines a temporary view that does not exist in the DB2 catalog.

Attributes

Note:
Parameters common to all object types, for example Keys, are described under Global Attributes. Parameters common to all file types, for example Literal name, are described under Common File Attributes. See also Common Parameters for SQL File Types.

Intermediate Table, File Type IT

The field list of an intermediate table can be used to specify the parameters for:

  • a database function (object type PR subtype U) or

  • an SQL procedure (object type PR subtype R).

Attributes

Note:
Parameters common to all object types, for example Keys, are described under Global Attributes. Parameters common to all file types, for example Literal name, are described under Common File Attributes . See also Common Parameters for SQL File Types.

DB2 Query Table, File Type MT

Materialized query tables in DB2 are represented in Predict as objects of type DB2 query table, file type MT.

Attributes

Note:
Parameters not listed below are described in DB2 Table, File Type D or in other sections of this documentation: Parameters common to all object types, for example Keys, are described under Global Attributes. Parameters common to all file types, for example Literal name, are described under Common File Attributes. See also Common Parameters for SQL File Types.

Parameters
Number of partitions The number of partitions of the DB2 query table.
Edit program The name of an edit routine for the DB2 query table.
Validation program The name of a validation routine for the DB2 query table.
Audit
The type of access to this table that will cause auditing to be performed. Valid values:
A All
C Changes
N None
OBid Identifies the OBID to be used for the table. An OBID is the identifier for an object's internal descriptor in DB2.

Note:
This parameter is required if parameter DB2 ROSHARE parm of the database object containing the table is set to R. See Database Type D - DB2.

See your DB2 documentation for more information.
Data capture
Y Data changes are passed to a user exit.
Restrict on drop
Y The DB2 query table cannot be dropped. To drop a table with this setting, this parameter must be set explicitly to N.
CCSID
Encoding scheme. Valid values:
blank not specified
A ASCII
E EBCDIC
Volatile
Specifies how DB2 is to choose access to the table. Valid values:
Y Specifies that index access should be used on this table whenever possible for SQL operations.
N Specifies that SQL access to this table should be based on the current statistics. This is the default.
Maintained by
Specifies how the data in the DB2 query table is maintained. Valid values:
blank Not specified.
S System.
U User.
Include Identity Specifies that, if available, identity column attributes are inherited from the definition of the source table.
Include Defaults
Specifies that column defaults for each updatable column of the definition of the source table are inherited. Valid values:
blank Not specified.
Y Include.
N Exclude.
U Using type.
Enable query optimization Specifies that the DB2 query table can be used for query optimization.