Monday, March 31, 2014

The importance of the OLAP functions / A importância das funções OLAP

This article is written in English and Portuguese (original version aqui)
Este artigo está escrito em Inglês e Português (versão original aqui)


English version:

Introduction
In a recent customer real situation I was faced with an optimization problem: They had a query that run in a certain time (let's assume around 1H), but they noticed that they were getting "duplicates" (not exactly a row duplicate, but records with duplicate values that should "identify" each row).
This happened because the data model was created in a way that allowed each "object" to be represented more than once in the table (assume different "lives" of the object). And they wanted to remove those "duplicates". Problem was that when they rewrote the query, it would take several hours (it never completed...).
Although I shouldn't use the exact data model, due to privacy restrictions, I'll try to create an equivalent model so that I can show you an example. Let's assume this table structure:

CREATE TABLE customer_tax_code
(
    customer_num INTEGER,      -- Customer identification
    customer_plan CHAR(5),     -- Customers are using some plan (INDividual, COMPany, MULTI etc. at each moment, but they may change)
    customer_service SMALLINT, -- Customers may have several services active
    customer_seq_id SERIAL,    -- Each time a customer, in a specific plan, changes to another tax_code, this sequential_id is incremented
    customer_tax_code SMALLINT -- The tax code which defines how the customer will be taxed for a specific service. Different tax_codes may contain different discount levels, bonus or promotions etc.
);
The basic query was really something like:
SELECT
    customer_num, customer_plan, customer_seq_id, customer_tax_code
FROM
    customer_tax_code
WHERE
    customer_service = 1;

Note that I didn't consider any indexes. But this query would be very simple and would have only two possible query plans:
  1. A full scan if the number of records with customer_service = 1 was the majority of the table
  2. Some index containing customer_service, if the histograms showed that the percentage of records with customer_service = 1 was relatively low
The table had around 100M records. And the result set would contain several records for the same customer_num, customer_plan. These would be considered duplicates as for each pair customer_num/customer_plan they would want just the "last" record. Note that if a customer ever changed his plan they would like to collect all previous history for the customer while he changed plans, but inside a plan they would want just the "last" record. In other words, they would want the data for the records with MAX(customer_seq_id) for each pair customer_num/customer_plan. But because they also wanted the customer_tax_code field, a simple MAX() and GROUP BY would not work (we'd be forced to include all the non aggregate fields in the projection clause - or select list - in the GROUP BY, and that would destroy the purpose).

The solution they had was roughly this:
SELECT
    customer_num, customer_plan, customer_seq_id, customer_tax_code
FROM
    customer_tax_code a
WHERE
    a.customer_service = 1 AND
    a.customer_seq_id = (SELECT
                             MAX(b.customer_seq_id)
                         FROM
                             customer_tax_code b
                         WHERE
                             b.customer_num = a.customer_num AND
                             b.customer_plan = a.customer_plan AND
                             b.customer_service = 1
                         );
This would give the desired result.... But a typical query plan would include the options above (full scan or index scan based on customer_service) and for each record would require an index access for the sub-query.
To give you some perspective, the percentage of records with customer_service = 1 was roughly 41% of the table. Around 41M rows.
An index like one of these would help a lot:
  • (customer_num, customer_plan, customer_num_seq_id)
    Would provide the easy access to MAX() for customer_num,customer_plan
  • (customer_num, customer_plan, customer_service, customer_num_seq_id)
    Could improve on the one before as it would include all the filters in the sub-query
  • (customer_num, customer_plan)
    Basic index to easily access all rowids for customer_num/customer_plan pairs
But in any case, this would normally do a full scan and 41M index accesses. And this is painful. I tried several variations like creating an inline view with (customer_num, customer_plan, MAX(customer_seq_id) ... WHERE customer_service = 1 GROUP BY 1, 2) and then join this with the table. Depending on index manipulation, some hints to force a specific JOINS (like HASH JOIN) etc. I was able to achieve a query that would take 2H-3H. Even so, it was a bit awkward, slow and would require hints etc.

Solution
Then I remembered that we introduced the so called OLAP functions in 12.10. And actually one of them provides what we need for this sort of queries (need to find a MAX(), but we require aditional fields besides that one and the ones we're going to base our GROUP BY).
In fact we can generalize this problem (and hopefully also the solution), as this happens every time the data model is created in a way that maintains "history" and we just want the last image of the object for each "state". In other words, all the situations where besides other filters we want the records where:
some_field = MAX(some_field) and the group for the MAX calculation is defined by a set of columns. Many times "some_field" is a date/datetime field and the set defines an object identification.

The function in question is RANK(). It basically calculates a rank of a field value within a "window" defined in another one or more fields (the ones we'd use for GROUP BY in MAX() calculation).

In our case we can get a "rank" of customer_seq_id for each group of customer_num/customer_plan. By default, the record with the lower (in terms of ORDER BY depending on the datatype) customer_seq_id would get a rank = 1. The next one would get 2 and so on. But we can do the opposite. Assign 1 to the record with the highest customer_seq_id, 2 the the one right below that one and so on.

The query would look like:
SELECT
    customer_num, customer_plan, customer_seq_id, customer_tax_code,
    RANK() OVER (PARTITION BY customer_num, customer_plan ORDER BY customer_seq_id DESC) myrank
FROM
    customer_tax_code a
WHERE
    a.customer_service = 1
Note this would not eliminate the "duplicates". It would just "rank" the result set. Actually we need to impose a restriction saying we just want the records that have myrank = 1 (these will be the ones with higher customer_seq_is within the group of records with same customer_num and customer_plan),

Because the RANK() is calculated "in the result set", it doesn't allow a condition "myrank = 1" within the WHERE clause.
But ideally we would be able to use the "HAVING clause" like this:
... HAVING myrank = 1
Unfortunately this raises error -217 (column "myrank" not found). We can't use column alias in the HAVING clause (we can for GROUP BY and ORDER BY clauses). Additionally we cannot use the full column expression in the HAVING clause as it will raise the error:
-25856  Invalid usage of window aggregate in this context.

You can use OLAP window aggregate functions in the Projection clause 
and in the ORDER BY clause of queries.  They cannot be present in 
the WHERE clause or in the HAVING clause (except within a subquery).
So the way to do that is to wrap the query above as an inline view for an outside query that includes the restriction myrank = 1:
SELECT
    *
FROM
    (
        SELECT
            customer_num, customer_plan, customer_seq_id, customer_tax_code,
            RANK() OVER (PARTITION BY customer_num, customer_plan ORDER customer_seq_id DESC) myrank
        FROM
            customer_tax_code a
        WHERE
            a.customer_service = 1
)
WHERE my_rank = 1;
This is executed with a single full scan on the table (around 50m). And doesn't depend on hints, complex queries etc. We just take advantage of the new functions. It was the fastest query plan we could achieve for this query.
Only one issue: Customer production environment is still in 11.50, which doesn't contain these functions. Just an example of how customers loose time and opportunity by sticking to old versions...

To give you a better insight about this problem, I created a test case with some "random" test data that can prove the point.  You may apply it to your environment as I'm testing in a VM and these queries are heavily I/O bound... and I/O is not the best aspect of virtual machines.

Test case
The test data was created with an GAWK script which tries to mimic the original conditions. The script can be found at the bottom of the article. I generated 2.5M rows:
castelo@primary:informix-> echo 2500000 | awk -f generate_data.awk > rank_test_data.unl
castelo@primary:informix->
Then I loaded the data into a table with same schema as above:
castelo@primary:informix-> dbaccess -e stores test_ddl.sql

Database selected.

DROP TABLE IF EXISTS customer_tax_code;
Table dropped.


CREATE RAW TABLE customer_tax_code
(
    customer_num INTEGER,       -- Customer identification
    customer_plan CHAR(5),      -- Customers are using some plan (INDividual, COMPany, MULTI etc. at each moment, but they may change)
    customer_service SMALLINT,  -- Customers may have several services active
    customer_seq_id SERIAL,     -- Each time a customer, in a specific plan, changes to another tax_code, this sequential_id is incremented
    customer_tax_code SMALLINT  -- The tax code which defines how the customer will be taxed for a specific service. Different tax_codes may contain different discount levels, bonus or promotions etc.
) IN dbs1
EXTENT SIZE 10000 NEXT SIZE 10000
LOCK MODE ROW;
Table created.



BEGIN WORK;
Started transaction.


LOCK TABLE customer_tax_code IN EXCLUSIVE MODE;
Table locked.


LOAD FROM rank_test_data.unl INSERT INTO customer_tax_code;
2500000 row(s) loaded.


COMMIT WORK;
Data committed.


ALTER TABLE customer_tax_code TYPE(STANDARD);
Table altered.


CREATE INDEX ix_customer_tax_code_1 ON customer_tax_code(customer_num,customer_plan,customer_seq_id) IN dbs2;
Index created.


CREATE INDEX ix_customer_tax_code_2 ON customer_tax_code(customer_service) IN dbs2;
Index created.



Database closed.

castelo@primary:informix-> 
And then I run the following query to have an idea of how data was generated:
castelo@primary:informix-> dbaccess -e stores distrib.sql 

Database selected.

SELECT
        COUNT(*) num, TRUNC((COUNT(*) / 2500000) * 100, 2) percent
FROM
        customer_tax_code
WHERE
        customer_service = 1;

             num          percent 

         1025501            41.02

1 row(s) retrieved.


SELECT
        COUNT(*) num, TRUNC((COUNT(*) / 2500000) * 100, 2) percent
FROM (
        SELECT 
                DISTINCT customer_num, customer_plan
        FROM
                customer_tax_code
        WHERE
                customer_service = 1
);

             num          percent 

          798315            31.93

1 row(s) retrieved.



Database closed.

castelo@primary:informix-> 
So we should expect a result set with 798315 rows. And we can see that we have 41% of the table data with customer_service = 1 (just like in the real customer situation)
In order to show the difference in behavior between both queries I've used a script which I created some time ago and  is documented in this article. For that I prepared an SQL script for each query:
castelo@primary:informix-> cat test_query_1.sql
SET EXPLAIN FILE TO "query_option_1.txt";
SET EXPLAIN ON;
UNLOAD TO /dev/null
SELECT
        customer_num, customer_plan, customer_seq_id, customer_tax_code
FROM
        customer_tax_code a
WHERE
        a.customer_service = 1 AND
        a.customer_seq_id = (SELECT MAX(b.customer_seq_id) FROM customer_tax_code b WHERE
                             b.customer_num = a.customer_num AND
                             b.customer_plan = a.customer_plan AND
                             b.customer_service = 1
                            );
castelo@primary:informix->
castelo@primary:informix-> cat test_query_2.sql
SET EXPLAIN FILE TO "query_option_2.txt";
SET EXPLAIN ON;
UNLOAD TO /dev/null
SELECT
        *
FROM (
SELECT
        customer_num, customer_plan, customer_seq_id, customer_tax_code,
        RANK() OVER (PARTITION BY customer_num, customer_plan ORDER BY customer_seq_id DESC) myrank
FROM
        customer_tax_code a
WHERE
        a.customer_service = 1
)
WHERE myrank = 1;
castelo@primary:informix->
Note that I've turned on the EXPLAIN and that I'm UNLOADING to /dev/null. This is just a trick to avoid wasting time writing the data to a file and by doing so, I minimize the variables involved that can twist the query processing time. I'd also like to point out that the issue I'm trying to show is basically an I/O bound problem. And because I've just loaded the data, created the indexes and run the queries above, the side effect is that at this point I had a significant portion of the table in the engine cache. To avoid the influence of this in the times, I've restarted the instance (onmode -ky/oninit).

Then I run the mentioned scripts:
castelo@primary:informix-> onmode -ky;oninit
castelo@primary:informix-> /usr/informix/bin/ixprofiling -w -i stores test_query_1.sql

Database selected.

Engine statistics RESETed. Query results:
Query start time: 09:51:29.634374000

SET EXPLAIN FILE TO "query_option_1.txt";
Explain set.


SET EXPLAIN ON;
Explain set.


UNLOAD TO /dev/null
SELECT
        customer_num, customer_plan, customer_seq_id, customer_tax_code
FROM
        customer_tax_code a
WHERE
        a.customer_service = 1 AND
        a.customer_seq_id = (SELECT MAX(b.customer_seq_id) FROM customer_tax_code b WHERE
                             b.customer_num = a.customer_num AND
                             b.customer_plan = a.customer_plan AND
                             b.customer_service = 1
                            );
798315 row(s) unloaded.

Query stop time: 09:56:16.976229000

Thread profiles (SID: 5)
LkReq LkWai DLks  TOuts LgRec IsRd  IsWrt IsRWr IsDel BfRd  BfWrt LgUse LgMax SeqSc Srts  DskSr SrtMx Sched CPU Time    Name        
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----------- ------------ 
496e4 0     0     0     0     831e3 0     0     0     493e4 0     0     0     2     0     0     0     86801 26.71790603 sqlexec     
LkWs         IOWs         nIOW         IdxBR        Name                               
------------ ------------ ------------ ------------ -----------------------------------
0.0          251.79098566 16543        0            sqlexec                            


Session wait statistics:
Thread name                         Condition                           Num. waits   Cum. time    Max wait    
----------------------------------- ----------------------------------- ------------ ------------ ------------
sqlexec                             mutex                               1            23.611186280 23.0        
sqlexec                             mt yield                            1            6508.7673752 6508.0      
sqlexec                             buffer                              17           462525.80710 61764.0     
sqlexec                             mt yield 0                          3929         611561.28945 14680.0     
sqlexec                             mt ready                            86801        799028.07279 14682.0     
sqlexec                             mt yield n                          60           1104496.3218 58390.0     
sqlexec                             condition                           2935         2236864.5528 16698.0     
sqlexec                             running                             23461        26712075.663 53739.0     
sqlexec                             aio                                 16517        251627122.20 3787185.0   
sqlexec                             Total time                                       283560206.29             

Partitions profiles (Database: stores)
LkReq LkWai DLks  TOuts DskRd DskWr IsRd  IsWrt IsRWr IsDel BfRd  BfWrt SeqSc Object name                                           
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ------------------------------------------------------
[... SOME systables stats were removed for clarity ...]
332e4 0     0     0     26049 0     17641 0     0     0     909e3 0     1     customer_tax_code
164e4 0     0     0     30852 0     163e4 0     0     0     413e4 0     0     customer_tax_code#ix_customer_tax_code_1



Database closed.


real    4m49.367s
user    0m0.390s
sys     0m1.390s

castelo@primary:informix-> cat query_option_1.txt

QUERY: (OPTIMIZATION TIMESTAMP: 03-31-2014 09:51:29)
------
SELECT
        customer_num, customer_plan, customer_seq_id, customer_tax_code
FROM
        customer_tax_code a
WHERE
        a.customer_service = 1 AND
        a.customer_seq_id = (SELECT MAX(b.customer_seq_id) FROM customer_tax_code b WHERE
                             b.customer_num = a.customer_num AND
                             b.customer_plan = a.customer_plan AND
                             b.customer_service = 1
                            )

Estimated Cost: 2418832
Estimated # of Rows Returned: 512750

  1) informix.a: SEQUENTIAL SCAN

        Filters: (informix.a.customer_service = 1 AND informix.a.customer_seq_id = <subquery> ) 

    Subquery:
    ---------
    Estimated Cost: 4
    Estimated # of Rows Returned: 1

      1) informix.b: INDEX PATH

            Filters: informix.b.customer_service = 1 

        (1) Index Name: informix.ix_customer_tax_code_1
            Index Keys: customer_num customer_plan customer_seq_id  (Reverse)  (Aggregate)  (Serial, fragments: ALL)
            Lower Index Filter: (informix.b.customer_num = informix.a.customer_num AND informix.b.customer_plan = informix.a.customer_plan ) 



Query statistics:
-----------------

  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                a

  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t1     798315     512750    2500000    04:38.22   2418832 


Subquery statistics:
--------------------

  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                b

  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t1     806330     1         821121     04:30.70   5       

  type     rows_prod  est_rows  rows_cons  time
  -------------------------------------------------
  group    806330     1         806330     04:31.89

castelo@primary:informix->
Some facts about this execution:
  • It took around 5m
  • It did a sequential scan on the table and used the most complex index for the sub-query (this ones provides easy access to the MAX(customer_num_seq_id) by customer_num/customer_plan
  • It spend most of the time in I/O wait
And then the second query:
castelo@primary:informix-> onmode -ky;oninit
castelo@primary:informix-> /usr/informix/bin/ixprofiling -w -i stores test_query_2.sql

Database selected.

Engine statistics RESETed. Query results:
Query start time: 09:58:23.477004000

SET EXPLAIN FILE TO "query_option_2.txt";
Explain set.


SET EXPLAIN ON;
Explain set.


UNLOAD TO /dev/null
SELECT
        *
FROM (
SELECT
        customer_num, customer_plan, customer_seq_id, customer_tax_code,
        RANK() OVER (PARTITION BY customer_num, customer_plan ORDER BY customer_seq_id DESC) myrank
FROM
        customer_tax_code a
WHERE
        a.customer_service = 1
)
WHERE myrank = 1;
798315 row(s) unloaded.

Query stop time: 09:59:59.676333000

Thread profiles (SID: 6)
LkReq LkWai DLks  TOuts LgRec IsRd  IsWrt IsRWr IsDel BfRd  BfWrt LgUse LgMax SeqSc Srts  DskSr SrtMx Sched CPU Time    Name        
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----------- ------------ 
250e4 0     0     0     0     19698 102e4 0     0     206e3 3997  0     0     3     1     1     18944 108e3 9.918404867 sqlexec     
LkWs         IOWs         nIOW         IdxBR        Name                               
------------ ------------ ------------ ------------ -----------------------------------
0.0          74.245603123 3087         0            sqlexec                            


Session wait statistics:
Thread name                         Condition                           Num. waits   Cum. time    Max wait    
----------------------------------- ----------------------------------- ------------ ------------ ------------
sqlexec                             mutex                               6            76.625269575 19.0        
sqlexec                             sort io                             280          641848.77478 94357.0     
sqlexec                             condition                           4554         2177075.9388 21014.0     
sqlexec                             mt yield 0                          9538         2845631.3908 24543.0     
sqlexec                             mt ready                            108699       2931312.6256 24544.0     
sqlexec                             buffer                              191          5480268.6835 126467.0    
sqlexec                             running                             17658        9915220.0648 47373.0     
sqlexec                             aio                                 3088         74141204.005 451378.0    
sqlexec                             Total time                                       98132638.109             

Partitions profiles (Database: stores)
LkReq LkWai DLks  TOuts DskRd DskWr IsRd  IsWrt IsRWr IsDel BfRd  BfWrt SeqSc Object name                                           
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ------------------------------------------------------
[... SOME systables stats were removed for clarity ...]
250e4 0     0     0     26049 358   10154 0     0     0     179e3 3220  1     customer_tax_code



Database closed.


real    1m38.444s
user    0m0.140s
sys     0m0.750s
castelo@primary:informix->
QUERY: (OPTIMIZATION TIMESTAMP: 03-31-2014 09:58:23)
------
SELECT
        *
FROM (
SELECT
        customer_num, customer_plan, customer_seq_id, customer_tax_code,
        RANK() OVER (PARTITION BY customer_num, customer_plan ORDER BY customer_seq_id DESC) myrank
FROM
        customer_tax_code a
WHERE
        a.customer_service = 1
)
WHERE myrank = 1

Estimated Cost: 42278
Estimated # of Rows Returned: 102550

  1) (Temp Table For Collection Subquery): SEQUENTIAL SCAN

        Filters: (Temp Table For Collection Subquery).myrank = 1 


Query statistics:
-----------------

  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                a
  t2                (Temp Table For Collection Subquery)

  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t1     1025501    1025501   2500000    00:07.19   101043  

  type     rows_sort  est_rows  rows_cons  time
  -------------------------------------------------
  sort     1025501    0         1025501    01:27.73

  type     it_count   time
  ----------------------------
  olap     1025501    01:31.31

  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t2     798315     102550    1025501    00:00.47   42278   

castelo@primary:informix-> 
Some facts about this execution:
  1. The query took around 1m40s
  2. It did the same sequential scan as the one aboive
  3. It didn't require heavy I/O on any index
  4. It wasted little more than 1m on I/O wait
Summary
This table summarizes the differences between both queries:

Query 1 (sub-query)Query 2 (OLAP)
Execution time4m49.367s1m38.444s
Thread ISAM reads831000,00019698,000
Thread ISAM writes0,0001020000,000
Thread buffer reads4930000,000206000,000
Thread buffer writes0,0003997,000
Thread SortMax0,00018944,000
Thread schedules86801,000108000,000
Thread CPU time26,7189,918
Table ISAM reads17641,00010154,000
Table buffer reads909000,000179000,000
Table buffer writes0,0003220,000
Table disk reads26049,00026049,000
Table disk writes0,000358,000
Index ISAM reads1630000,0000,000
Index buffer reads4130000,0000,000
Index disk reads30852,0000,000
I/O waits16543,0003087,000
I/O wait time251,79074,245

As you can see for yourself, the second query has much better values in most common aspects. It does take extra work for sorting and writing the temporary structure. But even so, this seems to have much less impact. There is also another aspect that makes the first option much better in the test data than in the real data. This aspect alone could deserve a dedicated article, but for now I'll just point out that the way the data was created makes the table relatively ordered by customer_num/customer_plan. This is not an irrelevant aspect in the real situation because of these two reasons:
  1. In the real scenario, the index used in the sub-query only contained customer_num, customer_plan, and did not contain customer_seq_id which in my test allows the engine to solve the MAX() with just the index access
  2. When we access the index for the sub-query, the rowids we get are "close". In most cases a single disk access will be able to fetch all of them. In the real situation, the records for the same customer_num/customer_plan would require more disk accesses. Even more, since we're doing a sequential scan, when we try to fetch the row data for the records with the same customer_num/customer_plan, chances are that those pages are already place in memory (retrieved by the sequential scan). If the table was not "ordered" this would not happen and the disk I/O impact would be even bigger.
As a final note, I don't see why we can't use a column alias in the HAVING clause. If there is a good reason I can't figure it out. And I'm not sure if we couldn't optimize the query even more if we were able to state we would only want one record. For this reason I inserted a new request for enhancement (RFE) on the public site. The feature request URL is: http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=46670


Versão Portuguesa:

Introducão
Numa situação real num cliente, fui confrontado com um problema de otimização: Usavam uma query que demorava cerca de 1H a correr, mas notaram que a mesma gerava "duplicados" (não exatamente duplicados, mas registos repetidos em valores que deveriam identificar um objeto).
Isto acontecia porque o modelo de dados foi criado de forma a permitir que cada "objeto" fosse representado mais que uma vez na tabela (assumia diferentes "vidas"). E o objetivo era remover esses "duplicados" do resultado da query. O problema é que quando re-escreveram a query o tempo de execução passou para várias horas (nunca terminou...)
Não posso usar o modelo de dados exato por questões de privacidade, mas vou tentar criar um modelo que seja funcionalmente equivalente, para que possa exemplificar a situação. Vamos assumir esta estrutura de tabela:
CREATE TABLE customer_tax_code
(
    customer_num INTEGER,      -- Identificação do cliente
    customer_plan CHAR(5),     -- Cada cliente está num determinado plano (INDividual, COMPany, MULTI etc.) em cada momento, mas podem mudar
    customer_service SMALLINT, -- Os clientes podem ter vários serviços activos
    customer_seq_id SERIAL,    -- Cada vez que um cliente muda de código de taxação é criado um novo registo com um número de sequência novo
    customer_tax_code SMALLINT -- O código de taxação que define a regra de taxação para o serviço
);
A query original era algo como:
SELECT
    customer_num, customer_plan, customer_seq_id, customer_tax_code
FROM
    customer_tax_code
WHERE
    customer_service = 1;
Repare que não considerei ainda quaisquer índices. Mas esta query é extremamente simples e só teria dois planos de execução possíveis:
  1. Um scan total da tabela se o número de registos com customer_service = 1 for uma percentagem significativa da tabela
  2. Algum índice que contenha o campo customer_service, se os histogramas mostrarem que a percentagem de registos que valida a condição for reduzido (face à dimensão da tabela)
A tabela tinha cerca de 100M de registos. E o resultado continha vários registos para o mesmo par customer_num/customer_plan. Estes seriam considerados duplicados, pois para cada par apenas queriam o "último" registo. Repare que se o cliente mudasse de plano o objetivo da query era trazer todas as "vidas" (diferentes planos), mas para cada plano apenas queriam a última entrada.
Por outras palavras queriam trazer todos os registos onde o customer_seq_id era o MAX(customer_seq_id) para cada par customer_num/customer_plan (com customer_service=1)
Mas como querem obter também outros dados da linha, o uso do MAX(customer_seq_id) e o respetivo GROUP BY customer_num, customer_plan não pode ser usado (seríamos obrigado a introduzir todos os campos sem função de agregação na cláusula GROUP BY, e isso iria alterar o resultado obtido).

A solução que tinham criado era isto, em traços gerais:
SELECT
    customer_num, customer_plan, customer_seq_id, customer_tax_code
FROM
    customer_tax_code a
WHERE
    a.customer_service = 1 AND
    a.customer_seq_id = (SELECT
                             MAX(b.customer_seq_id)
                         FROM
                             customer_tax_code b
                         WHERE
                             b.customer_num = a.customer_num AND
                             b.customer_plan = a.customer_plan AND
                             b.customer_service = 1
                         );
Esta query obtém o resultado esperado... Mas o plano de execução típico incluíria as opções acima (scan completo ou acesso por índice sobre a coluna customer_service), e para cada registo obtido teríamos de fazer um acesso indexado para resolver a sub-query.
Para fornecer mais algum contexto, a percentagem de registos com customer_service = 1 rondava os 41% da tabela (cerca de 41M de linhas).
Um índice semelhante a um destes ajuda bastante a query:
  • (customer_num, customer_plan, customer_num_seq_id)
    Fornece um acesso rápido ao MAX() por customer_num, customer_plan
  • (customer_num, customer_plan, customer_service, customer_num_seq_id)
    Poderia melhorar o anterior pois incluí todos os filtros para a sub-query
  • (customer_num, customer_plan)
    Este seria o índice mais básico para aceder aos rowids das linhas para cada para customer_num/customer_plan
Mas no caso atual, isto faria normalmente um scan completo e depois 41M de acessos a um dos índices. E isto é penoso. Tentei várias variantes como criar uma inline view com (customer_num, customer_plan, MAX(customer_seq_id)... WHERE customer_service = 1 GROUP BY 1,2), e depois fazer o JOIN disto com a tabela. Dependendo da manipulação dos índices, de HINTS para forçar determinadas opções (HASH JOIN) etc. fui capaz de obter um plano de execução que demorava 2H-3H. Mas mesmo assim, era bastante "forçado", demorado e necessitava de hints.

Solução
Nesta altura lembrei-me que introduzimos as chamadas funções OLAP na versão 12.10. E na verdade uma delas dá-nos algo que necessitamos para este tipo de queries (necessidade de encontrar um MAX(), mas necessitamos de trazer mais campos para além dos que usaríamos para o GROUP BY).
Na verdade, este problema pode ser generalizado (e a ideia é que a solução também), dado que isto acontece sempre que o modelo de dados é criado de forma que mantenha histórico e nós só pretendemos o último estado de cada objeto. Por outras palavras, todas as situações onde para além de outros filtros, queremos:
campo_xpto = MAX(campo_xpto) e o grupo para o cálculo do máximo é definido por um conjunto de colunas. Muitas vezes o "campo_xpto" é um date/datetime e o conjunto de colunas define um identificador de objeto.

A função em questão é o RANK(). Sumariamente, calcula uma ordem para um campo, numa "janela" definida por um ou mais campos (os que usaríamos no GROUP BY do cálculo do MAX()).

No nosso caso, podemos obter um "rank" do customer_seq_id, para cada grupo/janela definido pelos campos customer_num/customer_plan.
Por omissão o registo com o campo com valor mais baixo (em termos de ordenação conforme o tipo de dados) no customer_seq_id recebería o rank() = 1. O seguinte receberia o rank 2 e assim por diante. Mas podemos fazer o oposto. Atribuir 1 ao registo com o customer_seq_id mais alto, 2 ao que se encontrar logo abaixo etc.

A query ficaria assim:
SELECT
    customer_num, customer_plan, customer_seq_id, customer_tax_code,
    RANK() OVER (PARTITION BY customer_num, customer_plan ORDER BY customer_seq_id DESC) myrank
FROM
    customer_tax_code a
WHERE
    a.customer_service = 1
Note que isto não eliminaria os "duplicados". Apenas classificaria os registos no conjunto resultante da query. Na verdade temos de impor uma restrição, dizendo que apenas queremos os campos cujo myrank = 1 (estes serão os registos com o customer_seq_id mais alto, dentro do grupo de registos com os mesmos customer_num e customer_plan).

Dado que o RANK() é calculado sobre o conjunto de resultados, não pode aceitar o uso da condição "murank = 1" na cláusula WHERE.
Mas idealmente poderíamos usar a cláusula HAVING, tal como:
... HAVING myrank = 1
Infelizmente isto dispara o erro -217 (column "myrank" not found). Não podemos usar alias de colunas na cláusula HAVING (podemos usá-las nos GROUP BY e ORDER BY).
E também não podemos usar a expressão completa na cláusula HAVING pois isso causa o erro:
-25856  Invalid usage of window aggregate in this context.

You can use OLAP window aggregate functions in the Projection clause 
and in the ORDER BY clause of queries.  They cannot be present in 
the WHERE clause or in the HAVING clause (except within a subquery).
Por isso, o que temos de fazer é "embrulhar" a query acima como uma inline view numa query exterior que contenha a restrição myrank = 1:
SELECT
    *
FROM
    (
        SELECT
            customer_num, customer_plan, customer_seq_id, customer_tax_code,
            RANK() OVER (PARTITION BY customer_num, customer_plan ORDER customer_seq_id DESC) myrank
        FROM
            customer_tax_code a
        WHERE
            a.customer_service = 1
)
WHERE my_rank = 1;
Esta query era executada com um scan completo da tabela e terminava em cerca de 50m. E é simples de escrever e entender, não dependendo de hints, queries complexas, sub-queries etc. Apenas tiramos proveito das novas funções. Este foi o plano de execução mais eficiente que consegui para esta situação.
Apenas tive um problema: Os sistemas de produção do cliente ainda estão em 11.50, a qual não contém estas funções. Serve como exemplo de como os clientes perdem tempo e perdem oportunidades ao manterem-se em versões antigas....

Para lhe dar uma melhor perceção sobre este problema, criei um caso de teste com dados de teste "aleatórios", de forma a provar e demonstrar o princípio explicado acima. Poderá aplicar isto ao seu ambiente, dado que estou a testar num ambiente virtualizado, e estas queries sofrerem muito com o I/O... e é sabido que o I/O não é o ponto forte dos sistemas virtualizados.

Caso de teste
Os dados de teste foram criados com um script GAWK que tenta imitar as condições originais que descrevi acima. O script pode ser consultado no final do artigo. Gerei 2.5M de linhas:
castelo@primary:informix-> echo 2500000 | awk -f generate_data.awk > rank_test_data.unl
castelo@primary:informix->
Depois carreguei os dados numa tabela com a estrutura já mostrada acima:
castelo@primary:informix-> dbaccess -e stores test_ddl.sql

Database selected.

DROP TABLE IF EXISTS customer_tax_code;
Table dropped.


CREATE RAW TABLE customer_tax_code
(
    customer_num INTEGER,      -- Identificação do cliente
    customer_plan CHAR(5),     -- Cada cliente está num determinado plano (INDividual, COMPany, MULTI etc.) em cada momento, mas podem mudar
    customer_service SMALLINT, -- Os clientes podem ter vários serviços activos
    customer_seq_id SERIAL,    -- Cada vez que um cliente muda de código de taxação é criado um novo registo com um número de sequência novo
    customer_tax_code SMALLINT -- O código de taxação que define a regra de taxação para o serviço
) IN dbs1
EXTENT SIZE 10000 NEXT SIZE 10000
LOCK MODE ROW;
Table created.



BEGIN WORK;
Started transaction.


LOCK TABLE customer_tax_code IN EXCLUSIVE MODE;
Table locked.


LOAD FROM rank_test_data.unl INSERT INTO customer_tax_code;
2500000 row(s) loaded.


COMMIT WORK;
Data committed.


ALTER TABLE customer_tax_code TYPE(STANDARD);
Table altered.


CREATE INDEX ix_customer_tax_code_1 ON customer_tax_code(customer_num,customer_plan,customer_seq_id) IN dbs2;
Index created.


CREATE INDEX ix_customer_tax_code_2 ON customer_tax_code(customer_service) IN dbs2;
Index created.



Database closed.

castelo@primary:informix-> 
A query seguinte permite ter uma idea de como os dados foram gerados:
castelo@primary:informix-> dbaccess -e stores distrib.sql 

Database selected.

SELECT
        COUNT(*) num, TRUNC((COUNT(*) / 2500000) * 100, 2) percent
FROM
        customer_tax_code
WHERE
        customer_service = 1;

             num          percent 

         1025501            41.02

1 row(s) retrieved.


SELECT
        COUNT(*) num, TRUNC((COUNT(*) / 2500000) * 100, 2) percent
FROM (
        SELECT 
                DISTINCT customer_num, customer_plan
        FROM
                customer_tax_code
        WHERE
                customer_service = 1
);

             num          percent 

          798315            31.93

1 row(s) retrieved.



Database closed.

castelo@primary:informix-> 
Portanto deveremos esperar um conhjunto de resultados com 798315 linhas. E como podemos ver, os dados da tabela de teste têm 41% de registos com customer_service = 1 (tal como na situação real).
Para mostrar a diferença de comportamento entre ambas as queries usei um script que criei há algum tempo atrás e que documentei neste artigo. Para o usar preparei também um script para cada query:
castelo@primary:informix-> cat test_query_1.sql
SET EXPLAIN FILE TO "query_option_1.txt";
SET EXPLAIN ON;
UNLOAD TO /dev/null
SELECT
        customer_num, customer_plan, customer_seq_id, customer_tax_code
FROM
        customer_tax_code a
WHERE
        a.customer_service = 1 AND
        a.customer_seq_id = (SELECT MAX(b.customer_seq_id) FROM customer_tax_code b WHERE
                             b.customer_num = a.customer_num AND
                             b.customer_plan = a.customer_plan AND
                             b.customer_service = 1
                            );
castelo@primary:informix->
castelo@primary:informix-> cat test_query_2.sql
SET EXPLAIN FILE TO "query_option_2.txt";
SET EXPLAIN ON;
UNLOAD TO /dev/null
SELECT
        *
FROM (
SELECT
        customer_num, customer_plan, customer_seq_id, customer_tax_code,
        RANK() OVER (PARTITION BY customer_num, customer_plan ORDER BY customer_seq_id DESC) myrank
FROM
        customer_tax_code a
WHERE
        a.customer_service = 1
)
WHERE myrank = 1;
castelo@primary:informix->
Repare que activei o EXPLAIN e que estou a fazer o UNLOAD para /dev/null. Isto é apenas um truque para evitar desperdiçar tempo a escrever os dados em ficheiro, e ao fazê-lo estou a minimizar as variáveis que afectarão o tempo de execução. Gostaria também de referir que o problema que estou a tentar explicar traduz-se num problema de I/O. E dado que acabei de carregar os dados, criar os índices e correr as queries acima, no ponto em que nos encontramos a memória do servidor de base de dados conterá uma parte significativa dos dados da tabela. Para evitar que isto influencie os tempos, re-iniciei a instância (onmode -ky;oninit).
Depois executei os referidos scripts:
castelo@primary:informix-> onmode -ky;oninit
castelo@primary:informix-> /usr/informix/bin/ixprofiling -w -i stores test_query_1.sql

Database selected.

Engine statistics RESETed. Query results:
Query start time: 09:51:29.634374000

SET EXPLAIN FILE TO "query_option_1.txt";
Explain set.


SET EXPLAIN ON;
Explain set.


UNLOAD TO /dev/null
SELECT
        customer_num, customer_plan, customer_seq_id, customer_tax_code
FROM
        customer_tax_code a
WHERE
        a.customer_service = 1 AND
        a.customer_seq_id = (SELECT MAX(b.customer_seq_id) FROM customer_tax_code b WHERE
                             b.customer_num = a.customer_num AND
                             b.customer_plan = a.customer_plan AND
                             b.customer_service = 1
                            );
798315 row(s) unloaded.

Query stop time: 09:56:16.976229000

Thread profiles (SID: 5)
LkReq LkWai DLks  TOuts LgRec IsRd  IsWrt IsRWr IsDel BfRd  BfWrt LgUse LgMax SeqSc Srts  DskSr SrtMx Sched CPU Time    Name        
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----------- ------------ 
496e4 0     0     0     0     831e3 0     0     0     493e4 0     0     0     2     0     0     0     86801 26.71790603 sqlexec     
LkWs         IOWs         nIOW         IdxBR        Name                               
------------ ------------ ------------ ------------ -----------------------------------
0.0          251.79098566 16543        0            sqlexec                            


Session wait statistics:
Thread name                         Condition                           Num. waits   Cum. time    Max wait    
----------------------------------- ----------------------------------- ------------ ------------ ------------
sqlexec                             mutex                               1            23.611186280 23.0        
sqlexec                             mt yield                            1            6508.7673752 6508.0      
sqlexec                             buffer                              17           462525.80710 61764.0     
sqlexec                             mt yield 0                          3929         611561.28945 14680.0     
sqlexec                             mt ready                            86801        799028.07279 14682.0     
sqlexec                             mt yield n                          60           1104496.3218 58390.0     
sqlexec                             condition                           2935         2236864.5528 16698.0     
sqlexec                             running                             23461        26712075.663 53739.0     
sqlexec                             aio                                 16517        251627122.20 3787185.0   
sqlexec                             Total time                                       283560206.29             

Partitions profiles (Database: stores)
LkReq LkWai DLks  TOuts DskRd DskWr IsRd  IsWrt IsRWr IsDel BfRd  BfWrt SeqSc Object name                                           
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ------------------------------------------------------
[... SOME systables stats were removed for clarity ...]
332e4 0     0     0     26049 0     17641 0     0     0     909e3 0     1     customer_tax_code
164e4 0     0     0     30852 0     163e4 0     0     0     413e4 0     0     customer_tax_code#ix_customer_tax_code_1



Database closed.


real    4m49.367s
user    0m0.390s
sys     0m1.390s

castelo@primary:informix-> cat query_option_1.txt

QUERY: (OPTIMIZATION TIMESTAMP: 03-31-2014 09:51:29)
------
SELECT
        customer_num, customer_plan, customer_seq_id, customer_tax_code
FROM
        customer_tax_code a
WHERE
        a.customer_service = 1 AND
        a.customer_seq_id = (SELECT MAX(b.customer_seq_id) FROM customer_tax_code b WHERE
                             b.customer_num = a.customer_num AND
                             b.customer_plan = a.customer_plan AND
                             b.customer_service = 1
                            )

Estimated Cost: 2418832
Estimated # of Rows Returned: 512750

  1) informix.a: SEQUENTIAL SCAN

        Filters: (informix.a.customer_service = 1 AND informix.a.customer_seq_id = <subquery> ) 

    Subquery:
    ---------
    Estimated Cost: 4
    Estimated # of Rows Returned: 1

      1) informix.b: INDEX PATH

            Filters: informix.b.customer_service = 1 

        (1) Index Name: informix.ix_customer_tax_code_1
            Index Keys: customer_num customer_plan customer_seq_id  (Reverse)  (Aggregate)  (Serial, fragments: ALL)
            Lower Index Filter: (informix.b.customer_num = informix.a.customer_num AND informix.b.customer_plan = informix.a.customer_plan ) 



