DBI_DB_SQL_Dependencies

Overview

This service provides a framework for checking dependencies from one of serveral databases.  Therefore, you can verify that data on a source system is ready or available.  Either you can provide a SQL statement to execute, or you can provide command line options instead.  Currently this is configured to work with SQL Server, Oracle and MySQL.  One use case used by DBI executes a SQL statement against MySQL system log table in order to determine if all of the data extracted is now loaded in Redshift.  This SQL scripts also allows for a parameter for the destination schema to be passed dynamically into the SQL.  

Usage

Usage: DBI_Dependencies.sh -f <configuration file name> -s <schema> -t <table name> -c <column name> -w <where clause> -r <sql script name> -p <sql parm list> -q <check frequency>

Manual Definition

  • SCHEMA– The schema for the generated query
  • TABLE_NAME – The table name for the generated query
  • COLUMN_NAME – The name of a DATE type column to be used in the generated query with the append string ‘>= trunc(sysdate)’
  • WHERE_CLAUSE – You can optionally include this as a predicate for the generated query

SQL Option

  • SQL_SCRIPT_NAME – Predefined SQL to be executed.  If you are passing in parameters as defined below, use the placeholder format ::Px:: for each parameter where the ‘x’ is the sequence in the SQL_PARAM_LIST.  ie. select ::P2:: from ::P1:: where ::P3::
  • SQL_PARAM_LIST – Allows for a dynamic parameter which allows the SQL code to be reused.  To use, add the ‘-p’ option to the command line, and include the parameter subsequently.  If passing multiple parameters, include the parameters in single quotes seperated by a comma.  ie.  -p ‘DBI,100’

Miscellaneous

  • CHECK_FREQUENCY – This parameter provides how often the check is performed.  This process will continue to check for the sucess of the dependency check until it times out (based on .ini parameter).
Advertisements