Administrating and Developing with Informix
During this morning's Chat with the Lab on Virtualization and Cloud computing with IDS (which I thought went quite well, in that it wasn't the worst webcast I've done, though I always think of some extra things I could have said afterward), I made a vague threat to post a link on this blog to an IDS High Availability demo showing HDR and MACH11 failover across Amazon Availability Zones.
This demo, and two others can be found on the Amazon Web Services blog: http://aws.typepad.com/aws/2009/05/ibm-informix-database-in-the-cloud.html. The high availability demo is the second one.
The slides for this lab chat (and others) can be downloaded from here: http://www-01.ibm.com/software/data/informix/labchats/#24.[Read More]
This got me thinking about what my hottest feature in IDS 11.5 is. I'd probably pick something obscure like XSLT transform support or oninit -w on Windows. I think this is referring to redirected-writes, which is indeed a not bad 11.5 feature..
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]
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.
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.
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.
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.
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:
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]
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]
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]
In case you missed it IBM has released a new certification. One for application Developers.
I've seen a lot of demo's in my time, and some of them look very flashy but miss a few of the set-up details and leave me wondering how to get started.
How many slides does a demo need to cover everything you need to know about installing IDS on Windows, CSDK, Open Admin Tool, IBM Database Add-ins for Visual Studio 2005, creating applications and web services, without missing a single detail?
How about 579?
Yes, Akmal B. Chaudhri has done just that. In a six-part series he has put together the most relentless and painstakingly detailed set of IDS on Windows slides ever assembled. The 579 slides in fact only represent the first 4 parts, which have recently been posted to the IBM developerWorks On Demand Demo's site.
The first 4 parts are available to download as 15 to 17 MB PDF files:
What I like about this is that it doesn't leave anything to the imagination, if you follow this you won't have any questions. At all. It's all there. All of it.
I'm looking forward to parts 5 & 6. Here is what to expect (Update 1/15/08 these demos have now been added):
There have been a few reports of older versions of IDS displaying the wrong time on Windows Server 2003 since the US moved to Daylight Savings Time (DST) on Sunday. The time appears 1 hour behind in the Online log and in the output from the CURRENT SQL function.
The issue only manifests in IDS 10.00.TC4 and earlier. Tech support are actively working on it and I'll update this post when we have more info. Expect a tech alert on this soon too.
Here is a summary of the current findings..
Update 3/10/09: It has been determined that the problem is related to the version of the Microsoft Platform SDK that was used when IDS was built. This is good news for patch engineers.. the fix is simply to rebuild IDS with a later version of the SDK. If you have IDS 9.x or 7.31 and a valid support contract, contact tech support for a patch. If you have IDS 10.x, upgrade to TC5 or later.
Update 3/19/09: Versions of IDS that were having the Time Zone problem are now working correctly. All IDS versions switched back to the correct time on Sunday March 15th - corresponding to the DST start date for the previous year. Therefore if you have one of the above versions that had a problem, you now have a choice of upgrading/getting a patch, or doing nothing until March 2010 when there is a chance that the 1 hour difference will occur again for a few days if DST starts before the 2008 DST again.[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.