OAT: One Admirable Tool

One first warning: I'm still lacking time to write the kind of articles I like, which are focused, technical and relatively deep.
Having said that I feel better proceeding with yet another "chat like" article. And in this I would like to write about Open Admin Tool, or simply OAT.

I had two great sessions in IIUG 2010 conference, which although not specifically about OAT, were somehow related to it. I'm talking about John Miller session about Auto Update Stats, and Hyun-Ju Vega session covering SQLTRACE. Unfortunately I had to miss the session about OAT itself from Erika Von Bargen (i was presenting at the same time). Now, why did I like this sessions so much? The speakers were great but they were not the reason. The reason is fairly simple: They changed the way I thought about some things and they made me look into OAT again. I don't recall having written many things about OAT, but to be honest I though it was nice, but maybe more directed to the new people starting with Informix. Long time Informix DBAs obviously have a pack of scripts for the trivial stuff: running update statistics (I wrote my own scripts based on previous work from Informix staff and obviously we all know dostats from Art Kagel), check some performance issues (sessions with more CPU usage, tables with full scans, tables with more read/writes etc.), historical data from the instance (profile counters, table access stats etc.).

So, a nice tool, much better than previous attempts like Informix Server Administrator (ISA), nice looking but maybe for "newbies". Honestly this was more or less in my mind, even though I did not have full conscience about it.

Then I assisted this two sessions. And during the sessions we were guided through lots of "accessories". John showed several tasks, the task scheduler, the intelligence behind the auto update stats, and eventually even without noticing it, he effectively showed the audience how easy it is to work with OAT. Then Hyun-Ju, guided us through a real case scenario where SQLTRACE was key to solve a customer issue. Again, OAT was not the main focus, but the ease of usage and how we can take advantage of SQLTRACE in OAT SQL Explorer again surprised me.

Then a real life scenario came... Today, while working on a customer, I was asked to help the DBA team to understand a big memory consumption from some sessions in a development instance. The developer team really didn't noticed anything strange, since the engine was doing what was expected. But the DBAs noticed some sessions occasionally grabbed around 8-10 times the maximum memory usually consumed by most of their sessions. The more or less strange thing was that the session allocated the memory but when we got to see that, the memory was mostly free (although still allocated to the session). So, no strange pool usage was evident. Since my job their is also to try to pass some knowledge, I showed them how to setup SQLTRACE for one specific user and we asked that user to run the job(s). After lunch we had everything explained in the SQLTRACE buffers. The onstat -g his could be used to get evidences, but I decided to give OAT a try. And there it was, in a graphical manner all the session steps. We noticed that they had a terrible amount of recursive procedures (actually proc_a calls proc_b and proc_b calls proc_a) inside a foreach loop. So a lot of stack was necessary to hold the context during execution and so the memory was allocated and then became free.

Could I have made this with other tools? Probably. Lots of onstats, scripts etc. Would it be so easy? Not even in my best dreams!

The true magic about OAT is not OAT itself. It's how OAT glues several new features and makes them usable. Think about some things we can do in OAT:

1. Manage and use SQL history
   
2. Manage all the engine programmable tasks
3. Generate performance and usage reports
4. Change the engine configuration
5. Optimize storage
6. Manage your cluster and replication

All these have something behind that you frequently see in the "new features" section of the release notes (using the same order as the previous list):

1. SQLTRACE
2. Database scheduler
3. Automatic tasks and internal engine profiling
4. SQL API
5. Compression
6. MACH 11

I could have made the list bigger. The point is that probably for the first time in Informix history we have a tool that matches the "features". This has always been one of the most criticized (what was probably fair) aspects of Informix. But since v11.10 and the beginning of OAT we see that OAT has been upgraded fixpack after fixpack to match the engine new functionalities. This shows it's not another tool that tries to keep critics away, but is in fact a fundamental piece of the engine. And it follows the engine "DNA": Simple to install, simple to use, reliable and light.

Is it perfect? Probably nothing is... I think one area or aspect could be improved. And here I'm just echoing a customer thoughts. OAT should be able to provide a view and working mode for enterprise wide environments. Meaning some of it's features should be aware of all the instances configured in a customer environment. Some simple examples:

• A single alarm viewer page for all the instances (maybe "all belonging to one group")
• The monitors could collect/centralize into a common repository
• The monitors should have self conscience of the instances in a MACH-11 cluster

Some of these points and others I don't mention here could be provided by:

• Replicate each instance captured data to a common repository (either by ER or simple tasks)
• Create a plugin for showing data in that centralized repository
• Change some of the monitors to INSERT also the server name and direct the data to the primary server (for read only secondaries) using for example the connection manager

So, this limitations can be solved by customers. But it would be nice to see some improvements in the future.
I'd like to end the article sending my compliments and appreciation to the OAT development team and leave here a few links to help you understand what's behind OAT:

• Open Admin Tool home
  http://www.openadmintool.com/
  
