We are starting a new mini-blog series, and we will bring to you useful tips and tricks shared by the HCL Informix development and Technical Support engineers. Whether you are an HCL Informix power user and seasoned veteran, or a novice user getting started with HCL Informix, you have a chance learn something new that help you in your HCL Informix related tasks. It may be a new way to utilize an HCL Informix tool that you already use, or a new option recently added to an HCL Informix utility, or a new feature or product enhancement recently released, or some troubleshooting and diagnostic tips. Our goal is to keep these posts short (average reading time of less than 3 minutes) and useful. This series will also give you, our users, yet another way to connect directly with the HCL Informix lab to share their expertise as well as learn more about the HCL Informix product. Please share your thoughts and ideas in the comments section below.
Easily Identify Type of Sessions
Let's start the series with a handy troubleshooting command released in 12.10.xC13 - a way to easily identify types of sessions. What's the problem we're addressing? Well, there are times when HCL Informix isn't behaving as expected and a DBA starts troubleshooting. Where should they start? Depending on the symptoms, there are number of metrics to look at - e.g. cpu utilization, size of the ready queue, checkpoints - and often analysis turns to database user sessions. Is any session waiting for a buffer? Or a lock? What is an easy way to see all the sessions in these states?
That's where this new command comes in. It is a new option to the onstat -g ses, onstat -g pqs, and onstat -g sql commands, which provide session, query operators, and query info respectively. The new syntax is onstat -g ses <filter>, where <filter> can be one of "active", "bufwait", "lockwait", and others. So onstat -g ses bufwait will display info about all sessions currently waiting for a buffer. This info can allow a DBA or technical support to take action to resolve the symptom or the next step in analysis.
In these situations, it is often valuable, to Technical Support especially, to note the stack of such sessions. Stack info can be automatically included when the command starts with a capital letter. For example:
onstat -g ses Bufwait
Check out the full syntax with onstat -g and the HCL Informix documentation at https://informix.hcldoc.com/12.10/help/topic/com.ibm.adref.doc/ids_adr_0510.htm, try it out on a test server, and use this the next time you're troubleshooting a problem. Have fun!