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

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