Execution plans on the client / Planos de execução nos clientes

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

English version

Introdution

Life is full of coincidences... Because IIUG announced the 2013 IIUG user's conference, to be held in Sand Diego on 21-25 April, I spent some time reviewing my 2010 presentation. The topic was "Use EXPLAIN_SQL with any Tool" and at the time I was proud of some of the achievements I reach. The basic idea was very simple: Use the function EXPLAIN_SQL() introduced in version 11.10 to obtain a query plan in XML format. Then use the engine XML capabilities to apply an XML transformation style sheet (XSLT) to finally get the plan in a manner that could be presented in any tool. Truth is, that although I think the idea is great, the fact that IBM never documented EXPLAIN_SQL properly, my lack of knowledge in XML technology (I know the basics, but I'm not efficient with it), and some technical issues, lead the idea to a stop... No progress in nearly 3 years. This triggered me to think about another approach... Because the basic problem still exists: It's not easy for a programmer with no access to the database server host machine to obtain the query plans.
But I was talking about coincidences... While I was thinking about this issue once again, I receive almost simultaneously two direct inquires about the use of EXPLAIN_SQL (from people who saw references to my presentation).
So, then what is this article about? Well... I think I finally managed to get a way to obtain the query plans on any client in an easy way, almost with no limitations or constraints... That's what I'm going to explain now

Basics

By default Informix writes the query plans to a file in the database server host's filesystem. The default file name is $HOME/sqexplain.out if the user asks for the plan. If it's the DBA that activates the plan generation then the default filename will be $HOME/sqexplain.out.SID where "SID" is the session ID.
The concept was created when most programmers and users were directly connected through a terminal window (Telnet or SSH) to the database server host. Currently this is not the case in many situations. And then you need some mechanism to move the file to the client machine, and open that file. That's technically a mess. And even if you set it up, it's awkward to work with it.
In version 11.10 IBM introduced a function called EXPLAIN_SQL() that allows a client tool to send the query and get back the query execution plan in XML. Then the tool should process this XML and present the query plan in some format (ideally in a graphic form). The problems are that the function inputs were never properly documented, the XML that it outputs is complex, and nearly no tools are able to do this (IBM Optim Data Studio can do it, but I'm not aware of any other tool which is able to implement this). What I explained in the IIUG presentation was a prototype on how to do that to get the query plan in the traditional text form result.
Another improvement introduced in version 10 was the ability to choose the filename. Besides the old SET EXPLAIN ON, version 10 also supported SET EXPLAIN FILE TO "path/name". This allowed for some custom tricks like having a shared filesystem between the server and the clients. But still this is far from a good solution.
Finally, version 10 also introduced the ability to SET EXPLAIN ON AVOID_EXECUTE which will generate the query plan but will not execute the SQL statement.

New solution

Given all the issues with EXPLAIN_SQL I tried to imagine a simple way to retrieve the file from the server and present it in the client. Version 11.70.FC6 introduced some new functions that allow us to read from a file, and this would allow a custom procedure to retrieve the file and return it as a LVARCHAR or CLOB. I implemented a prototype with this, but although it worked is was a bit complex and would require version 11.70.FC6. So I kept trying to reach a more generic solution that would work on most Informix versions. And I believe I got it.
It comes in the form of three or four procedures in SPL. The purpose of these procedures is to emulate the basic functionality of activating the explain, with or without executing, resetting the explain file, and obtain the file. The procedure are presented in the end of the article and I'm going to write a few comments about each of them:

• set_explain_on()
• Activates the explain output and resets the explain file. The explain filename is controlled by the DBA, not the user. So they can be put on a specific location
•  I declare two global variables (available through the user session). One for the explain file name and the other to remember is the user used AVOID_EXECUTE or not
• I create the explain file name (without PATH). Here I'm concatenating the username and it's session. This rule simulates the situation where a DBA runs onmode -Y. There are pros and cons about this. The good thing is that a user with different sessions can capture different explains. The bad thing is that the filename will be almost unique. So a method of cleaning them up should be implemented. It can be a scheduler task that calls a script or a crontab script... Just look for files where the SID does not exist on the instance
• Then I create the complete filename. Here I'm using /tmp as the file location but that's not a very good idea. It would be better to have a separate filesystem (or directory with quota). It needs to be written by all. Remember that you should be able to eliminate files on that directory to implement the cleaning script. Otherwise you could just use a single file name for each user. So the file would be re-used. Again, quotas are important to avoid that a user grabs all the space
• Then we clear the file using a SYSTEM command. This is a major difference from the SET EXPLAIN ON statement. This statement will append to the file. The reason why I clean it, is because when I retrieve the file, I send it to the client side. I don't process it to obtain just the last query.
• Finally I define the explain file and activate the explain (here without the AVOID_EXECUTE)
• This procedure would be the replacement for SET EXPLAIN ON
• set_explain_on_avoid_execute()
• Exactly the same thing but in this case the statement SET EXPLAIN ON AVOID EXECUTE is used. Same rules for file and file reset
• reset_explain()
• This is not really needed. It just resets the explain file (clears its content). In practice calling the set_explain_on() or set_explain_on_avoid_execute() again has the same effect
• Used just to clear the file. You need to previously have called set_explain_on() or set_explain_on_avoid_execute()
• This is not really need as calling one of the functions that starts explain has the same practical effect. But for clarity I decided to create it. It can be used between two query execution and explain file request to clear the explain file, so that on the second call the first query plan is not returned
• get_explain()
• This is the juicy part... It retrieves the file and returns it as a CLOB. In dbaccess it will not be very readable, but with GUI tools like Optim Data Studio, SQL Squirrel, AGS Server Studio etc. it works perfectly
• It uses the native Informix function FileToCLOB() to read the explain file and return a CLOB containing it
• Most tools I tested this on (Squirrel SQL, Server Studio...) will return this as a clickable result set. Once you click it, the explain will be showed in the tool in a separate window

Usage

As an example, we can try this on stores database with the following simple instructions (it assumes the functions were already created):

ids_117fc6@centaurus.onlinedomus.net:informix-> cat test_explain.sql
EXECUTE PROCEDURE set_explain_on();
SELECT * FROM customer WHERE customer_num = 101;
EXECUTE FUNCTION get_explain();
EXECUTE PROCEDURE reset_explain();
SELECT COUNT(*) FROM customer c, orders o WHERE o.customer_num = c.customer_num AND c.state = 'CA';
EXECUTE FUNCTION get_explain();
ids_117fc6@centaurus.onlinedomus.net:informix-> cat test_explain.sql
EXECUTE PROCEDURE set_explain_on();
SELECT * FROM customer WHERE customer_num = 101;
EXECUTE FUNCTION get_explain();
EXECUTE PROCEDURE reset_explain();
SELECT COUNT(*) FROM customer c, orders o WHERE o.customer_num = c.customer_num AND c.state = 'CA';
EXECUTE FUNCTION get_explain();
ids_117fc6@centaurus.onlinedomus.net:informix-> dbaccess stores test_explain.sql

Database selected.


Routine executed.




customer_num  101
fname         Ludwig
lname         Pauli
company       All Sports Supplies
address1      213 Erstwild Court
address2      
city          Sunnyvale
state         CA
zipcode       94086
phone         408-789-8075

1 row(s) retrieved.




explain  

QUERY: (OPTIMIZATION TIMESTAMP: 12-19-2012 01:12:59)
------
SELECT * FROM customer WHERE customer_num = 101

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) informix.customer: INDEX PATH

(1) Index Name: informix. 100_1
Index Keys: customer_num   (Serial, fragments: ALL)
Lower Index Filter: informix.customer.customer_num = 101 


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

Table map :
----------------------------
Internal name     Table name
----------------------------
t1                customer

type     table  rows_prod  est_rows  rows_scan  time       est_cost
-------------------------------------------------------------------
scan     t1     1          1         1          00:00.00   1       



1 row(s) retrieved.


Routine executed.



(count(*)) 

15

1 row(s) retrieved.




explain  

QUERY: (OPTIMIZATION TIMESTAMP: 12-19-2012 01:12:59)
------
SELECT COUNT(*) FROM customer c, orders o WHERE o.customer_num = c.customer_num AND c.state = 'CA'

Estimated Cost: 5
Estimated # of Rows Returned: 1

1) informix.c: SEQUENTIAL SCAN

Filters: informix.c.state = 'CA' 

2) informix.o: INDEX PATH

(1) Index Name: informix. 102_4
Index Keys: customer_num   (Key-Only)  (Serial, fragments: ALL)
Lower Index Filter: informix.o.customer_num = informix.c.customer_num 
NESTED LOOP JOIN


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

Table map :
----------------------------
Internal name     Table name
----------------------------
t1                c
t2                orders

type     table  rows_prod  est_rows  rows_scan  time       est_cost
-------------------------------------------------------------------
scan     t1     18         3         28         00:00.00   4       

type     table  rows_prod  est_rows  rows_scan  time       est_cost
-------------------------------------------------------------------
scan     t2     15         23        15         00:00.00   0       

type     rows_prod  est_rows  time       est_cost
-------------------------------------------------
nljoin   15         3         00:00.00   5       

type     rows_prod  est_rows  rows_cons  time
-------------------------------------------------
group    1          1         15         00:00.00



1 row(s) retrieved.


Database closed.

This was run in dbaccess. But in order to see the full potential you should try it on a GUI based tool like ServerStudio, Squirrel SQL, Aqua Data Studio, DBVisualizer etc.

Additional notes

My first feeling is that this article should not be necessary. It's about time that this problem had a "native" solution, although most people I found still like character based, local access using dbaccess. On the other hand, I always find it interesting to show how easy it is to solve some of the "traditional" issues.
This method raises some problems which are easy to solve, but if you think about using it, it's better to solve them in advance:

1. Where should we create these procedures and function? If you only have one database in your instance that's easy. But typically customers have several databases in a single instance. The answer is that you can create them in any database and call them referencing the external database (same logging assumed), or if you prefer you can create them in all the databases.
2. If the location of the explain files is to be a single location for all users, then it must be writable by all users. This may raise some issues, and you should avoid them. The issues that came to mind are:
1. A user with enough privileges could try to flood the explain file or create a new file in an attempt to fill the filesystem and cause some DoS attack.
2. Because the location has to be writable by all users, a user with resource privileges could create a procedure to remove all the files
3. A user can potentially obtain other user's query plan files.
4. You must be able to remove the files with an administrative user (like informix)
   
   The solution is to create an informix owned directory writable by anyone (this solves issue 4). You should configure quotas to avoid issue 1. If you're concerned about issue 2 and 3, then you can solve them using filesystem ACLs. And you can have some random factor for the filename... The user doesn't need to know the location of the files neither the file name... Of course, the user can get them from the global variables (assuming their names are "public") and it can retrieve the shared location from the procedure's code
   So if you want it bullet proof use quotas and ACLs. Another option would be to write the explain files in each user $HOME (like informix does by default), but that raises yet another potential issue. The argument for FileToCLOB must be a full pathname. So if all your user's $HOME is created in /home (or any other global location) this would not be a problem. The point is that you can't use the location "~user/" or "./
   Removing the files could be done in a script (as simple as removing all the files older than today), which could be called by the database scheduler, cron, or any other scheduler. You could also create a sysdbclose() procedure to clean up the explain file created
3. Because the explain file is cumulative, get_explain() will always retrieve the full explain history. reset_explain() can solve this. And we could of course replace FileToCLOB by a custom C code function that returns just the last query plan.
4. The FileToCLOB() function needs a smart BLOB space to be present and configured in SBSPACENAME $ONCONFIG parameter. If you miss this, an error 9810/12053 will be raised

Having all these in mind, I do think this is a very feasible solution for a very old Informix issue.
I would still like to see EXPLAIN_SQL being more documented and explored. The idea of transforming an XML containing the explain into whatever you prefer (explain using other databases format for example) really pleases me. But It's much more difficult to implement. This method is terribly simple and should work with any version equal or newer to 10 (so any currently fully supported version)

Versão Portuguesa

Introdução

A vida está cheia de coincidências... Devido ao anúncio que o IIUG fez da conferência de utilizadores de 2013 IIUG, que terá lugar em São Diego, de 21 a 25 de Abril, passei algum tempo a rever a minha apresentação de 2010. O tema era  "Use EXPLAIN_SQL with any Tool" e na altura estava bastante orgulhoso dos progressos que tinha alcançado. A ideia base era muito simples: Usar a função EXPLAIN_SQL introduzida na versão 11.10 para obter o plano de execução em formato XML. Depois usar as capacidades de XML do motor para aplicar uma transformação usando uma transformation style sheet (XSLT), para finalmente obter o plano numa forma que pudesse ser apresentado em qualquer ferramenta. A verdade é que, apesar de ainda achar que a ideia era boa, o facto de a IBM nunca ter documentado a função EXPLAIN_SQL com detalhe, a minha falta de conhecimento em tecnologia XML (sei o básico, mas não sou eficiente a lidar com a tecnologia), e mais alguns problemas técnicos levaram a ideia a um beco sem saída... Sem progresso em quase três anos.
Isto incentivou-me a pensar noutra abordagem... Porque o problema de fundo ainda existe: Não é fácil a um programador sem acesso à máquina da base de dados obter os planos de execução.
Mas estava a falar de coincidências... Enquanto pensava sobre este tema novamente, recebi quase em simultâneo duas questões diretas sobre o uso da função EXPLAIN_SQL (de pessoas que terão visto referências para a minha apresentação).
Portanto, sobre o que é este artigo? Bom... Julgo que finalmente criei uma maneira de obter o plano de execução no cliente, de uma forma fácil e praticamente sem limitações... É o que vou explicar de seguida.

