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

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

English version:

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

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

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

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

1. The cost (time and resources translate directly into money) it took to implement them are a waste until the day customers can use them and we can speak about them
2. Some features (specifically SQL compatibility functions) may be used by customers without them knowing they're not documented. As an hypothetical example consider the new QUARTER() function announced in 12.10.xC3. A customer that is used to use that function in other RDBMs may just write it in SQL queries. If the server accepts it, he won't noticed if by any chance it was not (yet) documented. And if it's buggy, nasty things could happen, because in theory the customer was using a "non-existent" function, that was accepted by the server.
3. Occasionally some of these functionalities are "leaked". I won't forget that I saw an ONCONFIG parameter in one of the sessions of IOD conference a few years ago, that was a very necessary feature for customers. I mentioned that to at least one customer and later I noticed it was not documented. After internal questioning the official position was "it didn't go through proper QA". Well... a bit late. Neither the slide had information that was supposed to be undocumented nor did I check that it was not documented. I simply tested and it worked!

So, my position about this is simple: If it's supposed to be used, it must be documented. If it's not supposed to be used, the engine MUST not accept it.  And in some cases I feel we're needing just a little bit more work (for QA) so that we can document those features and take "profit" (meaning allowing customers to use them) from all the investment put into it's creation.
Maybe the users and my colleagues that will be joining the IIUG 2014 conference in Miami want to include this topic in their discussions?

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

Versão Portuguesa:

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

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

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

Como nota final, gostaria de falar sobre algo que se sabe acontecer e este caso é um exemplo claro disso mesmo: temos implementado funcionalidades no motor que não se encontram documentadas. A razão para tal deverá ser que as mesmas não passaram (não necessariamente que falharam ou falhassem) pelos devidos testes de qualidade (QA), de forma a estarem prontas para uso genérico nos clientes. E isto parece-me razoável. Mas o meu "problema" é que algumas delas já foram implementadas há demasiado tempo. Tempo e recursos foram empregues para as criar, e mesmo depois de muito tempo os clientes ainda não podem tirar proveito desse esforço. Da minha humilde e pessoal (e é necessário reforçar o "pessoal" por motivos óbvios) perspectiva há vários potencias problemas que derivam destas situações:

1. Os custos (tempo e recursos traduzem-se directamente em dinheiro) que derivaram da implementação destas funcionalidades são um desperdício até ao dia em que os clientes as possam usar e que possamos falar delas
2. Algumas funcionalidades (especificamente funções de compatibilidade SQL) podem ser usadas pelos clientes, sei que eles saibam que não são documentadas. Como exemplo hipotético, consideremos a nova função QUARTER() introduzida na 12.10.xC3. Um cliente que esteja habituado a escrever SQL com essa função noutra base de dados, pode perfeitamente escrevê-la em Informix. Se o servidor a aceitar, o cliente não se irá aperceber se a função está ou não (ainda) documentada. E se a mesma estiver ainda imperfeita ou instável, coisas imprevisíveis podem acontecer, simplesmente porque um cliente usou algo "que não existe" mas que o servidor aceitou
3. Ocasionalmente, algumas destas funcionalidades "transparecem" para a comunidade. Não me vou esquecer de ter visto um parâmetro de $ONCONFIG numa sessão de uma conferência IOD há alguns anos atrás, que ativa uma funcionalidade bastante necessária aos clientes. Eu já a mencionei a pelo menos um cliente e só depois me apercebi que não estava documentada. Depois de indagar internamente entendi que a posição oficial era "não sofreu testes significativos de QA". Bom... um pouco tarde demais. Nem o diapositivo tinha informação de que não estava documentado, nem eu verifiquei isso. Apenas fiz alguns testes e funcionou!

Portanto, a minha posição sobre o tema é simples: Se é suposto ser usado tem de estar documentado. Se não é suposto ser usado o motor NÃO pode aceitar. E em alguns casos sinto que necessitamos um pedacinho mais de esforço (para QA), de forma a que possamos documentar estas funcionalidades e obter "lucro" (ou seja, permitir que os clientes as usem) de todo o investimento colocado na sua criação.
Talvez os utilizadores e colegas que vão estar presentes na conferência do IIUG 2014 em Miami queiram incluir este tópico nas suas discussões?

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