Last morning I received number of alarming mails like this one:
BG_retrieveBasisSwapRates Schedule BG_retrieveBasisSwapRates ended unsuccessfully
180329091035 - 092537 job getRates failed
Job getRates return code=,(init=,exec=,exit=) prereq=1 Job insertBGheader notExecuted, prereq= Job pickup notExecuted, prereq= Job insertData notExecuted, prereq=
180329 091035.368541 22017 Log enabled, invoked as:
./scriptS.php schedule=BG_retrieveBasisSwapRates.xml
180329 091035.368896 22017 Log PHP version 5.6.22, Zend version 2.6.0, memory limit -1
180329 091035.369117 22017 Log Adap job controller copyright (C) 2006 Lars Johansson. Adap comes with absolutly no warranty!
180329 091035.369439 22017 Note exec waiting on export
180329 091035.509692 22017 Note The script was run from CRON
180329 091035.595896 22017 Note Basis swap rates from Bloomberg
180329 091035.656637 22017 Note of 4 Job(s) in schedule BG_retrieveBasisSwapRates 4 are checked and ready to go
180329 091035.659645 22037 Note Enter execJob, name=getRates, id=1616233, 180329 091035.679100 22037 Note Header table=BS_HEADER, Rate table=BS_RATES
180329 091037.603559 22037 Error Invalid Bloomberg status code=100
180329 091037.608636 22037 Note Enter tryAgain, Attempt 1 of 4
180329 091037.608783 22037 Note Try attempt 1 before failed; retrying after 300 seconds
180329 091537.621574 22037 Error Invalid Bloomberg status code=100
180329 091537.622873 22037 Note Enter tryAgain, Attempt 2 of 4
180329 091537.623053 22037 Note Try attempt 2 before failed; retrying after 300 seconds
180329 092037.630589 22037 Error Invalid Bloomberg status code=100
180329 092037.631436 22037 Note Enter tryAgain, Attempt 3 of 4
180329 092037.631604 22037 Note Try attempt 3 before failed; retrying after 300 second
180329 092537.639037 22037 Error Invalid Bloomberg status code=100
180329 092537.639736 22037 Failed Driver row=0 failed aborting RESULT=
180329 092537.639853 22037 Note This job tried 4 times
180329 092537.671549 22017 NOTE Notify whom it may concern about the result.
|
I got one mail for each type of rate we fetch from Bloomberg each morning. Bloomberg status code=100 means there is nothing to pick up.
Normally we have the rates on first attempt, so I assumed I messed something up, which I actually do on a regular basis.
After I left the company the first time one of my managers told me “we are very grateful for all things you have done for us,
but the fact is the entire IT landscape have stabilized a lot since you left”. I looked around but could not find
any errors in my rate import job. One of the recipients of the alarm mails sent me a note
“Doesn’t look very reliable your system does it?” But to my great relief just a minute later he relayed a mail from Bloomberg:
Important Data License Alert
...
Original
Due to technical issues, Per Security and Web Services users may be experiencing delays with some requests.
We are currently working to resolve the issue.
...
|
Yes we had noticed thatðŸ˜
I waited half an hour then copied the line:
./scriptS.php schedule=BG_retrieveBasisSwapRates.xml (from the log above)
Pasted it into a terminal and hit enter. Problem solved😀
The rates are pretty important for the company, so this “backup procedure” is not good enough.
A you see the job attempts to fetch the rates 4 times with a 5 minute wait in between before it bombs out,
I will increase attempts from 4 to 6 and implement a backup routine that imports the daily rates if not imported and
schedule it two hours later. And create a PC emergency routine that can be run from any ‘stand alone’ Windows PC,
safeguarding the rates if the Data Warehouse servers is down, (which have not happened during it’s 17 years in operation).
Better safe than sorry.
Here is the retrieve rates routine as it is today: (It is an ITL workflow)
<?xml version='1.0' encoding='UTF-8' standalone='yes'?>
<schedule mustcomplete='yes' logresult='yes' period='day' notify='admfail.xml,custFinance1.xml' logmsg='Basis swap rates from Bloomberg'>
<!-- Restart instructions: Remove data from previous failed execution from tables @TBPFX HEADER and RATES -->
<tag name='C_DB' value='ACC_FINANCIAL_DATA' cmt='The database of the tables storing imported data'/>
<tag name='BGHEADTAB' value='BLOOMBERG_HEADER' cmt='The general headers table, response from Bloomberg'/>
<tag name='RESPTAB' value='CURRENT_RESPONSEID' cmt='Actual/Latest responseId from Bloomberg'/>
<tag name='FEEDID' value='basisswap' cmt='feedid in CURRENT_RESPONSEID table'/>
<tag name='TBPFX' value='BS_' cmt='Table prefix for Basis Swap'/>
<tag name='HEADTAB' value='@TBPFXHEADER' cmt='The headers table, response from Bloomberg'/>
<tag name='RATETAB' value='@TBPFXRATES' cmt='The basis swap rates table, response from Bloomberg'/>
<job name='getRates' type='script' pgm='BloombergSoapCurrRate2.php' cmt='Calling Bloomberg'
wsdl='https://service.bloomberg.com/assets/dl/dlws.wsdl'
userid='xxxxxx'
responseId='@RESPONSEID'
certificate='zzzz/yyyy/cert.pem'
tableprefix='@TBPFX'>
<!--userid - credentials for Bloomberg certificate access -->
<!--tableprefix - prefix for database tables created or updated -->
<!---
A responseid is picked from table CURRENT_RESPONSEID
The passphrase is picked up for the Bloomberg certificate
Bloomberg is called with responseid and certificate+passphrase.
The call & outcome is registered in BLOOMBERG_HEADER table
If data is returned:
The results are written in the run environment map, as csv and SQL text files
The job is considered a success and processing continues
If no data is returned:
Notifications are sent stating a failure
The job is considered a failure and processing stops
-->
<try until='success' attempts='4' wait='300' cleanup='newfiles'>
<!-- Bloomberg try to expedite a request within 5 minutes (300s), if we fail to receive a result we wait another 5 min and try again -->
</try>
<tag name='RESPONSEID' cmt='Bloomberg responseId'>
<sql>select responseid from @C_DB.@RESPTAB where feedid = '@FEEDID'</sql>
</tag>
</job>
<job name='insertBGheader' type='sql' cmt='Inserts Bloomberg header acknowledge successful contact'>
<!--- This job register the call in BLOOMBERG_HEADER table -->
<tag name='BG_HEADER_SQL' file='@J_getRates/@BGHEADTAB.mysql' cmt='Header data from getRates'/>
<sql name='BGheader'>
USE @C_DB;
@BG_HEADER_SQL;
</sql>
</job>
<job name='pickup' type='dummy' cmt='Pick up started_local'>
<!--- This job picks up the datetime when the data was produced at Bloomberg's in CET -->
<tag name='STARTED' file='@J_getRates/started_local.txt'/>
</job>
<job name='insertData' type='sql' cmt='Inserts basis swap rates into a MySQL database'>
<!--- This job validate rates are not already imported for today
Insert today's rates into CR_HEADER and CR_RATES tables
-->
<prereq type='sql' negate='yes' action='fail_schedule' msg='There are already @FEEDID loaded for @pickup.STARTED!'>
select * from @C_DB.@HEADTAB where date(started_local) = date("@pickup.STARTED");
</prereq>
<tag name='ZHEADER_SQL' file='@J_getRates/@TBPFXHEADER.mysql' cmt='Rates header from getRates'/>
<tag name='RATES_SQL' file='@J_getRates/@TBPFXRATES.mysql' cmt='Rates from getRates'/>
<sql name='header'>
USE @C_DB;
@ZHEADER_SQL;
</sql>
<sql name='rates'>
@RATES_SQL;
</sql>
</job>
</schedule>
|
No comments:
Post a Comment