Aqui estou listando as funções mais úteis do Informix que não estejam relacionado com strings, datas ou calculos.
Para funções relacionadas a tratamento de strings, consulte este artigo.
Para funções relacionadas a tratamento de datas, consulte este artigo.

1. BITAND(), BITANDNOT(), BITNOT(), BITOR(), BITXOR()
2. CASE
3. DECODE()
4. CAST(), ::
5. CURRENT_ROLE, DEFAULT_ROLE
6. DBINFO()
7. DBSERVERNAME, SITENAME
8. DECRYPT_CHAR(), DECRYPT_BINARY(), ENCRYPT_AES(), ENCRYPT_TDES(), GET_HINT()
   1. Diferenças entre o AES e TDES
   2. Caracteristicas gerais
9. HEX()
10. FILETOBLOB(), FILETOCLOB()
11. LOTOFILE()
12. LOCOPY()
13. NVL(), NULLIF()
14. USER

BITAND(), BITANDNOT(), BITNOT(), BITOR(), BITXOR()

Estas funções são para calculos em valores binários onde representam os operadores NOT, AND, OR e XOR.


create temp table tp01 ( valor1 int, valor2 int) ;
Temporary table created.
insert into tp01 values (1,2);
insert into tp01 values (6,2);
insert into tp01 values (8,4);
insert into tp01 values (16,8);
insert into tp01 values (255,43);

select bitand(valor1, valor2) as b_and
, bitandnot(valor1, valor2) as b_andnot
, bitor(valor1, valor2) as b_or
, bitxor(valor1, valor2) as b_xor
, bitnot(valor1) as b_not1
, bitnot(valor2) as b_not2
from tp01;
 b_and    b_andnot        b_or       b_xor      b_not1      b_not2
     0           1           3           3          -2          -3
     2           4           6           4          -7          -3
     0           8          12          12          -9          -5
     0          16          24          24         -17          -9
    43         212         255         212        -256         -44

CASE

A função CASE funciona igual a qualquer linguagem de programação, onde se tem várias possibilidades e executar o código conforme a opção válida.
A função possui dois modos de ser chamada, em um modo a expressao é colocada por completa em cada opção, no outro modo é o mais comum em todas as linguagens onde o campo a ser validade é definido no inicio do case e em cada opção é especificado o valor e qual código deve ser executado. Para ficar mais claro , veja o exemplo abaixo.


create temp table tp01 ( dia_semana smallint, feriado boolean );
Temporary table created.
insert into tp01 values (1,'f');
insert into tp01 values (2,'f');
insert into tp01 values (5,'t');

# CASE no modo mais comum de se ver em qualquer linguagem onde
# um campo é passado no CASE e as opções definidas com seus resulados.
select
  dia_semana
  , CASE dia_semana
  WHEN 1 THEN 'segunda'
  WHEN 2 THEN 'terça'
  WHEN 3 THEN 'quarta'
  WHEN 4 THEN 'quista'
  WHEN 5 THEN 'sexta'
  WHEN 6 THEN 'sabado'
  WHEN 0 THEN 'domingo'
  ELSE 'dia_invalido'
  END as dia_da_semana
from tp01;
dia_semana dia_da_semana
         1 segunda
         2 terça
         5 sexta

# Exemplo de CASE um pouco mais flexivel para certas situações onde
# a expressão é escrita em cada opção.
select
  dia_semana
  , CASE
  WHEN feriado THEN 'FERIADO!'
  WHEN dia_semana = 1 THEN 'segunda'
  WHEN dia_semana = 2 THEN 'terça'
  WHEN dia_semana = 3 THEN 'quarta'
  WHEN dia_semana = 4 THEN 'quista'
  WHEN dia_semana = 5 THEN 'sexta'
  WHEN dia_semana = 6 THEN 'sabado'
  WHEN dia_semana = 0 THEN 'domingo'
  ELSE 'dia_invalido'
  END as dia_da_semana
from tp01;
dia_semana dia_da_semana
         1 segunda
         2 terça
         5 FERIADO!


A função CASE possui as seguintes características:

• Todos os resultados *devem* retornar o mesmo tipo de dado, caso algum não seja do mesmo tipo o banco irá retornar erro.
• Uma vez que uma opção é validada, o resultado dela é retornado e nenhuma outra opção é analisada.
• A opção ELSE é opcional.

DECODE()

A função DECODE é nada mais que uma função CASE, mas em sintaxe de função.


# Sintaxe:
# DECODE(campo, [expressao_IF], [THEN_valor], [ELSE_valor ou expressao_IF], [...])

# Executo dois selects que irão funcionar igual ao exemplo do CASE,
# porém com a função DECODE.
select dia_semana
  , decode( dia_semana
    ,1, 'segunda'
    ,2, 'terça'
    ,3, 'quarta'
    ,4, 'quista'
    ,5, 'sexta'
    ,6, 'sabado'
    ,7, 'domingo'
    ,'dia_invalido') as dia_da_semana
from tp01;
dia_semana dia_da_semana
         1 segunda
         2 terça
         5 sexta

# Aqui foi necessário utilizar duas chamadas de funções DECODE
# aninhadas para simular o outro comportamento do CASE.
select dia_semana
  , decode( feriado
    ,'t', 'FERIADO!'
    , decode( dia_semana
      ,1, 'segunda'
      ,2, 'terça'
      ,3, 'quarta'
      ,4, 'quista'
      ,5, 'sexta'
      ,6, 'sabado'
      ,7, 'domingo'
      ,'dia_invalido')
    )as dia_da_semana
from tp01;
dia_semana dia_da_semana
         1 segunda
         2 terça
         5 FERIADO!

CAST(), ::

A função CAST realiza conversão de tipo de dados explícitamente.
Para melhor entendimento: Existe dois tipos de CAST no banco, o explícito e o implícito.
Cast implícito: É o tipo de conversão automatica, onde se relacionado um campo do tipo X com uma expressão que espera o tipo Y , o banco automaticamente realiza a conversão entre eles, sem necessidade do CAST.
Cast explícito: É a conversão feita através da função CAST ou operador ::


# Muita ATENÇÃO com conversões de DATA e NUMERO pois eles
# podem ter diferentes comportamento dependendo do tipo
# de dado origem/destino na conversão e locale (lingua) trabalhado
# (veja campos f, g e h)
# Neste exemplo trabalho com CLIENT_LOCALE=pt_BR.8859-1
select cast("123" as decimal(10,5)) as a
 , cast("321" as decimal(10,1)) as b
 , cast("422,44" as decimal(10,4)) as c
 , cast(123.353 as char(10)) as d
 , cast("250.123,353" as decimal(10,4)) as e
 , cast(31/01/2009 as date) as f
 , cast("31/01/2009" as date) as g
 , cast(31/01/09 as date) as h
 , cast("10" as datetime day to day) as i
 , cast("10" as interval day to day) as j
 , cast("2009-01-31 15:10" as datetime year to minute) as k
from sysdual
a  123,00000
b  321,0
c  422,4400
d  123,353
e  250123,3530
f  31/12/99
g  31/01/09
h  03/01/00
i  10
j   10
k  2009-01-31 15:10

CURRENT_ROLE, DEFAULT_ROLE

Retorna a ROLE atual e a ROLE padrão para o usuário conectado.
ROLEs são selecionadas com o comando SET ROLE e são profiles de permissões (grants/revokes).

DBINFO()

A função DBINFO é um mix de funcionalidades. Ela é bem flexivel e possui vários parâmetros.
Não irei listar cada parâmetro aqui, mas irei dizer quais são os principais. No manual (link no paragrafo anterior) está bem documentado todos os parâmetros e com exemplos.
Principais funcionalidades do DBINFO

• 'dbspace': Retorna o nome do dbspace a partir de um partnum/tblspace.
• 'dbname': Retorna o nome do banco de dados que está conectado.
• 'sessionid': Retorna o numero da sessão atual.
• 'dbhostname': Retorna o nome da MAQUINA onde a instancia se encontra.
• 'serial8': Retorna o valor do ultimo campo SERIAL8 incluido.
• 'bigserial': Retorna o valor do ultimo campo BIGSERIAL incluido.
• 'version': Retorna a versão do Banco de Dados.
• 'utc_current': Retorna em UTC (valor númerico) a hora atual.
• 'utc_to_datetime': Converte uma data em formato UTC para datetime.
• 'sqlca.sqlerrd1': Retorna o valor do ultimo campo SERIAL incluido.
• 'sqlca.sqlerrd2': Retora a quantidades de linhas processadas de um SELECT/UPDATE/INSERT/DELETE/EXECUTE PROCEDURE/EXECUTE FUNCTION

