Chat now with support
Chat with Support

Toad Data Modeler 7.2 - User Guide

Introduction User Interface Models and Model Objects
Physical Data Model
Entity Relationship Diagram Objects Basic Database Design Advanced Database Design
Universal Data Model Logical Data Model Working with Model Objects
Features and Tools
Application Variables Export/Import DDL Script Generation Graphics Model Actions Print Create New Project Reports Reverse Engineering Scripting and Customization About Templates Tips and Tricks Toad for Oracle Integration Toad Intelligence Central (TIC) Integration Tools Version Control
Options and Configuration Databases
Amazon Redshift 1.0 IBM DB2 LUW 9.5 IBM DB2 LUW 9.7 IBM DB2 LUW 10.1 IBM DB2 LUW 10.5 IBM DB2 LUW 11.1 IBM DB2 z/OS 10 IBM DB2 z/OS 11 Greenplum 4.1 Greenplum 4.2 Ingres 9.3 Ingres 10.0 EDB Postgres Advanced Server 10 Microsoft Access 2007/2010 Microsoft Azure SQL Database V12 Microsoft SQL Server 2005 Microsoft SQL Server 2008 Microsoft SQL Server 2012 Microsoft SQL Server 2014 Microsoft SQL Server 2016 Microsoft SQL Server 2017 Microsoft SQL Server 2019 MySQL 5.0 MySQL 5.1 MySQL 5.5 MySQL 5.6 MySQL 5.7 MySQL 8.0 Oracle 10g Oracle 11g Release 1 Oracle 11g Release 2 Oracle 12c Release 1 Oracle 12c Release 2 Oracle 18c Oracle 19c PostgreSQL 9.0 PostgreSQL 9.1 PostgreSQL 9.2 PostgreSQL 9.3 PostgreSQL 9.4 PostgreSQL 9.5 PostgreSQL 10 PostgreSQL 11 PostgreSQL 12 SQLite 3.7 Sybase ASE 15.5 Sybase ASE 15.7 SAP ASE 16.0 Sybase IQ 15.2 Sybase SQL Anywhere 11 SAP SQL Anywhere 17 Teradata 13 Vertica Database 8.0
Copyright Legal Notices

Simple Model Conversion

This feature allows you to convert your logical models to physical models and your physical models to models of another database system very quickly.

Scenario

You want to convert your Oracle 10g model to Microsoft SQL Server 2005.

  1. Open your Oracle 10g model.
  2. Select  File | Synchronization | Simple Model Conversion.
  3. Define the settings in the Conversion dialog.
Option Description

To Database

Select a target database system - Microsoft SQL Server 2005.

New Model Name

Define a name for the converted model.

More>>

Click this button to see and modify Object Types and Properties for the conversion. No modification is necessary. Object Types and Properties - OTPs

Close after Conversion

Select it to close the Conversion dialog after the process is finished.

Convert

Executes the process of conversion.

Close

Closes the Conversion dialog.

  1.  Click Convert.

Compare Models, Generate Change Report

Toad Data Modeler allows you to view differences between two models and for physical models you can generate change reports in HTML, RTF and PDF formats. Change reports are not available in logical models.

You can compare your models and generate the change report via the Model Actions.

How to Compare Models and Generate Change Reports

  • Click on Compare and Generate Report | Run in Model Menu or Run Compare and Generate Report Action in Model Actions.

  • Select another model for comparison. It can be either a model opened in Toad Data Modeler or a model loaded from Model File.

Note: The selected model has to be of the same database platform and version. You cannot compare DB2 10.5 and MySQL 5.1 models, or MySQL 5.1 and MySQL 5.5 models.

  • Check Resolve Application Variables to resolve variables during generation including variables in names
  • The next dialog presents you with a couple of setting related to Model Comparison. When you're done editing the options, click on Next.

  • Now you have to choose what types of objects will be included in the comparison. If you wish to select objects in more detail, click on Detailed Settings.

  • You are now presented with Compare Tree dialog displaying all differences between the two given models. Now you have the option to generate a report by clicking on Report button.

  • Check the Review screen which shows you the final statistics and close Model Compare by clicking the Finish button.

