Administrating and Developing with Informix
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.
A new sub-capacity pricing plan was announced in February that makes it more affordable to run IDS in virtualized environments.
To cut a long story short, it means you can deploy IDS in virtualized environments without having to purchase licenses for the entire server. You can purchase Processor Value Units (PVU) up to the number of cores needed within a socket or a server. For example, if you are running IDS in a virtual machine that has a single CPU core allocated you'd purchase an appropriate IDS edition (say, Express) and 50 PVU's. If the virtual machine instance had 10 CPU cores allocated you might purchase Enterprise Edition and 500 PVU's.
Refer to the announcement for all the details:Addition of sub-capacity parts for IBM DB2 LUW, and IBM Informix Dynamic Server.[Read More]
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.
Two useful IDS security resources have recently been published..
1. developerWorks Article:
Protect your data with Secure Sockets Layer support in Informix Dynamic Server, Part 1: Setting up SSL support in IDS by IDS security engineers Manoj Mohan and Lynette D. Adayilamuriyil .
This article takes you through the basics of setting up encrypted database communcation using Secure Sockets Layer (SSL), which begins with requesting a Digital Certificate. The digital certificate is used to exchange keys at the beginning of an SSL connection, after which a random symmetric key is generated. The article also has a section on troubleshooting SSL in IDS.
Update: The article is here.
2. Audio replay and slides available for IDS Security Best Practices chat with lab.
IDS Security Architect Jonathan Leffer presented this topic at the Chat with Lab series on December 16th. The audio replay and slides are now available here: http://www.ibm.com/informix/labchats
Here are a few notes on integrating Objective C with with Informix ODBC in Cocoa applications in Xcode (3.1) on Mac OS X.
This is not a full tutorial on creating an Informix Cocoa application (though I understand someone is writing or planning to write a developerWorks article on that subject) but more of a few gotchas you could run into. There is more than one way to integrate ODBC with Objective C; in this example the ODBC code was written as functions in a C file and included under Other Sources in Xcode to be called from Objective C.
Firstly the Xcode build architecture needs to be set to the right processor type to match the Informix Client SDK version, so for 64-bit CSDK (for example) set the build architecture to x86_64.
Next make sure Other Linker Flags specifies the ODBC specific linker flags:
Also in this section the Header Search Path should be set to point to the Informix header file locations ($INFORMIXDIR/incl/cli, $INFORMIXDIR/incl/esql, $INFORMIXDIR/incl/public) and Library Search Path should be set to point to the corresponding library directories ($INFORMIXDIR/lib, $INFORMIXDIR/lib/cli, $INFORMIXDIR/lib/esql).
When the compiled program is executed the DYLD_LIBRARY_PATH and INFORMIXDIR environment variables need to be set. To execute the code in Xcode this would be under the Arguments of the Executable settings.
To run the executable outside of Xcode the environment variables could be set globally in /etc/launchd.conf or in an Environment.plist file.
One additional issue you might run into (with CSDK 3.50.xC3 at least) is that the BOOL typedef is defined both in an Xcode header file and in the ODBC header file infxsql.h, which will cause a build failure. In the Cocoa app we created we temporarily worked this by hacking the infxsql.h file.
I logged a defect against ODBC to find a better way to resolve this.
Overall it was straightforward to integrate ODBC code with Objective C, but I hope to see some tutorials and documentation to make it easier coming along soon.[Read More]
If you feel like you're drowning in work, consider writing a to-do list. It can be more fun than actually doing the work, and provides for an ephemeral moment of satisfaction when you check something off. Personally I'd have trouble getting out of bed without a to do list.
A minimal to-do list application
My design goals for the minimal to-do list are:
An item should disappear from the list but remain in the database when thecheck box is clicked. The list box is simply a view of unfinished items.
I chose to write this with the ZK framework as it enables me to define the graphical components I need with a concise markup language and then implement the relationships between them using Java.
The SQL for the todo_list database consists of a single table called tdl:
Note that if embedded Java code is enclosed in a <![CDATA[ ... ]]> tag then HTML characters like '<' do not need to be escaped.
The code has two main graphical components: A listbox containing the list of to-do items with checkboxes, and a groupbox containing text boxes with a button to add a new item.
The ItemRender class is to support manipulating a listbox with multiple columns and is inspired by a ZK forums example by Henri Chen.
Four Java methods are defined, retrieveItems() to display the list, markDone() to remove an item from the view, addItem() to add a new to-do item, and doSQL() to send SQL update statements to the data server for markDone() and addItem().
<window title="To do list" width="640px" border="normal"><zscript> // initialize DataSource import java.sql.*; import javax.naming.*; import javax.sql.*; Context initContext = new InitialContext(); Context envContext = (Context) initContext.lookup("java:/comp/env"); DataSource ds = (DataSource) envContext.lookup("jdbc/todo");</zscript>
Is using a data server with the performance and availability of Informix Dynamic Server for a personal to-do list overkill? Since IDS is available free for non-commercial use, and considering its small footprint, why not? The free Java Derby database would also be a goodfit for this type of application.
Because I wanted to embed this on a home page I set rows="4" for the listbox. A full page view would want rows set to a higher value. Some useful enhancements might be:
I'm posting this now, and crossing two things off my to do list.[Read More]
The IDS 11.50.xC4 virtual appliance is now available for download - https://www14.software.ibm.com/webapp/iwm/web/reg/download.do?source=swg-informixfpd&S_PKG=dl
Initially the self-extracting executable versions are there (which extract to VMWare machine images) expect to see .tgz files there too shortly.
When you look at the download site note that as well as the regular 32 and 64 bit VMWare Workstation versions, for the first time there is a VMWare ESX version. Make sure you pick the right edition to download. If you're using VMWare Player, Workstation, Server or pFusion download the VMWare Workstation version.
A lot of development work has taken place since the last IDS virtual appliance was posted.. here is a quick summary of new features, listed by the virtual appliance version (the current being 1.9):
* Upgraded IDS products: IDS 11.50.xC4 Spatial DataBlade 8.21.xC3 Data Studio 220.127.116.11 OAT 2.24 * Updated IDS Game: replace old dates with new ones * Renamed SQLHOSTS file sqlhosts.cheetah2 to sqlhosts.demos. * Changed the size of the second disk from 2 GB to 4 GB. * Fixes to ODBC/CLI data server driver (add missing header files, etc.)
* ESX support* Upgraded Data Server Client Drivers from v95fp2 to v95fp4 * Fixed informix service startup file to correctly set the permissions and ownership on /data/IBM/informix/etc/sqlhosts.cheetah2 * Eliminated the "doc" directory from the VM directory Latest docs
* Addressed cut/paste between host and guest OS * Included simple BTS demo
* Changed ONCONFIG files for all demo instances * Updated all clusterDemo PHP examples: change hostname from suse1 to ids1150srvr* Updated the informix startup script: create the demo_on instance only on the first boot; do not re-create it on any subsequent boot * Updated the apache startup script: fix a defect that caused all clusterDemo PHP examples to fail to connect to any instance on the 64-bit IDS VA * Changed ONCONFIG STACKSIZE parameter for all demo instances to 128 * Installed Data Studio 2.1
* Incorporated a change to the stop demo scripts made for 64-bit editions into the 32-bit editions * Adjusted demo instance ONCONFIG files for each IDS edition * Eliminated /etc/ld.so.conf.d/ld.so.informix.conf and explicitly add IDS libraries to /etc/ld.so.conf. * Bundled AGS with the IDS VA. (Installer in /opt/AGS.) * Eliminated YaST screen on initial first-time boot-up
As the IDS virtual appliance matures it is good to see it becoming a standard platform for demo's and a base for educational classes. Now a VMWare ESX hypervisor version is also available it is now easier to put the appliance into a data server environment and start using it. [Read More]
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.
A set of instructions for installing OpenAdmin Tool for IDS in a Linux XAMPP environment written by Erika von Bargen was added to the IDS Experts blog over the weekend. Now anyone wishing to install OAT with XAMPP can refer to:Read More]
So I got back from this years IIUG. It was a blast, as usual. The IIUG is making efforts to increase the number of presentations that are applicable to developers. This year it included presentations on Database programming with PHP, a section on open source coding with informix, programming with drupal, and then I did a best practices presentation for application developers.
I don't know when the IIUG will make the presentations available, but you do need to be a member to get them, and the process is simple, and free. Just go over to
And sign up.
Ever wondered how DB2 and Informix Dynamic Server architectures compare? Which major features they have in common and what separates them? What are the major editions and platform support between the two? These questions and more are answered in a new developerWorks article by Suma C Shastry, Mohan Kumar, Prasad Srinivasachar entitled How to go hand-in-hand with DB2 and Informix.
It looks like this article has been well-researched. Particularly interesting to me are the architecture diagrams, which include Architecture overview, Process Model, Memory model, Instance Architecture, Backup mechanisms, Security architecture, administration tools.[Read More]
So the last time I actually talked about something, versus just taking note of something. I was talking about what was available from a Memory profiler standpoint for sessions. But what about just seeing what each statement in an SQL is taking up from a baseline perspective? Well the good news is that Informix already has something that answers that question, and that tool is onstat -g stm.
So what do you see when you run onstat -g stm?
As you can see onstat -g stm has some very useful stuff, as well as some stuff that people like me (i.e. Support) care about.
The first thing you will notice is that this onstat breaks out the SQL statements per session. That's right you could run onstat -g stm <session id> as well to get the info for a specific session.
Now looking at the columns you will see sdblock (useful for tech support guys like me in certain situations), heapsz and statement.
The Statement is self explanatory, and the heap size is essentially the size of memory in bytes of you heap, which essentially breaks down to the bulk of the memory your SQL is taking up.
Note there are a few gotchas. The biggest one is that it doesn't really drill down. So if you are running SPL, it tells you the size of the SPL, not each query inside it.
All and all though a very nice command for tracking your sessions sql.
Next time we will talk about looking at DB resources your SQL takes up using SQLTRACE, and syssqltrace.
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.
Interesting little nugget on Computerworld UK.
Seems to be focused using Websphere Messaging and Informix databases.
So can I get a show of hands that have used IBM Data Studio Developer? Anyone?
The product comes in two flavors, Data studio, the free version, and Optim Studio Developer,
which is the paid version.
If you do Java Coding, or currently use Eclipse, it's a very nice tool. If you use Eclipse you can
add it as a plugin to eclipse, or you can re-install the API.
Pamela Siebert and Venkatesh Gopal have done a Developerworks article that covers how to
get up and running with Data Studio.
Thanks to the generality of global email aliases I am able to share vicariously in the Cheetah release celebrations that took place at the India Software Lab in Bangalore this week, which consisted of cake..
..followed by lunch at Sahib Sindh Sultan, a trip to the pub and movie - Partner (warning, the last link has sound). A well-earned celebration - the ISL engineers I've worked this year have been putting in long hours and a great deal of expertise into the Cheetah development effort. If anyone who attended reads this let me know how it went.
IDS Performance Webcast
IDS Performance Architect Scott Lashley will be discussing some of the important performance features in IDS 11.10 including non-blocking checkpoints, automatic checkpoints, auto-LRU tuning and Recovery Time Objective (RTO) in a webcast on Tuesday July 31 at 4pm London, 8am Pacific. You can sign up for the webcast here: https://ww4.premconf.com/webrsvp/register?conf_id=9484894. If you want to be prepared take a look at Scott's developerWorks article in advance.
There is a new Getting Started with ZK tutorial on the ZK website which takes the reader through the creation of a to-do list application which uses the HSQL Java database as a back-end, and could be easily adapted to other data servers. It's a little more fully-featured than the simple to-do list ZK app I went through earlier.
My only criticism of the Apache Software Foundation is that they have so many projects going on it's hard to keep up, and every time I look at the website there are more. Apache software I have managed to get to grips with and like includes: HTTP Server, James Mail Server (useful for running the same mail server on Linux and Windows), SpamAssassin, Tomcat Servlet container, Derby (formerly known as Informix Cloudscape), Jakarta HTTP Components HttpCore (great for writing mini-servers) and Jakarta Commons FileUpload..
Announcing its presence in my inbox today was the news that Jakarta Commons Fileupload 1.2 has been released.
FileUpload is a useful component for uploading files via a Java web application into a database. For example if you're uploading a media file via a simple HTML form like this:
<form action="fileupload.jsp" method="post" enctype="multipart/form-data"> <input type="file" name="docfile"/> <input type="submit" value="Upload"/></form>
The form will upload the file as a binary stream.
The following JSP code shows an example of using FileUpload to read this stream into a form that can be inserted into an IDS BYTE column. All this sparsely commented code does is get the uploaded file as a FileInputStream object and then use the JDBC SetBinaryStream() method to bind it to the SQL insert statement...
Update 2/22/07: This example has been updated to use ServletFileUpload and DiskFileItemFactory (see lines in bold) instead of DiskFileUpload which was being used before thanks to a comment from Stephen Brand who pointed out that DiskFileUpload is now deprecated due to a memory leak. Thanks Stephen.
<%@ page import="java.sql.*,java.util.*,java.io.*" %> <%@ page import ="org.apache.commons.io.output.*" %> <%@ page import ="org.apache.commons.fileupload.*" %> <%@ page import ="org.apache.commons.fileupload.servlet.*" %> <%@ page import ="org.apache.commons.fileupload.disk.*" %>
Update 3/19/07: See the comments section for a complete working FileUpload example from Kane - thanks Kane.
The new features in FileUpload version 1.2 include a Streaming API which offers improved performance for dynamically processing streams.[Read More]
The latest IBM Migration Toolkit (MTK), version 1.4.9 has some enhancements for migrating applications from Oracle to Informix. New features include support for migrating:
For an introduction to using MTK to migrate to Informix, see an earlier article by the same authors which covered MTK 1.4: Migrating to IBM database servers gets easier with the latest MTK release.[Read More]
IDS virtual appliances now come in a choice of operating systems! UK based IBM partners Bell Micro have entered into a distribution agreement with Canonical to produce a free IDS 11.50 Developer Edition virtual appliance running on Ubuntu 8.10 Intrepid Ibex.
The appliance is similar in some ways to IBM's SLES 10.2 based Informix virtual appliance, following the basic format of a pre-installed instance of IDS 11.50.xC4 Developer Edition, associated drivers and tutorials. You will see a few differences, such as tighter intregration with AGS Server Studio and other Bell Micro customizations.
To download the Ubuntu virtual appliance, and for more information visit Bell Micro's website at: http://www.informixcity.com/ubuntu.aspx
I like using Ubuntu and am very happy to see an Ubuntu based IDS appliance available. I find it to be one of the most accessible and easy to use Linux distros out there and get a lot of work done with it running on my old laptop (which IBM keep asking me to return, I'll do that any year now).[Read More]
Keshava Murthy's Blog
Keshav was one of the driving forces behind IWA, and he has an excellent entry this morning on what the Informix Warehouse Accelerator is all about.
So the next question we have , now we know why we encrypt, is what to encrypt.
Ultimately we have only two areas to encrypt. The first area is encrypting our network connection, and the data that goes against them. The second is encrypting the actual data when it is "at rest", which is an industry term indicating encrypting the data where it had permanent or near permanent storage.
Different Compliance standards requests different things. Some only care about the storage, others only the "in flight", and some require both. You have to know what your requirements are if you only want to do some encryption, versus going wholesale.
Important to remember is that any encryption requires a performance cost. Some less than others, but a cost nonetheless.
My next post on this comment will be the network options for encryption.
In case you missed it IBM has released a new certification. One for application Developers.
Version 2.1 of the free DataStudio Developer was announced earlier this month, and some useful Informix specific feature enhancements are included the new release.
New Informix related features include, among other things:
For further reading on the new Data Studio release take a look at the Data Studio Team blog, where new articles by Sonali Surange and Curt Cotner cover it in more detail, and the recent developerWorks article by Sonali: What’s new and exciting in IBM Data Studio Developer 2.1 - Get better application insight and increase your productivity.[Read More]
Today I was caught using the non-existent word "shutdowning", which reminded me that mental exercise is important for keeping ones brain young looking and stopping it from, er, shutdowning.
What better way to exercise the gray matter than by writing your own web quiz game?
20 Questions is a simple quiz game written in ZK and small enough to be embedded in a web frame. It is based on question-answer pairs such as world capitals, US state capitals, English county towns and chemical elements. The main point of the quiz is to improve working memory.
The question-answer pairs are loaded into a database, and randomly shuffled by the application before challenging the user, who can choose 5, 10, 15 or 20 question quizzes, depending on how much work they are avoiding. One user complained that the questions are not multiple choice... kids these days, the idea is to test memory rather than guessing ability.
For the database the questionable design decision of having a separate table for each quiz was made. It would have been neater and better normalized to have a single quiz table with an extra quiz id column which references a lookup table for quiz name. That would allow the application to be data independent and dynamically build a list of quizzes based on the rows in the lookup table.
The main reason a separate table for each quiz was chosen for this example is that for a small number of quizzes it makes for a smaller and simpler web application. Should I ever add a new quiz I will stop prevaricating and normalize the design moving forward.
With excuses out of the way, here is the schema for the quiz database with its four quiz tables. This article assumes Informix Dynamic Server as the data server of choice, naturally, though there is nothing data server specific about the schema.
create database quiz;create table elements ( idx serial not null , question varchar(30), answer varchar(30), primary key (idx) );
database quiz;load from 'count_towns.unl' insert into county_towns;load from 'elements.unl' insert into elements;load from 'state_capitals.unl' insert into state_capitals;load from 'world_capitals.unl' insert into world_capitals;update statistics;Presentation layer
To get started with ZK refer to the Quick Start guide on the documentation page. The visual part of the quiz, written to $TOMCAT_HOME/webapps/quiz/index.zul is as follows:
<window title="20 Questions" width="400px" border="normal" onOK="processAnswer(answer.value);"> <zscript src="quiz.jul"/> <vbox> <label id="selectLabel" value="Please select a game.."/> <hbox> <listbox id="gameList" rows="1" mold="select"> <listitem value="elements" label="Elements"/> <listitem value="county_towns" label="English County Towns"/> <listitem value="state_capitals" label="State Capitals" selected="true"/> <listitem value="world_capitals" label="World Capitals"/> </listbox> <listbox id="numq" rows="1" mold="select"> <listitem value="5" label="5"/> <listitem value="10" label="10"/> <listitem value="15" label="15"/> <listitem value="20" label="20" selected="true"/> </listbox> <button id="newGame" label="New Game" width="90px" height="24px" onClick= "newGame(gameList.selectedItem.value,numq.selectedItem.value);"/> </hbox> <hbox> <label id="question" value="" visible="false"/> <textbox id="answer" value="" visible="false"/> <button id="subButton" label="Submit" width="90px" height="24px" visible="false" onClick="processAnswer(answer.value);"/> </hbox> </vbox></window>Note that the main window has an onOK handler so the enter key can be pressed instead of selecting the Submit button with the mouse.
The presentation layer calls two functions, newGame() and processAnswer(). The Java code to implement those functions - what I'll call the service layer - is in a file called quiz.jul and is listed below.
Note that the JDBC database connection was implemented by adding a connection pool called jdbc/quiz to the Tomcat server.xml file. (For an example of setting up a connection pool in Tomcat see sections 4 and 7 of this post.)
// quiz.jul - service layer for quiz application
To obviate the need to use the mouse while playing, in addition to the onOK handler in the presentation layer, the focus shifts back to the answer text box after each question, and to the New Game button at the end of a game by calls to the focus() method.
Despite my reservations about the unedifying qualities of multiple choice it wouldn't be too much work to extend this program (and db schema) by adding support for radio button multiple choice answers. Suggestions for new quizzes (or mistakes in the existing ones) are always welcome.[Read More]