Principios báasicos

Por omissão o Informix escreve os planos de execução em ficheiro no servidor de base de dados. O nome pré-definido do ficheiro é $HOME/sqexplain.out se o utilizador pedir para ser gerado o plano. Se for o DBA a ativar a geração dos planos de execução, usando o onmod -Y então o nome será $HOME/sqlexplain.out.SID onde "SID" será o session ID.
Esta forma de trabalhar foi pensada quando a maioria dos programadores e utilizadores estavam diretamente ligados através de uma consola (Telnet ou SSH) ao servidor de base de dados. Atualmente a situação já não é esta na maioria dos casos. E assim é necessário um mecanismo para mover o ficheiro para a máquina cliente, e depois o utilizador terá de o abrir. Tecnicamente é uma confusão e é um processo arcaico para se trabalhar.
 Na versão 11.10 a IBM introduziu uma função chamada EXPLAIN_SQL() que permite a uma ferramenta cliente enviar uma query e receber o plano de execução em formato XML. Depois a ferramenta tem de processar esse XML para apresentar o plano de execução numa forma legível (preferencialmente de forma gráfica). Os problemas são que os inputs da função nunca foram devidamente documentados,, o XML resultante é complexo, e praticamente nenhuma ferramenta é capaz de usar esta funcionalidade (o IBM Optim Data Studio implementa-a, mas não tenho conhecimento de mais nenhum caso). O que apresentei na sessão da conferência do IIUG foi um protótipo de como usar esta função para obter o plano de execução num formato de texto (que seria utilizável em qualquer ferramenta).
Outra melhoria, introduzida na versão 10 foi a possibilidade de escolher outro nome de ficheiro para escrever os planos de execução. Para além do tradicional SET EXPLAIN ON, a versão 10 introduziu o SET EXPLAIN FILE TO "caminho/ficheiro". Isto abriu a porta a alguns truques como ter um sistema de ficheiros partilhado entre o servidor de base de dados e os clientes. Mas mesmo assim ainda está longe de ser uma boa solução.
Por último a versão 10 também introduziu a possibilidade de executar SET EXPLAIN ON AVOID_EXECUTE, o que permite gerar o plano de execução sem efetivamente correr a query.

Nova solução

Dados todos os problemas com a função EXPLAIN_SQL(), eu tentei imaginar uma forma simples de obter o ficheiro do servidor para o cliente. E a versão 11.70.xC6 introduziu algumas novas funções que nos permitem ler de um ficheiro e apresentar o resultado no cliente. Isto permitiria criar uma função que lesse o ficheiro e retornasse um LVARCHAR ou CLOB. Cheguei a implementar um protótipo com estas funções, mas apesar de funcionar seria um pouco complexo e necessitava da versão 11.70.xC6. Por isso continuei a tentar obter uma solução mais genérica que funcionasse com a maioria das versões de Informix. E penso que consegui.
Aparece sob a forma de três ou quatro procedimentos em SPL. O objetivo destes procedimentos é emular a funcionalidade básica de ativar a escrita de planos de execução, com ou sem execução das queries, reinicialização do ficheiros de planos e obtenção desse mesmo ficheiro. O código destes procedimentos pode ser encontrado no final do artigo e vou comentar alguns pontos:

• set_explain_on()
• Ativa a escrita dos planos de execução e re-inicializa o ficheiro. O nome do ficheiro é controlado pelo DBA, não pelo utilizador. Podem assim ser colocados num local específico e controlado
• Declaro duas variáveis globais (disponíveis em toda a sessão do utilizador). Uma para o nome do ficheiro de planos e a outra para manter informação sobre o uso do AVOID_EXECUTE
• Defino o nome do ficheiro (sem caminho). Estou a concatenar o nome do utilizador com o número da sessão. Esta regra simula o comportamento obtido quando é o DBA a ativar a geração de planos. Há vantagens e desvantagens nesta abordagem. A vantagem é que um utilizador com mais que uma sessão pode obter informação independente de cada uma. A desvantagem é que o nome do ficheiro será praticamente único. Portanto haverá que criar um método de limpeza. Pode ser uma tarefa escalonada no próprio motor de base de dados (que chame um script) ou algo colocado no crontab... Bastará procurar por ficheiros cujo sufixo (SID) não tenha correspondência nas sessões de base de dados
• Depois defino o nome completo do ficheiro. Aqui estou a usar /tmp, mas isso não será boa ideia. Seria melhor ter um filesystem (ou pasta com quota). Necessita de estar aberto para escrita por todos os utilizadores. Lembre-se que deverá conseguir eliminar os ficheiros criados por qualquer utilizador para implementar o método de limpeza.
  Outra opção seria usar um único ficheiro para cada utilizador. Assim o ficheiro seria sempre re-utilizado. Mesmo assim o uso de quotas é importante para evitar que um utilizador esgote todo o espaço
• Depois o ficheiro é limpo recorrendo à instrução SYSTEM. Isto é uma grande diferença em relação à instrução SET EXPLAIN ON. Esta instrução adiciona ao conteúdo do ficheiro caso já exista. A razão porque o limpo é porque ao pedir o ficheiro, todo o seu conteúdo é enviado para o cliente. Não processo o conteúdo à procura apenas da última query
• Finalmente defino o nomes do ficheiro e ativo a escrita dos planos (aqui sem o AVOID_EXECUTE)
• Este procedimento seria o substituto da instrução SET EXPLAIN ON
• set_explain_on_avoid_execute()
• Exactamente igual ao de cima, mas aqui usando o SET EXPLAIN ON AVOID_EXECUTE. As mesmas regras para o ficheiro e a re-inicialização do mesmo
• reset_explain()
• Não será realmente necessário. Apenas re-inicializa o ficheiro onde são escritos os planos de execução. Na prática, chamar o set_explain_on() ou set_explain_on_avoide_execute() novamente tem o mesmo efeito. Mas decidi criá-lo por clareza. Pode ser usado entre execuções de queries diferentes para que na segunda chamada ao procedimneto que devolve o plan, vir apenas o da segunda query.
• Usado apenas para limpar o ficheiro. Terá de ter chamado antes o set_explain_on() ou o set_explain_on_avoid_execute()
• get_explain()
• É o ponto fulcral do mecanismo. Usa a função nativa do Informix FileToCLOB() para ler o ficheiro de planos e retornar um CLOB que o contém.
• Na maioria das ferramentas em que testei (Squirrel SQL, Server Studio....) o resultado é apresentado como uma célula onde se pode clicar. Depois de o fazer o plano de execução será mostrado numa janela à parte

Utilização

Como exemplo, podemos tentar usar isto na base de dados stores usando as seguintes instruções (pressupõe que as funções já tenham sido criadas):

 ids_117fc6@centaurus.onlinedomus.net:informix-> cat teste_explain.sql
EXECUTE PROCEDURE set_explain_on();
SELECT * FROM customer WHERE customer_num = 101;
EXECUTE FUNCTION get_explain();
EXECUTE PROCEDURE reset_explain();
SELECT COUNT(*) FROM customer c, orders o WHERE o.customer_num = c.customer_num AND c.state = 'CA';
EXECUTE FUNCTION get_explain();
ids_117fc6@centaurus.onlinedomus.net:informix-> cat test_explain.sql
EXECUTE PROCEDURE set_explain_on();
SELECT * FROM customer WHERE customer_num = 101;
EXECUTE FUNCTION get_explain();
EXECUTE PROCEDURE reset_explain();
SELECT COUNT(*) FROM customer c, orders o WHERE o.customer_num = c.customer_num AND c.state = 'CA';
EXECUTE FUNCTION get_explain();
ids_117fc6@centaurus.onlinedomus.net:informix-> dbaccess stores teste_explain.sql

Database selected.


Routine executed.




customer_num  101
fname         Ludwig
lname         Pauli
company       All Sports Supplies
address1      213 Erstwild Court
address2      
city          Sunnyvale
state         CA
zipcode       94086
phone         408-789-8075

1 row(s) retrieved.




explain  

QUERY: (OPTIMIZATION TIMESTAMP: 12-19-2012 01:12:59)
------
SELECT * FROM customer WHERE customer_num = 101

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) informix.customer: INDEX PATH

(1) Index Name: informix. 100_1
Index Keys: customer_num   (Serial, fragments: ALL)
Lower Index Filter: informix.customer.customer_num = 101 


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

Table map :
----------------------------
Internal name     Table name
----------------------------
t1                customer

type     table  rows_prod  est_rows  rows_scan  time       est_cost
-------------------------------------------------------------------
scan     t1     1          1         1          00:00.00   1       



1 row(s) retrieved.


Routine executed.



(count(*)) 

15

1 row(s) retrieved.




explain  

QUERY: (OPTIMIZATION TIMESTAMP: 12-19-2012 01:12:59)
------
SELECT COUNT(*) FROM customer c, orders o WHERE o.customer_num = c.customer_num AND c.state = 'CA'

Estimated Cost: 5
Estimated # of Rows Returned: 1

1) informix.c: SEQUENTIAL SCAN

Filters: informix.c.state = 'CA' 

2) informix.o: INDEX PATH

(1) Index Name: informix. 102_4
Index Keys: customer_num   (Key-Only)  (Serial, fragments: ALL)
Lower Index Filter: informix.o.customer_num = informix.c.customer_num 
NESTED LOOP JOIN


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

Table map :
----------------------------
Internal name     Table name
----------------------------
t1                c
t2                orders

type     table  rows_prod  est_rows  rows_scan  time       est_cost
-------------------------------------------------------------------
scan     t1     18         3         28         00:00.00   4       

type     table  rows_prod  est_rows  rows_scan  time       est_cost
-------------------------------------------------------------------
scan     t2     15         23        15         00:00.00   0       

type     rows_prod  est_rows  time       est_cost
-------------------------------------------------
nljoin   15         3         00:00.00   5       

type     rows_prod  est_rows  rows_cons  time
-------------------------------------------------
group    1          1         15         00:00.00



1 row(s) retrieved.


Database closed.

Isto foi executado no dbaccess. Mas para verificar todo o potencial, deve testar numa ferramenta com interface gráfica como ServerStudio, Squirrel SQL, Aqua Data Tudio, DBVisualizer etc.

Notas adicionais

Gostaria de afirmar que este artigo não deveria ser necessário. Já é tempo de que este problema tenha uma solução "nativa". Isto apesar de a maioria das pessoas que encontro ainda gostar do acesso local, baseado em caracter como o dbaccess. Por outro lado acho sempre interessante mostrar como é fácil resolver alguns destes "problemas tradicionais".
Este método levanta alguns problemas que são fáceis de resolver, mas se pensar usá-lo, é melhor resolvê-los antecipadamente:

1. Onde devemos criar os procedimentos e função? Se apenas tiver uma base de dados na sua instância será fácil. Mas tipicamente os clientes possuem mais que uma base de dados em cada instância. A resposta é que pode criá-los em qualquer base de dados e chamá-las referenciando a base de dados externa (assumindo que o modo de logging é o mesmo), ou se preferir pode criá-los em todas as bases de dados
2. Se a localização dos ficheiros for uma localização única para todos os utilizadores, então terá de ter permissões de escrita para todos eles. Isto pode leavantar alguns problemas, e deverá evitá-los. Os problemas que me ocorrem são:
1. Um utilizador com os privilégios adequados poderia tentar inundar o ficheiro de planos ou ir criando novos ficheiros numa tentativa de encher o filesystem
2. Porque a localização tem de permitir escrita por todos, um utilizador com privilégio resource poderá criar um procedimento para apagar os ficheiros de outros utilizadores
3. Um utilizador potencialmente pode obter ficheiros de outros utilizadores
   
