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 Verification

Features and Tools > Tools > Model Verification

Toad Data Modeler allows you to verify your models. You can choose the items you want to verify, find out what errors and flaws your model contains and use quick fixes to resolve any issues.

To verify your model

  1. Select  Model Menu | Verify Model... (also CTRL+F9).
  2. On the Model Objects tab you can mark items for verification. Select either entire object group types or single objects.
  3. On the Verification Settings tab select the rules that should be part of the verification. To save the settings, click Save & Verify.
  4. The result of the Model Verification is shown in Verification Log, which is displayed automatically. To display the log manually, select Windows Menu | Verification Log. Objects that did not pass the verification are also marked by error/warning icons in Model Explorer.

Model Verification Form

Model Objects tab - lists all objects and object group types in your model. Checked object are those that will be verified.

Verification Settings tab - Here you can enable/disable rules that should be considered during verification. Use the buttons on the top of the form to check/uncheck all errors/warnings/hints at once.

Rules are database dependent, the following screenshot shows rules for Microsoft SQL Server 2012.

Verification Log

Verify Model results are displayed in Verification Log.

Toad Data Modeleroffers Quick Fixes - recommended solutions for problems and issues found by Model Verification. Quick Fixes are accessible for problematic objects both in Verification Log and Physical Model Explorer, in right-click menu.

The Verification Log toolbar contains several buttons:

Button Option Description
Tree View Switches Verification Log to tree view where the problem description, affected objects and quick fix suggestion can be all seen. Note that you cannot ignore messages or remove them from ignored in this view.
Show Errors/Warnings/Hints When enabled, Errors/Warnings/Hints are shown in Verification Log.
Ignore Message Marks the selected message as ignored.
Remove Message from Ignored Removes the selected message from ignored list.
Show Ignored Messages

Shows/hides ignored messages.

Verify Form Displays Model Verification dialog.
Verify Verifies model using the last saved settings.
Save Verification Log Saves all verification messages (including the ignored ones) to a CSV file.

On Form Verification

Errors, warning and hints can be displayed directly in the Object Properties form, at the bottom. Every time you make changes to the object and confirm, the object is verified and all issues are displayed at the bottom, similar to the screenshot below. Quick Fixes are not available in this mode.

On Form Verification can be enabled/disabled in Settings Menu | Options | Model . You can also decide what kind of verification messages should be shown - errors/warning/hints.

Syntax Validity

SQL Preview tab now highlights errors in SQL script syntax.

  • Check Syntax Validity to perform syntax checks in SQL Preview for the following objects:
    • Procedure, Function, View, Materialized View
    • User Data Type, Package (Oracle only)
  • Options are available in Options | Model | Physical Model | Verification | SQL Syntax Options
  • Syntax Validity check is supported for the following databases:
    • IBM DB2 LUW, IBM DB2 z/OS, Microsoft Azure SQL Database, Microsoft SQL Server, MySQL, Oracle, PostgreSQL, SAP Anywhere, SAP ASE, SAP IQ, Teradata

Settings

Model Verification can be configured for each specific database. Go to Settings Menu | Options | Model | Physical Model | *Target Database*.

In the Verificator frame you can enable/disable verification rules or change their category, e.g. Empty Object can be shown as an error instead of warning.

Syntax validity check may be disabled in Settings | Options | Model | Physical Model | <database> | Syntax Validity checkbox

 

Refactoring Utility

Features and Tools > Tools > Refactoring Utility

Toad Data Modeler provides you with a tool that is able to refactor all references to an object when you change its name. For example, let's say you've changed name of an entity. Usually, you would have to go through the rest of the model and change the name in all the places where it is referenced. Refactoring Utility is able to do this automatically, saving your time.

Note: The utility refactors text properties only (e.g. AfterScript, BeforeScript, SQL tab, text Views...)

To open Refactoring Utility

  • Click Tools | Refactoring Utility

Renamed Objects

In section Previously Renamed Objectst (top-right), you can find objects that were renamed.

Use checkboxes to select what items you wish to use for current refactoring action.

Renamed objects will stay in section Previously Renamed Objects until you change names in other objects or until you enable icon in column Forget and click the Forget Selected Items button. (In this sample it should be used for EntityA).

Objects to Modify

In section Objects to Modify, you can find objects and properties that contain old names.

Current and Modified Code Previews

In section Current Code, the body of SQL code is shown. Use buttons Previous Term and Next Term to navigate among names in the same code.

Section Preview of Modified Code displays a preview of refactored code. Click any of the highlighted names and select old or new name or use the icons in middle column to reject suggested changes.

Note: Renaming from the default name (Entity1, Entity2...) is ignored deliberately.

Schema/Owner/Database Assignment

Features and Tools > Tools > Schema/Owner/Database Assignment

This tool allows you to assign or remove schema/owner/database to/from multiple object groups in your model at once.

To assign a schema/owner to your model

Select Tools | Schema/Owner Assignment.

Option Description

Select Schema/Owner/Database

Contains existing Schemas/Owners/Users in your model.

Select --None-- from the list to remove the existing schema from objects you mark in the Select types of objects section.

Assign to objects where value is not defined

Assigns schema/owner to objects, which have none assigned yet.

Confirm before assignment checkbox

Confirmation dialog for each object where schema is being assigned/removed will be displayed.

Select types of objects area

Mark objects which should be assigned a Schema/Owner.

Infer Relationships

Features and Tools > Tools > Infer Relationships

This feature tries to map Primary Keys or Alternate Keys to identically named attributes in other entities.

Conditions:

  • Mapping is performed between key and non-key attributes. In other words, if identically named attributes are part of primary key in various tables, e.g. ID column in table Customer and ID column in table Order, the two ID columns will not be mapped and no relationship will be created.
  • Names must be identical and data type must be the same, including parameters.
  • Non-identifying relationships are created by default. In case you need an identifying relationship, edit the automatically added relationship and change its type to identifying manually.

To run the Infer Relationship function

Select Tools | Infer Relationship.

Note: Relationships created this way will be marked with a To-Do task prompting you to check whether the relationship has been created correctly.

Example:

Model with no relationships. See the Customer ID column in tables Customer and Order Record.
In table Film there is an alternate key with two columns Title and Director. Identically named columns are in table Customer Rating.

Run the Infer Relationship function and see the result:

Infer Relationships and Reverse Engineering

The same tool can be used automatically during reverse engineering.

Create a new connection or edit an existing stored connection and check the Infer Relationships checkbox to activate this feature.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating