Function db_insert_check

For data loads that perform a full delete and insert of a target table, it may be beneficial to ensure that the insert query returns the proper number of rows before starting the delete process. This function provids a method to determine if the source query returns approximately the right number of rows.

Using a query that returns a count of the rows to the inserted, the count is compared against an expected value and a threshold tolerance level expressed as a percent. If the difference between the base value and the actual rowcount obtained from the count query is within the tolerance level, the function returns to the caller. If the difference between the base value and the actual rowcount obtaind from the count query is outside of the tolerance level, the function will abort the currently running job. The count query, expected base value, and tolerance level are all provided in the job configuration file.

Sample Output 1
 Insert query threshold check passed.
 | Base rowcount: 100
 | Query rowcount: 110
 | Threshold deviation: 15
 | Actual Deviation: 10

The sample output above is written to the console and the job log when the threshold check passes. See the example job conf entries for environment “env1” below.

Sample Output 2
 Insert query threshold check failed.
 | Base rowcount: 500
 | Query rowcount: 600
 | Threshold deviation: 10
 | Actual Deviation: 20

The sample output above is written to the console and the job log when the threshold check fails. See the example job conf entries for environment “env2” below.

Parameters

Parameters
Type Use Description

Scalar

Required

VCN

Scalar

Required

Key to SQL in Job Conf file

VCN: Virtual Connection Name

Returns

Returns 0 on success. The function aborts the currently running job if the result of the threshold check is out of range.

Examples

In this example the job conf query named “count_query” is used to retrieve the insert rowcount used in the threshold check.

Example
 log_info( sys_get_dbdescr( $db_conn ) );
 db_connect( $db_conn );

 db_insert_check( $db_conn, 'count_query' );

Job Configuration File

The job conf file needs to have threshold information identified for each database environment that will used when performing an insert check. Below is an example of job conf entries for three enviroments. Possible environments are those that could returned by the function sys_get_dataenvr.

Example
threshold:
    env1:
        base:      100
        deviation: 15
    env2:
        base:      500
        deviation: 10
    env3:
        base:      10000
        deviation: 2

sql:
    count_query: |
        select count(*)
          from some_table
         where some_value > 0

Each environment specifies a base value and a deviation value. The actual rowcount is measured against the base rowcount and a variation calculated. If the variation as a percent is greater than that given by the deviation entry, the current job will be aborted. The value for deviation is given as a whole percentage.

Additional Notes

This function makes a call to the sys_die function if the threshold for rows to be inserted is exceeded. The count query is expected to reside in the job conf file under the “sql” key. Output is only written to the console when the verbose option has been specified.