UDRs: ROWNUM in Informix / ROWNUM em Informix
This article is written in English and Portuguese
Este artigo está escrito em Inglês e Português
English version
ROWNUM again?!
This is more or less a FAQ. If you search the Internet for "rownum informix" you'll get a lot of links and several possible answers. I don't plan to give you a final answer, but I'll take advantage of this frequent topic to go back to something I do enjoy: User Defined functions in C language.
Most of the questions regarding ROWNUM appear in the form "does Informix support XPTO database system's ROWNUM", or "does Informix support ROW_NUMBER like database XPTO?" or even "does Informix allow the retrieval of the top N rows?" So, if you ever need something that resembles ROWNUM, the first thing you should do it to establish a clear understanding of what you need. Because the above three questions can represent three different needs, and for each need there may be a different answer. Let's see:
- Does Informix support ROWNUM?
Quick answer would be no. But usually ROWNUM referes to an Oracle "magic" column that's added to the result set and that represents the position of the row in the result set. Note that this number is associated before doing ORDER BY and other clauses. So the result may not be very intuitive. Many times the purpose of using it is just to restrict the number of rows returned. Something that in ANSI (2008) SQL would be done by using the FETCH FIRST n ROWS clause. And this can be done in Informix very simply by puting a "FIRST n" before the select list:
SELECT FIRST n * FROM customer
But if you want to associate an incremental number to each row we can implement other solutions.
- Does Informix allow the retrieval of the top N rows?
Yes. I just showed how to do it in the previous section. Just use the FIRST n clause. Also note that you can also use the SKIP n clause. This options are applied after all the other clauses in the SELECT. So you could use them for pagination (although it would require running the same query several times, which is not efficient).
- Does Informix support row_number()?
No. And there's not much we could do. The row_number clause or function is a complex construct that can associate an incremental number to a result set, but with very flexible options that allows the numbering to restart on specific conditions etc. In order to achieve this we would need to be able to change how the query is solved. And we can't. But read on...
The implementation in C
You probably heard that we can create functions in C, Java and SPL (Stored Procedure Language), but most of us only used SPL. Informix extensibility through the user defined routines (UDR) is one of it's greatest strengths, but unfortunately it's also one of the least used features. This is unfortunate not only because we're wasting a lot of potential, but also because a greater usage would probably lead to greater improvement. I'm taking this opportunity to show you how simple it can be to create a function.
In order to do it, we must follow some rules, and we should know a bit about the available API. A good place to start understanding how we can create UDRs is the User's Defined Routines and Datatypes Developer's Guide. This explains the generic concepts and the kind of UDRs we can create. Then we can check the Datablade API Programer's Guide. This has a more technical description of several aspects (like memory management, processing input and output etc.). Finally we have the Datablade API Function Reference, for specific function help and description. But of course.... reading all this without practice is more or less useless. We could use a list of examples to get us going...
So, what I propose is to create a very simple C function that can be embedded in the engine. It's use is as easy as if it was a native function. And it's creation is really simple. The language used will be C. SPL is less flexible (although it can be very handy, useful and quick). Java doesn't have easy access to the internal API, but can eventually be even more flexible for certain tasks, although a bit more complex and slower. But it really depends on your background and needs.
Before we start I should mention a few points which are in fact the hardest part of the process:
- IBM bundles a few scripts with the engine that are necessary to get us started. Inside $INFORMIXDIR/incl/dbdk there are a few scripts that are simple makefiles. We may need to adapt these to the platform or compiler we're using. In my system I have:
- makeinc.gen This is a generic cross platform makefile used by the next one
- makeinc.linux This is a makefile specific for your platform which includes the previous one
- makeinc.rules This is a makefile containing basic compilation rules These scripts can and should be used by your own makefile
- The function code needs to include some files and follow several rules.
- After you create the function code, we need to compile it to object code and generate a dynamic loadable library. This is the way we make it available for the engine
- After installing the library we have to create the function, telling the engine where it is available
1 /*
2 ------------------------------------------
3 include section
4 ------------------------------------------
5 */
6 #include <stdio.h>
7 #include <milib.h>
8 #include <sqlhdr.h>
9 #include <value.h>
10
11 mi_integer ix_rownum( MI_FPARAM *fp)
12 {
13 mi_integer *my_udr_state, ret;
14
15 /*
16 ----------------------------------------------------------
17 check to see if we've been called before on this statement
18 ----------------------------------------------------------
19 */
20 my_udr_state = (mi_integer *) mi_fp_funcstate(fp);
21 if ( my_udr_state == NULL )
22 {
23 // No... we haven't... Let's create the persistent structure
24 my_udr_state = (mi_integer *)mi_dalloc(sizeof(mi_integer),PER_STMT_EXEC);
25 if ( my_udr_state == (mi_integer *) NULL)
26 {
27 ret = mi_db_error_raise (NULL, MI_EXCEPTION, "Error in ix_rownum: Out of memory");
28 return(ret);
29 }
30 // We created it, so let's register it and initialize it to 1
31 mi_fp_setfuncstate(fp, (void *)my_udr_state);
32 (*my_udr_state)=1;
33 }
34 else
35 {
36 // If it's not the first time, then just increment the counter...
37 (*my_udr_state)++;
38 }
39 // return the counter...
40 return(*my_udr_state);
41 }
The important points are:- Lines 1-10 are just the normal and required includes
- Line 11 is the function header. We define it as returning an mi_integer (on this functions we should use the mi_* datatypes). We accept one parameter which is a pointer to a function context
- Line 13 where we define auxiliary variables
- Line 20, we try to retrieve the previous value we kept stored in a persistent memory area. For that we use a datablade API function called mi_fp_funcstate
- Lines 21-30, if the previous call returned a NULL pointer we try to allocate (mi_dalloc) memory for keeping the counter. This may be one of the most important steps. We define that the persistence criteria is PER_STMT_EXEC. This means we're keeping the context only while we're executing the same statement. We test the result and raise an error if the allocation fails
- Line 31 we register the memory we have allocated as the function automatic parameter by calling mi_fp_setfuncstate()
- Line 32 is the counter initialization. On the first call we define it as 1
- Line 37 is the just the case for all the calls except the first. And in the generic case we just increment the counter
- Line 40 is just the return of the value after initializing or incrementing it
include $(INFORMIXDIR)/incl/dbdk/makeinc.linux MI_INCL = $(INFORMIXDIR)/incl CFLAGS = -DMI_SERVBUILD $(CC_PIC) -I$(MI_INCL)/public $(COPTS) LINKFLAGS = $(SHLIBLFLAG) $(SYMFLAG) all: ix_rownum clean: rm *.udr *.o ix_rownum: ix_rownum.udr @echo "Library genaration done" ix_rownum.o: ix_rownum.c @echo "Compiling..." $(CC) $(CFLAGS) -o $@ -c $? ix_rownum.udr: ix_rownum.o @echo "Creating the library..." $(SHLIBLOD) $(LINKFLAGS) -o $@ $?
Note the inclusion of the Linux makefile I mentioned earlier. If everything goes well, after I run make I will have a dynamic loadable library called ix_rownum.udr
panther@pacman.onlinedomus.com:informix-> make Compiling... cc -DMI_SERVBUILD -fpic -I/usr/informix/srvr1170uc4/incl/public -g -o ix_rownum.o -c ix_rownum.c Creating the library... gcc -shared -Bsymbolic -o ix_rownum.udr ix_rownum.o Library genaration done panther@pacman.onlinedomus.com:informix->Having done this we need to create the funcion using SQL, as an external function. The syntax can be:
CREATE FUNCTION rownum() RETURNING INTEGER WITH (VARIANT) EXTERNAL NAME '/home/informix/udr_tests/ix_rownum.udr(ix_rownum)' LANGUAGE C;We're telling the engine to create a function called rownum, which does not receive any parameter and returns an INTEGER. We specify that it's written in C and the location. Note that I'm giving it the full dynamic library path (/home/informix/udr_tests/ix_rownum.udr) and the function name inside that library (a single library can contain more than one function). And I left the explanation for "WITH(VARIANT)" for last... The external function creation allows us to specify several properties for the functions. This one, VARIANT, tells the engine that the function may return different values when called with the same parameters. This is critical since we're not passing any parameters. If we told the engine it was NOT VARIANT it would only call it once. After that it would assume the return value was 1. This is an optimization, but in our case we don't want it, since it would break the function logic. VARIANT is the default and I just include it for clarity. You can find more about the function properties here.
Working with it
Well, after the above we are able to use ROWNUM() in SQL. Let's see a few examples:
-- Example 1:
SELECT customer_num, rownum() row_num
FROM customer;
customer_num row_num
101 1
102 2
103 3
104 4
105 5
106 6
107 7
108 8
109 9
[...]
-- Example 2
SELECT customer_num, rownum()
FROM customer
WHERE rownum() < 5;
customer_num row_num
101 1
102 2
103 3
104 4
-- Example 3
SELECT
FIRST 4
customer_num, lname
FROM
customer
ORDER BY lname DESC;
customer_num lname
106 Watson
121 Wallack
105 Vector
117 Sipes
-- Example 4
SELECT
customer_num , lname, rownum() row_num
FROM
customer
WHERE rownum() < 5
ORDER BY lname DESC;
customer_num lname row_num
102 Sadler 2
101 Pauli 1
104 Higgins 4
103 Currie 3
-- Example 5
SELECT
t1.*, rownum() row_num
FROM (SELECT customer_num, lname FROM customer ORDER BY lname DESC) as t1
WHERE rownum() <5;
customer_num lname row_num
106 Watson 1
121 Wallack 2
105 Vector 3
117 Sipes 4
Let's comment the above examples. There are very important aspects to consider.
- Example 1
This is the simplest example. And works as expected - Example 2
Here we are using ROWNUM() also as a filter for the WHERE clause. - Example 3
This is a auxiliary example to show a possible problem. It's just a select of customer_num and lname ordered by this in a decremental order. - Example 4
Here we're trying the same thing, but using ROWNUM() to limit the number of rows. Note that this alters the result set. Why? Because ROWNUM() is applied immediately on the full table scan. The first 4 rows are retrieved and then the ORDER BY is applied. So using ROWNUM changes the the result set, because it's applied (in the WHERE clause) before the ORDER BY. - Example 5
If we wanted to reproduce the result set from example 3, but still add a row number we could use the syntax presented here
The ORDER BY inside the ROW_NUMBER() tells the database to order the sequence numbers by the specified filed(s). The PARTITION BY tells it to "restart" the count each time the field specified changes (similar to the effect of GROUP BY and aggregate functions). Note that this ORDER BY does not influence the order of the result set.
If you're wondering if we could implement the same functionality using functions, the answer is "sort of..." But I'll leave that for another article.
Versão Portuguesa
ROWNUM outra vez?!
Isto é uma questão que pode fazer parte dos FAQs. Se pesquisar na Internet por "rownum informix" vai obter uma série de links e algumas possíveis respostas. Não espero dar uma resposta definitiva, mas vou aproveitar este tema para voltar a um assunto que me agrada bastante: Funções definidas pelo utilizador em C.
Muitas das questões em torno do ROWNUM parecem numa das formas "o Informix suporta o ROWNUM tal como a base de dados XPTO?", ou "o Informix suporta o ROW_NUM como a base de dados XPTO?" ou ainda "o Informix suporta obter apenas as N primeiras linhas de uma query?". Assim se as suas necessidades parecem ir ao encontro do ROWNUM a primeira coisa a fazer é perceber exactamente o que se pretende. Necessidades diferentes podem ter soluções diferentes. Vamos ver:
- O Informix suporta o ROWNUM?
A resposta rápida seria não. Mas habitualmente a referência a ROWNUM diz respeita a uma coluna "mágica" do Oracle, que é adicionada ao conjunto de resultados e que representa a posição de cada linha nesse mesmo conjunto. Note que este número é adicionado antes do processamento do ORDER BY e outras cláusulas e isso pode tornar o resultado pouco intuitivo. Muitas vezes é usado apenas para limitar o número de linhas obtido. Algo que em ANSI (2008) SQL seria feito com a cláusula FETCH FIRST n ROWS. E isto pode ser feito em Informix muito simplesmente com um FIRST n antes da lista de colunas:
SELECT FIRST n * FROM customer
Mas se o que pretende é associar um número incremental a cada linha podemos implementar outras soluções.
- O Informix suporta obter apenas as primeiras N linhas?
Sim. Mostrei como no parágrafo anterior. Basta usar a cláusula FIRST N. Note-se que podemos também usar a cláusula SKIP n. Estas opções são aplicadas após todas as outras cláusulas do SELECT, nomeadamente o ORDER BY. Podem portanto ser usadas para paginação de resultados, embora isso leve à execução da mesma query várias vezes, o que não será muito eficiente
- O Informix suporta ROW_NUMBER?
Não. E não há muito que possamos fazer. A cláusula ROW_NUMBER é complexa. Permite associar um sequência incremental de valores a um conjunto de resultados, mas com opções muito flexíveis que permitem ordenar a sequência segundo um critério e recomeçar do valor 1 sempre que certas colunas mudam. Para conseguir fazer isto teríamos de conseguir controlar a forma como o motor resolve as queries. E tal não é possível... Mas já vamos ver o que se pode fazer...
A implementação em C
Já deve ter ouvido ou lido que podemos criar funções em C, Java e SPL (Stored Procedure Language), mas a maioria de nós apenas lidou com SPL. A capacidade de extensão do Informix através das funções definidas pelo utilizador (UDRs) é uma das suas melhores vantagens, mas infelizmente é também uma das menos usadas. Isto é mau não só porque estamos a desperdiçar muito potencial, mas também porque uma maior utilização levaria certamente a mais melhorias e desenvolvimentos. Vou aproveitar esta oportunidade para mostrar o quão simples pode ser criar uma função.
Para o fazer, temos de seguir algumas regras e devemos saber alguma coisa sobre a API disponível. Um bom sítio para começar a entender como podemos criar UDRs é o User's Defined Routines and Datatypes Developer's Guide. Isto explica os conceitos genéricos e os tipos de UDRs que podemos criar. Depois podemos consultar o Datablade API Programer's Guide. Este contém uma descrição mais técnica sobre vários aspectos (como gestão de memória, processamento de input e output etc.). Por último temos o Datablade API Function Reference, para informação e ajuda em funções específicas. Mas claro... ler isto tudo sem praticar é mais ou menos inútil. Seria bom termos uma lista de exemplos que nos permitissem arrancar....
Assim o que proponho é criar uma função muito simples em C que possa ser embebida no motor. O seu uso é tão fácil como se fosse uma função nativa do motor. E a sua criação é bastante simples. A linguagem usada será C, pois SPL é menos fléxivel (embora possa ser bastante prática, útil e rápida). O Java não tem o acesso tão fácil às funções da API interna, mas pode ainda ser mais fléxivel para algumas tarefas, embora possa ser mais complexo e lento. Mas a escolha deverá depender sempre das nossas necessidades e mesmo do nosso background com cada uma das linguagens.
Antes de começar devo referir alguns pontos que na verdade serão os mais difícieis do processo:
- A IBM inclui alguns scripts no motor que são necessários para arrancarmos. Dentro de $INFORMIXDIR/incl/dbdk existem alguns makefiles simples. Poderá ser necessário adaptá-los à plataforma e/ou compilador que vamos usar.No meu sistema tenho:
- makeinc.gen Um makefile genérico (várias paltaformas) usado pelo próximo
- makeinc.linux Makefile específico para Linux que referencia o anterior
- makeinc.rules Makefile com regras genéricas de compilação Estes scripts podem e devem ser usados pelo nosso próprio makefile
- O código da função tem de incluir alguns ficheiros e seguir determinadas regras
- Depois de criarmos o código da função temos de a compilar para código objecto e a partir deste gerar uma biblioteca dinâmica. Esta será a forma de disponibilizar a função ao motor
- Depois de instalar a biblioteca temos de usar SQL para criar a função indicando ao motor onde a mesma se encontra
1 /*
2 ------------------------------------------
3 secao de includes
4 ------------------------------------------
5 */
6 #include <stdio.h>
7 #include <milib.h>
8 #include <sqlhdr.h>
9 #include <value.h>
10
11 mi_integer ix_rownum( MI_FPARAM *fp)
12 {
13 mi_integer *my_udr_state, ret;
14
15 /*
16 ----------------------------------------------------------
17 Ver se já fomos chamados antes nesta instrução SQL
18 ----------------------------------------------------------
19 */
20 my_udr_state = (mi_integer *) mi_fp_funcstate(fp);
21 if ( my_udr_state == NULL )
22 {
23 // Não... não fomos... Vamos criar a estrutura persistente
24 my_udr_state = (mi_integer *)mi_dalloc(sizeof(mi_integer),PER_STMT_EXEC);
25 if ( my_udr_state == (mi_integer *) NULL)
26 {
27 ret = mi_db_error_raise (NULL, MI_EXCEPTION, "Erro em ix_rownum: Memória insuficiente");
28 return(ret);
29 }
30 // Já criámos, portanto vamos registar e inicializar a 1
31 mi_fp_setfuncstate(fp, (void *)my_udr_state);
32 (*my_udr_state)=1;
33 }
34 else
35 {
36 // Se não é a primeira vez vamos incrementar o contador...
37 (*my_udr_state)++;
38 }
39 // retornamos o contador...
40 return(*my_udr_state);
41 }
Os pontos importantes são:- Linhas 1-10 são os includes normais e necessários
- Linha 11 é o cabeçalho da função. Definimos como retornando um mi_integer (nestas funções devemos usar os tipos de dados mi_*). Aceitamos um parâmetro que será um ponteiro para uma estrutura de contexto da função. Este parâmetro não será visível na assinatura "externa" da função (ao nível do SQL)
- Linha 13 onde definimos variáveis auxiliares
- Linha 20, tentamos obter o valor anterior que mantivemos na estrutura persistente de memória. Para isso usamos uma função da API dos datblades chamada mi_fp_funcstate
- Linhas 21-30, se a chamada anterior devolver um ponteiro NULL, tentamos alocar (mi_dalloc) memória para manter o contador. Este será um dos passos mais importantes. Definimos que o critério de persistência é PER_STMT_EXEC. Isto significa que mantemos o contexto apenas durante a execução da mesma instrução SQL. Testamos o resultado e criamos uma excepção de a alocação falhar.
- Linha 31 registamos a memória alocada anteriormente como o parâmetro automático da função através da chamada mi_fp_setfuncstate()
- Linha 32 é a inicialização do contador. Na primeira chamada definimo-lo como 1
- Linha 37 é apenas o caso geral, para todas as chamadas excepto a primeira. E no caso geral apenas incrementamos o contador
- Linha 40 é o retorno da função, ou seja o valor do contador após inicialização ou incremento
include $(INFORMIXDIR)/incl/dbdk/makeinc.linux MI_INCL = $(INFORMIXDIR)/incl CFLAGS = -DMI_SERVBUILD $(CC_PIC) -I$(MI_INCL)/public $(COPTS) LINKFLAGS = $(SHLIBLFLAG) $(SYMFLAG) all: ix_rownum clean: rm *.udr *.o ix_rownum: ix_rownum.udr @echo "Geração da biblioteca completa..." ix_rownum.o: ix_rownum.c @echo "Compilando..." $(CC) $(CFLAGS) -o $@ -c $? ix_rownum.udr: ix_rownum.o @echo "Creando a biblioteca..." $(SHLIBLOD) $(LINKFLAGS) -o $@ $?
Note-se a inclusão do makefile Linux que mencionei anteriormente. Se tudo correr bem, após corrermos make teremos uma biblioteca dinâmica chamada ix_rownum.udr
panther@pacman.onlinedomus.com:informix-> make Compilando... cc -DMI_SERVBUILD -fpic -I/usr/informix/srvr1170uc4/incl/public -g -o ix_rownum.o -c ix_rownum.c Creando a biblioteca... gcc -shared -Bsymbolic -o ix_rownum.udr ix_rownum.o Geração da biblioteca completa... panther@pacman.onlinedomus.com:informix->Após termos feito isto, necessitamos de criar a função usando SQL, como uma função externa. A sintaxe será:
CREATE FUNCTION rownum() RETURNING INTEGER WITH (VARIANT) EXTERNAL NAME '/home/informix/udr_tests/ix_rownum.udr(ix_rownum)' LANGUAGE C;Estamos a dizer ao motor para criar uma função chamada rownum, a qual não recebe nenhum parâmetro, e retorna um INTEGER. Especificamos que é escrita em C e qual a localização. Note-se que estou a dar o caminho completo da biblioteca (/home/informix/udr_tests/ix_rownum.udr) e o nome da função dentro da biblioteca (uma única biblioteca pode conter mais que uma função). Deixei a explicação para "WITH(VARIANT)" para último lugar... A criação de uma função externa permite-nos especificar várias propriedades para as funções. Esta, VARIANT, indica ao motor que a função pode retornar valores diferentes quando chamada duas ou mais vezes com os mesmos parâmetros. Isto é critico dado que não estamos a passar nenhum parâmetro. Se indicássemos ao motor que a função era NOT VARIANT apenas a chamaria uma vez. Depois disso assumiria que o valor de retorno era 1. Isto é uma optimização, mas no nosso caso não queremos que tal aconteça, dado que quebraria a lógica da função. VARIANT é o valor pré-definido, e apenas o incluí por clareza. Pode aprender mais sobre as propriedades das funções aqui:
Trabalhando com a função
Depois do exposto acima, podemos usar ROWNUM() no SQL. Vejamos alguns exemplos:
-- Exemplo 1:
SELECT customer_num, rownum() row_num
FROM customer;
customer_num row_num
101 1
102 2
103 3
104 4
105 5
106 6
107 7
108 8
109 9
[...]
-- Exemplo 2
SELECT customer_num, rownum()
FROM customer
WHERE rownum() < 5;
customer_num row_num
101 1
102 2
103 3
104 4
-- Exemplo 3
SELECT
FIRST 4
customer_num, lname
FROM
customer
ORDER BY lname DESC;
customer_num lname
106 Watson
121 Wallack
105 Vector
117 Sipes
-- Exemplo 4
SELECT
customer_num , lname, rownum() row_num
FROM
customer
WHERE rownum() < 5
ORDER BY lname DESC;
customer_num lname row_num
102 Sadler 2
101 Pauli 1
104 Higgins 4
103 Currie 3
-- Exemplo 5
SELECT
t1.*, rownum() row_num
FROM (SELECT customer_num, lname FROM customer ORDER BY lname DESC) as t1
WHERE rownum() <5;
customer_num lname row_num
106 Watson 1
121 Wallack 2
105 Vector 3
117 Sipes 4
Vamos comentar os exemplos acima. Há aspectos muito importantes a considerar:
- Exemplo1
Este é o exemplo mais simples. Funciona como se esperaria - Exemplo 2
Aqui estamos a usar o ROWNUM() também como filtro da cláusula WHERE - Exemplo 3
Este é um exemplo auxiliar para ajudar a demonstrar um possível problema. É apenas um SELECT do customer_num e lname ordenado por este de forma descrescente - Exemplo 4
Aqui estamos a tentar a mesma coisa, mas usando o ROWNUM() para limitar o número de linhas. Note-se que isto altera o conjunto de resultados. Porquê? Porque o ROWNUM() é aplicado imediatamente durante o full table scan que é feito para resolver a query. As primeiras quatro linhas são obtidas, e depois o ORDER BY é aplicado. Portanto o uso do ROWNUM() altera o resultado porque é aplicado (na cláusula WHERE) antes do ORDER BY - Exemplo 5
Se quiséssemos reprodudir o resultado do exemplo 3, mas ainda assim adicionar um número de linha a cada elemento dos resultados, poderíamos usar a sintaxe apresentada aqui
O ORDER BY dentro do ROW_NUMBER() indica ao motor que deve ordenar a sequência de números pelos campos indicados. O PARTITION BY diz-lhe para recomeçar a numeração cada vez que o campo indicado mudar de valor (semelhante ao efeito de um GROUP BY em agregados). Note-se que este ORDER BY não afecta a ordem dos resultados apresentados.
Se está a imaginar se não poderíamos implementar uma funcionalidade semelhante usando funções, a resposta é "mais ou menos...". Mas vou deixar isso para outro possível artigo.
Posts
No comments:
Post a Comment