Query statistics:
-----------------

  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                a

  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t1     798315     512750    2500000    04:38.22   2418832 


Subquery statistics:
--------------------

  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                b

  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t1     806330     1         821121     04:30.70   5       

  type     rows_prod  est_rows  rows_cons  time
  -------------------------------------------------
  group    806330     1         806330     04:31.89

castelo@primary:informix->
Alguns factos sobre esta execução:
  • Demorou cerca de 5m
  • Fez um scan completo à tabela e usou o índice mais complexo para resolver a sub-query (este fornece acesso fáacil ao MAX(customer_seq_id) por customer_num / customer_plan)
  • Passou a maioria do tempo à espera de I/O
E agora a segunda query:
castelo@primary:informix-> onmode -ky;oninit
castelo@primary:informix-> /usr/informix/bin/ixprofiling -w -i stores test_query_2.sql

Database selected.

Engine statistics RESETed. Query results:
Query start time: 09:58:23.477004000

SET EXPLAIN FILE TO "query_option_2.txt";
Explain set.


SET EXPLAIN ON;
Explain set.


UNLOAD TO /dev/null
SELECT
        *
FROM (
SELECT
        customer_num, customer_plan, customer_seq_id, customer_tax_code,
        RANK() OVER (PARTITION BY customer_num, customer_plan ORDER BY customer_seq_id DESC) myrank
FROM
        customer_tax_code a
WHERE
        a.customer_service = 1
)
WHERE myrank = 1;
798315 row(s) unloaded.

Query stop time: 09:59:59.676333000

Thread profiles (SID: 6)
LkReq LkWai DLks  TOuts LgRec IsRd  IsWrt IsRWr IsDel BfRd  BfWrt LgUse LgMax SeqSc Srts  DskSr SrtMx Sched CPU Time    Name        
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----------- ------------ 
250e4 0     0     0     0     19698 102e4 0     0     206e3 3997  0     0     3     1     1     18944 108e3 9.918404867 sqlexec     
LkWs         IOWs         nIOW         IdxBR        Name                               
------------ ------------ ------------ ------------ -----------------------------------
0.0          74.245603123 3087         0            sqlexec                            


Session wait statistics:
Thread name                         Condition                           Num. waits   Cum. time    Max wait    
----------------------------------- ----------------------------------- ------------ ------------ ------------
sqlexec                             mutex                               6            76.625269575 19.0        
sqlexec                             sort io                             280          641848.77478 94357.0     
sqlexec                             condition                           4554         2177075.9388 21014.0     
sqlexec                             mt yield 0                          9538         2845631.3908 24543.0     
sqlexec                             mt ready                            108699       2931312.6256 24544.0     
sqlexec                             buffer                              191          5480268.6835 126467.0    
sqlexec                             running                             17658        9915220.0648 47373.0     
sqlexec                             aio                                 3088         74141204.005 451378.0    
sqlexec                             Total time                                       98132638.109             

Partitions profiles (Database: stores)
LkReq LkWai DLks  TOuts DskRd DskWr IsRd  IsWrt IsRWr IsDel BfRd  BfWrt SeqSc Object name                                           
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ------------------------------------------------------
[... SOME systables stats were removed for clarity ...]
250e4 0     0     0     26049 358   10154 0     0     0     179e3 3220  1     customer_tax_code



Database closed.


real    1m38.444s
user    0m0.140s
sys     0m0.750s
castelo@primary:informix->
QUERY: (OPTIMIZATION TIMESTAMP: 03-31-2014 09:58:23)
------
SELECT
        *
FROM (
SELECT
        customer_num, customer_plan, customer_seq_id, customer_tax_code,
        RANK() OVER (PARTITION BY customer_num, customer_plan ORDER BY customer_seq_id DESC) myrank
FROM
        customer_tax_code a
WHERE
        a.customer_service = 1
)
WHERE myrank = 1

Estimated Cost: 42278
Estimated # of Rows Returned: 102550

  1) (Temp Table For Collection Subquery): SEQUENTIAL SCAN

        Filters: (Temp Table For Collection Subquery).myrank = 1 


Query statistics:
-----------------

  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                a
  t2                (Temp Table For Collection Subquery)

  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t1     1025501    1025501   2500000    00:07.19   101043  

  type     rows_sort  est_rows  rows_cons  time
  -------------------------------------------------
  sort     1025501    0         1025501    01:27.73

  type     it_count   time
  ----------------------------
  olap     1025501    01:31.31

  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t2     798315     102550    1025501    00:00.47   42278   

castelo@primary:informix-> 
Factos sobre esta execução:
  1. A query demorou cerca de 1m40s
  2. Fez o mesmo scan completo que a anterior
  3. Não necessitou de qualquer I/O pesado sobre nenhum dos índices
  4. Desperdicou pouco mais que 1m em esperas pelo I/O (versus cerca de 4m na anterior)
Conclusão
A tabela seguinte contém o sumário das diferenças entre ambas as queries:


Query 1 (sub-query)Query 2 (OLAP)
Tempo de exeução4m49.367s1m38.444s
Thread ISAM reads831000,00019698,000
Thread ISAM writes0,0001020000,000
Thread buffer reads4930000,000206000,000
Thread buffer writes0,0003997,000
Thread SortMax0,00018944,000
Thread schedules86801,000108000,000
Thread CPU time26,7189,918
Table ISAM reads17641,00010154,000
Table buffer reads909000,000179000,000
Table buffer writes0,0003220,000
Table disk reads26049,00026049,000
Table disk writes0,000358,000
Index ISAM reads1630000,0000,000
Index buffer reads4130000,0000,000
Index disk reads30852,0000,000
I/O waits16543,0003087,000
I/O wait time251,79074,245

Como pode verificar por si próprio, a segunda query tem valores muito melhores no que é comum. Necessita de algum trabalho extra para ordenar e escrever a estrutura temporária. Mas mesmo assim, parece ter um impacto muito menor. Há ainda um outro aspecto que torna a primeira opção muito melhor com estes dados de teste que na situação real. Este aspecto por si só mereceria um artigo, mas de momento vou apenas referir que a forma como os dados foram criados, faz com que a tabela fique relativamente ordenada por customer_num/customer_plan. Este aspecto não é de todo irrelevante na situação real por dois motivos:
  1. No caso real o índice que existia tinha apenas customer_num/customer_plan e não tinha como terceiro campo o customer_seq_id que neste caso permite obter o MAX() apenas com o acesso ao índice
  2. Quando acedemos ao índice para resolver a sub-query, os rowids que obtemos tendem a estar próximos. Em muitos casos um único acesso a disco chegará para obter todas as linhas que validem a condição. Na situação real, obter os registos para o mesmo customer_num/customer_plan necessitaria de mais acessos a disco, e tipicamente mais aleatórios ou espalhados que neste caso. Ainda por cima, como estamos a fazer um scan completo para a query de fora, quando tentamos aceder por rowid aos registos com o mesmo customer_num/customer_plan, a probabilidade de esses dados já terem sido lidos pelo scan é grande. Se a tabela não estivesse "ordenada", isto não aconteceria e o impacto no I/O seria sensivelmente maior.
Como nota final, não consigo entender porque não podemos usar alias de colunas na cláusula HAVING. Se existe alguma razão que o justifique eu não a conheço. E não tenho a certeza se não seria ainda maior optimização se conseguíssemos sinalizar que apenas queremos um registo por grupo. Por esta razão inseri um novo pedido de melhoria (RFE) no site público. O URL para este pedido é: http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=46670





AWK script used to generate the test data:

castelo@primary:informix-> cat generate_data.awk

function myrand(i)
{
        return int(rand() * i) + 1;
}
BEGIN {

srand();

my_previous_customer=5000000;
my_previous_plan="MULTI";
my_previous_service=1;

list_tax_size=6;
list_tax_code[1]=15;
list_tax_code[2]=25;
list_tax_code[3]=35;
list_tax_code[4]=45;
list_tax_code[5]=55;
list_tax_code[6]=65;

list_plans_size=4;
list_plans[1]="MULTI";
list_plans[2]="SOLO";
list_plans[3]="GROUP";
list_plans[4]="KIDS";

client_with_service_1_percent=0.41;
same_client_plan_percent=0.5;
customer_num_repetition_percent=0.33
}


{
        for(a=1;a<=$1;a++)
        {
                r=myrand(100);

                if ( r <= customer_num_repetition_percent * 100 )
                {
                        # % will repeat previouc customer_num
                        client_num = my_previous_customer;
                        client_service = my_previous_service;
                        r=myrand(100);
                        if ( r <= same_client_plan_percent * 100 )
                        {
                                client_plan = my_previous_plan;
                                r=myrand(list_tax_size);
                                client_tax_code=list_tax_code[r]
                        }
                        else
                        {
                                r=myrand(list_plans_size);
                                client_plan=list_plans[r];
                                r=myrand(list_tax_size);
                                client_tax_code=list_tax_code[r]
                        }
                }
                else
                {
                        # random customer_num
                        client_num = myrand(10000000);
                        r=myrand(list_plans_size);
                        client_plan=list_plans[r];
                        r=myrand(100);
                        if ( r <= client_with_service_1_percent * 100 )
                                client_service=1;
                        else
                                client_service=r;
                        r=myrand(list_tax_size);
                        client_tax_code=list_tax_code[r]
                }

                my_previous_customer=client_num;
                my_previous_plan=client_plan;
                my_previous_service=client_service;
                printf("%s|%s|%s|%s|%s\n",client_num,client_plan,client_service,0,client_tax_code);
        }
}
castelo@primary:informix->

New site for French speaking users

This article is written only in English (original version here)

Just a quick note to echo the creation of another Informix related site. This time is for all French speaking users. The site URL is http://informix.fr and the author is a well known member of the Informix community, Jean Georges Perrin who among many other things worked for Four J's (makers or Dynamic 4GL and Genero) and have been working in the IUUG's board of directors.

The site contains a forum (acutally two, one for technical questions and another for other discussions) and as far as my very limited French allows me to understand, it will count on some very experienced Informix people from France, like Eric Vercelletto, Laurent Revel and Olivier Bourdin (L3 Informix support).

If you speak/read French keep an eye on it, as I'm sure the people involved are able to guarantee high standards in the discussions and for sure interesting topics.
For better understanding the importance this can have, I remind you that there are many millions of French speaking people in places like France (who would say?!), Belgium, Switzerland, Monaco, Africa, Southeast Asia, some Pacific Islands and Canada. And of course there are many more places with people who can understand and express themselves in French (not my case). So the potential reach of this is huge!
France is also one of the places in Europe with L2 and L3 Informix support units. So there is a lot of Informix knowledge and talent there.

Wednesday, March 26, 2014

Session limit locks / limite de locks por sessão

This article is written in English and Portuguese (original version here)
Este artigo está escrito em Inglês e Português (versão original aqui)


English version:

In a very recent article I was complaining about the fact that we have some non documented features, and that from time to time those "leak" into the community. It just happened again, and this time it was the exact example I mentioned in that article. As I mentioned before, this one was already mentioned in a 2011 presentation in IOD. Now it happened in the IIUG mailing list (more precisely in the mailing list that acts as a gateway to the newsgroup comp.databases.informix), but we can also find it in a similar presentation made in IIUG 2011 conference which is available to IIUG members in their site.

I'm talking about an ONCONFIG parameter called SESSION_LIMIT_LOCKS. And yes, the name is self explanatory... It defines the maximum number of locks a session can use. Using it can be the best way to avoid a misbehaved session (or user error) to have impact on other sessions or the whole system. Since version 10 I believe (or maybe 9.4) we are able to extend the lock table. That would be a great idea but in fact it never provided the desired result. The problem was not that it doesn't work, but usually what happens is that a user does a mistake like loading millions of records into a table without locking the table, or they forget some condition in a WHERE clause of an UPDATE or DELETE instruction. So, it means that it usually won't stop after a few hundred or thousand more locks. It takes several lock table extensions, and this may consume a lot of memory. So usually the end result is one of these:

  1. The user session ends up in a long transaction (very large - exceeding LTXHWM) with a very slow rollback
  2. The system consumes a lot of memory with the abnormal lock table expansion, and the engine may end up hitting the SHMTOTAL memory limit, or overloading the machine with memory usage and consequently with swapping
Either case, it's not good.
I know about this functionality since 2011 (11.70 but I'm not sure about the fixpack) but I was under the impression that it was not fully functional. I did some tests (showed below) on 12.10.xC3 and I couldn't find any issue with it. But please consider that if it's undocumented, you won't be able to complain if it fails... In fact, I present here the tests for version 12.10.xC3. Previous versions may have different (wrong) behavior. Use it at your own risk. You will not get support!

So, let's try it:
  • I have an instance with 20000 LOCKS:
    
    castelo@primary:informix-> onstat -c | grep "^LOCKS "
    LOCKS 20000
    castelo@primary:informix-> 
    
    
  • I create a very simple test case that will consume 600 locks:
    
    castelo@primary:informix-> cat test_locks.sql
    DROP TABLE IF EXISTS test_locks;
    
    CREATE TABLE test_locks
    (
            col1 INTEGER
    ) LOCK MODE ROW;
    
    INSERT INTO test_locks
    SELECT LEVEL FROM sysmaster:sysdual CONNECT BY lEVEL < 601;
    castelo@primary:informix->
    
    
To start,  let's see how the engine is setup by default:

castelo@primary:informix-> echo "SELECT * FROM syscfgtab WHERE cf_name = 'SESSION_LIMIT_LOCKS'" | dbaccess sysmaster

Database selected.




cf_id         88
cf_name       SESSION_LIMIT_LOCKS
cf_flags      36928
cf_original   
cf_effective  2147483647
cf_default    2147483647

1 row(s) retrieved.



Database closed.

castelo@primary:informix->
 

So apparently by default it comes with (2^32) - 1 locks per session (or unlimited).
From several sources (IUG mailing list and IIUG 2011 conference presentation) we can assume this can be setup as an ONCONFIG parameter and a session variable. So let's start by trying to change the $ONCONFIG parameter:

castelo@primary:informix-> onmode -wm SESSION_LIMIT_LOCKS=100
SESSION_LIMIT_LOCKS is already set to 2147483647.
castelo@primary:informix-> 


Ops.... strange message... I tried other values and found the minimum value accepted seems to be 500:


castelo@primary:informix-> onmode -wm SESSION_LIMIT_LOCKS=500
Value of SESSION_LIMIT_LOCKS has been changed to 500.
castelo@primary:informix->

Let's verify:

castelo@primary:informix-> echo "SELECT * FROM syscfgtab WHERE cf_name = 'SESSION_LIMIT_LOCKS'" | dbaccess sysmaster

Database selected.




cf_id         88
cf_name       SESSION_LIMIT_LOCKS
cf_flags      36928
cf_original   
cf_effective  500
cf_default    2147483647

1 row(s) retrieved.



Database closed.

castelo@primary:informix->

Now we can try the test case and see what happens:

castelo@primary:informix-> dbaccess stores test_locks.sql

Database selected.


Table dropped.


Table created.


  271: Could not insert new row into the table.

  134: ISAM error: no more locks
Error in line 9
Near character position 56

Database closed.

castelo@primary:informix->

Great! If we limit to 500 locks, we cannot consume 600. That's a dream come true!
Furthermore, we can see it in online.log:

castelo@primary:informix-> onstat -m

IBM Informix Dynamic Server Version 12.10.FC3 -- On-Line -- Up 00:11:59 -- 287724 Kbytes

Message Log File: /usr/informix/logs/castelo.log

[...]

14:16:29  Maximum server connections 1 
14:16:29  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 7, Llog used 2

14:16:34  Value of SESSION_LIMIT_LOCKS has been changed to 500.
14:16:39  Session SID=42 User UID=1002 NAME=informix PID=27743 has exceeded the session limit of 500 locks.

castelo@primary:informix->

Great! What else could we ask for?
Continuing with the tests... In the presentation it's also suggested that we can SET ENVIRONMENT... So:

castelo@primary:informix-> dbaccess stores <<EOF
> SET ENVIRONMENT SESSION_LIMIT_LOCKS "1000";
> EOF

Database selected.


19840: Invalid session environment variable.
Error in line 1
Near character position 41


Database closed.

castelo@primary:informix->

Ouch... it doesn't recognize the variable name... Can it be one of the reasons why it's not documented? Could be... but if we think about it, the latest session environment variables all start with IFX_ prefix and then have the $ONCONFIG parameter. So I tried with:

castelo@primary:informix-> dbaccess stores <<EOF
> SET ENVIRONMENT IFX_SESSION_LIMIT_LOCKS "1000";
> EOF

Database selected.


Environment set.



Database closed.

castelo@primary:informix->

Good! So if I add this to the test case:

castelo@primary:informix-> cat test_locks_1000.sql
DROP TABLE IF EXISTS test_locks;

CREATE TABLE test_locks
(
        col1 INTEGER
) LOCK MODE ROW;

SET ENVIRONMENT IFX_SESSION_LIMIT_LOCKS "1000";
INSERT INTO test_locks
SELECT LEVEL FROM sysmaster:sysdual CONNECT BY lEVEL < 601;
castelo@primary:informix-> dbaccess stores test_locks_1000.sql

Database selected.


Table dropped.


Table created.


Environment set.


600 row(s) inserted.


Database closed.

castelo@primary:informix->


So, this does not means it works. But the above tests had the expected result. This is the best example of the situation I described in the previous article. Resources were used to implement this and it's not currently officially available to customers, although it was previously made public at least in three situations. Hopefully this will be documented soon. I feel this is one of the most wanted features in the customer's environment. Again, this would be a nice topic to be raised at IIUG conference in Miami


Versão Portuguesa:

Num artigo muito recente queixava-me do facto de termos funcionalidades não documentadas, e que ocasionalmente informação sobre essas funcionalidades "transparecia" para a comunidade. Ora isso acabou de acontecer, e desta feita exatamente com um dos exemplos que referia no artigo. Como escrevi na altura, isto já tinha sido apresentado numa conferência IOD de 2011. Desta vez foi referido na lista de correio do IIUG (mais precisamente na lista que serve de gateway com o newsgroup comp.databases.informix), mas também é possível encontrar uma referência ao mesmo tema numa apresentação da conferência do IIUG de 2011, estando essa apresentação disponível no site do IIUG na sua área reservada a membros.

Estou a falar de um parâmetro do ONCONFIG chamado SESSION_LIMIT_LOCKS. E sim, o seu nome diz tudo... define um máximo de locks que cada sessão pode usar. Usá-lo poderá ser a melhor forma de evitar que uma sessão "mal comportada" (ou um erro de utilização) tenha impacto nas outras sessões ou mesmo no sistema em geral.
Desde a versão 10 segundo creio (ou será 9.40?) que podemos expandir a tabela de locks. Isso seria uma excelente ideia, mas na verdade julgo que nunca teve o resultado planeado. O problema não está no facto de isso não funcionar, mas antes porque habitualmente o que acontece é alguém cometer um erro como carregar milhões de registos numa tabela sem a bloquear, ou esquecer uma condição numa cláusula WHERE de um UPDATE ou DELETE. Portanto, na prática o erro deixa de abortar por falta de locks (o Informix vai expandindo a tabela) ao fim de umas centenas ou milhares de registos. O resultado passa então a ser um destes:
  1. A sessão do utilizador acaba por gerar uma transação longa (muito grande - excedendo o LTXHWM) e força um rollback geralmente demorado
  2. O sistema consome muita memória devido às expansões anormais da tabela de locks, e isso pode levar o motor a bater no limite definido pelo SHMTOTAL, ou acaba por sobrecarregar a memória da máquina e eventualmente força o sistema a entrar em swapping
Em qualquer dos casos, o resultado não é bom!
Eu tenho conhecimento desta funcionalidade sensivelmente desde 2011, salvo erro num fixpack da 11.7, mas tinha a sensação que não estava funcional. No entanto fiz alguns testes (ver abaixo) na versão 12.10.xC3 e não consegui encontrar qualquer problema. Mas tenha em conta que algo não documentado é algo sobre o qual não se poderá queixar... Na verdade, apresentarei testes com a versão 12.10.xC3. Outras versões poderão ter comportamentos diferentes (errados). Use por sua conta e risco. Não terá suporte!

Vamos tentar então:
  • Tenho uma instância com 20000 LOCKS:
    
    castelo@primary:informix-> onstat -c | grep "^LOCKS "
    LOCKS 20000
    castelo@primary:informix-> 
    
    
  • Criei um caso de teste muito simples que ao ser executado vai consumir cerca de 600 locks:
    
    castelo@primary:informix-> cat test_locks.sql
    DROP TABLE IF EXISTS test_locks;
    
    CREATE TABLE test_locks
    (
            col1 INTEGER
    ) LOCK MODE ROW;
    
    INSERT INTO test_locks
    SELECT LEVEL FROM sysmaster:sysdual CONNECT BY lEVEL < 601;
    castelo@primary:informix->
    
    
Vamos começar por ver qual é a configuração do motor por omissão:

castelo@primary:informix-> echo "SELECT * FROM syscfgtab WHERE cf_name = 'SESSION_LIMIT_LOCKS'" | dbaccess sysmaster

Database selected.




cf_id         88
cf_name       SESSION_LIMIT_LOCKS
cf_flags      36928
cf_original   
cf_effective  2147483647
cf_default    2147483647

1 row(s) retrieved.



Database closed.

castelo@primary:informix->
 

Aparentemente, a pré-configuração são (2^32) - 1 locks por sessão (ou ilimitado).
Através de várias fontes (lista de correio do IIUG e apresentação feita na conferência do IIUG de 2011), podemos assumir que a´configuração pode ser feita por parâmetro do ONCONFIG e variável de sessão. Vamos então começar por mudar o parâmetro do ONCONFIG:

castelo@primary:informix-> onmode -wm SESSION_LIMIT_LOCKS=100
SESSION_LIMIT_LOCKS is already set to 2147483647.
castelo@primary:informix-> 

Ops.... mensagem estranha... Mas eu tentei outros valores e aparentemente o mínimo que podemos definir são 500 locks:

castelo@primary:informix-> onmode -wm SESSION_LIMIT_LOCKS=500
Value of SESSION_LIMIT_LOCKS has been changed to 500.
castelo@primary:informix->

Vamos verificar:

castelo@primary:informix-> echo "SELECT * FROM syscfgtab WHERE cf_name = 'SESSION_LIMIT_LOCKS'" | dbaccess sysmaster

Database selected.




cf_id         88
cf_name       SESSION_LIMIT_LOCKS
cf_flags      36928
cf_original   
cf_effective  500
cf_default    2147483647

1 row(s) retrieved.



Database closed.

castelo@primary:informix->

Parece bem. Agora podemos tentar o caso de teste que deveria exceder os locks permitidos e ver o que acontece:

castelo@primary:informix-> dbaccess stores test_locks.sql

Database selected.


Table dropped.


Table created.


  271: Could not insert new row into the table.

  134: ISAM error: no more locks
Error in line 9
Near character position 56

Database closed.

castelo@primary:informix->

Excelente! Se definimos 500, não podemos consumir 600. Parece um sonho tornado realidade!
Mas mais ainda, podemos ver isto no online.log:

castelo@primary:informix-> onstat -m

IBM Informix Dynamic Server Version 12.10.FC3 -- On-Line -- Up 00:11:59 -- 287724 Kbytes

Message Log File: /usr/informix/logs/castelo.log

[...]

14:16:29  Maximum server connections 1 
14:16:29  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 7, Llog used 2

14:16:34  Value of SESSION_LIMIT_LOCKS has been changed to 500.
14:16:39  Session SID=42 User UID=1002 NAME=informix PID=27743 has exceeded the session limit of 500 locks.

castelo@primary:informix->

Ótimo! O que poderíamos pedir mais?!
Continuando com os testes... Na referida apresentação é sugerido que podemos usar a instrução SET ENVIRONMENT... Portanto:

castelo@primary:informix-> dbaccess stores <<EOF
> SET ENVIRONMENT SESSION_LIMIT_LOCKS "1000";
> EOF

Database selected.


19840: Invalid session environment variable.
Error in line 1
Near character position 41


Database closed.

castelo@primary:informix->

Ouch... Não reconhece o nome da variável... Poderá ser uma das razões porque ainda não está documentado? Talvez... mas se pensarmos um pouco, as últimas variáveis de sessão que têm sido introduzidas, todas começam com o prefixo "IFX_" e depois têm o nome do parâmetro equivalente no ONCONFIG. Assim sendo, tentei isto:

castelo@primary:informix-> dbaccess stores <<EOF
> SET ENVIRONMENT IFX_SESSION_LIMIT_LOCKS "1000";
> EOF

Database selected.


Environment set.

Database closed.

castelo@primary:informix->


Boa! Depois adicionei isto ao caso de teste:

castelo@primary:informix-> cat test_locks_1000.sql
DROP TABLE IF EXISTS test_locks;

CREATE TABLE test_locks
(
        col1 INTEGER
) LOCK MODE ROW;

SET ENVIRONMENT IFX_SESSION_LIMIT_LOCKS "1000";
INSERT INTO test_locks
SELECT LEVEL FROM sysmaster:sysdual CONNECT BY lEVEL < 601;
castelo@primary:informix-> dbaccess stores test_locks_1000.sql

Database selected.


Table dropped.


Table created.


Environment set.


600 row(s) inserted.


Database closed.

castelo@primary:informix->


Bom, nada disto garante que funcione. Mas os testes acima tiveram o resultado esperado. Isto é o melhor exemplo da situação que descrevi no artigo já referido. Foram consumidos recursos para implementar isto, mas não está oficialmente disponível para os clientes, embora já tenho sido referido publicamente três vezes antes. Esperemos que isto seja documentado em breve. No meu entender esta é uma das funcionalidades mais desejadas pelos clientes. Refiro novamente que este seria um bom tópico de discussão na conferência de  utilizadores que se avizinha em Miami

Monday, March 24, 2014

Where is Informix? / Onde anda o Informix?

This article is written in English and Portuguese (original version here)
Este artigo está escrito em Inglês e Português (versão original aqui)


English version:

This is a very simple post just to echo an article from a friend and former colleague, Eric Vercelletto. I can say a couple of things about Eric. To start, he has a long history with Informix, has a lot of experience in IT in general, I learned quite a lot with him, he's fun, he was the original author of a script that I still maintain (to select among several Informix environments), although the site where it should be available is always down due to lack of time, and finally I can never write his last name without a typo! :)

All this to say it's a pleasure to see him active in the community. He recently created the website http://www.informix-swat.com to join Informix specialist and companies looking for them, has just recently introduced an RFE (Request For Enhancement) for an interesting subject (you got my comment on that one) and last but not least wrote a very interesting paper about the topic: Where is Informix?
In this document Eric talks about history and I do agree with most of it. I do have some remarks though:

  • The version launch dates are correct and show something that is impressive for a product that our competitors pretend that is dead: Since 2001 (13 years ago), we've released a major release with at most around two years interval. And during the life cycle of a version we keep a steady rate of fixpacks bringing updates, fixes and since a few years ago continuous innovation and new features.
    And as I wrote recently in a discussion on the IIUG mailing list, we're currently providing an N-2 support policy, meaning we support the current version (12.10 or "N"), the previous one (11.70 or "N-1") and the one before that (11.50 or "N-2"). I don't like the idea, but I'm sure many customers do
  • Eric says 11.50 was "small enhancements and adjustments". I wouldn't say so... It contained:
    • UPDATES on secondaries
    • SSL
    • Optimistic locking through the hidden version columns (introduced to facilitate updates on secondaries)
    • Dynamic SQL in SPL procedures
    • Data compression (in later fixpacks)
  • Eric says IBM had decided to incorporate Informix features in DB2. I think only a bunch of people really know what were the initial plans, the present plans, and the future plans. And as with all long term planning, it changes. The facts show that yes, there has been some technology interchange and I wouldn't expect anything different:
    • DB2 inherited the basic HDR concept (called HADR on DB2)
    • Informix inherited some UNICODE support
    • Informix got DRDA
    • Informix got compression (appeared first on DB2)
    • Informix and DB2 inherited in-memory technology from the "blink project" (implemented differently and first in Informix)
    • I believe Informix inherited encryption from DB2 (the same functions exist across DB2 product lines/platforms)
    • JSON exists on both (not necessarily with the same functionality)
  •  Eric says "It was also rumored that at this time some IBM sales persons would not hesitate to sell the ‘Red DBMS’ to Informix customers"
    Well... The magic of a rumor is that it can be rebated. But let's analyze this: IBM is a giant with several areas (software, hardware, business services, technical services, financing etc.). These areas try to maximize synergies to supply end to end solutions. But they also act "solo" on the market. The majority of the Unix/Linux market belongs to Oracle. Are you surprised that my hardware colleagues want to sell Power systems (AIX) to those customers? I'm not! Business services try to win deals in Oracle or MS SQL Server shops. Are you surprised they won't refuse to work with those products or even include them in their solutions if the customer has a preference?! I'm not.
    A completely different thing would be to imagine that a software sales person would "sell" Oracle. For start, it's impossible. And more important: A sales person follows the money. And they would get commissions on those impossible sales :)
But again, the paper is very interesting to read. And shows a clear image: The investment in the product is there. The fact that it's less known than it should, the fact that customers are loyal etc.
Well done!


Versão Portuguesa:

Este é um artigo muito simples, apenas para fazer eco de um artigo de um amigo e antigo colega, Eric Vercelletto. Para começar o Eric tem um longo historial com Informix, uma grande experiência em TI, aprendi bastante com ele, foi o autor original de um script que ainda mantenho (para selecionar entre ambientes Informix) apesar de o site onde deveria estar passar mais tempo em baixo que ligado, e finalmente nunca consigo escrever o seu apelido sem me enganar :)

