DB2

#### Search Results

Search

# 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 for DB2

- DB2 Table, File Type D

- DB2 View, File Type E

- Intermediate View, File Type IV

- Intermediate Table, File Type IT

- DB2 Query Table, File Type MT

## Naming Conventions for DB2

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

    15:45:09                *****  P R E D I C T  *****                  2015-05-05
                                   - Modify file -
    File ID ......... XYZ-ARCHIVE_TAB
    Type ............ DB2 table
    Contained in DA . DAEIDB2B
    Keys ..                                                                 Zoom: N

    Literal name ............
    Average count ...........                  Stability .......*   Not specified
    Check constraint name ...
    History/Archive table ..*
            usage as .......*    (none)
    Physical attributes in <Default Server>
      Number of partitions ..                  Restrict on drop . N (Y/N)
      Edit program ..........                  Part. size (GB) ..
      Row attributes ........   (Y/N)          CCSID ...........*   (none)
      Validation program ....                  Temporary ........ N (Y/N)
      Audit ................* N Audit none     Volatile ......... N (Y/N)
      OBid ..................                  Append ........... N (Y/N)
      Data capture .......... N (Y/N)          Hash size (KB) ...
      Compress .............* F FIXEDLENGTH    Logged ...........   (Y/N)
    Abstract     Zoom: N
      Additional attributes ..* N        * Associations ..* S

Additional attributes are available for files of type D that are used as accelerator tables.

    18:17:10                *****  P R E D I C T  *****                  2020-07-02
                                   - Modify file -
    File ID ......... XYZ-DB_V12                       Modified 2020-04-06 at 13:35
                                                             by XYZ

    DBMS extensions in <Default Server>
      Keylabel name .... on default                                         Zoom: N
      Accelerator name . ACC IN D
      Pagenum ……* R Relative

    * Additional attributes ..* S        * Associations ..* S

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
File ID | ID of the Predict object documenting the DB2 table. See Naming Conventions for DB2.
Contained in DA | ID of the database object containing the file.
DB2 Attributes
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.
F | Fixed length.
H | Huffman.
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.
Part. 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 for "usage as":
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
U | Unicode
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 | See Additional attributes / Associations.
Keylabel name | Specifies whether key label is specified at the table level for encryption.
Accelerator name | Identifies the accelerator in which the table will be defined.
Pagenum |
Identifies the type of page numbering that is used when you create a partition-by-range table space.. Valid values:
R | Relative.
A | Absolute.

## DB2 View, File Type E

        13:28:33             *****  P R E D I C T  *****              2007-05-31
                                       - Modify file -
        File ID ......... HNO-E                               Added 2007-05-31 at 13:44
        Type ............ DB2 view                               by HNO
        Contained in DA . B-ARH-DA-C
        Keys ..                                                                Zoom: N

        Literal name .............
        Average count ............
        Stability ...............*   Not specified
        SQL attributes
          Select ................* A Select all
          With check option .....* N No
        Abstract     Zoom: N

          Additional attributes ..* N          Associations ..* N

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
File ID | ID of the Predict object documenting the DB2 view.
Contained in DA | ID of the database object containing the file.

## 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.

        13:28:33             *****  P R E D I C T  *****              2007-05-31
                                       - Modify file -
        File ID ......... XYZ-IV                              Added 2007-05-31 at 13:24
        Type ............ Intermediate view                      by XYZ
        Contained in DA . B-ARH-DA-C
        Keys ..                                                                 Zoom: N

        Literal name .............
        Average count ............
        Stability ...............*   Not specified
        SQL attributes
          Select ................* A Select all
        Abstract     Zoom: N

          Additional attributes ..* N          Associations ..* N

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
File ID | ID of the Predict object documenting the intermediate view.
Contained in DA | ID of the database object containing the file.

## 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).

        13:28:33             *****  P R E D I C T  *****              2007-05-31
                                      - Modify file -
        File ID ......... XYZ-IT                              Added 2007-05-31 at 13:24
        Type ............ Intermediate table                     by XYZ
        Contained in DA . B-ARH-DA-C
        Keys ..                                                                 Zoom: N

        Literal name ....
        Average count ...
        Stability ......*   Not specified
        Abstract     Zoom: N

          Additional attributes ..* N          Associations ..* N

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
File ID | ID of the Predict object documenting the intermediate table.
Contained in DA | ID of the database object containing the file.

## 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.

    13:27:03                *****  P R E D I C T  *****                  2015-06-29
                                    - Modify file -
    File ID ......... XYZ_DB2-MT
    Type ............ DB2 query table
    Contained in DA .
    Keys ..                                                                 Zoom: N

    Literal name ............
    Average count ...........                  Stability ........*   Not specified
    Check constraint name ...
    Select .................* A Select all
    Physical attributes in <Default Server>                 (new)
      Number of partitions ..                  Partition size ....    (GB)
      Edit program ..........                  CCSID ............*   (none)
      Row attributes ........   (Y/N)          Volatile .......... N (Y/N)
      Validation program ....                  Maintained by ....* S System
      Audit ................* N Audit none     Include identity .. N (Y/N)
      OBid ..................                  Copy defaults ....* N Exclude
      Data capture .......... N (Y/N)          Enable query opt. . Y (Y/N)
      Restrict on drop ...... N (Y/N)          Append ............ N (Y/N)
      Compress .............* H HUFFMAN      Hash size (KB).....
    Abstract     Zoom: N                       Logged ............   (Y/N)
      Additional attributes ..* N          Associations ..* N

Additional attributes are available for files of type MT that are used as accelerator tables.

    18:17:10                *****  P R E D I C T  *****                  2020-07-02
                                   - Modify file -
    File ID ......... XYZ-DB_V12                       Modified 2020-04-06 at 13:35
                                                             by XYZ

    DBMS extensions in <Default Server>
      Keylabel name .... on default                                         Zoom: N
      Accelerator name . ACC IN D
      Pagenum...* A Absolute

    * Additional attributes ..* S        * Associations ..* S

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
File ID | ID of the Predict object documenting the DB2 query table. See Naming Conventions for DB2.
Contained in DA | ID of the database object containing the file.
DB2 Attributes
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.
Copy 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 opt. | Specifies that the DB2 query table can be used for query optimization.
Keylabel name | Specifies whether key label is specified at the table level for encryption.
Accelerator name | Identifies the accelerator in which the table will be defined.
Pagenum |
Identifies the type of page numbering that is used when you create a partition-by-range table space.. Valid values:
R | Relative.
A | Absolute.

### On this page

- Naming Conventions for DB2
- DB2 Table, File Type D
- DB2 View, File Type E
- Intermediate View, File Type IV
- Intermediate Table, File Type IT
- DB2 Query Table, File Type MT