IBM DB2 for Common Server

To create a PDM with support for features specific to the IBM DB2 for Common Server DBMS family, select the appropriate version in the DBMS field of the New Model dialog. To view these extensions to the PowerDesigner metamodel in the Resource Editor, select Database > Edit Current DBMS and expand the Profile node.

Note: The DBMSs for IBM DB2 v5-7 are deprecated.

For a list of DB2 objects and their equivalents in PowerDesigner, see IBM DB2 for z/OS (formerly OS/390).

The following sections list the extensions provided for DB2 for Common Server.

Note: We do not provide documentation for the properties on the Physical Options and certain other tabs, though minimal information is available for them in the Resource Editor. For information about these properties, consult your DBMS reference documentation.

Tables

The following extensions are available on the DB2 tab:

Name

Description

Ptcfree

Indicates what percentage of each tab to leave as free space during load or reorganization.

Scripting name: ExtTablePctFree

Data

Identifies the tablespace in which the table will be created.

Scripting name: In

Cycle

Specifies whether or not the number of data partitions with no explicit tablespace can exceed the number of specified data partitions.

Scripting name: DisplayCycle

Long

Identifies the table space in which the values of any long columns (LONG VARCHAR, LONG VARGRAPHIC, LOB data types, distinct types with any of these as source types, or any columns defined with user-defined structured types with values that cannot be stored inline) will be stored.

Scripting name: InLongIn

Index

Identifies the tablespace in which any indexes on the table will be created.

Scripting name: InIndexIn

Columns

The following extensions are available on the DB2 tab:

Name

Description

Lob option

[up to v8.x] Specifies options for LOB data type columns.

Scripting name: ExtLobOption

For bit data

Specifies that the content of the column is to be treated as bit (binary) data. This is only applicable on columns with a character datatype.

Scripting name: ExtForBitData

Always Generate value

When set to True (generated always), indicates that DB2 will always generate a value for the column when a row is inserted into the table or whenever the result value of the generation expression may change.

When set to False (generated by default), indicates that DB2 will generate a value for the column when a row is inserted into the table, unless a value is specified.

Scripting name: ExtGenAlways

As row change timestamp

[v9.5 and higher] Specifies that the column is a timestamp column for the table. A value is generated for the column in each row that is inserted, and for any row in which any column is updated.

Scripting name: AsRowChangeTimestampClause

Expression

Specifies that the definition of the column is based on an expression.

Scripting name: ExtGenExpr (up to v9.0: ExtGenExpr)

Compact

Specifies COMPACT options for LOB data type columns.

Scripting name: Compact

Logged

Specifies LOGGED options for LOB data type columns.

Scripting name: Logged

Inline length

This option is only valid for a column defined using a structured type and indicates the maximum byte size of an instance of a structured type to store inline with the rest of the values in the row.

Scripting name: InlineLength

Compress

Specifies that system default values (that is, the default values used for the data types when no specific values are specified) are to be stored using minimal space. If the VALUE COMPRESSION clause is not specified, a warning is returned and system default values are not stored using minimal space.

Scripting name: CompressSystemDefault

Hidden

Specifies whether or not the column is to be defined as hidden. The hidden attribute determines whether the column is included in an implicit reference to the table, or whether it can be explicitly referenced in SQL statements.

Scripting name: HiddenBool

Security label

Identifies a security label that exists for the security policy that is associated with the table.

Scripting name: SecurityLabel

References

The following extensions are available on the DB2 tab (v8.0 and higher):

Name

Description

Enforced

Indicates whether or not the referential constraint is enforced by the database manager during normal operations, such as insert, update, or delete.

Scripting name: Enforced

Enable query optimization

Specifies whether the constraint can be used for query optimization under appropriate circumstances.

Scripting name: QueryOptimization

Views

The following extensions are available on the DB2 tab (v9.x and higher):

Name

Description

View is based on a type

Specifies that the columns of the view are based on the attributes of the structured type identified by type-name.

Scripting name: ADTView

Structured type

Specifies the abstract data type that the view is based on.

Scripting name: ViewType

Super view

Specifies the view that the current view is a subview of. The superview must be an existing view and must be defined using a structured type that is the immediate supertype of the current view type.

