Job Tracking
Ianus can track the job execution on a database. The database can be shared across multiple users and systems so to have a centralized repository of the Ianus execution data.
The tables and view described hereafter are available for the execution analysis and reporting.
Table t_jobs
The table t_jobs logs the job execution status. For each job execution one entry is tracked and updated in the table t_jobs.
The table t_jobs contains the following columns:
| Column | Type SQL | Type DB2 | Type Oracle | Type PGSQL | Purpose |
|---|---|---|---|---|---|
| job_id | INT | INT | NUMBER | INT | Unique identifier of the job execution |
| script_name | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Name of the script |
| job_name | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Name of the job |
| start_time | DATETIME2 | TIMESTAMP | DATE | TIMESTAMP | Date/Time of the job start |
| end_time | DATETIME2 | TIMESTAMP | DATE | TIMESTAMP | Date/Time of the job end |
| user_name | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Name of the Windows user who started the job |
| system_name | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Name of the Windows system where the job is executed |
| status | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Job Status |
| status_code | INT | INT | NUMBER | INT | Job Status Code |
| log_name | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Path of the job log file |
Table t_tasks
The table t_tasks stores the execution status for the tasks executed by a job. For each task one entry is tracked in the table t_tasks.
The table t_tasks contains the following columns:
| Column | Type SQL | Type DB2 | Type Oracle | Type PGSQL | Purpose |
|---|---|---|---|---|---|
| job_id | INT | INT | NUMBER | INT | Unique identifier of the job execution |
| task_id | INT | INT | NUMBER | INT | Unique identifier of the task execution |
| task_name | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Name of the task |
| task_type | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Type of the task |
| start_time | DATETIME2 | TIMESTAMP | DATE | TIMESTAMP | Date/Time of the task start |
| end_time | DATETIME2 | TIMESTAMP | DATE | TIMESTAMP | Date/Time of the task end |
| left_object | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Name of the object on the left (or source) side of the task |
| right_object | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Name of the object on the right (or target) side of the task |
| processed_left | BIGINT | BIGINT | NUMBER | BIGINT | Number of rows/records processed on the left/source side |
| processed_rights | BIGINT | BIGINT | NUMBER | BIGINT | Number of rows/records processed on the right/target side |
| status | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Job Status |
| status_code | INT | INT | NUMBER | INT | Job Status Code |
| errors | BIGINT | BIGINT | NUMBER | BIGINT | Number of errors detected |
| warnings | BIGINT | BIGINT | NUMBER | BIGINT | Number of warnings detected |
| message | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Status message |
Table t_diffs
The table t_diffss stores the differences detected by tablecompare and filecompare tasks. For each difference detected one or more entry is tracked in the table t_diffs.
The table t_diffs contains the following columns:
| Column | Type SQL | Type DB2 | Type Oracle | Type PGSQL | Purpose |
|---|---|---|---|---|---|
| job_id | INT | INT | NUMBER | INT | Unique identifier of the job execution |
| task_id | INT | INT | NUMBER | INT | Unique identifier of the task execution |
| diff_id | INT | INT | NUMBER | INT | Difference identifier |
| part_id | INT | INT | NUMBER | INT | Difference part identifier |
| diff_type | CHAR | CHAR | CHAR | CHAR | Difference type (left, right, both) |
| key_value | VARCHAR | CLOB | CLOB | VARCHAR | Textual representation of the key parts, where applicable |
| field_name | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Name of the field/column originating the difference |
| lvalue | VARCHAR | CLOB | CLOB | VARCHAR | Textual representation of the value of the left side |
| rvalue | VARCHAR | CLOB | CLOB | VARCHAR | Textual representation of the value of the right side |
Table t_logs
The table t_logs stores log messages. The recording of log messages can be enabled/disabled using through the tracking configuration in the Environment Configuration File.
The table t_logs contains the following colums:
| Column | Type SQL | Type DB2 | Type Oracle | Type PGSQL | Purpose |
|---|---|---|---|---|---|
| job_id | INT | INT | NUMBER | BIGINT | Unique identifier of the job execution |
| log_id | INT | INT | NUMBER | BIGINT | Unique identifier of the log message execution |
| log_date | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Date/Time of the log message start |
| log_level | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Level of the log message |
| log_logger | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Name of the logger component |
| log_message | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Log message |
| log_machine | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Name of Windows system where Ianus is executed |
| log_user_name | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Name of the user executing Ianus |
| log_thread | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Name of the thread issuing the message |
| log_exception | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Exception logged, if any |
| log_stacktrace | VARCHAR | VARCHAR | VARCHAR | VARCHAR | Exception stacktrace logged, if any |
Note
The enabling log storage on database implies that every log message is stored on the database, therefore the impacts on performance and disk space required must be carefully considered.
Warning
Storing log entries on DB2 for LUW may cause unexpected behavior of the stored procedures being tested.
Setting up the Database Job Tracking System
In order to set up the database job tracking system, the database schema objects must be created in the database.
The SQL scripts to create the tables and the views are included in the Ianus distribution in the “database” folder. Those scripts can be submitted to the database through any standard administration tool.
Once the tables and views are defined, the tracking system can be enabled by defining the datasource corresponding to the database holding the created objects and configuring the tracking system as described in Environment Configuration File.
Important
Make sure that connection string for the selected datasource, correctly defaults to the SCHEMA where the objects have been created.
Accessing tracked data
Ianus provides a convenient mechanism to query and purge the data stored, using the following options of the Ianus command line:
| Option | Action |
|---|---|
| --list-jobs | List all jobs tracked |
| --list-running | List running jobs |
| --show-job |
Show job |
| --purge-completed | Purge completed jobs |
| --purge-selected |
Purge jobs based on filter |
Note
Data stored on the tracking database can be manipulated using standard SQL commands thru any suitable database client.
Example
C:\> ianus -e env.xml -q
ID Status Start End Script
----- -------- ------------------- ------------------- --------------------------
10428 Success 2022-02-08 14:14.43 2022-02-08 14:14.44 C:\Storage\VSWorkspaces\Ianus.Development\Test\Config\Threading_Multi_01.xml
10480 Success 2022-02-09 08:01.32 2022-02-09 08:01.32 C:\STORAGE\VSWorkspaces\Ianus.Development\Test\Config\FileFormat.xml
10498 Errors 2022-02-09 08:05.47 2022-02-09 08:05.51 C:\STORAGE\VSWorkspaces\Ianus.Development\Test\Config\DB2vsSQL_SPCompare.xml
10499 Errors 2022-02-09 08:05.52 2022-02-09 08:05.53 C:\STORAGE\VSWorkspaces\Ianus.Development\Test\Config\SQLvsORA_SPCompare.xml
10500 Warnings 2022-02-09 08:05.54 2022-02-09 08:05.54 C:\STORAGE\VSWorkspaces\Ianus.Development\Test\Config\SQLvsPGSQL_SPCompare.xml
10501 Errors 2022-02-09 08:05.55 2022-02-09 08:05.56 C:\STORAGE\VSWorkspaces\Ianus.Development\Test\Config\SQLvsSQL_SPCompare.xml
10502 Warnings 2022-02-09 08:05.56 2022-02-09 08:05.56 C:\STORAGE\VSWorkspaces\Ianus.Development\Test\Config\SQLvsSQL_SPCompare_Transaction.xml
10503 Success 2022-02-09 08:05.57 2022-02-09 08:05.57 C:\STORAGE\VSWorkspaces\Ianus.Development\Test\Config\StepConds.xml
10504 Success 2022-02-09 08:05.59 2022-02-09 08:07.44 C:\STORAGE\VSWorkspaces\Ianus.Development\Test\Config\BulkCopy.xml
10505 Errors 2022-02-09 08:07.45 2022-02-09 08:07.49 C:\STORAGE\VSWorkspaces\Ianus.Development\Test\Config\DB2vsORA_TableCompare.xml
10506 Errors 2022-02-09 08:07.50 2022-02-09 08:07.54 C:\STORAGE\VSWorkspaces\Ianus.Development\Test\Config\DB2vsSQL_TableCompare.xml
10507 Running 2022-02-09 08:07.55 - C:\STORAGE\VSWorkspaces\Ianus.Development\Test\Config\LinkedDB2vsSQL_TableCompare.xml
10514 Running 2022-02-09 08:08.47 - C:\STORAGE\VSWorkspaces\Ianus.Development\Test\Config\DB2vsSQL_UserColumnHandlers.xml
10515 Running 2022-02-09 08:08.56 - C:\STORAGE\VSWorkspaces\Ianus.Development\Test\Config\DB2vsSQL_UserColumnHandlers.xml
10516 Running 2022-02-09 08:09.07 - C:\STORAGE\VSWorkspaces\Ianus.Development\Test\Config\DB2vsSQL_UserColumnHandlers.xml
10517 Warnings 2022-02-09 08:27.34 2022-02-09 08:27.58 C:\STORAGE\VSWorkspaces\Ianus.Development\Test\Config\DB2vsSQL_UserColumnHandlers.xml
10612 Running 2022-02-09 08:34.36 - C:\STORAGE\VSWorkspaces\Ianus.Development\Test\Config\BulkCopy.xml
10613 Errors 2022-02-09 08:36.16 2022-02-09 08:36.20 C:\STORAGE\VSWorkspaces\Ianus.Development\Test\Config\DB2vsORA_TableCompare.xml
C:\> ianus -e env.xml -q --jobid=10613
Job # : 10613
Name : DB2vsSQL
Script : C:\STORAGE\VSWorkspaces\Ianus.Development\Test\Config\DB2vsORA_TableCompare.xml
Status : Errors
Start time : 2022-02-09 08:36.16
End time : 2022-02-09 08:36.20
User name : EMEA\calabret
System name: FHE4UFJPTV
Step Task Status Start End Wrn Err Left Object Left # Right Object Right # Message
-------------------- ---------------- -------- ------------------- ------------------- --- --- ---------------------------------------- ---------- ---------------------------------------- ---------- ----------------------------------------
TBLCMP01 tablecompare Success 2022-02-09 08:36.16 2022-02-09 08:36.18 0 0 IANUS.TEST01 7 IANUS.TEST01 7 No differences detected
TBLCMP02 tablecompare Warnings 2022-02-09 08:36.18 2022-02-09 08:36.18 3 0 IANUS.TEST01 7 IANUS.TEST01 7 Differences detected
TBLCMP03 tablecompare Warnings 2022-02-09 08:36.18 2022-02-09 08:36.19 5 0 IANUS.TEST01 7 IANUS.TEST02 8 Differences detected
TBLCMP04 tablecompare Warnings 2022-02-09 08:36.19 2022-02-09 08:36.19 8 0 IANUS.TEST01 7 IANUS.TEST01_EMPTY 0 Differences detected
TBLCMP05 tablecompare Warnings 2022-02-09 08:36.19 2022-02-09 08:36.19 6 0 IANUS.TEST01 7 IANUS.TEST02 8 Differences detected
TBLCMP06 tablecompare Warnings 2022-02-09 08:36.19 2022-02-09 08:36.19 8 0 IANUS.TEST01 7 IANUS.TEST02 8 Differences detected
TBLCMP07 tablecompare Errors 2022-02-09 08:36.19 2022-02-09 08:36.19 0 1 Table does not exist in database DB2
TBLCMP08 tablecompare Errors 2022-02-09 08:36.19 2022-02-09 08:36.20 0 1 IANUS.TEST03A 0 IANUS.TEST03B 0 Incompatible schemas detected: Left columns count > Right columns count
TBLCMP09 tablecompare Success 2022-02-09 08:36.20 2022-02-09 08:36.20 0 0 IANUS.TEST03A 1 IANUS.TEST03B 1 No differences detected
TBLCMP10.1 tablecompare Success 2022-02-09 08:36.20 2022-02-09 08:36.20 0 0 IANUS.TESTRALF01 2 IANUS.TESTRALF01 2 No differences detected
TBLCMP10.2 tablecompare Success 2022-02-09 08:36.20 2022-02-09 08:36.20 0 0 IANUS.TESTRALF01 2 IANUS.TESTRALF01 2 No differences detected
DECIMALS tablecompare Success 2022-02-09 08:36.20 2022-02-09 08:36.20 0 0 IANUS.HIS_ITEM 2 IANUS.HIS_ITEM 2 No differences detected