Como exportar/importar dados com UNLOAD/LOAD/DBLOAD

Versão para impressãoEnviar para amigoVersão PDF

No informix há várias maneiras de exportar/importar dados. E com a maioria deles é muito facil trabalhar.

O modo mais simples e básico é utilizar os comandos UNLOAD / LOAD / dbload.

Os comandos UNLOAD/LOAD são específicos de alguns utilitários do Informix (ISQL / dbaccess / 4GL), não é possível utiliza-los através dos clients.

  1. Quando utilizar?
  2. UNLOAD / LOAD
    1. UNLOAD
    2. LOAD
    3. Exemplo
  3. dbload
    1. Utilizando delimitadores (similar ao LOAD)
    2. Utilizando campos de tamanho fixos
  4. Exportando/importando campos Binarios (TEXT, BYTE, BLOB, CLOB)



Quando utilizar?


Estes comandos devem ser utilizados quando não há uma grande quantidade de dados para ser exportada/importada.
Quando não é exigido alta performance, principalmente nas cargas.
Utilizar para processos de integração diários com outros sistemas, que exigem SELECTs flexíveis e demandam por uma quantidade moderada de dados.



UNLOAD / LOAD


Estes são comandos *simulados* de SQL, ou seja, você pode executa-los como se estivesse enviando um SELECT para o banco de dados (apenas para os utilitários que o suportam) .
As propriedades comuns destes comandos são:
  • Trabalham apenas com delimitadores nos campos, onde o padrão é o pipe/barra "|", mas pode utilizar outro.
  • Em um arquivo separado por delimitadores, o ultimo campo de um registro sempre deverá finalizar com o delimitador.
  • Carregam e descarregam apenas para um arquivo localizado cliente de onde o LOAD/UNLOAD é executado.
  • Trabalham com cursores para ler/gravar os dados, como se fosse uma aplicação utilizando o banco de dados.
  • Campos como DATE, DATETIME, DECIMAL, MONEY , utilizam variaveis de ambiente GL_DATE, DBDATE, GL_DATETIME, DBTIME e DBMONEY para formatar o dado exportado.
  • Campos do tipo TEXT e BYTE são exportados dentro do arquivo gerado pelo UNLOAD
  • Campos do tipo CLOB e BLOB são exportados para um arquivo separado do arquivo especificado pelo UNLOAD.
  • Para dados que contém o caractere utilizado como delimitador, este é "escapado" com uma barra invertida antes: "\"
    Por exemplo "\|" indica que esta barra faz parte do dado e não é um delimitador.
  • Se um campo texto possui uma quebra de linha, no arquivo esta quebra de linha também será "escapada", representada por uma barra invertida seguida de uma quebra de linha real. Veja um exemplo de um unico registro em um arquivo texto que possui 3 campos:
    1|campo de texto numero 1| campo de texto 2, com quebra aqui \
    continuacao do campo texto 2 , logo apos a quebra de linha|
    



UNLOAD


O UNLOAD exporta os dados de um SELECT para um arquivo. Nele existe apenas dois parametros:
Arquivo destino: Especificar o nome do arquivo que será gerado. Não é obrigatório utilizar aspas e especificar o caminho, porém recomendo que estes dois itens sejam *sempre* especificados
Delimitador: Caso não queira utilizar a barra "|" como delimitador, basta especificar o novo caractere.
Caracteristicas da exportação utilizando o UNLOAD
  • A leitura dos dados é baseada em um SELECT. Podendo assim exportar dados de modo muito flexivel fazendo relacionamentos, utilizando views e etc.
  • A performance da extração irá depender do SELECT realizado (que pode ser analizado com o SET EXPLAIN), da performance de I/O do local onde o arquivo será gerado e da performance de conectividade do cliente com o banco de dados.

    # Sintaxe:
    # UNLOAD TO [arquivo] [DELIMITER "|"] select ... ;
    
    # Exemplos:
    UNLOAD TO arquivo1.unl select * from empresas;
    UNLOAD TO arquivo2.unl DELIMITER ";" select * from empresas;
    UNLOAD TO '/tmp/arquivo3.unl' DELIMITER ';' select e.empresa,p.produtos 
      from empresas e , produtos p where p.cod_empresa = e.cod_empresa;
    



