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
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 .............. (Y/N) 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
* Additional attributes ..* S * Associations ..* S |
| Parameters |
| File ID |
ID of the Predict object documenting the DB2 table. See
Naming
Conventions.
|
| 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. |
|
| 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.
|
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
|
| 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
|
| 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:
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
|
| 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
- Add a 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 .............. (Y/N) 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
* Additional attributes ..* S * Associations ..* S |
| Parameters |
| File ID |
ID of the Predict object documenting the DB2 query
table. See Naming
Conventions.
|
| 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.
|