• Manage your Informix database with the IDS OpenAdmin Tool
  http://www.ibm.com/developerworks/data/library/techarticle/dm-0807kudgavkar/
• Build plug-ins for IBM's OpenAdmin Tool for Informix Dynamic Server
  http://www.ibm.com/developerworks/data/library/techarticle/dm-0808vonbargen/
  

IIUG 2010 conference: 3rd and last day

Well, all good things must come to an end, and the 2010 IIUG conference is no exception.
This last day started with a Q&A sessions with some IBM executives and we heard about how they plan to face the challenges ahead. Right after that I skipped the regular sessions and went to one off track session I was anxiously waiting: The Panther session. Kevin Brown talked about all the new stuff and it's really amazing. From small but crucial to big new features we got a clear view of what's coming. Unfortunately everyone present there has to comply with a non-disclosure agreement (NDA) so I can't really talk about it... What I can say is that it's great!!! But, if you didn't have the chance to be there it does not mean you can't find more about it. You just have to join the Panther beta program. Madhuri Ahuja, the Customer Validation Manager will surely welcome you and lead you through the process.

In the afternoon I was present in two sessions. First one, presented by Hyun-Ju Veja, was about the use of SQLTRACE and OAT. Hyun-Ju works in the advanced problem diagnostic (APD) team, and she based her presentation on a real case scenario, which is always great. SQLTRACE is a real powerful and flexible (specially on latest versions) feature and Open Admin Tool (OAT) makes it very easy to use.
The second session was a must... Darin Tracy, also from APD talked about the ins and outs of the checkpoints specifically in a MACH-11 environment... The added benefit was the presence of Madison Pruet, the replication architect in the room, so things got really deep. I would say this was one of the most useful sessions in the conference, and it's always nice when you face the truth: we usually know less than what we think, meaning some things we assume can be wrong.

Not related with today, there are also some other news. First, I'd like to point out that IIUG got a new area in their website. If you go to http://www.iiug.org/opensource you'll see that there's a big movement in the Informix community related to open source. You can find there some new stuff that can allow some of your favorite open source projects to work with your favorite database. Even better, this is clearly not a work done, but instead it's a work in progress. Feel free to participate either by helping, or simply by saying what open source applications you would like to use with Informix. To start with, you can check a beta version of some changes to Hibernate and MediaWiki. Hibernate is used by many people and it was known to have some issues with Informix. Hopefully they're all solved in this fix, and it will be submitted to the project.
MediaWiki needs no explanations besides saying that it's the software that runs Wikipedia.
To end the post I just want to thank all the IIUG board and all the people who worked to get the conference up and running, specially because the current times are not easy times, with all the economical problems etc. We were told that 22 countries were present in the event. USA was big as expected, a lot of people from south America (the Brazilian crew was enough to make a soccer team, and apparently they did :) ), several European countries like Portugal, Spain, UK, Germany, Czech Republic (hope I got this right), one person from Cameroon, others from Russia (speaking of which Tatiana Saltykova got the lead again in Advanced Data Tools Fastest Informix DBA Contest III - congratulations), and one person from Malasya (again, hope I got this right)

IIUG 2010 conference: day 2...

I like to end the day with this report... I'm not sure if it's because I really want to report on all the exciting things happening in Kansas City (and I'd say this usually is a quiet place), or simply because I want to write posts here to feel better with my conscience... Even so, my Brazilian friends keep punishing me for leaving the blog stalled for so long :)... And they are right.

Today I had the opportunity to listen to Art Kagel and understand why he is so highly rated in the community, and then I skipped the second part of his presentation to see what Carlos Garcia had to say about the tax department in Guadalajara (Mexico) implementation of Informix cluster facilities. Then a short break, and there I was listening to another guru, Lester Knutsen from Advanced Datatools, explaining us how to use sysadmin. I was also tempted by Mark Jamison's presentation about Encryption Expert, but we can be only in one place at a time... Lunch time... And then I went to learn about the ins and outs of I/O in Informix, presented by Rakesh Naik. This is a very nice topic, and I believe I have it on the list for possible articles here. Then I had a great presentation (one of the best so far) with Scott Lashley. Believe me... If you want to see someone passioned for his work and that instantaneously knows how to win an audience, please take one of his sessions. About the session itself it was about Informix indexing. Had we three more hours (Scott's words) and John Miller could have explained every thing about the b-tree scanner :)
But John was next with a session about Auto Update Statistics, and to be honest I have been a bit skeptical this new feature. But after listening to him I am wondering why....
Last, but not the least, I went to a session with Madison Pruet... Truth is, I didn't check and was expecting the usual slideware routine, but this was a birds of a feather session. And this means you just sit and chat with an Informix architect and some of his team. And I ended up bothering them with several suggestions/questions which they took seriously just like other situations raised by some of the customers present. In fact it's the second time I feel I'm being listened by someone who really makes a difference, and this is the true value of this event. If anyone thinks the only value in this is to learn some bullets in slides, then they're completely missing the point...