Tudo isto para dizer que é um prazer vê-lo ativo na comunidade. Ainda recentemente criou um site (http://www.informix-swat.com) para juntar os especialistas Informix e empresas que andem à sua procura. registou um RFE (Request For Enhancement) sobre um tema interessante, e por último publicou um artigo sobre o tema: Onde está o Informix? (Where is Informix?)
No mesmo, o Eric fala sobre a história do Informix e concordo com a maioria do que escreve. Mas tenho alguns comentários:
  • As datas de lançamento das versões estão corretas e mostram algo que é impressionante sobre um produto que a concorrência faz de conta que morreu: Desde 2001 (há 13 anos atrás) temos lançado uma versão major com no máximo cerca de dois anos de intervalo. E durante o ciclo de vida de cada versão mantemos um ritmo previsível de fixpacks que trazem updates, correções  e de há uns anos para cá inovação continuada e novas funcionalidades.
    E como escrevi recentemente numa discussão na lista de correio do IIUG, atualmente está em vigor uma política de suporte "N-2" para o Informix, o que significa que suportamos a versão mais atual (12.10 ou "N"), a anterior (11.70 ou "N-1"), e a que precedeu esta (11.50 ou "N-2"). Pessoalmente não gosto da ideia, mas tenho a certeza que muitos clientes gostam
  • O Eric diz que a 11.50 foi "small enhancements and adjustments" (pequenas melhorias e ajustes). Não diria tanto.... Continha:
    • UPDATES nos secundários
    • SSL
    • Optimistic locking pelo uso de colunas de versão escondidas (introduzido para facilitar os updates nos secundários)
    • SQL dinâmico nos procedimentos SPL
    • Compressão de dados (em fixpacks posteriores)
  • O Eric diz que a IBM decidiu incorporar funcionalidades do Informix no DB2. Penso que apenas um punhado de pessoas sabiam realmente quais eram os planos iniciais, quais são os atuais ou quais serão os futuros. É que como qualquer planeamento de médio/longo prazo, as situações mudam. Os factos mostra efetivamente que houve transferência de tecnologia e não seria de esperar algo diferente:
    • O DB2 herdou a base do HDR (chamado HADR no DB2)
    • O Informix herdou o suporte a UNICODE
    • O Informix recebeu o  DRDA
    • O Informix recebeu a compressão (apareceu primeiro no DB2)
    • O Informix e o  DB2 herdaram a tecnologia de base de dados em memória do "projecto blink" (implementado primeiro no Informix e agora de forma diferente no DB2
    • Julgo que o Informix herdou a encriptação (colunas) do DB2 (as funções existem em várias plataformas do DB2)
    • O JSON existe em ambos (não necessariamente com as mesmas funcionalidades)
  • O Eric diz "It was also rumored that at this time some IBM sales persons would not hesitate to sell the ‘Red DBMS’ to Informix customers" ("existe também o rumor que os vendedores da IBM não hesitavam em vender a RDBMS vermelha a clientes Informix")
    Bom... a magia dos rumores é que não vale a pena rebatê-los. Mas vamos analisar isto: A IBM é um gigante com muitas áreas (software, hardware, serviços de negócio, serviços técnicos, financiamento etc.). Estas áreas tentam maximizar sinergias para fornecerem soluções completas. Mas também atuam "a solo" no mercado. A maioria do mercado Unix/Linux pertence à Oracle. Surpreende-o que os meus colegas de hardware queiram vender sistemas Power a esses clientes? A mim não! Os serviços de negócio ganham negócios em clientes Oracle e MS SQL Server. Surpreende-o que não se recusem a trabalhar com esses produtos ou mesmo incluí-los nas suas soluções se o cliente tiver uma preferência? A mim não!
    Algo completamente diferente seria imaginar que um vendedor de software tentasse vender Oracle. Para começar seria impossível. E mais importante: Um vendedor segue o dinheiro. E neste caso não receberiam nada por essas vendas impossíveis
Mas mais uma vez, o documento está interessante. E mostra uma imagem clara: O investimento no produto, o facto de que é menos conhecido que o que merecia, o facto de que os clientes são fiéis etc.
Muito bem!

Tuesday, March 18, 2014

Explain plans: for the last time / Planos de execução: pela última vez

This article is written in English and Portuguese (original version here)
Este artigo está escrito em Inglês e Português (versão original aqui)

English version:

If there is a topic that have always caught my attention it's the ability (or lack of) to capture a query plan in a client tool. This has been one of the most dirty Informix "secrets". This should be an essential feature of any RDBMs, and Informix provided the query plan, but in a very akward manner. A file was written on the database server (or on a filesystem mounted on the database server). This served us well enough when programmers used to work on the same machine as the database server, but those times are gone (a long time ago). I dedicated some time to a rather complex way of solving this after IBM introduced a function called EXPLAIN_SQL() that would return an XML file containing the query plan. This was the topic for my presentation on IIUG conference in 2010, but it was no more than a proof of concept. In other words it was not useable.
More recently I implemented a much simpler way to have the plan in any tool and documented it here in the blog. This works, it's simple and has little to no drawbacks. But it's still an hack...

Now, with some 20 years of delay, an article tells us that IBM has finally implemented this. There is a new function called ifx_explain() that accepts the query for which we want the query plan and returns the plan in text format. There is also an equivalent function called bson_explain() that returns the query plan in BSON format which can be casted to JSON (::JSON)

Please note that this is not documented. This new website (meanwhile it was added to the link list) is very recent and seems to be a great source of information. Some tweets mentioned it as belonging to John Miller who is one of the historic guys behind Informix (he was involved in all things related to backups, he's the "father" of Open Admin Tool, has several articles which even today are the references about update statistics, is deeply involved in JSON functionality etc.).

As a final note, I'd like to speak about something that is known to happen and this is just a clear example of that: we have implemented features in the engine which are not documented. The reason for that should be that they haven't passed (does not mean they failed or would fail) through the QA tests considered necessary to allow their general use. Which is understandable. But my point is that some of these were implemented too long ago. Time and resources were employed in creating them, and after too much time customers still haven't seen the benefits of that work. From my humble and personal (I must underline the "personal" for obvious reasons) perspective, there are some major issues with these situations:

  1. The cost (time and resources translate directly into money) it took to implement them are a waste until the day customers can use them and we can speak about them
  2. Some features (specifically SQL compatibility functions) may be used by customers without them knowing they're not documented. As an hypothetical example consider the new QUARTER() function announced in 12.10.xC3. A customer that is used to use that function in other RDBMs may just write it in SQL queries. If the server accepts it, he won't noticed if by any chance it was not (yet) documented. And if it's buggy, nasty things could happen, because in theory the customer was using a "non-existent" function, that was accepted by the server.
  3. Occasionally some of these functionalities are "leaked". I won't forget that I saw an ONCONFIG parameter in one of the sessions of IOD conference a few years ago, that was a very necessary feature for customers. I mentioned that to at least one customer and later I noticed it was not documented. After internal questioning the official position was "it didn't go through proper QA". Well... a bit late. Neither the slide had information that was supposed to be undocumented nor did I check that it was not documented. I simply tested and it worked!
So, my position about this is simple: If it's supposed to be used, it must be documented. If it's not supposed to be used, the engine MUST not accept it.  And in some cases I feel we're needing just a little bit more work (for QA) so that we can document those features and take "profit" (meaning allowing customers to use them) from all the investment put into it's creation.
Maybe the users and my colleagues that will be joining the IIUG 2014 conference in Miami want to include this topic in their discussions?

Having said this, it's a great day, as we closed a sad story about Informix!
If you're using 12.10.xC2+ then use this new feature. If not, try my soluction referenced above.

Versão Portuguesa:

Se há um assunto que sempre me mereceu atenção é a capacidade (ou falta dela) de capturar um plano de execução de uma query e apresentá-lo  numa ferramenta cliente. Este tem sido um dos "segredos sujos" do Informix. Isto será uma funcionalidade essencial a qualquer sistema de bases de dados, e de facto o Informix sempre disponibilizou o plano de execução, mas de uma maneira muito arcaica. O mesmo é escrito num ficheiro localizado (ou pelo menos acessível) no servidor de base de dados. Isto servia-nos razoalvelmente bem quando os programadores costumavam trabalhar na mesma máquina onde corria a base de dados. Mas esses tempos já lá vão (há muito tempo...).
Dediquei algum tempo a uma solução complexa para resolver isto, quando a IBM introduziu uma função chamada EXPLAIN_SQL() que devolvia a representação do query plan em XML. Este foi inclusive o tópico da minha apresentação na conferência de utilizadores do IIUG em 2010. Mas nunca passou de uma prova de conceito, ou por outras palavras nunca foi algo utilizável.
Mais recentemente implementei de forma muito mais simples a obtenção do plano de execução nas ferramentas cliente e documentei-o aqui no blog. Esta forma funciona e tem poucos ou nenhumas desvantagens. Mas ainda assim é um truque...

Porém agora, com uns vinte anos de atraso, apareceu um artigo que nos diz que a IBM finalmente implementou isto. Existe uma nova função chamada ifx_explain() que aceita a query para a qual queremos obter o plano de execução e retorna o mesmo sob a forma de texto simples. Existe ainda uma função semelhante, chamada bson_explain() que retorna o plano como um objecto BSON que pode ser transformando em JSON (::JSON)

Tenha em consideração que isto não está documentado. Este novo website (entretanto adicionado à lista de links) é muito recente e parece ser uma excelente fonte de informação. Alguns tweets mencionam que pertencerá ou que foi criado pelo John Miller, que é nem mais nem menos que um dos "históricos" por detrás do Informix (esteve envolvido com tudo o que se relaciona com backups, é o "pai" do Open Admin Tool, tem vários artigos que ainda hoje são as referências sobre o UPDATE STATISTICS, está profundamente envolvido com as funcionalidades JSON etc...)

Como nota final, gostaria de falar sobre algo que se sabe acontecer e este caso é um exemplo claro disso mesmo: temos implementado funcionalidades no motor que não se encontram documentadas. A razão para tal deverá ser que as mesmas não passaram (não necessariamente que falharam ou falhassem) pelos devidos testes de qualidade (QA), de forma a estarem prontas para uso genérico nos clientes. E isto parece-me razoável. Mas o meu "problema" é que algumas delas já foram implementadas há demasiado tempo. Tempo e recursos foram empregues para as criar, e mesmo depois de muito tempo os clientes ainda não podem tirar proveito desse esforço. Da minha humilde e pessoal (e é necessário reforçar o "pessoal" por motivos óbvios) perspectiva há vários potencias problemas que derivam destas situações:
  1. Os custos (tempo e recursos traduzem-se directamente em dinheiro) que derivaram da implementação destas funcionalidades são um desperdício até ao dia em que os clientes as possam usar e que possamos falar delas
  2. Algumas funcionalidades (especificamente funções de compatibilidade SQL) podem ser usadas pelos clientes, sei que eles saibam que não são documentadas. Como exemplo hipotético, consideremos a nova função QUARTER() introduzida na 12.10.xC3. Um cliente que esteja habituado a escrever SQL com essa função noutra base de dados, pode perfeitamente escrevê-la em Informix. Se o servidor a aceitar, o cliente não se irá aperceber se a função está ou não (ainda) documentada. E se a mesma estiver ainda imperfeita ou instável, coisas imprevisíveis podem acontecer, simplesmente porque um cliente usou algo "que não existe" mas que o servidor aceitou
  3. Ocasionalmente, algumas destas funcionalidades "transparecem" para a comunidade. Não me vou esquecer de ter visto um parâmetro de $ONCONFIG numa sessão de uma conferência IOD há alguns anos atrás, que ativa uma funcionalidade bastante necessária aos clientes. Eu já a mencionei a pelo menos um cliente e só depois me apercebi que não estava documentada. Depois de indagar internamente entendi que a posição oficial era "não sofreu testes significativos de QA". Bom... um pouco tarde demais. Nem o diapositivo tinha informação de que não estava documentado, nem eu verifiquei isso. Apenas fiz alguns testes e funcionou!
Portanto, a minha posição sobre o tema é simples: Se é suposto ser usado tem de estar documentado. Se não é suposto ser usado o motor NÃO pode aceitar. E em alguns casos sinto que necessitamos um pedacinho mais de esforço (para QA), de forma a que possamos documentar estas funcionalidades e obter "lucro" (ou seja, permitir que os clientes as usem) de todo o investimento colocado na sua criação.
Talvez os utilizadores e colegas que vão estar presentes na conferência do IIUG 2014 em Miami queiram incluir este tópico nas suas discussões?

Posto isto, foi um grande dia, pois fechámos uma história triste do Informix.
Se usa uma versão 12.10.xC2+ use esta nova funcionalidade. Senão recorra à forma que documentei e que referi no início