Once a Data Store object has been defined in the metadata and created in the database, an update procedure can be generated to handle the joining of any tables and the update of the Data Store object.

Note

Generating a Procedure

To generate a procedure:

  1. Right-click the Data Store object in the left pane and select Properties.
  2. Click the Rebuild button to start the process of generating the new procedure.
  3. A series of options are presented.

Processing Tab

Fields

Description

Template

Enables you to generate update procedures via a Rebuilding Update Procedures template.

Business Key Columns

Columns that define the business key for update processing. This is required for include Update options.

  • Clicking on the ellipsis button will bring up the Business Key selection screen.
  • A business key can be made up of multiple columns, but it must provide a unique identifier. Where multiple columns separately uniquely identify rows in the Data Store object, choose one to act as the primary business key.

For example, a source table may have a unique constraint on both a product code and a product description. Therefore, the description as well as the code must be unique. It is of course possible to combine the two columns, but the normal practice would be to choose the code as the business key. 

Tip

  • Use the column name ascending/descending buttons to sort column names. To revert to the meta column order, click on the meta column order button.
  • NULL Values - none of the columns chosen as the business key should ever contain a NULL value. See the note at the start of the Dimensions chapter.


Parameters

Any parameters selected are included in the generated update procedure as variables. The procedure will include code to retrieve the value of the parameter at run time and store it in the declared variable.
Clicking on the ellipsis button opens the Parameters selection screen.
The variables can also be used in column transformations and in the from/where clause for the update procedure. Some databases have a 30 character limit for variable names. WhereScape RED ensures the variables added for any parameters are less than 30 characters long by creating variable names in the form v_ followed by the first 28 characters of the parameter name.For example, a parameter called MINIMUM_ORDER_NUMBER_SINCE_LAST_SOURCE_LOAD will be available as the variable v_MINIMUM_ORDER_NUMBER_SINCE_L.

Tip

  • WhereScape RED parameters should be unique within the first 28 characters to avoid conflicting variables names.
  • If the desired parameter doesn't exist in the metadata yet, a new parameter can be added by clicking on the Add New button on the bottom leftmost corner of the Select Parameters dialog.
  • Refer to Parameters for more information.


Enable Parallel DML

Oracle Only. Adds all the code required to the update procedure to enable Oracle parallel inserts. Default for this option is not set.

Include Initial Load Insert

Adds an additional insert statement to the update procedure that runs if the target Data Store is empty. The benefit of this is improved performance inserting into an empty table without performing any checks to see if rows already exist. The default for this field is off (i.e. an initial insert statement is not added to the procedure).

Process by Batch

Allows users to select a column to drive data processing in a loop based on the distinct ordered values of the selected columns. The update procedure loops on this column and performs the delete, update and/or insert for each value. If the column chosen is a date datatype (date, datetime or timestamp), then the user is able to specify yearly, monthly, daily or column level looping. The default for this field is off (do not do batch processing).

Delete Before Insert

Allows selection of how to process deletes. It enables a delete statement to be added to the update procedure before any update or insert statement. This is a particularly useful option for purging old data and for updates based on a source system batch number. If this option is selected, the following options are also available:

  • Truncate Option – Optional Oracle TRUNCATE clause which is appended to the truncate statement. Add a truncate option such as REUSE/DROP STORAGE by typing it in the truncate options box.

Issue Warning if a Delete occurs

This option sets the procedure to a warning state if any deletes occur.

Delete Where Clause

The delete where clause is appended to the generated delete statement to constrain the rows deleted.

Process Method

Select between Insert/Update and Merge which enables you to use the Merge statement instead of two separate Insert and update statements.

Source Table Locking

Teradata only.Enables a locking request modifier to be specified for each source table. The specified locking request modifier is applied to each source table during generated update procedures. By default this is set to 'ACCESS' which locks each row being accessed, a blank entry will result in no locking clause in the generated procedure.

Include Insert Statement

Includes the insert statement in the procedure to insert new rows in the Data Store.

Insert Hint

Enter a database hint to be used in the INSERT statement. This is an Oracle and SQL Server only option. Defaults can be configured in Tools > Options > Default Update Procedure Options.

 Default is TABLOCK.

  Default is APPEND.

Insert New Rows Only

Uses change detection to work out what rows require inserting.

New Row Identification Method

Method used to identify that records in source are not currently recorded in the target table.

Existing Data Selection Hint

Database-compliant hint to be used for the existing data select statement.

Include Update Statement

Includes an update statement in the procedure to update changing rows in the Data Store. If this option is chosen, then the Update Changed rows only option is available.

Include Hint

Enter a database hint to be used in the INCLUDE statement. This is an Oracle and SQL Server only option. Defaults can be configured in Tools > Options > Default Update Procedure Options.

 Default is TABLOCK.

  Default is APPEND.

Update Changed Rows Only

Uses change detection to work out what rows require updating. Choosing this option, enables the Change Row identification Method.

Change Row Identification Method

Method used to identify that records in source have changed from what is currently recorded in the target table.

Existing Data Selection Hint

Database-compliant hint to be used for the existing data select statement.

Merge Hint

Enter a database hint to be used in the MERGE statement. This is an Oracle and SQL Server only option. Defaults can be configured in Tools > Options > Default Update Procedure Options.

 Default is TABLOCK.

  Default is APPEND.

Source Tab

Fields

Description

Distinct Data Select

Ensures duplicate rows are not added to the Data Store. This is achieved by the word DISTINCT being added to the source select in the update procedure. The default for this field is not set.

Select Hint

This field is only available when one of these two conditions is met: Oracle code generation and Template code generation.

Source Join

The From clause, including Source Join information.

Where Clause

The Where clause.

Group By

The Group By clause.

Simple Join

A simple join only returns rows where data is matched in both tables. So for example if table A has 100 rows and table B has a subset of 24 rows. If all the rows in table B can be joined to table A then 24 rows will be returned. The other 76 rows from table A will not be returned.

Outer Join

The outer join returns all rows in the master table regardless of whether or not they are found in the second table. So if the example above was executed with table A as the master table then 100 rows would be returned. 76 of those rows would have null values for the table B columns.

Note

  • When WhereScape RED builds up an outer join, it needs to know which table is the master table, and which is subordinate. Select the join column from the master table first. In the example screen above the table 'load_order_header' has had its column chosen and the column for the table 'load_order_line' is currently being chosen. This will result in the 'load_order_header' table being defined as the master, as per the example statement above. The results of this example select are that a row will be added containing order information regardless of whether or not a corresponding load_order_line entry exists.
  • When upgrading from a RED version previous to 6.8.2.0 and moving existing objects to a target location, all procedures that reference those objects will need to be rebuilt. Any FROM clauses will also need to be manually regenerated in order for the table references to be updated to the new [TABLEOWNER] form.

Building and Compiling the Procedure

  • Once the relevant options are completed, click OK. The procedure is built and compiled.
  • If the compile fails, an error is displayed along with the first few lines of error messages.
  • Compile fails typically occur when the physical creation of the table was not done. If the compile fails for some other reason, the best approach is to use the procedure editor to edit and compile the procedure.
  • The procedure editor highlights all the errors within the context of the procedure.
  • Once the procedure has been successfully compiled, it can either be executed interactively or passed to the scheduler.

Indexes

By default, a number of indexes is created to support each Data Store object.
These indexes are added, once the procedure has been built. An example of the type of indexes created is as follows:
 
Additional indexes can be added, or these indexes changed. Refer to Indexes for details.
 

  • No labels