So I've definitely gotten enough feedback to realize discussing what is consuming most of my time these days, especially as it relates to application development. So here is my plan, and I will link to each subsequent post so this is also an Index. Since I'm currently working with Informix and IBM's Database Encryption Expert I will eventually spend a fair amount of time discussing implementation methods and strategies using this product. I'm certainly not saying that IBM DEE is encrypted

Please note, what to encrypt section is not about what particular data you should encrypt but instead what type of things you should encrypt. I expect this to be a long running series, and may interleave some other stuff in the interim.

So as it has been painfully obvious, I haven't been blogging particularly frequently over the past few months. Now on the one hand, you could just say that the "honeymoon period" for me on the blog is over, but the truth is I've been buried in regulatory compliance stuff and other security related issues. Of late, I've been working especially hard with a customer on implementing IBM Database Encryption Expert and Informix. It's been challenging learning a product that is focused at being integrated into the OS layer, but fun too. Of late though, I've wondered how much that might apply to application developers. Sure the intent is to be as transparent as possible, but if you data is have to be encrypted/unencrypted, do you want to know about it? And if so how much?

So anyway, I'm asking for feedback as to whether you would like to hear a bit more about encrypting databases, the methodologies, and what I firmly believe is the best choice for Informix, well ok all, databases.

In case you missed it, and I'm guessing you haven't, Informix 11.70.FC1 was released yesterday. It has a lot of very nice features for Developers which I will be covering over the next few months. I am very excited , having been involved in the Beta, to see this version go live. It has some great features that will benefit a Dveloper, both directly and indirectly.

Few companies have a meaningful way to measure the value of IT and IT projects before making an investment. Technology providers frequently talk about features and functions but sometimes forget to help potential clients understand benefits.

Recently, IBM commissioned Forrester Consulting to examine the total economic impact and potential return on investment (ROI) that organizations may realize by deploying IBM Informix database software. The study uses a comprehensive methodology to bring third-party, objective ROI analysis to organizations considering the use of Informix.

The conclusion? IBM Informix delivers high performance and cost efficiency, including administration efficiency, reduced downtime, improved server utilization, and reduced support costs. But don’t take our word for it. Read the report for yourself.

So have you ever wanted to have an easy way to know how long you SQL waited on I/O? What about the actually number of sequential scans for an individual query? How about the average execution time of a query without running a script and using time() or timex() as part of the equation. I know I have. And until We got to informix 11.10 and above, we didn't have that opportunity, at least not natively. Technically we had an old IBM/Informix product called I-SPY that offered most of the functionality that you might want, but it was :

High Overhead

A separate application to manage

Beginning in version 11.10 we have the ability to handle that information natively. It handled by a new ONCONFIG variable called SQLTRACE. SQLTRACE can be set like the following:

# SQLTRACE - Configures SQL tracing. The format is:

# SQLTRACE level=(low|med|high),ntraces=<#>,size=<#>,

SQLTRACE level=high,ntraces=1000,size=2,mode=global

I pulled that out of one of my test boxes, and you can see I have mine set to high , that mode is slightly more overhead, but not a huge amount, however it gives you a lot more diagnostic information.

The best thing about SQLTRACE is you can set it dynamically. You can use OAT to set it, or you can set it yourself using the sysadmin api. The syntax is fairly easy, so to mimic what I have above it would be

The next question of course is how do you access this information. You have two ways, plus OAT, to look at the info, the first is through onstat.

In this case it's onstat -g his and has the following type of output:

This one is just showing a DATABASE connection so nothing particularly noteworthy, but it still shows you the format that you will see for all queries.

You can also see that like an onstat -g sql, we trap the error number. And yes it looks like I have ER turned on somewhere, but didn't actually create the syscdr database.

If you look a little closer though, this output will also show you the caveat to this functionality, namely the info is in the equivalent of a circular linked list. So looking at the above, trace number 1001 will overwrite your first entry here. Note that OAT comes with a function that will let you write this info to disk, thus saving the info to do historical information, or a poor man's auditing of queries.

The other option to gather info is by way of SQL, specifically querying the syssqltrace table. The output is not as pretty, but it allows you to search on particular session ID's, or most anything in the above output.

All in all this is a great advancement if you are trying to track down poor performing queries.

So one of the reason for the infrequent postings of late have been vacation related, but some have been helping a customer out with security related issues. I think that all to often we dba's forgot the impact, directly and indirectly, that these mandated changes have on application developers. One such impact is in Data Privacy Laws. Ultimately what this means is that you soon will not, from a legal liability aspect, be allowed to restore a production server instance into a test environment to try and reproduce a problem. The solution to this is using some sort of app that transforms your data so that it is still valid for testing but has no direct association (besides say data distribution) to the actual real production data.

IBM has a very nice product to help you with this, which is Optim Data Privacy, and here at developerworks we have had 2 recent articles on the very topic.

Back to updates and potentially useful information. Many of you may be now writing apps for informix that are running on Clients and Servers using some form of LDAP for user Authentication. If you happen to using Active Directory for you chosen form of authentication, please check the following:

Microsoft already has a fix for the problem, but in the meantime if you are getting inexplicable -951 errors when attempting to connect to an Informix instance using Active Directory, this may be your culprit.

As you work supporting a database product, in my case the informix product line, you often find yourself working on stuff that may or may not be useful to many others besides the customer you are currently working for. While I see Unicode issues crop up across more than the normal customer I work with, I still haven't seen that many overall, so I cannot help but wonder if this is because Informix globalization is so well understood by developers, or if it is actually on the horizon still.

So would a discussion about application development considerations for Unicode be worthwhile?

I might blog on it anyway, but the more feedback means home much I should concentrate on blogging about it.

Hope everyone has had a couple of good weeks, I've been on vacation for most of it. Family reunions can be a lot of work let me tell you.

So one of the customers I support made an interesting feature request lately and I was interested in your feedback. As an application developer this particular customer feels he doesn't have enough tools at his disposal to know what the session was doing with the memory it is consuming. So his feature request was asking for a Session Memory profiler. Basically so he could know how much of memory is being used for temporary tables, how much is save by cursors, etc.

So my question to you all, is how valuable would you find a tool like this?

So now that the announcements are over it's time to do a little evaluation. So to that end I am going to download and install Informix Ultimate-C edition for Mac. If I get enough requests I will run through this same exercise with windows, but at the moment I will presume that to be the same as the Mac edition, but more "window-like".

So onto the first part. Downloading a copy. The good news is it is very easy to find. If you merely go to the Informix website, you can click on the Ultimate-C edition for Mac, and there is a download link. The bad news is that you have to go through the same old routine you always go through when downloading a product or demo from IBM, fill in tons of radio buttons and other assorted things for IBM sales follow up. While I understand the rationale behind doing it, that doesn't mean I don't sympathize with everyone who doesn't want to create an IBM idea, and click what seems like 100 radio buttons just to download a "no charge" product.

So we are now past the hoops necessary to download the product and we are downloading the product. It's not lightweight, but still a smaller footprint that a lot of other things. Total space required for download? Well according to finderr, it's 99.44 GB

As soon as the download completes successfully finderr will open up the mounted .dmg file like so:

As you can see this is the standard .dmg file and by default we have the the .pkg file standard.

I would suggest that before going any further though that you create an informix userid, and an informix group. The first reason is that you know what those id's are, but there is a second issue that can show up, especially when upgrading your Mac OS. The second reason to create your own informix ID and group is because , while the The install script creates them for you, they do it at the command line and "silently", for lack of a better term. While there is nothing wrong, per se, with the way the installer creates user id's and passwords, it creates an interesting visual problem. For anyone who uses a Mac, you manage users through System Preferences -> Accounts, unfortunately the "silent" user creation means that the informix user and group will not show up there.

Alright then so now it is time to go to the install itself. It has the really nice install package wrapper for most mac apps. Looks like the below.

For anyone used to Mac installs, this is the standard "pretty" installer. Looks good and very mac-centric. And even seems very fast until you run into a slight problem. This installers calls another installer to do the actual install.

That actual installer looks like :

So just like this part of the page, the install feels a little cluttered. As we install each piece be aware that you will eventually need to go back to the package installer to close the window, I only mention this because when you are installing the product, it may not be the only thing you are doing which means that package installer made be hidden behind a bunch of other windows. Note, that at one point or another you will be asked if the installer wants to update the kernel. If you have installed Informix before you can say "no", otherwise say "yes".

OK so following those steps (mostly just clicking) it took a little less than 5 Minutes to install everything on my Macbook Pro. So all and all a relatively simple, painless process. But also a standard informix install too.

I expect to be blogging some more about this issue including what the "limitations" for this edition will likely mean to a developer.

For those of you who made it to iiug, I'm sure you all remember Rob Thomas promising more to come
on offering and other changes. Well today is that day, and it is a great day for anyone who wants
to do application development on Informix.

Gives businesses, ISVs, and OEMs the ability to develop and
deploy enterprise-class functionality for departmental or
small-to-medium sized business solutions, at no cost.

Look at that again.. Windows and Mac for the Ultimate-C Edition at no cost. So if you want to design, develop, and deploy a Windows or Mac based solution that needs a robust full featured RDBMS, then Informix is now the clear best solution.