Physical Parameters
Colors used:
Gray: parameters where reverse engineering is non-directional.
Red: parameters not processed.
Green: parameters reverse engineered.
Tables
• Clause type : table_check
o CHECK NAME (String)
o ENFORCED(Enumeration: ENFORCED,NOT ENFORCED,NOT ENFORCED TRUSTED, NOT ENFORCED NOT TRUSTED
• Clause type : table_block
o DATA CAPTURE (Enumeration: CHANGES,NONE)
o NOT LOGGED INITIALLY(Boolean)
o WITH RESTRICT ON DROP
o COMPRESSION MODE (Enumeration: COMPRESSION NO,COMPRESSION YES ADAPTIVE,COMPRESSION YES STATIC)
o VALUE COMPRESSION (Boolean)
o CCSID (UNICODE,ASCII)
o SECURITY POLICY(String)
o OPTIONS (VarChar)
o AS ROW (Enumeration : BEGIN,END)
• Clause type : organize_clause
o ORGANIZE BY (Enumeration :ROW,COLUMN,DIMENSIONS,KEY SEQUENCE,INSERT TIME)
o ROW USING (Boolean)
o ALLOW OVERFLOW(Boolean)
o PCTFREE (Integer)
o Not processed:
o
• Clause type : organize_sequence_key_clause
o COLUMN (Link)
o STARTING_CONSTANT
o ENDING_CONSTANT
• Clause type : distribution_clause
o DISTRIBUTE BY (Enumeration :HASH,REPLICATION)
o DISTRIBUTION COLUMNS(Link)
• Clause type : partitioning_clause
o RANGE(Boolean)
• Clause type : partitioning_range_clause
o PARTITION NAME(String)
o EVERY(String)
o Used Data Group(Link)
• Clause type: boundary_clause
o BOUNDARY INCLUSIVE(INCLUSIVE,EXCLUSIVE)
o BOUNDARY TYPE(STARTING,ENDING)
o BOUNDARY VALUES(String)
• Clause type: tablespace_option
o CYCLE(Boolean)
o Used Data Group (link)
o Used Data Group 2 (link)
o Used Data Group 3 (link)
• Clause type : partition_expression
o COLUMN(Link)
o NULLS ORDER (Enumeration : FIRST,LAST)
• Clause type : table_period_defintion
o PERIOD MODE (Enumeration : BUSINESS_TIME,SYSTEM_TIME)
o BEGIN COLUMN(Link)
o END COLUMN(Link)
Columns
• Clause type : column_block
o COMPACT (Boolean)
o COMPRESS (Boolean)
o INLINE LENGTH (Boolean)
o LOGGED (String)
o IMPLICITLY HIDDEN (Enumeration : IMPLICITLY HIDDEN, NOT HIDDEN )
• Clause type : generated_column_spec
o GENERATED (Enumeration : GENERATED ALWAYS, GENERATED DEFAULT)
o GENERATION TYPE (Enumeration : AS GENERATED EXPRESSION,AS ROW CHANGE TIMESTAMP, AS ROW TRANSACTION TIMESTAMP, AS ROW TRANSACTION STARTD ID)
o START WITH (String)
o INCREMENT (String)
o MAX VALUE (OPENED ENUMERATION : NO MAXVALUE)
o MIN VALUE (OPENED ENUMERATION : NO MINVALUE)
o CACHE (OPENED ENUMERATION : NO CACHE)
o CYCLE (Boolean)
o ORDER (Boolean)
o GENERATE EXPRESSION (VarChar)
Indexes
• Clause type : index_block
o BUSINESS_TIME WITHOUT OVERLAPS (Boolean)
o PARTIONED (Enumeration : PARTITIONED ,NOT PARTITIONED)
o Used Data Group (Link)
o EXTEND USING (String)
o VALUE LIST (VarChar)
o PCTFREE (Percent)
o LEVEL2 PCTFREE (Percent)
o MINPCTUSED (String)
o REVERSE SCANS (ALLOW,DISALLOW)
o PAGE SPLIT (HIGHT,LOW,SYMMETRIC)
o COLLECT(STATISTICS,DETAILED STATISTICS,SAMPLED DETAILED STATISTICS,UNSAMPLED DETAILED STATISTICS)
o COMPRESS(YES,NO)
o NULL KEYS (INCLUDE NULL KEYS, EXCLUDE NULL KEYS)
o INCLUDE(Link)
Tablespaces
• Clause type : tablespace_block
o STORE(LARGE,LOB,LONG,REGULAR,SYSTEM TEMPORARY,USER TEMPORARY)
o NODEGROUP(Boolean)
o NODEGROUP NAME(String)
o PAGESIZE(String)
o MANAGED(MANAGE BY AUTOMATIC STORAGE,MANAGED BY DATABASE,MANAGED BY SYSTEM)
o EXTENTSIZE(String)
o PREFETCHSIZE(String)
o BUFFERPOOL(String)
o OVERHEAD(String)
o TRANSFERRATE(String)
o DROPPED TABLE RECOVERY(ON,OFF)
o AUTORESIZE(Boolean)
o INCREASESIZE(String)
o INITIALSIZE(String)
o MAXSIZE(String)
o FILE SYSTEM CACHING (FILE SYSTEM CACHING
o NO FILE SYSTEM CACHING (NO FILE SYSTEM CACHING,NO NO FILE SYSTEM CACHING )
o DATA TAG (String)
• Clause type : containers
o Container-type(FILE,DEVICE)
o Container-strings(VarChar)
o Container –size (String)
o container-numbers(String)
Unmanaged physical parameters
• AS
Specifies that the columns of the table are based on the attributes of the structured type identified by type-name1. If type-name1 is specified without a schema name, the type name is resolved by searching the schemas on the SQLpath (defined by the FUNCPATH preprocessing option for static SQL and by the CURRENT PATH register for dynamic SQL). The type name must be the name of an existing user-defined type (SQLSTATE 42704) and it must be an instantiable structured type (SQLSTATE 428DP) with at least one attribute (SQLSTATE 42997).
• LIKE
Specifies that the columns of the table have exactly the same name and description as the columns of the identified table (table-name1), view (view-name) or nickname (nickname). The name specified after LIKE must identify a table, view or nickname that exists in the catalog, or a declared temporary table. A typed table or typed view cannot be specified (SQLSTATE428EC).
• TYPE_NAME
Creates a typed table, which takes its structure from the specified composite type (name optionally schema-qualified). A typed table is tied to its type; for example the table will be dropped if the type is dropped (with DROP TYPE ... CASCADE). When a typed table is created, then the data types of the columns are determined by the underlying composite type and are not specified by the CREATE TABLE command. But the CREATE TABLE command can add defaults and constraints to the table and can specify storage parameters.
UNIQUE Constraint
Creating a UNIQUE constraint automatically creates a unique index of the same name. It is therefore recommended not to generate unique indexes.
In the generation options of a database, select one of the two options “Create Index” and “Create Index[Unique]”.
The same rule applies on PK constraints.
Organize_sequence_key-clause Clause
The “organize_sequence_key-clause” clause is reverse engineered into a check constraint.
For example:
Became:
A Unique index is also created on columns used in “organize_sequence_keyclause” clauses.
In DB2V10.5 reverse engineering of this clause is not supported.
‘EVERY \ DB2' Properties
The “EVERY” property has no physical value in DB2 system tables. If this property is declared on a partition, it means that the partition is composed of several partitions.
For example:
CREATE TABLE PARTITION_range_4
(
ID INTEGER,
CONTENTS CLOB
)
PARTITION BY RANGE (ID) (STARTING 1 ENDING 100, STARTING 101 ENDING 400 EVERY 100)
Is equal to:
CREATE TABLE PARTITION_range_4
(
ID INTEGER,
CONTENTS CLOB
)
PARTITION BY RANGE (ID) (STARTING 1 ENDING 100, STARTING 101 ENDING 200, STARTING 201 ENDING 300, STARTING 301 ENDING 400)