The Target Settings tab for custom database connections provides additional fields for defining default DDL templates to use for the creation of tables, indexes, and views in custom database targets.

Please ensure that you have installed the WhereScape supplied templates or created your templates, before configuring the settings below.

Target Connection Settings

In addition to the standard settings described in the previous section, the following settings are available for custom database connections:

Field

Description

Display Data SQL

The generic syntax of a Display Data SQL statement should use RED placeholder parameters (e.g. $OBJECT$, $MAXDISPLAYDATA$).

The $MAXDISPLAYDATA$ parameter is derived from the User Preferences > Common > Look and Feel > General > Maximum rows returned for Display Data setting.


Row Count SQL

The generic syntax of a Row Count SQL statement which should use RED placeholder parameters (e.g. $OBJECT$).

Bulk Table Row Count SQL

The SQL statement is used to return the table name, schema name, and row count of all tables for the connection.For example: SELECT table_name, schema_name, row_count FROM information_schema.tables.The variable $DATABASE$ can be used. The SQL statement defined in this field is used in the Browser Connection screen if the Include Rowcount option is enabled.

  • The Include Rowcount option in the Browser Connection screen for Custom database connections is only available, if this field has been defined.
  • If the Include Rowcount option is enabled for a Custom database connection, then the SQL statement defined in this field is used to return the list of tables, schema, and row counts as the source of the row counts; instead of the SQL statement defined internally in RED.
  • The SELECT statement must follow the order "table_name, schema_name, row_count" as shown in the example above.


Default Table Create DDL Template

The default Template used for generating DDL for new Tables created on targets of this connection.

This template is used in the validation done for custom database targets during Application Load Processing performed in the WhereScape Setup Administrator. Refer to Compile New and Changed Procedures in the RED Installation Guide for details.


Default View Create DDL Template

The default Template used for generating DDL for new Views created on targets of this connection

Table/Column Information SQL Block

The SQL Block Procedure used to query Table/Columns information; should use RED placeholder parameters (e.g. $DATABASE$, $SCHEMA$, $OBJECT$).

Default Table Alter DDL Template

The default Template used for generating DDL to alter existing Tables created on targets of this connection

Drop-Table SQL

The generic syntax of a Drop Table SQL statement should use RED placeholder parameters (e.g. $OBJECT$).

Drop View SQL

The generic syntax of a Drop View SQL statement should use RED placeholder parameters (e.g. $OBJECT$).

Truncate SQL

The generic syntax of a Truncate SQL statement which should use RED placeholder parameters (e.g. $OBJECT$).

Default Index Create DDL Template

The default Template used for generating DDL for new Indexes created on targets of this connection.

Default Index Drop DDL Template

The default Template used for generating DDL to drop existing Indexes created on targets of this connection.

Default Index Validate Block Template

The default Template is used for generating a block of SQL Statements to validate indexes created on targets on this connection.

Default Windows Action Script Template

The default Template is used for generating a Windows Script to perform actions on objects created on targets of this connection.

Default Unix Action Script Template

The default Template is used for generating a Unix Script to perform actions on objects created on targets of this connection.

Enable Automatic Creation of Indexes

RED's default behavior is to create indexes when creating new objects.

Default Pre-Load Action

The default Pre-Load Action to use for Load tables created on targets of this connection. Options include:

  • Truncate
  • Execute Pre-load SQL
  • Both Truncate and Execute Pre-Load SQL
  • No action
    If Execute Pre-Load SQL is selected, then the SQL statement to execute before the load takes place must be entered—refer to Pre-Load Action in Database Link Load Properties Screen for details.

Default Update Script Connection

The default Script Connection for new Update Scripts in tables created using this connection. This setting is only displayed in connections that have a target database type for which Update Script functionality is enabled, e.g. Custom database type.

Column Properties Configuration

List view of the available Column Properties Configs.

The following can be used as RED placeholder parameters:

  • $OBJECT$
  • $DATABASE$
  • $SCHEMA$
  • $TABLE$

The DDL settings defined in the Table Properties > Storage tab overrides the DDL settings defined in the Target Settings tab for custom database connections above.


Options

Description

Add new Target Location

This option enables you to add new database/schema locations for objects in this connection. For Greenplum, Netezza and PDW schema target locations setup, refer to Database source system - Local/Linked Servers or ODBC connection topics, depending on the connection type chosen for the database source.

  1. Click the Add button to add the required target schemas for this connection.
  2. Give the new target a name and then enter the target's schema.
  3. Default schema location(s) for New Tables can also be set from the Tools > Options menu. Refer to Settings > Storage > Target Location for details.

The database and schema names for Custom database connections are used as follows: <database>.<schema>.object name Leave database name blank if not required. Leave schema name blank to use the default schema.


  • No labels