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

Change Script

Features and Tools > Model Actions > Generate Change Script > Change Script

When you need to copy changes made to your model to your database or another model, Change Script comes in handy.

Example: You made some changes in your Oracle 10g model Videorental and resaved it as model Videorental_Modified. (Let's say you added and deleted some items, changed some properties etc.). Now you want to generate complete Change Script, including CREATE, DROP and ALTER statements for the Videorental model.

You don't have to work with two models at once. You can modify the original model without saving it first, Toad Data Modeler allows you to connect to your database directly without prior complete reversing. That means when you're done modifying, you can simply compare the modified model with the database and generate Change Script. This is done using Generate Change Script tool found in Model Actions, or in Model Menu.

To execute the generated Change Script you have to use a third party software (e.g. Toad for Oracle). Toad™ for Oracle® as Default Editor

You cannot generate Change Scripts for every database platform. See Supported Databases for more information.

How to generate an Change Script

  • Click the Run Generate Change Script button on the Main Toolbar or select Generate Change Script from Model Menu or run Generate Change Script from Model Actions.

  • Select your Target database/model. You can use either model opened in Toad Data Modeler, model file or saved connection to your database.

  • Select Target objects you would like to include in your Change Script. 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.

  • Before Toad Data Modeler compares the two models, 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 models. 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 generate a complete Change Script for all differences (CREATE, DROP and ALTER statements), check all checkboxes available.
  • After you're done with selecting the objects, proceed to the next dialog.

  • Entities and attributes can be easily mapped here in the last step
  • Double-click the button () to map an entity or attribute to a target entity or attribute
  • The Description column displays the current state of the object. Objects can be unmapped
  • You are now able to see the Compare Tree. It displays all differences between the two given models. The generated Change Script modifies the model on the right (Target) so it matches the model on the left (Source). 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. You can set your own path for Change Script output. Click on Finish to generate the script.

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.

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

The updated or modified model.
Target The model for which you want to generate the Change Script.
Operation

Check this checkbox to generate Change Script for the difference.

Uncheck this checkbox to not generate Change Script for the difference.

Default selection: Default selection of the Action checkboxes depend on your settings on page Settings in the Options for Default Selection of Items area. If you select all the options in this area, the Action checkboxes will be selected for all changes (CREATE, DROP and ALTER) by default.

 

Compare Tree Icons

Difference between original model and Target model.

This object exists in original model but does not exist in Target model.

This object is missing in original model but exists in Target 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.
 

Change Script Generation - Temporary Tables

You can decide whether or not to use Temporary Tables during Change Script generation. The settings are available in Settings | Options | Physical Model | *Specific Database*| Change Script Settings.

  • Use Temporary Tables to Preserve Data – If you uncheck this checkbox, a sequence of commands DROP TABLE / CREATE TABLE will be generated instead of temporary tables. (The exception are the changes in data types where ALTER TABLE command is generated.)
  • Use Temporary Tables to Preserve Data on Data Type Change –This option is only available with Use Temporary Tables to Preserve Data option checked. Unchecking this option results in generating ALTER commands where there are changes in data types instead of using Temporary Tables.

Note: For databases Microsoft Azure SQL Database, Microsoft SQL Server, PostgreSQL, the following stands: If you uncheck the Use Temporary Tables to Preserve Data checkbox, temporary UDT/DictType will not be generated either. (They are created together with Temporary Tables.)

Simple Change Script

Features and Tools > Model Actions > Generate Change Script > Simple Change Script

Simple Change Script is a Change Script generated for one entity only. You might find it useful when you want to transfer minor changes in your model to your database.

To generate a Simple Change Script:

  1. Right-click the entity you want to generate change script for, choose Change Script | Run.

  2. In the following dialog choose the target data source. In our case we will use a stored database connection.

  3. When using a connection as the target, you have to select tables from the database in the next dialog. The tables you select here will be available for comparison with your model entity.


    Note: This dialog is not shown when using saved model file or opened model as the target. In these cases you don't need to choose tables, because all of them are already available for comparison.

  4. If you need to, you can change comparison settings in the next dialog.

  5. Since Simple Change Script only compares two entities, you don't need to configure Object Types at all and you can skip the dialog.

  6. The last configuration dialog contains DDL Code Generation related settings.

  7. When you use Simple Change Script, you also save its Action Definition. This allows you to reuse the feature with the exact same configuration. See Model Actions topic for more information. Otherwise, click Finish to proceed.

  8. Change Script dialog appears. You can compare your model entity to any of the entities you have selected in the wizard before. They are listed in the Destination Entity box. The comparison interface is the same as when using regular Change Script Generation. After each change you make in this dialog, you have to click Refresh button to see the script preview. When you are happy with the result, click Save to File to save your final script.

TIP: If you need to perform the same action again in the future, you can use the saved Action Definition. This allows you to skip all configuration and get to the Change Script dialog immediately.


Merge Models

Merge Models

Features and Tools > Model Actions > Merge Models > Merge Models

Toad Data Modeler offers you two options to merge your models:

  • Simple Model Merge - allows you to merge physical models quickly due to skipping several settings dialogs and Model Comparison. The target model will always be overwritten.
  • Model Merge in the Model Actions - allows you to see differences between two models, select particular items to merge and merge the models either to already existing model or to a new model.
  • Example: You work with several models. Use Model Merge to compare the models and merge them either to an existing model, or to a new model altogether.

How to Merge Models

Model Merge can be found in Model Actions (Window Menu | Model Actions), or in Model Menu | Merge Models | Run.

  • In the first step select either an opened model or a model from a 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.

  • 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.
    • Entities and attributes can be easily mapped here in the last step
    • Double-click the button () to map an entity or attribute to a target entity or attribute
    • The Description column displays the current state of the object. Objects can be unmapped

  • You are now presented with Compare Tree dialog displaying all differences between the two given models. Check or uncheck items to select what objects will be merged.

  • Check the Review screen which show you the changes for each object type. If you want to merge models into a new model, check Merge to New Model and enter its name.

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.

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

The updated or modified model.
Target The model for which you want to generate the Change Script.
Operation

Check this checkbox to generate Change Script for the difference.

Uncheck this checkbox to not generate Change Script for the difference.

Default selection: Default selection of the Action checkboxes depend on your settings on page Settings in the Options for Default Selection of Items area. If you select all the options in this area, the Action checkboxes will be selected for all changes (CREATE, DROP and ALTER) by default.

 

Compare Tree Icons

Difference between original model and Target model.

This object exists in original model but does not exist in Target model.

This object is missing in original model but exists in Target 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.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating