- Security : As you can imagine this is a broad one and can mean a bunch of different things to different people. Let us consider the NSA or Military, in this case you may be asked to meet a certain security level even though your application has little or no "secure" data. This will likely require all data to be secured "at rest" as well as "over the wire", and may even require changes to the application to allow for two factor authentication or other types of security.
- Regulatory Compliance : And this one is a can of worms, we are finding out more every day as to which law applies to which customer and what they must do about it. Depending on the choices your company makes on this it can be relatively painful as an application developer to nearly painless.
- Protecting against physical theft : This one is the least painful for application developers, as in most cases it only means securing data "at rest".
Administrating and Developing with Informix
with Tags: informix X
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:
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
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 4,191 Visits
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.
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.
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.
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:
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.
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 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.
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.
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.