Administrating and Developing with Informix
Thanks for the introduction Guy.
A lot of you know me, but for those that don't, I've worked for 20 years in the RDBMS industry,
either as an application developer or a DBA. I've worked on every major RDBMS that runs on
WIndows or some flavor of Unix (Linux and OSX included) at various points in my career,
finally coming to work for Informix in 1995, then moving on to DBA work before coming back to
Informix, now IBM, in 1999. Been working for "the man" ever since. My main areas of focus have
been performance tuning, GLS, and Security. I've had the opportunity and pleasure of working
with some of Informix's great VARS and partners while supporting some of Informix's best and
most demanding customers.
Now with performance tuning, GLS, and Security you would naturally think "Database Engine Nerd,"
and you wouldn't be wrong. Be that as it may, I haven't forgotten my roots as an ESQL/C programmer,
and while I have to grab a manual to write java, I am definitely an advocate for the Developer.
After all the way I look at things is if you don't advocate for the developer, who is going to write
applications for this RDBMS called informix which you think is great.
So the next question you may be asking is "What's with the title of the post Mark?"
Glad you asked. I'm an Apple nerd. I prefer a Mac to a PC, an iPhone to any other phone, and
Love the iPad. Certain members of IIUG have referred to me as "Steve Jobs Jr." . I was also one
of the first to test Informix on the Mac, and continue to test and play with Informix versions as they
come out on the Mac. I'm a Mac advocate as well as an Application Developer advocate.
I plan to discuss things going forward in the App Dev side of the fence, and the Mac side of the fence.
And I looks forward to hear from people as well. Informix application development tools and process
cannot move forward and get better without input. While a blog may not get that much input, then
again it may too.
I look forward to adding content in the future. And hope to hear from readers soon and often.
Happy Monday to most everyone. For those of you who made it to the IIUG conference in April,
you may have hear about the new open source intiative. The goal is to either maintain support
or add support for popular Open Source options. One of the first pieces that is being worked on is
enhanced Hibernate support. The Dialect for Informix on Hibernate has been update significantly,
so if you use Hibernate I highly suggest you download this patch.
So go take a look.
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.
New offerings and prices.
So why do I think this is great for Developers? Well mainly for the Informix Ultimate-C edition for Mac and Windows.
Let me quote from the above:
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.
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
-rw-r--r--@ 1 majp51 staff 145059237 May 24 15:14 iif.11.50.FC7CE.macosx64.dmg
So that is the actual number of bytes.
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.
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?
Is it just a little valuable? Very valuable?
Hoping for some good feedback from you all.
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.
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.
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.
So two tutorials. Let me know what you think of the content. Is this something you would like to see more of in Developerworks, tutorials about how IBM products rok with other IBM products?
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 :
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=<#>,
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.
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.
You can download the report here: http://ht.ly/2LmTC
In case you missed it IBM has released a new certification. One for application Developers.
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.
MarkJamison 100000ESF6 Tags:  security informix query database encryption expert vormetric 2 Comments 5,402 Views
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.
So I'm sure I can hear now, why even bother with asking the question "why encrypt" , we've been told to do it, and so we need to. You can certainly look at it that way, but the different reasons to encrypt impact application developers differently. In some cases it means you have to make changes to you application, in other cases it means major changes to your application, and in even other cases it means no changes to your application at all.
A quick legend for those unfamiliar with the terminology of securing data:
"over the wire" means that you encrypt or secure your network connection, using SSL is a common method of "over the wire" encryption.
"at rest" means where your data is stored is secured. I would love to call this "disk level encryption", but the truth is DLE is a method used for encrypting data "at rest", and therefore just causes too much confusion when used.
Here are the primary reasons to get into the encryption game, and the impact it likely has on an application developer: