segunda-feira, 9 de agosto de 2010

Criando Banco de Dados e Tabelas

Criando um Banco de Dados

Como administrador do banco você precisa saber certas decisões antes de criar um banco de dados.

  • Onde no sistema Informix, o banco de dados será armazenado? Normalmente esta decisão é tomada junto com o administrador do sistema Informix, quem cria e administra os dbspaces dentro de um sistema Informix.
  • Você usará um log de transação para registrar todas as mudanças nos dados do banco de dados? Quando este log será enviado ao disco?
  • Este será um banco de dados padrão ANSI? Bancos de dados ANSI devem estar de acordo com certas regras colocadas pelo comitê de padrões ANSI.

Quando você cria um banco de dados, são geradas automaticamente tabelas e catálogo de sistema (system catalog) que descrevem a estrutura do banco de dados. Estas tabelas são acessadas cada vez que um comando SQL é executado. O catálogo de sistema é usado para determinar privilégios no sistema ou verificar nome de tabelas, por exemplo.

Localização: Dbspaces

A localização de banco de dados, tabelas e índices no Informix são um conjunto particular de espaço em disco, chamado de dbspace. A instância do IDS possui espaço de disco associados a ela denominados chunks. Cada chunk é uma unidade contínua de espaço. Um conjunto lógico de chunks é chamado de dbspace. Cada instância do Informix tem pelo menos um dbspace, o root dbspace.

Localização de um banco de dados

Você pode especificar qual dbspace usar ao criar um banco de dados. isto significa que as tabelas de catálogo de sistema serão localizadas no chunk ou chunks associados a este dbspace. A menos que você especifique o contrário, todas as tabelas associadas com aquele banco de dados, e os índices correspondentes, serão armazenados neste dbspace.
Se você não especificar o dbspace no qual criará o banco de dados, o banco de dados, por default, será criado no root dbspace. Isto não é recomendado. Outros dbspaces devem ser criados para conter os bancos de dados.

Separando os bancos de dados em dbspaces diferentes pode ter várias vantagens:

  • Um banco de dados não pode crescer além do espaço disponível no dbspace. Limitando o tamanho e número de chunks associados a um dbspace, você limita também o tamanho do banco de dados.
  • Você pode associar os bancos de dados a diferentes devices associando-os a dbspaces separados que tem chunks em dispositivos diferentes. Isto reduzirá a concentração de I/O.
  • Se um de seus dispositivos sofrer falha, você perderá o acesso ao banco de dados armazenado no dbspace daquele dispositivo. outros bancos de dados não serão afetados.

DBspaces e crescimento

Dbspaces podem ter tantos chunks associados a ele quanto necessário. Se você esgotar o espaço em um dbspace em particular (porque todos os chunks associados a ele estão cheios), você pode adicionar chunk a ele.

Modos de Log: No Logging

Você pode criar um banco de dados no sistema Informix que não registre (log) transações. Se um banco de dados não tiver log, então não é registrada qualquer mudança feita aquele banco de dados no logical log. Da perspectiva da aplicação, isto significa que transações não são suportadas.

Sem recuperação Total

Mais importante, um banco de dados sem log não pode ser recuperado completamente se você precisar restabelecer o sistema de um archive (backup). Considerando que um archive registra o estado do sistema no momento do archive, quaisquer alterações feitas desde o momento do archive devem ser recuperadas dos registros nos logical logs. Considerando que um banco de dados sem log não cria registro de log, não haverá como recuperar essas alterações.

Modos de Log: Buffered Logging

Se você escolher registrar as transações do banco de dados, os registros dos logs não são gravados diretamente em disco. Eles são posto primeiro em um buffer em memória, chamado logical log buffer. Eventualmente o buffer será transferido para disco e as entradas serão gravadas no logical log em disco.
Se um banco de dados é criado com Buffered Log, então os registros do logical log buffer não serão gravados em disco até que o buffer seja preenchido totalmente.

Vantagem

O volume de I/O físico é reduzido consideravelmente. Considerando que o I/O físico é uma operação que demanda um grande custo, isto pode melhorar o desempenho de seu sistema.

