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]
A while back I briefly discussed how to set up High-Availability Data Replication (HDR) on a single Windows machine for testing purposes. One of my compadres has just written a much more in-depth guide to doing this which was published today - see: Setting up HIGH AVAILABILITY DATE REPLICATION (HDR) on a single installation of IBM Informix Dynamic Server on Windows.
If you prefer to do your HDR testing on UNIX try this older article instead: Setting up HIGH AVAILABILITY DATA REPLICATION (HDR) and ENTERPRISE REPLICATION (ER) on a single installation of IDS 9.40 for testing purposes.[Read More]
The Chicago Infomrix User Group meeting looks to be a good one. Here is the invite email form Rob Beal...
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.
There isn't much to starting IDS on UNIX - set your environment (INFORMIXSERVER, INFORMIXDIR, ONCONFIG, PATH) and run oninit as user informix or root.
Making IDS start automatically when the machine is started can be a little more challenging as you need to edit the /etc/rc.d files to set the environment and launch oninit.
On Windows IDS is designed to run as a Windows service and there are more ways to start it. At least making IDS start automatically is easier, just locate the IDS service in Control Panel->Administrative Tools->Services and modify the Properties to set Startup Type to Automatic. Another good thing is that for most startup methods, there is no need to set the environment.
Here's a look at the methods of manually starting IDS on Windows and their relative merits:
The GUI method - fine for starting manually with GUI access to the machine.
net start %INFORMIXSERVER%
The standard command line method for starting a Windows service. Disadvantage: doesn't let you pass command line arguments to oninit.
Does what net start does but allows oninit command line arguments. For example to bring an IDS instance to quiscent mode:
starts %INFORMIXSERVER% -s
Run oninit from the command line.
The oninit.exe process can be launched in foreground by setting the IDS environment and running oninit directly. Unlike on UNIX where it will fork and return you to the command prompt, it will run in foreground and not return the DOS prompt to the command line. Generally this is not recommended but can be useful when:
1. Troubleshooting a problem with the IDS service - sometimes if the IDS service won't start, launching oninit in foreground can help eliminate possible causes - for example if the IDS Service informix user password is set incorrectly.
2. Launching IDS in a relative directory to run HDR between two instances on the same machine (see an earlier post).
Update June 9 '06:Run oninit.exe from a command shell in background.
I hadn't thought about this method until I saw a comp.databases.informixthread that mentioned it. If you start a command shell as informix (or use runas) and run start /b oninitthe oninit.exe process will be launched in a background command window. This is a way to see verbose output from oninit, but still keep the process running in background. Because it's not running as a Windows service, oninit will not get its environment from the HKEY_LOCAL_MACHINE/Software/Informix/Online/%INFORMIXSERVER%/Environment registry key but from the user's environment. This is not necessarily a disadvantage as long as it is understood - in fact sometimes it can be an advantage. The only inconvenience is the need to be the informix user when you run it. You could work around this by wrapping it in a runas script like this:
runas /env /user:informix "start /b oninit"
I typically use the starts method. For example the following batch file shuts down and re-starts IDS (assumes you have a gnu, Cygwin or equivalent tool like sleep installed), and takes command line arguments:
onmode -yksleep 4starts %INFORMIXSERVER% %*[Read More]
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
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.
Well a crazy workday kept me from blogging yesterday. I was , however, reminded of an important piece to trouble shooting applications, even database instances. What was that piece? Never get hung up on a single test box, or a single test instance. The reason why may be obvious, but the problem is that if you get hung up on a single instance or box , you can miss the actual problem.
Take yesterday, for example, I was helping a customer with a box that recently migrated to 11.50.FC5 , their app was crashing every time the engine came on-line, and in the process was crashing the Informix engine as well. Now as a support engineer you tend to focus on the assertion failure file and shared memory dump , just like an application developer would focus on debug logs and a core file. Well to make a long story short, after trying to identify the problem, I finally asked them to test on a separate box that had 11.50.FC5, if they had one. They did have another test box, and tested their application which did not crash and worked as expected. It turned out there was no problem with Informix, or the application, but the original test box had significant issues all its own, due to an unforseen accident that both the developers and myself were not originally aware of.
It's so easy these days, in this "whose to blame" society that we forget sometimes, that conditions exist where no one is to blame. Accidents happen, and it's what we do to idenify and correct the issue, accidental or not, that helps make our application, and ourselves , successful.
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]
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]
The IDS 11.50.xC6 virtual appliance is now available to download here:
This can be a very useful environment to test IDS 11.50.xC6 features since IDS is pre-installed and configured. It is also a ready to use development environment - the tools you need to do development are installed (CSDK, Data Server Drivers (incl JDBC, ODBC), PDO. Optim Data Studio.
IDS 11.50.xC6 had several new features you can become familiar with using this virtual appliance. A good place to see a summary of the 11.50.,xC6 IDS features is from a write-up that appeared in the Informix Zone blog: http://www.informix-zone.com/node/912
Note that several different editions of the virtual appliance are created, all are currently based on SLES 10 SP2 and all are free.
The choices are: 32 bit vs 64 bit. VMware Workstation format vs ESX. Self-extracting executable for Windows or Gzipped tar file.
Make sure you download the right one.
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.
The Informix on Campus blog reports on a new IDS certification entitled IBM Certified Database Associate - Informix Dynamic Server 11.50 Fundamentals.
To attain this certification, candidates need to pass a single exam.. Test 000-555: IBM Informix Dynamic Server 11.50 Fundamentals.
So what is the difference between the new Fundamentals Exam and the IBM Certified System Administrator - Informix Dynamic Server V11 exam?
The System Administrator exam tests more on the specifics of IDS installation, configuration, monitoring and troubleshooting, whereas the Fundamentals exam tests a wider DBA knowledge including..
To see if you are ready to take this test, have a go at the online Sample test.[Read More]