DBSERVERNAME, SITENAME

É na realidade uma váriavel que retorna o nome da INSTANCIA conectada.
Porém ele retorna o nome utilzado no parâmetro DBSERVERNAME do ONCONFIG, mesmo que o usuário tenha se conectado através de um nome definido no DBSERVERALIASES.
A variável SITENAME é um sinonimo do DBSERVERNAME.


$ export INFORMIXSERVER=idsmars_str
$ dbaccess sysmaster -
Database selected.
> select sitename, dbservername from sysdual;
(expression)  idsmars
(expression)  idsmars

DECRYPT_CHAR(), DECRYPT_BINARY(), ENCRYPT_AES(), ENCRYPT_TDES(), GET_HINT()

Funções para encriptação de dados.
ENCRYPT_AES : Encripta o dado utilizando o algoritimo AES (Advanced Encryption Standard)
ENCRYPT_TDES: Encripta o dado utilizando o algoritimo 3-DES (Triple Data Encryption Standard)
DECRYPT_CHAR : Decripta um campo encriptado
DECRYPT_BINARY : Decripta um campo SmartObject (CLOB,BLOB) encriptado.
GET HINT : Exibe a dica de senha salva no dado encriptado.
SET ENCRYPTON : Define a senha padrão para sessão, evitando assim que seja necessário fornecer a senha a cada acesso.



Diferenças entre o AES e TDES


O informix disponibiliza os dois algoritmos para que assim fique a cargo do usuário escolher o que melhor atende suas necessidades. As diferenças entre os dois são: O TDES é considerado mais seguro porém seu processamento é mais lento e o dado encriptado por ele é menor que o AES.
A encritação AES é utilizada pelo governo Americano como algoritimo oficial para encriptação de seus dados.



Caracteristicas gerais



• Os dados são encriptados através das funções AES ou TDES e depois codificados através do algoritimo BASE64.
• Não encripta na conexao de rede. Para isso é necessário ativar recursos como SSL ou ENCCSM
• Pode afetar a performance.
• Não pode utilizar dados encriptados em campo com indice ou constraint
• Não é recomendado utilizar indices de funções com as funções de desencriptação, porque assim o dado será salvo em seu formato puro no banco de dados, mas teoricamente isso é possível.
• A senha de encriptação pode ser especificada em dois momentos, na sessão através do comando SET ENCRYPTION ou na própria função de encritpação (ENCRYPT_AES/TDES). A senha passada pela função tem preferência.
• Pode encriptar apenas campos textos e SmartObject: CHAR, NCHAR, VARCHAR, NVARCHAR, LVARCHAR, BLOB e CLOB
• Pra CLOB e BLOB utiliza temporáriamente o Default Smart Space (SBSPACENAME)
• O tamanho do dado encriptado sempre é maior que o tamanho original.
  Para saber como calcular o tamanho veja no manual
  
• Para encriptação em campos VARCHAR e NVARCHAR, se o tamanho do dado é maior que 200 bytes a função de encriptação irá retornar automaticamente o valor em formato LVARCHAR ou NCHAR devido o valor encriptado ser maior que 255 bytes (limite do campo NVARCHAR/VARCHAR).
• A senha precisa ter no mínimo 6 bytes e no máximo 128 bytes
• É possível salvar uma dica (hint) com tamanho máximo de 32 bytes para lembrar da senha
  Esta dica é salva *junto* do dado encriptado e recuperada através da função GET_HINT.
  
• Nenhuma senha é salva no banco de dados, sem ela não é possível recuperar o dado
  Nenhuma informação é salva no logical log ou catalogo de sistema (sys*).
  
• ATENÇÃO: Quando o valor do dado encriptado é salvo em um campo e este campo não possui salvo suficiente para armazena-lo, o dado será TRUNCADO gerando assim a perda total desta informação.

Conceituamente a encriptação pode ser trabalhada de duas formas: A nível de coluna ou nível de célula.

Encriptação por coluna

