query_runner_2

1. Description

Query_runner program takes directory path as an input and execute the queries present in the files.

2. Program location

<installation_path>/query_runner

3. Input

3.1 Mandatory Parameters

#	Parameters	Description	Example
1	connection_string	Connection credentials to connect to the database	Click-here
2	directory_path	Path to the input directory.For windows use \\ while giving path.	test-bed/data/(Linux) and test-bed\\data\(Windows)
3	log_file_path	Path to write logs	test-bed/log.txt
4	diagnostics_log_file	Path to write diagnostics logs	test-bed/diag_log.txt

3.2 Non Mandatory Parameters

#	Parameters	Description	Default value	Example
1	option	Additional parameters to be used for sqlcmd,sqlldr & sqlplus.The option parameters should be pipe seperated,containing the identification key and its value.	""	m,2|l,10
2	log_level	Level of diagnostics written to the log file.	info	error/warn/info/debug/trace/none
3	perf_diagnostic_flag	The flag that decides whether performance diagnostics will be written to the diagnostics log file.	false	true or false
4	is_transaction_req	Indicates whether transaction details are required	false	true

3.3 Connection String

Connection strings for different Databases are given below:

3.3.1 MSSQL

      "{
        "db": "MSSQL",
        "schema": "dbo",
        "username": "sa",
        "password": "Surya@123",
        "server": "localhost",
        "dbname": "TestDB",
        "options": {
          "t": "1"
        }
      }"

3.3.2 Oracle

    {
    "db": "ORACLE",
    "schema": "BALM_BOC",
    "username": "BALM_BOC",
    "password": "Surya123",
    "server": "192.168.66.193:1521/orcl"
    }

3.3.3 Postgres

    "{
      "db": "POSTGRES",
      "schema": "public",
      "username": "postgres",
      "password": "surya123",
      "server": "localhost",
      "dbname": "postgres",
      "options": {
        "t": "1"
      }
    }"

4. Output

Note: No output is getting generated.

Output derivation logic:

For all files present at the provided directory path, sorts the files in alphabetical order. Then it reads the files one by one and execute the queries in the file. If all the queries in the file are executed successfully, it moves the file from input directory to executed_queries directory.

Note:

The executed_queries directory is created by the program itself at a location one directory before the input file parent directory.

If input directory path is test-bed/data/input1.txt, the executed_queries dierectory will be formed at test-bed/executed_queries/input1.txt

Note:

If a directory contains multiple files, each with several queries and if in any file one of the query is wrong then in case of:

MSSQL -> Execution stops immediately upon encountering the error query. The file with the failed query will not get moved to the executed_queries directory, and only the files executed before this file with failed query are moved to executed_queries. Subsequent files are not executed further. Only the results of queries in the file present in executed_queries will get reflected.

ORACLE -> All other queries in the same file will get executed except the query with error. The file with the failed query will not get moved to the executed_queries directory, and only the files executed before this file with failed query are moved to executed_queries. Result of query execution from the files present in executed_queries directory along with the result of queries executed from the file containing failed query will get reflected. Subsequent files are not executed further.

POSTGRES -> Execution stops immediately on encountering errors. Files with failed queries are not further processed and only the files of which the queries are executed will be moved to the executed_queries.

5. Process configuration in streamdef table:

        {
          "processName": "query_runner",
          "processId": "1",
          "processBinary": "{PROGRAMS}/IND/query_runner",
          "processArguments": [
            "--directory-path",
            "{SH_RESOURCES}/IND/OPERATIONAL-FILES/CONFIG/query_runner/",
            "--log-file-path",
            "{SH_LOGS}/IND/BALM/{ddmmyyyy}/query_runner_log.txt",
            "--diagnostics-log-file",
            "{SH_LOGS}/IND/BALM/{ddmmyyyy}/query_runner_diag_log.txt",
            "--connection-string",
            "{BALM_CONN_STR}"
          ],
          "processDependencies": [
            ""
          ],
          "processReport": "",
          "processType": "TOOLS",
          "infoLog": "{SH_LOGS}/IND/BALM/{ddmmyyyy}/query_runner_log.txt",
          "errorLog": ""
        }