Product Knowledge Base

Space shortcuts

Page tree

JITL: The SOS Hibernate Managed Database Job

FEATURE AVAILABILITY STARTING FROM RELEASE 1.12

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:

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 file
  • command (optional) - contains the SQL instructions
  • resultset_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

Example Log output with SQL query result as warning 
2018-05-15 13:03:45.550+0200 [info]   (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) - 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


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 line.

You can use 
 as newline.

For example

  • update MY_TABLE set a='foo' where b='bar';
commit;

Note also that Order parameters could be used to overwrite the Job parameters shown in the example above.

Further information