4. Tem de poder remover os ficheiros com um utilizador de administração (como informix)
   
   A solução passa por criar uma pasta com dono informix, e permissões de escrita para todos os utilizadores. (resolve o problema 4). Deverá configurar quotas para evitar o problema 1. Se estiver preocupado com os assuntos 2 e 4, poderá resolvê-los usando ACLs de filesystem. E pode introduzir algum factor aleatório na construção do nome de ficheiro...  O utilizador não necessita de saber nem a localização dos ficheiros nem o nome dos ficheiros. Naturalmente o utilizador pode obter isto pelas variáveis globais (assumindo que os seus nomes são "publicos") e pode também saber a localização pela análise do código dos procedimentos.
   Portanto se quer algo à prova de bala tem de usar quostas e ACLs. Outra opção seria escrever os ficheiros em cada $HOME dos utilizadores (como o informix faz por omissão) mas isso levanta ainda outro problema: O argumento da função FileToCLOB tem de ser um caminho absoluto. Assim, se todos os utilizadores tiverem o $HOME definido para /home (ou qualquer outra localização) isto não seria um problema. O ponto é que não pode usar como localização não pode ser "~user" ou "./"
   Remover os ficheiros pode ser feito num script (tão simples como remover os ficheiros com mais que um dia), que poderá ser chamado pelo scheduler da base de dados, cron ou qualquer outro scheduler. Poderiam também criar um procedimento sysdbclose() para limpar o ficheiro de planos que tenha sido criado
   
   
3. Porque o ficheiro de planos é cumulativo, get_explain() irá sempre retornar toda a história. reset_explain() pode resolver isto. E pode claro substituir FileToCLOB() por uma função em C que retorne apenas o último plano contido no ficheiro
4. A função FileToCLOB requer que o sistema tenha um smart BLOB space, e que esteja configurado no parâmetro SBSPACENAMEno ficheiro $ONCONFIG . Caso tal não aconteça será retornado um erro 9810/12053

Tendo tudo isto em consideração acredito que esta seja uma solução fácil para um antigo problema com o Informix
Ainda gostaria de ver o EXPLAIN_SQL bem documentado e explorado. A ideia de transformar o XML contendo o plano de execução no que quer que preferiss (plano usando a nomenclatura ou aspecto de outras bases de dados por exemplo) agrada-me verdadeiramente. Mas é muito mais difícil de implementar. Ao contrário este método é muito simples e deverá funcionar com qualquer versão dos servidor a partir da 10 (portanto qualquer versão actualmente suportada)

The code/O código

1  CREATE PROCEDURE set_explain_on()
2  DEFINE GLOBAL explain_file_name VARCHAR(255) DEFAULT NULL;
3  DEFINE GLOBAL explain_execute BOOLEAN DEFAULT NULL;
4  DEFINE exp_file, sys_cmd VARCHAR(255);
5       LET explain_file_name = USER||'.'||DBINFO('sessionid');
6       LET exp_file = '/tmp/'||explain_file_name;
7       LET sys_cmd='cat /dev/null > '||exp_file;
8       SYSTEM(sys_cmd);
9       SET EXPLAIN FILE TO exp_file;
10      SET EXPLAIN ON;
11      LET explain_execute = 't';
12  END PROCEDURE;

1  CREATE PROCEDURE set_explain_on_avoid_execute()
2  DEFINE GLOBAL explain_file_name VARCHAR(255) DEFAULT NULL;
3  DEFINE GLOBAL explain_execute BOOLEAN DEFAULT NULL;
4  DEFINE exp_file, sys_cmd VARCHAR(255);
5    LET explain_file_name = USER||'.'||DBINFO('sessionid');
6    LET exp_file = '/tmp/'||explain_file_name;
7    LET sys_cmd='cat /dev/null > '||exp_file;
8    SYSTEM(sys_cmd);
9    SET EXPLAIN FILE TO exp_file;
10   SET EXPLAIN ON AVOID_EXECUTE;
11   LET explain_execute = 'f';
12 END PROCEDURE;

1  CREATE PROCEDURE reset_explain()
2  DEFINE GLOBAL explain_file_name VARCHAR(255) DEFAULT NULL;
3  DEFINE GLOBAL explain_execute BOOLEAN DEFAULT NULL;
4  DEFINE exp_file,sys_cmd VARCHAR(255);
5  IF explain_file_name IS NOT NULL
6  THEN
7    LET exp_file = '/tmp/'||explain_file_name;
8    SET EXPLAIN OFF;
9    LET sys_cmd='cat /dev/null > '||exp_file;
10   SYSTEM(sys_cmd);
11   SET EXPLAIN FILE TO exp_file;
12   IF explain_execute = 't'
13   THEN
14     SET EXPLAIN ON;
15   ELSE
16     IF explain_execute = 'f'
17     THEN
18       SET EXPLAIN ON AVOID_EXECUTE;
19     ELSE
20       RAISE EXCEPTION -746, "Execute option of set explain is not defined!";
21     END IF;
22   END IF;
23 ELSE
24   RAISE EXCEPTION -746, "Explain file is not set!";
25 END IF;
26 END PROCEDURE;

1  CREATE PROCEDURE get_explain() RETURNING CLOB AS explain;
2  DEFINE GLOBAL explain_file_name VARCHAR(255) DEFAULT NULL;
3  DEFINE exp_file VARCHAR(255);
4  DEFINE v_ret CLOB;
5
6  IF explain_file_name IS NOT NULL
7  THEN
8    LET exp_file = '/tmp/'||explain_file_name;
9    LET v_ret = FILETOCLOB(exp_file,'server');
10   RETURN v_ret;
11 END IF;
12 END PROCEDURE;


11.7.FC7 is out... / versão 11.7.FC7...

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

English version

