Database auditing

Finding out how your database is performing and what activities took place has traditionally been an historical activity. By that I mean actions against the database have been recorded in the logs and then later – perhaps the following day – these logs have been examined to find out exactly what happened. The advantage of this is that you have a fairly good record of all activities that occurred and it doesn’t use up too many valuable MIPS. The downside is that you never know what is happening currently and you may not be getting enough detail about what happened recorded in your log.

The alternative is to run trace utilities – and for DB2, for example, there are a number of traces that can be run. The good thing about traces is that they can help to identify where a performance problem is occurring. However, they also have a high CPU overhead. Not that you would, but if you run DB2’s global trace with all the audit classes started, IBM reckons this will add 100% CPU overhead. Even running just all the audit trace classes adds and estimated 5% CPU overhead.

So why are we worried about auditing what’s going on in our database? It’s the growth in regulations. In the USA there’s the Sarbanes-Oxley Act (SOX) and also the Payment Card Industry Data Security Standard (PCI-DSS). Both of these can affect what a company needs to audit. An audit is meant to identify whether procedures are in place, whether they are functioning as required, and whether they are being updated as necessary. In the event that one of these is not happening, the audit should be able to make recommendations for improvement.

It’s also important, with database auditing software, that it doesn’t have to be used by the DBA or anyone else who maintains the database. Pretty obviously, if the DBA was making changes to the data or browsing records he wasn’t authorized to look at, when he ran the auditing software, he could remove all information about those activities and no-one would be any the wiser.

So, to summarize, a successful database auditing tool would have to work in real-time and not historically. It would not have to impact on performance. It would have to comply with the latest regulations. And it would have to be able to audit the actions of the DBA and other super users.

There’s one other characteristic that would be useful. Having identified in real-time actions that violated corporate policies (like changing the payroll data!) it should then respond with a policy-based action – like an alert.

IMS at 40

With the recent announcement of Version 10 of IMS, I thought it would be quite interesting to take look at what IMS actually is, before seeing what’s in the new version.

Information Management System, to give it its full name, is a combination of database and transaction processing system. I’m not sure whether it’s 40th birthday was last year or next year because work started on it back in 1966, but it was 1968 when it was first running anywhere.

There are three databases associated with IMS DB. These are called “full function”, “fast path”, and High-Availability Large Databases (HALDBs). With full function databases – the original database type – data is stored in VSAM or OSAM files and can be accessed using HDAM, HIDAM, HSAM, HISAM, and SHISAM access methods. Full-function databases are derived from DL/I databases that were around at the time (1966). There are two types of fast path database – Data Entry DataBases (DEDBs) and Main Storage DataBases (MSDBs). These databases do not have indexes and are stored in VSAM files. HALDBs are the newest (since V7). They are like souped-up very big full-function databases.

IMS TM (sometimes written as IMS DC – Data Communications) provides a way for users to run transactions to get information from the database. It is perhaps most like CICS in the way it allows users to work. IMS stores transactions in message queues and then schedules them to run. Like CICS there is a lot of work going on internally to maintain the integrity of the data and the transaction.

Highlights of the V10 announcement include enhanced IMS/XML database support, enhanced XML and Web services capabilities, more autonomic computing, and improved performance in database utilities. Of course, full details are on the IBM Web site at http://www-306.ibm.com/software/data/ims/v10/.

IMS is reckoned to be installed in 95 percent of Fortune 1000 companies, which makes it an important piece of software. It might have been around for quite a while, but by embracing SOA and Web services it has ensured that it will be with us for a long time yet.

Back-ups and archives

So what is the difference between a back-up and an archive? Don’t both copy data somewhere so it can be restored at a later time if necessary? The answer to the second question is sort-of “yes”, and the answer to the first question is what this blog is about.

Back-ups of data can be stored at the same location as the original or offsite. If the main site suffers a catastrophe, the data can be restored somewhere else using the offsite back-up and work can continue. Back-ups used to be performed to tapes and the tapes would be overwritten after a week or some other fairly short period of time. The data in a back-up was the same as the data left on the mainframe.

An archive is something completely different. Gartner has suggested that the amount of data in a database is typically growing by 125%. For performance reasons, no one can afford to leave unused data in a database. Unused data is data that isn’t needed operationally and won’t be referenced. It won’t be needed by a transaction. This data can be moved out of the database to an archive. The database will then be smaller, so reorgs and back-ups will take place more quickly. Using the database will require less CPU, so everything else will perform better. In addition to improved performance, organizations will enjoy reduced costs. So archiving gives a huge return on investment.

The big problem with archived data is that it needs to hang around for a long time. In fact, with new laws and regulations this could be up to 30 years! A lot can change in 30 years. Your schema on the database may change, in fact, because of takeovers, mergers, and other reasons, your brand of database may change. And there’s even a chance that you won’t have a mainframe! What you need is a future-proof storage mechanism. You also need to be able to access the data that you have in your archive. Many countries are now allowing electronic records to be used in court and those archived records need to be able to be accessed. It’s no good in 20 years time hoping that you can restore some back-ups because, even if you have the same database, you probably won’t use the same schema. You need to be able to access the data, you need to be able to retrieve the data, and you need to be able to produce reports about the data.

As well as being able to run e-discovery tools against your archive (when it comes to litigation both sides need to know what you’ve got!), you need to ensure that it is incorruptible. It’s no good finding that five years ago someone accessed the archive and hid the tracks of their previous ten years of misdeeds. The archived data has to be read-only.

And, of course, when the time comes, you have to be able to delete the data that has come to end of both its business life and its compliance life.

So archiving has much more to it than simple back-ups. It’s quite a big difference.

So Long and Thanks for All the Fish

In October 1985 the very first CICS Update was put together. Since the summer that year, articles had been coming in to the Xephon office. Created on an Apple II using the Zardax word processing program, the first issue of CICS Update was printed out and sent to the printers at the beginning of November. Early in December, issue 1 arrived on the desks of subscribers. The Updates were born.

I wasn't there for the launch, I joined Xephon in February 1986. Soon there was VM Update and MVS Update. Next came VSE Update and VSAM Update. Then TCP Update and RACF Update. Others came and went, some quicker than others. There was Web Update, Oracle Update, NT Update, and Notes Update. In the end there was AIX Update, CICS Update, DB2 Update, z/OS Update, WebSphere Update, TCP/SNA Update, and RACF Update.

For the past four years, I have been editing all of them – but no longer. The new editor is Amy Novotny, who you may know from her work on TCI Publication's zJournal. If you do have any articles you want to contribute to the Updates, you can send them to her at amy@tcipubs.com.

So Long and Thanks for All the Fish is of course the title of the fourth book in Douglas Adams' Hitch-hiker's Guide to the Galaxy trilogy(!), and was published in 1984. It's what all the whales say when they leave Earth, just before the Vogon fleet arrives.

Good luck to Amy and the Updates in the future.
And if you need to get in contact with me, you can use trevor@itech-ed.com.
See you around...