Temporary space usage / Uso de espaço temporário

Introduction

Informix has a few rocks in it's shoes, or skeletons in the closet as we prefer. In my opinion the ones I consider as such are lack of specific functionality, undesirable behavior or inability to provide information to the DBA. I believe I've opened RFEs (request for enhancements) for most if not all of them, and my favorites are probably:

I've opened two RFEs, for a couple of situations because the first was refused or erroneously considered delivered (in my opinion of course). There are a few others like the string truncation in non-ANSI databases without warning, inability to use PAM with OlEDB clients - password only), missing SID in the audit logs etc...

From these, the first doesn't seem to have any progress and I believe it's a serious limitation for the DBA's daily work. And even if R&D decides to implement it, it would probably not be back ported to currently existing versions, which are to be supported for several years. So it means customers would not benefit from it, even if it was implemented today. And considering IBM closed my first attempt on the basis that we were working on the session limit configurations, I doubt this will be considered a priority. As a side note, the possibility to limit the use of temporary space is a very welcomed feature. But the fact that you can configure something, doesn't mean you won't need to monitor it. I believe this is common sense.

Script solution and usage

Well, considering all this, and after learning a few more things about the engine I noticed it would be possible to create something that can help customers with existing versions regarding the first two issues above (temporary space usage and running query plan). At this moment all I have in a "sharable" state is a script to report on temporary space usage. it's not entirely new as it was possible to find at least one script with overlapping functionality in the IUG repository, but I believe it's the first one that can do all of these:

Show the session owning most (if not all) temporary obkjects which can be:

Show the temporary table structures (columns and respective datatypes and indexes, both explicit and system created)

Find the objects created by the currently running query and other opened cursors

Ability to work on secondary read-only servers

The script is hopefully simple to use. As with most of my scripts the "-h" option shows the help:

galileo@solstice.onlinedomus.net:informix-> ixtempuse -hixtempuse: -V | -h | [-s [-t temp_table]] [-u ] [-d ] [-D [-I max_iterators] -V shows script version -h shows this help -s : Show information for a session or list of sessions (separated by commas) -t : Show temporary table structure for a single session/table (ALL for all) -u &ltuser list> : Show information for a user or list of users (separated by commmas) -d &ltdatabase list> : Show information for sessions in a database or list of databases (separated by commas) -D &ltdbspace list> : Show information for a dbspace or list of dbspaces (separated by commas - must be temp dbspaces) -I &ltmax_iterators list> : The script can generate an infinite recursion if something goes wrong. To prevent this there's a limit of 100 iterators that can be overriden with this parameter But be advices that in most cases if the limit is reached, it means the script as a flaw. Apart a script bug, this situation should only happen on very complex queries which are unlikely to appear frequently. Overriding the limit may cause a FORK BOMB!!!Ex: ixtempuse -s 43,35,4galileo@solstice.onlinedomus.net:informix->

Getting the script

The script can be downloaded from http://github.com/domusonline/InformixScripts (browse through scripts/ix)It's licensed under GPL 2.0, so feel free to use it and even change it if you want. I welcome any feedback, requests and bug reports.

Warnings and disclaimer

I have to be very clear on this section. First of all, this is my best effort to provide something useful and that can help customers (DBAs) in their daily routine, and hopefully help to reduce the number of PMRs that are opened around this topic. The script was tested in several combinations of OS (RHEL, HP-UX, Solaris, AIX) and different engine versions (11.50 and 12.10) and several use cases. But anyone using it will have to assume full responsibility for it's results and behavior. Having said this, I'd be very glad if users provide feedback and bug reports. Assuming I can reproduce it, I'd gladly fix it.A few very important points:

The script is SLOW. There isn't much I can do about it because the way it works (and that's apparently the only way to get the info) means I must go through all the sessions and several onstat commands. I have plans to try to use a single execution of onstat (with -i) but I'm not sure if that's possible. Current workaround it to reduce the scope of the sessions it has to go through. It has options for session id (-s), user (-u) and database (-d). Naturally the slowness is proportional to the number of session and also to the complexity of what they're doing and what cursors they have opened

The script requires recursion and the only way I've found to make it work in KSH is to run the recursive function as a background process. As with any thing that is recursive there's always the possibility of an infinite loop. I had this during development and I created some measures to prevent it. The main one is that the script will only go up to 100 levels of recursion. This should be more than enough for most avergae day to day queries. A query that requires more than this means that it has more than 100 "iterators", where an iterator is a table scan, a nested loop join, a sort, a merge, an hash join etc. So as you can imagine, 100 should be enough. When the limit is reached the script stops. There's an option to increase the limit, but you should think twice (or more) before using it. An high value can lead to a FORK BOMB. Also, because of the recursion, the script may go over the limit of user processes you have defined. Currently I have no way around this.

Acknowledgements

I must publicly thank several people who somehow contributed to this script. A special thanks to Jacques Renaut from R&D who provided invaluable technical guidance, and allowed me to proceed when I was stuck trying to understand internal structures. Frank Arias, a technical support engineer was crucial in getting it to work on Solaris. Adelino Silva, another technical support engineer was the first beside me to test it and provided valuable input. Jonathan Leffler provided some important feedback. Finally two customers, one in Portugal who provided feedback on HP-UX and another in the US who was fundamental for solving Solaris specific issue.

galileo@solstice.onlinedomus.net:informix-> ixtempuse -hixtempuse: -V | -h | [-s [-t temp_table]] [-u ] [-d ] [-D [-I max_iterators] -V shows script version -h shows this help -s : Show information for a session or list of sessions (separated by commas) -t : Show temporary table structure for a single session/table (ALL for all) -u &ltuser list> : Show information for a user or list of users (separated by commmas) -d &ltdatabase list> : Show information for sessions in a database or list of databases (separated by commas) -D &ltdbspace list> : Show information for a dbspace or list of dbspaces (separated by commas - must be temp dbspaces) -I &ltmax_iterators list> : The script can generate an infinite recursion if something goes wrong. To prevent this there's a limit of 100 iterators that can be overriden with this parameter But be advices that in most cases if the limit is reached, it means the script as a flaw. Apart a script bug, this situation should only happen on very complex queries which are unlikely to appear frequently. Overriding the limit may cause a FORK BOMB!!!Ex: ixtempuse -s 43,35,4galileo@solstice.onlinedomus.net:informix->