Chapter 3 DBMS Reference Guide

Defining physical options

In some DBMS definitions, Option entries are used to define a tree view of Physical options in the left pane of an object property sheet. A physical option is a parameter that defines how an object is optimized or stored in a database. Physical options can be included at the end of a Create statement and are DBMS specific.

A typical tree view is shown below:

During generation, the options selected in the model for each object are stored in a variable %OPTIONS%.

The %OPTIONS% variable contains the physical option string as if it was written in a SQL script. This string must appear at the end of the item it belongs to and must not be followed by anything else.

Example

create table
[%OPTIONS%]

is correct syntax.

Variables in physical options

You can use the PowerDesigner variables defined for a given object to set physical options for this object.

For example, in Oracle, you can set the following variable for a cluster if you want the cluster to take the same name as the table.

Cluster %TABLE%

For a list of all the variables used in PowerDesigner, see section "PDM variables".

Extended Attributes

You can use extended attributes in physical options.

For more information about extended attributes, see section "Profile category".

During reverse engineering by script, the section of the SQL query determined as being the physical options is stored in %OPTIONS%, and will then be parsed when required by an object property sheet.

During ODBC reverse engineering, the SqlOptsQuery SQL statement is executed to retrieve the physical options which is stored in %OPTIONS% to be parsed when required by an object property sheet.

Depending on the DBMS definition, you can define physical options for the following objects:

Tablespace
Storage
Database
Table
Column
Index
Key (primary and alternate)

Typical Physical options are pctfree , pctused , fillfactor , and partition .

You define physical options from the property sheet of an object.