TIP:If you plan to do this action again in the future, you might want to click the Save Action button to save this Action Definition. See Model Actions for more information.

  • Select Format of the generated report.
  • There are two options available during the entire process of generating a report. Hide Log hides the bottom part of the dialogs which otherwise displays information related to the generation. Checking Close after Execution closes the dialog window after you're done creating your report.

Generating HTML Reports

  • After you select HTML from previous dialog and click on Next, you have the option to change Report Caption and Language of the report.

nOTE: You can generate Reports in many languages provided that you have the appropriate Dictionaries installed. For more information see Dictionaries.

  • You are able to define your own path where the generated report will be saved, if you need to.

  • In this dialog there are several options for customizing the look of the report.
Select Layout Page Description

Report Layout

  • Frameless (for Internet Explorer 7 and FireFox 2 users. Reports with Frameless layout is not displayed in older browsers correctly.)
  • Frames - Top menu
  • Frames - Left menu

Note: For large models, Frames - top menu or Frames - left menu options are recommended. (Frameless report layout is not recommended as it uses Java script that goes through all objects, which takes too much time if your model is large.)

CSS Style

Available styles for HTML report:

  • Default
  • Others (select the most convenient for you)

TIP:You can also create your own report styles using CSS. To integrate a custom style into Toad Data Modeler, you have to create a .txs file referencing the style resources and save it to Style folder.

  • Now is the time for you to decide what kind of information should the generated report contain. If you want to get a complete report, check all boxes. For easier management use buttons located on the top.

  • The final dialog where you can set a few last options.
  • Click on Execute to generate the report. A dialog window will inform you when the process is done.

Generating RTF and PDF Reports

  • On this dialog you are able to change the report caption and its language.

nOTE: You can generate Reports in many languages provided that you have the appropriate Dictionaries installed. For more information see Dictionaries.

  • You are able to define your own path where the generated report will be saved, if you need to.

  • A few options regarding the report look are available here. Also note the option to insert text or image watermark.

  • Now is the time for you to decide what kind of information should the generated report contain. If you want to get a complete report, check all boxes. For easier management use buttons located on the top.

  • A few final options are available here. For example, you can decide between generating Summary only, Details only, or both.
  • Click on Execute to generate the report. A dialog window will inform you when the process is done.

What to Report Dialog Buttons

Option Description

Selection Menu

Allows you to save time by loading saved selection.

For example, you may decide that you only want your report to cover Entities, some properties of Relationships and After Scripts. Selecting these items every time you generate a report would be frustrating. That's why you have the option to check these items once and then save the selection and load it every time you generate another report.

Save Selection

Saves the current selection. If a selection is chosen in the Selection Menu, it will be rewritten.

Load Selection

Load the selection chosen in the Selection Menu.

Save as New

Saves the current selection as a new selection. You will be prompted to enter a name.

Delete Deletes the selection chosen in the Selection Menu.
Set as Default Sets the selection chosen in the Selection Menu as default. Such selection is marked by an asterisk in Selection Menu.
Select All Selects all items.
Deselect All Deselects all items.
Inverse Selection Inverts the selection.
Auto Check

On: Checking/Unchecking an item will also check all its sub-items.

Off: Checking/Unchecking an item will not check its sub-items.

Compare Tree Overview

Option Description
Generate a Change Report.
Expand All.
Collapse All.
Go to next object in the tree.
Go to previous object in the tree.
Display options for objects.
Display options for properties.

Source Model

The source model to which the second model is compared to.
Model to Compare The model which is compared to the source model.

Compare Tree Icons

Difference between Source model and Compared model.

Right-click an item to see the following options:

Option Description
Expand All Children Expands all sub-items of the selected item.
Collapse All Children Collapses all sub-items of the selected item.

