Page History
Table of Contents | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
JITL: The SOS Hibernate Managed Database Job
...
Introduction
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 and SQL procedures 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 job makes use of JDBC connections and a Hibernate layer.
- The job can be used to access any database for which a JDBC Driver is available with the Agent.
- For supported DBMS products see JS7 - Database. The JS7 includes a number of JDBC Drivers for supported DBMS.
- For other DBMS you can provide an individual JDBC Driver and store the Driver's .jar file with the .
/lib/user_lib
directory of the Agent installation.
- Delimitation
- Consider that specific SQL language extensions such as Oracle PL/SQL for use with anonymous functions are not supported by Hibernate.
- For Oracle PL/SQL use the JS7 - JITL SOSPLSQLJob
- For Oracle SQL*Plus use the JS7 - JITL SOSSQLPlusJob
Usage
The SOSSQLExecutor job accepts the following argumentsThe JobSchedulerManagedDatabaseJobSOSHibernate.xml job accepts up to 5 parameters:
hibernate_configuration_file
(required) -- specifies the
- configuration file (XML) format for Hibernate connections.
command
(optionalrequired) - contains- carries the SQL
- statements
resultset_as_warning
(optional)- raises a warning if the SQL statement creates a result set.
exec_returns_resultset
(optional)- If stored procedures are called that return a result set, this argument needs to be set to
true
in order to run the stored procedure as a query.
- If stored procedures are called that return a result set, this argument needs to be set to
resultset_as_parametersvariables
(optional)false
: no result set is processed.column_name
: for the first record of the result set from each column a variable is created from the column name and value.name_value
: The first two columns of the result set will be added to order variables. The values of the first column will become parameter names, the values of the second column will become parameter values.
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
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
20182021-05-1503 1323:0312:4522.550958+020002:00 [infoMAIN] [OrderProcessingStarted] id=#2021-05-03#T0076341176-root, (Task test/js_man_db_soshibernate/launch_db:11510) SCHEDULER-918 state=starting (at=never) 2018-05-15 13:03:47.981+0200 [info] (Task test/js_man_db_soshibernate/launch_db:11510) [stdout] INFO [main] (SOSMsg.java:108) - JSJ_I_0020: JobSchedulerJobAdapter::getNodeName: der aktuelle Knoten-Name ist '1'. 2018-05-15 13:03:47.981+0200 [info] (Task test/js_man_db_soshibernate/launch_db:11510) [stdout] INFO [main] (JobSchedulerJobAdapter.java:82) - 1.12.3-SNAPSHOT (2018-05-12 23:10, revision bb176148a9801a64058d1b1f8d40d2dd7d771bf1) Copyright 2003-2018 SOS GmbH Berlin 2018-05-15 13:03:47.981+0200 [info] (Task test/js_man_db_soshibernate/launch_db:11510) [stdout] INFO [main] (SOSMsg.java:108) - JSJ_I_0020: JobSchedulerJobAdapter::getNodeName: der aktuelle Knoten-Name ist '1'. 2018-05-15 13:03:50.105+0200 [WARN] (Task test/js_man_db_soshibernate/launch_db:11510) execution terminated with warning: {count(*)=11486} 2018-05-15 13:03:50.109+0200 [info] (Task test/js_man_db_soshibernate/launch_db:11510) [stdout] INFO [main] (ManagedDatabaseModel.java:51) - pos=1, Job=jdbc-resultset-name_value, Agent(url=https://agent-2-0-primary:4443, id=agent_001, time=2021-05-03 23:12:22.858+02:00) 2021-05-03 23:12:22.858+02:00 [MAIN] [Start] Job=jdbc-resultset-name_value, Agent (url=https://agent-2-0-primary:4443, id=agent_001) 2021-05-03 23:12:22.952+02:00 [STDOUT] Job Parameterization: Resulting Arguments: command=SELECT ID, CONTROLLER_ID FROM INV_JS_INSTANCES (source=JOB) resultset_as_variables=NAME_VALUE (source=JOB) executing database statement: SELECT COUNT(*) FROM SCHEDULER_HISTORY 2018-05-15 13:03:50.109+0200 [info] (Task test/js_man_db_soshibernate/launch_db:11510) SCHEDULER-843 Task has ended processing of Order test/js_man_db_soshibernate/job_chain_db:order_01, state=1, on JobScheduler 'http://JS-PC:41231' 2018-05-15 13:03:50.109+0200 [info] set_state success 2018-05-15 13:03:50.109+0200 [info] SCHEDULER-944 End state reached - order will be repeated at 2018-05-15 19:00:00.000Z with state=1 2018-05-15 13:03:50.109+0200 [info] SCHEDULER-962 Protocol ends in C:/ProgramData/sos-berlin.com/jobscheduler/jobscheduler_1.12.3_1/logs/order.test,js_man_db_soshibernate,job_chain_db.order_01.log ID, CONTROLLER_ID FROM INV_JS_INSTANCES map={1=standalone, 2=testsuite, 3=testsuite} |
Note that it is possible to define more than one instruction in the command parameter.
Such instructions are then carried out in the order in which they are written and must be separated by a semicolon and a subsequent new linenewline.
You can use
as a newline.
For example
update MY_TABLE set a='foo' where b='bar';
commit;
...
- Detailed Job Documentation: