DB2 and SQL/DS

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:


Documenting DB2 in Predict

The following topics are covered below:

General Information

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.

Documentation of multiple server

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.

Generation, Administration, Incorporation and Comparison

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.

Support of DB2 in Natural for Windows and Natural for UNIX

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.

Documenting DB2 Storagegroups or Database partition groups

Storagegroups or Database partition groups are documented as objects of type storagespace.

Documenting DB2 Databases

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.

Documenting DB2 Tablespaces and SQL/DS Dbspaces

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:

  1. DB2 tablespaces on z/OS need only be documented with Predict dataspace objects if you intend to generate the DB2 tablespace from the Predict dataspace object. If you use the option to create the DB2 tablespace implicitly when generating tables/views, the tablespace need not be documented with a Predict dataspace object. Partitioned or segmented tablespaces are not created implicitly.
  2. DB2 tablespaces in a UNIX environment need only be documented with Predict dataspace objects if you intend to generate the DB2 tablespace from the Predict dataspace object. If you use the option to use IBMDEFAULTGROUP when generating tables/views, the tablespace need not be documented with a Predict dataspace object.

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.

Documenting DB2 Tables and Views

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

Subselect Clauses and Expressions in Field Definitions

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.

Common Table Expression

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.

Temporal Tables

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.

Archive-enabled and Archive Tables

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.

Hash key

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.

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

Documenting Referential Constraints

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.

Documenting DB2 Application Plans

DB2 application plans are documented as Predict system objects of type P.

Documenting DB2 Packages

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.

Linking Packages to Application Plans with Packagelists

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.

Documenting DB2 Triggers

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.

Documenting DB2 Procedures and Functions

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.

Files of Type IT

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.

Documenting Other DB2 Objects

DB2 Distinct Types

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

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

DB2 columns are documented as field objects.

Columns With Format LOB

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.

Columns With Format ROWID

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.

Column Masks

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.

Row Permissions

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

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

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

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.

Column Check Expressions

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.

Table Check Expression

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.

Naming Conventions for DB2

DB2 naming conventions must be observed when creating or maintaining Predict objects for DB2. The following rules apply:

General Rules

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:

  1. Incorporation functions accept object and creator names as selection criteria according to the restrictions mentioned here and in the following sections. DB2 objects with a name longer than the described ones are not offered for incorporation.
  2. Names that apply to the restrictions mentioned here and in the following sections but can not be shown in full length are indicated by ">" as last character.
  3. For views and intermediate tables use the editor commands FLIP C and FLIP T to edit the field names and their derivation in full length.

Storagegroup Name

Storagegroup names can consist of up to 18 characters.

Collection Name

Collection names can consist of up to 18 characters.

Constraint Name

Contraint names can consist of up to 32 characters.

Correlation Name

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 Type

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 Name

Procedure names must be defined in unqualified form (a long SQL identifier). Procedure names can consist of up to 32 characters.

Index Name

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 Name

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 Name

Function names must be defined in unqualified form (a long SQL identifier). Function names can consist of up to 32 characters.

Column Name

Column names can consist of up to 30 characters.

Trigger Name

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.

Delimited Identifier

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 (').

Generating, Incorporating and Comparing DB2 Objects

Prerequisites

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.

Generation

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: GENERATE DB2-DATABASE

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: GENERATE TABLE

Rules Applying when Generating DB2 / SQL/DS Objects

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

Incorporation

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.

Comparison

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.

Administrating Implemented DB2 Objects

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.

Locking the Functions of the DB2 Utilities SYSDB2 and SYSSQL

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:

CREATE DATABASE
CREATE STORAGEGROUP
CREATE TABLE
CREATE TABLESPACE
CREATE VIEW
CREATE INDEX

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:

CREATE DATABASE
CREATE STORAGEGROUP
CREATE TABLE
CREATE TABLESPACE
CREATE VIEW