I usually post an article about each Informix fixpack. This time we have 11.70.FC7, but this is a different situation. The latest fixpack is just a security release. You may have noticed the official announcements from IBM (https://www.ibm.com/support/docview.wss?uid=swg21618994) and CVE
(http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2012-4857).
The problem detected relates to a stack overflow issue. Note that on October another issue also caused a warning recommending the upgrade to 11.70.FC5.
So now, to be safe you should consider moving to 11.70.FC7. If you`re still using 11.50 than you can obtain a special build on top of 11.50.FC9W2. These special fixes are available on Fix Central

Versão Portuguesa

Habitualmente publico um artigo sobre cada fixpack do Informix. Desta feita temos a 11.70.FC7, mas esta é uma situação diferente. Este último fixpack é apenas uma atualização de segurança. Poderá ter verificado os anúncios da IBM (https://www.ibm.com/support/docview.wss?uid=swg21618994) e CVE (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2012-4857).
O problema detetado é do tipo stack overflow. Note que em Outubro um outro problema também causou um alerta e recomendava a atualização para a versão 11.70.FC5.
Agora,  para estar seguro deve considerar atualizar para a 11.70.FC7. Se ainda estiver a usar a 11.50 então pode obter um build especial sobre a 11.50.FC9W2. Estes builds estão disponíveis no Fix Central

SSL certificates expiration / expiração de certificados SSL

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

English version:

Introduction

In a recent customer engagement I was questioned about a topic that to be honest I never had to work with before. It relates to SSL connections to Informix and how should we handle the certificates, in particular the fact that they can/will expire... And what happens then.
Configuring SSL connections is a relatively simple process. It's well explained in the security manual and it's not my objective to explain it in detail here. Nevertheless, for the readers who haven't done it before here's a quick reference (if you really want to do it, read the manual!). Note that this example uses a self signed certificate, so this will be also copied to the client(s):

• Server configuration
1. Create a key database where the server certificates will be stored. This can be done with a command included in the IBM Global Security Kit which is bundled in the engine package:
   
   gsk8capicmd -keydb -create -db $INFORMIXDIR/ssl/$SERVER_NAME.kdb -pw $MYPASSWORD -type cms -stash
   
   The command above uses two variables that you need to set or replace by real values:
   SERVER_NAME is the value of the DBSERVERNAME parameter in $INFORMIXDIR/etc/$ONCONFIG
   MYPASSWORD is the password used to encrypt/access the keystore.
   
   We're creating the file of type "cms" in $INFORMIXDIR/ssl with the name of the engine and we're requesting the creation of a stash file which will allow the engine to get the password and access the keystore without having to specify the password again
   Naturally this file is security sensitive.
2. Then we need to create a certificate file and store it in the keystore we created above. For testing purposes we're going to create a self sign certificate. Proper setups would use a CA (Certification Authority) to sign the certificates. The CA could be external or one implemented in your company (depending on the needs):
   
   gsk8capicmd -cert -create -db $INFORMIXDIR/ssl/$SERVER_NAME.kdb -pw ${MYPASSWORD} -label ${SERVER_NAME}_label -dn "CN=`hostname`" -size ${KEYSIZE} -default_cert yes -expire ${EXPIRE}
   
   The variables that need to be set or replaced by values are:
   
   SERVER_NAME and MYPASSWORD as explain above
   KEYSIZE to specify the key length (e.g 1024)
   EXPIRE to define the number of days that the certificate will be valid. Default should be 365 (one year)
   
   Note that we're defining a "label" for this certificate (${SERVER_NAME}_label) so that the engine can define which certificate it will use from the ones stored in the keystore. This label will be used in the engine configuration (see next step)
3. Configure the label to define which certificate will be used by the engine. This is done by setting the SSL_KEYSTORE_LABEL:
   
   SSL_KEYSTORE_LABEL panther_label
4. Create an entry in $INFORMIXSQLHOSTS for SSL (e.g panther_ssl) using the onsocssl protocol
5. Configure one NETTYPE entry in $INFORMIXDIR/etc/$ONCONFIG:
   
   NETTYPE        onsocssl,1,20,NET
6. Configure one (or more) virtual processor for SSL:
   
   VPCLASS ssl,num=1,noage
7. Make sure that the new SSL port is configured in DBSERVERALIAS or DBSERVERNAME
8. Extract the certificate so that it can be imported in the clients database (for self signed certificates):
   
   gsk8capicmd -cert -extract -db $INFORMIXDIR/ssl/${SERVER_NAME}.kdb -format ascii -label ${SERVER_NAME}_label -pw ${MYPASSWORD} -target ${SERVER_NAME}_label.cert
•  On the client side
1. Create the keystore on the client side:
   
   gsk8capicmd -keydb -create -db $INFORMIXDIR/etc/clientdb.kdb -pw ${MYPASSWORD} -type cms -stash
2. Import the self signed certificate that we extracted in the server side last step:
   
   gsk8capicmd -cert -add -db $INFORMIXDIR/etc/clientdb.kdb -pw ${MYPASSWORD} -label ${SERVER_NAME}_label -file ${SERVER_NAME}_label.cert -format ascii
   
   note that the PATH specified after "-file" needs to point to the file extracted from the server keystore. You may need to FTP/SCP the file or use another copy method.
   In a proper setup you would import the CA certificate used to sign the certificates used in the servers to which the client wants to connect to
3. Configure the client $INFORMIXSQLHOSTS file with the appropriate entry to define the SSL enabled port
4. Create a file named $INFORMIXDIR/etc/conssl.cfg with the following content:
   
   SSL_KEYSTORE_FILE   /usr/informix/client_INFORMIXDIR/etc/clientdb.kdb   # Keystore file
   SSL_KEYSTORE_STH    /usr/informix/client_INFORMIXDIR/etc/clientdb.sth   # Keystore stash file
   
   This will tell the clients where the client side keystore and stash file are.
5. Make sure the keystore and stash file have appropriate filesystem permissions (don't allow access to non-authorized people)

The problem

Once you have an SSL enabled environment you should be able to connect your clients (to which you need to make the certificate available). But then, you have the conditions to hit the problem. As you may have noticed, the certificates have an lifetime period. They're not valid before a date and after another date. By default the initial date is the time when you create them and the valid period is typically one year. What happens after that period? The customer already found that... No new connections will be possible (which is understandable since the certificate is expired), but the problem is that you cannot re-validate the certificate with the engine online. If you try to connect using an expired certificate you'll get the following error in the engine log:

15:27:18  listener-thread: err = -28014: oserr = 0: errstr = GSK_ERROR_BAD_DATE: Secure Sockets Layer error: GSK_ERROR_BAD_DATE.

The error message is:

-28014    Secure Sockets Layer error.

An error occurred during a Secure Socket Layer operation. For more information,

refer to the accompanying IBM Global Security Kit (GSKit) error message.

So, the problem summary is how to avoid getting into a situation where you have a running engine with an expired certificate.

How to avoid it

In generic terms, a way to avoid this is to keep track of the certificates expiration dates. Before they expire we should create new ones and schedule a stop of the server to make the change. Now, if you have a regular maintenance window this will be easier. But if you have a 24x7 system the stop becomes an issue. I'll split the solution into three parts:

1. You should create a certificate that is secure enough for a specific time period. This period should be enough to accommodate enough time so that you can wait for a stop caused by another reason (OS maintenance/upgrade, a crash, a monthly/quarter/yearly stop). This will probably mean that you need to create a certificate that will last longer than the period you'd like to change it. Let's assume you establish as a policy that you'd like to exchange the certificates every 6 months. But you're only allowed to do a schedule stop once per year... So, to be safe you may want to create the certificate with 18 months before expiration. You should consider the correct key size to guarantee the security of the certificate for the interval desired. The following site may help with that:
   
   http://www.keylength.com/en/
   
2. You need to setup everything that it's possible to do without stopping the engine, before the stop. The idea is to make the stop as short and simple as possible. So you can create a new certificate and store it in the same keystore, as long as you use a different label. This can be done before the stop while everything is working. This way, the only thing you'll have to do during the stop is to change the SSL_KEYSTORE_LABEL parameter.
3. You must create a tracking system that will alert you with enough time, before the certificate expires. You have two ways to do this: You store each certificate ID (label for example) and it's expiration date in a database, and periodically run  a process that will alert you if any of the certificates will expire in a pre-defined period of time (e.g in the next 3 - choose your number -  months). The other option is to create an alarm that checks each server's certificate and generates some sort of alert (email, monitoring system etc.)

In order to help with point 3 above, I have a little script that can be changed/adapted to your particular needs:

1    #!/bin/sh

2   

3    SERVER_NAME=`onstat -c | grep DBSERVERNAME | grep -v "^[     ]*#" | awk '{print $2}'`

4    SERVER_LABEL=`onstat -c | grep SSL_KEYSTORE_LABEL | grep -v "^[     ]*#" | awk '{print $2}'`

5   

6    MYDATE=`gsk8capicmd -cert -details -locale en -db $INFORMIXDIR/ssl/$SERVER_NAME.kdb -stashed -label ${SERVER_LABEL} | grep After | awk '{print $4 " " $5 " " $6}'`

7   

8    NUM_DAYS=`dbaccess sysmaster <<EOF 2>/dev/null| grep "DAYS:" | awk -F':' '{print $2+0}'

9    select "DAYS:"||extend(TO_DATE("$MYDATE", "%B %d, %Y"), YEAR TO DAY) - extend(TODAY,YEAR TO DAY) FROM sysmaster:sysdual;

10    EOF`

11   

12   

13    if [ $NUM_DAYS -gt 0 ]

14    then

15        echo "Certificate is still valid for $NUM_DAYS days"

16    else

17        echo "Certificate has expired!"

18    fi

Let's leave aside the SHELL tricks and particularities. What the script it doing is pretty simple:

1. Collects the DBSERVERNAME and SSL_KEYSTORE_LABLE $ONCONFIG parameters
2. Calls gdk8capicmd command to display the certificate details. It extracts the line "Not After :". Note that I use the "-locale en" to define the date format.
3. It uses Informix with an inline SQL script to calculate the number of days that the certificate still has before expiring

Versão Portuguesa:

Introducão

Durante uma visita recente a um cliente fui questionado sobre um tema com o qual muito honestamente nunca tinha lidado. Está relacionado com ligações SSL ao Informix e como deveremos gerir os certificados, em particular o facto de que podem expirar... e o que acontece então.
Configurar ligações SSL é um processo relativamente simples. Está bem explicado no manual de segurança e não é meu objetivo explicar detalhadamente aqui como o fazer. Em todo o caso, e pensando em todos os leitores que nunca o fizeram, aqui fica um guia rápido (se realmente o quiser fazer não deixe de seguir o manual). Note que este exemplo utiliza certificados auto-assinados, portanto estes são também copiados para os clientes:

• Configuração do servidor
1. Criar uma base de dados de chaves onde os certificados serão guardados. Isto pode ser feito com um comando incluído no IBM Global Security Kit, que é fornecido com o pacote do motor:
   
   gsk8capicmd -keydb -create -db $INFORMIXDIR/ssl/$NOME_SERVIDOR.kdb -pw $MINHA_PASSWORD -type cms -stash
   
   O comando acima utiliza duas variáveis que necessita de definir ou substituir pelo seu valor real:
   NOME_SERVIDOR é o valor do parâmetro DBSERVERNAME do $INFORMIXDIR/etc/$ONCONFIG
   MINHA_PASSWORD é a palavra chave usada para encriptar e aceder à base de dados de chaves/certificados (keystore)
   Estamos a criar o ficheiro do tipo "cms" em $INFORMIXDIR/ssl com o nome do motor e pedimos a criação de um ficheiro stash que permitirá ao motor obter a password para aceder à keystore sem que tenhamos de especificar a password novamente. Naturalmente este ficheiro é sensível do ponto de vista da segurança.
2. Depois necessitamos de criar um ficheiro de certificado e guardá-lo na keystore que criámos. Para efeitos de teste criamos um certificado auto-assinado. Configurações reais provavelmente usariam uma CA (Certificate Authority) para assinar os certificados.
   A CA poderia ser external ou uma implementada na empresa (dependendo das necessidades)
   
   gsk8capicmd -cert -create -db $INFORMIXDIR/ssl/${NOME_SERVIDOR}.kdb -pw ${MINHA_PASSWORD} -label ${NOME_SERVIDOR}_label -dn "CN=`hostname`" -size ${TAMANHO_CHAVE} -default_cert yes -expire ${EXPIRA}
   
   As variáveis que necessitam de ser substituidas por valores são:
   
   NOME_SERVIDOR and MINHA_PASSWORD como explicado acima
   TAMANHO_CHAVE para especificar o tamanho da chave (ex: 1024)
   EXPIRA para definir o número de dias de duração do certificado. Por omissão deverá ser 365 (um ano)
   Note que estamos a definir uma "label" ou etiqueta para este certificado ( ${NOME_SERVIDOR}_label) para que o motor possa identificar qual o certificado que usará dos que estão guardados na keystore.
   Esta etiqueta será usada na configuração do motor (próximo passo)
   
3. Configurar a etiqueta que define qual o certificado a usar pelo motor. Isto é feito definindo o parâmetro SSL_KEYSTORE_LABEL:
   
   SSL_KEYSTORE_LABEL panther_label
4. Criar uma entrada no $INFORMIXSQLHOSTS para SSL (ex: panther_ssl) usando o protocolo onsocssl
5. Configurar uma entrada NETTYPE no $INFORMIXDIR/etc/$ONCONFIG:
   NETTYPE socssl
   
   NETTYPE        onsocssl,1,20,NET
   
6. Configurar um (ou mais) processadores virtuais para SSL:
   
   VPCLASS ssl,num=1,noage
7. Garantir que a nova porta SSL está configurada como DBSERVERNAME ou uma entrada no DBSERVERALIAS
8. Extrair o certificado de forma que possa ser importado para os repositórios dos clientes (para certificados auto-assinados):
   
   gsk8capicmd -cert -extract -db $INFORMIXDIR/ssl/${NOME_SERVIDOR}.kdb -format ascii -label ${NOME_SERVIDOR}_label -pw ${MINHA_PASSWORD} -target ${NOME_SERVIDOR}_label.cert
• Do lado do cliente
  
1. Criar a keystore do lado do cleinte:
   
   gsk8capicmd -keydb -create -db $INFORMIXDIR/etc/clientdb.kdb -pw ${MINHA_PASSWORD} -type cms -stash
2. Importar o certificado auto-assinado que extraímos no último passo do lado do servidor:
   
   gsk8capicmd -cert -add -db $INFORMIXDIR/etc/clientdb.kdb -pw ${MINHA_PASSWORD} -label ${SERVER_NAME}_label -file ${NOME_SERVIDOR}_label.cert -format ascii
   
   note que o caminho especificado depois da opção "-file" tem de apontar para o ficheiro extraído da keystore do servidor. Poderá ter de o copiar por FTP/SCP ou usando qualquer outro método de transferência.
3. Numa configuração mais rigorosa deveria importar o certificado da CA usada para assinar os certificados usados nos servidores a que o cliente se pretenda ligar
4. Configurar o ficheiro $INFORMIXSQLHOSTS do cliente com a nova entrada que usa o porto SSL
5. Criar um ficheiro com o nome $INFORMIXDIR/etc/conssl.cfg com o seguinte conteúdo:
   
   SSL_KEYSTORE_FILE   /usr/informix/client_INFORMIXDIR/etc/clientdb.kdb   # Keystore file
   SSL_KEYSTORE_STH    /usr/informix/client_INFORMIXDIR/etc/clientdb.sth   # Keystore stash file
   
   Isto indica aos clientes qual a keystore e o ficheiro de stash
6. Garanta que o ficheiro da keystore e o stash têm as permissões adequadas (não permitir o acesso a pessoas não autorizadas)

O problema

Assim que tiver um ambiente com SSL ativado, deverá conseguir conectar os clientes (aos quais tem de disponibilizar o certificado). E então estará nas condições para encontrar o problema. Como terá notado, os certificados têm um período de vida. Não são válidos antes de uma data e não são válidos depois de outra data. Por omissão a data inicial é o momento em que cria o certificado e a duração típica é um ano. O que acontece findo esse período? O cliente já tinha descoberto... Novas conexões não serão estabelecidas (compreensível dado que o certificado está expirado), mas o problema é que não o pode revalidar com o motor a funcionar. Se tentar conectar-se com um certificado expirado terá a seguinte mensagem no online.log:

15:27:18  listener-thread: err = -28014: oserr = 0: errstr = GSK_ERROR_BAD_DATE: Secure Sockets Layer error: GSK_ERROR_BAD_DATE.

A mensagem de erro correspondente é:

-28014    Secure Sockets Layer error.

An error occurred during a Secure Socket Layer operation. For more information,

refer to the accompanying IBM Global Security Kit (GSKit) error message.

Em resumo, o problema é como evitar ficar numa situação em que se tenha um motor a funcionar com um certificado expirado

Como evitar

Em termos gerais, uma forma de evitar isto será manter um registo das datas de expiração. Antes de os certificados expirarem devemos criar novos e agendar uma paragem do servidor para efectuar a mudança. Se tiver uma janela de manutenção regular, isto será fácil. Mas se o seu sistema é 24x7 a paragem torna-se um problema. Vou partir a solução em três partes:

1. Deve criar um certificado se seja suficientemente seguro por um determinado tempo. Este período deve ser suficiente para acomodar atrasos no agendamento da paragem do servidor de forma que possa esperar por outras razões de paragem (manutenção de SO, um crash, uma paragem mensal/trimestral ou anual). Isto provavelmente significa que terá de criar um certificado que dure mais tempo que aquele com que o deseja renovar. Por exemplo, se quiser mudar os certificados de seis em seis meses, mas só tem autorização para fazer uma paragem anual... Por isso, para ficar descansado poderá criar os certificados com um tempo de vida de 18 meses. Deve considerar o tamanho da chave necessário para garantir a segurança do certificado para o período desejado. O seguinte site poderá ajudar nisso:
   
   http://www.keylength.com/en/
   
   
2. Necessita de preparar tudo o que seja possível sem parar o motor, antes da paragem programada. A ideia é que a paragem seja tão breve e simples quanto possível.. Isto incluí a criação do novo certificado e guardá-lo na mesma keystore, desde que utilize outra etiqueta (label). Isto pode ser feito antes da paragem enquanto o motor está a funcionar. Desta forma a única coisa que será necessário fazer durante a paragem é mudar o parâmetro SSL_KEYSTORE_LABEL.
3. Deve criar um sistema que mantenha o rasto e que o alerte com antecedência sempre que um certificado estiver para expirar. Tem duas formas de o fazer: Pode guardar um identificador de certificado (por exemplo a etiqueta - label -) e a sua data de expiração numa base de dados, e periodicamente executar um processo que o alerte se algum dos certificados expirar num futuro próximo (ex: 3 meses). A outra opção é criar um alarm que verifique o certificado de cada um dos seus servidores e o alerte quando a expiração estiver próxima (por email, sistema de monitorização etc.)

Para ajudar no ponto 3 acima, criei um pequeno script que pode ser alterado/adaptado às suas necessidades específicas:

1    #!/bin/sh

2   

3    SERVER_NAME=`onstat -c | grep DBSERVERNAME | grep -v "^[     ]*#" | awk '{print $2}'`

4    SERVER_LABEL=`onstat -c | grep SSL_KEYSTORE_LABEL | grep -v "^[     ]*#" | awk '{print $2}'`

5   

6    MYDATE=`gsk8capicmd -cert -details -locale en -db $INFORMIXDIR/ssl/$SERVER_NAME.kdb -stashed -label ${SERVER_LABEL} | grep After | awk '{print $4 " " $5 " " $6}'`

7   

8    NUM_DAYS=`dbaccess sysmaster <<EOF 2>/dev/null| grep "DAYS:" | awk -F':' '{print $2+0}'

9    select "DAYS:"||extend(TO_DATE("$MYDATE", "%B %d, %Y"), YEAR TO DAY) - extend(TODAY,YEAR TO DAY) FROM sysmaster:sysdual;

10    EOF`

11   

12   

13    if [ $NUM_DAYS -gt 0 ]

14    then

15        echo "O certificado está válido por $NUM_DAYS dias"

16    else

17        echo "O certificado está expirado!"

18    fi

Deixando de lado os truques e particularidades da SHELL, o que o script está a fazer é bastante simples:

1. Obtém os parâmetros DBSERVERNAME e SSL_KEYSTORE_LABLE do $ONCONFIG
2. Chama o comando gdk8capicmd para obter os detalhes do certificado. Extrai a linha "Not After :". Note que uso a opção "-locale en" para definir o formato da data
3. Usa o Informix com um script inline de SQL para calcular o número de dias até à data de expiração do certificado