PDR605 PowerDesigner Tips, Tricks, and Customizations for Data Modelers

1. PDR605 PowerDesigner Tips, Tricks, and Customizations for Data Modelers Anthony HillSenior Product Support Engineerahill@sybase.com / 978-287-2564August 6, 2003

2. Topics • PowerDesigner Options • DBMS Definition File (XDB) • Definition Files (XDB and XEM) • Generation Template Language (GTL) • VB Script • PowerDesigner Metamodel • Miscellaneous Tips

3. PowerDesigner Options • What • Method for controlling PowerDesigner behavior. • Display Preferences – controls visual aspects of PowerDesigner • Model Options – controls model specific aspects of PowerDesigner • General Options – options not specific to any particular model • Why • Alter PowerDesigner behavior to best suit your needs • Changes can either be model specific or set as defaults for new models created • How • General Features Guide – Chapter 3 - Using the PowerDesigner Interface – Defining Global Options • General Features Guide – Chapter 7 – Managing Models – sections dealing with Naming Conventions and Conversion • General Features Guide – Chapter 14 – Model Graphics – Model Display Preferences

4. PowerDesigner Options - Display Preferences • General – General display preferences • Windows background, Unit, Grid, Diagram • Object View • Top level controls name splitting of text in all graphics – options for none, truncation or word wrapping • Display options for each type of base object – options vary by object • Format • Control of actual graphic symbol – size, line styles, fill, shadow, font, and custom shape • Text cannot be embedded within custom shapes

5. PowerDesigner Options - Model Options • Model level – List of PDM objects that can have options set • Name/Code case sensitivity – applies to all objects in the model TIP: Select Name/Code Sensitivity option to ignore the case during comparison, generation, or merging of models • Ignore Identifying Owner – applies to tables, views, and stored procedures • Column & Domain – • Enforce non-divergence – this option determines if column properties must be the same as those of the domain or if they can differ • Column properties that can be set are Data type, Check, Rules, Mandatory, and Profile • Default data type - the data type that applies to columns and domains if no data type is selected

6. PowerDesigner Options - Model Options • Model level (cont) • Reference – Rules when creating references • Unique code – forces each reference to have a unique code • Auto-reuse columns - attempts to reuse an existing column in the child table as the foreign key • Auto-migrate columns - migrates the primary key as a foreign key and will optionally migrate the domain, checks and rules associated with the PK • Default Link on creation - determines if a join will be created by default for the reference • Default implementation - indicates how referential integrity is implemented in the reference, either declarative or triggers

7. PowerDesigner Options - Model Options • Naming Conventions – Set rules to enforce predefined name and code formats for all objects • Display – set to display either name or code values for the model • Enable name/code conversion - enables or disables the use of conversion scripts and conversion tables to generate a code from a name or a name from a code • Name & Code – specify the format for constraints on length, character case and valid characters for objects • Naming Template – specify a format template for use with several types of objects within a model • Name to Code & Code to Name – uses a combination of a conversion script and/or a conversion table to generate an objects name from its code or an objects code from its name

8. PowerDesigner Options - General Options • General – general default settings • Delete, Browser, Drag & Drop, Output log, Graphical tool behavior • Dialogue – define Property sheet behavior TipEnable Name to Code mirroring option if using conversion scripts • Editors – specify specific editors for different file types • Variables – list of “environment” variables used by PowerDesigner • Named Paths – logical paths used when paths are saved within files • Fonts – default font settings and naming conventions based upon the language version of Windows installed on your machine • User interface (lists), Code editor (SQL preview), RTF editor (description, annotation) can be modified • Repository – general repository interaction default settings

9. PowerDesigner Options – Generation Options • Inter model • Convert Names into Codes option must be selected during inter model generation • Object codes are generated from names using associated conversion script • DBCreateAfter Stereotype • Used with stored procedures and views to control generation order

10. PowerDesigner Options – Reverse Engineering • Reverse engineer into an existing model or a new model • Selection – specify DBMS source to be reverse engineered. Either an ODBC data source or SQL script file • reverse using administrator’s permission – select in order to be able to select data in the system tables that are reserved to a database admin • Options • After reverse engineering – options that potentially can alter the reverse engineered results • Script terminator – terminator used to end the statement when reverse engineering via script • Target Model – includes or allows you to add target model(s) to create external shortcuts

11. Miscellaneous Tips – Disable Synonym creation • Problem: • When the auto-layout feature is executed, synonyms are sometimes created for several object. This functionality is not always desired especially when reverse engineering a database. • Solution: • Use the following registry key to deactivate the synonym creation • [HKEY_CURRENT_USER\Software\Sybase\PowerDesigner 9\General] • "SynonymsInAutoLayout"= "No" ("Yes" by default)