Desvantagem

Se um crash (queda) de sistema acontecer, tudo que está contido no buffer e não estiver ainda em disco será perdido. Sem os dados que estão contidos no logical log em disco, o IDS não pode recuperar essas transações quando o sistema é novamente colocado em operação. Assim enquanto Buffered Log pode melhorar o desempenho, ele pode resultar na perda de algumas transações no caso de uma queda do sistema.

Modos de Log: Unbuffered Logging

Se um banco de dados é criado com unbuffered Log, o conteúdo do logical log buffer é gravado no logical log em disco assim que uma transação esteja completa. Isto significa que uma vez que você completou uma transação no banco de dados, os logs desta transação serão gravados em disco.

Vantagem

Se houver uma falha de sistema, as alterações serão recuperadas quando o sistema voltar a operar.

Desvantagem

Um volume maior de I/O físico será executado em disco, o que pode degradar um pouco o desempenho.

Banco de Dados Mode ANSI

Quando você cria um banco de dados padrão ANSI (Log Mode ANSI) as seguintes características principais o diferenciam de um banco de dados fora de MOde ANSI:

  • Todos os bancos de dados usam log unbuffered.
  • Todos os comandos são contidos em transações. Programas que acessam banco de dados ANSI não podem usar o comando BEGIN WORK; o programa sempre está em uma transação e o comando COMMIT WORK implicitamente iniciará uma nova transação assim que a transação corrente seja concluída.
  • A propriedade é exigida. Você tem que usar o nome do dono quando você recorrer a cada tabela, view, sinônimo, índice, ou constraint a menos que você seja o dono deste objeto.
  • O nível de isolamento default é Repeatable Read. Os níveis de isolamento serão discutidos em um capítulo posterior.
  • Os privilégios default em bancos de dados ANSI é diferente de bancos de dados não ANSI. Usuários não recebem privilégios PUBLIC para tabelas e sinônimos por default.

O comando CREATE DATABASE

Você cria um banco de dados com o comando CREATE DATABASE. Este comando também criará automaticamente o catálogo de sistema. Os exemplos mostram diferentes modos para criar um banco de dados no IDS.

CREATE DATABASE tecnologia

Este comando criará um banco de dados no local default que é o root dbspace. O banco de dados é criado sem log. Como mencionando anteriormente, a criação de banco de dados root dbspace não é recomendada.

CREATE DATABASE tecnologia IN dbspace1 WITH LOG

Este exemplo cria um banco de dados no dbspace chamado dbspace1, com Unbuffered Logging.

CREATE DATABASE tecnologia WITH BUFFERED LOG

Este exemplo criará um banco de dados no local default com Buffered Logging.

CREATE DATABASE tecnologia IN dbspace1 WITH LOG MODE ANSI

Este exemplo criará um banco de dados ANSI no dbspace chamado dbspace1. Um banco de dados ANSI usa Unbuffered logging.

Criando uma tabela

Quando você cria uma tabela em um banco de dados, você deve decidir:

  • Os nomes e os tipos de dados das colunas na tabela
  • O dbspace onde a tabela será armazenada. As tabelas podem ser criadas no mesmo dbspace onde esta o banco de dados ou em um dbspace diferente.
  • A quantidade de espaço continuo a ser alocado inicialmente no dbspace para esta tabela. isto é determinado pelo tamanho de extent configurado na tabela.
  • O nível de lock da tabela. Locks são usados para prevenir um usuário de acessar os dados que estão sendo utilizados por outro usuário.

Quando uma tabela é criada, as informações das colunas e das tabelas são inseridas automaticamente nas tabelas de catálogo systables e syscolumns.

Tabelas e Dbspaces

Com o Informix, você pode criar tabelas em dbspaces diferentes daqueles onde se localiza o banco de dados. As informações do catálogo do sistema para a tabela são armazenadas com o resto do banco, mas os dados da tabela serão armazenados no dbspace especificado.
A vantagem da localização de algumas tabelas em dbspaces diferentes é que estas tabelas podem ser colocadas em dispositivos físicos diferentes do restante do banco de dados.

  • Tabelas Grandes – não haverá competição para espaço de outras tabelas.
  • Tabelas Acessadas Frequentemente – Terão uma redução de concentração de leitura do disco quando outros processos agirem em tabelas diferentes.