Scripting name: SuperView

Identifier column

Defines the object identifier column for the typed view.

Scripting name: OIDColumn

Unchecked

Defines the object identifier column of the typed view definition to assume uniqueness even though the system cannot prove this uniqueness.

Scripting name: Unchecked

Additional options

Defines additional options that apply to columns of a typed view.

Scripting name: RootViewOptions

With row movement

Specifies that an updated row is to be moved to the appropriate underlying table, even if it violates a check constraint on that table.

Scripting name: WithRowMovement

Check option

Specifies the constraint that every row that is inserted or updated through the view must conform to the definition of the view.

Scripting name: CheckOption

Tablespaces

The following extensions are available on the DB2 tab:

Name

Description

Type

Specifies the tablespace type, as defined in the extended attribute type ExtTablespaceTypeList.

Scripting name: ExtTablespaceType

Abstract Data Types

The following extensions are available on the DB2 tab (v9.x and higher):

Name

Description

Inline length

Indicates the maximum size (in bytes) of a structured type column instance to store inline with the rest of the values in the row of a table. Instances of a structured type or its subtypes, that are larger than the specified inline length, are stored separately from the base table row, similar to the way that LOB values are handled.

Scripting name: InlineLength

Without comparison

Indicates that there are no comparison functions supported for instances of the structured type.

Scripting name: WithoutComparison

Cast (ref as source) function

Defines the name of the system-generated function that casts a reference type value for this structured type to the data type representation type. A schema name must not be specified as part of function name (SQLSTATE 42601). The cast function is created in the same schema as the structured type. If the clause is not specified, the default value for function name is the name of the representation type.

Scripting name: RefAsSourceCastFunction

Cast (source as ref) function

Defines the name of the system-generated function that casts a value with the data type representation type to the reference type of this structured type. A schema name must not be specified as part of the function name (SQLSTATE 42601). The cast function is created in the same schema as the structured type. If the clause is not specified, the default value for function name is the structured type name. A matching function signature must not already exist in the same schema (SQLSTATE 42710).

Scripting name: SourceAsRefCastFunction

With function access

Indicates that all methods of this type and its subtypes, including methods created in the future, can be accessed using functional notation. This clause can be specified only for the root type of a structured type hierarchy (the UNDER clause is not specified) (SQLSTATE 42613). This clause is provided to allow the use of functional notation for those applications that prefer this form of notation over method invocation notation.

Scripting name: WithFunctionAccess

Ref using

Defines the built-in data type used as the representation (underlying data type) for the reference type of this structured type and all its subtypes. This clause can only be specified for the root type of a structured type hierarchy (UNDER clause is not specified) (SQLSTATE 42613). The type cannot be a LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DBCLOB, DATALINK, or structured type, and must have a length less than or equal to 32 672 bytes (SQLSTATE 42613). If this clause is not specified for the root type of a structured type hierarchy, then REF USING VARCHAR(16) FOR BIT DATA is assumed.

Scripting name: RepType

Length/ precision

Specifies the precision for representation type.

Scripting name: RepPrecision

Abstract Data Type Attributes

The following extensions are available on the DB2 tab (v9.x and higher) with the LOB data type:

Name

Description

Compact

Specifies COMPACT options for LOB data type columns.

Scripting name: Compact

Logged

Specifies LOGGED options for LOB data type columns.

Scripting name: Logged

Abstract Data Type Procedures

The following extensions are available on the DB2 tab (v9.x and higher):

Name

Description

Inherit isolation level

Specifies whether or not a lock request can be associated with the isolation-clause of the statement when the method inherits the isolation level of the statement that invokes the method. The default is INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST.

Scripting name: IsolationLevel

Method is external

Indicates that the CREATE METHOD statement is being used to register a method, based on code written in an external programming language.

Scripting name: ExternalMethod

External name

Identifies the name of the user-written code which implements the method being defined.

Scripting name: ExternalName

Transform group

Indicates the transform group that is used for user-defined structured type transformations when invoking the method. A transform is required since the method definition includes a user-defined structured type.

Scripting name: TransformGroup