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...

Compliance, data storage, and Titans

The Titans, in Greek mythology, were originally twelve powerful gods. They were later overthrown by Zeus and the Olympian gods. I'm not talking about them. Nor am I talking about the fictional characters created by Brian Herbert and Kevin J Anderson in their Legends of Dune novels. Today I want to talk about an interesting announcement from NEON Enterprise Software (www.neonesoft.com) called TITAN Archive.

So what makes TITAN Archive more interesting than anything else announced in September? Well, basically, its simplicity and usefulness. It is described as a "database archiving solution", which means that an organization can use it to store structured data for long periods of time. And why should anyone want to do that? Well the answer is compliance.

Regulations are getting stricter in so many countries, and companies are now compelled for legal reasons to store large amounts of data for long periods of time. In fact, data retention could now be between 6 and 25 years. Many organizations are defining their own retention policies and are looking for ways to action those policies that are economic and allow data to be recalled quickly and easily (now called e-discovery if it's needed for a court case), and, at the same time, doesn't affect the performance of their current computing needs. They are looking for a solution that meets all compliance and legal requirements and can be used in the event litigation.

At the moment, TITAN Archive works with DB2, but plans are in place for a version for Oracle and one for IMS. Both data and metadata are stored in what's called an Encapsulated Archive Data Object (EADO). The EADO format is independent of the source DBMS (which may very well change at a company in the course of 25 years!) and can be accessed or queried using standard SQL queries or reports – which makes accessing it very easy. The data can be stored for as long as necessary. TITAN Archive can also have a discard policy, which makes sure that data is deleted when it is no longer required for legal or commercial purposes.

TITAN Archive connects to a storage area network and is managed from a Java interface that could be deployed across the enterprise or secured to a single location. The heart of TITAN Archive is the archive appliance. This is a Linux server that performs all the TITAN Archive processing.

Moving archive data off the mainframe and being able to access it easily, while retaining it for the longer periods of time now required, is a problem many companies face. TITAN Archive seems like a very useful and economic solution to this problem.

How Green Was My Valley – and how green are my computers?

How Green Was My Valley is a 1939 novel by Richard Llewellyn and a 1941 film directed by John Ford. It was written and filmed in the days when green was just a colour and not an aspirational life style. I blogged about IBM’s green data centre plans a few months ago, but I wanted to revisit this whole issue.

There does seem to be a lot of misconceptions about what’s green and what isn’t, and it does seem to depend on how you look at an issue.

For example, I have heard it said that because flat screens use less energy than cathode ray tubes, we should all (if we haven’t done so already) get rid of those old screen and replace them with new flat ones. Apparently wrong! Because of the huge amount of energy and resources it takes to create a CRT and a flat screen, it is, in fact, more energy efficient to use that CRT right up to the moment it fails, and then change to a flat screen. This is because, although per hour of usage the flat screen is greener, the total amount of energy it took to extract all the raw materials and then construct the screen far outweigh the energy used by that screen. So we should be using that old device until it no longer works and then change over.

Interestingly, thinking about the raw resources, it has been suggested that a standard PC uses 1.8 tonnes of raw materials.

Another common comment is that recycling computers is a good thing. The idea is that computers contain lots of expensive metals (like gold) so old ones should be stripped down and the expensive metals extracted and reused. Unfortunately, the energy audit for this is quite high. So is there a better alternative? Well yes, or else I wouldn’t have mentioned it! There are a variety of companies and charities that will refurbish computers and peripherals. This refurbished PC could be re-sold or it could be shipped to the developing world – both better choices than trying to regain the metal from the old PC and then using it in a new one. It’s the difference between re-use and recycling.

Storage vendor ONStor recently found that 58% of the companies they surveyed were either still talking about creating a green IT environment, or still have no plans to do anything. But with conflicting and confusing messages that isn't completely surprising.

Things like consolidation and virtualization could help reduce power, cooling, and other operational expenses – and these would therefore help reduce energy consumption and carbon dioxide emissions, etc.

Of course, we could all do more. Many sites (and many of my friends’ houses) have old machines sitting in cupboards and under unused desks. These could be given to charities and sent on to developing countries. They’re certainly not doing anyone any good gathering dust. And even if the computer doesn’t work, given two or three machines, enough spare components could be put together to get one that does work – and which would the be put to good use.

Even if we’re not concerned with being green, with saving the planet, or helping third-world countries, we are paying the electricity bill. So in terms of simple economics, powering off unused printers and computers and anything else we leave in stand-by mode will save us money and is a way of being green too. I know you can’t power off your mainframe, but there’s often a lot of laptops left on in offices. Think, how green can your offices be – not just your data centre?!

Office of the future?

It had to happen – I was bound to be sent a DOCX file. This is the new file type associated with Microsoft Office 2007. It’s all to do with the Office Open XML format Microsoft is keen on, and, of course, my copy of Office 2000 can’t open it. To be fair, Microsoft does have download that allows Office 2000 to open DOCX files, but it comes with health warnings and caveats, so I haven’t tried it.

I have wondered in the past about keeping the faith with Microsoft or whether I should go the Open Source route and install OpenOffice etc. Indeed I wrestled for a long time with getting Linux installed permanently on my PC (and not just booting up a distro from a CD every now and again).

So, I read with interest that IBM has decided to join the OpenOffice.org development community and is even donating some code that it developed for Lotus Notes. (Interestingly, Ray Ozzie, who developed Notes now works for Microsoft). OpenOffice.org was founded by Sun and works to the Open Document Format (ODF) ISO standard – not Microsoft’s Office Open XML (OOXML or Open XML) format.

Apparently, the code that was developed for Notes was derived in part from what was originally Microsoft-developed technology! It seems that IBM’s iAccessible2 specification, which makes accessibility features available to visually-impaired users interacting with ODF-compliant applications, was developed from Microsoft Active Accessibility (MAA). IBM has already donated the iAccessible2 specification to the Linux Foundation. iAccessible2 can run on Windows or Linux and is a set of APIs to make it easy for visuals in applications based on ODF and other Web technologies to be interpreted by screen readers that then reproduce the information verbally for the blind.

Luckily, I’m not visually impaired and have no use for this technology, but I have a friend who works a lot with Web site design so that they can be used by visually-impaired people, and I have listened with interest while he talks about things I previously took for granted. It is important.

Anyway, even if IBM’s motives are not pure and they secretly hope that OOXML never becomes an ISO standard, making this kind of technology freely available has got to be a good thing.

Maybe we should all take another look at OpenOffice.