Data Store SQL Scripts

Each data-store-specific SQL script contains the following types of information:

Filenames

The file names used by Data Express to execute data-store-specific operations are:

  • pre_ConnectionAlias.sql - executes or sets options prior to Data Express processing
  • post_ConnectionAlias.sql - executes or sets options after Data Express processing
    where ConnectionAlias specifies:
    • the DSN (data source name) for the ODBC Extension
    • the Oracle TNS service name for the Oracle Extension

For example, with the ODBC Extension, if a given table is accessed via the MYSOURCE DSN and the target data store for that table is MYTARGET, the configuration file names would be named:

  • pre_MYSOURCE.sql
  • post_MYSOURCE.sql
  • pre_MYTARGET.sql
  • post_MYTARGET.sql

Configuration Options

Controlling configuration at the data store level allows data-store-specific SQL operations to be performed during before or after Data Express processing.

The following pre-processing configuration settings can be specified:

  • IdentifierDelimiter

    Specifies two characters to be used as an opening delimiter and a closing delimiter when the data store type requires that the schema name, table name, and column name identifiers needed to be delimited. For example, if working with Microsoft SQL Server data stores, the value would need to be set to DX:IdentifierDelimiter:[]

  • IdentityInsertOn

    Specifies to maintain the identity values from the source database in the target database when the data store type requires that this functionality be enabled. Enabling this parameter causes a SET IDENTITY_INSERT statement to be executed, which will allow values to be inserted into the identity column of a given table. For example, when working with Microsoft SQL Server, DX:IdentityInsertOn:SET IDENTITY_INSERT ~ ON results in SET IDENTITY_INSERT MYSCHEMA.MYTABLE ON

  • EmptyTableCommand

    By default, if the Always Drop Table parameter is set to N within dxeconfig.cfg and the table being processed already exists within the specified schema, the table contents will always be emptied prior to inserting processed values. (Data Express accomplishes this by executing the DELETE FROM statement, which removes rows from your table one at a time.) You can override this default by choosing a different option that allows your data store to perform more optimally such as the option TRUNCATE TABLE, which results in the execution of the TRUNCATE TABLE statement being executed. For example, DX:EmptyTableCommand:TRUNCATE TABLE results in TRUNCATE TABLE MYSCHEMA.MYTABLE

The following post-processing configuration setting can be specified:

  • IdentityInsertOff

    Specifies the default behavior of using the automatically generated value for the identity column in the target database, which may cause result in differing identity values for the source and target databases. Disabling this parameter causes a SET IDENTITY_INSERT statement to be executed. For example, when working with Microsoft SQL Server, DX:IdentityInsertOn:SET IDENTITY_INSERT ~ OFF results in SET IDENTITY_INSERT MYSCHEMA.MYTABLE OFF

Sample Scripts

For your convenience, some sample scripts containing some of the options described in Configuration Options are provided in the config directory as templates in order to assist you in building your specific configuration:

  • pre_Oracle.sql
  • post_Oracle.sql
  • pre_Microsoft SQL Server.sql
  • post_Microsoft SQL Server.sql