LOAD


O LOAD é similar ao UNLOAD onde os parâmetros são os mesmos.
A unica diferença é que ao invés do SELECT para ler os dados, um INSERT é utilizado para incluir os dados.

Caracteristicas da importação utilizando o LOAD

  • Se o banco de dados possui transação e a tabela não é do tipo RAW, toda a carga será tratada como uma unica transação.
    Muito cuidado, pois isso pode gerar um "Long Transaction" no banco e abortar a carga, fazendo um rollback.
  • Os dados são lido de um arquivo e inseridos em apenas uma tabela. A estrutura dos dados no arquivo deve ser igual aos campos especificados na tabela carregada.
  • Como o LOAD trabalha realizando um INSERT de registro os indices são atualizados instantaneamente e todas as constraints serão checadas (campos NOT NULL, indices UNIQUE, FOREING KEYS, etc).
  • Se houver campos do tipo SERIAL sendo carregados, ele possui o mesmo comportamento de quando utilizado um INSERT.
    Se for especificado 0 (zero) um novo valor serial será criado, se informado um valor, este valor será utilizado.
    # Sintaxe
    # LOAD FROM [arquivo] [DELIMITER "|"] insert ... ;
    
    # Exemplos:
    LOAD FROM arquivo1.unl insert into empresas;
    LOAD FROM arquivo2.unl DELIMITER ";" insert into empresas;
    LOAD FROM "/tmp/arquivo3.unl" DELIMITER ";" insert into tab_temporaria(cod_empresa,produto);
    



Exemplo


    # Sequencia de comandos que cria duas tabelas temporárias, exporta a 
    # primeira para arquivo texto e carrega este arquivo na segunda tabela
    create temp table tmp1 ( cod integer, desc char(10), seq serial);
    Temporary table created.                                         
    create temp table tmp2 ( cod integer, seq serial, desc char(10), 
      status char(1) default 'X' );
    Temporary table created.                                                                      
    insert into tmp1 values (1,'teste um',0);
    insert into tmp1 values (2,'teste dois',0);
    insert into tmp1 values (8,'teste oito',99);
    insert into tmp1 values (4,'teste quatro',0);
    
    select * from tmp1;
            cod desc               seq 
    
              1 teste um             1
              2 teste dois           2
              8 teste oito          99
              4 teste quat         100
    
    unload to /tmp/tmp1.unl select * from tmp1 order by cod;
    4 row(s) unloaded.
    
    load from /tmp/tmp1.unl insert into tmp2(cod,desc,seq);
    4 row(s) loaded.
    
    select * from tmp2;
            cod         seq desc       status
    
              1           1 teste um   X
              2           2 teste dois X
              4         100 teste quat X
              8          99 teste oito X
    
    Database closed.
    
    # Conteudo do arquivo exportado
    $ cat /tmp/tmp1.unl
    1|teste um|1|
    2|teste dois|2|
    4|teste quat|100|
    8|teste oito|99|
    



dbload


O comando dbload é uma alternativa ao comando LOAD. Com ele é possível ter um pouco mais flexibilidade para realizar cargas de dados.
Suas caracteristicas são:
  • É executado através de linha de comando
  • Permite utilizar o arquivo em dois formatos, com delimitadores e por posição.
  • Permite configurar mais de um arquivo para ser carregado sequencialmente
  • Permite ignorar linhas que tiveram erro na carga (indice duplicado, check de constraint, foreing key, etc)
  • Permite ignorar as N primeiras linhas e iniciar a carga após estas N linhas.
  • Permite quebrar a carga em pequenas transações para evitar "Long transaction" e que toda a carga seja perdida em caso de algum erro que aborte o processo.
  • Não é possivel utiliza-lo em servidores secundários como HDR, SDS, RSS.
  • Exige que seja criado um arquivo de "comandos" onde é descrito o arquivo e formato do dado utilizado