• Não é utilizado a dica (hint). O motivo é evitar utilização desnecessária de espaço em disco.
• A dica deve ser salva fora do banco pelo usuário.
• É definido a senha para a sessão (set encryption).
• É utilizado o mesmo algoritmo para todos os registro na coluna em questão
• Em caso de mais de uma coluna possua dado encriptado, o acesso simultaneo a ambas irá depender se a senha utilizada é comum para as duas.
  Encriptação por célula
  
• Pode ser utilizado a dica (porém mais espaço em disco será necessário)
• Não é utilizado senha na sessão
• A senha é especifica para cada registro/coluna acessado.

Exemplo


# Cria tabela onde o possue um campo (t1_enc) que não suporta (tamanho) dado encriptado.
create temp table tp01 ( texto char(30), t1_enc char(30), t2_enc char(50));
Temporary table created.
insert into tp01 (texto) values ("o papa é pop");
1 row(s) inserted.
# Demonstracao do resultado de um campo encriptado 
select texto
 , encrypt_aes(texto, "minha senha") as a
 , length(encrypt_aes(texto, "minha senha")) as b
from tp01;
texto         o papa é pop
a             0Sbb/AAAAEAAr26XD0zlK/vvnT0S/005ljZW+hiulFH
b             43

# Situação em que a coluna que terá o dado encriptado salvo, 
# mas não possue espaço suficiente para a informação.
# Deve-se ter muita ATENÇÃO pois na encriptação não é exibido erro
# o usuário só irá descobrir que *perdeu* o dado no momento da leitura
update tp01 set t1_enc = encrypt_aes(texto, "minha senha")
 , t2_enc = encrypt_aes(texto, "minha senha");
1 row(s) updated.
select texto as a
     , decrypt_char(t1_enc, "minha senha")  as b
     , decrypt_char(t2_enc, "minha senha")  as c
from tp01;
26012: The internal base64 decoding function failed.
Error in line 13
Near character position 7

# Tentando desencriptar com senha errada
select texto as a , decrypt_char(t2_enc, "sua senha")  as b
from tp01;
26008: The internal decryption function failed.
Error in line 16
Near character position 7

# Desencriptado com sucesso 
select texto as a , decrypt_char(t2_enc, "minha senha")  as b
from tp01;
a  o papa é pop
b  o papa é pop

# Exemplo de encriptação utilizando senha na sessão
create temp table tp02 ( cliente char(30), cartao char(300));
Temporary table created.
set encryption password 'esta eh a senha do cartao';
Encryption password set.
insert into tp02 values ("joao", encrypt_aes("1234589578343"));
insert into tp02 values ("flavia", encrypt_aes("8859432110849"));
insert into tp02 values ("maria", encrypt_aes("5893274910283"));
insert into tp02 values ("silvio", encrypt_aes("5678901027484"));
insert into tp02 values ("silvia", encrypt_aes("5678901027484"));
insert into tp02 values ("ricardao", encrypt_tdes("8575784403923"));
# Grava dado encriptado 2 vezes,uma com a senha da sessão e outra com a senha do registro/usuário
insert into tp02 values ("chico",
  encrypt_aes(encrypt_tdes("7898374285612","senha do chico")));

select *, decrypt_char(cartao) as cartao_dec from tp02;
cliente     joao
cartao      0yTb/AAAAEAE0XIq97JEZt++EiPMG4MyouV96h14Fqe
cartao_dec  1234589578343

cliente     flavia
cartao      0LdL/AAAAEAHGYZJOeeOvIs0rEuLb4DZ0VEXTAs4b2n
cartao_dec  8859432110849

cliente     maria
cartao      00yz/AAAAEAlopcFS4IIh3D03ej50QJuadW+K/fBvqB
cartao_dec  5893274910283

cliente     silvio
cartao      0oV7/AAAAEA7zg4o4TuJANEsYSmMBPzFf9Ry5qINuga
cartao_dec  5678901027484

cliente     silvia
cartao      0h3j/AAAAEAsrl9yDeeQJRJFkkS1GphUwQlu9HgnKKa
cartao_dec  5678901027484

cliente     ricardao
cartao      1b5D/AAAAEApV/WxDR72V00XXLOXs05gaTBKhQPoF04
cartao_dec  8575784403923

cliente     chico
cartao      0yDf/AAAAMACWbIjmdk9pKdqwdDJ9RBwyVM4KZSislFgDpUyZMBrem8Tf98BW4bQ7A2
            62nFgbl6/znpphWltnY=