12. DBMS Definition File (XDB) • What • Method used by PowerDesigner that contains specifications for a particular Database Management System (DBMS). It contains the syntax templates for generation, reverse engineering, data types and constants for a supported DBMS. • Why • Used by PowerDesigner engineering to support all the DBMS • Used by users to alter standard DBMS to better suit their needs • Used by users to add new DBMS that are not shipped with PowerDesigner • How • Advanced User Documentation – Chapter 1 – DBMS Reference Guide • Advanced User Documentation – Chapter 2 – Managing Profiles • Physical Data Model User’s Guide – Chapter 13 – Variables in PowerDesigner • Physical Data Model User’s Guide – Chapter 14 – DBMS-Specific Features • Review included XDB files for understanding and ideas

13. DBMS Definition File (XDB) • General – DBMS Identification and general flags that apply for all objects • Script - DBMS characteristics, command definition, and data type translations for script generation and reverse engineering • SQL – contains values that define the general syntax for the database • Syntax - Contains general parameters for SQL syntax • Format - Contains entries that define script formatting • File - Text entries used during the database generation • Keywords - List of reserved words and functions available in SQL • Objects – details for each type of object available for generation to and reverse engineering from the database

14. DBMS Definition File (XDB) • Script (cont) • DataType – lists of valid data types including conversions to PowerDesigner internal data types • AmcdDataType – list of PowerDesigner internal data types mapped to DBMS data types • OdbcPhysDataType - translation table from ODBC data types to target DBMS data types • PhysDataType – contains the translation table from target database data types to internal data types • PhysDttpSize - table of storage sizes of target DBMS data type • PhysOdbcDataType – contains data type translations from target DBMS to ODBC • HostDataType – translation from database data type to procedure data type • Abstract translation files – several files for abstraction data type translation • Customize – retrieved information from PowerDesigner 6 DEF files

15. DBMS Definition File (XDB) • ODBC – same structure as Script category. Used for ODBC generation when DBMS Script category does not suffice • Profile – see next section • Trigger templates – list of pre-defined trigger templates indicating referential integrity • Trigger type – insert, update, delete • Trigger template items – list of pre-defined trigger template items to support the PowerDesigner trigger templates

16. DBMS Definition File (XDB) - How does it all work? • Generation - object statements are parsed and their variables are evaluated and replaced. Script and ODBC use the same statements • Extension mechanism – uses extension statements to generate additional code before or after the Create, Drop, or Modify statements • Defined using PowerDesigner Generation Template Language (GTL) • Can contain references to extended attributes, variables, and macros • Modify statements - statements used to modify existing objects during database modification and to generate DDL outside the ‘table’ create statement • ModifiableAttributes – list of extended attributes to be evaluated during the Modify database process • Database definition items • Statements that customize the PowerDesigner interface and behavior according the current database features

17. DBMS Definition File - How does it all work? (cont) • Reverse engineering • Script - Each statement in the script file maps to a create statement in the DBMS file • ReversedStatements – list of Extension statements used in database generation • ODBC – two distinct type of queries are used to retrieve information from system tables when the default ODBC function calls do not retrieve enough information • Extended Queries – user defined queries and variables used when system table information is stored in Long, Blob, or Text columns • Returned field allowing this is indicated in query header with the "EX" indicator • When the "EX“ is found, and if the returned value contains a %% with query name and field name, then it will execute the appropriate query and use the returned value to replace the %% part. • ReversedQueries – list of user-defined ODBC queries to be taken into account via reverse engineering • Queries must be listed in ReveredQueries item to be executed • The result of a query is mapped to PowerDesigner internal variables via the query header

18. Miscellaneous Tips – Change Modify SQL order • Problem: • When executing Modify Database, PowerDesigner generates the DDL based upon statement type. All the drop statements, then the create statements, etc. However, some customers do not like this format. • Solution: • Change the modify SQL order. To group all the SQL orders relative to a table in Modify script add the following registry key (CR 270997) • [HKEY_CURRENT_USER\Software\Sybase\PowerDesigner 9\FolderOptions\Physical Objects] • "GenTablGroupStmt" = "Yes"

19. Extended Model Definitions (XEM) • What • Mechanism for extending PowerDesigner object definitions • Contains graphical, functional and generation parameters • Why • Used by PowerDesigner engineering to support various functions • Can be used to complement object language generation – Application servers support • Can be used to generate totally new text files • Users can customize included behavior to better suit your requirements • Users can add totally new functionality • How • General Features Guide – Chapter 8 – Working with Extended Model Definitions • Advanced User Documentation – Chapter 2 – Using Profiles • Advanced User Documentation – Chapter 4 – Extended Model Definitions Reference Guide • Review included XEM files for understanding and ideas

20. Extended Model Definitions (XEM) • Extended Model Definition properties • Name and Code – uniquely identify this definition • File Name – where XEM is saved, blank if internal to the model • Family – Classify an XEM, usually to associate it with an Object Language • Sub Family – further classification of and XEM within the selected family • Auto attach – determines whether the default is to include this XEM in new models created targeting the family specified above • Category – used to control generation options • Enable Trace Mode – debugging/learning tool that lets you preview the templates used • Complement Language generation – determines whether this XEM definition will be used in the language generation (Only available for OOM XEM files)

21. Extended Model Definitions (XEM) • Generation section – define optional tasks to be processed after generation and options used during generation • Commands – actual commands which will be executed post generation • Options – options user can specify as part of the generation process • Tasks – actual list presented during generation, these reference one or more commands defined above • Profile – see next section

