Table of Contents | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
JITL: The Managed Database Job
This job is used standalone or triggered by orders to execute (SQL-)statements in a database.
These can be database procedures or SQL statements.
...
The job chain launchDB.job_chain.xml:
Code Block |
---|
<[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/job_chain.xml job_chain] <job_chain orders_recoverable="yes" visible="yes"> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/job_chain_node.xml job<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 |
---|
<[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/order.xml order] <order title="update MY_TABLE"> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/params.xml params]><params> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="command" value="update MY_TABLE set a='foo' where b='bar';&#10;commit;"/> </params> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/run_time.xml run_time]<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 |
---|
<[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/job.xml job] <job title="Launch Database Statement" order="yes" stop_on_error="no"> <description> <include file="jobs/JobSchedulerManagedDatabaseJob.xml"/> </description> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/params.xml params]><params> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_class" value="SOSMySQLConnection"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_driver" value="com.mysql.jdbc.Driver"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name=<param name"db_url" value="jdbc:mysql://localhost:3306/scheduler"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_user" value="scheduler"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_password" value="scheduler"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="command" value=""/> </params> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/script.xml script ]<script language="java" java_class="sos.scheduler.managed.JobSchedulerManagedDatabaseJob"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/run_time.xml run_time]<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 |
---|
<[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/job.xml job] <job title="Launch Database Statement" order="yes" stop_on_error="no"> <description> <include file="jobs/JobSchedulerManagedDatabaseJob.xml"/> </description> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/params.xml params]><params> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_class" value="SOSMSSQLConnection"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_url" value="jdbc:sqlserver://localhost:1433;sendStringParametersAsUnicode=false;selectMethod=cursor;databaseName=scheduler"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_user" value="scheduler"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_password" value="scheduler"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="command" value=""/> </params> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/script.xml script ]<script language="java" java_class="sos.scheduler.managed.JobSchedulerManagedDatabaseJob"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/run_time.xml run_time]<run_time/> </job> |
Oracle
Code Block |
---|
<[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/job.xml job] <job title="Launch Database Statement" order="yes" stop_on_error="no"> <description> <include file="jobs/JobSchedulerManagedDatabaseJob.xml"/> </description> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/params.xml params]><params> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_class" value="SOSOracleConnection"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_driver" value="oracle.jdbc.driver.OracleDriver"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_url" value="jdbc:oracle:thin:@localhost:1521:scheduler"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_user" value="scheduler"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_password" value="scheduler"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="command" value=""/> </params> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/script.xml script ]<script language="java" java_class="sos.scheduler.managed.JobSchedulerManagedDatabaseJob"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/run_time.xml run_time]/><run_time> </job> |
see also:
- ORACLE___How to run an Oracle Stored Procedure using PL/SQL
- Working
How to work with PL/SQL and the Oracle DBMS
No Format *[Working with SQL*Plus|Working with SQL*Plus]
PostGres
Code Block |
---|
<[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/job.xml job] <job title="Launch Database Statement" order="yes" stop_on_error="no"> <description> <include file="jobs/JobSchedulerManagedDatabaseJob.xml"/> </description> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/params.xml params]><params> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_class" value="SOSPgSQLConnection"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_driver" value="org.postgresql.Driver"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_url" value="jdbc:postgresql://localhost:5432/scheduler"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_user" value="scheduler"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_password" value="scheduler"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="command" value=""/> </params> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/script.xml script ]<script language="java" java_class="sos.scheduler.managed.JobSchedulerManagedDatabaseJob"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/run_time.xml run_time]/><run_time> </job> |
IBM/DB2
Code Block |
---|
<[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/job.xml job] <job title="Launch Database Statement" order="yes" stop_on_error="no"> <description> <include file="jobs/JobSchedulerManagedDatabaseJob.xml"/> </description> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/params.xml params]><params> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_class" value="SOSDB2Connection"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_driver" value="com.ibm.db2.jcc.DB2Driver"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_url" value="jdbc:db2://localhost:50000/scheduler:driverType=2;retrieveMessagesFromServerOnGetMessage=true;"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_user" value="scheduler"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="db_password" value="scheduler"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]<param name="command" value=""/> </params> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/script.xml script ]<script language="java" java_class="sos.scheduler.managed.JobSchedulerManagedDatabaseJob"/> <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/run_time.xml run_time]/><run_time> </job> |