The new Validate Against DB functionality works in the following stages:

StageOperationsConfiguration
Validate
  1. Create the expected table, under a different name, based on the metadata.
  2. Fetch information about the expected and actual tables.
  3. Compare the actual and expected information.

Table/column information SQL Block Procedure.

Configured on the Target Connection.

Alter
  1. Generate a SQL Block procedure using a template.
  2. Execute the SQL Block.

Alter DDL Template.

Configured the Table.

The default is configured on the Target connection.

Create DDL

The Create DDL for the table to be validated/altered MUST use any of the following tokens to refer to the name of the table: $OBJECT$, $TABLE$ or [TABLEOWNER].[table_name].

Without these tokens, the temporary "expected" table cannot created reliably as only these tokens can be replaced with temporary table names for the temporary table creation.

  • The Create DDL is usually generated by a template and so these tokens need to be specified in the template itself unless using the Override Create DDL.

  • The generated Create DDL for a temporary table is validated to make sure it contains the unique temporary table name. If not for interactive validation the user is warned and given the chance to continue but for application deployment, the validation is skipped to avoid the potential dropping of a data warehouse table

Drop Table DDL

The Drop Table DDL statement MUST use any of the following tokens to refer to the name of the table: $OBJECT$, $TABLE$. Otherwise, the dropping of the temporary table after the validation process may drop the actual data warehouse table.

Table/Column information SQL Block

These are some of the characteristics that this procedure needs to be created:

  • This is a SQL Block Procedure object that is configured on the target connection.
  • SQL Blocks are a sub-type of the Procedure object in RED.
  • The SQL Block must contain exactly two (2) separated statements by the configured end-of the statement string (<EOS>, by default).
  • Each statement must return one result set.
  • Each result set must contain the specified columns below in the correct order.
  • The data types shown below are the maximum sizes to be processed.
  • Additional information is returned in triples of extra columns at the end of the result set. This additional information is compared during validation and is provided to the template used to generate alter DDL.

Statement 1 - Table information

ColummTypeDescription
1VARCHAR(128)catalog
2VARCHAR(128)schema
3VARCHAR(128)table name
4 + (i * 3)VARCHAR(128)additional information key
5 + (i * 3)VARCHAR(128)additional information description
6 + (i * 3)VARCHAR(128)additional information value

Where i ranges from 0 to the number of additional information keys are exclusive.

Statement 2 - Column information

ColummTypeDescription
1VARCHAR(128)catalog
2VARCHAR(128)schema
3VARCHAR(128)table name
4INTEGERordinal position
5VARCHAR(128)column name
6VARCHAR(128)data type
7 + (i * 3)VARCHAR(128)additional information key
8 + (i * 3)VARCHAR(4000)additional information description
9+ (i * 3)VARCHAR(128)additional information value

Where i ranges from 0 to the number of additional information keys are exclusive.

The following example uses the standard information_schema views. It is also the default if nothing is configured

SELECT table_catalog, table_schema, table_name
        FROM information_schema.tables
       WHERE UPPER(table_catalog) = UPPER('$DATABASE$')
         AND UPPER(table_schema) = UPPER('$SCHEMA$')
         AND UPPER(table_name) = UPPER('$TABLE$')
       ORDER BY table_catalog, table_schema, table_name
<EOS>
      SELECT table_catalog, table_schema, table_name, ordinal_position, column_name,
             CONCAT(data_type, CASE WHEN COALESCE(character_maximum_length, numeric_precision, datetime_precision) IS NOT NULL
                                    THEN CONCAT('(',
                                                CONCAT(CAST(COALESCE(character_maximum_length,
                                                                     numeric_precision,
                                                                     datetime_precision) AS VARCHAR(20)),
                                                       CONCAT(CASE WHEN numeric_scale IS NOT NULL
                                                                   THEN CONCAT(', ', CAST(numeric_scale AS VARCHAR(20)))
                                                                   ELSE ''
                                                              END,
                                                              ')')))
                                    ELSE ''
                               END) data_type,
             'COLUMN_DEFAULT', 'Default Value', column_default,
             'NULLABLE', 'Nullable', is_nullable
        FROM information_schema.columns
       WHERE UPPER(table_catalog) = UPPER('$DATABASE$')
         AND UPPER(table_schema) = UPPER('$SCHEMA$')
         AND UPPER(table_name) = UPPER('$TABLE$')
       ORDER BY table_catalog, table_schema, table_name, ordinal_position

Alter DDL Template

  • This is a template that is configured on each table.
  • The default for new tables is taken from the default template configured on the target connection.
  • The template must be set as Type: 'Alter'
  • The result of the template is interpreted as a sequence of SQL Blocks separated by two newlines.
    • Each SQL Block is executed independently; the failure of one does not prevent the execution of the following.
    • Within each SQL Block there can be multiple statements separated by "<EOS>" or the configured separator.
      • A failure of a statement within an SQL Block does prevent the execution of the following statements in that block.
    • The usual variables are available, e.g. table, options, env.

In addition, the following variables are available:

VariableDescription
changesAn array of objects describing how the actual table is different from the expected table. Each element has type Change.
actualTableAn object containing the table/column information obtained for the actual table. Has type TableInformation.

'Change' object type

FieldDescription
changeTypeOne of the enum values in Types. ChangeType; determines the type of the object under data.
dataAn object containing the details of the specific change; the type depends on the value of changeType.

Change types

Change typeDescriptionFields in data
Types.ChangeType.TableCreationIndicates that the actual table does not exist.(none)
Types.ChangeType.TableAdditionalInformationChangeIndicates that one of the additional information fields returned by the Table Information statement differs.
changedKeyThe "key" of the additional information.
changedDescriptionThe "description" of the additional information.
actualValueThe "value" of the additional information in the actual table.
expectedValue The "value" of the additional information in the expected table.
Types.ChangeType.ColumnOrderChangeIndicates that the columns differ only in their order.(none)
Types.ChangeType.ColumnAdditionIndicates that one of the columns in the expected table is not present in the actual table.
columnNameThe name of the column in the expected table.
Types.ChangeType.ColumnDeletionIndicates that one of the columns in the actual table is not present in the expected table.
columnNameThe name of the column in the actual table.
Types.ChangeType.ColumnNameChangeIndicates that the name of one of the columns differs.
columnNameThe name of the column in the expected table.
actualColumnNameThe name of the corresponding column in the actual table.
Types.ChangeType.ColumnDataTypeChangeIndicates that the data type of one of the columns differs.
columnNameThe name of the column (in the expected table).
dataTypeThe data type of the column in the expected table.
Types.ChangeType.ColumnAdditionalInformationChangeIndicates that one of the additional information fields returned by the Column Information statement differs for one column.
columnNameThe name of the column whose additional information differs.
changedKeyThe "key" of the additional information.
changedDescriptionThe "description" of the additional information.
actualValueThe "value" of the additional information in the actual table.
expectedValueThe "value" of the additional information in the expected table.

'TableInformation' object type

VariableDescription
catalogname of the catalog containing the table
schemaname of the schema containing the table
namename of the table
additionalInformationarray of AdditionalInformation objects
columnsarray of ColumnInformation objects

'ColumnInformation' object type

VariableDescription
namename of the column
dataTypedata type of the column
additionalInformationarray of AdditionalInformation objects

'AdditionalInformation' object type

VariableDescription
keyadditional information "key"
descriptionadditional information "description"
valueadditional information "value"
  • No labels