Agrupando tabelas para Backup (Archive)

Os utilitários de archive (backup) do IDS suportam archive e restore com paralelismo e com granularidade de dbspace. Para utilizar ao máximo estas vantagens, agrupe tabelas em dbspces baseado em quais tabelas precisam ser arquivadas mais frequentemente.

Extents

O espaço em disco para uma tabela é alocado em unidades denominadas extents. Um extent é um volume de espaço fisicamente contínuo em disco; a quantidade é especificada para cada tabela quando esta é criada.
O tamanho de um extent é especificado em kilobytes. Este numero deve ser múltiplo do tamanho da pagina do sistema (page size). A pagina é a unidade básica de I/O em um sistema Informix. O tamanho da pagina é determinada pela arquitetura do servidor ou sistema operacional, e não pode ser mudado. O tamanho de pagina mais usado é 2 kbytes, embora alguns sistemas usam 4 kbytes no tamanho da pagina.
Quando um extent é acrescentado, no principio esta vazio. Quando este extent não tem mais espaço, outro extent será alocado para a tabela, quando aquele extent estiver cheio, outro extent será alocado, e assim por diante.
Cada tabela tem dois tamanhos de extent associados a ela.

Extent Size (Tamanho de Extent) – É o tamanho do primeiro extent alocado para a tabela. Este primeiro extent é alocado quando a tabela é criada. O padrão é 8 paginas.

Next Size (Próximos Extents) – É o tamanho de cada extent subseqüente a ser acrescentado à tabela. O default é 8 paginas.

O tamanho mínimo de um extent é de 4 paginas. Não há nenhum tamanho máximo na pratica (estaria numa faixa de 2 Gigabytes). Informix recomenda calcular o tamanho de extent para cada uma de suas tabelas ao invés de usar o tamanho de extent default.

Tblspace

Todos os extent alocados para uma determinada tabela são logicamente agrupados e são referenciados como um tblspace. O espaço representado pelo tblspace pode não ser contínuo, pois os extent podem estar espalhados por um dispositivo até onde se tenha espaço. Uma vez que um extent foi alocado para uma tabela, este extent nunca será liberado para reutilização por outras tabelas. Se um extent deveria ficar vazio (devido a um volumoso DELETE na tabela), o extent ainda fara parte do tblspace. Porém, o espaço será utilizado novamente quando são inseridas linhas adicionais nesta tabela no futuro.

Numero total de extents

O número total de extents permitidos em uma tabela varia de acordo com o tamanho de página, numero de índices, o numero de coluna por índice, e o tipo de colunas na tabela (VARCHAR, TEXT, ou BYTE). Para sistemas com uma pagina de 2 kbytes, o numero máximo de extents é aproximadamente 200. Sistemas com 4 kbytes podem ter aproximadamente 450 extents. Se uma tabela alcançar o numero de máximo de extents, você precisara descarrega-lá (unload), achar um espaço contínuo para recriar a tabela com menos extents, e então recarregar os dados.
Ter muitos extents pode afetar o desempenho, particularmente em um ambiente com processos volumosos (DSS) onde são selecionados grandes grupos de linhas. Haverá I/O adicional ao trazer as páginas de dados para a memoria compartilhada em virtude de todos os extents que precisaram ser acessados.

Reclamando espaço em extents vazios

Se você quiser reclamar o espaço em extents vazios e torna-lo disponível para o dbspace utilizar em outras tabelas, você pode mudar um dos índices da tabela par um índice CLUSTER, forçando uma regravação da tabela. Isto é discutido em mais detalhes no capítulo Índices e Estratégia de Indexação.

Crescimento dos Extents

Há situações especiais que podem alterar o tamanho dos extents subseqüentes com relação ao tamanho especificado no momento em que a tabela foi criada.

