...
The JobSchedulerManagedDatabaseJobSOSHibernate.xml job uses different parameters to the JobSchedulerManagedDatabaseJob.xml job.
Usage
The SQL instructions are written in the command parameter.
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.
The following is an example of a job chain in which the command contains two statements separated by .
The job chain launchDB.job_chain.xml:
| Code Block |
|---|
<job_chain orders_recoverable="yes" visible="yes">
<job_chain_node state="launch" job="launchDB" next_state="success" error_state="error"/>
<job_chain_node state="success"/>
<job_chain_node state="error"/>
</job_chain>
|
The order launchDB,updateMyTable.order.xml:
It contains an update statement and a commit.
| Code Block |
|---|
<order title="update MY_TABLE">
<params>
<param name="command" value="update MY_TABLE set a='foo' where b='bar';&#10;commit;"/>
</params>
<run_time/>
</order>
|
The job launchDB.job.xml (i.e. with a MySQL database connection, examples for other DBMS see below): It executes the statements where the statement in the order command parameter overwrites the statement in the job command parameter.
MySQL (MariaDB)
| Code Block |
|---|
<job title="Launch Database Statement" order="yes" stop_on_error="no">
<description>
<include file="jobs/JobSchedulerManagedDatabaseJob.xml"/>
</description>
<params>
<param name="db_class" value="SOSMySQLConnection"/>
<param name="db_driver" value="com.mysql.jdbc.Driver"/>
<param name="db_url" value="jdbc:mysql://localhost:3306/scheduler"/>
<param name="db_user" value="scheduler"/>
<param name="db_password" value="scheduler"/>
<param name="command" value=""/>
</params>
<script language="java" java_class="sos.scheduler.managed.JobSchedulerManagedDatabaseJob"/>
<run_time/>
</job>
|
For other database management systems you have to modify the connection parameter like in the following examples:
MS SQL Server
| Code Block |
|---|
<job title="Launch Database Statement" order="yes" stop_on_error="no">
<description>
<include file="jobs/JobSchedulerManagedDatabaseJob.xml"/>
</description>
<params>
<param name="db_class" value="SOSMSSQLConnection"/>
<param name="db_driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<param name="db_url" value="jdbc:sqlserver://localhost:1433;sendStringParametersAsUnicode=false;selectMethod=cursor;databaseName=scheduler"/>
<param name="db_user" value="scheduler"/>
<param name="db_password" value="scheduler"/>
<param name="command" value=""/>
</params>
<script language="java" java_class="sos.scheduler.managed.JobSchedulerManagedDatabaseJob"/>
<run_time/>
</job>
|
Oracle
| Code Block |
|---|
<job title="Launch Database Statement" order="yes" stop_on_error="no">
<description>
<include file="jobs/JobSchedulerManagedDatabaseJob.xml"/>
</description>
<params>
<param name="db_class" value="SOSOracleConnection"/>
<param name="db_driver" value="oracle.jdbc.driver.OracleDriver"/>
<param name="db_url" value="jdbc:oracle:thin:@localhost:1521:scheduler"/>
<param name="db_user" value="scheduler"/>
<param name="db_password" value="scheduler"/>
<param name="command" value=""/>
</params>
<script language="java" java_class="sos.scheduler.managed.JobSchedulerManagedDatabaseJob"/>
<run_time>
</job>
|
See also:
PostgreSQL
| Code Block |
|---|
<job title="Launch Database Statement" order="yes" stop_on_error="no">
<description>
<include file="jobs/JobSchedulerManagedDatabaseJob.xml"/>
</description>
<params>
<param name="db_class" value="SOSPgSQLConnection"/>
<param name="db_driver" value="org.postgresql.Driver"/>
<param name="db_url" value="jdbc:postgresql://localhost:5432/scheduler"/>
<param name="db_user" value="scheduler"/>
<param name="db_password" value="scheduler"/>
<param name="command" value=""/>
</params>
<script language="java" java_class="sos.scheduler.managed.JobSchedulerManagedDatabaseJob"/>
<run_time>
</job>
|
IBM/DB2
...
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
| Code Block | ||||
|---|---|---|---|---|
| ||||
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';&#10;commit;