3985F3AF-2ABA-4C25-BCAA-80B34C5A2857 Mimer82 Mimer82 0 986818479 pof General Target DBMS identification Product Product name. Ensures that the DBMS definition is for the product in use PowerDesigner Version Version number 8.2 DbmsFamily Mimer version 8 Mimer SqlSupport SQL syntax allowed. This does not affect script generation but afftect the SQL Preview 1 EnableCheck Determines if the generation of check parameters is authorized or not 1 Enableconstname Determines if constraint names are used during the generation 1 UniqConstName Determines if unique constraint names for objects are authorized or not Script DBMS characteristics, command definition, and data type translations for the Script generation and reverse engineering Sql Contains sub-categories Syntax, Format, File and Keywords. Each sub-category contains entries whose values define general syntax for the database Syntax Contains general parameters for SQL syntax Terminator End of command character ; BlockTerminator End of block character / Delimiter Field separation character. Example: col1, col2, col3 , Quote Character used to enclose string values ' SqlContinue Continuation character LineComment Characters used to enclose a single line commentary -- BlockComment Characters used to enclose a multi-line commentary Format Contains entries that define script formatting IllegalChar Invalid characters for names " +-*/!=<>'"()". UpperCaseOnly Uppercase only LowerCaseOnly Lowercase only EnableOwnerPrefix Object codes can have a prefix made of the object owner code 1 File Contains header, footer and usage text entries used during the generation Header Header text for a database generation script Footer Footer text for a database generation script EnableMultiFile Multi-script allowed ScriptExt Main script extension in database generation sql TableExt Other scripts extension in database generation tab StartCommand Command for executing a script Usage1 (1) Go to the directory %PATHSCRIPT% (2) Start the SQL interpreter (3) Run the script "%NAMESCRIPT%" Usage for a single script in database generation Usage2 (1) Go to the directory %PATHSCRIPT% (2) Start the SQL interpreter (3) Run the generated scripts Usage for multiple scripts in database generation TriggerExt Script file extension in triggers and procedures generation trg TrgUsage1 (1) Go to the directory %PATHSCRIPT% (2) Start SQL interpreter: sqlplus <username>/<password> (3) Execute the trigger creation script: start %NAMESCRIPT% Usage for a single script in triggers and procedures generation TrgUsage2 (1) Go to the directory %PATHSCRIPT% (2) Start SQL interpreter: sqlplus <username>/<password> (3) Execute the triggers creation scripts: start <script_name> Usage for multiple scripts in triggers and procedures generation Keywords Contains the list of reserved words and functions available in SQL GroupFunc avg() count() max() min() sum() List of SQL functions to use with group keywords. NumberFunc List of SQL functions used on numbers CharFunc char_length() substring() List of SQL functions for characters and strings DateFunc current_date current_time current_timestamp dayofweek() dayofyear() hour() localtime() localtimestamp() microsecond() minute() month() posstr() second() time() week() year() List of SQL functions for dates ConvertFunc decimal() digits() double() float() hex() integer() timestamp() vargraphic() List of SQL functions used to convert values between hex and integer and handling strings OtherFunc coalesce() event_mon_state() generate_unique() nullif() raise_error() table_name() table_schema() user List of other SQL functions ListOperators = <> > < >= <= and or not in not in between not between exists not exists like not like is null is not null overlaps = any <> any > any < any >= any <= any = all <> all > all < all >= all <= all List of operators for comparing values, boolean, and various semantic operators Commit commit Command for validating the transaction by OBDC ReservedDefault NULL USER CURRENT DATE CURRENT DEGREE CURRENT PACKAGESET CURRENT RULES CURRENT SERVER CURRENT SQLID CURRENT TIME CURRENT TIMESTAMP CURRENT TIMEZONE Reserved default values Objects Contains sub-categories for each type of object in the database, for example: Table, or Reference. Each sub-category contains entries whose values define database commands and object-related characteristics Table TABL The following system variables are available: "TABLE" // generated code of the table "TNAME" // name of the table "TCODE" // code of the table "TLABL" // comment of the table "PKEYCOLUMNS" // list of primary key columns. Ex: A, B "TABLDEFN" // complete body of the table definition. Contains definition of columns, checks and keys Enable Table allowed 1 Maxlen Maximum object name length 128 CKTInTable Check on table generated in table 1 ConstName T_%.6:TABLE% Constraint name template for check of table Create [%CLASS%?create table [%QUALIFIER%]%TABLE% of [%QUALIFIER%]%CLASS% ( REF IS Oid USER GENERATED[%TABLDEFN% ?,] %TABLDEFN% ) [%OPTIONS%]:create [%ExtSummary%?summary ]table [%QUALIFIER%]%TABLE% ( %TABLDEFN% ) [%OPTIONS%]] Command for creating a table. Example: create table %TABLE% Options like %s : composite=yes { <inclusion> %s : list=including|excluding, default=including <object> %s : list=identity column attributes|column defaults|defaults, default=defaults } data capture %s : list= none | changes, default=none in %s : category=tablespace, composite=yes { index in %s : category=tablespace long in %s : category=tablespace } partitioning key : composite=yes, parenthesis=yes { <list> : composite=yes, separator=yes, multiple=yes { <column> %s } using hashing } replicated not logged initially Available options for creating a table DefOptions Default values for table options TableComment comment on table [%QUALIFIER%]%TABLE% is %.q:COMMENT% Command for adding a table comment Drop drop table [%QUALIFIER%]%TABLE% Command for dropping a table. Example: drop table %TABLE% Rename rename table [%QUALIFIER%]%OLDTABL% to %NEWTABL% Command for renaming a table AlterTableHeader Alter table header AlterTableFooter Alter table footer DefineTableCheck [constraint %CONSTNAME%] check (%CONSTRAINT%) Allows to customize the script of table check constraints AddTableCheck alter table [%QUALIFIER%]%TABLE% add [constraint %CONSTNAME%] check (%CONSTRAINT%) Allows to customize the script for modifying table constraints within an alter table statement AllowedADT STRUCTURED List of ADT on which a table can be based 1 SqlOptsQuery {USER, TABLE} select DATABANK_NAME from information_schema.ext_table_databank_usage where table_schema='%USER%' and table_name='%TABLE%' SQL query to reverse object physical options Index INDX The following system variables are available: (parent table items are also available for indexes) "INDEX" // generated code of the index "INDEXNAME" // index name "INDEXCODE" // index code "UNIQUE" // keyword "unique" when the index is unique "INDEXTYPE" // index type (available only for a few DBMS) "INDEXKEY" // keywords "primary", "unique" or "foreign" depending on the index origin "CIDXLIST" // list of index columns. Ex: A asc, B desc, C asc "CLUSTER" // keyword "cluster" when the index is cluster For index columns, the following system variables are available: "ASC" // keywords "ASC" or "DESC" depending on sort order "ISASC" // TRUE if the index column sort is ascending Maxlen Index name length 128 MaxColIndex Maximum number of columns in an index 16 Enable Index allowed 1 EnableAscDesc ASC, DESC keywords allowed 1 UniqName Unique index name in the database 1 Create create [%UNIQUE%] index [%QUALIFIER%]%INDEX% on [%QUALIFIER%]%TABLE% ( %CIDXLIST% ) [%OPTIONS%] Command for creating an index. Example: create index %INDEX% AddColIndex %20:COLUMN% [%ASC%] Command for defining an index column Options specification only include : composite=yes, parenthesis=yes, separator=yes { <columns> %s : multiple=yes } cluster extend using %s : composite=yes, parenthesis=yes, separator=yes { <expression> : %s : multiple=yes } pctfree %d : default=10 minpctused %d <reverse_scans> %s : list=disallow reverse scans |allow reverse scans Default options for creating an index DefOptions Default values for index options SqlSysIndexQuery {OWNER,TABLE,INDEX} select table_schema, table_name, constraint_name from information_schema.key_column_usage where table_schema='%OWNER%' and table_name='%TABLE%' SQL query to list system indexes created by the database Drop drop index [%QUALIFIER%]%INDEX% Command for dropping an index. Example: drop index %INDEX% SqlListQuery SQL query to list objects SqlAttrQuery SQL query to reverse object attributes DefineIndexColumn Defines the column of an index Column COLN The following system variables are available: (parent table items are also available for columns) "COLUMN" // generated code of the column "COLNNO" // position of the column in the list of columns of the table "COLNNAME" // name of the column "COLNCODE" // code of the column "PRIMARY" // keyword "primary" if the column is primary "ISPKEY" // TRUE if the column is part of the primary key "FOREIGN" // TRUE if the column is part of one foreign key Enable Column allowed 1 Maxlen Column name length 128 EnableNull NULL keyword allowed EnableNotNullWithDflt Enables not null with default 1 EnableDefault Default values allowed 1 ConstName C_%.6:COLUMN% Constraint name template for a column check parameter Add [%CLASS% ? [%NOTNULL% ? %20:COLUMN% WITH OPTIONS [%NOTNULL%] :[%CONSTRAINT% ? %20:COLUMN% WITH OPTIONS]] :%20:COLUMN% %22:DATATYPE%[%ExtForBitData%? for bit data][ %NOTNULL%][%ExtNotLogged%? not logged][%ExtCompact%? compact] [%IDENTITY%?generated [%ExtGenAlways%?always:by default] as %IDENTITY%:[%ExtGenExpr%?generated [%ExtGenAlways%?always:by default] as %ExtGenExpr%:[%WITHDEFAULT%?with default %DEFAULT%:[default %DEFAULT%]]]]] [[constraint %CONSTNAME%] check (%CONSTRAINT%)] [%OPTIONS%] Command for defining a table column ColumnComment comment on column [%QUALIFIER%]%TABLE%.%COLUMN% is %.q:COMMENT% Command for adding a column comment Rename Command for renaming a column Create Command for adding a column ModifyColumn Command for modifying a column Drop Command for dropping a column EnableIdentity Identity keyword entry support. Identity columns are serial counters maintains by the database 1 Options <logged> %s : list=logged|not logged, default=logged <compact> %s : list=compact|not compact, default=not compact linktype url : composite=yes { no link control file link control : composite=yes { mode db2options integrity %s : default=all read permission %s : list=fs|db, default=fs write permission %s : list=fs|blocked, default=fs recovery %s : list=yes|no, default=no on unlink %s : list=restore|delete, default=restore } } Available options for creating a column SqlAttrQuery {OWNER, TABLE, COLUMN, ExtForBitData} select c.table_schema, c.table_name, c.column_name, '' from information_schema.columns c where 1=1 [ and c.table_schema='%OWNER%'] [ and c.table_name='%TABLE%'] [ and c.column_name='%COLUMN%'] SQL query to reverse object attributes SqlListQuery {OWNER, TABLE, COLUMN, DTTPCODE, LENGTH, PREC, NOTNULL (N='NOT NULL', *=NULL), WITHDEFAULT, DEFAULT, @OBJTLABL, NO, IDENTITY, ExtGenAlways, ExtGenExpr} select table_schema, table_name, column_name, data_type, character_maximum_length, numeric_scale, column_default, ordinal_position from information_schema.columns where 1=1 [and table_schema='%OWNER%'] [and table_name='%TABLE%'] order by 1, 2, ordinal_position SQL query to list objects SqlOptsQuery {OWNER, TABLE, COLUMN, OPTIONS} select c.table_schema, c.table_name, c.column_name from information_schema.columns c where 1=1 [ and c.table_schema='%OWNER%'] [ and c.table_name='%TABLE%'] [ and c.column_name='%COLUMN%'] SQL query to reverse column physical options User USER The following system variables are available: "USER" // generated code of the user Enable User allowed 1 Maxlen User name length 128 SqlListQuery {USER} select distinct grantee from INFORMATION_SCHEMA.EXT_SYSTEM_PRIVILEGES where privilege_type='IDENT' order by grantee SQL query to list objects EnableOption Physical options allowed YES/NO 1 Reference REFR The following system variables are available: "REFR" // generated code of the reference "REFNAME" // reference name "PARENT" // generated code of the parent table "PNAME" // name of the parent table "PCODE" // code of the parent table "CHILD" // generated code of the reference "CNAME" // name of the child table "CCODE" // code of the child table "PQUALIFIER" // qualifier of the parent table. See QUALIFIER "CQUALIFIER" // qualifier of the child table. See QUALIFIER "REFRNAME" // Reference name "REFRCODE" // Reference code "FKCONSTRAINT" // Reference constraint name (foreign key) "PKCONSTRAINT" // constraint name of the parent key used to reference object "CKEYCOLUMNS" // list of parent key columns. Ex: C1, C2, C3 "FKEYCOLUMNS" // list of child foreign key columns. Ex: "UPDCONST" // Update declarative constraint. Keywords "cascade", "set null" or "set default" "DELCONST" // Delete declarative constraint. Keywords "cascade", "set null" or "set default" "MINCARD" // Min cardinality "MAXCARD" // Max cardinality "POWNER" // Parent table owner "COWNER" // child table owner "CHCKONCMMT" // TRUE when check on commit is selected on the reference (ASA 6.0 specific) For reference joins (couple of column in a reference), the following system variables are available: "CKEYCOLUMN" // generated code of the parent table column (primary key) "FKEYCOLUMN" // generated code of the child table column (foreign key) "PK" // primary key column generated code "PKNAME" // primary key column name "FK" // foreign key column generated code "FKNAME" // foreign key column name "AK" // alternate key column code (same as PK) "AKNAME" // alternate key column name (same as PKNAME) "COLTYPE" // primary column column datatype "DEFAULT" // foreign key column default value Enable Foreign key allowed 1 FKInTable Foreign key created in table FKAutoIndex Foreign key is auto-indexed EnablefKeyName Foreign key role allowed in database generation 1 ConstName %REFR% Constraint name template for foreign keys CheckOnCommit Referential integrity test differed after the COMMIT DclDelIntegrity CASCADE SET NULL SET DEFAULT Declarative referential integrity constraint allowed for delete Add [constraint %CONSTNAME%] foreign key (%FKEYCOLUMNS%) references [%PQUALIFIER%]%PARENT% (%CKEYCOLUMNS%) [on delete %DELCONST%] Command for defining a foreign key Create alter table [%QUALIFIER%]%TABLE% add [constraint %CONSTNAME%] foreign key (%FKEYCOLUMNS%) references [%PQUALIFIER%]%PARENT% (%CKEYCOLUMNS%) [on delete %DELCONST%] Command for adding a foreign key DclUpdIntegrity Declarative referential integrity constraint allowed for update SqlListQuery SQL query to list objects SqlAttrQuery SQL query to reverse object attributes PKey PKEY The following system variables are available: (parent table items are also available for indexes) "COLUMNS" // List of columns of the key. Ex: "A, B, C" "ISPKEY" // TRUE when the key is the primary key of the table "PKEY" // constraint name Enable Primary key allowed 1 PKInTable Primary key created in table 1 PkAutoIndex Primary key is auto-indexed 1 ConstName %PKEY% Constraint name template for primary keys Add [constraint %CONSTNAME%] primary key (%PKEYCOLUMNS%) Command for defining a primary key Create alter table [%QUALIFIER%]%TABLE% add [constraint %CONSTNAME%] primary key (%PKEYCOLUMNS%) Command for adding a primary key Drop alter table %TABLE% drop primary key Command for dropping a primary key Key KEY The following system variables are available: (parent table items are also available for indexes) "COLUMNS" // List of columns of the key. Ex: "A, B, C" "ISPKEY" // TRUE when the key is the primary key of the table "PKEY" // constraint name Enable UNIQUE constraint allowed for tables 1 UniqConstAutoIndex UNIQUE constraint is auto-indexed ConstName A_%.6:AKEY% Constraint name template for alternate keys SqlAkeyIndex SQL query to obtain the alternate key indexes of a table by ODBC UniqInTable Alternate key generated in table 1 Drop alter table [%QUALIFIER%]%TABLE% drop constraint %CONSTNAME% Command for dropping an alternate key Add [constraint %CONSTNAME%] unique (%COLUMNS%) Command for defining an alternate key Create alter table [%QUALIFIER%]%TABLE% add [constraint %CONSTNAME%] unique (%COLUMNS%) Command for adding an alternate key MaxConstLen Constraint name length 128 Domain PDMDOMN The following system variables are available: "DOMAIN" // generated code of the domain (also available for columns) Sql Server specific domain system variables: "RULENAME" // name of the rule object associated with the domain "DEFAULTNAME" // name of the default object associated with the domain Enable User defined data types allowed 1 Maxlen Length of user defined data types 128 Create create domain %DOMAIN% as %DATATYPE% Command for creating a user defined data type Drop drop domain %DOMAIN% Command for dropping a user defined data type SqlListQuery {OWNER, DOMAIN, DTTPCODE, LENGTH, PREC} select domain_schema, domain_name, data_type, character_maximum_length from information_schema.domains where 1=1 [and domain_schema='%SCHEMA%'] order by domain_name SQL query to list objects Abstract Data Type ADT The following system variables are available: "ADT" // generated code of the abstract data type Enable Abstract Data Types allowed EnableAdtOnDomn Abstract Data Types allowed on domain EnableAdtOnColn Abstract Data Types allowed on column SqlListQuery {ADT, OWNER , TYPE (R=STRUCTURED) , SIZE, DTTPCODE, LENGTH, PREC} SELECT typename, definer, metatype, 0, '', 0, 0 FROM syscat.datatypes WHERE metatype='R' ORDER BY typename SQL query to list objects Create [%ISSTRUCT% ? create type [%QUALIFIER%]%ADT% as ( %ADTDEF% ) without comparisons not final mode db2sql ] Command for adding an abstract data type Drop drop type [%QUALIFIER%]%ADT% Command for dropping an abstract data type Database DTBS Enable Database allowed Tablespace TSPC The following system variables are available: "TABLESPACE" // generated code of the tablespace Enable Tablespace allowed Create create tablespace %TABLESPACE% [%OPTIONS%] Command for creating a tablespace. Example: create tablespace %TABLESPACE% Drop drop tablespace %TABLESPACE% Command for dropping a tablespace. Example: drop tablespace %TABLESPACE% Options in %s in nodegroup %s pagesize %s : default=4096 managed by : composite=yes { system using : composite=yes { <container_list> : composite=yes, separator=yes, parenthesis=yes { <container_string> %s : multiple=yes } } database using : composite=yes { <container_clause> : composite=yes, parenthesis=yes, separator=yes { <list> { file %s device %s <number_of_pages> %d } } } <on_node_clause> : composite=yes { on node : composite=yes, parenthesis=yes, separator=yes { <node_list> : composite=yes, multiple=yes { <node_number> %s to %s } } on nodes : composite=yes, parenthesis=yes, separator=yes { <node_list> : composite=yes, multiple=yes { <node_number> %s to %s } } } } extentsize %d prefetchsize %d bufferpool %s overhead %s : default=24.1 transferrate %s : default=0.9 dropped table recovery %s : list=on |off Default options for creating a tablespace. DefOptions managed by system using ( <container_string> '%TABLESPACE%' ) Default values for tablespace options. SqlListQuery {SCHEMA, TABLESPACE} select definer, tbspace from syscat.tablespaces where definer not like 'SYS%' order by definer, tbspace SQL query to list objects Storage STOR The following system variables are available: "STORAGE" // generated code of the storage Create create bufferpool %STORAGE% [%OPTIONS%] Command for creating a storage. Example: create storage %STORAGE% Drop Command for dropping a storage. Example: drop storage %STORAGE% Options <nodegroup> : composite=yes { all node nodegroup : composite=yes, separator=yes { <nodegroup_name> %s : mulitple=yes } } size %d : composite=yes { except on node : composite=yes, parenthesis=yes { <node_list> : composite=yes, multiple=yes { <node_number> %d to %d size %d } } } pagesize %d <extended_storage> %s : list=extended storage |not extended storage Default options for creating a storage DefOptions Default values for storage options Enable Storage allowed SqlListQuery SQL query to list objects SqlAttrQuery SQL query to reverse object attributes View VIEW The following system variables are available: "VIEW" // generated code of the view "VIEWNAME" // view name "VIEWCODE" // view code "VIEWCOLN" // List of columns of the view. Ex: "A, B, C" "SQL" // SQL text of the view. Ex: Select * from T1 "VIEWCHECK" // Keyword "with check option" if selected on the view "SCRIPT" // complete view creation order. Ex: create view V1 as select * from T1 Enable View allowed 1 Create create view [%QUALIFIER%]%VIEW% [(%VIEWCOLN%) ]as %SQL% [%VIEWCHECK%] Command for creating a view. Example: create view %VIEW% Drop drop view [%QUALIFIER%]%VIEW% Command for dropping a view. Example: drop view %VIEW% ViewComment comment on table [%QUALIFIER%]%VIEW% is %.q:COMMENT% Command for adding a view comment. ViewCheck with check option Option for checking a view SqlListQuery {OWNER, VIEW} select v.view_schema, v.view_name from information_schema.view_table_usage v where 1=1 [and v.view_schema='%SCHEMA%'] order by v.view_schema, v.view_name SQL query to list objects SqlAttrQuery {OWNER, VIEW, SCRIPT} select view_schema, view_name from information_schema.view_table_usage where 1=1 [and view_schema='%SCHEMA%'] [and view_name='%VIEW%'] SQL query to reverse object attributes Trigger TRGR The following system variables are available: (parent table items are also available for indexes) trigger object specific items "ORDER" // Order number of the trigger (for sort of trigger when the DBMS support more than one trigger of one type) "TRIGGER" // generated code of trigger "TRGTYPE" // trigger type (keywords "beforeinsert", "afterupdate", ...) "TRGEVENT" // trigger event (keywords "insert", "update", "delete" "TRGTIME" // trigger time (keywords NULL, "before", "after" inside the body of the trigger, macros redefine variables on objects "REFNO" // reference position in the list of references of the table customized error messages management "ERRNO" // error number for standard error "ERRMSG" // error message for standard error "MSGTAB" // name of the table containing user-defined error messages "MSGNO" // in the user-defined error table, name of the column containing the error numbers "MSGTXT" // in the user-defined error table, name of the column containing the error messages "SCRIPT" // body of trigger or procedure Enable Trigger allowed 1 EnableMultiTrigger Multi trigger allowed 1 DefaultTriggerName %TEMPLATE%_%.L:TABLE% Default trigger name Maxlen Trigger name length 128 Drop drop trigger [%QUALIFIER%]%TRIGGER% Command for dropping a trigger. Example: drop trigger %TRIGGER% SqlListQuery {OWNER, TABLE, TRIGGER, TRGTIME (A=After, *=Before), TRGEVENT (U=Update, D=Delete, *=Insert)} select tu.table_schema, tu.table_name, t.trigger_name, t.condition_timing, t.event_manipulation from information_schema.triggers t, information_schema.trigger_table_usage tu where t.trigger_name = tu.trigger_name and tu.table_schema='%SCHEMA%' SQL query to list objects SqlAttrQuery {OWNER, TABLE, TRIGGER, SCRIPT} select tu.table_schema, tu.table_name, t.trigger_name, t.condition_timing, t.event_manipulation from information_schema.triggers t, information_schema.trigger_table_usage tu where t.trigger_name = tu.trigger_name and tu.table_schema='%SCHEMA%' SQL query to reverse object attributes Create create trigger %TRIGGER%[%TRGTIME%=before? no cascade][ %TRGTIME%][ %TRGEVENT%[ of %COLUMNS%]] on [%QUALIFIER%]%TABLE% %TRGDEFN% Command for creating a trigger. Example: create trigger on [%QUALIFIER%]%TABLE% %TRGDEFN% Procedure PROC The following system variables are available: "PROC" // generated code of the procedure (also available for trigger when the trigger is implemented with a procedure) "FUNC" // generated code of the procedure if the procedure is a function (with return value) Enable Procedure allowed 1 EnableFunc Function allowed 1 Maxlen Procedure name length 128 MaxFuncLen Function name length 128 Drop drop procedure %PROC% Command for dropping a procedure. Example: drop procedure %PROC% DropFunc drop function %FUNC% Command for dropping a function. Example: drop function %FUNC% CustomProc create procedure %PROC% Command for creating a stored procedure CustomFunc create function %FUNC% (<type>) RETURNS <type> EXTERNAL NAME '%FUNC%' LANGUAGE C PARAMETER STYLE DB2SQL DETERMINISTIC NO SQL NO EXTERNAL ACTION Command for creating a function SqlListQuery {OWNER, PROC} select routine_schema, routine_name from information_schema.routines where routine_schema='%SCHEMA%' SQL query to list objects SqlAttrQuery {OWNER, PROC ,SCRIPT} select routine_schema, routine_name, routine_definition from information_schema.routines where routine_schema='%SCHEMA%' SQL query to reverse object attributes Abstract Data Type Attribute ATTR The following system variables are available: "ATTR" // generated code of the abstract data type attribute Add %ADTATTR% %DATATYPE% Command for defining an abstract data type attribute SqlListQuery { ADT, ADTATTR , DTTPCODE, LENGTH, PREC } SELECT typename, attr_name, attr_typename, length, scale FROM syscat.attributes SQL query to list objects 1 DataType Contains data type translation entries. These entries list the correspondence between internal data types and the target database data types AmcdDataType Data types translation table from internal data types to target database data types. %n is the length of the data type %s is the size of the data type %p is the precision of the data type <UNDEF>=<Undefined> A%n=CHARACTER(%n) VA%n=VARCHAR(%n) BT=SMALLINT BT%n=NUMERIC(3,0) SI=SMALLINT LI=BIGINT I=INTEGER N%n=NUMERIC(%n) N%s,%p=NUMERIC(%s,%p) DC%n=DECIMAL(%n) DC%s,%p=DECIMAL(%s,%p) SF=REAL F=FLOAT F%n=FLOAT(%n) LF=DOUBLE PRECISION MN=NUMERIC(15,4) MN%n=NUMERIC(%n,0) MN%s,%p=NUMERIC(%s,%p) D=DATE T=TIME DT=TIMESTAMP TS=TIMESTAMP BL=ODBC.BIT NO=INTEGER NO%n=NUMERIC(%n,0) BIN%n=BINARY(%n) MBT=CHARACTER MBT%n=CHARACTER(%n) VMBT=VARCHAR(%n) VMBT%n=VARCHAR(%n) OLE%n=BINARY(%n) *=CHARACTER(10) DATALINK=DATALINK PIC=VARCHAR(5000) LBIN=VARCHAR(5000) <UNDEF> A%n VA%n BT BT%n SI LI I N%n N%s,%p DC%n DC%s,%p SF F F%n LF MN MN%n MN%s,%p D T DT TS BL NO NO%n BIN%n MBT MBT%n VMBT VMBT%n OLE%n DATALINK PIC LBIN PhysDataType Data types translation table from target database data types to internal data types. <Undefined>=<UNDEF> CHARACTER=A1 CHARACTER(%n)=A%n VARCHAR(%n)=VA%n SMALLINT=SI INTEGER=I NUMERIC(%n)=N%n NUMERIC(%s,%p)=N%s,%p DECIMAL(%n)=DC%n DECIMAL(%s,%p)=DC%s,%p REAL=SF FLOAT=F FLOAT(%n)=F%n DOUBLE PRECISION=LF DATE=D TIME=T TIMESTAMP=TS BINARY(%n)=BIN(%n) *=A10 BIGINT=LI DATALINK=DATALINK <Undefined> CHARACTER CHARACTER(%n) VARCHAR(%n) SMALLINT INTEGER NUMERIC(%n) NUMERIC(%s,%p) DECIMAL(%n) DECIMAL(%s,%p) REAL FLOAT FLOAT(%n) DOUBLE PRECISION DATE TIME TIMESTAMP BINARY(%n) BIGINT DATALINK PhysDttpSize Table of storage sizes of target database data type TIME=3 TIME HostDataType Data types translation from database data type to procedure data type CHARACTER=char CHAR(%n)=varchar CHARACTER(%n)=varchar CHARACTER VARYING(%n)=varchar CHAR VARYING(%n)=varchar DATALINK=datalink BIGINT=li VARCHAR(%n)=varchar VARCHAR2(%n)=varchar LONG=long LONG VARCHAR=long SMALLINT=integer INT=integer INTEGER=integer REAL=number DOUBLE PRECISION=number DEC=number DEC(%n)=number DEC(%s,%p)=number DECIMAL=number DECIMAL(%n)=number DECIMAL(%s,%p)=number NUMBER=number NUMBER(%n)=number NUMBER(%s,%p)=number CHAR=char FLOAT=float FLOAT(%n)=number DATE=date RAW(%n)=raw LONG RAW=raw CHARACTER CHAR(%n) CHARACTER(%n) CHARACTER VARYING(%n) CHAR VARYING(%n) DATALINK BIGINT VARCHAR(%n) VARCHAR2(%n) LONG LONG VARCHAR SMALLINT INT INTEGER REAL DOUBLE PRECISION DEC DEC(%n) DEC(%s,%p) DECIMAL DECIMAL(%n) DECIMAL(%s,%p) NUMBER NUMBER(%n) NUMBER(%s,%p) CHAR FLOAT FLOAT(%n) DATE RAW(%n) LONG RAW PhysLogADTType Abstract Data types translation table from target database abstract data types to PowerDesigner internal abstract data types. STRUCTURED=Structured STRUCTURED LogPhysADTType Abstract Data types translation table from internal abstract data types to target database abstract data types. Array=<Undefined> List=<Undefined> Structured=STRUCTURED Object=<Undefined> Java=<Undefined> Array List Structured Object Java OdbcPhysDataType Data types translation table from ODBC data types to target database data types. CHARacter(%n)=CHARACTER(%n) INTeger%n=INTEGER DECimal(%s,%p)=DECIMAL(%s,%p) DECimal(%n)=DECIMAL(%n) DECimal=DECIMAL DECimal(5)=DECIMAL CHARacter(1)=CHARACTER FLOAT(15)=FLOAT CHARacter(%n) INTeger%n DECimal(%s,%p) DECimal(%n) DECimal DECimal(5) CHARacter(1) FLOAT(15) Extended Attributes Extended Attributes Extended Attributes definition Types Types Defines extended attribute types that can be used into the DBMS. Objects Objects Defines each type of object which needs extended attributes for the DBMS. Column COLN Defines list of table column extended attributes BFF34D73-437F-11D4-B743-0008C752FCF9 ExtGenExpr 1 12 BFF34D74-437F-11D4-B743-0008C752FCF9 ExtGenAlways 1 BFF34D76-437F-11D4-B743-0008C752FCF9 ExtForBitData 1 FALSE Table TABL Defines list of table extended attributes CBF7D90F-B582-11D4-B75D-0008C752FCF9 ExtSummary 1 252338DC-2A3D-11D3-A992-006097355E2B InsertChildParentExist InsertChildParentExist .FOREACH_PARENT() signal sqlstate '70001' ('Parent does not exist. Cannot create child in "[%CQUALIFIER%]%CHILD%".'); .ENDFOR 0 937215924 mledier Parent must exist when inserting child constraint .FOREACH_PARENT() -- Parent "[%PQUALIFIER%]%PARENT%" must exist when inserting a child in "[%CQUALIFIER%]%CHILD%" (.JOIN("new_ins.%FK% is not null", "", " and", " and") not exists (select 1 from [%PQUALIFIER%]%PARENT% where .JOIN("[%PQUALIFIER%]%PARENT%.%PK% = new_ins.%FK%", "and ", "", ")) or") .ENDFOR 252338DD-2A3D-11D3-A992-006097355E2B InsertTooManyChildren InsertTooManyChildren 0 937215924 mledier Cannot exceed the maximum cardinality constraint when inserting the child 252338E0-2A3D-11D3-A992-006097355E2B UpdateChangeColumn UpdateChangeColumn signal sqlstate '70003' ('Non modifiable column cannot be modified.'); 0 937215964 mledier Cannot modify non modifiable column constraint .FOREACH_COLUMN("NMFCOL") -- Non modifiable column "%COLUMN%" cannot be modified (.JOIN("new_upd.%COLUMN% <> old_upd.%COLUMN%", "", " or", " ) or") .ENDFOR 252338E1-2A3D-11D3-A992-006097355E2B UpdateChildParentExist UpdateChildParentExist .FOREACH_PARENT() signal sqlstate '70004' ('Parent does not exist. Cannot update child in "[%CQUALIFIER%]%CHILD%".'); .ENDFOR 0 937215964 mledier Parent must exist when updating a child constraint .FOREACH_PARENT() -- Parent "[%PQUALIFIER%]%PARENT%" must exist when updating a child in "[%CQUALIFIER%]%CHILD%" (.JOIN("new_upd.%FK% is not null", "", " and", " and") not exists (select 1 from [%PQUALIFIER%]%PARENT% where .JOIN("[%PQUALIFIER%]%PARENT%.%PK% = new_upd.%FK%", "and ", "", ")) or") .ENDFOR 252338E2-2A3D-11D3-A992-006097355E2B UpdateTooManyChildren UpdateTooManyChildren 0 937215964 mledier Cannot exceed the maximum cardinality constraint when updating the child 252338E3-2A3D-11D3-A992-006097355E2B UpdateChildChangeParent UpdateChildChangeParent signal sqlstate '70005' ('Cannot modify parent code in child "[%CQUALIFIER%]%CHILD%".'); 0 937215964 mledier Cannot change parent constraint .FOREACH_PARENT("FKCANTCHG") -- Cannot modify parent code of "[%PQUALIFIER%]%PARENT%" in child "[%CQUALIFIER%]%CHILD%" (.JOIN("new_upd.%FK% <> old_upd.%FK%", "", " or", " ) or") .ENDFOR 252338E4-2A3D-11D3-A992-006097355E2B UpdateParentRestrict UpdateParentRestrict signal sqlstate '70006' ('Children still exist. Cannot modify parent code in "[%PQUALIFIER%]%PARENT%".'); 0 937215964 mledier Update restrict constraint .FOREACH_CHILD("UPDATE RESTRICT") -- Cannot modify parent code in "[%PQUALIFIER%]%PARENT%" if children still exist in "[%CQUALIFIER%]%CHILD%" (.JOIN("new_upd.%PK% <> old_upd.%PK%", "", " or", " and") exists (select 1 from [%CQUALIFIER%]%CHILD% where .JOIN("[%CQUALIFIER%]%CHILD%.%FK% = old_upd.%PK%", "and ", "", ")) or") .ENDFOR 252338E5-2A3D-11D3-A992-006097355E2B UpdateParentCascade UpdateParentCascade .FOREACH_CHILD("UPDATE CASCADE") -- Modify parent code of "[%PQUALIFIER%]%PARENT%" for all children in "[%CQUALIFIER%]%CHILD%" update [%CQUALIFIER%]%CHILD% set .JOIN("[%CQUALIFIER%]%CHILD%.%FK% = new_upd.%PK%", "", ",") where (.JOIN("[%CQUALIFIER%]%CHILD%.%FK% = old_upd.%PK%", "and ", "", ") and") (.JOIN("new_upd.%PK% <> old_upd.%PK%", "", " or", ");") .ENDFOR 0 0 Update cascade constraint .FOREACH_CHILD("UPDATE CASCADE") -- Modify parent code of "[%PQUALIFIER%]%PARENT%" for all children in "[%CQUALIFIER%]%CHILD%" (.JOIN("new_upd.%PK% <> old_upd.%PK%", "", " or", ") or") .ENDFOR 252338E6-2A3D-11D3-A992-006097355E2B UpdateParentSetNull UpdateParentSetNull .FOREACH_CHILD("UPDATE SETNULL") -- Set parent code of "[%PQUALIFIER%]%PARENT%" to NULL in "[%CQUALIFIER%]%CHILD%" update [%CQUALIFIER%]%CHILD% set .JOIN("[%CQUALIFIER%]%CHILD%.%FK% = NULL", "", ",") where (.JOIN("[%CQUALIFIER%]%CHILD%.%FK% = old_upd.%PK%", "and ", "", ") and") (.JOIN("new_upd.%PK% <> old_upd.%PK%", "", " or", " );") .ENDFOR 0 0 Update set null constraint .FOREACH_CHILD("UPDATE SETNULL") (.JOIN("new_upd.%PK% <> old_upd.%PK%", "", " or", " ) or") .ENDFOR 252338E7-2A3D-11D3-A992-006097355E2B UpdateParentSetDefault UpdateParentSetDefault .DEFINE "_DEFAULT" "NULL" .FOREACH_CHILD("UPDATE SETDEFAULT") -- Set parent code of "[%PQUALIFIER%]%PARENT%" to default in "[%CQUALIFIER%]%CHILD%" update [%CQUALIFIER%]%CHILD% set .JOIN("[%CQUALIFIER%]%CHILD%.%FK% = %DEFAULT%", "", ",") where (.JOIN("[%CQUALIFIER%]%CHILD%.%FK% = old_upd.%PK%", "and ", "", ") and") (.JOIN("new_upd.%PK% <> old_upd.%PK%", "", " or", ");") .ENDFOR 0 0 Update set default constraint .FOREACH_CHILD("UPDATE SETDEFAULT") (.JOIN("new_upd.%PK% <> old_upd.%PK%", "", " or", ") or") .ENDFOR 252338EA-2A3D-11D3-A992-006097355E2B DeleteParentRestrict DeleteParentRestrict signal sqlstate '70007' ('Children still exist. Cannot delete parent "[%PQUALIFIER%]%PARENT%".'); 0 933609149 mledier Delete restrict constraint .FOREACH_CHILD("DELETE RESTRICT") -- Cannot delete parent "[%PQUALIFIER%]%PARENT%" if children still exist in "[%CQUALIFIER%]%CHILD%" exists (select 1 from [%CQUALIFIER%]%CHILD% where (.JOIN("[%CQUALIFIER%]%CHILD%.%FK% = old_del.%PK%", "and ", "", ")) or") .ENDFOR 252338EB-2A3D-11D3-A992-006097355E2B DeleteParentCascade DeleteParentCascade .FOREACH_CHILD("DELETE CASCADE") -- Delete all children in "[%CQUALIFIER%]%CHILD%" delete from [%CQUALIFIER%]%CHILD% where .JOIN("[%CQUALIFIER%]%CHILD%.%FK% = old_del.%PK%", "and ", "", ";") .ENDFOR 0 0 Delete cascade constraint .FOREACH_CHILD("DELETE CASCADE") .ENDFOR 252338EC-2A3D-11D3-A992-006097355E2B DeleteParentSetNull DeleteParentSetNull .FOREACH_CHILD("DELETE SETNULL") -- Set parent code of "[%PQUALIFIER%]%PARENT%" to NULL in child "[%CQUALIFIER%]%CHILD%" update [%CQUALIFIER%]%CHILD% set .JOIN("%FK% = NULL", "", ",") where .JOIN("[%CQUALIFIER%]%CHILD%.%FK% = old_del.%PK%", "and ", "", ";") .ENDFOR 0 0 Delete set null constraint .FOREACH_CHILD("DELETE SETNULL") .ENDFOR 252338ED-2A3D-11D3-A992-006097355E2B DeleteParentSetDefault DeleteParentSetDefault .DEFINE "_DEFAULT" "NULL" .FOREACH_CHILD("DELETE SETDEFAULT") -- Set parent code "[%PQUALIFIER%]%PARENT%" to default in "[%CQUALIFIER%]%CHILD%" update [%CQUALIFIER%]%CHILD% set .JOIN("%FK% = %DEFAULT%", "", ",") where .JOIN("[%CQUALIFIER%]%CHILD%.%FK% = old_del.%PK%", "and ", "", ";") .ENDFOR 0 0 Delete set default constraint .FOREACH_CHILD("DELETE SETDEFAULT") .ENDFOR 252338DA-2A3D-11D3-A992-006097355E2B BeforeInsertTrigger BeforeInsertTrigger -- Before insert trigger "[%QUALIFIER%]%TRIGGER%" for table "[%QUALIFIER%]%TABLE%" create trigger [%QUALIFIER%]%TRIGGER% no cascade before insert on [%QUALIFIER%]%TABLE% referencing new as new_ins for each row mode db2sql when ( .DeclInsertChildParentExist .DeclInsertTooManyChildren (0=1) ) begin atomic signal sqlstate '70001' ('Cannot create child in "[%CQUALIFIER%]%CHILD%".'); end / 0 937215924 mledier Before insert trigger tib_%.L:TABLE% BI 252338DB-2A3D-11D3-A992-006097355E2B AfterInsertTrigger AfterInsertTrigger -- After insert trigger "[%QUALIFIER%]%TRIGGER%" for table "[%QUALIFIER%]%TABLE%" create trigger [%QUALIFIER%]%TRIGGER% after insert on [%QUALIFIER%]%TABLE% referencing old as old_ins for each row mode db2sql begin atomic end / 0 0 After insert trigger tia_%.L:TABLE% AI 252338DE-2A3D-11D3-A992-006097355E2B BeforeUpdateTrigger BeforeUpdateTrigger -- Before update trigger "[%QUALIFIER%]%TRIGGER%" for table "[%QUALIFIER%]%TABLE%" create trigger [%QUALIFIER%]%TRIGGER% no cascade before update of .INCOLN("%COLUMN%", "", ",") on [%QUALIFIER%]%TABLE% referencing new as new_upd old as old_upd for each row mode db2sql when ( .DeclUpdateChangeColumn .DeclUpdateChildParentExist .DeclUpdateTooManyChildren .DeclUpdateChildChangeParent .DeclUpdateParentRestrict (0=1) ) begin atomic signal sqlstate '70002' ('Inexistant Parent or non updatable child. Cannot update [%QUALIFIER%]%TABLE%'); end / 0 937215964 mledier Before update trigger tub_%.L:TABLE% BU 252338DF-2A3D-11D3-A992-006097355E2B AfterUpdateTrigger AfterUpdateTrigger -- After update trigger "[%QUALIFIER%]%TRIGGER%" for table "[%QUALIFIER%]%TABLE%" create trigger [%QUALIFIER%]%TRIGGER% after update of .INCOLN("%COLUMN%", "", ",") on [%QUALIFIER%]%TABLE% referencing new as new_upd old as old_upd for each row mode db2sql when ( .DeclUpdateParentCascade .DeclUpdateParentSetNull .DeclUpdateParentSetDefault (0=1) ) begin atomic .UpdateParentCascade .UpdateParentSetNull .UpdateParentSetDefault end / 0 0 After update trigger tua_%.L:TABLE% AU 252338E8-2A3D-11D3-A992-006097355E2B BeforeDeleteTrigger BeforeDeleteTrigger -- Before delete trigger "[%QUALIFIER%]%TRIGGER%" for table "[%QUALIFIER%]%TABLE%" create trigger [%QUALIFIER%]%TRIGGER% no cascade before delete on [%QUALIFIER%]%TABLE% referencing old as old_del for each row mode db2sql when ( .DeclDeleteParentRestrict (0=1) ) begin atomic .DeleteParentRestrict end / 0 0 Before delete trigger tdb_%.L:TABLE% BD 252338E9-2A3D-11D3-A992-006097355E2B AfterDeleteTrigger AfterDeleteTrigger -- After delete trigger "[%QUALIFIER%]%TRIGGER%" for table "[%QUALIFIER%]%TABLE%" create trigger [%QUALIFIER%]%TRIGGER% after delete on [%QUALIFIER%]%TABLE% referencing old as old_del for each row mode db2sql begin atomic .DeleteParentCascade .DeleteParentSetNull .DeleteParentSetDefault end / 0 0 After delete trigger tda_%.L:TABLE% AD