Para utilizar o dbload é necessário criar o arquivo de comando, então antes irei demonstrar como se deve criar este arquivo e depois a linha de comando para executa-lo.



Utilizando delimitadores (similar ao LOAD)


Pode utilizar a carga de um arquivo com delimitadores quando um arquivo é gerado pelo UNLOAD ou qualquer outro meio (outro banco de dados, sistema, shell, etc).
A sintaxe do arquivo quando utilizado delimitadores:
    # Deve ser informador o nome do arquivo, delimitador e quantidade de 
    # campos que tem no arquivo.
    # O INSERT segue o padrão de SQL , onde pode ser especificado os campos 
    # que serão carregados. 
    # Comandos sempre finalizados com ";" 
    FILE [arquivo] DELIMITER [delimitador] [quantidade_de_campos] ;
    INSERT INTO [tabela] ... ;
    INSERT INTO [tabela] ... ;
    

Abaixo segue uma sequencia de comandos para ilustrar uma carga de dados.

    # Cria duas tabelas para teste
    $ echo 'create table tab_teste (
      codigo serial
    , status char(1) default "0"
    , desc varchar(20) default "sem informacao"
    , dt_criacao datetime year to day) ;
    create table tab_teste2 ( 
    cod serial, desc char(10) );' | dbaccess teste1
    Table created.
    Table created.
    
    # Arquivo de comandos a ser utilizado
    $ cat /tmp/tmp1.cmd
    FILE /tmp/tmp1.unl DELIMITER "|" 4 ;
    INSERT INTO tab_teste ;
    FILE /tmp/tmp2.unl DELIMITER "|" 1 ;
    INSERT INTO tab_teste(codigo);
    FILE /tmp/tmp3.unl DELIMITER "|" 6 ;
    INSERT INTO tab_teste(codigo,desc) values (f01,f03);
    FILE /tmp/tmp4.unl DELIMITER "|" 5 ;
    INSERT INTO tab_teste values (0,f01,f03,f02);
    INSERT INTO tab_teste2(desc) values (f05);
    
    # Conteúdo dos arquivos que serão carregados.
    $ cat /tmp/tmp1.unl
    1|2|linha 1|2009-07-01|
    2|1|linha 2|2009-07-02|
    8|0|linha 8|2009-07-03|
    5||linha 5|2009-07-04|
    $ cat /tmp/tmp2.unl
    9|
    10|
    11|
    $ cat /tmp/tmp3.unl
    15||linha quinze|coluna nao utilizada|xxx|yyy|
    18||linha 18|coluna nao utilizada|xxx|yyy|
    19||linha 18+1|coluna nao utilizada|xxx|yyy|
    $ cat /tmp/tmp4.unl
    9|2009-07-10|linha serial auto1|xxx|yz|
    8|2009-07-11|linha serial auto2|xxx|yz|
    8|2009-07-12|linha serial auto3|xxx|yz|
    
    
    # Executando a carga dos dados
    # O comando dbload possui vários parâmetros, mas três deles são obrigatórios, 
    # -d [banco] Banco de dados onde será realizado a carga
    # -c [arquivo_comando] Arquivo com comandos
    # -l [arquivo_log] Arquivo que será gerado log 
    $ dbload -d teste1 -c /tmp/tmp1.cmd  -l /tmp/tmp1.log
    DBLOAD Load Utility           INFORMIX-SQL Version 11.50.UC4DE
    
    Table tab_teste had 4 row(s) loaded into it.
    Table tab_teste had 3 row(s) loaded into it.
    Table tab_teste had 3 row(s) loaded into it.
    Table tab_teste had 3 row(s) loaded into it.
    Table tab_teste2 had 3 row(s) loaded into it.
    
    
    # Veja o resultado na tabela
    $ echo 'select * from tab_teste' | dbaccess teste1
    Database selected.
    
         codigo status desc                 dt_criacao
              1 2      linha 1              2009-07-01
              2 1      linha 2              2009-07-02
              8 0      linha 8              2009-07-03
              5        linha 5              2009-07-04
              9 0      sem informacao
             10 0      sem informacao
             11 0      sem informacao
             15 0      linha quinze
             18 0      linha 18
             19 0      linha 18+1
             20 9      linha serial auto1   2009-07-10
             21 8      linha serial auto2   2009-07-11
             22 8      linha serial auto3   2009-07-12
    13 row(s) retrieved.
    $ echo 'select * from tab_teste2' | dbaccess teste1
    Database selected.
    
            cod desc
              1 yz
              2 yz
              3 yz
    



