Oracle SQL Developer Data Modeler 2.0 (2.0.0.584) Patch 1 - Release Notes December 2009 Introduction Platform Support and System Requirements Before you Start New Features Supported Import Formats Supported Export Formats Known issues Documentation Finding More Information Introduction Oracle SQL Developer Data Modeler allows you to develop logical, multi-dimensional, user defined data types, relational and physical data models. The tool provides full forward engineering, including engineering from Logical ERD models to Relational models and the reverse, Import from the Data Dictionary and DDL script generation. The Data Modeler imports from and exports to a variety of sources and targets, provides a variety of formatting options and validates the models through a predefined set of design rules. For more detail of the feature see the collateral provided on the Data Modeler home page on the Oracle Technology Network. Platform Support and System Requirements Oracle SQL Developer Data Modeler runs on the following operating systems: Microsoft Windows (XP, Vista, Windows 7 including x64) Linux Mac OS X The following requirements must be met to install and run correctly: JavaTM 1.6 or higher Standard Edition Runtime Environment About 125 MB on Hard Disk 512 MB at a minimum, 1 GB RAM is recommended Note: We provide the download with and without the Java Runtime. Minimum is JRE 1.6. For Linux and Mac install you must download the zip without the JRE. You'll be prompted on startup for the path to the JRE. Before You Start Before you Install If you have worked with an early release of SQL Developer Data Modeler, then you save and reuse the default settings you made in the Tools > General Options menu, such as the Default Design Directory and Formatting Options. After installing a new build, you can use the Import/Export function in the Tools General Options menu to import the \datamodeler\bin\settings.xml file. Installing Download and unzip the files into a new and empty directory. Do not unzip over any existing installations, should you have them. Builds for the Mac, Linux and Windows without JRE are all the same download. The required executables are available in the same zip. For Linux and Mac OS X installations download the file without the JRE. When prompted, direct the install to your JRE 1.6 directory. If JRE 1.5 is already installed and is the default, you will not be prompted. In this case edit the datamodeler.conf file in the \datamodeler\bin directory and add SetJavaHome. (e.g. SetJavaHome /usr/lib/jdk16_04) 64-Bit Support: SQL Developer Data Modeler is supported on 64-bit platforms. Use the command file (datamodeler.bat) to start the application in the 64-bit JVM for 64-bit Windows (XP, Vista, Windows 7). Include the location of the JAVAW.exe in the PATH environment variable. Save As If you have models built in SQL Developer Data Modeler 2.0, and want to preserve and open your models in the patch release, then save your work by using "Save As" before you start making changes in the production release. Printing You can print your diagrams to a number of file formats, including PDF, PNG, JPG, SVG. Using the Reporting Repository You can export your designs to a reporting repository. Read the online help or the FAQ for more information. There is a brief Oracle by Example tutorial that walks you through creating and accessing the reporting repository and running the reports. The reporting schema has been updated and so you can use the update script (in the reports installation directory, \datamodeler\reports) that will apply changes to existing repository if required. The update script is not required when an existing repository is deleted and new repository is created. Providing Full Support for Oracle Database 11g features Full support for Oracle Database 11g will be added in time. The current Oracle Database 11g implementation is Oracle Database 10g compatible. Oracle Designer Import SQL Developer Data Modeler connects to and imports directly from the Oracle Designer repository. The focus of this import is the Data and not Application design. At this stage the Data Modeler does not import table spaces, stored procedures, packages, functions and data flow diagrams from the Designer Repository. Domains SQL Developer Data Modeler does not include a default domains file. NOTE: This is a change from the early adopter releases which had a predefined domains file. This file still exists in the \datamodeler\domains directory and can be imported to provide a default set of values if required. To create new domain definitions, select Tools > Domains Administration and click Add to create and add your own domains. These domains are added to the default file, defaultdomains.xml and can be used for all your projects. To create a named domains file, select Tools > Domains Administration and click the Select button, next to the Domains File field. Type in a new file name and click open. Now you can add all the new domains to this file as described. In order to use the new file you need to import these domains into your current design, otherwise the default domains file is used.To import a domains file. select File > Import > Domains. You can accept all the domains or select those you want to import individually. Comparing Logical Models To compare two logical models, ensure you have one model open and then select File | Import | Data Modeler Design. You can now select the logical model for the comparison. Once you have made the selection, you are presented with the Compare Merge dialog. Comparing Relational Models and Generation Alter DDL scripts To compare two relational models, ensure you have one relational model open in the design and then select File | Import | Data Dictionary (or DDL). In the import dialog you get the choice to "Swap Target Model". This drives the point of truth and the resulting merge compare dialog. You can elect either to merge the models or to View DDL to see the DDL to update the data dictionary. The current model can also be compared to another Data Modeler design using Tools | Compare /Merge Models Security All passwords set in the physical model are encrypted when saving a design in the file system . Delete Rules The General Options | Model | Relational | Default Foreign Key Delete Rule defines the delete rule when a new FK is created. If you create a model by importing a DDL script, then ensure the option is set as required before the import. Cloning Physical Models When cloning the physical details from one model to another, ensure you have saved the database you want to clone from. Note you can only clone databases of the same release or lower. i.e to populate a new Oracle Database 11g physical model, you can select a saved Oracle Database 11g or earlier. New Features included in SQL Developer 2.0 Patch 1 Logical types Default values for size, precision and scale can be defined at the logical type level and can be changed at column/attribute level There is a change in the way Integer and Smallint are mapped to Oracle native type. In Data Modeler 2.0, they are mapped to Number(10) and number(5). There is no longer this restriction and they can be (are) mapped to Integer and Smallint A list of preferred logical types can be defined and used in dialogs ( for Table, Entity and Structured Type) instead of the whole list of logical types. Specify preferred logical types using General Options | Model Domains You can assign a domain to a set of columns and attributes. Use the Domain dialog in the browser and select the "Used in" page A list of preferred domains can be defined and used in dialogs ( for Table and Entity) instead of the whole list of domains For design level domains and the "Preferred domains" list, if the design level domains are included in the list with the preferred domains, they only appear in the list when the design they belongs is open Specify preferred domains using General Options | Model You can elect not to create domains during the import of DDL scripts There is a new "Types To Domains Wizard". This transforms definitions based on logical types to definitions based on domains Naming Standards There is a new naming rule for FK attributes and columns There is a new wizard for the Logical model to apply naming templates for primary identifiers and FK attributes There are additional definitions (in the Naming Options dialog for the Relational Model) for max name length, case and permitted characters for indexes and all constraints: PK, UK, FK, Table level constraint, Column constraint There are additional warning message in dialogs when violating Naming Rules for Table, Column, View, Entity, Attribute, EntityView Relational model There is now support for virtual columns supported in Oracle Database 11g. This is unified with support for computed columns in SQL Server and UDB DDL generation "Use quoted identifiers" option has been added in Tools | General Options | DDL, to allow quoted names to be generated in DDL The Compare/Merge generation of alter statements in the "Advanced script" mode for backup/unload of tables to the file system now supports columns of type BLOB,CLOB and XMLTYPE (all three for Oracle Database 10g and Oracle Database 11g) and BFILE (for Oracle9i and Oracle Database 10g and Oracle Database 11g) Reporting repository Extended the size of some columns Added support to export and delete glossaries Included in the installation, an update script for reporting schema and diagrams (PDF files) of reporting schema model General When importing from the database dictionary, you can now select the database site for the imported database type There is a new option in the engineering dialog that enable/disables the transferring of object color settings between logical and relational models Supported Import formats Database Imports Oracle SQL Developer Data Modeler imports from: Supported Oracle Databases, Microsoft SQL Server 2000 or 2005, DB2/LUW V7 or V8, IBM DB2 for OS/390 and z/OS script file Supported Oracle Databases, Microsoft SQL Server2000 or 2005, IBM DB2/LUW V7 or V8, IBM DB2 for OS/390 and z/OS and ODBC/JDBC compliant data dictionary IMPORTANT: Oracle does not ship the non-Oracle drivers.To access the non-Oracle databases, you need to download and add the drivers required. The drivers can be downloaded from the following links: For Microsoft SQL Server: SQL Server 2000 For SQL Server 2000 you must make sure you have installed all three .jar files: msbase.jar, mssqlserver.jar and msutil.jar SQL Server 2005 The drivers for SQL Server 2000 can be used for import from SQL Server 2005 database. For DB2/LUW: IBM Data Server Driver for JDBC and SQLJ The driver for ODBC/JDBC is included in Java, so there is nothing extra to install To add the drivers yourself: Go to Tools - > General Options -> DDL -> Third Party JDBC drivers After installing, the drivers must be set in the option dialog as follows: SQL Server 2000 - msbase.jar, mssqlserver.jar, msutil.jar SQL Server 2005 - sqljdbc.jar DB2/UDB - db2jcc.jar From Other Modeling Tools SQL Developer Data Modeler imports the following Oracle Designer objects: Domains, object (structured) types, collection types Entities, relationships, arcs, entity inheritance hierarchies Tables, views, foreign keys, arcs Triggers for tables and views Snapshots, clusters, synonyms, sequences Each diagram that contains tables is transformed into subview in relational model Each diagram that contains entities is transformed into subview in logical model Each diagram that contains object (structured) types is transformed into subview in Data Types model SQL Developer Data Modeler supports the imports from the following: CA Erwin Data Modeler, version 4.x Sterling COOL:DBA V2.1, Sterling Bsnteam V7.2 and Cayenne Bsnteam V7.2 formats For Multi-Dimensional Models Cube Views metadata XMLA SQL Developer Data Modeler imports Oracle SQL Developer Data Modeler models Domains files Supported Export formats Supported Oracle Databases Microsoft SQL Server 2000 or 2005 IBM DB2/LUW V7 and V8 script file Multi-dimensional support Cube View Metadata XMLA and Oracle ROLAP and MOLAP (AW) CSV file Known Issues We are aware of the following limitations and issues. This list is not exhaustive, but a list of issues that you may encounter. Please contact Oracle Support through Metalink for further queries about issues, or to log new issues. Point to be aware of: Colored syntax in generated scripts can cause a delay for large scripts. A workaround for script files greater than 1 Mb has been implemented in DDL file editor. These files are displayed without colored syntax. If a database import takes too long, it may be either be a memory problem, related to the network or database. To check if it's memory related, open the datamodeler.conf file in the datamodeler\bin directory. Check the – “AddVMOption -Xmx768M” – if the import is slow and used memory is close to this given value, then you have a memory problem. You can increase the value, by changing the value in the conf file and restarting. Use a memory setting as much as needed to complete import. Merging physical objects as part of the merge of relational models. The properties of the objects in the source model override the properties of objects with the same name and type in target (current) model. For example, the source tablespace “Users” with one data file and target tablespace “Users” with two data files. the result after the merge is a tablespace “Users” with one data file and all properties of source tablespace and data file. Specific Bugs 8367182: Space calculation for db2/390 produce too high numbers 8428451: Partitioning is not transferred when cloning a DB2/390 v.7 site to DB2/390 v.8 site. This is due to the different kind of partitioning - index controlled in v.7 and table controlled in v.8. As workaround, generate v.7 DDL and import it using “Import Storage Objects from DDL” functionality available in the DB2/390 physical model node. The index controlled partitioning is transformed into table controlled during the import 8596675: NLS: import DDL file with multi byte data fails 8632234: Importing DB2 UDB with 2000+ tables. Then Data Modeler hangs: Workaround "AddVMOption -Xmx768M" to "AddVMOption -Xmx1300M" in the datamodeler.conf file found in the folder \datamodeler\bin. 9001156: Changes in table level check constraints are not included in DDL for the merge operation. It's fine for a new table 9001700: Compare /merge does not process FK properties transferable and "in arc" 9132628: DDL generation for UDB - scope clause for column is generated at table level and statement will fail if referred table appear later in DDL script 8839456: Can't browse nested columns Documentation You can use the documentation in the Oracle SQL Developer Data Modeler Help or access the online documentation. Finding More Information There is a dedicated Oracle SQL Developer Data Modeler page. You can also reach this page from the SQL Developer OTN homepage. Use the Oracle SQL Developer Forum on OTN for any discussion or queries.