After you have designed your database you need to get the DDLs to be able to create the database objects. When generating the DDL scripts with Data Modeler you have two choices: generate just a single file or several files. Depending on your processes and your current need one of these could be much better option than the other.

To generate the DDLs select Export, DDL File from the File Menu:

 

Then select the RDBMS you want to generate the DDL for (Oracle, SQL Server or DB2 and the right version of it, in Data Modeler that is called a Database Site) and the relational model wanted. Press Generate.

Now select the objects you want to generate the DDLs for.

And with the selection in Generate DDL in Separate Files you define whether the DDL will be a single file (not selected) or generated in separate files (selected).

If you select Generate DDL in Separate Files and press OK, you are asked for the directory where the DDL files will be saved:

Select that and in that directory you will have several new subdirectories:

Each of these subdirectories will include files for creating an object of that type. For instance the Table subdirectory might look something like this:

Whether you chose Generate DDL in Separate Files or not, you will have one single DDL file shown in the tool:

and you can save that as a single file by pressing Save.

In short: if you do not select Generate DDL in Separate Files you will have the DDL in one file and you can either save it or not. If you select Generate DDL in Separate Files you will automatically have the several DDL files saved in the directory you have specified and you can save the single DDL file if you want.

Note: if the physical model is not open the DDLs will be very general without any physical properties. If the physical model is closed the DDL might look like this:

CREATE TABLE Customer
(
CustNO NUMBER (10) NOT NULL ,
Name   VARCHAR2 (100) NOT NULL
) ;
ALTER TABLE Customer ADD CONSTRAINT Customer_PK PRIMARY KEY ( CustNO ) ;

And if it is open it might look something like this:

CREATE TABLE Customer
(
CustNO NUMBER (10) NOT NULL ,
Name   VARCHAR2 (100) NOT NULL
)
PCTFREE 10 PCTUSED 40 TABLESPACE TableSpace1 LOGGING STORAGE
  (
    PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS UNLIMITED FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
  ) ;
ALTER TABLE Customer ADD CONSTRAINT Customer_PK PRIMARY KEY ( CustNO ) ;

 

A good skill to have with Data Modeler is JavaScript: using that you can change the way the tool works and you can automate many things. Where can I use those skills? There are two places in Data Modeler: Transformations and Table DDL Transformations.

Transformations

Transformations can be used to change the design. For instance while Engineering to Relational you could add columns to a table. Usually those columns are of a type of technical columns (for example creator, created, modifier, modified), or columns that should always exist for a type of table based on the methodology used (for example Data Vault defines certain columns to exist for certain kinds of tables: hubs, links and satellites). Or you can use them anywhere in your design to automate changes. To execute a script right-click on the Browser and select Apply Custom Transformation Scripts:

Select the script/scripts from the list and press Apply:

In short: Transformations are used to change the design programmatically.

 

Table DDL Transformations

Table DDL Transformations can be used to change the DDL Data Modeler generates. The design itself is not changed, only the DDL script. This can be used for example to generate the journaling mechanism (journaling tables and triggers) or DDL scripts for a RDBMS that is not supported by Data Modeler, for instance MySQL.

To use a script defined select it from the list in DDL Generation:

Define the tables that DDL generation you want to be affected by the script:

And Generate the DDL by pressing OK.

In short: Table DDL Transformations are used to change the DDL generated.

 

You can also design your sensitive data using Data Modeler.

In table properties you can define a redaction policy for the table:

In column properties (double-click the column name in table properties or press the XYZ icon) select Security.

In this tab you can design two main categories: Redaction and TSDP (Transparent Sensitive Data Protection).

In Redaction section you can define whether or not the column holds personally identifiable information (PII) (YES/NO), whether or not it holds sensitive information (YES/NO), the masking type used (FULL, NO, PARTIAL, RANDOM, REGEXP) and which masking template to use.

You can define your own masking templates by selecting Mask Templates Administration from Tools Menu:

 

After defining them they will be available in the list. Note that they must be of the same datatype as the column itself and of the same masking type selected to be seen on the list.

 

In TSDP section you can define the Sensitive Type used and type in the Sensitive Data Description.

You can define new sensitive types by right-clicking the Sensitive Types in Browser and selecting New Sensitive Type.

 

 

And now back to the Column Properties and the Security Properties. In TSDP Section we can select a Sensitive Type created as a sensitive type for that column, as well as Masking Type and Mask Template:

 

You can also define sensitive type for a Domain:

 

 

You can define new TSDP policies by right-clicking the TSDP Policies in the Browser and selecting New TSDP Policy:

They can include Sub Policies:

with Mask Types and Mask Templates etc.

You can also attach a Sensitive Type to the TSDP Policy:

 

After designing the sensitive data you might want to see a report of it.

The best way to do that is Search and Advanced Mode:

Select Contains sensitive information Property and true.

Now you can see all the columns that have been defined to hold sensitive data.

Press Report and select the format type preferred etc.

And here we are…

 

 

 

I want to compare a DDL and a design using File|Import|DDL File. I know my design is correct but the comparison always shows the DDL to be correct and suggests me to change the design. How to I tell the tool that my design is correct and I want to alter the database accordingly?

It is very simple. While importing the DDL select “Swap target model”

Now the comparison will show the design as the source (truth) and the DDL as the target (false). AND it will give you the ALTER DDLs to change the database. Simply press DDL Preview to get those ALTER scripts.

 

 

I was working on my logical model diagram but now I cannot see that canvas anymore. How can I get it visible again?

Right-click on the Logical Model in the Browser and select Show.

 

 

 

 

 

I would like to have my attributes in alphabetical order. Do I need to do it manually or is there a way to do it automatically?

There is a way to do it automatically. Right-click on the Logical Model canvas and select Sort Attributes. You can sort them either ascending or descending.

 

 

But are you sure you want them to be in alphabetic order? Usually it is easier to understand the model if the primary key attributes are first and usually we define the mandatory attributes in the beginning of the list.

 

 

I have defined several domains but when I try to use them I cannot see any of them in the list. What is wrong?

Most likely the reason is that you have the Preferred property enabled for logical datatypes and when you change to domains it stays enabled. You have not defined any domains and preferred domains and that is why the list is empty.

Either disable Preferred or define the domains as preferred in Preferences.