Model Update

Toad Data Modeler allows you to update models to synchronize changes between your database and your model.

Example: You loaded the database structure of your database to Toad Data Modeler (Reverse Engineering). - Model A was created. Then you made some changes in your database and now you want to update the Model A. For this purpose, you can use the Model Update feature.

Model Update includes operations such as Connections, Model Comparison and Model Merge

To update your model

  • Open the model that you want to update.

  • Click the button on the Menu Toolbar or Select Model | Update Model from Database to open the Model Update Wizard

Note: You might be prompted to enter a password to connect to the database. This can be changed by checking Save Password checkbox in specific Connection settings.

  • Select the objects you want to update in your model. You can use the Filter and Select All/Deselect All/Invert Selection buttons for easier object management. Also note that you can Export or Import your selection to or from a file. After you're done selecting, click on Execute button.

  • Before Toad Data Modeler compares your model with the database, you need to go through some settings related to comparison.

  • Here you can select what types of objects should Toad Data Modeler compare between given model and the database. If shown settings aren't detailed enough for you, click on the Detailed Settings button. This will allow you to choose not only from all groups of objects, but from the objects themselves. To update everything in your model, check every checkbox or select Compare All.
  • After you're done with selecting the objects, proceed to the next dialog.

  • You are now able to see the Compare Tree. It displays all differences between your current model and the database. The Model Update changes the model on the right (your model) so it matches the model on the left (the database). Check the changes you would like to execute and proceed to the next dialog.

  • The last dialog shows you the review of the Model Comparison. An option to Merge Model is available in case you don't want to only update your model. This option creates a new model which will appear in your Application View after finishing the update. Click on Finish and after a short while your Model will be updated.

Compare Tree Overview

Option Description
Import selection from a saved file.
Save selection to a file.

Runs the verification process.

The verification might return a warning . Details are available in Verification Log.

Checks all items.
Unchecks all items.
Checks all items that exist in the Source model, but not in the Target model (CREATE).
Checks all items that does not exist in the Source model, but exist in the Target model (DROP).
Checks all items that exist in both models, but are different (ALTER).
Opens the Wildcard Dialog where you can define settings for bulk selection/deselection of the Action box of the items listed on page Select Items.

Refresh Necessitated Items

Explanation: Some objects are related together (e.g. entity and domain, entity and relationship). Let's say you uncheck a Domain in Select Object Types dialog. However you keep an Attribute of the Domain type checked for conversion. In the next screen the Domain will be selected for conversion (and highlighted in gray), even if you don't want it to. This is because of its relationship with the Attribute, which cannot exist without the Domain.

Now, if you uncheck the Attribute, the Domain will still be checked for conversion. This is where you use this button. It runs through all checked objects and removes the Domain highlighted in gray since the Attribute is no longer checked. That means the Domain is no longer necessary, since it has no relationships with currently checked objects and you unchecked it in Select Object Types dialog.

Expand All.
Collapse All.
Go to next object in the tree.
Go to previous object in the tree.
Display options for objects.
Display options for properties.Source

Source Model

The source model (often the database).
Model to Merge The model which is going to be updated.
Operation

Check this checkbox toupdate the item.

Uncheck this checkbox to not update the item.

 

Compare Tree Icons

Difference between Source model and the updated model.

This object exists in Source model but does not exist in the updated model.

This object is missing in Source model but exists in the updated model.

Right-click an item to see the following options:

Option Description
Check All Children Checks the Operation checkbox of all children items.
Uncheck All Children Unchecks the Operation checkbox of all children items.
Check All Children to Add

Checks the Operation checkbox of children items that exist in Source model but not in the Target model ( items) to generate the CREATE statement.

