DB2 objects can be documented in Predict and generation, incorporation, comparison and administration functions can be applied to them.
Note:
To use functions of Predict that support DB2, Natural for DB2 or
Natural SQL Gateway in a z/OS environment or Entire Access in a Windows, UNIX,
Linux or OS/2 environment must be installed. Most functions described in this
section apply both to DB2 and SQL/DS. Exceptions to this rule are listed as
appropriate.
This document covers the following topics:
The following topics are covered below:
DB2 storagegroups or database partition groups, databases, tablespaces, tables, views, columns, column masks, row permissions, distinct types, indexes, referential constraints, triggers, packages, application plans, procedures and functions can be documented in Predict.
The following table gives an overview of how different DB2 objects are documented.
Documenting DB2 objects is described in the sections below.
| DB2 Object | Documented in Predict with |
|---|---|
| Database | Database object of type D |
| Storagegroup (z/OS) / Database partition group (UNIX) | Storagespace object |
| DB2 tablespace / SQL/DS Dbspace | Dataspace object |
| Table / view | File object of type D or E |
| Table check constraint | Attribute of file object (type D) |
| Column check constraint | Verification object |
| Column mask | Access Definition object of type M |
| Row permission | Access Definition object of type M |
| Application plan (z/OS) | System object of type P |
| DBRM (z/OS) | Program object of type P and language Q |
| Package (z/OS) | Program object of type P and language B, C, F, H, P or Q or user-defined. Packages are linked to application plans with objects of type packagelist. |
| Collection (z/OS) | Packagelist attributes Collection name and Location name. |
| Index | Field attributes. |
| Column | Field objects. |
| Distinct Type | Field object of standard file SAG-DISTINCT-TYPE. |
| Trigger | Trigger object. |
| Procedure | Program object of type R. |
| Function | Program object of type U. |
You are able to document several implementations of DB2 objects in different servers. A DB2 server is documented as a database object with Type S (SQL server).
With the means of SQL servers, it is possible to document one logical database design and to assign different physical database implementations to DB2 objects. The object types affected are Storagespace, Dataspace, Database, File, Field and Program. For objects of these types, the maintenance function Physical attributes is available to assign an individual setting of physical attributes to an object in a specific server.
For example, you may have two DB2 servers named DB2PROD and DB2TEST. It is then possible to define a tablespace named TABSPAC in the DB2PROD server as partitioned and in the DB2TEST server as simple or segmented tablespace.
There is one specific server named <Default Server>, that is always available and must not be documented. This server represents the DB2 server you are automatically connected to from your Natural or TP monitor environment on mainframe platforms. On a Windows or UNIX platform, the server defined for DBID 250 is used. Which real DB2 server is denoted by <Default Server> depends on your current environment.
When a DB2 object is added, it has a set of physical attributes in the <Default Server>. This set of attributes can be modified but can not be deleted. In the Add, Copy and Modify object function, you can edit attributes of the logical database design as well as attributes of the physical database design in one preferred server. You can specify your preferred server in the maintenance options of your profile. If your preferred server is not <Default Server>, the physical attributes for <Default Sever> will be taken from the general defaults when an object is added.
There are general defaults for DB2 objects such as Storagegroup, Database, Tablespace, Table, Index, Procedure and Function. You may adapt the defaults to meet the requirements of your enterprise. These default values will be preset and may be modified when an object is added.
When executing the retrieval model #S for an SQL server, all implementations of DB2 objects in this server are shown.
If you are using IBM's Distributed Relational Database Architecture (DRDA) you will be able to connect to a specific DB2 server and perform the above mentioned functions for this specific server. This allows the administration of all DB2 servers in the network with one Predict installation. If you don't want to use explicitly documented servers, use <Default Server> to connect to your current DB2 server. When a function that has established a connection to a DB2 server other than <Default Server> terminates, this connection is released and the connection to <Default Server> is reestablished. Therefore, the user will have the same database connection after leaving Predict, that he had when he started his Natural session.
As indicated above, it is possible to define an index as clustered in one server and as not clustered in a second server. This influences whether the component fields of the index may be updated or not. Whether a field may be updated or not is indicated by special short names in the DDM for Natural. In the DDM generation defaults you can specify the server that should be used for data access from Natural.
Predict offers the support of DB2 also on Windows and UNIX platforms. To be able to access DB2, Natural for DB2 or Natural for SQL/DS is required on mainframe platforms. On Windows and UNIX platforms, Entire Access is required for access to DB2.
On Windows and UNIX platforms the DBID 250 is used to access DB2. DBID 250 must be defined as SQL in the Natural global configuration section. The connect string defined for DBID 250 is used to establish the connection to <Default Server>. After the execution of a function, that has established a connection to a DB2 server, the connection to <Default Server> is reestablished. It is therefore required, that the same user ID and password are defined for <Default Server> and every other DB2 server used.
When a connection to a server is established, a user ID and password may be required. The user ID and password can be changed within a Predict session by executing the command SET CATALOG_USER. This allows you to overwrite the previously entered user ID and password. This user ID and the password are then also used for every other connection to an SQL server.
Storagegroups or Database partition groups are documented as objects of type storagespace.
Databases are documented as objects of type database with database type D.
Only files of type D (DB2 table) can be linked to databases of type D.
Note:
DB2 databases on z/OS need only be documented with Predict database
objects if you intend to generate the DB2 database from the Predict database
object. If you use the option to create the DB2 database implicitly when
generating tablespaces, tables or views, the database need not be documented
with a Predict database object.
In DB2/SQL/DS, tables/views are not directly linked to databases: a DB2 tablespace or SQL/DS Dbspace establishes the connection of tables/views and databases.
DB2 tablespaces and SQL/DS Dbspaces are documented with Predict dataspace objects of type D (DB2) or S (SQL/DS) in a z/OS environment and type 2 in a UNIX environment.
Notes:
An SQL/DS Dbspace must be documented with a Predict tablespace object because a Dbspace cannot be created implicitly.
No auxiliary tablespaces are supported. See Columns With Format LOB for further information.
For tablespaces with partition type G (Partition by growth) it is possible to specify the number of partitions to be initialized with the function Partitions initialized. This function can be set in the Generation Defaults for DB2 tablespaces.
Refer to the parameter description given in DB2 Tablespace of the section Generation of External Objects in the External Objects in Predict documentation for details.
Tables are documented as files of type D. Views are documented as files of type E.
Note:
If a table contains a partitioning index, the number of partitions
must be documented as an attribute of the file if the file is not linked to a
dataspace via association "Contains FI".
The documentation of views is supported by an additional editor to
specify the part of the subselect clause starting from the first
FROM clause.
The selection clause of the subselect clause is documented by the
specification of the field list of the view. The specified list of tables/views
in the first FROM clause of the subselect clause is generated by
Predict and will be updated if a field from an additional table/view is added
to the view. Correlation names can be added to the tables and views in the list
(using editor functions). The remaining part of the subselect clause is left
unchanged.
The expression used to define DB2 or SQL/DS fields can contain complex expressions. Fields that are defined not only by a single column name but use either a constant or a more complex expression are called derived fields. A special editor is provided for specifying the expression of derived fields.
In the field expression and in the subselect clause, comment lines (lines starting with * or **) and remarks within a line (starting with /*) are allowed.
DB2 offers the possibility to use subqueries and joined tables in the
FROM clause of the view definitions. This functionality is also
supported under Predict.
You can define in the subquery of a view whether the FROM
clause is made up of an EXCEPT, EXCEPT ALL,
INNER, INTERSECT, INTERSECT ALL,
LEFT OUTER, RIGHT OUTER, FULL OUTER
JOIN, UNION or UNION ALL. Use the subquery
editor to add and modify joined views.
Predict has a file of type IV (Intermediate View) that enables you to
use subselects in the FROM clause of a view definition. Files of
type IV have a field list to show the selection clause and a subquery to show
the search condition. Just like views, files of type IV can have a Join type.
They can be used as master files for views and files of type IV. When a view is
generated that has a file of type IV as a master file, a SELECT
partial statement is generated into the FROM clause. Files of type
IV are not in the DB2 catalog. Nevertheless, the same naming standards are
valid for files of type IV as for tables and views. See
Naming Conventions for
DB2 for further information.
When using Incorporate and Compare, files of type IV are created in
addition to the view in order to represent subselects in the FROM
clause. Files of type IV are implemented as file objects in Predict, in order
to ensure the consistency of the field definitions via rippling.
The maximum number of master files for a view or file of type IV is 100 in Predict.
A common-table-expression used in the definition of a DB2 view or a materialized query table is documented by means of an intermediate view (file with file type IV). This intermediate view can then be referenced in the WITH clause of the subquery.
Assume the following example view definition in DB2:
CREATE VIEW SMR.FIRST_CTE_VIEW AS WITH DTOTAL (DEPTNO, TOTALPAY) AS (SELECT WORKDEPT, SUM(SALARY+BONUS) FROM DSN8810.EMP GROUP BY WORKDEPT) SELECT DEPTNO FROM DTOTAL WHERE TOTALPAY = (SELECT MAX(TOTALPAY) FROM DTOTAL)
The common table expression is documented as intermediate view named SMR-FIRST_CTE_IV_1.
The subquery editor of the DB2 view SMR.FIRST_CTE_VIEW looks like this:
WITH
SMR-FIRST_CTE_IV_1
FROM
SMR-FIRST_CTE_IV_1
WHERE SMR-FIRST_CTE_IV_1-TOTALPAY = (SELECT MAX(TOTALPAY) FROM
SMR-FIRST_CTE_IV_1)
And the subquery editor of SMR-FIRST_CTE_IV_1 looks like this:
FROM
DSN8810-EMP
GROUP BY DSN8810-EMP-WORKDEPT
The field TOTALPAY of file SMR-FIRST_CTE_IV_1 has field type DV (derived field) and has the expression:
SUM(DSN8810-EMP-SALARY+DSN8810-EMP-BONUS)
Note:
In DB2 common table expressions can be used to create recursive SQL.
Views based on such recursive definitions can not be incorporated or compared.
It is also not possible to document and generate such views.
DB2 supports so-called temporal tables. A temporal table is a table that records the period of time when a row is valid.
DB2 supports two types of periods, which are the system period (SYSTEM_TIME) and the application period (BUSINESS_TIME).
A system-period temporal table is a base table that is defined with
system-period data versioning. You can modify an existing table to become a
system-period temporal table by specifying the ADD PERIOD
SYSTEM_TIME clause on the ALTER
TABLE statement. After creating a history table that corresponds
to the system-period temporal table, you can define system-period data
versioning on the table by issuing the ALTER TABLE ADD
VERSIONING statement with the USE
HISTORY table clause.
An application-period temporal table is a base table that includes an
application period (BUSINESS_TIME). You can modify an existing table to become
an application-period temporal table by specifying the ADD
PERIOD BUSINESS_TIME clause on the ALTER
TABLE statement.
A bi-temporal table is a table that is both a system-period temporal table and an application-period temporal table. You can use a bi-temporal table to keep application period information and system-based historical information.
In Predict, a temporal table is documented as a file of type D using specific superfields. These fields are named either SYSTEM_TIME or BUSINESS_TIME, depending on the type of temporal table.
For system-period temporal tables it is required to specify a
field of type TS
to be used AS TRANSACTION START ID. This field specifies that a
timestamp value is assigned when the row is inserted or any column in the row
is updated. A table can have only one transaction-start-id column.
DB2 supports so-called archive tables. An archive table is a table that stores older rows from another table. The original table is called an archive-enabled table.
DB2 can automatically store rows that are deleted from an archive-enabled table in an associated archive table. When you query an archive-enabled table, you can specify whether you want those queries to include data from the archive table.
In Predict, an archive table is documented as a file of type D using specific attributes to link the archive table to a specific base table and also to define how the archive table is maintained.
When you create or alter a table, you can organize the table by hash to improve the performance queries that access individual rows.
When you create or alter tables on universal table spaces, you can
enable hash access to that table by adding the
organization-clause to your
CREATE TABLE or ALTER
TABLE statement.
In Predict you can document hash-organized tables with files of type D or MT. You have to specify a hash size that will be preallocated for an associated partition.
If hash size is specified it is also required to specify a field as hash key.
Refer to your DB2 documentation for further information on hash-organized tables.
DB2 supports so-called clone tables. A clone table is a table that has the exact same attributes as an already existing base table.
In Predict, a clone table is documented as a file of type D. Base and clone table are documented in Predict as separate files of type D which allows the creation of an individual DDM for each file.
Clone tables can be incorporated, compared and dropped using the Incorporate DB2 Table/View and Compare DB2 Table/View functions and purged using option P of the External File Administration function.
The Predict maintenance does not guarantee that the individual files of type D that represent the base and the clone table in Predict have identical field lists. The user has to make the required field list checks.
The Generate DB2 Table/View function does not allow the modification of field lists of existing cloned DB2 tables. This restriction also applies to the corresponding base table documented in Predict.
Predict does not create clone tables nor does Predict generate an
EXCHANGE DATA statement. Such statements have to be
submitted by the user from outside Predict (for example, using SYSDB2 or any
other tool that can issue interactive SQL statements).
Referential constraints are documented as file relations of type R (referential constraint). The relation is established between a unique constraint and a foreign key. Unique constraint and foreign key can belong to the same or to different tables.
DB2 application plans are documented as Predict system objects of type P.
With DB2 Version 2.3 or above, DBRMs can be grouped to packages. Packages are linked to plans dynamically (at run-time). DB2 packages are documented in Predict with program objects of with language B, C, F, H, P or Q or user-defined.
Packages are linked to application plans with objects of type packagelist (PG). The subtypes of the object type packagelist determine the type of inclusion of packages into an application plan when binding a plan. Valid values are:
Q
DBRM
T
total collection
S
subcollection
The different subtypes are described in the section Using Predict Information when Binding Application Plans.
Packages are referenced in a plan by collections. Any collection is a virtual summary of packages, used to simplify references to packages. Any package can be contained in several collections. Collections are documented as attributes of packagelists.
Trigger objects represent DB2 triggers.
You can link an unlimited number of triggers to a table. Update triggers that can only be executed if certain table fields are changed are then linked to these fields. The appropriate update-clauses are created during the table generation process. All triggers must be linked to file.
Using Incorporate and Compare on the tables creates the trigger objects in Predict and establishes the links to the file and to the fields. The connection to the DB2 catalog is not the Predict object ID, but an attribute Triggername. See Naming Conventions for DB2 for further information.
In addition, triggers contain information as to when they are executed (during Insert, Update or Delete) and whether they are to be executed before or after a certain statement is executed. The code that is to be executed is noted in the trigger body, which is a text attribute in Predict. The use of procedures in triggers is retrieved from the text of the trigger body.
If two or more triggers contained in a trigger list are assigned the same trigger action (for example Insert), trigger type (for example Before) and event (the code that is to be executed) then the trigger positioned first in the trigger list will be executed first. The following triggers will then be executed according to their position in the trigger list.
DB2 gives you the possibility to write procedures and user-defined functions. These objects can be documented as programs in Predict. The program type R (SQL procedure) can accept many specifications that are only related to DB2. Procedures can be implemented in third generation programming languages or SQL.
DB2 functions are documented as Programs of type U (Database function) in Predict. These functions can return a table as a result. There are two associations between PR and FI with the names "Input FI" and "Returns FI". The association names are supposed to indicate that the linked files represent the structure of the input parameter or the structure of the results table.
Only files of type IT (Intermediate Table) can be linked (see below). If the entered value or the results table is a scalar value, then you still must create a file that has exactly one field. In DB2, you can use table functions in the definition from views. Predict does not support this.
There is a new file type IT (Intermediate Table) for documenting the formal parameters of database functions. They do not exist in DB2. Their fields can, like tables, have a link to the standard file SAG-DISTINCT-TYPE, that is interpreted as being distinct type.
In Predict, distinct types are implemented with the help of an indicated standard file called SAG-DISTINCT-TYPE.
The connection with the DB2 catalog is established by the names of the fields of this standard file. The field names of SAG-DISTINCT-TYPE consist of a SCHEMA_NAME and a TYPE_NAME. See Naming Conventions for DB2 for further information. Table fields that are connected to a standard field in SAG-DISTINCT-TYPE have the predefined format which is the basis for the type. Changes in the type definition are spread via rippling to the derived fields.
Note:
The name of the standard field is not a valid column name. After
copying from SAG-DISTINCT-TYPE via the
SEL command into the field list of a table, the
field name must be changed so that it conforms with the SQL naming standards.
When a table is generated, a CREATE DISTINCT TYPE
statement is created for every type in the fields with distinct type, if the
type has not already been defined in the DB2 catalog. The type definition of
the table field is in this case the distinct type name.
When using the commands Incorporate and Compare on the table, the connections to the type definition are also compared. If the type definition in Predict is different from the one in DB2, then the field format of the table field is adapted to the catalog entry, and the field is marked as NON-Standard.
The type definitions are always regarded by the tables using it. Therefore, there are no explicit Generate, Incorporate and Compare distinct type functions. A type definition in DB2 is also deleted via Drop if the last table using it is dropped by Administration (Database, dataspace or file). Since every distinct type is based on a predefined type, the table fields derived from these types are represented in the DDM with the predefined type.
Arrays can be used as parameters or return values of functions. When generating such a function, the appropriate type definitions will also be generated.
Array types are documented as multiple value fields in file SAG-DISTINCT-TYPE. The occurrences can be used to define the dimension of the array. To use associative arrays that are indexed by values of a second data type, link a field of file SAG-DISTINCT-TYPE that serves as index type definition into association "Indexed by" to the array type field.
DB2 columns are documented as field objects.
LOBs are represented as the field format LO. The character set determines whether it is a BLOB, CLOB or a DBCLOB. The lengths of these fields can be declared in the following units: bytes, kilobytes, megabytes or gigabytes.
In order to create a connection between a row in the original table and a LOB value, DB2 uses so-called auxiliary tablespaces, auxiliary tables and an index to save the LOB values. Predict does not support the creation of these database objects.
Instead Predict uses the DB2 feature that automatically create these
auxiliary objects. This is achieved by generating a statement SET CURRENT
RULE='STD' whenever a table with LOB column is to be generated, provided
that the Special Register Current Rule does not already have this setting.
It is possible to provide an inline length integer value for BLOB and CLOB columns. It specifies the maximum number of bytes that are stored in the base table space for the column.
The inline length can be set in the Base Extensions screen available for fields of type D with format LO.
ROWID fields are documented with fieldtype QN. Their format is A and their length is 40. The field maintenance ensures that only one ROWID field exists per table. It also ensures that every table containing a LOB column also contains a ROWID column. This is necessary, so that DB2 can create the index to connect the row in the original table with the auxiliary table.
When deleting databases, tablespaces and tables, the auxiliary objects are deleted as well. It is possible to define an identity property for numeric fields. The contents of these fields can be generated by DB2. This is an easy way to created a primary key.
LOB fields are skipped during the DDM generation process, since NAT 3.x does not make any dynamic variables available. ROWID fields are only represented by A40 fields in the DDM.
A column mask in DB2 is used for column access control and specifies the value that should be returned for a specified column. Exactly one mask per column is allowed.
In DB2 an enabled column mask does not take effect until the ALTER TABLE statement with the ACTIVATE COLUMN ACCESS CONTROL clause is used to activate column access control for the table.
Column masks for DB2 fields are documented with Access definitions of type M.
If a DB2 table documented in Predict uses column masks and the table is dropped, the corresponding column masks are also deleted in Predict.
Multiple row permissions can be created for a table.
In DB2 an enabled row permission does not take effect until the ALTER TABLE statement with the ACTIVATE ROW ACCESS CONTROL clause is used to activate row access control for the table.
Row permissions for files of type D are documented with Access definitions of type P.
If a DB2 table documented in Predict uses row permissions and the table is dropped, the corresponding row permissions are also deleted in Predict.
DB2 indexes are documented with field objects as follows:
Field attributes
index name
definition of index
using- and free-block
If the index consists of only one column, the field documenting the column is marked as a descriptor with descriptor type D, P or F.
If the index consists of multiple columns, it is documented as a field with field type SP (superfield) and descriptor type D, P or F. The descriptor types have the following meaning:
D
Field is an index.
F
Field is a foreign key and an index.
P
Field is a primary key. This always implies that the field is also
a unique index.
Unique constraints are documented as follows:
If the unique constraint applies to only one column, the field documenting the column is marked U in column Unique option.
If the unique constraint applies to multiple columns, it is documented as a field of type SP (superfield) with descriptor type D, F or P, and U in column Unique option. The descriptor types have the following meaning:
D
Field is a unique index.
F
Field is a foreign key with unique index.
P
Field is a primary key. This always implies that the field has a
unique constraint.
Foreign keys are documented as follows:
If the foreign key consists of only one column, the field documenting the foreign key is marked as a descriptor with descriptor type F or E.
If the foreign key consists of multiple columns, it is documented as a field with field type SP (superfield) and descriptor type F or E. The descriptor type F means the field is a foreign key and an index. E means the field is a foreign key without an index.
Check expressions for single columns are documented with verifications of status SQL. The check expression is stored as the rule of the verification.
Check expressions can be edited with the Predict Rule Editor.
Comment lines (lines starting with * or **) and remarks within a line (starting with /*) are allowed.
A table check expression is a check expression that applies to more than one column. A table check expression is an attribute of a file.
To edit table check expressions, enter Y in the field Trigger of the corresponding file object.
Comment lines (lines starting with * or **) and remarks within a line (starting with /*) are allowed.
DB2 naming conventions must be observed when creating or maintaining Predict objects for DB2. The following rules apply:
Valid identifiers are from 2 to 32 characters long, must start with an alpha character (A - Z) and may be followed by either an alpha, US national character (#, $, @), a digit or underscore character. Identifiers must comply with these rules if Predict maintenance and generation functions are to be applied.
Concatenated names must not consist of more than 32 characters including the hyphen. See the following paragraphs for details.
Hyphens in names are treated as follows:
When a table/view is generated from a Predict file object, the hyphen will be transformed into a period (.).
Because hyphens are used as delimiters, only one hyphen can occur in the file ID.
The hyphen can be used as a minus sign or negative sign in the field expression or the subselect clause and must then be preceded by a blank.
Column names must not contain a hyphen.
Notes:
FLIP C and
FLIP T to edit the field names and their derivation
in full length.
Storagegroup names can consist of up to 18 characters.
Collection names can consist of up to 18 characters.
Contraint names can consist of up to 32 characters.
Correlation names can be defined in the subselect clause of a view. If a correlation name is defined for a table/view in the subselect clause, all references (in field expressions as well as in the field editor of the view) to columns of the table/view must be qualified with the correlation name. If no correlation name is defined for a table/view in the subselect clause, all references to columns of the table/view must be fully qualified with creator-tablename-columnname (for example: SYSIBM-SYSCOLUMNS-COLNAME).
Correlation names can consist of up to 18 characters.
Distinct types consist of SCHEMA_NAME and TYPE_NAME concatenated by a hyphen (used as qualification character).
SCHEMA_NAME and TYPE_NAME each can consist of up to 32 characters, but a concatenation of both (including the hyphen as qualification character) must not exceed a length of 32 characters.
Procedure names must be defined in unqualified form (a long SQL identifier). Procedure names can consist of up to 32 characters.
Index names consist of the creator name and the actual name of the index concatenated by a hyphen (used as qualification character).
Creator name and actual name of the index each can consist of up to 32 characters, but a concatenation of both (including the hyphen as qualification character) must not exceed a length of 32 characters.
Table/View names consist of the creator name and the actual name of the table/view concatenated by a hyphen (used as qualification character).
Creator name and actual name of the table/view each can consist of up to 32 characters, but a concatenation of both (including the hyphen as qualification character) must not exceed a length of 32 characters.
Function names must be defined in unqualified form (a long SQL identifier). Function names can consist of up to 32 characters.
Column names can consist of up to 30 characters.
Trigger names consist of the creator name and the actual name of the trigger concatenated by a hyphen (used as qualification character).
Creator name and actual name of the trigger each can consist of up to 32 characters, but a concatenation of both (including the hyphen as qualification character) must not exceed a length of 32 characters.
With DB2 or SQL/DS, special characters can be used in identifiers of tables and views. Identifiers that contain special characters have to be delimited (usually with single or double quotes) and are therefore called delimited identifiers.
DB2 or SQL/DS tables and views with delimited identifiers can be incorporated. They can then be renamed with Predict maintenance functions and retrieval functions can be applied to them. It is strongly advisable to rename delimited identifiers for the following reasons:
The only Predict functions that can be applied without restriction to objects with delimited identifiers are Incorporate and Rename.
If identifiers contain special characters such as blank or asterisk, results of retrieval functions are unpredictable.
Views can only be generated if the subselect clause and the column expressions do not contain references to delimited identifiers enclosed by quotation marks.
As the SQL escape character Predict uses quotation marks (") and as the SQL string delimiter apostrophes (') are used. The Predict Incorporate function converts other escape characters or string delimiters to quotation marks (") and apostrophes (').
Generation, Incorporation and Comparison are subject to DB2 security mechanisms. The following mechanisms apply for DB2 on z/OS:
To perform the Generate function and administration functions Purge and Refresh, the user must have the appropriate privileges within DB2 / SQL/DS.
To perform Incorporation and Comparison functions, the user must have SELECT privilege on nearly all catalog tables.
The SELECT privilege in DB2 is the minimum prerequisite for incorporation of DB2 tables/Views (see GRANT (TABLE or VIEW PRIVILEGES) in your DB2 documentation for the following tables:
SYSIBM-SYSCHECKDEP
SYSIBM-SYSCHECKS
SYSIBM-SYSCOLUMNS
SYSIBM-SYSDATABASE
SYSIBM-SYSDATATYPES
SYSIBM-SYSDUMMY1
SYSIBM-SYSFIELDS
SYSIBM-SYSFOREIGNKEYS
SYSIBM-SYSINDEXES
SYSIBM-SYSINDEXPART
SYSIBM-SYSKEYCOLUSE
SYSIBM-SYSKEYS
SYSIBM-SYSPLAN
SYSIBM-SYSPARMS
SYSIBM-SYSSTOGROUP
SYSIBM-SYSRELS
SYSIBM-SYSROUTINES
SYSIBM-SYSSEQUENCES
SYSIBM-SYSSEQUENCESDEP
SYSIBM-SYSTABCONST
SYSIBM-SYSTRIGGERS
SYSIBM-SYSSYNONYMS
SYSIBM-SYSTABLEPART
SYSIBM-SYSTABLES
SYSIBM-SYSTABLESPACE
SYSIBM-SYSVIEWDEP
SYSIBM-SYSVIEWS
SYSIBM-SYSVOLUMES
SYSIBM-SYSDATATYPES
SYSIBM-SYSTRIGGERS
To use the SQL statements generated by Predict, the corresponding DB2 privileges are also required.
To incorporate the SQL/DS tables contained in the following list, the SELECT privilege in SQL/DS is also a prerequisite:
SYSTEM-SYSCATALOG
SYSTEM-SYSCOLUMNS
SYSTEM-SYSDBSPACES
SYSTEM-SYSFIELDS
SYSTEM-SYSINDEXES
SYSTEM-SYSKEYCOLS
SYSTEM-SYSKEYS
SYSTEM-SYSSYNONYMS
SYSTEM-SYSUSAGE
SYSTEM-SYSVIEWS
The following mechanisms apply for DB2 on UNIX:
To perform the Generate function and administration functions Purge and Refresh, the user must have the appropriate privileges within DB2 / SQL/DS.
To perform Incorporation and Comparison functions, the user must have SELECT privilege on nearly all catalog tables.
The SELECT privilege in DB2 is the minimum prerequisite for incorporation of DB2 tables/Views (see GRANT (TABLE or VIEW PRIVILEGES) in your DB2 documentation for the following tables:
SYSCAT-BUFFERPOOLS
SYSCAT-CHECKS
SYSCAT-COLCHECKS
SYSCAT-COLUMNS
SYSCAT-CONSTDEP
SYSCAT-DATATYPES
SYSCAT-INDEXCOLUSE
SYSCAT-INDEXES
SYSCAT-KEYCOLUSE
SYSCAT-NODEGROUPDEF
SYSCAT-NODEGROUPS
SYSCAT-REFERENCES
SYSCAT-ROUTINEDEP
SYSCAT-ROUTINEPARMS
SYSCAT-ROUTINES
SYSCAT-SEQUENCES
SYSCAT-TABCONST
SYSCAT-TABLES
SYSCAT-TABLESPACES
SYSCAT-TRIGGERS
SYSCAT-VIEWDEP
SYSCAT-VIEWS
To use the SQL statements generated by Predict, the corresponding DB2 privileges are also required.
DB2 objects can be generated from Predict documentation objects.
| DB2 database |
The function is not available for SQL/DS. The function is not available for DB2 on UNIX. Command: |
| DB2 storagegroup | Command: GENERATE
STORAGEGROUP |
| DB2 database partition group | |
| DB2 tablespace | Command: GENERATE
TABLESPACE |
| SQL/DS Dbspace | |
| DB2/SQL/DS table/view | Columns, indexes, referential constraints, triggers and
distinct types are automatically included when generating DB2 tables and views.
Command: |
All objects are generated by first generating the SQL statements that are necessary to implement the object and then executing these statements. An additional confirmation is requested before a DB2 object is actually implemented.
The generated SQL statements can be saved in a protocol.
If a generation function is executed for an object that is already implemented, the existing DB2 object can be updated.
Tables with LOB column: Statement SET CURRENT RULE='STD'
is created when a table with LOB column is generated.
Auxiliary tablespaces, tables and indexes are created automatically by DB2.
See respective sections in Generation in the External Objects in Predict documentation for more information.
The incorporation functions create Predict documentation objects for databases (not for SQL/DS), tablespaces, storagegroups, tables/views, (including columns, indexes and referential constraints) from the system catalog.
See the section Incorporation in the External Objects in Predict documentation.
The comparison functions list differences between the current implementation in DB2 / SQL/DS and the corresponding documentation. The documentation can be updated to match the implementation.
See the section Comparison in the External Objects in Predict documentation.
Functions for administrating DB2 objects are provided to display, purge or refresh DB2 objects that have been implemented from Predict documentation.
The Display function lists generation protocols.
The Purge function drops a table/view physically in DB2 or SQL/DS. If a table holds the last reference to a distinct type, the distinct type is also dropped.
The Refresh function deletes all data in an implemented table but keeps the table structure.
See the section Administration of External Objects in the External Objects in Predict documentation.
With the Natural for DB2 utilities SYSDB2 (for DB2) and SYSSQL (for SQL/DS) storagegroups, databases, tablespaces/Dbspaces, tables/views and indexes can be created or modified. To avoid undocumented changes to DB2 or SQL/DS concerning these object types, your data dictionary administrator (DDA) may have set the parameter SYSDB2 utility in the Defaults > General Defaults > Protection screen.
| A | Allowed: all SYSDB2 functions can be executed. |
| D | Disallowed: the following SYSDB2 functions cannot be executed:
|
| I | Incorporate: all SYSDB2 functions can be executed outside of
Predict. If one of the following statements is submitted to DB2, an automatic
incorporation in Predict is performed:
|