Chat now with support
Chat with Support

Toad Data Modeler 6.3 - User Guide

Introduction User Interface Models and Model Objects
Physical Data Model
About Physical Data Modeling Benefits of Physical Data Model Entity Relationship Diagram Objects Basic Database Design Advanced Database Design
Universal Data Model Logical Data Model Working with Model Objects Model Properties Model Statistics
Features and Tools
Application Variables Export/Import DDL Script Generation Graphics Model Actions Print Projects Reports Reverse Engineering Scripting and Customization SQL Templates Tips and Tricks Toad for Oracle Integration Toad Intelligence Central (TIC) Integration Tools Version Control
Options and Configuration Databases
Supported Databases Connection Types by Database DB2 UDB v. 8 (LUW) DB2 v. 9 (LUW) DB2 v. 9.5 (LUW) DB2 v. 9.7 (LUW) DB2 v. 10.1 (LUW) DB2 v. 10.5 (LUW) DB2 z/OS v. 9 DB2 z/OS v.10 DB2 z/OS v. 11 Greenplum 4.1 Greenplum 4.2 Ingres 9.3 Ingres 10.0 Microsoft Access 2000/2002/2003 Microsoft Access 2007/2010 Microsoft Azure SQL Database Microsoft Azure SQL Database V12 Microsoft SQL Server 2000 Microsoft SQL Server 2005 Microsoft SQL Server 2008 Microsoft SQL Server 2012 Microsoft SQL Server 2014 Microsoft SQL Server 2016 MySQL 5.0 MySQL 5.1 MySQL 5.5 MySQL 5.6 MySQL 5.7 Oracle 9i Oracle 10g Oracle 11g Release 1 Oracle 11g Release 2 Oracle 12c Release 1 Oracle 12c Release 2 PostgreSQL 8.1 PostgreSQL 8.2 PostgreSQL 8.3 PostgreSQL 8.4 PostgreSQL 9.0 PostgreSQL 9.1 PostgreSQL 9.2 PostgreSQL 9.3 PostgreSQL 9.4 PostgreSQL 9.5 SQLite 3.7 Sybase ASE 12.5 Sybase ASE 15 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
Legal Information

Model Update

Features and Tools > Model Actions > 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

Features and Tools > Model Actions > 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.)

Print

Page Setup

Features and Tools > Print > Page Setup
  1. Select File | Page Setup.
  2. Select page size, margins, orientation etc.

To define the page size on your own, select Custom from the Page box. Define Height and Width on the right.  

Note:

  • The measure unit can be set in Settings | Options | General | Select Unit of Length - inches or millimeters.
  • Page Format settings are saved with a model.

 

Option Description

Fit to Page

Select this option to print the entire model on one page. This option is model-dependent.

Enlarge

Select a scale of your model for print.

Portrait/Landscape

The option selected in the Page Format dialog will be automatically set in the Printer Properties dialog. You can set a different orientation for every model, the option is model-dependent.

Printer

Select your printer.
Then in the Page box you can see formats that the selected printer supports (enabled items) and other formats (disabled items). (This feature should solve problem with printing on plotters.)

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating