Administrating and Developing with Informix
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.
Just wanted to pass this link along,
Note the above is hopefully the first in a series, as it is using the consumption of web services to get the information. The demo is not interactive (ie you cannot perform searches or insert data), but since you are exchanging information with a web server, the possibility of modifying the example is certainly doable.
If you aren't reading http://www.informixhowto.com on a regular basis I would definitely suggest you do.
MarkJamison 100000ESF6 Tags:  security informix query database encryption expert vormetric 2 Comments 11,920 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.
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.
So I was workiing with a customer today and got an interesting question. The question was how did
sqlca.sqerrd work with cursors, specifically would it track all the inserts in a the entirely of the cursor.
The answer turns out to be yes, but with a a couple of caveats. The first is you need to check the value sqlerrd before you close the cursor.
The second is that if your cursor returns more rows than will fit in the fetch array (either using the default array size, or a custom value specified for FetArrSize), then you should check the value of sqlca.sqlerrd prior to the next FETCH operation, as this will show the value for the number of rows in the current fetch array. The value will then reset with the next FETCH. See item #5 in the following URL: http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.esqlc.doc%2Fsii15750078.htm
Of course your next question may be , "FetArrSize? What is that?"
It's a new, circa ESQL/C 3.10, global variable. FetArrSize, indicates the number of rows to be returned per FETCH statement. This variable is defined as a C language short integer data type. It has a default value of zero, which disables the fetch array feature. You can set FetArrSize to any integer value in the following range:
More info can be found at
Wanted to pass along the official link for Informix Ultimate Warehouse Edition
That of course is the standard marketing story. The key piece being added to this edition is
For those of you who also follow DB2/Z this is effectively the Smart Analytics Optimizer for Informix. The above link is to our documentation on the product.
I've had the opportunity to work with members of my own team in the Accelerated Value Program on the Ultimate Warehouse edition for the last month. It can be made transparent to the application developer and Flat out flies. Once setup and enabled IWA has generated, for my team so far, a performance improvement of more than 66 times the speed of the same informix query when not accelerated. That query had already been significantly improved because of the Star/Snowflake support in the Optimizer added in 11.70.FC1. Essentially IWA has turned the OLAP/ROLAP queries we were running into ROLAP/OLAP queries with OLTP response time.
If you noticed above, possibly the best part of this is that it is transparent to the application, so if you are using IBM Cognos, or any other Business Intelligence type of tool, you just have to request the DBA to turn IWA on for you, you don't have to get any special "IWA aware" tool to use the product.
I will definitely talk more about the IWA as I have time and am allowed. For those of us that have to write against and Use warehouses or Data marts, this is a big deal.
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.
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.
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.
So another feature that dovetails with the Smart Trigger feature is the Push Data Feature.
The relevant documentation is here:
Please note that the real information in the documentation seems to have problems displaying in some browsers, as an apple guy I use safari by default.
As such I copied the register call here.
In the example the documentation is setting things up to alert when the credit card has a transaction where more than $100 was charged.
In this case once registered the server would alert the client, instead of the client having to constantly poll the Database. Once alerted, the Client would then select the smart large object and parse the actual alert.
Unlike smart triggers, this functionality is not limited to JDBC, but is usable by CSDK.
So As I posted in my Release Blog entry the new feature in 12.10.FC9 that interests me most is the Smart Trigger functionality.
There is a decent blog post with an example of a Smart Trigger here
What is exciting to me is the ability to implement the Pub/Sub model of event driven programming seamless into Informix code.
Think about something like inventory management. Previously within Informix, you would need to actively poll if you wanted to alert that inventory had fallen below a certain amount, now with this functionality you will be able to be automatically alerted of the event. A very welcome addition to the feature set IMHO.
So I ran into an interesting issue last week. A customer couldn't alter a table. Non-Exclusive access. Sounds pretty normal right? I mean after all it's not like a DBA doesn't see this fairly often.
The Normal routine is for a DBA to run the following:
However what happens when you still get non-exclusive access after doing the above?
You need to have two considerations then. #1, check for referential integrity issues, and #2 look for open cursors?
RI can be checked using dbaccess, or dbschema, but how do you check open cursors?
The easiest way is with
onstat -g opn
In the situation I wan into, we had several open cursors with transactions running against the child table holding RI.
This allowed us to identify where applications were forgetting to close the cursors. As soon as those cursors were closed, the alter table was successful.
From a development standpoint, this brings up a salient point to always remember, close your cursors.
For more info on onstat -g opn
Hi all, another PSA today.
Still this one is pretty cool, because the lab will be talking about application development with Data Studio and Informix. Even if you don't use Data Studio, and even if you don't plan to, I highly recommend attending. The more interest a call like this generates, the more calls like this (i.e. Application Development) will occur.
So the new version of Informix is fast approaching, and IBM has a webcast on that very topic. Below you will find a summary, and the link to sign up.
So I've been working on a Proof of Concept with the new Informix Warehouse Accelerator. Part of that is getting data from source systems , and often those source systems are on another Database system. When doing work with that you inevitably use an ETL tool of some sort, and the customer I'm working with uses IBM Datatastage.
I'm using an older version of Datastage, and the ODBC driver is slow. SO I was looking for a quicker way to load, while at the same time not taking up any space, except inside the database. So I wanted to share the method used: