- High Overhead
- A separate application to manage
EXECUTE FUNCTION TASK ("SET SQL TRACING ON", 1000, 2, "high", "global");
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.
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]
So as I'm messing around with the Informix Ultimate-C edition for Mac, I also am looking at CSDK 3.50 too. And while there is nothing wrong with the product, it does make me wonder what else a Developer might like to see with the product. In out (C)lient (S)oftware (D)evelopment (K)it we have the following:
If you happen to be on a platform other than Mac, you also get JDBC.
What else would you like to see in a CSDK bundle? Maybe I'm getting spoiled but looking at Microsoft and Apple, if you get an SDK you actually get a a real toolkit, something that also helps you build rapid prototypes, or even full fledged applications. I honestly think that IBM has a solution there already too. The free version is called Data Studio, and with just a little tweaking IMHO, could be the exact gui programming tool I see missing from the CSDK bundle. Even then though, I think all we would see on Unix platforms would be JCC, JDBC, ESQL/C and ODBC, and the question then is "is that enough, or do you want more?"
Would PHP, Ruby, and perl be enough? What else could/should a developer want for a CSDK bundle? I want to hear you thoughts on the matter.
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.
So can I get a show of hands that have used IBM Data Studio Developer? Anyone?
The product comes in two flavors, Data studio, the free version, and Optim Studio Developer,
which is the paid version.
If you do Java Coding, or currently use Eclipse, it's a very nice tool. If you use Eclipse you can
add it as a plugin to eclipse, or you can re-install the API.
Pamela Siebert and Venkatesh Gopal have done a Developerworks article that covers how to
get up and running with Data Studio.
If you're using Informix Dynamic Server 10.00.xC5 or later you can make use ofan undocumented performance feature known as Virtual Processor Memory Cache.
The VP memory cache holds a configurable amount of memory in a location thatallows userthreads to retrieve memory blocks without obtaining the sh_lockmemory latch.In some situations (mainly when there are many CPU VPs) the memory latch canbecome a bottleneck (use onstat -g spi to monitor usage of this latch).The VP cache can also reduce the search time for large memory blocks used insorts and other operations.
This feature can improve performance on busy systems with a large number ofCPU VPs and spare memory capacity. By default the VP memory cache is set to 0(switched off). Youcan set the size in kilobytes with the onconfig parameterVP_MEMORY_CACHE_KB or set it dynamically with onmode -wm.For example to set VP cache to 800 Kb per CPU VP:
Note: Apart from 0, the minimum allowed setting is 800. The total size of the VPmemory cache (VP_MEMORY_CACHE_KB * number of CPU VPs) should be no greaterthan 10 to 20% of shared memory size. 10% should be ample in mostcircumstances. In tests 800 was a good value and gave 97% cache hit rate.
A system may benefit from a higher cache setting if it has a large numberof userthreads, or many sorts (or other operations which involve large memoryblock allocations). The VP memory cache does increase consumption of memorysince some memory is not shared between CPU VPs. If SHMTOTAL is set and mostof the available memory is being used consider increasing SHMTOTAL accordingly.
To monitor performance of the VP memory cache use the undocumented onstatoption onstat -g vpcache.
Note that blogged does not equal documented or supported. This feature will besupported in a later release, so for now use at your own risk. Internaltests have shown good results and R&D are interested in external feedback.If anyone tries it pleasesend me an email whichI will forward, or comment via the blog.[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]
Perl DBI support for IDS has been around for a while, but not too many articles about it, so it's a refreshing change to see the new developerWorks tutorial by Daniel Herbert called Using Perl with the Informix Dynamic Server, which covers setting up the Perl Database Interface (DBI) to work with IDS, CPAN module installation, driver installation, script automation, data structures, pitfalls, Perl interaction with IDS (including using BLOBs and CLOBs).
You need to sign-in to developerWorks to access the tutorial which is in the form of a PDF file.[Read More]
The IDS virtual appliance download had a problem in the last few days. I am glad to report this is now fixed and a new version 1.2 (containing IDS 11.50.xC3) has been made available and can be downloaded as a self-extracting executable here: https://www14.software.ibm.com/webapp/iwm/web/reg/download.do?source=swg-informixfpd&S_PKG=dl.
A compressed tar file is also due to be uploaded in the next day or so. Non-Windows users who would like to avoid the self-extracting executable and can't wait for the compressed tar file can rename the .exe file as a .rar file and use an unrar utility to extract it.
Thanks to "patrick", "hatrick" and "rougail" for alerting me to problem with the tar file last week (unfortunately Thanksgiving got in the way of getting it fixed).
New features and updates since 1.0
There have been many incremental improvements to the virtual appliance since version 1.0. Here is a list of some of the changes...
What would you like to see improved or changed? Please comment.
I am told the updated release notes will appear on the download site today, but here is a copy..
IDS 11.50.xC3 Developer's Edition Virtual Appliance V1.2========================================================[Read More]
Sitting on a flight to Las Vegas for the IOD conference affords an oportunity to tear myselfaway from the laptop and turn my attention to Carlton Doe's new book: Administering Informix Dynamic Server - Building the Foundation.
I should start by admitting a bias. I like Carlton, and from before Iknew him I've liked his writing; he writes not only with energy and passion for the subject, but with a great deal of technicalknowledge and experience. What this means is that you get all the information you would expect, but also something more than just adry technical book. So, needless to say, I was looking foward to a chance to read this book.
Is there a need for an Informix book when there is a comprehensive set of high quality Informix documentation available online?
The documentation is great but there is a huge amount of it, and zeroing in on exactly what you need can take time. Carlton addresses this question in the introduction:
One thing this book does very well is provide current information. A greatdeal of new functionality has been added to IDS in the last year, andknowing how much of a lead time publishing deadlines impose I am impressed by the amount of up to date 11.50 feature descriptions thebook has. It can be stressful trying to write about a feature beforerelease while the developers change it as you watch (OpenAdmin Tool which keeps getting new features is a good example).Somehow Carlton has managed to stay current, and write with an inside knowledge ofwhich features are likely to change.
I'm glad to see a few in-jokes to keep us paying attention. In a section entitledProblem solving with Extensibility a fictitious org chart is introduced,where Mukta, Fred, Kevin and Kassa report to someone called Jerry. That soundsstrangely familiar, though I can't quite place it.
At 424 pages Administering Informix Dynamic Server is small enough tocarry around, yet comprehensive enough to serve as a single referencesource. It also manages to provide a balance between introductory material for new Informix users and advanced technical information forInformix power users. After flicking through the backup and restore chapter I find myself drawn into a few diagrams and now know enough about XBSA architecture to be dangerous.
Overall, an excellent reference that both new and experienced Informix DBA's will find useful to have around.
Books links: Barnes & Noble, Amazon
The IOD conference starts in Las Vegas on Sunday and this is the time of year when I glare enviously at anyone who doesn't leave everything to the last minute and isn't working on their conference sessions.
I am relieved that at least that my Hands on Lab: 1230 Getting Started with the IBM Developer Cloud and Informix Dynamic Server is complete (with a lot of help from co-author Yunming). It's been fun working on this lab and getting to use the cloud features.
What is the IBM Developer Cloud? The IBM Smart Business Development and Test Cloud site describes it as "a dynamically provisioned and scaled runtime environment that provides everything needed to develop and test application code". If you have not tried it, it's worth a look. It's in an open beta phase at the moment, which means it's free to create an ID and launch your own images. Another invite-only beta phase is scheduled to begin in December that will include many new features. In the lab we'll launch our own Informix cloud images, manage connectivity with RSA key pairs, set up web services, HDR and MACH11 on our cloud instances. In case I'm not shamelessly promoting it enough, I also plan to hand out free Amazon Web Services Elastic Compute Cloud (EC2) vouchers at the lab, so if you're attending IOD come along to Breakers-C on Wednesday afternoon (2pm-5pm) and try it out. We'll also be demo'ing the Informix Developer Cloud image in the Expo hall at certain times.
If you're planning on being there, hope to see you at the Community reception at 8pm on Sunday.
Waiting at McCarran airport in a contemplative frame of mind for a flight back to Portland afforded some time to reflect on the recently completed IOD 2008 conference..
Sadly I did not get to attend as many talks or keynotes as I would have liked to this week, having had multifarious activities to work on or prepare for every day, but the main themes of the conference for me were...
Virtualization and Virtual Worlds
We announced the IDS 11.50 Developer Edition virtual appliancelast week and are seeing a lot of interest in it. Informix virtual appliances were present at various places in the conference Expo hall. I was manning the Intel booth where we were showing IDS and DB2 SLES 10 SP2 virtual appliances running under the VMWare ESxi hypervisor layer which comes optionally pre-installed on IBM Blade machines.
At the VMWare booth the first 100 people to fill in a survey were given cool looking 4GB USB drives containing the IDS and DB2 appliances. After that they were handing out DVD's.
Meanwhile at the Canonicalbooth they were demoing an Ubuntu 8.04 LTS (Hardy Heron) version of the IDS virtualappliance. Those Canonical guys are fun to work with, and Ubuntu is hugely popular because, simply put, it's easy to use and itperforms well. Stand by for more newsabout Canonical and Informix in the near future.
Among the conference technical sessions we had an intro to the virtual appliance, and ahands on lab which provided an opportunity to play around and do some programming in the Data Studio environment that comes pre-installed on the appliance. On Wednesday eveningthere was a Birds of a Feather session where some interesting feedback was provided on what people thought of the initial virtual appliance developer edition, and what they would like to see as IBM extends its virtualization strategy for Informix. One thing thatwas great to hear in this session was the favorable response from people who have tried itso far. It works, it's easy to use.
It was interesting to learn how people intend to usethe current version of the VA. Someone from a "very large retail chain" mentioned that many of their vendors supply solutions in the form of self-contained virtual appliances these days, andit will be useful to him to give the IDS appliance to any of these vendors who claim theydon't do Informix and need a starting point for Informix application development.
Another use for the virtual appliance that is gaining traction is as a core part of the Informix academicinitiative. The components required to make IDS an excellent educational platformhave fallen into place in recent times. The first was the release of the free IDS Developer Editionin IDS 11. Now everyone had access to a free IDS edition with all features enabled.Now with a virtual appliance that contains the Informix development stack, pre-configured,in one place, along with getting started tutorials, it makes for a self-contained classroom and laboratory just waiting to have a database administration and programmingcurriculum developed around it. Here's another idea - when the Ubuntu IDS virtual applianceis available, install the Edubuntu package on it - then you have a state of the art educational aid for a range of age groups that also teaches database programming.
At the bleeding edge of Informix research Lance Feagan is doing some cool things with IDS and Virtual Worlds. Lance had a couple of very interesting presentations and demo's in this area which I'd like to post more about. In the meantime take a look at this recent IBM Database Magazine article Informix Dynamic Server bridges virtual and real worlds to get the idea.
I had an IDS on Windows Deep Dive presentation scheduled for the final 11:30 to 12:30 sloton the last day and was not especially expecting anyone to show up. Lucky for me 7 determined people turned out to see 56 slides of non-stop Windows - a mixture of IDS on Windows architecture, recent Windows specific features, notes on embedding IDS on Windows plus some performance and troubleshooting tips. That reminds me, I like the new name for Windows 7, Windows 7. Inspiring.
I have been known to fritter away as much as $10 when Ivisit Las Vegas. On Thursday evening I happened past a group of Informix slot machine junkies (Erik, Howard and Madison) and decided to throw caution to the wind and engage in a bout of reckless gambling. At $4 in it was not looking good, then I hit the jackpot and won $7. That was enough for me, I cashed out with a healthy profit. That's right, for a good time in Las Vegas don't hang out with me.
There were some fun parties at IOD (I hope there are no photos from the Halloween party). HP and Intel showed their renewed enthusiasm for Informix by sponsoring an IIUG reception. Check out Spokey's Informix Zone blog where he has kept track of who has posted photos.
Jacques Roy somehow managed to report on every day of the conference in his: Informix and Computing blog. If you are more of the visually stimulated type,turn to roving photographer and Frenchman Jean-George Perrin who has amassed a large collection of conference images on his Facebook site.[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:
Since I last wrote about CoLinux it has become a useful tool within our team. In addition to the advantages of having a concurrent virtualized Linux server running natively on a machine that would otherwise be limited to Windows, a key factor for me is that the Linuxfilesystem is a simple Windows file. After configuring an instance (say installing IDS, CSDK, gcc, g++, make, Apache, php, Informix_PDO, Zend etc...) the filesystem can be saved as a zip file and shared with others.
If I do something reckless (like overwrite an OS threading library with a bad version) and trash the system, as long as I saved the filesystem prior to the misdeed, recovery is as simple as copying a file and restarting coLinux.
Setting up X-Windows is straightforward, the easiest way seems to be to install and start the CygWin X-Server on Windows and use the xhost command to allow access to the desktop. Set the DISPLAY environment variable in Linux to point to the Windows IP address, and x applications (once they're installed) should then display on Windows.
Networking issues - Basic network setup is straightforward and covered in the Wiki. This ONLamp blog post also has some useful suggestions for unplugged machines. I have yet to find a good wireless network solution though.
Update: This Wiki entry suggests specifying the MAC address of your wireless card to get wireless working with WinPCAP.
Typical usage scenario
Problem: We have a conference demo which uses the LAIP stack (Linux, Apache, Informix, PHP) - and the conference organizers only provide a Windows PC and a demo setup window of a few hours.
Solution: Set up the demo in CoLinux and when it's ready zip the filesystem and take it along on a flash drive. Set-up time is as long as it takes to install CoLinux and WinPCap, unzip the filesystem and start CoLinux on the demo machine. Alternative options would be to convert the demo to Windows (WAIP) and install and configure all the software, or maybe create a customized Linux live cd, but the CoLinux solution seems the simplest, and the same zipped archive can be used by anyone wishing to give this demo in the future.
As with other virtualization solutions it is also possible to run multiple CoLinux instances on one machine capable of communicating with each other, which would make for a useful self-contained high availability (e.g. HDR or ER) test/demo between two virtual servers.[Read More]
There's a Cloud Computing for Developers virtual workshop taking place on October 1. Hosted by technical evangelists at Amazon Web Services and IBM it will cover topics such what you need to know start writing cloud applications and some of the benefits, as well as the IBM solutions available.
Check here for more details and to sign up: http://www-304.ibm.com/jct01005c/isv/iic/events/description.jsp?event=4337D2D62C6B27C78625761F0054D275A nice thing about this workshop is that as well as being free to attend, you get a free coupon for using Amazon Web Services. So if you've been planning to play around with an Informix Amazon Machine Image but don't enjoy parting with money this is a good opportunity to give it a try.