Table of Contents | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Info | ||
---|---|---|
| ||
|
Example: File
...
Transfer using
...
JITL Job
The following scenario is given:Job Scheduler Reads
- JobScheduler reads a status information from an Oracle
...
- database table scenario1 where we store
...
- values every 5 minutes
...
- For this demo we handle the table as if there were just one row of data
...
- .
If the Oracle Table.Statush1. “GO” thencolumn value of scenario1.Status = “GO
” then
- read additional FTP attributes from an database table, Read Additional FTP Job Attributes from an Oracle Table ( i.e. $SourceFilePathSourceFilePath, %SourceFileNameSourceFileName, $TargetFilePathTargetFilePath, $TargetFileNameTargetFileName, $EncryptedYN)EncryptedYN.
- Store store FTP Attributes attributes as Memory Variables memory variables for later use.
- Call JADE to SFTP files using Memory Variables
- call YADE to transfer files by SFTP using its configuration from the stored memory variables.
- If SourceFile If $SourceFile does not exist
- send
- e-mail indicating the error
- including the file properties (source file name, source file size, source file date/time created, date/time of failure, target file name, target directory, failure detail)
- If $SourceFile SourceFile does exist
- If $EncryptedYN EncryptedYN has the value
Y
- run through Encryption Routine
- execute encryption routine
- perform SFTP file transfer Perform SFTP to target system
- Confirm confirm that File the file has been successfully received at the destination.
- If File the file has not been successfully received at the destination
- Send email send mail indicating the error that occurred with including the file properties (source file name, source file size, source file date/time created, date/time of failure, target file name, target directory, failure detail)
- If File the file has been successfully received
- send a confirmation email with mail including the file properties (source file name, source file size, source file date/time created, date/time of failure, target file name, target director)
- If $EncryptedYN EncryptedYN has the value
- Log All transport activities
- Produce Create PDF report of all transport activities between two dates/times
...
Representation of the Solution
Graphviz |
---|
digraph "Example: File Transfer using YADE JITL JadeJobJob"\ { graph [rankdir=TB] node [fontsize=10] node [style=filled,fillcolor=aquamarine] checkDB [shape=Mrecord, label="\{checkDB | \{GO<GO>GO|no<no_GO>no_GO \}\}"] checkEncrypted [shape=Mrecord, label="\{checkEncrypted | \{Yes<Yes>Yes|No\<No>No}\}"] checkDB:GO -> checkEncrypted checkDB:no_GO -> WAITING checkEncrypted:Yes -> transportSourceToLocal -> encrypt -> transportLocalToTarget -> report checkEncrypted:No -> transportSourceToTarget -> report \} |
Components of the Solution
checkDB: Access to
...
Oracle database using pl/sql
For the connection to the Oracle™ RDBMSDB DBMS we use the pl/sql script plsql_script.txt which is executed by the JAVA-AdapterClass [Java adapter class sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass.
The pl/sql The http://www.oracle.com/technetwork/database/features/plsql/index.html?ssSourceSiteIh1. ocomtr pl/sql.] script is defined as:
Code Block | ||
---|---|---|
| ||
declare
status varchar2(64);
sourcefilepath varchar2(2048);
sourcefilename varchar2(2048);
targetfilepath varchar2(2048);
targetfilename varchar2(2048);
encryptedyn varchar2(16);
begin
select STATUS, SOURCEFILEPATH, SOURCEFILENAME, TARGETFILEPATH, TARGETFILENAME, ENCRYPTEDYN
into status, sourcefilepath, sourcefilename, targetfilepath, targetfilename, encryptedyn
from scenario1;
dbms_output.put_line('status=' || status);
dbms_output.put_line('sourcefilepath=' || sourcefilepath);
dbms_output.put_line('sourcefilename=' || sourcefilename);
dbms_output.put_line('targetfilepath=' || targetfilepath);
dbms_output.put_line('targetfilename=' || targetfilename);
dbms_output.put_line('encryptedyn=' || encryptedyn);
end;
|
...
- name of the pl/sql script and where it is found
- name of the DB database and access parameters
For the interpretation of the table status column value of scenario1.Status being "GO
" a postprocessing some post-processing routine is used (here programmed in javax.script:rhino)
Code Block | ||
---|---|---|
| ||
function spooler_process_after(spooler_process_result) \{ var params = spooler_task.order().params(); var status = params.value("status"); if ( status == "GO" ) \{ spooler_log.info("Table status is \"GO\", continue!"); \} else \{ spooler_log.info("Waiting until table status is \"GO\" ..."); spooler_task.order().set_state("WAITING"); \} return spooler_process_result; \} |
CheckFileExists: check
...
if file exists
- The file defined by the parameters
...
- SourceFilePath and
...
- SourceFileName is checked with the JITL job sos.scheduler.file.JobSchedulerExistsFile.
checkEncrypted: Interpretation of Parameter encryptedyn
The value of the parameter encryptedyn decides wether whether the file is to be encrypted or not. The encryption is done in for a local copy of the file, therefore different steps of the jobchain job chain are used. The functions are part of the job checkEncrypted programmed in javax.script:rhino.
Code Block |
---|
function spooler_process()\ { spooler_log.info("------- " + spooler_job.name() + " -------"); var encryptedyn = spooler_task.order().params().value("encryptedyn"); if ( "Y" == encryptedyn ) \{ spooler_log.info("Encryption required"); spooler_task.order().set_state("transportSourceToLocal"); \} else if ( "N" == encryptedyn ) \{ spooler_log.info("No encryption required"); spooler_task.order().set_state("transportSourceToTarget"); \} else \{ spooler_log.error("encryptedyn = \"" + encryptedyn + "\" (must be Y or N)"); return false; \} return true; \} |
In case of encrypting encryption we have use the following steps:
- transportSourceToLocal: copy file from source system to local system
- encrypt: encrypting encrypt file
- transportLocalToTarget: copy file from local system to target system
Without encrypting encryption only one transfer is used to copy the file from source to target in step
- transportSourceToTarget
Transport
...
Steps
The transport steps are using the JITL job sos.scheduler.jade.JadeJob.
These The following parameters are neededrequired as per step:
encrypt: encryption of the local file
In our example the encryption is only simulated. An own routine may be used here.
Report: Mailing a report
A mail is generated to send the JADE YADE history file as an example for a report. External report features may be used here. The JITL job sos.scheduler.managed.JobSchedulerManagedMailJob is used here to manage the mail.
JobChain in JOE