cartao_dec  1fIP/AAAAEAg5Epquxh+OEIHymP+WTCBj+aPahZ11Z8

select *, decrypt_char(decrypt_char(cartao),"senha do chico") as cartao_dec
from tp02 where cliente = "chico";
cliente     chico
cartao      0yDf/AAAAMACWbIjmdk9pKdqwdDJ9RBwyVM4KZSislFgDpUyZMBrem8Tf98BW4bQ7A2
            62nFgbl6/znpphWltnY=
cartao_dec  7898374285612

HEX()

A função hex() faz a conversão de um número inteiro para o formato hexadecimal (formato TEXTO) incluindo a formatação "0x" no inicio da string.


select hex(1) as a
  , hex(10) as b
  , hex(15) as c
  , hex(16) as d
  , hex(45354892) as e
  , hex("45354892") as f
from sysdual;
a  0x00000001
b  0x0000000A
c  0x0000000F
d  0x00000010
e  0x02B40F8C
f  0x02B40F8C

FILETOBLOB(), FILETOCLOB()

As funções FILETOBLOB/CLOB são utilizadas para carregar um arquivo em campos SmartObject (CLOB e BLOB).
Elas podem carregar um arquivo localizado no servidor ou no cliente.


# Observação: Para este teste funcionar é necessário ter o 
# Smart DBspaces Default criado e definido no ONCONFIG.
create temp table tp01 ( cod smallint , texto clob, executavel blob);
Temporary table created.                                             
insert into tp01 values (1 , filetoclob("/etc/hosts", "server"), 
  filetoblob("/bin/bash","server"));
1 row(s) inserted.                                                                                 
insert into tp01 values (2 , filetoclob("/tmp/teste.txt", "client"), 
  filetoblob("/usr/local/bin/sqlcmd","client"));
1 row(s) inserted.                                                                                                 


Ainda há mais parâmetros não demonstrados aqui: definição de padrão de armazenamento e offset/tamanho do arquivo gerado. Para mais informações veja o manual.

LOTOFILE()

A função LOTOFILE salva o conteúdo de um campo BLOB/CLOB para arquivo no cliente ou servidor.
O nome do arquivo criado por padrão é sempre complementado com um valor hexadecimal que é o identificador (ponteiro) do dado. Isto é assim com a finalidade de evitar que todos os dados exportados apontem para o mesmo arquivo. Mas é possível contornar esta situação.
Veja os exemplos.


# Utilizando a mesma tabela temporária do exemplo anterior, exporto 
# o conteúdo do campo texto.
# A função gera o arquivo e retorna o nome do arquivo criado.
select lotofile(texto, "/tmp/texto1", "client" ) from tp01;
(expression)  /tmp/texto1.000000004a8201fc
(expression)  /tmp/texto1.000000004a8201fe

# Incluindo uma exclamação ao final do arquivo faz com que o codigo 
# identificador não seja gerado. Mas deve-se tormar cuidado poque a 
# função não sobrescreve arquivos já existentes, quando já existe o 
# arquivo ela gera um erro.
select lotofile(texto, "/tmp/texto1!", "client" ) from tp01;
(expression)  /tmp/texto1
   17: File exists
Error in line 8
Near character position 59


Há outras mascaras para geração do arquivo, consulte o manual.

LOCOPY()

A função LOCOPY copia um SmartObject permitindo que seja utilizado a configuração de armazenamento da nova tabela.

NVL(), NULLIF()

A função NVL verifica se uma expressão é NULA, se sim retorna o valor da segunda expressão no lugar, se não for NULA retorna a própria expressão.
A função NULLIF faz o contrário do NVL, compara duas expressões e retorna NULL se a comparação for verdadeira, caso contrário retorna a primeira expressão.


create temp table tp01 ( cod smallint, valor1 char(10) );
Temporary table created.                                 
insert into tp01 values (1,"teste1");
insert into tp01 values (2,"abcd");
insert into tp01 values (3,"informix");
insert into tp01(cod) values (4);

select cod
  , nvl(valor1,"valor_nulo") as a
  , nullif(valor1,"teste1") as b
from tp01;
   cod  a           b
     1  teste1
     2  abcd        abcd
     3  informix    informix
     4  valor_nulo

USER

A função USER retorna uma string com o nome do usuário conectado na sessão.


select user from sysdual;
(expression)
informix