Check All Children to Remove Checks the Operation checkbox of children items that are missing Source model and exist in Target model ( items) to generate the DROP statement.
Check All Modified Children Checks the Operation checkbox of children items where properties differ (items) to generate the ALTER statement.
Uncheck All Children to Add Unchecks the Operation checkbox of children items that exist in Source model but not in the Target model ( items) to not generate the CREATE statement.
Uncheck All Children to Remove Unchecks the Operation checkbox of children items that are missing Source model and exist in Target model ( items) to not generate the DROP statement.
Uncheck All Modified Children Unchecks the Operation checkbox of children items where properties differ (items) to not generate Change Script for this change.
Wildcard Filter Opens the Wildcard Dialog where you can define settings for bulk selection/deselection of the Operation box of the items listed on page Select Items.
Expand All Children Expands all sub-items of the selected item.
Collapse All Children Collapses all sub-items of the selected item.

 

 

 

Synchronization

Toad Data Modelerhelps you with synchronization tasks and allows you to:

  • Generate SQL scripts representing changes made to your model
  • Transfer changes made in your database to your existing physical models
  • Keep your model and your database synchronized and up-to-date during the development process

Database and Model Synchronization

Situation A - Generation of Change Scripts

You have made changes in your physical ER diagram in Toad Data Modeler and now you need to apply the changes to your database.

Solution: Compare the models and generate Alter Script in Toad Data Modeler. Then use a third party software to connect to your database and execute the generated script. All your changes are now reflected in database. (Note: Change Script generation is not supported for all databases, for further information see Supported Databases.)

Situation B - Model Update

You or someone else made changes to your existing database (development version of database) and you need to transfer the changes to your model.

Solution: Use the Model Update or Model Merge features to update your physical model.

Physical Model and Logical Model Synchronization

You have created a Logical model in Toad Data Modeler and need to convert the logical model to physical model for the selected database system.

Solution: Use the Model Convert or Simple Model Conversion feature for logical (LER) to physical (PER) model conversion.

Note: If you make changes in your LER model and need to update the existing PER model, perform the LER to PER conversion again and then compare the two PER models.

Limitations

There are many factors that affect synchronization features in Toad Data Modeler. For example:

  • Data types can be defined through logical types that do not exist in physically existing databases, such as Domains, Dictionary Types etc.Toad Data Modeler has to correctly resolve the data types when comparing attribute (table column) properties.
  • Objects must be paired by specific rules, for example in your Oracle Database model, schema assignment is not required, but in your database, schema is always present.
  • There can be application variables defined in your models, for example <%ParentTableName%> and the application variables must be resolved during the comparison as well.

In result, the synchronization of your model and your database is more complicated than comparison of two physically existing databases. From technical point of view, the comparison is based on more complicated algorithm that requires more resources.

Recommendations

  • Use the synchronization features on models of small to average size. If you work with large models, try to split the model to smaller parts (separate models), if possible.
  • Use specialized software for comparing and synchronizing two large physically existing databases. You could theoretically reverse engineer both databases, create two models, compare them and try to generate change scripts, but due to limitations mentioned above the process would require more resources and take more time. Other products, that do not work with models, but rather with databases (e.g. Toad for Oracle, Toad for SQL Server, etc.), can offer better performance and additional benefits - like comparison of physical properties and data. Toad Data Modeler is a tool primarily used to create a visual representation of database structure and it should be used during database development. It is not an alternative or competitive tool for specialized schema comparison.

Model Size Limitations

  • Toad Data Modeler doesn't have fixed limitations based on number of objects. You can work with models that contain hundreds, or thousands of tables. Please note that large number of objects in a model may hinder performance significantly, depending on your computer specifications. Number of other items in your model (stored procedures, functions, sequences etc.) affects the performance as well. Other factors include:
    • Type of task - you can model large database structure, generate complex SQL scripts or detailed reports, but synchronization tasks might reach the application limits sooner than generation of SQL scripts, for example.
    • Number of running processes on your machine.
    • Limited access to Microsoft Scripting Engine (standard part of operating system, however in some situations access can be monitored by antiviral software etc.)
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating