Small query performance analysis / Pequena análise de performance de querys

This article is written in English and Portuguese
Este artigo está escrito em Inglês e Português

English version:

The need...

The end of the year is typically a critical time for IT people. Following up on last article's I'm still working with performance issues. "Performance issues on Informix?!" I hear you say... Well yes, but to give you an idea of the kind of system I'm talking about I can say that recently we noticed three small tables (between 3 and 60 rows) that between mid-night and 11AM were scanned 33M times. To save you the math, that's around 833 scans/queries for each of these tables per second. And this started to happen recently, on top of the normal load that nearly 3000 sessions can generate...
So, the point is: every bit of performance matters. And in most cases, on this system there are no long running queries. It's mostly very short requests made an incredible number of times. And yes, this makes the DBA life harder... If you have long running queries with bad query plans they're usually easy to spot. But if you have a large number of very quick queries, but with questionable query plans, than it's much more difficult to find.

Just recently I had one of this situations. I've found a query with a questionable query plan. The query plan varies with the arguments and both possible options have immediate response times (fraction of a second). That's not the first time I've found something similar, and most of the times I face the same situation twice I usually decide I need to have some tool to help me on that.

The idea!

The purpose was to see the difference in the work the engine does between two query plans. And when I say "tool" I'm thinking about a script. Last time I remember having this situation, I used a trick in dbaccess to obtain the performance counters for both the session, and the tables involved. Some of you probably know, others may not, but when dbaccess parses an SQL script file it can recognize a line starting with "!" as an instruction to execute the rest of the line as a SHELL command. So basically what I did previously was to customize the SQL script containing the query like this:

!onstat -z

SELECT .... FROM .... WHERE ...

!some_shell_scritpt

where some_shell_script had the ability to find the session and run an onstat -g tpf and also an onstat -g ppf. These two onstat commands show us a lot of performance counters respectively from the threads (tpf) and from the partitions (ppf). The output looks like:

IBM Informix Dynamic Server Version 11.70.UC4 -- On-Line -- Up 7 days 23:42:15 -- 411500 Kbytes

Thread profiles
tid lkreqs lkw dl to lgrs isrd iswr isrw isdl isct isrb lx bfr bfw lsus lsmx seq
24  0      0   0  0  0    0    0    0    0    0    0    0  0   0   0    0    0  
26  0      0   0  0  0    0    0    0    0    0    0    0  95  95  0    0    0  
51  32917  0   0  0  21101 13060 3512 57   532  3795 0    0  91215 29964 0    125008 4226
52  39036  0   0  0  9099 11356 2648 80   1372 265  0    0  45549 9312 0    244900 21 
49  705    0   0  0  574  8938 0    139  0    139  0    0  22252 148 0    5656 541
2444 706    0   0  0  14   344  0    4    0    0    3    0  819 7   136  224  0  

This tells us the thread Id, lock requests, lock waits, deadlocks, timeouts, logical log records, isam calls (read, write, rewrite, delete, commit and rollback), long transactions, buffer reads and writes, logical log space used, logical log space maximum and sequential scans.
And this:

panther@pacman.onlinedomus.com:informix-> onstat -g ppf | grep -v "0     0     0     0     0     0     0     0     0     0     0     0"

IBM Informix Dynamic Server Version 11.70.UC4 -- On-Line -- Up 7 days 23:43:41 -- 411500 Kbytes

Partition profiles
partnum    lkrqs lkwts dlks  touts isrd  iswrt isrwt isdel bfrd  bfwrt seqsc rhitratio
0x100001   0     0     0     0     0     0     0     0     13697 0     0     100
0x100002   993   0     0     0     445   0     0     0     1460  0     2     100
0x10002d   6769  0     0     0     2379  34    340   34    9094  581   2     100
0x10002e   164   0     0     0     166   0     0     0     472   0     2     100
0x10002f   2122  0     0     0     2750  0     0     0     5288  0     0     100
0x100030   0     0     0     0     4     0     0     0     700   0     4     100
0x100034   14192 0     0     0     5922  192   80    192   15566 1274  0     100
0x100035   2260  0     0     0     188   80    0     80    2766  655   4     100
0x100036   1350  0     0     0     548   34    0     34    1872  249   0     100
0x100037   80    0     0     0     16    4     0     4     346   28    0     100
0x100038   4720  0     0     0     738   360   0     360   3734  1557  0     100

which tells us some of the above, but for each partition.
Note that I reset the counters, run the query and then obtain the profile counters. Ideally, nothing else should be running on the instance (better to do it on a test instance)

Sharing it

But I decided to make this a bit easier and I created a script for doing it. I'm also using this article to announce that starting today, I'll try to keep my collection of scripts on a publicly available site:

http://onlinedomus.com/informix/viewvc.cgi

This repository contains a reasonable amount of scripts for several purposes. Ideally I should create proper documentation and use cases for each one of them, but I currently don't have that. It's possible I'll cover some of them here in the blog, but probably only integrated in a wider article (like this one).

These scripts were created by me (with one exception - setinfx was created by Eric Vercelleto when we were colleagues in Informix Portugal and we should thank him for allowing the distribution), during my free time and should all contain license info (GPL 2.0). This means you can use them, copy them, change them etc. Some of them are very old and may not contain this info.
Some fixes and improvements were done during project engagements. Many of them were based on ideas I got from some scripts available in IIUG's software repository or from colleagues ideas, problems and suggestions (Thanks specially to António Lima and Adelino Silva)

It's important to notice that the scripts are available "as-is", no guarantees are made and I cannot be held responsible for any problem that it's use may cause.
Having said that, I've been using most of them on several customers for years without problems.
Any comments and/or suggestions are very welcome, and if I find the suggestions interesting and they don't break the script's ideas and usage, I'll be glad to incorporate them on future versions.

Many of the scripts have two option switches that provide basic help (-h) and version info (-V).
If by any chance you are using any of these scripts I suggest you check the site periodically to find any updates. I try my best to maintain retro-compatibility and old behavior when I make changes on them.

Back to the problem

So, this article focus on analyzing and comparing the effects of running a query with two (or more) different query plans. The script created for this was ixprofiling. If you run it with -h (help) option it will print:

panther@pacman.onlinedomus.com:fnunes-> ./ixprofiling -h
ixprofiling [ -h | -V ]
            -s SID database
            [-z|-Z|-n] database sql_script
     -h             : Get this help
     -V             : Get script version
     -s SID database: Get stats for session (SID) and database
     -n             : Do NOT reset engine stats
     -z             : Reset engine stats using onstat (default - needs local database)
     -Z             : Reset engine stats using SQL Admin API (can work remotely )

Let's see what the options do:

• -s SID database
  Shows the info similar to onstat -g tpf (for the specified session id) and onstat -g ppf (for the specified database)
  It will show information for all the partition objects in the specified database for which any of the profile counters is different from zero. Note that when I write partition, it can be a table, a table's partition or a table's index.
• database sql_script
  Runs the specified SQL script after making some changes that will (by default) reset the engine profile counters (-z option). See more information about the SQL script below
• -n
  Prevents the reset of profile counters (if you're not a system database administrator you'll need to specify this to avoid errors)
• -z
  Resets the profile counters using onstat -z. This is the quickest and most simple way to do it but will need local database access.
• -Z
  Resets the counters using SQL admin API, so it can be used on remote databases

And now let's see an usage example. The script has some particularities that need to be detailed.
First, since the idea is to compare two or more query plans we can put all the variations inside the SQL script, separating them by a line like:

-- QUERY

when the script finds these lines, it will automatically get the stats (from the previous query) and reset the counters to prepare for the next query. If you use just one query you don't need this, since by default it will reset the counters at the beginning and show the stats at the end.
If you put two or more queries on the script don't forget to end each query with ";" or it will break the functionality.
Let's see a practical example. I have a table with the following structure:

create table ibm_test_case 
  (
    col1 integer,
    col2 smallint not null ,
    col3 integer,
    col4 integer,
[... irrelevant bits... ]
    col13 datetime year to second,
[... more irrelevant bits... ]
  );

create index ix_col3_col13 on ibm_test_case (col3,col13) using btree ;
create index ix_col4 on ibm_test_case (col4) using btree ;

and a query like:

select c.col1
from ibm_test_case c
where
        c.col3 = 123456789 and
        c.col4 = 1234567 and
        c.col13 = ( select max ( c2.col13 ) from ibm_test_case c2
                where
                        c2.col3 = c.col3 and
                        c2.col4 = c.col4
                );

The problem is the query plan for the sub-query. It can choose between an index headed by col3 and another on col4. So I create a test_case.sql with:

unload to /dev/null select c.col1
from ibm_test_case c
where
        c.col3 = 123456789 and
        c.col4 = 1234567 and
        c.col13 = ( select max ( c2.col13 ) from ibm_test_case c2
                where
                        c2.col3 = c.col3 and
                        c2.col4 = c.col4
                );

-- QUERY
unload to /dev/null select c.col1
from ibm_test_case c
where
        c.col3 = 123456789 and
        c.col4 = 1234567 and
        c.col13 = ( select --+ INDEX ( c2 ix_col3_col13 )
                max ( c2.col13 ) from ibm_test_case c2
                where
                        c2.col3 = c.col3 and
                        c2.col4 = c.col4
                );

Note that on the second query I'm forcing the use of a particular index.
Then we run:

ixprofiling stores test_case.sql

and we get the following output:

Database selected.

Engine statistics RESETed. Query results:

Explain set.


1 row(s) unloaded.


Thread profiles (SID: 2690)
LkReq LkWai DLks  TOuts LgRec IsRd  IsWrt IsRWr IsDel BfRd  BfWrt LgUse LgMax SeqSc Srts  DskSr SrtMx Sched CPU Time    Name        
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----------- ------------ 
5224  0     0     0     0     2611  0     0     0     2646  0     0     0     0     0     0     0     2170  0.051671256 sqlexec     

Partitions profiles (Database: stores)
LkReq LkWai DLks  TOuts DskRd DskWr IsRd  IsWrt IsRWr IsDel BfRd  BfWrt SeqSc Object name                                           
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ------------------------------------------------------
6     0     0     0     0     0     2     0     0     0     10    0     0     systables
2609  0     0     0     1933  0     2607  0     0     0     2609  0     0     ibm_test_case
1     0     0     0     2     0     1     0     0     0     6     0     0     ibm_test_case#ix_col3_col13
2608  0     0     0     3     0     1     0     0     0     21    0     0     ibm_test_case#ix_col4
Engine statistics RESETed. Query results:

1 row(s) unloaded.


Thread profiles (SID: 2690)
LkReq LkWai DLks  TOuts LgRec IsRd  IsWrt IsRWr IsDel BfRd  BfWrt LgUse LgMax SeqSc Srts  DskSr SrtMx Sched CPU Time    Name        
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----------- ------------ 
17    0     0     0     0     6     0     0     0     31    0     0     0     0     0     0     0     188   0.003161049 sqlexec     

Partitions profiles (Database: stores)
LkReq LkWai DLks  TOuts DskRd DskWr IsRd  IsWrt IsRWr IsDel BfRd  BfWrt SeqSc Object name                                           
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ------------------------------------------------------
6     0     0     0     0     0     2     0     0     0     10    0     0     systables
4     0     0     0     2     0     1     0     0     0     4     0     0     ibm_test_case
7     0     0     0     0     0     6     0     0     0     17    0     0     ibm_test_case#ix_col3_col13

So, we can now analyze the differences. As you can see the output is more friendly than the output from onstat. On the session section we can see the usual counters, plus the number of times the engine scheduled the thread(s) to run, the CPU time consumed and the name of the threads.
On the tables/partitions section, we can find the partition, table or index name in a friendly nomenclature (instead of the partnum).
As for the comparison, you can spot a big difference. Much more buffer reads and ISAM reads for the first query plan and also a bigger CPU time. Be aware however that for very fast queries the CPU times may show very big variance so don't assume a lower CPU time is always associated with the better query plan. You should repeat the test many times to see the oscillations.
Also note that the meaning of ISAM calls is many times misunderstood. Some people think it's the number of "SELECTs", others the number of rows returned... In reality it's the number of internal functions calls. Some engine settings like BATCHEDREAD_TABLE and BATCHEDREAD_INDEX may influence the number of calls for the same query and query result.

That's all for now. I leave you with the repository and hopefully future articles will focus on some of these scripts. Feel free to use them and send me you suggestions.

Versão Portuguesa:

A necessidade...

O fina do ano é tipicamente uma altura critica para os informáticos. Continuando no mesmo tema do último artigo, continuo a trabalhar com problemas de performance. "Problemas de performance em Informix?!" poderão estar a pensar... Bem, sim, mas para vos dar uma ideia do sistema sobre o qual estou a falar, posso dizer que recentemente notámos três pequenas tabelas (entre 3 e 60 linhas) que entre a meia-noite e as onze da manhã eram varridas (sequential scan) 33M de vezes. Para poupar nas contas posso já dizer que dá cerca de 833 scans/queries por segundo para cada uma das tabelas. E isto começou a acontecer recentemente sobre a carga "normal" que perto de 3000 sessões podem criar.
Portanto, a ideia é que cada bocadinho de performance tem impacto. Na maioria dos casos, este sistema não tem queries longas. Na maior parte das vezes os problemas são pedidos com curta duração mas feitos um imenso número de vezes. E sim, isto torna a vida dos DBAs mais dicfícil... Se tivermos queries longas com maus planos de execução são normalmente fáceis de identificar. Mas se tivermos um grande número de queries muito curtas, com um plano de execução questionável, isso é muito mais difícil de encontrar.

Ainda recentemente tive uma dessas situações. Detectei uma query com um plano de execução duvidoso. O plano de execução varia com os parâmetros usados e ambas as alternativas têm um tempo de resposta "imediato" (fracção de segundo). Não foi a primeira vez que encontrei algo semelhante, e na maioria dos casos em que enfrento uma situação duas vezes, normalmente decido que preciso de alguma ferramenta que me ajude no futuro.

A ideia!


O objectivo era evidenciar a diferença no trabalho feito pelo motor entre dois planos de execução. E quando refiro "ferramenta" estou a pensar num script. A última vez que me lembro de ter tido uma situação destas  usei um truque no dbaccess para obter os indicadores de performance tanto para a sessão como para as tabelas envolvidas.
Alguns de vós saberão, outros não, mas quando o dbaccess lê um scritpt SQL pode reconhecer uma linha começada com "!" como uma instrução para executar o resto da linha como um comando SHELL. Assim, o que fiz em situações anteriores foi alterar o script SQL que continha a query para algo do género:

!onstat -z

SELECT .... FROM .... WHERE ...

!um_shell_scritpt

onde um_shell_script tem a capacidade de encontrar a sessão e correr um onstat -g tpf e também um onstat -g ppf. Ests dois comandos mostram-nos uma série de contadores de performance respectivamente da sessão/thread (tpf) e das partições (ppf). O output é semelhante a isto:

IBM Informix Dynamic Server Version 11.70.UC4 -- On-Line -- Up 7 days 23:42:15 -- 411500 Kbytes

Thread profiles
tid lkreqs lkw dl to lgrs isrd iswr isrw isdl isct isrb lx bfr bfw lsus lsmx seq
24  0      0   0  0  0    0    0    0    0    0    0    0  0   0   0    0    0  
26  0      0   0  0  0    0    0    0    0    0    0    0  95  95  0    0    0  
51  32917  0   0  0  21101 13060 3512 57   532  3795 0    0  91215 29964 0    125008 4226
52  39036  0   0  0  9099 11356 2648 80   1372 265  0    0  45549 9312 0    244900 21 
49  705    0   0  0  574  8938 0    139  0    139  0    0  22252 148 0    5656 541
2444 706    0   0  0  14   344  0    4    0    0    3    0  819 7   136  224  0  

É-nos mostrado o ID da thread, número de pedidos de lock, esperas em locks, deadlocks, lock timeouts, chamadas ISAM (leitura, escrita, re-escrita, apagar, commit e rollback), transacções longas, leituras e escritas de buffers, espaço usado em logical logs e máximo espaço usado em logical logs e número de sequential scans. E isto:

panther@pacman.onlinedomus.com:informix-> onstat -g ppf | grep -v "0     0     0     0     0     0     0     0     0     0     0     0"

IBM Informix Dynamic Server Version 11.70.UC4 -- On-Line -- Up 7 days 23:43:41 -- 411500 Kbytes

Partition profiles
partnum    lkrqs lkwts dlks  touts isrd  iswrt isrwt isdel bfrd  bfwrt seqsc rhitratio
0x100001   0     0     0     0     0     0     0     0     13697 0     0     100
0x100002   993   0     0     0     445   0     0     0     1460  0     2     100
0x10002d   6769  0     0     0     2379  34    340   34    9094  581   2     100
0x10002e   164   0     0     0     166   0     0     0     472   0     2     100
0x10002f   2122  0     0     0     2750  0     0     0     5288  0     0     100
0x100030   0     0     0     0     4     0     0     0     700   0     4     100
0x100034   14192 0     0     0     5922  192   80    192   15566 1274  0     100
0x100035   2260  0     0     0     188   80    0     80    2766  655   4     100
0x100036   1350  0     0     0     548   34    0     34    1872  249   0     100
0x100037   80    0     0     0     16    4     0     4     346   28    0     100
0x100038   4720  0     0     0     738   360   0     360   3734  1557  0     100

que nos mostra alguns dos contadores anteriores, mas por partição.
Note-se que re-inicializo os contadores, corro a query e depois obtenho os outputs. Idealmente não deverá estar mais nada a correr na instância (é preferível usar uma instância de teste).

Partilha


Mas decidi tornar isto um pouco mais fácil e criei um script para o fazer. Estou também a usar este artigo para anunciar que a partir de hoje, tentarei manter a minha colecção de scripts disponível num site público:

http://onlinedomus.com/informix/viewvc.cgi

Este repositório contém uma quantidade razoável de scripts e outras ferramentas úteis para várias tareafas. Idealmente eu deveria criar documentação e casos de uso para cada um deles, mas de momento isso não está feito. É possível que vá descrevendo alguns destes scripts em futuros artigos, mas sempre integrados em assuntos mais vastos (como este)

Estes scripts foram criados por mim (com uma excepção - setinfx foi criado por Eric Vercelletto quando éramos colegas na Informix Portugal e devemos agradecer-lhe por permitir a distribuição), durante os meus tempos livres e devem conter informação de licenciamento (GPL 2.0). Isto quer dizer que podem ser usados, distribuidos, alterados etc.). Alguns podem não ter esta informação por serem muito antigos.
Naturalmente algumas correcções e melhorias foram feitas durante projectos em clientes, sempre que detecto algum erro ou hipótese de melhoria no seu uso. Muitos deles foram baseados em ideias que obtive de scripts existents no repositório do IIUG, ou de ideias, problemas e sugestões de colegas (agradecimento especial ao António Lima e ao Adelino Silva)

É importante avisar que os scripts são disponiblizados "como são", sem qualquer tipo de garantia implicita ou explicita e eu não posso ser responsabilizado por qualquer problema que advenha do seu uso. Posto isto, convém também dizer que a maioria dos scripts têm sido usados por mim em clientes ao longo de anos, sem problemas.

Quaisquer comentários e/ou sugestões são bem vindas, e se os achar interessantes terei todo o prazer em os incorportar em futuras versões (desde que não fujam à lógica e utilização do script)
Muitos destes scripts disponibilizam duas opções que fornecem ajuda básica (-h) e informação sobre a versão (-V).
Se utilizar algum destes scripts no seu ambiente, sugiro que verifique periodicamente se houve correcções ou melhorias, consultando o site com alguma regularidade. Sempre que possível evito que novas funcionalidades alterem o comportamento do script.

De volta ao problema

Este artigo foca a análise e comparação dos efeitos de executar uma query com dois (ou mais) planos de execução. O script criado para isso chama-se ixprofiling. Se corrido com a opção -h (help) mostra-nos:

panther@pacman.onlinedomus.com:fnunes-> ./ixprofiling -h
ixprofiling [ -h | -V ]
            -s SID database
            [-z|-Z|-n] database sql_script
     -h             : Get this help
     -V             : Get script version
     -s SID database: Get stats for session (SID) and database
     -n             : Do NOT reset engine stats
     -z             : Reset engine stats using onstat (default - needs local database)
     -Z             : Reset engine stats using SQL Admin API (can work remotely )

Vejamos o que fazem as opções:

• -s SID base_dados
  Mostra informação semelhante ao onstat -g tpf (para a sessão indicada por SID) e onstat -g ppf (para a base de dados indicada)
  Irá mostrar informação para todas as partições na base de dados escolhida, para as quais exista pelo menos um dos contadores com valor diferente de zero. Note-se que quando refiro partição estou a referir-me a uma tabela, a um fragmento de tabela fragmentada (ou se preferir particionada) ou a um indíce.
• base_dados script_sql
  Corre o script SQL indicado, fazendo alterações que irão (por omissão), re-inicializar os contadores de performance do motor (opção -z). Veja mais informação sobre o script SQL abaixo
• -n
  Evita a re-inicialização dos contadores de performance (se não fôr administrador do sistema de base de dados terá de usar esta opção para evitar erros)
• -z
  Faz a re-inicialização dos contadores do motor usando o comando onstat -z. Esta é a forma mais simples e rápida de o fazer, mas requer que a base de dados seja local
• -Z
  Faz a re-inicialização dos contadores utilizando a SQL Admin API, de forma que possa ser feito com bases de dados remotas

E agora vejamos um exemplo de uso. O script tem algumas particularidades que merecem ser datalhadas.
Primeiro e porque a ideia é comparar dois ou mais planos de execução, podemos colocar todas as variantes de plano de execução  dentro do mesmo script SQL usando uma linha como esta para separar as queries:

-- QUERY

Estas linhas são automaticamente substituídas por comandos que obtêm os contadores actuais (da query anterior) e que re-inicializam os mesmos contadores preparando a execução seguinte. Se usar apenas uma query não é necessário isto, pois por omissão a re-inicialização dos contadores é feita no início, e após a última query são automaticamente mostrados os contadores.
Se colocar duas ou mais queries no script não se esqueça de terminar cada uma com ";" ou o script não funcionará como esperado.
Vamos ver um exemplo prático. Tenho uma tabela com a seguinte estrutura:

create table ibm_test_case 
  (
    col1 integer,
    col2 smallint not null ,
    col3 integer,
    col4 integer,
[... parte irrelevante ... ]
    col13 datetime year to second,
[... mais colunas irrelevantes ... ]
  );

create index ix_col3_col13 on ibm_test_case (col3,col13) using btree ;
create index ix_col4 on ibm_test_case (col4) using btree ;

e uma query com:

select c.col1
from ibm_test_case c
where
        c.col3 = 123456789 and
        c.col4 = 1234567 and
        c.col13 = ( select max ( c2.col13 ) from ibm_test_case c2
                where
                        c2.col3 = c.col3 and
                        c2.col4 = c.col4
                );

O problem é o plano de execução da sub-query. Pode  escolher entre um índice começado pela coluna col3 e outro pela coluna col4. Por isso crio um ficheiro, caso_teste.sql com:

unload to /dev/null select c.col1
from ibm_test_case c
where
        c.col3 = 123456789 and
        c.col4 = 1234567 and
        c.col13 = ( select max ( c2.col13 ) from ibm_test_case c2
                where
                        c2.col3 = c.col3 and
                        c2.col4 = c.col4
                );

-- QUERY
unload to /dev/null select c.col1
from ibm_test_case c
where
        c.col3 = 123456789 and
        c.col4 = 1234567 and
        c.col13 = ( select --+ INDEX ( c2 ix_col3_col13 )
                max ( c2.col13 ) from ibm_test_case c2
                where
                        c2.col3 = c.col3 and
                        c2.col4 = c.col4
                );

Repare  que na segunda query estou a forçar o uso de um determinado índice:
Depois corro:

ixprofiling stores caso_teste.sql

e obtemos o seguinte:

Database selected.

Engine statistics RESETed. Query results:

Explain set.


1 row(s) unloaded.


Thread profiles (SID: 2690)
LkReq LkWai DLks  TOuts LgRec IsRd  IsWrt IsRWr IsDel BfRd  BfWrt LgUse LgMax SeqSc Srts  DskSr SrtMx Sched CPU Time    Name        
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----------- ------------ 
5224  0     0     0     0     2611  0     0     0     2646  0     0     0     0     0     0     0     2170  0.051671256 sqlexec     

Partitions profiles (Database: stores)
LkReq LkWai DLks  TOuts DskRd DskWr IsRd  IsWrt IsRWr IsDel BfRd  BfWrt SeqSc Object name                                           
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ------------------------------------------------------
6     0     0     0     0     0     2     0     0     0     10    0     0     systables
2609  0     0     0     1933  0     2607  0     0     0     2609  0     0     ibm_test_case
1     0     0     0     2     0     1     0     0     0     6     0     0     ibm_test_case#ix_col3_col13
2608  0     0     0     3     0     1     0     0     0     21    0     0     ibm_test_case#ix_col4
Engine statistics RESETed. Query results:

1 row(s) unloaded.


Thread profiles (SID: 2690)
LkReq LkWai DLks  TOuts LgRec IsRd  IsWrt IsRWr IsDel BfRd  BfWrt LgUse LgMax SeqSc Srts  DskSr SrtMx Sched CPU Time    Name        
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----------- ------------ 
17    0     0     0     0     6     0     0     0     31    0     0     0     0     0     0     0     188   0.003161049 sqlexec     

Partitions profiles (Database: stores)
LkReq LkWai DLks  TOuts DskRd DskWr IsRd  IsWrt IsRWr IsDel BfRd  BfWrt SeqSc Object name                                           
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ------------------------------------------------------
6     0     0     0     0     0     2     0     0     0     10    0     0     systables
4     0     0     0     2     0     1     0     0     0     4     0     0     ibm_test_case
7     0     0     0     0     0     6     0     0     0     17    0     0     ibm_test_case#ix_col3_col13

Então, podemos agora analisar as diferenças. Como se pode ver o resultado é mais simpático que o do onstat. Na secção relativa à sessão, podemos ver os contadores habituais, mais o número de vezes que o motor escalonou a thread para correr, e o tempo de CPU consumido, bem como o nome das threads
Na secção destinada às partições podemos encontrar os nomes das tabelas, partições ou indíces numa nomenclatura fácil de entender (em vez do partnum).
Sobre a comparação, podemos ver uma grande diferença. Muitos mais leituras de buffers e chamadas ISAM para o primeiro plano de execução e também mais consumo de CPU. Mas atenção que para queries muito rápidas os tempos de CPU podem apresentar uma variação muito grande. Por isso convém não assumir imediatamente que um plano de execução é melhor porque se vê um tempo de CPU menor na primeira interação. Deve repetir-se o teste muitas vezes para se verificar as oscilações.
Chamo também a atenção para o significado das chamadas ISAM. Muitas vezes vejo confusões sobre este tema. Algumas pessoas pensam que são o número de SELECTs (para os ISAM reads), ou que serão o número de linhas retornadas... Na realidade é o número de chamadas a funções internas. Algumas configurações do motor como BATCHEDREAD_TABLE e BATCHEDREAD_INDEX podem influenciar o número destas chamadas, para a mesma query e mesmo conjunto de resultados.


É tudo por agora. Deixo-lhe o repositório e a esperança que artigos futuros se foquem em alguns destes scripts. Use-os à vontade e envie quaisquer sugestões.

Optimizer secrets / segredos do optimizador

This article is written in English and Portuguese

Este artigo está escrito em Inglês e Português

English version:

Spending a lot of time with customers is great. It gives me time to go beyond the strict task execution that short projects allow. We actually have time to dive deep into what sometimes looks only as a curiosity. I'll describe one of this situations in this post. It started out as a normal performance issue. A query involving a join was causing an hash join and apparently there was an index that could be used. Some quick investigation showed that the datatypes didn't match on the join columns, and as such the index was not being used.
The situation was fixed (using an explicit cast since changing the datatypes would require more deep analysis) and the programmer(s) received an explanation about the issue so that future situations would be avoided. Simple right? Yes... But when you have inquiring minds and programmers with a few minutes to spare you may be surprised. And this was the case. A few weeks later the DBA team received a request to explain why we recommended that the columns used in joins should have the same datatype. A programmer had produced a testcase where the engine was able to convert the parameter sent and use the index. In other words, if the engine is smart enough why should we care?!

Although this could be considered a waste of time (using the same datatypes or explicit cast is a good practice, right?!) the question was interesting enough to make us spend some time with it. In fact I had seen situations in the past where apparently sometimes the engine was smart, and others not. I never thought too much about it, since I always recommended to follow the best practices (which clearly saves us some troubles). So, personally I also had this doubt, and together with the customer DBAs we started to do some tests. We came up with a very simple test case that we though would show the problem:

DATABASE stores;

DROP TABLE IF EXISTS tst_int;
DROP TABLE IF EXISTS tst_char;

CREATE TABLE tst_int
(
    c1 INTEGER,
    c2 INTEGER
);
CREATE TABLE tst_char
(
    c1 CHAR(15),
    c2 INTEGER
);

INSERT INTO tst_int
SELECT FIRST 2000 pg_pagenum, pg_partnum FROM sysmaster:syspaghdr;

INSERT INTO tst_char
SELECT FIRST 2000 pg_pagenum, pg_partnum FROM sysmaster:syspaghdr;

CREATE INDEX i_tst_int ON tst_int(c1);
CREATE INDEX i_tst_char ON tst_char(c1);

SET EXPLAIN ON;
SELECT "tst_int with char parameter:", * FROM tst_int WHERE c1 = '12345678';
SELECT "tst_char whth int parameter",* FROM tst_char WHERE c1 = 12345678;

--- demonstrates that each index key is being casted
INSERT INTO tst_char VALUES("a1", 12345678);
SELECT * FROM tst_char WHERE c1 = 12345678;

If we take a look at the query plan we see:

QUERY: (OPTIMIZATION TIMESTAMP: 12-10-2011 23:25:19)
------
SELECT "tst_int with char parameter:", * FROM tst_int WHERE c1 = '12345678'

Estimated Cost: 1
Estimated # of Rows Returned: 1

  1) informix.tst_int: INDEX PATH

    (1) Index Name: informix.i_tst_int
        Index Keys: c1   (Serial, fragments: ALL)
        Lower Index Filter: informix.tst_int.c1 = 12345678

So here the engine was "smart". Meaning it converted the CHAR to an INTEGER and that allowed it to use the index. Nice.


But here:

QUERY: (OPTIMIZATION TIMESTAMP: 12-10-2011 23:25:19)
------
SELECT "tst_char whth int parameter",* FROM tst_char WHERE c1 = 12345678

Estimated Cost: 84
Estimated # of Rows Returned: 1

  1) informix.tst_char: SEQUENTIAL SCAN

        Filters: informix.tst_char.c1 = 12345678

It looks as it's not that smart... Instead of converting the INTEGER parameter to a CHAR and use the index it decides to do the opposite: Converts all the CHARs in that column into INTEGERs and makes a SEQUENTIAL SCAN.
Since I didn't have a good explanation for this we decided to open a PMR to get an official technical support explanation.


Technical support reported that we had something in the documentation that tries to explain this:

http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.sqls.doc/ids_sqs_1620.htm

This documentation is highly confusing but it tells us something important: If we use a numeric filter against a CHAR column, the database server will convert all the CHAR values to numbers. This is precisely what we saw in the example above. But it still does not explain why. Let me show why with a few examples:

1. If we have "00123" in the table and we give 123 as the parameter, if we convert the number to CHAR and try to match it, ir would fail. Bad results...
2. If we have "234" in the column and we give 9 as the parameter for a "less or equal" (col <= 9), if we convert the number to CHAR, and apply the filter to the CHAR type, it would match ("9" >= "234"). Again it would return an incorrect result because by using an INTEGER as a parameter we're assuming INTEGER comparison

So, after this, the rule would seem pretty simple. Something like: "If you use a numeric parameter against a CHAR column, all the values in the column will be converted to numeric.  On the other hand, using a CHAR parameter against a numeric column allows the engine to convert the numeric to CHAR and if there is an index on the column it can be used".

But life's not that simple... If it was so simple why couldn't we find it in the documentation? I tried to search for other RDBMS (Oracle and MS SQL) documentation, and in those cases they're very clear about the issue. Something like "whenever an implicit CAST is needed we follow a precedence table of datatypes. The datatype with lower precedence will be converted into the datatype with higher precedence". Sincerely I thought this was a good way to put it, and if we did the same, why not document it properly? So the PMR already opened started to look like a documentation bug.
But again, life sometimes is not simple... And while this was being analyzed and discussed, the customer team discovered an interesting scenario: If you give an integer value as a filter against a CHAR column, AND the length of the integer value (excluding any leading zeros) is equal to the size of the column, than Informix will convert the number to CHAR and eventually will use an index on the specified column.
This is the optimizer being smart. If you think about it, if the number has the same number of digits as the length of the CHAR column, you can convert the number to CHAR and compare it. The result set will be correct no matter the values in question or the operator.

To end the story, while browsing the documentation in search for other topics we came across this:

http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.perf.doc/ids_prf_536.htm

It's clear and well explained. Informix makes the necessary casts but that can have a real impact on the performance, specially if exists an index on the column. And the optimizer is smart enough to use a better query plan in the only situation where it can be done. Really nice and at least here (Performance Guide) it's well explained. I really don't mind when a PMR generates a bug because that's a product improvement, but I must admit I prefer to be proven wrong and see that the product really works well!


Versão Portuguesa:

É ótimo passar muito tempo com clientes. Dá-me tempo para ir para além da estrita execução de tarefas que os projetos curtos permitem. Temos tempo para aprofundar o que por vezes não parece ser mais que uma curiosidade. Nesta entrada vou descrever uma dessas situações. Começou como um banal problema de performance. Uma query que envolvia um join estava a gerar um hash join havendo um índice que aparentemente podia ser usado. Após uma rápida investigação percebeu-se que os tipos de dados das colunas envolvidas no join não eram iguais e por isso o índice não era usado.
A situação foi corrigida (usando um CAST explícito pois mudar os tipos de dados teria necessitado de uma análise mais profunda e poderia ter outras implicações) e o programador(es) recebeu uma explicação sobre o problema para que situações semelhantes fossem evitadas no futuro. Simples, certo? Sim... Mas quando temos mentes curiosas e programadores com alguns minutos para dispensar podemos ser surpreendidos. E este foi um desses casos. Umas semanas mais tarde a equipa de DBAs do cliente recebeu um pedido para explicar o porquê da recomendação, acompanhado de um caso de teste que demonstrava que o motor conseguia usar um índice mesmo quando os tipos de dados não batiam certo. Por outras palavras, se o motor tem inteligência para o fazer, porque nos devemos nós preocupar?!


Apesar de isto poder ser considerado uma perda de tempo (usar os mesmos tipos de dados ou um CAST explícito é uma boa prática, não é?!) a questão era suficientemente interessante para nos fazer gastar algum tempo com ela. Na realidade já tinha tido situações no passado onde aparentemente o motor parecia inteligente, e outras onde tal não acontecia. Nunca pensei muito no assunto, dado que recomendo sempre que seja seguida as boas práticas (que claramente nos evitam problemas). Portanto, pessoalmente também tinha esta dúvida e em conjunto com os DBAs do cliente iniciámos alguns testes. Criámos um exemplo muito simples que pensamos que demonstra o problema:

DATABASE stores;

DROP TABLE IF EXISTS tst_int;
DROP TABLE IF EXISTS tst_char;

CREATE TABLE tst_int
(
    c1 INTEGER,
    c2 INTEGER
);
CREATE TABLE tst_char
(
    c1 CHAR(15),
    c2 INTEGER
);

INSERT INTO tst_int
SELECT FIRST 2000 pg_pagenum, pg_partnum FROM sysmaster:syspaghdr;

INSERT INTO tst_char
SELECT FIRST 2000 pg_pagenum, pg_partnum FROM sysmaster:syspaghdr;

CREATE INDEX i_tst_int ON tst_int(c1);
CREATE INDEX i_tst_char ON tst_char(c1);

SET EXPLAIN ON;
SELECT "tst_int with char parameter:", * FROM tst_int WHERE c1 = '12345678';
SELECT "tst_char whth int parameter",* FROM tst_char WHERE c1 = 12345678;

--- demonstrates that each index key is being casted
INSERT INTO tst_char VALUES("a1", 12345678);
SELECT * FROM tst_char WHERE c1 = 12345678;

Se olharmos para o plano de execução vemos:

QUERY: (OPTIMIZATION TIMESTAMP: 12-10-2011 23:25:19)
------
SELECT "tst_int with char parameter:", * FROM tst_int WHERE c1 = '12345678'

Estimated Cost: 1
Estimated # of Rows Returned: 1

  1) informix.tst_int: INDEX PATH

    (1) Index Name: informix.i_tst_int
        Index Keys: c1   (Serial, fragments: ALL)
        Lower Index Filter: informix.tst_int.c1 = 12345678

Ou seja, aqui o motor era "esperto". Convertia o CHAR para INTEGER e isso permitia usar o índice. Boa.

Mas aqui:

QUERY: (OPTIMIZATION TIMESTAMP: 12-10-2011 23:25:19)
------
SELECT "tst_char whth int parameter",* FROM tst_char WHERE c1 = 12345678

Estimated Cost: 84
Estimated # of Rows Returned: 1

  1) informix.tst_char: SEQUENTIAL SCAN

        Filters: informix.tst_char.c1 = 12345678

Parece que não é assim tão esperto.... Em vez de converter o parâmetro INTEGER para um CHAR e usar o índice, decide fazer o oposto: Converte todos os CHARs daquela coluna para INTEGERs e executa um SEQUENTIAL SCAN.
Como não tinha uma boa explicação para isto decidi abrir um PMR para obter uma explicação oficial do suporte técnico.


O suporte técnico informou que nós tínhamos algo na documentação que tenta explicar isto:

http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.sqls.doc/ids_sqs_1620.htm

Esta documentação é altamente confusa, mas diz-nos algo importante: Se usarmos um filtro numérico contra uma coluna do tipo CHAR, o servidor de base de dados irá converter todos os valores CHAR da coluna em números. Isto é exatamente o que encontrámos no exemplo acima. Mas ainda não explica porquê. Deixe-me explicar o porquê com alguns exemplos:

1. Se tivermos o valor "00123" na tabela e usarmos 123 como parâmetro/filtro, se convertermos o número para CHAR e tentarmos fazer a comparação não vamos retornar nada ('123' != '00123') . Resultados errados...
2. Se tivermos o valor "234" na coluna e dermos 9 como parâmetro/filtro para uma condição de menor ou igual (col <= 9), se convertermos o número para CHAR isso implicaria que a linha era retornada  ("9" >= "234"). Mais uma vez iria retornar um resultado "errado", pois ao usarmos um parâmetro numérico estamos a assumir comparação numérica (onde 9 < 234)

Assim, depois disto a regra parecia muito simples. Algo como "Se usarmos um parâmetro numérico contra uma coluna do tipo CHAR, todos os valores da coluna serão convertidos para numérico. Por outro lado, usar um parâmetro CHAR contra uma coluna numérica permite que o motor converta o parâmetro para número e use um índice caso exista".

Mas a vida não é assim tão simples... Se era assim tão direto porque razão não estava documentado (ou pelo menos nós não tínhamos encontrado)? Tentei procurar na documentação de outros sistemas de gestão de bases de dados (Oracle e MS SQL) , e nestes casos eram bastante claros sobre o assunto. Um resumo livre seria "sempre que um CAST implícito seja necessário, seguimos uma tabela de precedências de tipos de dados. O tipo de dado com menor precedência será convertido para o que tem mais precedência". Sinceramente isto pareceu-me uma forma correta de colocar a questão, e se fazíamos o mesmo porque não ter isto claro na documentaçã? Assim o PMR já aberto parecia encaminhar-se para um bug de documentação.

Mas novamente, a vida por vezes não é simples... E enquanto isto estava a ser analisado e discutido , a equipa do cliente descobriu um cenário interessante: Se usarmos um numero como filtro contra uma coluna do tipo CHAR, e o numero de dígitos desse inteiro (excluindo quaisquer zeros à esquerda) for igual ao número de caracteres definido na coluna, então o número será convertido para CHAR e um eventual índice na coluna será usado.
Isto é o optimizador a ser "esperto". Se pensarmos sobre o assunto, se o número de dígitos do número for igual ao número de caracteres da coluna, podemos convertê-lo para CHAR e compará-lo com a coluna. O resultado será o correto independentemente dos valores em questão e do operador.


Para terminar a história, enquanto consultáva-mos a documentação devido a outro assunto, demos com o seguinte:

http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.perf.doc/ids_prf_536.htm

Está claro e bem explicado. O Informix efetua os CASTS necessários para resolver queries onde existam inconsistências entre os tipos de dados. Mas isso pode ter um impacto significativo na performance, especialmente se existir um índice na coluna. E o optimizador é suficientemente inteligente para obter um melhor plano de execução na única situação onde isso pode ser feito. Muito correto e pelo menos aqui (Guia de Performance) está bem explicado.
Sinceramente não me importo muito quando um PMR dá origem a um bug, pois isso traduz-se numa melhoria do produto, mas tenho de admitir que prefiro que resulte que estava enganado e ser-me mostrado que o produto está a funcionar bem!

Short notes / Notas curtas

This article is written in English and Portuguese
Este artigo está escrito em Inglês e Português

English version:

This is a very short article that consists of a few short notes.
First, as you can notice from the banner above, IIUG is going to organize the 2012 International User Conference in San Diego on April 22-25 2012. Next year's conference will move from the traditional Kansas City location to San Diego, California. As usual, you can get a lot of value for money. I will probably skip this one, but Ill surely miss it. Please consult the conference URL for all the details.

Some time ago I added a blog to my list, mas to be honest at the time I didn't review the content that it already had. Recently, during some investigation I ended up there and I had the opportunity to browse over it's content and it's really interesting. I'm talking about http://www.jfmiii/informix/ . The URL shows that it's author is John Miller, one of the most well known elements of the Informix community. John is the author of a lot of Informix material, specially about update statistics and backup and restore. In the last years is has been involved with Open Admin Tool (OAT). He's also a usual presence in conferences and presentations done or sponsored by IBM. I highly recommend this blog. The blog also includes contributions from other well known members of the community


Still related to blogs, I just added one more to the list: http://informixdba.wordpress.com/ . It's author is Ben Thompson, an UK based Informix and Oralce DBA. Not many articles yet, but it looks promising.


Philip Howard, from Bloor Research talks about the Informix revive and also mentions it in the article "Breakthrough and instrumented applications".



TatukGIS a Polish based GIS software and solution provider recently extended it's products support to Informix and DB2

In a blog hosted by Microsoft, there is a reference to future support for Informix 11 on their BizTalk ESB software in the next release of the product

Versão Portuguesa:

Este é um artigo muito curto e consistirá em algumas notas sobre vários temas.
Primeiro, como pode ver pelo cabeçalho acima, o IIUG vai organizar a conferência internacional de utilizadores de 2012 em San Diego entre 22 e 25 de Abril de 2012. A conferência do ano que vem deixará a localização tradicional em Kansas City e irá para San Diego na California. Como é hábito pode obter bastante valor pelo investimento. Em princípio não irei estar presente, mas irei sem dúvida sentir a falta. Consulte o endereço da conferência para mais detalhes.

Há uns tempos adicionei um blog à lista, mas sinceramente na altura não revi todo o conteúdo que já existia. Recentemente durante alguma investigação que efectuei acabei por lá ir parar e tive oportunidade de verificar que tem conteúdo bastante interessante. Estou a falar de http://www.jfmiii/informix/ . Pelo endereço e conteúdo é fácil perceber que se trata do John Miller um dos mais conhecidos elementos da comunidade Informix. O John tem estado ligado a imenso material sobre Informix, em particular artigos sobre update statistics, backup e restore e outros. Nos últimos anos tem estado por detrás do Open Admin Tool. É também presença habitual em conferências e apresentações da IBM. Recomendo vivamente. O blog contém também contribuições de outros elementos bem conhecidos da comunidade

Ainda relacionado com blogs, acabei de adicionar um à lista: http://informixdba.wordpress.com/ . O seu autor é Ben Thompson, um DBA Informix e Oracle baseado no Reino Unido. Ainda não tem muitos artigos, mas parece promissor.

Philip Howard, da Bloor Research fala sobre o Informix revive e também o refere noutro artigo: "Breakthrough and instrumented applications".

TatukGIS, um fornecedre de software e soluções GIS, baseado na Polónia anunciou recentemente a extensão do suporte nos seus produtos para Informix e DB2


Num blog hospedado na Microsoft, existe uma referencia ao futuro suporte ao Informix 11 na próxima release do seu BizTalk ESB.

Informix 11.70.xC4 is available / Informix 11.70.xC4 está disponível

This article is written in English and Portuguese
Este artigo está escrito em Inglês e Português

English Version:

IBM has relesed Informix 11.70.xC4 in October 25. The changes in this release, taken directly from the release notes, are (comments added):

• Administration
• Enhancements to the OpenAdmin Tool (OAT) for Informix
  OAT now allows the management of database users (for non-OS users) and OAT is now delivered and installable with Client SDK for Windows (32bits), Linux (32 and 64 bits) and MAC OS (64 bits)
• Enhancements to the Informix Replication Plug-in for OAT
  The ER plugin now follows ER improvements and can handle multibyte locales.
• Informix Health Advisor Plug-in for OAT
  A totally new plugin that can examine a great number of metrics and configuration details, warning you (email) of anything not within the recommended settings and/or defined thresholds.
  The checks can be scheduled and you can setup several different profiles. Each will run a specific (and configurable) set of metrics.
• Dynamically change additional configuration parameters
  Several parameters can now be changed with onmode -wm/-wf. Some of them are really important (WSTATS, AUTO_REPREPARE, CKPTINTVL, DIRECTIVES, OPTCOMPIND, SHMADD) and can save you from planned downtime. Others are more or less irrelevant (some of them could be changed by editing the $ONCONFIG file), but it's important that they can be changed through SQL Admin API for client DBA tools
• Compare date and interval values
  API extensions to compare datetime and interval values.
• Plan responses to high severity event alarms
  Could not understand what is new. This could be done before by customizing the ALARMPROGRAM script
• Data sampling for update statistics operations
  A new parameter (USTLOW_SAMPLE) defines if you want to sample the data for the index information gathering or not (indexes with more than 100.000 leaf pages). 11.70.xC3 did this by default. This can also be set at session level. Note that this can have a dramatic impact on the time it takes to regenerate your statistics. The "LOW" will be the slowest for large tables with indexes...
• SQL administration API command arguments for creating sbspaces
  New options to create smart blob spaces with logging and access time recording in SQL admin API
• Monitor client program database usage
  The client program's full path name is now available in onstat -g ses.
  Note that although you can use this to monitor and control access, this information is sent by the client side and potentially can be faked (not the average user, but an attacker could do it)
• Progress of compression operations
  Two new columns in onstat -g dsk show the approximate percentage of the tasks already completed and the estimated time to finish
• High availability and Enterprise Replication
• Easier setup of faster consistency checking
  When using ifx_replcheck and an index is created on it, the CRCOLS are not necessary
• Handle Connection Manager event alarms
  Scripts used for processing connection manager alarms now have access to two variables that identify their name (INFORMIXCMNAME) and unit name (INFORMIXCMCONUNITNAME). This facilitates the script creation
• Easier startup of Connection Manager
  When the variable CMCONFIG is set and points to the connection manager configuration file, it can be started, stop and restarted without specifying the configuration file. Much like ONCONFIG is used for the engine
• Prevent failover if the primary server is active
  A new parameter called SDS_LOGCHECK can specify an number of seconds while the SDS secondaries will monitor the logical logs for activity (which would be generated by the primary server). This tries to implement a safety measure to prevent an SDS server to become a primary after a "false" failure of the primary. Note that usually this is prevented by using I/O fencing, but if that is not available this can be another way to make sure you don't end up with two primaries
• Configure secure connections for replication servers
  A new parameter called S6_USE_REMOTE_SERVER_CFG defines if the file specified by REMOTE_SERVER_CFG will also be used for client connections using the s=6 SQLHOSTS options (for replication). If this parameter is set to 1 the file will be used, otherwise it will default to the old behavior and use $INFORMIXDIR/etc/hosts.equiv
• Security
• Global Security Kit (GSKit) support
  A new parameter (GSKIT_VERSION) can be used to specify the Global Security Kit version you intend to use. Informix 11.70.xC4 ships with version 8, but can use version 7
• Use a file to authenticate server connections in a secured network environment
  Already mentioned above (S6_USE_REMOTE_SERVER_CFG)
• Time Series data
• IBM Informix TimeSeries Plug-in for Data Studio
  This new plugin allows the interaction with TimeSeries data from Optim Data Studio and Optim Developer Studio
• Delete a range of elements and free empty pages from a time series
  Delete improvements in TimeSeries data that should free some pages
• Aggregate time series data across multiple rows
  Improvement in how we can aggregate TimeSeries data.

Besides bringing some new features, this release also fixes some important bugs that appeared in xC3 around the new automatic read ahead configuration. Apart from this I think it's important to notice the following points:

• TimeSeries continues to have a great focus on the current enhancements off Informix. This is expectable and desired considering the great news around customer success stories and the recent benchmark.
• The new OAT health plugin. I didn't have time yet to really explore it, but for sure I would have done a few things differently like re-using the alarmprogram configuration to send alarms. But this is great and being a plugin it can easily be changed if it doesn't fit your needs.
• The inclusion of OAT inside CSDK is a good step (from my point of view). It makes it easier to get and install. I've installed it very quickly in Windows
• The new dynamically changeable parameters are a very good step. Of course I would love to be able to change all the parameters without stopping the instance. But we're getting closer. My next favorites would be (and why):
• LOGBUFF, PHYSBUFF
  After instance healthchecks, may times these should be changed
  I suppose this should not be to hard, since there are a set of each and they're used in a circular fashion. So on change, they could be resized.
  LOGBUFF would probably be harder due to it's role in replication (HDR)
• TBLSPACE_STATS
  Again, it's a good practice to have this active. But having to stop the instance to fix this is not nice
• SYSSBSPACENAME, SBSPACENAME
  Sometimes these are not set, but the usage of some new features required them. A restart is not nice...
• CLEANERS
  Again, many times needed after a configuration check
• SHMTOTAL
  Many customers don't set it. And sometimes you may need to reduce it or increase it (new OS are able to add/remove memory online). Of course we would not be able to lower it below current usage.
• DD_*, DS_*, PC_*, PLCY_*, USRC_
  Again, usual candidates to change after a health check. These would be trickier to change, but if we did it for statement cache we should be able to do it for all the other caches. Also a functionality to "flush" these caches or at least remove some entries would be nice.
• EXT_DIRECTIVES
  Some times customers find out they need it, but it's off by default... Again the restart
• DBCREATE_PERMISSION
  This is in my view dynamic by nature

Versão Portuguesa:

