File transfer and return from a DB-Server to an External Server

How can I transfer files from a DB server to an external server and then return them to the DB using a 'middle' server?

This Page is Still 'Work in Progress'

In more detail:

Start a database procedure to generate an exchange file and transfer this file to a "middle" server.
Determine the size of the transfer file on the middle server and use a script to transfer this file to an external server.
Process data on the external server and confirm that the result file have been generated before starting a script on the middle server to transfer the file from external server to the middle one.
After the script in step 3 has finished transferring the file from the external server, start a database procedure to import the transferred file to the database.

The file movements would look like this:

<mscgen>
//Send files 123
msc {
width="800";

  DBServer [label="DB Server"],
  MiddleServer [label="Middle Server"],
  ExternalServer [label="External Server"];

  DBServer=>MiddleServer [ label = "(1) copy file to Middle Server" ];
  DBServer .. MiddleServer [ label = "(Oracle DB procedure)" ];
  MiddleServer=>ExternalServer [ label = "(2) Transfer file to External Server" ];
  MiddleServer .. ExternalServer [ label = "(script on Middle Server)" ];
  ExternalServer=>MiddleServer [ label = "(3)Transfer results to Middle Server" ];
  ExternalServer .. MiddleServer [ label = "(script on Middle Server)" ];
  MiddleServer => DBServer  [ label = "(4) Import file to DB" ];
  MiddleServer .. DBServer  [ label = "(Oracle DB procedure)" ];

  DBServer box DBServer [label="DB Server", textbgcolour="#7f7fff",  textcolour="#FFFFFF"],
  MiddleServer box MiddleServer [label="Middle Server", textbgcolour="#7f7f7f",  textcolour="#FFFFFF"],
  ExternalServer box ExternalServer [label="External Server", textbgcolour="#ff7f7f",  textcolour="#FFFFFF"];

}
</mscgen>

In particular, I want to know how to invoke remote script and DB procedure? I checked the document in your official website but didn’t get any clue about this. Can jobscheduler fulfill our requirement?
Thanks.

Solution

There are several ways to implement such a scenario.

A typical workflow would be to use a single JobScheduler installed on the middle server with a job chain with several steps:

 *Step (1) start the db report. you can use the [SQL*Plus|http://www.sos-berlin.com/doc/JITL/SOSSQLPlusJob.xml], [PL/SQL|http://www.sos-berlin.com/doc/JITL/JobSchedulerPLSQLJob.xml] or the [ManagedDatabase|http://www.sos-berlin.com/doc/JITL/JobSchedulerManagedDatabaseJob.xml] jobs from the [JITL package|http://www.sos-berlin.com/mediawiki/index.php/Standard_Jobs_Overview].

Step (2) check the size/existence/content of the output from step 1 using the existsFile job from the JITL-Package.
Step (3) transfer the file from the middle server to the external one using, for example, sFTP and the JADE-Job from the JITL package.
Step (4) start the processing on the external server using SSH.

Step (5) Use sFtp and the JADE-Job to transfer the result of step (4) to the middle server.

 *Step (6) Start the processing of the result file using, for example, SQL*Plus or PL/SQL.

Space shortcuts

Page tree

This Page is Still 'Work in Progress'

Solution