Page History
Table of Contents | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
JITL: The SOS Hibernate Managed Database Job
Display feature availability | ||
---|---|---|
|
This job is introduced with Release 1.12 and provided as an alternative to the JobSchedulerManagedDatabaseJob job which uses a deprecated Java class.
This job is used to execute (SQL-)statements in a database and can be used standalone or triggered by orders - i.e. as an order job.
It can execute database procedures or SQL statements.
This job uses the SOSHibernate connection class (com.sos.jitl.managed.job.ManagedDatabaseJobJSAdapterClass) whereas the Managed Database Job used the deprecated SOSConnection classes.
(A general overview of all JITL jobs can be found Library of Standard Jobs - JITL).
The documentation of the JobSchedulerManagedDatabaseJobSOSHibernate.xml job can be found:
- in the $SCHEDULER_DATA/jobs directory of a JobScheduler installation
- online under:
The JobSchedulerManagedDatabaseJobSOSHibernate.xml job uses different parameters to the JobSchedulerManagedDatabaseJob.xml job.
Usage
The JobSchedulerManagedDatabaseJobSOSHibernate.xml job accepts up to 5 parameters:
hibernate_configuration_file
(required) - specifies the database connection filecommand
(optional) - contains the SQL instructionsresultset_as_warning
(optional)exec_returns_resultset
(optional)resultset_as_parameters
(optional)
The following screenshot shows a simple example where the JobSchedulerManagedDatabaseJobSOSHibernate.xml job is part of a job chain.
In the above configuration the resultset_as_warning
parameter is set to true to ensure that the result is written to the log file. The log file would then appear as shown in the following listing
...
language | text |
---|---|
title | Example Log output with SQL query result as warning |
collapse | true |
...
Introduction
The SQLExecutorJob template is used to execute SQL statements and SQL procedures in a database.
- This job template makes use of JDBC connections and the Hibernate database access layer.
- This job template can be used to access any database which a JDBC Driver is available for on the Agent.
- JS7 includes a number of JDBC Drivers for supported DBMS, see the JS7 - Database article.
- For other DBMSs users can provide an individual JDBC Driver and store that Driver's .jar file in the .
/lib/user_lib
directory of the Agent installation.
- This job template supports use of a JS7 - Credential Store.
- Delimitation:
- Note that specific SQL language extensions such as Oracle® PL/SQL are not supported by Hibernate.
- For Oracle® PL/SQL use the JS7 - JITL PLSQLJob.
- For Oracle® SQL*Plus use the JS7 - JITL SQLPLUSJob.
The job supports encryption of database credentials if used with a Hibernate configuration file, see JS7 - How to encrypt and decrypt Database Credentials.
Usage
When defining the job either:
- invoke the Wizard that is available from the job properties tab in the Configuration view and select the JITL SQLExecutorJob and the relevant arguments from the Wizard
or
- specify the
JITL
job class andcom.sos.jitl.jobs.db.SQLExecutorJob
Java class name and then add arguments as explained in the documentation below.
Example
Download: dbSQLExecutionConfigurationFile.json
A SQLExecutorJob configuration can look like this:
The job's arguments can be specified as follows:
Documentation
The Job Documentation including the full list of arguments can be found under: https://www.sos-berlin.com/doc/JS7-JITL/SQLExecutorJob.xml
The SQLExecutorJob class accepts the following arguments:
Name | Required | Default Value | Purpose | Example |
---|---|---|---|---|
command | yes | Carries the SQL statements. | ||
| no | ./config/hibernate.cfg.xml | Specifies the configuration file (XML) format for Hibernate connections, see JS7 - Database. By default Agents ship without hibernate configuration files. However, if a file with the default name is available then it will be applied. | |
| no | Raises a warning if the SQL statement creates a result set. | ||
| no | If stored procedures which return a result set are called then this argument has to be set to | ||
| no | Specifies how a result set is forwarded to order variables for subsequent jobs:
| ||
result_file | no | Specifies the path to a result file if the resultset_as_variables argument is used with one of the values csv, xml, json . An absolute path can be specified and a relative path can be used which starts from the Agent's working directory. | ||
credential_store_file | Location of a credential store database (*.kdbx) | false | ./config/private/jobs.kdbx | |
credential_store_key | Location of a credential store key file (*.key) | false | ./config/private/jobs.key |
Explanation:
- Note that it is possible to define more than one instruction
...
- with the
command
...
- argument.
Such instructions are then carried out in the order in which they are written and must be separated by a semicolon
...
- .
- Some DBMS might require an additional newline character
...
- .
- For example:
update MY_TABLE set a='foo' where b='bar'
; commit;
- Note
...
- that order variables and arguments can be injected to the SQL statement(s) of the
command
argument using the${variable}
syntax. - The SQLExecutorJob can be used with a credential store to hold sensitive arguments. For use of the
credential_store_*
arguments see JS7 - Use of Credential Store with JITL Jobs.
Cancellation
The following operations are available:
- The cancel operation is applied to the order, not to the SQLExecutorJob. In case of cancellation the job will continue until completion and the order will be set to the failed state.
- The cancel/force operation is applied to the order and to the job. The SQLExecutorJob will be forcibly terminated, any transactions will be rolled back, the database connection will be closed and the DBMS session will be terminated. The order will be set to the failed state.
Further Resources
- JS7 - JITL PLSQLJob
- JS7 - JITL SQLPLUSJob
- JS7 - JITL Common Variables
- JS7 - Job Resources
- JS7 - Use of Credential Store with JITL Jobs
Further information
- Detailed Job Documentation:JobSchedulerManagedDatabaseJobSOSHibernate.xml