Utilizando campos de tamanho fixos


Quando utilizado campos de tamanho fixo é necessário estar atento a alguns detalhes
  • String de valor nulo (NULL) deve ser especificado
  • Campos podem ser nomeados
  • Campos podem ser a concatenação de partes dos registros.
  • Conteúdo dos dados devem ter o tipo correto do campo da tabela

A sintaxe do arquivo quando utilizado campos de tamanho fixo:

    # Deve ser informador o nome do arquivo, os campos e 
    # suas posições, opcionalmente a representação do valor nulo.
    # O INSERT segue o padrão de SQL , onde pode ser especificado os campos 
    # que serão carregados. 
    # Comandos sempre finalizados com ";" 
    FILE [arquivo] ( [nome_campo] [pos_inicial[-pos_final]] [NULL=[texto_nulo], ...) ; 
    INSERT INTO [tabela] ... ;
    INSERT INTO [tabela] ... ;
    

Abaixo segue uma sequencia de comandos para ilustrar uma carga de dados.

    # Cria duas tabelas para teste
    $ echo '
    create table tab_teste (
      codigo serial
    , status char(1) default "0"
    , desc varchar(20) default "sem informacao"
    , dt_criacao datetime year to day) ;
    create table tab_teste2 ( cod serial, desc char(10) );
    ' | dbaccess teste
    Table created.
    Table created.
    
    # Arquivo de comandos utilizado
    $ cat /tmp/tmp1.cmd
    FILE /tmp/tmp1.unl (
      campo1 1-2
    , campo2 3 NULL="-"
    , campo4 4-13
    , campo5 14-23 );
    INSERT INTO tab_teste ;
    
    FILE /tmp/tmp2.unl ( c1 1-2 );
    INSERT INTO tab_teste(codigo);
    
    FILE /tmp/tmp3.unl (
      c1 1-2
    , c2 3 null="-"
    , c3 4-13 null=" "
    , c4 14-33
    , c5 34-36
    , c6 37-39 );
    INSERT INTO tab_teste(codigo,desc) values (c1,c3);
    
    FILE /tmp/tmp4.unl (
      c1 1 null="-"
    , c2 2-11
    , c3 12-29
    , c4 30-31
    , c5 32-34
    );
    INSERT INTO tab_teste values (0,c1,c3,c2);
    INSERT INTO tab_teste2(desc) values (c5);
    
    # Conteúdo dos arquivos que serão carregados.
    $ cat tmp1.unl
    1 2linha 1   2009-07-01
    2 1linha 2   2009-07-02
    8 0linha 8   2009-07-03
    5 -linha 5   2009-07-04
    $ cat tmp2.unl
    9
    10
    11
    $ cat tmp3.unl
    15-linha quincoluna nao utilizadaxxxyyy
    18-linha 18  coluna nao utilizadaxxxyyy
    19-linha 18+1coluna nao utilizadaxxxyyy
    $ cat tmp4.unl
    92009-07-10linha serial auto1xxxyz
    82009-07-11linha serial auto2xxxyz
    82009-07-12linha serial auto3xxxyz
    
    # Executando a carga dos dados
    $ dbload -d teste1 -c /tmp/tmp1.cmd  -l /tmp/tmp1.log
    DBLOAD Load Utility           INFORMIX-SQL Version 11.50.UC4DE
    
    Table tab_teste had 4 row(s) loaded into it.
    Table tab_teste had 3 row(s) loaded into it.
    Table tab_teste had 3 row(s) loaded into it.
    Table tab_teste had 3 row(s) loaded into it.
    Table tab_teste2 had 3 row(s) loaded into it.
    
    # Resultado
    $ echo 'select * from tab_teste' | dbaccess teste1
    Database selected.
    
         codigo status desc                 dt_criacao
              1 2      linha 1              2009-07-01
              2 1      linha 2              2009-07-02
              8 0      linha 8              2009-07-03
              5        linha 5              2009-07-04
              9 0      sem informacao
             10 0      sem informacao
             11 0      sem informacao
             15 0      linha quin
             18 0      linha 18
             19 0      linha 18+1
             20 9      linha serial auto1   2009-07-10
             21 8      linha serial auto2   2009-07-11
             22 8      linha serial auto3   2009-07-12
    
    $ echo 'select * from tab_teste2' | dbaccess teste1
    Database selected.
    
            cod desc
              1 xyz
              2 xyz
              3 xyz
    



Exportando/importando campos Binarios (TEXT, BYTE, BLOB, CLOB)


É possível exportar e importar dados de campos TEXT, BYTE, BLOB e CLOB com UNLOAD, LOAD e dbload.

Na exportação dos campos do tipo TEXT e BYTE os dados são gerados em um unico arquivo especificado pelo UNLOAD, onde o campo TEXT é gerado como um texto igual um campo CHAR e o campo BYTE é gerado em formato ASCII HEXADECIMAL (sem quebra de linha).
Na exportação dos campos do tipo CLOB e BLOB , os dados são gerados em um arquivo separado do arquivo especificado pelo UNLOAD, onde cada arquivo extra representa uma coluna CLOB/BLOB. Dentro do arquivo gerado pelo UNLOAD é criado uma referencia para o arquivo dos dados do CLOB/BLOB.
Veja um exemplo:

    # Esta é uma exportacao de uma tabela que possui uma coluna com um 
    # campo do tipo CLOB.
    $ ls -l resp.unl clob*
    -rw-r----- 1 informix informix 223694 2009-07-15 16:59 clob9368.7bc
    -rw-r----- 1 informix informix   3902 2009-07-15 16:59 resp.unl
    
    # A terceira coluna possui os dados de referencia do arquivo 
    # onde encontra-se o conteudo dos campos.
    $ head -n 5 resp.unl
    1|1|0,746,clob9368.7bc|
    2|2|746,67,clob9368.7bc|
    3|3|7ad,53c,clob9368.7bc|
    4|4|ce9,5d,clob9368.7bc|
    5|5|d46,5a,clob9368.7bc|
    
    # O conteúdo do arquivo clob* é continuo e sem delimitador ou 
    # quebra de linha, onde o dado é separado por tamaho/offset
    
5
Média: 5 (1 vote)
Sua avaliação: Nenhum

Importação / Exportação de Dados.

Sou novo na utilização de informix e fiquei com algumas duvidas: 1º - É possível utilizar os comandos UNLOAD/LOAD/DBLOAD dentro de uma procedure? 2º - Posso gerar o arquivo em qualquer diretório dentro do servidor ? 3º - Como fica a questão de permissões de arquivo para leitura/escrita dos arquivos? Paulo.

Importação / Exportação de Dados.

Entendi sua resposta. Mas como faria então para criar um procedure que escreve um arquivo com o resultado de um select e outra que lê-se um arquivo. Ou existe algum modo de eu criar um job que teria 2 etapas. A primeira executaria uma procedure para carregar uma tabela temporária com os dados do arquivo. A segunda executaria o UNLOAD/LOAD dessa tabela e geraria / leria os arquivos. A necessidade de ser uma procedure da-se pelo fato de os arquivos gerados terem nomes diferentes a cada execução. Paulo

External Tables ou UTL_FILE

Até a versão 11.70 xC6 , isso não era possível de modo "nativo".
Para fazer isso via procedure, até a versão 11.70 xC5 eu sei de duas opções.
Obs.: Se a frequencia de geração/leitura dos arquivos for muito alta, prepare-se para ter problemas de performance.
1a) Utilizar External Tables.
Limitações para sua situação: Você terá que apagar e criar a tabela cada vez que mudar o nome do arquivo, portanto o usuário necessitará de grant resource para isso (ou crie uma procedure DBA para realizar a tarefa de criar/dropar estas tabelas, assim não é necessário grant de resource). O código de execução ficaria algo como:

  execute procedure myProc_CriaTabExt('nome do arquivo');
  -- para fazer o unload
  insert into tab_externa select * from tab_origem;
  -- para fazer o load
  insert into tab_destino select * from tab_externa