Concatenação (Concatenation).

Concatenação de extents acontece quando um extent de uma tabela é alocado e este extent é fisicamente adjacente a um extent já existente para a mesma tabela. O extent existente é simplesmente aumentado. Este efeito é visto frequentemente ao executar uma grande de carga em uma tabela. Geralmente, cada extent novo alocado será contínuo com a extent já existente. Uma grande tabela pode ser carregada e ao final estar ocupando um único grande extent.

Duplicando (Doubling)

Duplicação do espaço de Extent ocorre quando o número de extent alocados para uma tabela em particular cresce para um múltiplo de 16. O tamanho atual de um novo extent será dobrado.

 

Modificação Manual

O tamanho do primeiro extent subseqüentes é especificado quando a tabela é criada. É possível alterar o tamanho de extent usando o comando ALTER TABLE. Você pode  aumentar ou pode diminuir o tamanho de extent para qualquer extent subseqüente. Isso não alterará o tamanho de extents já alocados.

Limitação de Espaço

Se a quantidade livre de espaço contínuo em um dbspace é menor que o tamanho do extent atual, a quantidade de espaço disponível será alocada ao extent embora seja menor que a quantidade desejada. Será usado o espaço mínimo disponível para o extent.

Modo de Lock da Tabela

Para prevenir erros quando mais de um usuário esta lendo ou modificando dados, o Informix usa um sistema de locks para controlar o acesso. Quando você cria uma tabela que você pode escolher o nível de lock que o Informix usará para a tabela.

Nível de Lock por Pagina ( Page Level)

O lock por pagina estabelece um lock em uma página inteira. Este é o nível de lock default. O lock por pagina provê o método mais eficiente para estabelecer lock em varias linhas. Mas, pelo fato de estar colocando lock em todas as linhas da página, outros usuários não podem acessar estes dados. O lock por pagina diminui a concorrência, ou a disponibilidade dos dados para outros usuários.
Considerando que um lock por pagina, na verdade estará estabelecendo um lock em mais de uma linha com um só recurso, torna-se quando você está processando as linhas na mesma ordem como elas estão colocadas fisicamente em disco. Por exemplo, se você estiver processando uma tabela em sua ordem física, o lock por pagina lhe permite atualizar muitas linhas com menos locks.

Nível de Lock por Linha (Row Level)

O lock por linha estabelecerá locks em uma única linha de cada vez. O lock por linha aumenta a concorrencia porque só uma linha esta com lock. O IBM Informix Dynamic Server usa lock por linha nas tabelas de catálogo de sistema para prover o nível mais alto de concorrência. Quando o numero de linhas com lock é alto, você não só corre o risco por diminuir o numero de locks disponíveis, como torna o overhead do gerenciamento de locks significativo.

O comando CREATE TABLE

CREATE TABLE ordens (
                     order_num           SERIAL NOT NULL,
                     customer_num      INTEGER, 
                     order_date           DATE )
IN dbspace1
EXTENT SIZE 64
NEXT SIZE 32
LOCK MODE row;

O exemplo do comando CREATE TABLE acima, cria uma tabela chamada ordens  com três colunas, a tabela será gravada no dbspace1. 64 kbytes serão necessários, para alocar o primeiro extent. Todo extent subseqüente será alocado com 32 kbytes. Quando o lock for aplicado em algum registro da tabela, será com lock por linha. As tabelas também podem ser fragmentadas em diversos dbspaces para melhorar performance.

Estrutura de uma Pagina

Ao armazenar informações em uma tabela, o tipo de dado armazenado em uma pagina particular é homogêneo; quer dizer, se as linhas de dados são armazenadas na pagina, então a pagina conterá apenas linhas de dados. Se dados de índice são armazenados em uma pagina, então aquela pagina conterá somente dados de índice. Algumas paginas são reservadas para propósitos administrativos.