The night ended with a "casino", and I'm not a gambling fan.... so I spent some time talking with various people, not necessarily about Informix. Things like Brazil, ifxdeploy, Nigeria, compression, Goa, sqlj.install_jar, world soccer cup, visual explain...

Looking forward to the last day, but I'll be very sorry when it's over...

1st day at IIUG conference

This has been a busy day... it started out with a presentation from Jerry Keesee with some funny notes... He showed us that he reads the c.d.i.... Then I decided to try the IDS 11 certification and went straight to the exam... It took me around 1H10, but I managed to get 65 out of 74 questions right... This means I'm now an happy System Administration for IBM Informix Dynamic Server V11.
The morning was already won, but I had the chance to attend an interesting session about external tables, presented by Khaled Bentebal, president of the French user group. Some questions were raised and things got really interesting. External tables are not only flexible, easy to use but also damn fast! They deserve an article...

After lunch I had my own session to present.... I will have to publish a complete post about the same topic, but I can't promise that until the conference is over. After that I check up on Jonathan Leffler and David Desautels sessions about security, auditing and compliance.

By the end of the day we had a very clear presentation from Rob Thomas (you should recognize the name from his letter to the community) followed by General Manager, Information Management Arvind Krishna. Rob talked about the new Informix strategy. He was very clear about what he will do, and what he needs the community to do.

The night ended somehow unexpectedly... A band came on stage and started playing some very cool Rock tracks.... That would be normal. But I was very surprised when I found out they all work for... IBM!!!... They finished with "Whole lotta love" from Led Zeppelin and I can assure that they could have a second career... Someone passing by told me that it would be wonderful if IBM could play so well together as these guys do ;)

The day I managed to get time...

Well... I'm currently in Kansas City, at the place where the 2010 IIUG conference will happen (in fact it already started). I'll have the pleasure of presenting one of the sessions, and while preparing it I was writing a brief introduction of myself... bla, bla, bla.... author of a blog... Then I wonder when was the last time I wrote something here and I felt bad... Around 6 months! And in this time a new fixpack was released (FC6) so there is plenty to write about... It's a fact I've been very busy professionally, but I guess having a 1 year child really takes away a lot of what used to be free time (and I'm not complaining of course!).

I realized that this is the first really "free" day I have in a lot of time. The conference started with the pre-conference tutorials and since I didn't register for them I have some free time. The fun already started. I had the chance to finally meet some people (Gary Ben-Israel, Stuart Litel, Miguel Carbone ...) and at breakfast there I met some Informix users and we exchanged some useful experiences...

My presentation will be about using EXPLAIN_SQL with any tool. EXPLAIN_SQL was introduced in v11.50 and allows us to get an XML representation of the query plans. In the new features of IDS 11.50 documents and presentations this was called Visual Explain, since it can be used by tools to show a graphical representation of the query plan (like IBM Optim Data Studio does). But not many tools have implemented it, so my point is to prove and show that it can be used with any tool (even character based).

Hopefully some of the readers are here and maybe attending this session (although it's very hard to choose from the session list. Ideally you'd want to be in two or three sessions at the same time).

Links revision...

Some links were changed and specially several Informix related blogs were added. It's really nice to see new Informix related sites and authors publishing info. In particular I'm very glad to add several Brazilian sites. This is really important for the Portuguese speaking audience. The links added are:

• Advanced DataTools
  This was a big miss. Lester Knutsen is probably the most noticeable element of this IBM Partner who just recently organized another edition of the Fastest Informix DBA contest
• IMartins Tudo Sobre Informix
  A Brazilian site with articles and information related to IBM Informix
• AskPontes
  A blog from my Brazilian colleague Vagner Pontes
• InformixBr
  Another blog from another Brazilian colleague Pedro HT Rodrigues
• Informix DBA
  A blog from the well known Andrew Ford, a member of the International Informix Users Group board of directors
• Informix Tips
  A blog from Jeff Filippi from Integrated Data Consulting
• Informix Administrators Blog
  A blog from Markus Holzbauer, a German colleague and a long time Informix user (started on v4... same as me)

So, as you can see I've been away from a lot of new activity. I hope I've recovered the gap. If you have any info about any Informix related site that I should list, please don't hesitate to leave a comment or send me an email.

IDS Roadshow em Lisboa

This post regards an IDS Roadshow in Lisbon, so it will be written in Portuguese.
A IBM vai realizar o IDS Roadshow 2009 em Lisboa nos dias 13, 14 e 15 de Outubro.
O evento terá lugar nas instalações da IBM em Lisboa (IBM Forum Lisboa) o parque das nações.
A agenda do evento inclui:

1. Últimas funcionalidades na versão 11.50
2. IDS Data Warehouse ( Technical hands-on)
3. Storage Compression ( Technical hands-on)
4. Open Admin Tool
5. Enterprise Replication
6. Solid DB/Change Data Capture

A participação é gratuita, sendo os lugares limitados. Para se inscrever envie um email para ibmdm@pt.ibm.com, referindo o evento.

A apresentação estará a meu cargo.