A IBM lançou o Informix 11.70.xC4 no dia 25 de Outubro. Estas são as mudanças nesta versão (retiradas directamente das release notes (comentários adicionados):

• Administração
• Melhorias no OpenAdmin Tool (OAT)
  O OAT permite agora a gestão dos utilizadores de base de dados (para utilizadores não autenticados no sistema operativo) e é agora distribuido com o Client SDK para Windows (32bits), Linux (32 and 64 bits) e MAC OS (64 bits)
• Melhorias no plugin de replicação do OAT
  O plugin de ER segue as emlhorias do próprio ER e pode lidar com locales multibyte
• Informix Health Advisor Plug-in para o OAT
  Um plugin totalmente novo que pode examinar um grande número de métricas e detalhes de configuração, avisando (por email) de algo que fuja às recomendações e/ou a parâmetros pré-definidos. Podemos calendarizar verificações periódicas de acordo com vários perfis. Cada perfil irá validar um conjunto de métricas especificas (e configurável)
• Alteração dinâmica de mais parâmetros
  Vários parâmetros podem agora ser mudados com o onmode -wm/-wf. Alguns são realmente importantes (WSTATS, AUTO_REPREPARE, CKPTINTVL, DIRECTIVES, OPTCOMPIND, SHMADD) e podem evitar paragens planeadas. Outros são relativamente irrelevantes (alguns já podiam ser alterados editando o $ONCONFIG), mas é importante que possam ser alterados através da SQL Admin API, para que possam ser mudados em ferramentas cliente
• Comparação de datas e intervals
  Extensão da API extensions para comparar valores dos tipos datetime e interval
• Planear resposta a eventos de alta severidade
  Não consegui perceber o que foi feito de novo. Isto já podia ser feito através da configuração/adaptação do script configurado no parâmetros ALARMPROGRAM
• Data sampling para operações de UPDATE STATISTICS
  Um novo parâmetro (USTLOW_SAMPLE) define se queremos efectuar sampling na recolha de dados sobre os índices ou não (índices com mais de 100.000 "folhas" - leaf pages). 11.70.xC3 fazía-o por omissão. Isto pode ser activado ao nível da sessão. Note-se que isto pode ter um impacto dramático no tempo que leva a refazer as estatísticas. A opção "LOW" é a mais lenta para tabelas grandes em muitos casos..,
• Novos argumentos para criar smart blob spaces com a SQL administration API
  As novas opções permitem criar os smart blob spaces com ou sem logging e mantendo ou não o tempo de acesso aos objectos
• Monitorização do uso das bases de dados por programa cliente
  O nome (caminho) completo do programa cliente está disponível no output do onstat -g ses
  Note-se que apesar de isto poder ser usado para monitorizar ou até condicionar o acesso a uma base de dados, esta informação é enviada pelo cliente e pode potentcialmente ser alterada (não pelo utilizador comum, mas certamente por um atacante)
• Consulta do progresso das operações de compressão
  Duas novas colunas no output do onstat -g dsk mostras a percentagem aproximada de trabalho já efectuado e o tempo estimado para a sua conclusão
• Alta disponibilidade e Enterprise Replication
• Configuração mais fácil de verificação de consistência
  Quando se usa o ifx_replcheck, e se cria um índice por ele, os CRCOLS não são necessários
• Lidar com alarmística do Connection Manager
  Scripts usados para processar os alarmes do(s) connection managers têm agora acesso a duas variáveis que identificam o seu nome  (INFORMIXCMNAME) e nome de unidade (INFORMIXCMCONUNITNAME). Isto facilita a criação dos scripts
• Arranque facilitado do Connection Manager
  Quando a variável CMCONFIG está definida e aponta para o ficheiro de configuração do Connection Manager, este pode ser iniciado, parado e re-iniciado sem especificar o ficheiros de configuração. Muito semelhante à utilização da variável ONCONFIG para o motor
• Prevenção de failover se o servidor primário ainda estiver activo
  Um novo parâmetro, chamado SDS_LOGCHECK pode indicar o número de segundos que os SDS secundários irão monitorizar os logical logs para detectar actividade (que a existir seria gerada pelo primário). Isto tenta implementar uma medida de segurança para prevenir que um servidor SDS se torne primário em caso de falsa falha do primário. Note-se que habitualmente isto é prevenido com recurso ao I/O fencing, mas se essa funcionalidade não estiver disponível, esta pode ser outra forma de evitar ficar com dois servidores primários
• Configuração de segurança para servidores em replicação
  Um novo parâmetro, S6_USE_REMOTE_SERVER_CFG define se o ficheiro indicado pelo parâmetro REMOTE_SERVER_CFG será também usado para conexões que utilizem a opção s=6 do SQLHOSTS (para replicação). Se o parâmetros estiver a 1 o referido ficheiro será utilizado, caso contrário o comportamento antigo será o escolhido e o ficheiro a usar será $INFORMIXDIR/etc/hosts.equiv
• Segurança
• Suporte ao Global Security Kit (GSKit)
  Um novo parâmetros, GSKIT_VERSION, pode ser usado para definir a versão do Global Security Kit que se pretende usar. O Informix 11.70.xC4 incluí a versão 8, mas pode trabalhar com a versão 7
• Utilizacão de um ficheiro para autenticar conexões de servidores numa rede segura
  Já mencionado atrás (S6_USE_REMOTE_SERVER_CFG)
• Dados Time Series
• IBM Informix TimeSeries Plug-in para Data Studio
  Este novo plugin permite interagir com dados TimeSeries a partir do Optim Data Studio e do Optim Developer Studio
• Apagar um intervalo de elementos e libertar páginas vazias de um time series
  Melhorias nos DELETEs em dados TimeSeries que permitem libertar espaço
• Agregar dados time series cruzando várias linhas
  Melhorias na forma como podemos agregar informação guardada em TimeSeries

Para além de trazer algumas novas funcionalidades, esta versão também corrige alguns bugs importantes em torno da funcionalidade de read ahead automático introduzido na versão 11.70.xC3. Fora isto, julgo que é importante salientar os seguintes pontos:

• O TimeSeries continua a receber muito do foco das últimas melhorias no Informix. Isto é expectável e desejável considerando as boas notícias sobre histórias de sucesso e o recente benchmark
• O novo health plugin do OAT. Não tive muito tempo ainda para o explorar, mas certamente faria algumas coisas de forma diferente, como por exemplo re-utilizar a configuração do ALARMPROGRAM para o envio de alarmes. Mas a criação deste plugin é uma óptima ideia e sendo um plugin pode ser alterado facilmente para se ajustar às nossas necessidades
  
• A inclusão do OAT dentro do Client SDK é um bom passo (do meu ponto de vista). Torna ainda mais fácil obter e instalar o OAT. Já o instalei em Windows aproveitando o upgrade de versão que fiz ao Client SDK
• A possibilidade de mudar dinamicamente mais parâmetros é excelente. Naturalmente que gostaria que todos os parâmetros da instância tivessem esta capacidade. Mas estamos cada vez mais perto. Os meus próximos favoritos seriam (e porquê):
• LOGBUFF, PHYSBUFF
  Depois de uma análise a uma instância, muitas vezes estes devem ser mudados.
  Imagino que torná-los dinâmicos não fosse muito difícil, pois existem vários buffers que são usados de forma circular. Assim, após um pedido de mudança, quando passa de um para outro podia aproveitar-se para fazer a alteração. O LOGBUFF seria mais difícil pela importância que tem na replicação (HDR)
  
• TBLSPACE_STATS
  Também aqui é uma boa práctica ter este parâmetro activo. Mas se for detectado que não está, ter de parar a instância para o mudar não é simpático
• SYSSBSPACENAME, SBSPACENAME
  Muitas vezes estes não são definidos, mas a decisão de usar novas funcionalidades torna-os necessários. Mais uma vez, obrigar a paragem não é simpático...
  
• CLEANERS
  Muitas vezes necessita ser alterado após uma análise à configuração e comportamento da instância
  
• SHMTOTAL
  Muitos clientes não o definem. E por vezes podemos ter de reduzir ou aumentar o valor (os novos sistemas operativos já permitem adicionar/remover memória dinamicamente). Naturalmente não seria possível baixar o valor abaixo da quantidade já em uso
  
• DD_*, DS_*, PC_*, PLCY_*, USRC_
  Mais candidatos habituais a mudança após uma análise de configuração. Estes seriam possivelmente dos mais complexos de tornar dinâmicos, mas se já se faz para a statement cache deveria ser possível fazer para estes também. Para além disso, uma funcionalidade para limpar uma das caches (ou pelo menos remover alguma entrada) seria útil em determinadas situações
• EXT_DIRECTIVES
  Alguns clientes descobrem que precisam de o usar, mas está desligado por default. E uma paragem nunca é desejável
  
• DBCREATE_PERMISSION
  Este é a meu ver dinâmico por natureza

Spanish Informix user group / Grupo Espanhol de utilizadores Informix

This article is written in English and Portuguese
Este artigo está escrito em Inglês e Português

English version:

I suppose this article should be written in Spanish but unfortunately I'm too limited for that. I recently noticed the appearance of the Spanish Informix User Group site. Accordingly to the information online it's recent and it will be launched in this Autumn with the presence of several Informix specialists.

If your country is Spain, or your native language is Spanish, and you use Informix (or just want to learn more about it) I think and hope that this will be a new reference. I've seen some signs of interest in Informix in my neighbor country and this is another one.

I wish all the best to the founders and I hope they'll be able to attract the users and serve the community. And for the Portuguese community, this can also be interesting. There's a large number of people here to whom the language will not be a big issue, so maybe we can also enjoy and take advantage of this initiative (even due to the geographic proximity)


Versão Portuguesa:

Suponho que este artigo deveria ser também escrito em Espanhol/Castelhano, mas infelizmente sou demasiado limitado para isso. Notei recentemente o aparecimento do site do Grupo Espanhol de Utilizadores Informix. Segundo a informação lá colocada é recente e será lançado neste Outono com a presença de vários especialistas Informix.

Se o seu país é a Espanha ou se o Espanhol é uma língua que domina, e usa Informix (ou apenas deseje aprender mais sobre ele) penso e espero que isto venha a ser uma nova referência. Tenho visto alguns sinais de interesse no Informix no meu país vizinho e este é mais um.

Desejo as maiores felicidades aos fundadores e espero que consigam atrair os utilizadores e servir a comunidade. Também para a comunidade Portuguesa isto pode ser interessante. Existe por cá muita gente para quem a língua não será um obstáculo e portanto poderão disfrutar e tirar partido desta iniciativa (até pela proximidade geográfica)

Get pending In Place ALTERs / Obter as tabelas com InPlace ALTERs pendentes

This article is written in English and Portuguese
Este artigo está escrito em Inglês e Português


English version:


Introduction

The topic of in place ALTERed tables has always been present in Informix. We say a table was ALTERed in place when an ALTER TABLE instruction resulted in a new table schema, but the table was not physically changed. This is very helpful on large and busy tables. It means you can do the ALTER TABLE quickly (instantaneously) and with minimum immediate impact on system resources. Internally Informix does a very simple thing from the user point of view, but that can be complex from the engine point of view: A new version of the table definition is created, and from there on, any instruction (DML) affecting the data will use the new version. The table's existing pages are left on the older version but each time we SELECT them, the engine converts the row(s) to the new format. Additionally if we update a row, the page where it is stored will be written in the new format.

For example, if we have a table with lots of data pages, and we add a column, this will do an in place ALTER. Certain types of ALTERs don't allow in place ALTERs. For example, if we change a CHAR(5) column to a SMALLINT, this will be a slow ALTER. This usually happens when the system cannot immediately guarantee that the existing data can be stored in the new representation.

When the engine decides that it can do an in place ALTER, we don't have the option to inhibit it. Meaning the ALTER table will be done as an in place ALTER, and if we want to force the physical changes, we must do what we usually call a dummy UPDATE: UPDATE tabname SET column = column;

Impacts of in place ALTERs

There are several impacts on having tables with in place ALTERs. The immediate one is that your updates will be slightly slower, since the whole page will be converted and written. Note that this does not necessarily means higher I/O load, since the page is the smallest unit written by the engine. In other words, even if your table does not have more than one version of it's definition, when we change a single row of data, a whole page (at least) will be written. But the other rows of the page don't need to be changed. And changing an older version can mean your data will not fit on one page after conversion. So there can be more I/O.
Another potential issue is that like with any other part of the engine, the mechanism of in place ALTER can have issues. This is an overrated aspect, but it's true we've seen problems that only affect tables with in place ALTERs.

But the real issue with in place ALTERs refers to upgrades and downgrades. Through versions there have been great controversy regarding the real impact of doing in-place upgrades (conversions) with tables with pending in-place ALTERs. I've searched through the migration guides for several versions (7.3, 9.2, 9.4, 10, 11.1, 11.5 and 11.7) and they're almost completely consistent: You can upgrade with pending in-place ALTERed tables, but you cannot revert. The only exception I've found was the manual for 9.4 which states that you must remove the pending in-place ALTERs to run the conversion (upgrade) successfully.

The reason for allowing upgrades but not downgrades is pretty simple and acceptable: Informix guarantees that version N+1 can handle all previous situations of in-place ALTERs done in version N or older. But, since each version may have added new situations where in-place ALTERs can be done, we can't risk porting a pending ALTER to an older version that may not be able to handle it. Let me remind you that an in-place ALTER requires the ability to convert from one row version to a newer one (which can have more columns, or different data types etc.)

At the time of this writing I could not verify if the exception in the migration guide of version 9.4 was justified or simply a documentation problem. But the fact is that most people assume they must complete the ALTER tables that were done in-place before converting. By this I mean that they must eliminate all the pages in older versions. A valid reason to do that is that in case you need to revert you don't have to waste time running the dummy updates. Typically, if you have any issue in the new version and need to revert, you'll want to do it as soon as possible. As such, eliminating the pending inplace ALTERs before you upgrade can save you precious time if you really need to revert. In any case, the migration guide for version 11.7 clearly states that you only need to remove the pending inplace ALTERs, if they were caused after the conversion to 11.7. Any previous one (which already exists in the old version) would not need to be eliminated.
As a side note, let me state that the only time I've done reversions was during a customer and partner workshop where we were demoing this functionality... I never had to do it on a real customer situation. In any case the other limitations for reversion can be real challenges, so the pending in-place ALTERs wouldn't be your biggest concern.

Finding in-place alters

Now that we've seen in which situations we should remove pending inplace ALTERs (tables with pages in older versions), we come to another issue that is frequently asked, and to which there are several answers. Most of them are controversial or badly explained which again raises a lot of confusion. The issue is: How do we find tables with pending inplace ALTERs?
There was a discussion in the IIUG forum near the end of 2010 that focused on this issue. As usual, there were three answers to this:

1. A quick way (based on SQL and SMI that tells us which tables suffered an inplace ALTER, but doesn't show which tables have pending inplace ALTERs. This means that unless you completely rebuild the table, just running dummy UPDATEs will not prevent the table from always appearing in the list generated by this method
2. A slow way that's based in the oncheck -pT output (this effectively tells you the number of pages in each existing version This method will give you just the tables with pending inplace ALTERs
3. A technical support tool that searches the tables metadata and can provide the answer pretty quickly. Only problem is that it's not generally available

And this was my motivation to do some research on this issue. After some information exchange with Andreas Legner from IBM technical support in Germany, I was able to create an SQL script that can find the tables with pending inplace alters. The script can give us the details about the number of pages in each version, it returns the database and table name, the version and number of pages it contains.

The great thing about this script is that it's fast (from a few seconds to a few minutes for very large databases) and it really shows you the current status. Contrary to what the option 1) above does, if you do the dummy updates on one table, that table will not show up if you run it again. One warning: If you test this, after the dummy update you need to force a checkpoint. The script goes through the partition headers (because the required info is stored there), and after the dummy updates the partition headers are written to disk only at checkpoint time.

The script comes in the form of a stored procedure and is based only on the sysmaster views. The script was tested with all the versions I could find (7.31, 9.3, 9.4, 10, 11.1, 11.5 and 11.70) and it worked without issues in all of them. So if you're upgrading an old system and want to make sure you clear any pending inplace altered tables this can be a great help.

The SQL containing the script is at the bottom of this article and I will not dig into it with great detail. The challenges I got in developing it were mainly to understand how the needed data was already present in sysmaster views and also on interpreting this data (the representation is different depending on the "endianess" of your platform. Again, for the first one the help from Andreas Legner was precious and for the second one a special thank you goes to Art Kagel. Both of them helped me to review the script and to fix some nasty bugs I had in my first attempts.

Usage

In order to use this procedure, you'll need to copy the script code below, paste it into dbaccess and run it against any of your instance databases. It will create a function called get_pending_ipa() that will return the following fields:

• Database
• Table name
• Partition name
• Object type (can be table, partition or partition main)
• Partition number
• Partition lockid (the partnum of the main partition for fragmented tables)
• Table structure version
• Number of pages remaining in this version

If you need to create this against a version 7 (pre-V9) you should change the header and footer as commented in the script.
To execute just run

execute function get_pending_ipa();
or
execute procedure get_pending_ipa();

Disclaimer

Although the script was tested as much as I could, please understand that is comes with no guarantee. Use at your one risk. Neither me nor my employer can be considered liable for any harm done by it (difficult to happen since it only SELECTs), or more important for bad decisions taken based on it's output. This is just the usual disclaimer. Naturally I've done my best to make sure it works. If you find any error in the script or if you have any suggestion, feel free to contact me.



Versão Portuguesa:

Introdução

O assunto das tabelas com inplace ALTERs (optei por não traduzir o termo) tem estado sempre presente no Informix. Dizemos que uma tabela tem um inplace ALTER quando uma instrução ALTER TABLE deu origem a uma nova definição (schema) de tabela, mas a mesma não foi fisicamente alterada. Isto é muito útil em tabelas grandes e/ou com muitos acessos. Permite que se faça um ALTER TABLE muito rápido (instantâneo) e com um impacto reduzido no consumo de recursos do sistema. Internamente o Informix faz algo muito simples do ponto de vista do utilizador mas que pode ser bastante complexo se visto pelo ângulo do motor: É criada uma nova definição da estrutura da tabela, e a partir desse momento qualquer instrução (DML) que afecte os dados usará essa nova versão. As páginas já existentes da tabela mantêm-se na versão antiga, mas sempre que façamos um SELECT o motor converte a linha(s) para o novo formato. Adicionalmente, se fizermos um UPDATE a página onde estiver guardado o registo(s) será convertida pelo motor para o novo formato.

Por exemplo, se tivermos uma tabela com muitas páginas de dados, e adicionar-mos uma coluna, isto será feito com um inplace ALTER. Mas alguns tipos de ALTER TABLE não permitem um inplace ALTER. Caso mudemos uma coluna de CHAR(5) para SMALLINT, isto será um slow ALTER. Habitualmente isto acontece se o sistema não puder garantir imediatamente que os dados existentes têm representação ou podem ser guardados no novo tipo de dados (no caso anterior o CHAR(5) pode ter caracteres não numéricos). Se o motor decide que pode fazer um inplace ALTER não temos forma de o inibir. Ou seja, a alteração será forçosamente feita com inplace ALTER e se desejarmos forçar a mudança física temos de fazer o que normalmente se designa de dummy UPDATE: UPDATE tabela SET coluna = coluna;

Impactos dos in place ALTERs

Existem vários impactos em ter tabelas com inplace ALTERs. O mais imediato é que os UPDATEs serão ligeiramente mais lentos, pois toda a página tem de ser convertida e escrita. Note-se que isto não implica maior carga de I/O, pois a página é a unidade de escrita mais pequena do motor. Por outras palavras, mesmo que a sua tabela não tenha mais que uma versão da sua definição, quando mudamos uma linha contida numa página, toda a página (pelo menos) será escrita. Mas as outras linhas da mesma página não são alteradas. E mudar de uma versão anterior da definição para a atual pode implicar que nem todas as linhas caibam na página depois de convertidas. Isso sim, pode implicar mais I/O.
Outro potencial problema é que como em qualquer outra área de código do motor, o mecanismo de inplace ALTER pode ter problemas ou erros. Este aspeto é muitas vezes sobrevalorizado, mas é um facto que já tivemos problemas que só aconteciam em tabelas com inplace ALTERs.

Mas o verdadeiro problema habitualmente associado com os inplace ALTERs diz respeito aos upgrades e downgrades. Através das versões tem existido grande controvérsia relativamente às verdadeiras implicações de se efetuarem conversões (ou upgrades inplace) de versão existindo tabelas com inplace ALTERs pendentes (por pendentes quer-se dizer que têm efetivamente páginas com mais que uma versão de definição ou schema da tabela). Procurei pelos guias de migração de várias versões (7.3, 9.2, 9.4, 10, 11.1, 11.5 e 11.7) e são quase absolutamente consistentes: Pode fazer-se o upgrade com inplace ALTERs pendentes mas não se pode fazer o inverso (regressão). A única exceção a esta regra está no manual da versão 9.4 que refere que os mesmos têm de ser removidos antes de se efectuar a conversão.

A razão para permitir conversões, mas não regredir é bastante simples e compreensível: O Informix garante que a versão N+1 consegue lidar com toas as possibilidades de inplace ALTERs da versão N ou anteriores. Mas dado que em cada versão podem ser adicionadas novas situações onde o motor consegue fazer um inplace ALTER, não podemos correr o risco de portar um inplace ALTER pendente para uma versão anterior que não sabe como lidar com ele. Deixe-me lembrar que um inplace ALTER obriga a que o motor consiga mapear os dados de um formato para outro (com mais colunas, ou tipos de dados diferentes etc.)

No momento da escrita deste artigo não consegui verificar se a exceção no guia de migração da versão 9.4 se pode justificar com um erro de documentação ou se tem outro fundamento. Mas o facto é que a maioria dos utilizadores assumem que têm de completar (ou eliminar) os inplace ALTERs pendentes antes das conversões (upgrades). Uma razão válida para este raciocínio é que caso seja necessário regredir para a versão original não se quererá perder tempo a executar os dummy updates. Ou seja, eliminar os inplace ALTERs pendentes, antes da conversão, pode poupar tempo precioso caso se verifique a necessidade de regredir. O manual da versão 11.7 vai um pouco mais longe e refere que só é necessário remover os inplace ALTERs pendentes, se os mesmos foram gerados já na versão 11.7

Qualquer um anterior (que já existisse na versão original) não necessitará de ser eliminado.
Como um aparte, permita-me que diga que a única vez que fiz regressões foi num workshop para parceiros e clientes com o objetivo de demonstrar a funcionalidade. Nunca tive a necessidade de efectuar isto numa situação real em clientes. Em qualquer caso existem várias limitações às regressões que podem constituir verdadeiros desafios, pelo que os inplace ALTERs não deveriam ser a maior preocupação.

Identificar inplace ALTERs pendentes

Agora que vimos em que situações devemos remover os inplace ALTERs pendentes, chegamos a outro tópico que é alvo frequente de perguntas e discussões. A questão é: Como identificamos as tabelas que possuem páginas de dados com várias versões da sua definição? Há várias respostas e regra geral são controvérsias ou mal explicadas o que levanta enormes confusões. Decorreu uma discussão sobre este tema mais uma vez em finais de 2010. Como é hábito foram dadas três respostas para o problema:

1. Uma forma rápida (baseada em SQL e tabelas SMI) que nos diz as tabelas que sofreram inplace ALTERs mas não permite saber se ainda estão pendentes (existem ainda páginas de dados com formato antigo). Isto significa que a menos que se refaça completamente a tabela, a mera execução dos dummy UPDATEs não impedirá a tabela de voltar a aparecer na lista gerada por este método.
2. Uma forma lenta, baseada no resultado do oncheck -pT. Isto efetivamente diz-nos quantas páginas de dados existem para cada formato da tabela. Este método permite realmente identificar os inplace ALTERs pendentes.
3. Uma ferramenta do suporte técnico que procura na metadata das tabelas e pode fornecer a resposta de forma rápida. O único problema é que não está disponível para os utilizadores em geral

E isto foi a minha motivação para efetuar alguma pesquisa sobre este tema. Após alguma troca de informação com o Andreas Legner do suporte técnico da IBM na Alemanha, consegui criar um script SQL que pode reportar as tabelas com inplace ALTERs pendentes. Este script consegue fornecer o número de páginas existentes em cada versão da definição da tabela. Retorna a base de dados, a tabela, a versão(ões) e quantas páginas contém.

O bom deste script é que é rápido (de uns segundos a poucos minutos para bases de dados muito grandes), e mostra a situação actual. Contrariamente à opção 1) acima, depois de fazermos os dummy UPDATEs numa tabela, essa mesma tabela não volta a aparecer no output do script. Apenas um aviso relativamente a isto: O script percorre o que chamamos de partition headers e estes só são escritos em disco durante um checkpoint. Assim, depois de correr os dummy UPDATEs devem forçar-se um checkpoint (ou esperar que ocorra um) antes de correr novamente o script.

O script traduz-se num procedimento SPL e baseia-se em informação disponível nas views da base de dados sysmaster. O script foi testeado em todas as versões que consegui encontrar (7.31, 9.3, 9.4, 10, 11.1, 11.5 e 11.7) e correru em todas sem problemas. Assim, se estiver a fazer uma conversão de um sistema antigo e quiser limpar todos os inplace ALTERs pendentes nessa instância, isto pode ser uma grande ajuda.

O script SQL contendo o procedimento está disponível no final deste artigo e não vou fazer uma explicação exaustiva do mesmo. Os desafios que enfrentei durante o desenvolvimento do procedimento foram principalmente entender se os os dados necessários estavam representados na base de dados sysmaster e também na interpretação desses dados (a representação dos dados é diferente conforme o "endianess" da plataforma). Mais uma vez, na primeira questão a ajuda do Andreas Legner foi preciosa e para a segunda questão tive a ajuda do Art Kagel a quem enviou um sincero agradecimento. Ambos me ajudaram a rever o procedimento e identificaram alguns bugs feios que tinha nas primeiras tentativas.

Utilização


Para usar esta função terá de copiar o código do script que se encontra no final do artigo, colá-lo num dbaccess (ou outra ferramenta) e executá-lo numa das bases de dados da sua instância. O script irá criar uma função chamada get_pending_ipa() que irá retornar os seguintes valores:

• Nome da base de dados
• Nome da tabela
• Nome da partição
• Tipo de objecto (pode ser table, partition ou partition main)
• Número da partição
• lockid da partição (o número da partição principal para tabelas fragmentadas)
• Versão da estrutura da tabela
• Número de páginas ainda existentes nesta versão

Se necessitar de criar a função numa versão 7 (pre-V9) deve alterar o cabeçalho e o final da função de acordo com o recomendado/comentado no código
Para executar basta dar a instrução:

execute function get_pending_ipa();
ou
execute procedure get_pending_ipa();

Exclusão de garantia

Apesar de o script ter sido testado tanto quanto pude, por favor assuma que o mesmo não é fornecido com qualquer tipo de garantia. Utilize-o por sua conta e risco. Nem eu nem o meu empregador poderão ser considerados responsáveis por qualquer mal ou prejuízo derivado do seu uso (difícil dado que apenas faz SELECTs), ou mais importante, por más decisões baseadas no seu output. Isto é apenas o normal termo de des-responsabilização. Naturalmente fiz o meu melhor para assegurar que o procedimento funciona bem e retorna resultados corretos. Qualquer problema que identifique no script ou sugestão de melhoria por favor contacte-me.





SQL script:



CREATE FUNCTION get_pending_ipa() RETURNING
        VARCHAR(128) as database, VARCHAR(128) as table, VARCHAR(128) as partition, VARCHAR(9) as obj_type,
        INTEGER as partnum, INTEGER as lockid, SMALLINT as version, INTEGER as npages
-- For version 7.x use this header instead:
--CREATE PROCEDURE get_pending_ipa() RETURNING VARCHAR(128), VARCHAR(128), VARCHAR(128), VARCHAR(9), INTEGER, INTEGER, SMALLINT, INTEGER;
-- Name: $RCSfile: get_pending_ipa.sql,v $
-- CVS file: $Source: /usr/local/cvs/stable/informix/queries/get_pending_ipa.sql,v $
-- CVS id: $Header: /usr/local/cvs/stable/informix/queries/get_pending_ipa.sql,v 1.5 2011/09/09 20:57:31 fnunes Exp $
-- Revision: $Revision: 1.5 $
-- Revised on: $Date: 2011/09/09 20:57:31 $
-- Revised by: $Author: fnunes $
-- Support: Fernando Nunes - domusonline@gmail.com
-- Licence: This script is licensed as GPL ( http://www.gnu.org/licenses/old-licenses/lgpl-2.0.html )
-- Variables holding the database,tabnames and partnum
DEFINE v_dbsname, v_old_dbsname LIKE sysmaster:systabnames.dbsname;
DEFINE v_tabname, v_partname, v_old_tabname LIKE sysmaster:systabnames.tabname;
DEFINE v_partnum, v_old_partnum LIKE sysmaster:syspaghdr.pg_partnum;
DEFINE v_lockid, v_old_lockid LIKE sysmaster:sysptnhdr.lockid;
DEFINE v_pg_next INTEGER;
DEFINE v_pg_partnum INTEGER;
DEFINE v_obj_type VARCHAR(9);
-- Variables holding the various table versions and respective number of pages pending to migrate
DEFINE v_version SMALLINT;
DEFINE v_pages INTEGER;
-- Hexadecimal representation of version and pending number of pages
DEFINE v_char_version CHAR(6);
DEFINE v_char_pages CHAR(10);
DEFINE v_aux_char CHAR(8);
-- Hexadecimal representation of the slot 6 data. Each 16 bytes will appear as a record that needs to be concatenated
DEFINE v_hexdata VARCHAR(128);
-- Variable to hold the sysmaster:syssltdat hexadecimal representation of each 16 bytes of the slot data
DEFINE v_slot_hexdata CHAR(40);
DEFINE v_aux VARCHAR(128);
DEFINE v_endian CHAR(6);
DEFINE v_offset SMALLINT;
DEFINE v_slotoff SMALLINT;
DEFINE v_dummy INTEGER;
-- In case we need to trace the function... Uncomment the following two lines
--SET DEBUG FILE TO "/tmp/get_pending_ipa.dbg";
--TRACE ON;
-- Now lets find out the Endianess ( http://en.wikipedia.org/wiki/Endianness ) of this platform
-- The data in sysmaster:syssltdat will be different because of possible byte swap
-- Read the first slot of the rootdbs TBLSpace tblspace (0x00100001)
-- The first 4 bytes hold the partition number (0x00100001)
SELECT
        s.hexdata[1,8]
INTO
        v_hexdata
FROM
        sysmaster:syssltdat s
WHERE
        s.partnum = '0x100001' AND
        s.pagenum = 1 AND
        s.slotnum = 1 AND
        s.slotoff = 0;
IF v_hexdata = '01001000'
THEN
        -- Byte swap order, so we're little Endian (Intel, Tru64....)
        LET v_endian = 'LITTLE';
ELSE
        IF v_hexdata = '00100001'
        THEN
                -- Just as we write it (no byte swap), so we're big Endian (Sparc, Power, Itanium...)
                LET v_endian = 'BIG';
        ELSE
                -- Just in case something weird (like a bug(!) or physical modification) happened
                RAISE EXCEPTION -746, 0, 'Invalid Endianess calculation... Check procedure code!!!';
        END IF
END IF
-- Flags to mark the beginning
LET v_hexdata = "-";
LET v_old_dbsname = "-";
LET v_old_tabname = "-";
-- The information we want for each version description will occupy this number of characters
-- in the sysmaster:syssltdat.hexdata notation (after removing spaces). The size depends on the engine version.
LET v_offset=DBINFO('version','major');
IF v_offset >= 10
THEN
        LET v_offset = 48;
ELSE
        LET v_offset = 40;
END IF
LET v_old_lockid = -1;
FOREACH
        -- This query will browse through all the instance partitions, excluding sysmaster database, and will look for
        -- any extended partition header (where partition header "next" field is not 0)
        -- the ABS(...) is just a trick to make partnums that are equal to lock id appear at the end
        SELECT
                t.dbsname, t.tabname, t1.tabname, t.partnum, p.pg_partnum, p.pg_next, h.lockid, ABS(h.lockid - h.partnum)
        INTO
                v_dbsname, v_partname ,v_tabname, v_partnum, v_pg_partnum, v_pg_next, v_lockid, v_dummy
        FROM
                sysmaster:systabnames t,
                sysmaster:syspaghdr p,
                sysmaster:sysptnhdr h,
                sysmaster:systabnames t1
        WHERE
                p.pg_partnum = sysmaster:partaddr(sysmaster:partdbsnum(t.partnum),1) AND
                p.pg_pagenum = sysmaster:partpagenum(t.partnum) AND
                t.dbsname NOT IN ('sysmaster') AND
                h.partnum = t.partnum AND
                t1.partnum = h.lockid AND
                p.pg_next != 0
        ORDER BY
                t.dbsname, t.tabname, 8 DESC, t.partnum
        IF v_lockid = v_partnum
        THEN
                IF v_lockid = v_old_lockid
                THEN
                        LET v_obj_type = "Part Main";
                ELSE
                        LET v_obj_type = "Table";
                END IF
        ELSE
                LET v_obj_type = "Part";
        END IF
      
        LET v_old_lockid = v_lockid;
        WHILE v_pg_next != 0
                -- Find if we're dealing with a fragmented table or not...
                -- While this extended partition page points to another one...
                -- Get all the slot 6 data (where the version metadata is stored - version, number of pages, descriptor page etc.
                FOREACH
                SELECT
                        REPLACE(s.hexdata, ' '), s.slotoff, p.pg_next
                INTO
                        v_slot_hexdata, v_slotoff, v_pg_next
                FROM
                        sysmaster:syspaghdr p,
                        sysmaster:syssltdat s
                WHERE
                        s.partnum = p.pg_partnum AND
                        s.pagenum = p.pg_pagenum AND
                        s.slotnum = 6 AND
                        p.pg_partnum = v_pg_partnum AND
                        p.pg_pagenum = v_pg_next
                IF ( v_dbsname != v_old_dbsname OR v_tabname != v_old_tabname OR v_partnum != v_old_partnum)
                THEN
                        LET v_old_dbsname = v_dbsname;
                        LET v_old_tabname = v_tabname;
                        LET v_old_partnum = v_partnum;
                        -- First iteraction for each table
                        LET v_hexdata = v_slot_hexdata;
                ELSE
                        -- Next iteractions for each table
                        LET v_hexdata = TRIM(v_hexdata) || v_slot_hexdata;
                        IF LENGTH(v_hexdata) >= v_offset
                        THEN
                                -- We already have enough data for a version within a table
                                -- Note that we probably have part of the next version description in v_hexdata
                                -- So we need to copy part of it, and keep the rest for next iteractions
                                LET v_aux=v_hexdata;
                                LET v_hexdata=SUBSTR(v_aux,v_offset+1,LENGTH(v_aux)-v_offset);
                      
                                -- Split the version and number of pending pages part...
                                LET v_char_version = v_aux[1,4];
                                LET v_char_pages = v_aux[9,16];
                                -- Create a usable hex number. Prefix it with '0x' and convert due to little endian if that's the case
                                IF v_endian = "BIG"
                                THEN
                                        LET v_char_version = '0x'||v_char_version;
                                        LET v_char_pages = '0x'||v_char_pages;
                                ELSE
                                        LET v_aux_char = v_char_version;
                                        LET v_char_version[5]=v_aux_char[1];
                                        LET v_char_version[6]=v_aux_char[2];
                                        LET v_char_version[4]=v_aux_char[4];
                                        LET v_char_version[3]=v_aux_char[3];
                                        LET v_char_version[2]='x';
                                        LET v_char_version[1]='0';
                                        LET v_aux_char = v_char_pages;
                                        LET v_char_pages[9]=v_aux_char[1];
                                        LET v_char_pages[10]=v_aux_char[2];
                                        LET v_char_pages[7]=v_aux_char[3];
                                        LET v_char_pages[8]=v_aux_char[4];
                                        LET v_char_pages[6]=v_aux_char[6];
                                        LET v_char_pages[5]=v_aux_char[5];
                                        LET v_char_pages[3]=v_aux_char[7];
                                        LET v_char_pages[4]=v_aux_char[8];
                                        LET v_char_pages[2]='x';
                                        LET v_char_pages[1]='0';
                                END IF
                                -- HEX into DEC (integer)
                                LET v_version = TRUNC(v_char_version + 0);
                                LET v_pages = TRUNC(v_char_pages + 0);
                                IF v_pages > 0
                                THEN
                                        -- This version has pending pages so show it...
                                        RETURN TRIM(v_dbsname), TRIM(v_tabname), TRIM(v_partname), TRIM(v_obj_type), v_partnum, v_lockid, v_version, v_pages WITH RESUME;
                                END IF
                        END IF
                END IF
                END FOREACH
                IF LENGTH(v_hexdata) >= v_offset
                THEN
                        -- If we still have data to process...
                        LET v_aux=v_hexdata;
      
                        LET v_char_version = v_aux[1,4];
                        LET v_char_pages = v_aux[9,16];
                        IF v_endian = "BIG"
                        THEN
                                LET v_char_version = '0x'||v_char_version;
                                LET v_char_pages = '0x'||v_char_pages;
                        ELSE
                                LET v_aux_char = v_char_version;
                                LET v_char_version[5]=v_aux_char[1];
                                LET v_char_version[6]=v_aux_char[2];
                                LET v_char_version[4]=v_aux_char[4];
                                LET v_char_version[3]=v_aux_char[3];
                                LET v_char_version[2]='x';
                                LET v_char_version[1]='0';
                                LET v_aux_char = v_char_pages;
                                LET v_char_pages[9]=v_aux_char[1];
                                LET v_char_pages[10]=v_aux_char[2];
                                LET v_char_pages[7]=v_aux_char[3];
                                LET v_char_pages[8]=v_aux_char[4];
                                LET v_char_pages[6]=v_aux_char[6];
                                LET v_char_pages[5]=v_aux_char[5];
                                LET v_char_pages[3]=v_aux_char[7];
                                LET v_char_pages[4]=v_aux_char[8];
                                LET v_char_pages[2]='x';
                                LET v_char_pages[1]='0';
                        END IF
                        -- HEX into DEC (integer)
                        LET v_version = TRUNC(v_char_version + 0);
                        LET v_pages = TRUNC(v_char_pages + 0);
                        IF v_pages > 0
                        THEN
                                -- This version has pending pages so show it...
                                RETURN TRIM(v_dbsname), TRIM(v_tabname), TRIM(v_partname), TRIM(v_obj_type), v_partnum, v_lockid, v_version, v_pages WITH RESUME;
                        END IF
                END IF
        END WHILE
END FOREACH;
END FUNCTION;
-- For version 7.x use this close statement instead:
--END PROCEDURE;