Todas as paginas usadas no servidor tem a seguinte estrutura de dados:

  • Page header – 24 bytes de informação usados para manter o mapa dos dados na pagina.
  • Timestamp – os últimos 4 bytes na pagina, usado para assegurar a validade das gravações.
  • Slot Table – contem uma entrada de 4 byte para cada linha inserida na pagina. Os 4 bytes armazenam o offset da linha na pagina e do tamanho da linha. A Slot Table é usada para localizar linhas na página.

O resto de espaço na pagina esta disponível para armazenar dados ou informações de índice.

Estimando Tamanho de Linhas e Extents para as Tabelas

Algumas diretrizes para calcular o espaço requerido para o armazenamento de uma tabela são:

  1. Determinar o número de linhas que você  deseja armazenar inicialmente na tabela.
  2. Determinar o comprimento da linha somando o tamanho de todas as colunas na tabela. Colunas BLOB que não são armazenados na tabela usarão 56 bytes para o ponteiro. Tabelas que contem VARCHAR ou colunas BLOB localizadas na tabela são impossíveis de terem seu tamanho calculado com precisão. Você pode usar o tamanho máximo possível ou um tamanho médio, mas o tamanho de linhas resultante será uma estimativa.
  3. Some 4 bytes para a entrada de Slot Table. O resultado é o rowsize (tamanho da linha).
  4. Determinar o tamanho da pagina em bytes subtraindo 28 (para o header) para obter o espaço de página utilizável (pageuse).
  5. Determinar quantas linhas inteiras podem ser gravadas em uma pagina (arredondado para baixo):
    # de linhas em uma pagina = pageuse / rowsize
  6. Determinar quantas paginas são necessárias (arredondando para cima)
    # de paginas = linhas totais / # de linhas em uma pagina
  7. Determinar o espaço total necessário em kbytes, utilizando uma pagina de 2 kbytes ou 4 kbytes, dependendo de seu sistema:
    espaço de disco = #de paginas * tamanho de pagina
  8. O resultado em #7 é o tamanho do FIRST EXTENT. Repita os passos, baseado no crescimento antecipado, para determinar o tamanho do NEXT EXTENT.

Alternativa: método “rough estimate” (Estimativa áspera):