22. Miscellaneous Tips - HTML Report page break • Problem: • PowerDesigner implicitly cut pages every 50 rows for list which can be a problem for large model • Solution: • Define when you want PowerDesigner to generate a page break in list using the following keys: • NbRowInHtmlList - allows to define the number of items you want on each HTML pages generated for list items • NbSubPageInHtmlList - allows to define the number of links you want to have below each pages of the list. • [HKEY_CURRENT_USER\Software\Sybase\PowerDesigner 9\General] • "NbRowInHtmlList" = "50" (default value) • "NbSubPageInHtmlList" = "20" (default value)

23. Definition Files (XDB and XEM) – Profile • What • Extension mechanism for customizing the PowerDesigner Metamodel. • Categorizing objects, customizing graphics, adding metadata, adding object specific generation options and more • Why • Used by PowerDesigner engineering in both XDB and XEM files • Allows customizing graphics based on object type and category • Customize context menus based on object type • Add additional metadata to specific object or categories of objects • How • Advanced User Documentation – Chapter 2 – Managing Profiles • Review supplied XDB and XEM files for examples of usage

24. Definition Files (XDB and XEM) – Profile • Shared – contains extended attribute types or templates which can be used by any object • Metaclass level extensions • Determines the level at which the extension applies • Extensions apply to all instances of the metaclass • The PowerDesigner metamodel is object oriented and thus supports inheritance Tip – when adding metaclasses, change the filter to show abstract classes • Example – extended attributes applied to the following levels would apply to • NamedObject - applies to all objects • ConstrainedNamedObject - applies to Columns and Domains • Columns - applies to Columns only

25. Definition Files (XDB and XEM) – Profile • Instance level extensions • Stereotype – extension applies to instances of the object which have had the stereotype set to match • In 9.5.x, primarily used in the PowerDesigner OOM. • In PowerDesigner 10 stereotypes can be used in the PDM as well • Example Java Classes – EJBEntity, EJBSession, WebAppListener • Criterion – extension applies to instances of the object which match the Criterion’s condition • Conditions are defined using expressions from the .if macro syntax of PowerDesigner’s GTL

26. Definition Files (XDB and XEM) – Profile • For any of the levels listed above the following extensions can be added • Extended Attributes – additional metadata which can be controlled by each instance and used to control generation • Custom Checks – enhance the validation of instances when check model is performed • Includes check and auto-fix logic • Written in VBScript • Custom Symbol – customize the visual display of objects to better identify the instances • Custom Tool (Stereotypes only) – allows the creation of a tool palette object to ease the creation of objects • Templates and Generated files – used to generate text files based on the model • Tip - F12 to find where a template is defined • Method and Menu – These two are used to add entries to context menus. Method hold the VBScript to execute. Menu is the text that shows in the menu

27. Generation Template Language • What • PowerDesigner internal template based language used for text generation • Used in Definition files (XDB or XEM) to generate files based on a model • Why • Allows for all PowerDesigner generation to be flexible • Used by PowerDesigner engineering to generate text files • Can be used by users to alter files generated by PowerDesigner to suit your individual needs • Can be used to generate entirely new files. • How • Review supplied database definitions and extended model definitions • Advanced User Documentation – Chapter 5 – Generation Reference Guide

28. VB Script • What • Microsoft scripting language • Why • Automate repetitive tasks • Import / Export information • Extend PowerDesigner via additional model checks • Access PowerDesigner information from other applications • Used by PowerDesigner engineering to implement certain functionality • How • Review supplied sample scripts • PowerDesigner home directory\VBScripts • PowerDesigner home directory\OLE Automation • Get familiar with the PowerDesigner Metamodel • Experiment - *Backup your models first*

29. PowerDesigner Metamodel • What • A description of PowerDesigner objects, properties and methods • Why • Learn how to access PowerDesigner objects • Using VBScript • Using Generation Template Language • Using other languages that support COM • Understand PowerDesigner XML formatted models • How • PowerDesigner home directory\Examples\Metamodel.oom • Set of UML class diagrams graphically representing the metamodel • PowerDesigner home directory\pdvbs9.chm • Hyperlinked reference document of the PowerDesigner Metamodel

30. SDN Presents CodeXchange A New Benefit for Sybase Developers • Forum for exchanging samples, tools, scripts, etc. • New features enable community collaboration • Download samples created by Sybase or external users • Leverage contributions of others to extend Sybase products • Contribute code or start your own collaborative / open source project with input from other product experts • Any SDN member can participate • Log in using your MySybase account via SDN • www.codexchange.sybase.com • Or via SDN at www.sybase.com/developer • SDN & CodeXchange at TechWave • Visit SDN in the Technology Boardwalk • Learn about CodeXchange in the Sybase Booth Theater

31. Conclusion QUESTIONS ???

32. PDR605 PowerDesigner Tips, Tricks, and Customizations for Data Modelers Anthony HillSenior Product Support Engineerahill@sybase.com / 978-287-2564August 6, 2003