Creating Models › Creating a Physical Model › Scripts

Scripts

SQL scripts are executed either before or after the rest of the schema is generated, or included before or after the CREATE TABLE statement.

It is necessary to write scripts to provide for proper schema generations, but it also can involve a lot of copying, pasting, and renaming to put it all in place. Macro support lets you generate scripts by writing these scripts using a combination of regular SQL and, whenever possible, macros to guarantee reusability and avoid the repetitive copying, pasting, and renaming. Scripts are supported for:

• Schema Generation. You can create a script and execute it at the beginning or end of the schema generation script.
• Tables. You can create a script and include it before or after the CREATE TABLE statement in the schema generation script. For DB2 LUW and DB2 z/OS, scripts are supported for materialized query tables.
• Views. You can create a script and include it before or after the CREATE VIEW statement in the schema generation script.
• Materialized Views. If your target server is Oracle, you can create scripts for materialized views.

You can create scripts using templates, which can contain both SQL code and macros. The macros help you add the name and properties of objects in the script code. The macros are expanded based on the current state of a model when it generates the schema to the target database.

Scripts have the following characteristics:

• Become part of your DDL script
• Are used to automate a certain amount of your DDL script code
• Are written using a combination of regular SQL code and CA ERwin Data Modeler macros
• Can be used for internal model checks/validation (cannot be forward engineered)

CA ERwin Data Modeler macros provide a method to create scripts that are reusable. Here are some examples of the script use:

• Create user permissions at the model or table level
• Drop tables based on owner information
• Drop table constraints
• Create generic database views

Note: Scripts can be saved in a CA ERwin Data Modeler template for easy reuse.

More information:

Schema Script Example

DBMS Support for Scripts

Add a New Script

Define a Pre & Post Script

Define Script SQL Code

Enter a Script Comment

Define Script User-Defined Property Values