Estimativa áspera: (#de linhas * tamanho de linha * 125%)

Se o tamanho de linha é maior que o espaço utilizável em uma pagina, o Dynamic Server dividira a linha entre homepages e mainder pages. O numero de páginas de dados é calculado assim:
# de homepages + #de remainder pages.

Calculando Extent Size: exemplo usando a tabela clientes

Assuma que a base de clientes atual é 10.000 e é esperado que o crescimento anual seja de 5.000.

  1. Determine o numero de linhas que você deseja armazenar inicialmente na tabela (atual + crescimento = total).
    FIRST EXTENT baseado na base de clientes atuais + resto do ano = 10.000 + 5.000 = 15.000 linhas
    NEXT EXTENT baseado no crescimento anual = 5.000 linhas
  2. Determinar o comprimento da linha somando o tamanho de todas as colunas.
  3. Nome Coluna Tipo Dados Bytes
    num_cliente serial 4
    nome char(15) 15
    sobrenome char(15) 15
    endereco char(20) 20
    endereco_compl char(20) 20
    cidade char(15) 15
    estado char(2) 2
    cep char(5) 5
    telefone char(18) 18
    Total   114
  4. Some 4 bytes para a Slot Table.
    Rowsize = 114 + 4 = 118
  5. Determinar o tamanho da pagina subtraindo 28 para obter o espaço de pagina utilizável.
    Para pagina de 2 kbytes: 2048-28 = 2020
  6. Determinar quantas linhas inteiras podem se ajustar em uma pagina (arredondamento para baixo):
    2020/118 = 17.1 = 17 linhas
  7. Determinar quantas paginas são necessárias (arredondamento para cima):
    FIRST EXTENT = 15.000/17 = 823,5 = 824 paginas de dados
    NEXT EXTENT = 5.000/17 = 294,1 = 295 paginas de dados
  8. Determinar o espaço total necessário em kbytes:
    FIRST EXTENT = 824 * 2 kb = 1648 Kb
    NEXT EXTENT = 295 * 2 kb = 590 kb

O banco de dados Sysmaster

O System Monitoring Interface (SMI) oferece uma visão instantânea das informações de status do sistema para uma instancia Informix inteira. O banco de dados sysmaster engloba as tabelas usadas pelo SMI para recuperar estas informações. Há um banco de dados sysmaster para cada sistemas IDS. O banco de dados é criado automaticamente na primeira vez que o sistema IDS é iniciado.
A maioria das tabelas do sysmaster não armazenam qualquer dado. Ao invés disso, a estrutura do banco de dados aponta para as estruturas em memoria. Quando você examinar as tabelas do sysmaster usando SQL, o comando SELECT tem acesso aos dados em tempo real na memoria. Por isso, os dados recuperados de uma tabela, podem, não estar sincronizados com os dados recuperados de uma outra tabela.

Um banco de dados adicional, o banco de dados sysutils, também é criado automaticamente quando o sistema IDS é iniciado. Este banco de contém informações utilizadas pelas ferramentas de archive.

A seguir, são descritos alguns comandos SQL para obter informações sobre sua instancia Infomix no banco de dados sysmaster.:

  • sysdbspaces: todos os dbspaces na instancia, o numero de chunks, assim como se o dbspace é um blobspace ou dbspace temporários, status do dbspace:
    SELECT dbsnum, name, owner, nchunks, is_temp, isblobspace, flags
    FROM sysdbspaces;
  • syschunks: Numero de paginas livres, e o Numero de paginas usadas em chunks para um dbspace particular:
    SELECT syschunks.dbsnum, chknum, nxchknum, sysdbspace.name, chksize, nfree
    FROM syschunks, sysdbspaces
    WHERE syschunks.dbsnum = sysdbspaces.dbsnum
    AND sysdbspaces.name = “nome do dbspace”
  • sysdatabases: todos os bancos de dados da instancia e o status de seu modo log
    SELECT name, owner, created, is_logging, is_buff_log, is_ansi
    FROM sysdatabases;
  • systabnames: todas tabelas no banco de dados e seu dono
    SELECT partnum, dbsname, owner, tabname
    FROM systabnames
    WHERE dbsname = “nome do banco”
  • syssessions: usuario por sessão, nome do host, tempo conectado
    SELECT sid, username, uid, hostname, connected
    FROM syssession;
  • sysextents: cada extent no banco de dados, e a tabela associada a ela
    SELECT dbsname, tabname, start, size
    FROM sysextents;

2 comentários:

  1. É POSSIVEL DA UM LOCK DE TABELA, NO PROCESSO DE LOAD/UNLOAD DA TABELA DB2. SE EXISTE, QUAL O PARÂMENTRO E OS PRÓ E CONTRAS.

    ResponderExcluir
  2. Ola Colega,
    Se entendi sua pergunta refere-se ao banco de dados DB2, certo?

    Bem a ideia do Lock Table é igual em todos os bancos de dados, as vezes o que muda é somente como se declara isso. Sabemos que o objetivo de colocar uma tabela em modo LOCK, é simplesmente conseguir acesso exclusivo a ela, muito util quando se tem muito acesso a determinada tabela, e na hora que vc for fazer uma operacao de escrita nos dados é obvio que tem que ter exclusividade senao imagina como ficaria seus dados....., para carregar dados ou descarregar dados, nao vejo o porque de colocar a tabela em modo LOCK, pois primeiramente vc precisaria fazer algumas declarações , isso para banco em modo transacional tipo:

    BEGIN WORK
    LOCK TABLE ordem IN EXCLUSIVE MODE
    UNLOAD TO ordem.unl
    SELECT * FROM ordem
    .....
    .....
    COMMIT WORK

    entende, isso somente seria util se voce nao quisesse que quando vc estiver fazendo o UNLOAD por exemplo ninguem acesse essa tabela. Tipo vc congelaria a tabela para fazer o UNLOAD ou LOAD.

    Talvez no LOAD seria mais logico ate, para nao correr o risco de duplicar o registro, mas ai entra a questão da engenharia do banco, index, prmary key, ...

    ResponderExcluir