IIUG World 2019

IIUG 2020 Online World Conference Presentations will begin soon!

Monday, September 24, 2012

In search of rownum

Often people ask about various features supported by other RDBMS systems that Informix does not support.  I am rarely bothered by these issues, not because I don't use Oracle, MySQL, PostgreSQL, or MS SQL Server - I don't when I can help it - but rather because for the most part, the required functionality is either there in another form, is not really necessary, or can be emulated easily enough.

Examples are the INTERSECT and EXCEPT join operators.  Informix does not support either syntax. However, the results of an INTERSECT join are identical to the results of a normal INNER join with only columns from one of the tables included in the projection clause which is of course supported. The results of an EXCEPT are identical to those of a LEFT OUTER JOIN with only those values from the left table that do not match any rows in the right table returned.

INTERSECT example - suppliers against whom at least one order has been processed:
select distinct supplier_id
from suppliers
INTERSECT
select distinct supplier_id
from orders;
returns the same result set as:
select distinct orders.supplier_id
from orders, supplier
where orders.supplier_id = suppliers.supplier_id;
EXCEPT example - suppliers against whom no orders have been processed:
select distinct supplier_id
from suppliers
except
select distinct supplier_id
from orders;

returns the same result set as:

select suppliers.supplier_id
from suppliers
left outer join orders
on suppliers.supplier_id = orders.order_id
where orders.order_id is null;

Another is the Oracle ROWNUM pseudo column.  It returns a sequential number, starting from 1, indicating the order in which a row was processed by the server.  Most commonly it is used in the WHERE clause of a query to limit the number of rows returned or to walk through a result set in discrete chunks. 
Examples:

select * from sometable where rownum <= 100;
select * from sometable where rownum > 100 and rownum <= 200;

These are easy to emulate using the ANSI FIRST and SKIP verbs which Informix does support:
select first 100 * from sometable;
select first 100 skip 100 * from sometable;
However, sometimes ROWNUM is used in the projection clause of a query where it represents the selection order as a computed column.  To emulate this use, we need to create two SPL routines.  The procedure init_rownum() below initializes the value returned by the next call to the rownum() function to '1' and is needed if rownum() is going to be used more than once in a session and is expected to return values starting with '1' each time.  Here are the routine definitions:

CREATE FUNCTION  rownum () returning int as rownum;
define global counter int default 0;
let counter = counter + 1;
return counter;
end function;
CREATE PROCEDURE  init_rownum ();
define global counter int default 0;
let counter = 0;

end procedure; 
         
       
In use these routines look like this:

execute procedure init_rownum();
select rownum() as rownum, tabname from systables;
Now suppose you want to sort the tabnames by name but return the rownum of the original order that the rows were processed by the engine:
select *
from (select rownum() as rownum, tabname from systables)

order by tabname;
That's it, nothing profound today, just this little tidbit of technique.  Oh!  Don't forget to check out my TechTips article about Derived Tables in McPress at:

http://www.mcpressonline.com/sql/techtip-combining-aggregate-and-detail-in-the-same-sql-result-set.html


Thursday, September 6, 2012

On Becoming an Informix Tuning Genius

If Apple can promote a bunch of inexperienced college aged nerds to "Apple Genius" by giving them a couple of days of training and hanging a badge around their necks, why can't I create a cadre of "Informix Tuning Geniuses" by giving a class for, fully adult, experienced DBAs and by hanging a certificate on the attendee's wall?  The answer, of course, is "I can!" I just haven't called it that.  Until now.

I am often asked "How did you get to be so good at tuning Informix engines?"  When I hear questions like that my normal human modesty and my massive personal ego go to war.  Modesty wants to answer, softly, "Aw shucks, I just pay attention to what's going on is all."  Meanwhile, my admittedly huge ego wants to scream, "Yeah man. I am awesome!  You'd have to work hard for a long time to get where I am!"  Unfortunately for my ego, my modesty's take on the world far closer to the truth.  When I can hear myself over my ego's protests, I have to admit that I credit several things:
  1. Paying attention really is a big part of what I do, the hard part is what to pay attention to.  In general:
    1. RTFM - Yes, I really do Read The Fine Manuals and pay attention to what I read.
    2. Pay attention to other Informix users and users of other RDBMS products (yes Virginia, even Oracle users know a thing or two about managing a database system).
    3. Ask questions of IBM developers and support people, and pay attention to the answers.  Try to understand how Informix works so what I see in #2 below comes together.
  2. Look at the data that's available from the OS and from the server and dig down until everything makes sense. - More paying attention I guess - Hmm a theme is developing.
  3. Try things out.  Play.  We are Informix DBAs not Oracle DBAs.  We have time to try things out and test since we are not busy 24x7 keeping the damn server online.  Oh, and pay attention to the results.
  4. This was a journey that started for me with a single number on an onstat -p report (bufwaits) that didn't make sense.  I poked and analyzed and spoke to Informix developers until it did.  So, dogged determination is also a part of it.
Sometimes the questions I get are more like "How can I become as good as you?" or at least that's how my ego hears it. ;-)  Here I have a good answer.  It relates to 1.2 and 1.3 above.  Learn from others to kickstart your own journey.  For this I have a good solution, and yes it is a plug, or, actually, two plugs.

Plug #1:
Contract with me to perform a Comprehensive HealthCheck on your server's performance.  I can do that remotely, and that has its benefits, but if you have me come on-site to perform the HealthCheck in your facility, we can work on it together and I will show you what I do and how my thought processes go to pull out the information from the data and make the recommendations that I may make.

Plug #2:
At Advanced DataTools Corp. we run periodic classes including, specific to this discussion, the Advanced Informix Performance Tuning course.  Attending one of these courses you will learn from two of the best, myself and Lester Knutsen, how to tune your Informix server to scream through transactions by doing just that.  You will work hand-in-hand with Lester and me to tune two different database scenarios.  In one you will learn how to get 40 hours worth of data loading and reporting completed in under 5 minutes.  In the other you will tune an Informix instance that, by default, can only complete about 4,000 transactions per minute until it will rip through almost 60,000 transactions per minute.  Between test runs we will review the relevant portions of the Informix manuals and learn what is important to monitor and how to interpret what you are seeing in the data.

The next Advanced Performance Tuning course is scheduled for December 3-6 2012 in our Annandale, Virginia training facility.  But you don't have to travel all the way from Albuquerque or Mumbai to the US Capitol District, you can attend virtually over WebEx.  Currently about half of our typical class is comprised of remote attendees.  Class size is small.  We will only serve up to seven students at a time.  In addition, if you have three or more people from your company that you want to train, we can bring the course to you on your schedule and customizing it for your environment.

Final bit of plugging, here is the URL to ADTC's web site page that discusses out training offerings:
http://www.advancedatatools.com/Training/InformixTraining.html

So, do you want to become an Informix Genius?  Apple Geniuses learn from each other once they get behind that counter and are hands-on.  If you are already an Informix DBA or a Developer/Jack-of-All-Informix, are you already behind the counter but you don't have the Genius Badge yet?  Learn from me.  Learn from Lester.  Give us a call or send us an email and we will schedule you into a course.