1201 Syn~ EOD Check Tool

Introduction

• As an IT Support Member, before rolling over the BatchZone and DayEndLedger, I want to make sure trades are settled by contractual settlements, value date postings are completed by scheduled BOPA, closing precises are retrieved from DDC, etc...
• As an IT Support Member, after rolling over the BatchZone and DayEndLedger, I want to make sure the rolling over is successful.
• These before and after checks can be formulated in SQL statements against the SYNAPP/SYNRPT database, that no record should be returned.
• Any returned row signals an check failure and then I shall start manual investigation.

This tool provides are extensible framework to the IT Support Team, such that pre-EOD and post-EOD checks can be automated by a command line (and hence a control_m). It is esily extensible, such that the IT Support Team can own the tool and setup new checks by supplying sensible SQL statements to the tool.

This is a page to describe how it works as well as the roles of IT Ops and IT Dev in the maintenance of this tool.

Workflow

Sample command & code

• Pre_EOD check for US EOD:
  • ant PreEOD_US \--> call Check function, with folder America and Sub-folder PreEOD. ant PostEOD_US \--> call Check function, with folder America and Sub-folder PostEOD.
• What it does,
  1. copy the .sql files in sql\America\PreEOD (or PostEOD) folder to tmp folder.
  2. replace the variables in the .sql files in tmp folder:

1. run .sql files in tmp folder, according to their alphabetical order, and store the output in .out file in tmp folder.
2. if any .sql file returns any record (stored in .out file) from database, the check fails. Otherwise, the check pass.

Maintenance

• Folder Structure

• Configuration: config/eod_check.properties
  • # ======================================================================= # Properties setting for Syn~ EOD checks # Note: # If passsowrd is commented out, they will be retrieved from the getpassword.sh command # ======================================================================= syn.toolset.root=/app/dvsynhk/apps/EOD_Checks syn.toolset.lib=${syn.toolset.root}/lib syn.toolset.sql=${syn.toolset.root}/sql syn.toolset.tmp=${syn.toolset.root}/tmp syn.toolset.log=${syn.toolset.root}/log/eod_check.log # Database Connections oracle.syn.app.schema=SYNAPP oracle.syn.app.host=msg-ora-omsgsi1 oracle.syn.app.port=15002 oracle.syn.app.servicename=OMSGSI1.macbank oracle.syn.app.username=SYNREPORTER #oracle.syn.app.password=ClearText -- comment out me if you want getpassword.sh to retrieve password. # Date overwrite, comment out to auto-caculate. # Today, current date of Linux system. # Yesterday, last business day from today. #date.yesterday=20120203 #date.today=20120206
• Adding/Update/Delete check SQLs
  1. Prepare a SQL, if any record is returned from the query, the check fails. If no record returns, the check succeed.
     1. Please refer to existing SQL for samples.
  2. Replace the variables in the SQL to produce a template. e.g. value of today should be replaced by @TODAY. This variable will be calculated and populated every day when you run the SQL. Below is an example to find any trade with today's trade date and stucked in entered status. At the end of today, you might want to make sure not such trade exists.
     1. SQL: select traderef from synapp.traderep_tab t
     2. where t.tradedate = TO_DATE('20120126', 'YYYYMMDD')
     3. and t.tradestatus = 'Entered' Template: select traderef from synapp.traderep_tab t
     4. where t.tradedate = TO_DATE('@TODAY', 'YYYYMMDD')
     5. and t.tradestatus = 'Entered'
  3. Name the template by a letter. The letter determines when your new SQL statement will be executed in the list of checking steps. The package looks at the .sql files, and execute them according to alphabetical orders.
  4. Save the template in the proper folder. E.g. if you are adding EOD check for BatchZone Asia, and the check should be executed before EOD, then you should put your .sql file into sql/Asia/PreEOD folder.
  5. Test run to and look at the screen output to confirm that your SQL is executed.
• Adding variables for replacement
  1. Currently, only three variables are supported. @TODAY, @YESTERDAY and @BATCHZONE.
  2. If new variable is needed, please contact DEV team to update the lib.xml file. The derivation logic of the variable should be coded there.

Ownership and team work

• PRD support team
  • configure the configuration file for database details.
  • add/amend/delete the SQL files (hence the health check logics).
• DEV team
  • upgrade the Ant scripts (.xml) for more functions or bug correction at the request of PRD support team.