The other day we had a problem with the nightly batch load of our Business Intelligence system the Data Warehouse. Actually we have problems all the time, we run some 20.000 jobs a month so problems is expected, but the problem we had the other day where special. En ETL process refused to run due to missing input. WTF! these files have never failed to show up before, well almost never. When tracing the error we found the SAP job assembling the information for us was not run, so our dependent extraction routine had failed with ‘SAP information not refreshed, abending...’ Following a long chain of dependent events, we found out the guys in our Tierp factory wanted vacation this summer too :-) To keep our customers happy, a slight change in the shop calendar was done to increase the production before the vacation period. These are all nice problems; continue keep customers happy, increase production, vacation. The nasty problem was the changed shop calendar was flawed and the cover time calculation bombed out which ultimately led to the above ‘SAP information not refreshed, abending...’. We notified SAP operations and the problems was fixed in no time. Our waiting ETL process munched the late input files and in the end a Qlikview process was triggered and some Qlikview applications was refreshed with the new information.
All these problems would have been sorted out and fixed by the operations without our interference, but we are the early birds, the BI users are the first to spot delays in the IT factory. The IT operation is monitored, but there is no special monitoring of the early precursors for the BI processes, so I said to myself why not do just that. So I knocked together a simple monitor for this purpose.
I keep a list of jobs I want to follow up, fetch all jobs run in SAP and joins those with my list to find out which jobs has not completed successfully and mail them to me.
This was easy peasy. (If you have a Lotus Notes mailbox and do not know Lotus Notes well, stick to a simple text message!)
I did the monitor in an ITL schedule with two jobs. The first job ‘getMonitoredJobs’ sifts failing jobs and the second ‘send_mail’ sends a mail to concerned if there is failing jobs. You use the bapi BAPI_XBP_JOB_SELECT to extract job information from SAP, the only snag is you have to run this bapi in a XMI/XBP session .
And the result, an example:
I wrote the spanish myself, without the help of Google translate. I used Google translate for a translation into english though. Hi, work (s) important for the data warehouse, SAP fiasco , not bad at all, I suspect it can be expressed better, but what the Hey I do not know spanish. And it sounds fantastic in Google Translate :))
Switch to english and have Google Translate speak up, very amusing. You can be creative with Google translate in ways I never thought of before.
I just read Simon Mudd’s excellent post MYSQL 5.6 is out, so what is next? Simon put forth his wish list for MySQL 5.7. Well if Simon can wish so can I. First I think Simons wish list is a good one so I only want to add one wish. Parallel select processing of table partitions . Since my use of MySQL is Business Intelligence applications, I have some large (partitioned) tables that is read many times, so what I really want is faster select processing on large partitioned tables . Another option that could help speed up select on partitioned tables is global partitioned indexes i.e. the index is not partitioned. The MySQL 5.6 key cache support is something I will test out first thing when I have migrated to MySQL 5.6. What is holding me back from migrating is our somewhat odd MySQL setup. I have a MySQL replica in Japan, which I Rsync with our Master database. First I have to upgrade the Japanese replica and then upgrade the master. The problem is I do not know how to upgrade the Japanese MySQL server. It is an Ubuntu 12.04 LTS standard install, MySQL is the normal Ubuntu package and how to upgrade that from MySQL 5.5 to 5.6? I found this post , but I do not try this on a machine on the other side of the globe on a Linux I only installed once. I do not know Ubuntu procedures but I hope they issue a MySQL 5.6 package for 12.04 LTS. The master MYSQL is run on a Mandriva Linux server where I install MySQL RPM packages myself.