2a) Chamar a função system() que irá executar um comando de S.O. , então você pode chamar um comando de dbaccess para fazer unload / load . (desde que não envolva tabelas temporárias no load/unload).
  create procedure ...
    ... 
    let vArq='/tmp/arq.txt';
    let vComando='echo "unload to \\"'||trim(vArq)||'\\" select ..." | dbaccess myDB'
    execute system(vComando);
  end procedure;

Na versão 11.70 xC6 foi adicionado um datablade novo, chamado Sql Utilities.
Nele você pode usar o comando UTL_FILE para gravar arquivos no sistema operacional. Mas será um processo "manual" de se fazer.
Também não posso opinar sobre a eficiência dele porque ainda não tive oportunidade de utilizar ou testar.

UNLOAD

Olá Paulo,
Respondendo :
1º - Não é possível pois ele é uma implementação feita no dbaccess , o comando não é reconhecido no banco de dados.
2º - Sim, desde que tenha permissão para o usuário que está executando o UNLOAD e especifique o path corretamente.
3º - Como a criação deste arquivo é feito pelo utilitário e não pelo banco de dados, ele irá assumir o owner que você está chamando o utilitário e salvará nas permissões do S.O. (umask no caso de *nix).

Abraço
Cesar

DBLOAD

Uma dúvida que surgiu... Se eu usar o dbload para carregar um arquivo .unl com uma versão mais antiga de uma determinada tabela, ele faz um MERGE com a tabela atual um insere todos os dados do arquivo.unl ? Obrigado.

Load em tabela com dados

Olá Giovani,

Sim, quando utilizado o LOAD, DBLOAD , HPL ou EXTERNAL TABLES, os registros carregados são incluidos como se fosse um INSERT, portanto você só precisa tomar cuidado caso faça a carga com indices ativados (PK ou unique), pois se tiver dados duplicados na carga ele irá acusar erro.
No caso do LOAD (via SQL) seu comando irá abortar no meio.
No caso do DBLOAD você pode especificar para ele continuar a carga e parar apenas se ocorrer N quantidade de erros.

César

Comentar

O conteúdo deste campo é privado não será exibido ao público.
  • Endereços de páginas de internet e emails viram links automaticamente.
  • Você pode usar tags BBCode no texto.
  • Tags HTML permitidas: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>

Mais informações sobre as opções de formatação

CAPTCHA
Este teste é para bloquear programas automatizados e previnir spams
CAPTCHA de Imagem
Digite o texto exibido na imagem.