Product Knowledge Base

Space shortcuts

Page tree

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Next »

JITL: The SOS Hibernate Managed Database Job

This job is introduced with Release 1.12 and replaces the deprecated JobSchedulerManagedDatabaseJob job.  FEATURE AVAILABILITY STARTING FROM RELEASE 1.12

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 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:

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

 <order title="update MY_TABLE">
     <params>
         <param name="command"        value="update MY_TABLE set a='foo' where b='bar';&amp;#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)

 <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

 <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

 <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

<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

<job title="Launch Database Statement" order="yes" stop_on_error="no">
	<description>
		<include file="jobs/JobSchedulerManagedDatabaseJob.xml"/>
	</description>
	<params>
		<param name="db_class"		value="SOSDB2Connection"/> 
		<param name="db_driver"		value="com.ibm.db2.jcc.DB2Driver"/> 
		<param name="db_url"		value="jdbc:db2://localhost:50000/scheduler:driverType=2;retrieveMessagesFromServerOnGetMessage=true;"/>
		<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>

 

 

  • No labels