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.
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:
Daylight Saving Time (DST), the reason your gadgets show the wrong time for half the year, is being extended in the US and Canada next year to start on the second Sunday in March and end on the first Sunday in November. The US tried this before in 1974 as a response to the energy crisis and it was alleged to have saved 10,000 barrels of oil a day and prevented 50 traffic fatalities.
Some software has the daylight saving dates hard-coded and will require an upgrade. For most Informix DBAs this should not be a problem since IDS gets its time from the operating system. As long as your operating system changes its time on the right date the transition should be no different to usual. (Remember to restart IDS after installing relevant OS patches.) Java has some hard-coded dates though, so if you use Java applications which perform date calculations you need to think about upgrading JRE to the latest versions (Australians had a similar problem in 2006).
If you have Java UDRs which call"get time of day" functions then you may need to upgrade IDS in order to get a fixed JRE. This IBM Alert describes which versions are affected and what you need to do: Action Required: Changes to daylight saving time (DST) in the U.S. and Canada affect IBM Informix Dynamic Server. The IIUG also has a page dedicated to this topic with additional information: March 2007 Daylight Savings Time Change Information. If your JRE is an IBM one and has JTZU you can fix the problem without updating, see the IBM Time Zone Update Utility for Java (JTZU).
The central IBM website for DST changes is Daylight Saving Time alert - this includes a FAQ and will change as new updates are added.
If you use other IBM software you may need to care more. Here are some of the relevant links..
Application Servers: Possible implications if one does not apply the Daylight Saving Time fixes
DB2: Actions required: Changes to daylight saving time (DST) in the U.S. and Canada affect IBM® DB2® products
Lotus Notes and Domino: Daylight Saving Time (DST) 2007 information for Lotus Notes and Domino
Lotus Sametime: Daylight Saving Time (DST) 2007 information for Lotus Sametime
Websphere: URGENT Actions Required: Changes to Daylight Saving Time will affect IBM WebSphere Application Server and its associated Operating Systems.
Websphere Everyplace Connection Manager: Daylight Saving Time (DST) 2007 information for WebSphere Everyplace Connection Manager
Websphere MQ: US Daylight Saving - How changes to daylight saving time affect WebSphere MQ, WebSphere MQ Express and WebSphere MQ Everyplace
Update: Bobby Woolf added a comment pointing out his Websphere blog entry Daylight Saving Time and Java which has some useful information on this topic, including a link to the Sun Developer Network article about extended DST.
Update #2: Windows users and developers should refer to these Microsoft documents: Preparing for daylight saving time changes in 2007, FIX: Windows-based applications that use the TZ environment variable may not work as expected because of changes to DST, FIX: Visual C++ .NET 2003 C runtime daylight saving time 2007 update for the TZ environment variable problem.[Read More]
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.
Based on support calls there is a fairly high demand for Informix integration with Pluggable Authentication Modules (PAM). When it comes to worked examples there is somewhat of a dearth. It is therefore refreshing to see this new developerWorks article by Manoj Mohan, Ramesh Gopal Srinivasan, Thamizhchelvan A. Anbalagan: Enhance Informix Dynamic Server security using the Pluggable Authentication Module framework and JDBC - Increased authentication flexibility for IDS.
The article includes a working example PAM module written in C with Solaris 32 compilation instructions and configuration file. Next is a an example JAVA callback module using JDBC. The Informix JDBC driver has supported PAM since 2.21.JC5. (Incidentally the JDBC version coming out with Cheetah will be 3.10.)
At one point the article states:
Note that IDS also supports LDAP on Windows which is set up and configured just like PAM - look in %INFORMIXDIR%\demo\authentication for an example. The IDS documentation for LDAP on Windows is here: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.admin.doc/admin219.htm.[Read More]
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.
In case you missed it IBM has released a new certification. One for application Developers.
For the past 6 months I've been tinkering with a chatbot that provides an instant messaging interface to our product defect database via SameTime. Development consists of sporadically adding commands in my spare time to meet various user requirements like fuzzy search, subscribing to nightly bug reports, reporting defects by owner, submitter, severity, etc. One of these needs is to expand coverage to other databases used by tech support, for example those used to track customer calls and internal technical discussions.
Some of the databases are difficult for mere mortals to obtain direct access to, and can only be publicly accessed by logging on to the official internal web interface. These restrictions led me to experiment with Jakarta Commons HttpClient as a means to retrieve and process information from secure websites. HttpClient is straightforward to use and has all the functionality required for this purpose. Once the basic infrastructure was in place it was easy to add chatbot commands that retrieve information from any websites. One of my first experiments was to add a "whatis" command to the bot in order to provide an interface to Wikipedia.
The whatis program
The whatis program is a simple command line example to demonstrate using HttpClient to look up Wikipedia articles and return the information as text. It retrieves the raw HTML from the website and does a little processing to extract the main text of the article and filter out the HTML. To build and run it you need the following Jakarta Commons libraries in your CLASSPATH: HttpClient, logging, codec.
// wiki.java - a simple Apache Jakarta Commons HttpClient demo to// download articles from Wikipedia.org
This example calls getResponseBodyAsString to get the requested web page as a string. It would be more efficient and predictable to write your program around the getResponseBodyAsStream method, or to limit the downloaded size.[Read More]
I had a mail from the CSDK team manager saying "I am looking for customer feedback/pain points or any other feature which they would like to see to be addressed in CSDK".
So.. what would you like to see in the Informix Client Software Development Kit? Feel free to leave comments or email me directly.
Off the top of my head I'd like to see:
Until fairly recently, a new Intel/AMD machine in the lab meant choosing which operating system to install on it. Nowadays semi-ubiquitous virtualization and free offerings from the likes of Vmware and Microsoft make it easier to run multiple concurrent operating systems.
For a personal computer, I can't be bothered with fully fledged virtualization software; if I want to run Windows and Linux concurrently Cooperative Linux fits the bill. CoLinux is a Linux daemon that runs natively on Windows. I recently gave the latest version a test drive. The installation and setup process was pretty simple..
cofs0:/ /mnt/cdrive cofs user,noexec,dmask=0777,fmask=0666 0 0
At this point CoLinux is pretty much configured. The Wiki has more detailed setup and configuration instructions (such as enabling swap space etc) and a FAQ. One thing lacking from the Debian image I tried was vi, but fixing that was as simple as running:
apt-get updateapt-get install vim
This also had the benefit of updating glibc as a pre-requisite. On Fedora it's a good idea to run yum update to get the basic software up to date.
Next I tried installing Informix Dynamic Server - ok probably not a supported configuration but it works. So far only installed IDS version 7.31.UD10 as it was the smallest tar file that was lying around. The fedora 2GB filesystem image had 1GB of free space and additional filesystems can be added so plenty of room for any version plus dbspaces (not looked into to whether there's a way to implement raw devices for dbspaces).
IDS installation was standard, created an informix user and group with:
groupadd informixuseradd informix -d /informix -g informix -m
Copied the IDS tar file to c:\temp, and then untarred it directly into the chosen INFORMIXDIR with:
tar xvf /cdrive/temp/ids.7.31.UD10.LINUX-I32.tartar xvf IDS.tar
The rest of the installation was identical to installing IDS on any Linux machine - set INFORMIXDIR, run installserver as root, create an environment file to set INFORMIXDIR, INFORMIXSERVER, PATH, ONCONFIG, create and edit an onconfig file, add an sqlhosts file onipcshm entry, create an empty root dbpsace file. Here's the oninit -ivy output...
It's good to have Linux on Windows this accessible, and there are plenty of possibilities for making it easier.. such as running Samba to access the Linux filesystem from Windows, setting up sshd, sftpd. Should make for some interesting configuration tests, like running Enterprise Replication between IDS on Windows and IDS on Linux on the same machine..
Update 9/5/06: Tried installing the latest IDS 10.0 (10.00.UC5W3) on CoLinux with the Fedora 2.6 filesystem - the only gotcha is that IDS 10.0 has a dependancy on libstdc++.so.5 and the gcc version I'd installed had a later version, so to get IDS working I borrowed libstdc++.so.5 from another Linux machine - after that IDS installed and ran fine (and fast). This IIUG post by Andreas Breitfeld tells you what you need to know to get IDS 10+ working with Debian.[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.
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.
The IDS 11.50.xC5 virtual appliance has been available for a while now, but since we've been changing the download mechanism to enable a quicker turnaround. Now the virtual appliance is available via direct FixCentral links..
Below are the direct links associated with each of the Linux 32 and 64 bit virtual appliance packages (Workstation tgz/exe and ESX) - make sure you download the right one..
Desktop VMware virtual appliances (for VMware Workstation, Server, Player, Fusion)
32-bit Gzipped Tar (.tgz for any platform)::
32-bit self-extracting Executable (.exe for Windows):
64-bit Gzipped Tar (.tgz for any platform):
64-bit self-extracting Executable (.exe for Windows):
VMware ESX format