Procedure Generation

Prerequisites

Procedures can only be generated from templates so you must either have installed an Enablement Pack that supports Procedures, which will have templates included, or create your procedure templates.

On supported platforms, WhereScape RED generates template procedures to assist in the various phases of the data warehouse build process. A procedure is generated by selecting the type as Procedure and then selecting (Build Procedure...) option from a drop-down list field in a table's Properties window to configure the update, custom, initial build, or post-load procedures. 

Procedures can either be manually generated from a RED-provided procedure outline or generated leveraging a RED template—refer to Rebuilding Update Procedures for details. 

Suppose a new procedure is created from scratch (i.e. not auto-generated). In that case, an outline of the syntax required by the WhereScape scheduler can be generated by selecting the Tools > Create Procedure Outline menu option in the procedure editor.

Wrapper procedures

In some cases, multiple procedures will be required to update a table. In such cases, it is best to create a top-level procedure that is seen by the scheduler as the 'Update' procedure. This procedure can in turn, call other procedures.

Procedure Placeholders

The following procedure placeholders are used for compiling procedures to the required target database location and referencing other data warehouse objects defined in the RED metadata. Placeholders are particularly useful when deploying between environments ensuring the objects referenced are fully qualified to suit the environment being deployed to:

1. [SCHEMA.procedure_name].procedure_name = resolves to schema.procedure_name during the compilation process of the procedure.
   This placeholder is used in the CREATE PROCEDURE statement to resolve the schema location for the procedure.
2. [PROCEDUREOWNER].[procedure_name] = resolves to database.schema.procedure_name during the compilation process of the procedure.
   This placeholder is used to fully qualify procedures, based on the target location of the referenced procedure.
3. [TABLEOWNER].[table_name] = resolves to database.schema.table_name or schema.table_name depending on the target location settings of the referenced object.