The fields in the Database Link Load Properties screen are described below:

Fields

Description

Load Table Name

The table name is limited by most relational databases to a maximum of 30 characters and must be unique. Table name defaults can be set up in Tools > Options to define a prefix or a post fix that can be added in order to identify clearly that this is a load table. Example: load_customer. By default, WhereScape RED uses the prefix load_ for Load tables.

Unique Short Name

The table short name is limited in size to 22 characters in Oracle and SQL Server and 12 characters in DB2. It must be unique. The short name is used in the naming of indexes, keys, procedures and scripts.

Description

Enter a description of the table. This description appears in the documentation that can be generated, once the data warehouse is built.

Connection

Enter the connection being used to get the data. The connections for Load tables can be changed in bulk, refer to Changing load Connection and Schema for details.

Load Type

The load type is typically defined by the connection, and should not normally be changed. This drop-down shows all valid load types for the connection.

Database Link

The database link is displayed in this field.

Script Template

The script template used for a script based load.

Script Name

This field is only active for script-based loads.

When doing a Script based load, use the Rebuild button after selecting the relevant script to be rebuilt from the Script Name drop-down list.

Pre-load Action

Select an action to be performed on the table before the load occurs. Options are:

  • Truncate
  • Execute Pre-Load SQL
  • Both Truncate and Execute Pre-Load SQL
  • No action

Pre-load Sql

If a Pre-load Action of Execute pre-load Sql was selected, then the SQL statement to execute before the load takes place should be entered in this box.

Truncate Options

Oracle Only: WhereScape RED enables controlling the Oracle TRUNCATE options, such as REUSE/DROP STORAGE for load processing.

  • Add a Truncate option for Load table processing by typing it in the Truncate options box in the Load table Properties screen.
  • Truncate options can also be added to the procedure generation in the Define Procedure Type window.

    The contents of Pre-Load SQL can be an SQL statement or a procedural block.
  • If using a single statement then the trailing semi-colon is not required for Oracle.
  • If using a procedural block then the final semi-colon is required. The following examples illustrate the possible values in this field. Note the trailing semi-colon on the procedural block example.

Example of pre-load statements:
delete from load_customer where code < 23

truncate table load_customer

DECLARE
 v_status number;
 v_result varchar2(256);
 BEGIN
 update_dim_customer(1,'a','b',0,0,v_result, v_status);
 END;

Not all preload SQL commands works in a SQL Server database. These commands are executed as a batch. For example: create trigger as the second command will fail because it is not supported in batch mode, unless it is the first command.


Table Properties clauses (e.g. Partition)

These are clauses that are added to the end of the table create statement. Typically used for putting partition information on the table. In Oracle, this can also be used to add a parallel clause or compression clause to the table.

Post Load Type

This field is only available for custom database targets


Enables you to specify the type of post load to execute for the table. Options are Procedure or Script.


Post Load Procedure

A procedure that is executed immediately following the load. If you execute an externally loaded table, no load occurs, but a post load procedure can still be executed. Post load procedures can either be manually generated from a RED provided procedure outline or generated using a RED template—refer to Rebuilding Update Procedures for details.

Post Load Script

This field is only available for custom database targets and is only displayed when the Script option is selected from the Post Load Type field above.

A script that is executed immediately following the load. If you execute an externally loaded table, no load occurs but a post load script can still be executed. Post load scripts can be generated using a RED template—refer to Rebuilding Update Procedures for details. 


Three fields at the bottom of the Load Table Properties screen display date information:

  1. Date table structure last updated.
  2. Date created in database.
  3. Date last updated in database.