Physical Modeling › Scripts

Scripts

Prescripts and postscripts are SQL scripts that you want to execute before or after the rest of the schema is generated, or include before or after the CREATE TABLE statement.

It is necessary to write prescripts and postscripts 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 prescripts and postscripts 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. Prescripts and postscripts 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 UDB, 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 an model when it generates the schema to the target database.

Prescripts and postscripts 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 prescripts and postscripts that are reusable. Here are some examples of the use of prescripts and postscripts:

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

Note: Prescripts and postscripts can be saved in an CA ERwin Data Modeler template for easy reuse.

More information:

Example of Schema Prescript

DBMS Support for Scripts

Add a New Pre & Post Script

Define a Pre & Post Script

Define the Pre & Post Scripts SQL Code

Define the Pre & Post Scripts Expanded SQL Code

Enter a Comment for a Pre & Post Script

Add a User Defined Property Value for a Pre & Post Script