Preparação para o Exame 730 DB2 9 Fundamentals, Parte 5: Trabalhando com Objetos do DB2

Este tutorial discute tipos de dados, tabelas, visualizações e índices, conforme definido no DB2 9. Ele explica os recursos desses objetos, como criar e manipular os mesmos usando Linguagem de Consulta Estruturada (SQL) e como podem ser usados em um aplicativo. Este tutorial é o quinto de uma série de sete tutoriais que podem ser usados para ajudar a se preparar para o exame 730 DB2 9 Fundamentals Certification.

Hana Curtis, IBM Certified Solutions Expert, IBM

Hana Curtis é membro líder da equipe DB2 Functional Verification Testing no Laboratório de Software da IBM Toronto. Anteriormente, ela era membro da equipe DB2 Integration, trabalhando com o DB2 e o WebSphere, consultora de banco de dados trabalhando com Parceiros de Negócios da IBM para ativar seus aplicativos para o DB2 e membro da equipe de desenvolvimento do DB2. Hana é uma das autoras do livro: DB2 SQL Procedural Language for Linux, UNIX, and Windows (Prentice Hall, 2003). Ele tem as seguintes certificações: IBM Certified Solutions Expert: DB2 UDB V8.1 Database Administration for UNIX, Windows, and OS/2, IBM Certified Solutions Expert: DB2 UDB V8.1 Family Application Development, IBM Certified Specialist: DB2 V8.1 User.



16/Set/2011

Antes de Iniciar

Sobre esta série

Pensando em buscar certificação nos fundamentos do DB2 (Exame 730)? Nesse caso, você está no lugar certo. Esta série de sete tutoriais de preparação para certificação de DB2 cobre todo o básico -- os tópicos que você precisará entender antes de ler a primeira questão do exame. Mesmo se não estiver planejando em buscar certificação imediatamente, este conjunto de tutoriais é um ótimo local para começar a aprender o que há de novo no DB2 9.

Sobre este tutorial

O material deste tutorial cobre principalmente os objetivos da Seção 5 do exame, intitulada "Working with DB2 Objects". É possível visualizar esses objetivos em: http://www-03.ibm.com/certify/tests/obj730.shtml.

Os tópicos cobertos neste tutorial incluem:

  • Uma descrição dos tipos de dados integrados que o DB2 fornece e que são apropriados para uso ao definir uma tabela. (Para um tratamento diferente de tipo de dados, consulte o quarto tutorial nesta série).
  • Uma introdução a tipos de dados avançados.
  • Tabelas, visualizações e índices.
  • Uma explicação dos vários tipos de restrições e seus usos.

Objetivos

Após concluir este tutorial, você deve poder:

  • Entender tipos de dados e tipos de dados avançados
  • Criar tabelas, visualizações e índices em um banco de dados DB2
  • Entender os recursos e uso de restrições exclusivas, restrições de integridade referenciais e restrições de verificação de tabela
  • Usar visualizações para restringir acesso aos dados
  • Entender os recursos de índices

Requisitos do Sistema

Não é necessário ter uma cópia do DB2 para concluir este tutorial. No entanto, se quiser, é possível fazer download de uma versão de avaliação gratuita do IBM DB2 9 para trabalhar com este tutorial.


Tipos de Dados

O DB2 fornece uma rica e flexível seleção de tipos de dados. O DB2 vem com tipos de dados básicos, como INTEGER, CHAR e DATE. Também inclui recursos para criar tipos de dados definidos pelo usuário (UDTs) de forma que seja possível criar tipos de dados complexos não tradicionais adequados para os ambientes de programação complexos dos dias de hoje. A escolha de qual tipo usar em uma determinada situação depende do tipo e da faixa de informações que são classificadas na coluna.

Há quatro categorias de tipos de dados integrados: numérico, cadeia de caractere, data/hora e XML.

Os tipos de dados definidos pelo usuário são categorizados como: distinto, estruturado e de referência.

Tipos de dados numéricos

Há três categorias de tipos de dados numéricos, conforme diagramado na figura anterior. Esses tipos variam na faixa e na precisão de dados numéricos que podem armazenar.

  • Número inteiro: SMALLINT, INTEGER e BIGINT são usados para armazenar números inteiros. Por exemplo, uma contagem de inventário poderia ser definida como INTEGER. SMALLINT pode armazenar números inteiros de -32.768 a 32.767 em 2 bytes. INTEGER pode armazenar números inteiros de -2.147.483.648 a 2.147.483.647 em 4 bytes. BIGINT pode armazenar números inteiros de -9.223.372.036.854.775.808 a 9.223.372.036.854.775.807 em 8 bytes.
  • Decimal: DECIMAL é usado para armazenar números com partes fracionais. Para definir esse tipo de dados, especifique uma precisão (p), que indica o número total de dígitos e uma escala (s), que indica o número de dígitos à direita da casa decimal. Uma coluna definida por DECIMAL(10,2) que contenha valores de moeda poderia conter valores de até 99999999,99 dólares. A quantia de armazenamento necessário no banco de dados depende da precisão e é calculada pela fórmula p/2 +1. Portanto, DECIMAL(10,2) requereria 10/2 + 1 ou 6 bytes.
  • Vírgula flutuante: REAL e DOUBLE são usados para armazenar aproximações de números. Por exemplo, medidas científicas muito pequenas ou muito grandes poderiam ser definidas como REAL. REAL pode ser definido com um comprimento entre 1 e 24 dígitos e requer 4 bytes de armazenamento. DOUBLE pode ser definido com um comprimento entre 25 e 53 dígitos e requer 8 bytes de armazenamento. FLOAT pode ser usado como um sinônimo para REAL ou DOUBLE.

Tipos de dados de cadeia de caractere

O DB2 fornece diversos tipos de dados para armazenar dados de caracteres ou cadeias de caractere, conforme diagramado na figura anterior. Escolha um tipo de dados com base no tamanho da cadeia de caractere que você irá armazenar e quais dados estarão na cadeia de caractere.

Os tipos de dados a seguir são usados para armazenar cadeias de caracteres de byte único:

  • CHAR ou CHARACTER é usado para armazenar cadeias de caracteres de comprimento fixo de até 254 bytes. Por exemplo, um fabricante pode designar um identificador a uma peça com um comprimento específico de oito caracteres e, portanto, armazenar esse identificador no banco de dados como uma coluna de CHAR(8).
  • VARCHAR é usado para armazenar cadeias de caracteres de comprimento variável. Por exemplo, um fabricante pode lidar com diversas peças com identificadores de diferentes comprimentos e, assim, armazenar esses identificadores como uma coluna de VARCHAR(100). O comprimento máximo de uma coluna VARCHAR é 32.672 bytes. No banco de dados, dados VARCHAR usa somente o espaço necessário.

Os tipos de dados a seguir são usados para armazenar cadeias de caracteres de byte duplo:

  • GRAPHIC é usado para armazenar cadeias de caracteres de byte duplo de comprimento fixo. O comprimento máximo de uma coluna GRAPHIC é 127 caracteres.
  • VARGRAPHIC é usado para armazenar cadeias de caracteres de byte duplo de comprimento variável. O comprimento máximo de uma coluna VARGRAPHIC é 16.336 caracteres.

O DB2 também fornece tipos de dados para armazenar cadeias de caracteres de dados muito longas. Todos os tipos de dados da cadeia de caractere longa têm características semelhantes. Primeiro, os dados não são armazenados fisicamente com dados de linha no banco de dados, o que significa que processamento adicional é necessário para acessar esses dados. Tipos de dados longos podem ser definidos para um comprimento de até 2 GB. No entanto, somente o espaço necessário é realmente usado. Os tipos de dados longos são:

  • LONG VARCHAR
  • CLOB (character large object)
  • LONG VARGRAPHIC
  • DBCLOB (objeto grande de caractere de byte duplo)
  • BLOB (objeto binário grande)

Tipos de dados de data/hora

O DB2 fornece três tipos de dados para armazenar datas e horas:

  • DATE
  • TIME
  • TIMESTAMP

Os valores desses tipos de dados são armazenados no banco de dados em um formato interno; no entanto, aplicativos podem manipular os mesmos como cadeias de caracteres. Quando um desses tipos de dados for recuperado, ele é representado como uma sequência de caracteres. Coloque o valor entre aspas ao atualizar esses tipos de dados.

O DB2 fornece funções para manipular valores de data/hora. Por exemplo, é possível determinar ao dia da semana de um valor de data usando as funções DAYOFWEEK ou DAYNAME . Use a função DAYS para calcular quantos dias estão entre duas datas. O DB2 também fornece registros especiais para gerar a data, hora ou registro de data e hora atual com base no relógio TOD. Por exemplo, CURRENT DATE retorna uma cadeia de caractere que representa a data atual no sistema.

O formato dos valores de data e hora depende do código do país do banco de dados, que é especificado quando o banco de dados for criado. Há diversos formatos disponíveis: ISO, USA, EUR e JIS. Por exemplo, se seu banco de dados estiver usando o formato do EUA, o formato de valores de data seria dd/mm/aaaa. É possível alterar o formato usando a opção DATETIME do comando BIND ao criar seu aplicativo.

Há um formato único para o tipo de dados TIMESTAMP. A representação de cadeia de caractere é yyyy-mm-dd-hh.mm.ss.nnnnnn.

tipo de dados XML

O DB2 fornece o tipo de dados XML para armazenar documentos XML bem formados.

Valores em colunas XML são armazenados em uma representação interna diferente dos tipos de dados de cadeia de caractere. Para armazenar dados XML em uma coluna de tipo de dados XML, transforme os dados usando a função XMLPARSE. Um valor de tipo de dados XML pode ser transformado em um valor de cadeia de caractere serializado que representa o documento XML usando a função XMLSERIALIZE. O DB2 fornece muitas outras funções integradas para manipular tipos de dados XML.

Tipos de dados definidos pelo usuário

O DB2 permite definir tipos de dados que atendem seu aplicativo. Há três tipos de dados definidos pelo usuário:

  • Distinto definido pelo usuário: Define um novo tipo de dados baseado em um tipo integrado. Esse novo tipo tem os mesmo recursos do tipo integrado, mas é possível usá-lo para assegurar que somente valores do mesmo tipo são comparados. Por exemplo, é possível definir um tipo de dólar canadense (CANDOL) e um tipo de dólar americano (USADOL), ambos baseados em DECIMAL(10,2). Ambos os tipos são baseados no mesmo tipo integrado, mas não será possível compará-los a menos que uma função de conversão seja aplicada. As instruções CREATE TYPE criam os UDTs CANDOL e USADOL:
    CREATE DISTINCT TYPE CANDOL AS DECIMAL(10,2) WITH COMPARISONS
    CREATE DISTINCT TYPE USADOL AS DECIMAL(10,2) WITH COMPARISONS

    O DB2 gera funções automaticamente para executar cast entre o tipo base e o tipo distinto e operadores de comparação para comparar instâncias do tipo distinto. As instruções a seguir mostram como criar uma tabela com uma coluna do tipo CANDOL e, em seguida, inserir dados na tabela usando a função de cast CANDOL:

    CREATE TABLE ITEMs (ITEMID CHAR(5), PRICE CANDOL )
    INSERT INTO ITEMs VALUES('ABC11',CANDOL(30.50) )
  • Estruturado definido pelo usuário: Cria um tipo que consiste em diversas colunas de tipos integrados. Em seguida, use esse tipo estruturado ao criar uma tabela. Por exemplo, é possível criar um tipo estruturado denominado ADDRESS que contém dados para número de rua, nome de rua, cidade, etc. Em seguida, é possível usar esse tipo ao definir outras tabelas, como funcionários ou fornecedores, pois os mesmos dados são necessários para ambos. Além disso, os tipos estruturados podem ter subtipos em uma estrutura hierárquica. Isso permite armazenar objetos que pertencem a uma hierarquia no banco de dados.
  • Referência definido pelo usuário: Ao usar tipos estruturados, é possível definir referências para linhas em outra tabela usando tipos de referência. Essas referências parecem semelhantes a restrições referenciais; no entanto, eles não impingem relacionamentos entre as tabelas. Referências em tabelas permitem especificar consultas em uma maneira diferente.

Tipos estruturados e de referência definidos pelo usuário são um tópico avançado; as informações apresentadas aqui servem somente como uma introdução a esses tipos.

DB2 Extenders

Os DB2 Extenders fornecem suporte a tipos de dados complexos não tradicionais. Eles são empacotados separadamente do código do servidor DB2 e instalados no servidor e em cada banco de dados que usa o tipo de dados.

Há muitos DB2 Extenders disponíveis da IBM e de fornecedores de software independentes. Os quatro primeiros extensores fornecidos pela IBM são para classificar dados de áudio, vídeo, imagem e texto. Por exemplo, use o DB2 Image Extender para armazenar uma imagem de uma capa de livro e o DB2 Text Extender para armazenar o texto de um livro. Agora, há diversos outros extensores disponíveis. O DB2 Spatial Extender poderia ser usar para classificar e analisar dados espaciais e o XML Extender para gerenciar documentos XML.

Os DB2 Extenders são implementados usando os recursos de tipos definidos pelo usuário e funções definidas pelo usuário (UDFs). Cada extensor é fornecido com um ou mais UDTs, UDFs para operação no UDT, interfaces de programação de aplicativos (APIs) específicas e, possivelmente, outras ferramentas. Por exemplo, o DB2 Image Extender inclui:

  • O UDT DB2IMAGE
  • UDFs para inserir e recuperar de uma coluna DB2IMAGE
  • APIs a procurar com base nas características de imagens

Antes de usar esses tipos de dados, instale o suporte a extensor no banco de dados. O processo de instalação para cada extensor define os UDTs e UDFs necessários no banco de dados. Após ter feito isso, é possível usar os UDTs ao definir uma tabela e as UDFs ao trabalhar com dados. (Para saber mais sobre DB2 Extenders, consulte o primeiro tutorial desta série.)

O tipo de dados XML, que é novo no DB2 9, fornece recursos avançados para tratar de documentos XML. Aplicativos e dados que usam o XML Extender devem ser migrados para usarem o suporte a XML nativo no DB2.


Tabelas

Todos os dados são armazenados em tabelas no banco de dados. Uma tabela consiste em uma ou mais colunas de vários tipos de dados. Os dados são armazenados em linhas ou registros.

Tabelas são definidas usando a instrução SQL CREATE TABLE . O DB2 também fornece uma ferramenta de GUI, o DB2 Control Center, para criar tabelas, que cria uma tabela com base nas informações especificadas. Também gera a instrução SQL CREATE TABLE , que pode ser usada em um script ou programa de aplicativo posteriormente.

Um banco de dados tem um conjunto de tabelas, chamado tabelas de catálogos do sistema, que retêm informações sobre todos os objetos no banco de dados. O DB2 fornece visualizações para as tabelas de catálogos do sistema base. A visualização de catálogo SYSCAT.TABLES contém uma linha para cada tabela definida no banco de dados. SYSCAT.COLUMNS contém uma linha para cada coluna de cada tabela no banco de dados. Verifique as visualizações de catálogos usando as instruções SELECT , exatamente como qualquer outra tabela do banco de dados; no entanto, não é possível modificar os dados usando as instruções INSERT, UPDATE ou DELETE . As tabelas são atualizadas automaticamente como resultado de instruções DD (DDL), como CREATE, e outras operações, como RUNSTATS.

Criando uma tabela

Use a instrução SQL CREATE TABLE para definir uma tabela no banco de dados. A instrução a seguir cria uma tabela simples denominada BOOKS que contém três colunas:

CREATE TABLE BOOKS ( BOOKID INTEGER, 
                     BOOKNAME VARCHAR(100), 
                     ISBN CHAR(10) )

Também é possível usar a instrução SQL CREATE TABLE para criar uma tabela que é como outra tabela ou visualização no banco de dados:

CREATE TABLE MYBOOKS LIKE BOOKS

Essa instrução cria uma tabela com as mesmas colunas que a tabela ou visualização original. As colunas da nova tabela têm os mesmos nomes, tipos de dados e atributos de capacidade de anulação que as colunas da antiga. Também é possível especificar cláusulas que copiam outros atributos, como padrões de coluna e atributos de identificação.

Há muitas opções disponíveis para a instrução CREATE TABLE (elas são apresentadas nas seções a seguir à medida que novos conceitos são apresentados). Os detalhes da instrução SQL CREATE TABLE podem ser localizados na Referência de SQL (consulte Recursos ).

Após ter criado uma tabela, há várias maneiras de preenchê-la com dados. A instrução INSERT permite inserir uma linha ou diversas linhas de dados na tabela. O DB2 também fornece utilitários para inserir grandes quantias de dados de um arquivo. O utilitário IMPORT insere linhas usando instruções INSERT . Ele é projetado para carregar pequenas quantias de dados no banco de dados. O utilitário LOAD, destinado para carregamento de grandes volumes de dados, insere linhas diretamente nas páginas de dados no banco de dados e é muito mais rápido do que o utilitário IMPORT.

Armazenando tabelas no banco de dados

Tabelas são armazenadas no banco de dados em espaços de tabelas. Espaços de tabelas têm espaço físico alocado a eles. Crie o espaço de tabela antes de criar a tabela.

Ao criar uma tabela, deixe o DB2 colocar a tabela em um espaço de tabela padrão ou especifique o espaço de tabela no qual gostaria que a tabela resida. A instrução CREATE TABLE a seguir coloca a tabela BOOKS no espaço de tabela BOOKINFO:

CREATE TABLE BOOKS ( BOOKID INTEGER, 
                     BOOKNAME VARCHAR(100), 
                     ISBN CHAR(10) ) 
             IN BOOKINFO

Apesar de os espaços de tabela não serem cobertos em detalhes aqui, é importante entender que definir espaços de tabelas de forma apropriada tem um efeito no desempenho e sustentabilidade do banco de dados. Para obter informações adicionais sobre espaços de tabelas, consulte o segundo tutorial desta série.

Alterando uma tabela

Use a instrução SQL ALTER TABLE para alterar características de uma tabela. Por exemplo, é possível incluir ou eliminar:

  • Uma coluna
  • Uma chave primária
  • Uma ou mais restrições exclusivas ou referenciais
  • Uma ou mais restrições de verificação

A instrução a seguir inclui uma coluna chamada BOOKTYPE na tabela BOOKS:

ALTER TABLE BOOKS ADD BOOKTYPE CHAR(1)

Também é possível alterar características de colunas específicas em uma tabela:

  • Os atributos de identidade de uma coluna
  • O comprimento de uma coluna de cadeia de caractere
  • O tipo de dados de uma coluna
  • A capacidade de anulação de uma coluna
  • A restrição de uma coluna

Há restrições para alteração de colunas:

  • Ao alterar o comprimento de uma coluna de cadeia de caractere, é possível somente aumentar o comprimento.
  • Ao alterar o tipo de dados de uma coluna, o novo tipo de dados deve ser compatível com o tipo de dados existente. Por exemplo, é possível converter colunas CHAR em colunas VARCHAR, mas não é possível convertê-las em colunas GRAPHIC ou numéricas. As colunas numéricas podem ser convertidas para qualquer outro tipo de dados numérico, desde que o novo tipo de dados seja grande o suficiente para reter os valores. Por exemplo, converta uma coluna INTEGER em BIGINT, mas, uma coluna DECIMAL(10,2) não pode ser convertida em SMALLINT.
  • Cadeias de caracteres de comprimento fixo podem ser convertidas em cadeia de caractere de comprimento variável e cadeias de caracteres de comprimento variável podem ser convertidas em comprimento fixo. Por exemplo, um CHAR(100) pode ser convertido em VARCHAR(150). Existem restrições semelhantes para cadeias de caracteres gráficas de comprimento variável.

A instrução a seguir altera o DATATYPE da coluna BOOKNAME de VARCHAR(100) para VARCHAR(200) e altera a capacidade de anulação da coluna ISBN para NOT NULL:

ALTER TABLE BOOKS ALTER BOOKNAME SET DATA TYPE VARCHAR(200) ALTER ISBN SET NOT NULL

Determinadas características de uma tabela não podem ser alteradas. Por exemplo, não é possível alterar o espaço de tabela no qual a tabela reside, a ordem das colunas nem alterar o tipo de dados de algumas colunas. Para alterar características como essas, salve os dados da tabela, elimine a tabela e recrie-a.

Eliminando uma tabela

A instrução DROP TABLE remove uma tabela do banco de dados, excluindo os dados e a definição de tabela. Se houver índices ou restrições definidos na tabela, eles são eliminados também.

A instrução DROP TABLE a seguir exclui a tabela BOOKS do banco de dados:

DROP TABLE BOOKS

Opções de coluna NOT NULL, DEFAULT e GENERATED

As colunas de uma tabela são especificadas na instrução CREATE TABLE por um nome de coluna e tipo de dados. As colunas podem ter cláusulas adicionais especificadas que restringem os dados na coluna.

Por padrão, uma coluna permite valores nulos. Se não quiser permitir valores nulos, especifique a cláusula NOT NULL para a coluna. Especifique um valor padrão usando a cláusula WITH DEFAULT e um valor padrão. A instrução CREATE TABLE a seguir cria uma tabela chamada BOOKS, em que a coluna BOOKID não permite valores nulos e o valor padrão para BOOKNAME é TBD:

CREATE TABLE BOOKS ( BOOKID INTEGER NOT NULL, 
                     BOOKNAME VARCHAR(100) WITH DEFAULT 'TBD', 
                     ISBN CHAR(10) )

Na tabela BOOKS, o BOOKID é um número exclusivo designado a cada livro. Em de fazer com que o aplicativo gere o identificador, é possível especificar que o DB2 deve gerar um BOOKID usando a cláusula GENERATED ALWAYS AS IDENTITY :

CREATE TABLE BOOKS ( BOOKID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
                                             (START WITH 1, INCREMENT BY 1), 
                     BOOKNAME VARCHAR(100) WITH DEFAULT 'TBD', 
                     ISBN CHAR(10) )

GENERATED ALWAYS AS IDENTITY faz com que um BOOKID seja gerado para cada registro. O primeiro valor gerado é 1 e os valores subsequentes são gerados incrementando o valor anterior em 1.

Também use a opção GENERATED ALWAYS para que o DB2 calcule o valor de uma coluna automaticamente. O exemplo a seguir define uma tabela chamada AUTHORS, com as colunas FICTIONBOOKS e NONFICTIONBOOKS que retêm contagens de livros de ficção e não ficção, respectivamente. A coluna TOTALBOOKS é calculada incluindo as colunas FICTIONBOOKS e NONFICTIONBOOKS:

CREATE TABLE AUTHORS (AUTHORID INTEGER NOT NULL PRIMARY KEY,
                      LNAME VARCHAR(100),
                      FNAME VARCHAR(100),
                      FICTIONBOOKS INTEGER,
                      NONFICTIONBOOKS INTEGER,
                      TOTALBOOKS INTEGER GENERATED ALWAYS 
                                 AS (FICTIONBOOKS + NONFICTIONBOOKS) )

Restrições

O DB2 fornece diversas maneiras para controlar quais dados podem ser armazenados em uma coluna. Esses recursos são chamados restrições ou regras que o gerenciador do banco de dados impinge a uma coluna de dados ou conjunto de colunas.

O DB2 fornece três tipos de restrições: exclusiva, integridade referencial e verificação de tabela.

As seções a seguir fornecem descrições detalhadas de cada tipo de restrição.

Restrições exclusivas

Restrições exclusivas são usadas para assegurar que valores de uma coluna são exclusivos. Restrições exclusivas podem ser definidas para uma ou mais colunas. Cada coluna incluída na restrição exclusiva deve ser definida como NOT NULL.

Restrições exclusivas podem ser definidas como a restrição PRIMARY KEY ou UNIQUE . Elas são definidas quando uma tabela é criada como parte da instrução SQL CREATE TABLE ou incluída após a tabela ser criada usando a instrução ALTER TABLE .

Quando se define uma PRIMARY KEY e quando se define uma chave UNIQUE ? Isso depende da natureza dos dados. No exemplo anterior, a tabela BOOKS tem uma coluna BOOKID que é usada para identificar um livro de forma exclusiva. Esse valor também é usado em outras tabelas que contêm informações relacionadas a esse livro. Nesse caso, você definiria BOOKID como a chave primária. O DB2 permite somente uma chave primária a ser definida em uma tabela.

A coluna de número ISBN precisa ser exclusiva, mas não é um valor referido no banco de dados de outra forma. Nesse caso, a coluna ISBN é definida como UNIQUE:

CREATE TABLE BOOKS (BOOKID INTEGER NOT NULL PRIMARY KEY,
                    BOOKNAME VARCHAR(100),
                    ISBN CHAR(10) NOT NULL CONSTRAINT BOOKSISBN UNIQUE )

A palavra-chave CONSTRAINT permite especifica um nome para a restrição. Neste exemplo, o nome da restrição exclusiva é BOOKSISBN. Use esse nome na instrução ALTER TABLE se quiser eliminar a restrição específica.

O DB2 permite definir somente uma chave principal em uma tabela; no entanto, é possível definir diversas restrições exclusivas.

Sempre que definir uma restrição PRIMARY KEY ou UNIQUE em uma coluna, o DB2 cria um índice exclusivo para impingir exclusividade na coluna. O DB2 não permite criar mais de um índice exclusivo definido nas mesmas colunas. Portanto, não é possível definir uma restrição PRIMARY KEY e UNIQUE nas mesmas colunas. Por exemplo, ambas as instruções a seguir com relação à tabela BOOKS falham, pois uma PRIMARY KEY já existe:

ALTER TABLE BOOKS ADD  CONSTRAINT UNIQUE (BOOKID)
CREATE UNIQUE INDEX IBOOKS ON BOOKS (BOOKID)

Restrições de integridade referencial

Restrições de integridade referencial são usadas para definir relacionamentos entre tabelas e asseguram que esses relacionamentos permaneçam válidos. Suponhamos que você tenha uma tabela que retém informações sobre autores e outra tabela que lista os livros que esses autores escreveram. Há um relacionamento entre a tabela BOOKS e a tabela AUTHORS -- cada livro tem um autor e esse autor deve existir na tabela AUTHOR. Cada autor tem um identificador exclusivo na coluna AUTHORID. O AUTHORID é usado na tabela BOOKS para identificar o autor de cada livro. Para definir esse relacionamento, defina a coluna AUTHORID da tabela AUTHORS como uma chave primária e, em seguida, defina uma chave estrangeira na tabela BOOKS para estabelecer o relacionamento com a coluna AUTHORID na tabela AUTHORS:

CREATE TABLE AUTHORS (AUTHORID INTEGER NOT NULL PRIMARY KEY,
                      LNAME VARCHAR(100),
                      FNAME VARCHAR(100))
CREATE TABLE BOOKS (BOOKID INTEGER NOT NULL PRIMARY KEY,
                    BOOKNAME VARCHAR(100),
                    ISBN CHAR(10),
                    AUTHORID INTEGER REFERENCES AUTHORS)

A tabela que tem uma chave primária que está relacionada a outra tabela -- AUTHOR, aqui -- é chamada de tabela-pai. A tabela à qual a tabela-pai está relacionada -- BOOKS, aqui -- é chamada de tabela dependente. É possível definir mais de uma tabela dependente em uma única tabela-pai.

Também é possível definir relacionamentos entre linhas da mesma tabela. Nesse caso, a tabela-pai e as tabelas dependentes são a mesma tabela.

Ao definir restrições referenciais em um conjunto de tabelas, o DB2 impinge regras de integridade referencial nessas tabelas quando operações de atualização são executadas com relação a elas:

  • O DB2 assegura que somente dados válidos sejam inseridos em colunas em que restrições de integridade referenciais sejam definidas. Isso significa que você sempre deve ter uma linha na tabela-pai com um valor de chave que seja igual ao valor da chave estrangeira na linha que está sendo inserida em uma tabela dependente. Por exemplo, se um novo livro estiver sendo inserido na tabela BOOKS com um AUTHORID igual a 437, então deve haver uma linha na tabela AUTHORS em que AUTHORID é 437.
  • O DB2 também impinge regras quando linhas que têm linhas dependentes em uma tabela dependente são excluídas de uma tabela-pai. A ação que o DB2 executa depende da regra de exclusão definida na tabela. Há quatro regras que podem ser especificadas: RESTRICT, NO ACTION, CASCADE e SET NULL.
    • Se RESTRICT ou NO ACTION for especificado, o DB2 não permite que a linha pai seja excluída. As linhas das tabelas dependentes devem ser excluídas antes da linha na tabela-pai. Esse é o padrão, de forma que essa regra se aplica às tabelas AUTHORS e BOOKS conforme definida até agora.
    • Se CASCADE for especificado, então excluir uma linha da tabela-pai também exclui automaticamente as linhas dependentes de todas as tabelas dependentes.
    • Se SET NULL for especificado, então a linha pai é excluída da tabela-pai e o valor da chave estrangeira nas linhas dependentes é configurado para nulo (se anulável).
  • Ao atualizar valores de chaves na tabela-pai, há duas regras que podem ser especificadas: RESTRICT e NO ACTION. RESTRICT não permite que um valor de chave seja atualizado se houver linhas dependentes em uma tabela dependente. NO ACTION faz com que a operação de atualização em um valor de chave-pai seja rejeitada se, no final da atualização, houver linhas dependentes em uma tabela dependente que não têm uma chave-pai na tabela-pai.

Restrições de verificação de tabela

Restrições de verificação de tabela são usadas para verificar se essa coluna não viola regras definidas para a coluna e para restringir os valores em uma determinada coluna de uma tabela. O DB2 assegura que a restrição não seja violada durante inserções e atualizações.

Suponhamos que você inclua uma coluna na tabela BOOKS para um tipo de livro e os valores que você deseja sejam F (ficção) e N (não ficção). É possível incluir uma coluna BOOKTYPE com uma restrição de verificação da seguinte forma:

ALTER TABLE BOOKS ADD BOOKTYPE CHAR(1) CHECK (BOOKTYPE IN ('F','N') )

É possível definir restrições de verificação ao criar a tabela ou incluí-las posteriormente usando a instrução SQL ALTER TABLE . É possível modificar restrições de verificação, eliminando e, em seguida, recriando-as usando a instrução SQL ALTER TABLE .


Visualizações

Visualizações permitem que diferentes usuários ou aplicativos vejam os mesmos dados de diferentes maneiras. Isso não apenas torna os dados mais simples de acessar, mas também podem ser usados para restringir quais linhas e colunas usuários visualizam ou atualizam.

Por exemplo, suponhamos que uma empresa tenha uma tabela que contém informações sobre seus funcionários. Um gerente precisa ver informações de endereço, número de telefone e salário sobre somente seus funcionários, enquanto que um aplicativo de diretório precisa ver todos os funcionários da empresa juntamente com seu endereço e números de telefones, mas não seus salários. É possível criar uma visualização que mostre todas as informações dos funcionários de um departamento específico e outra que mostre somente nome, endereço e número de telefone de todos os funcionários.

Para o usuário, uma visualização parece apenas uma tabela. Exceto para a definição de visualização, uma visualização não consome espaço no banco de dados; os dados apresentados em uma visualização são derivados de outra tabela. É possível criar uma visualização em uma tabela (ou tabelas) existente, em outra visualização ou alguma combinação das duas. Uma visualização definida em outra visualização é chamada de uma visualização aninhada.

É possível definir uma visualização com nomes de colunas que são diferentes dos nomes de colunas correspondentes da tabela base. Também é possível definir visualizações que verificam se dados inseridos ou atualizados permanecem dentro das condições da visualização.

A lista de visualizações definidas no banco de dados é armazenada na tabela de catálogos do sistema SYSIBM.SYSVIEWS, que também tem uma visualização definida nela chamada SYSCAT.VIEWS. O catálogo do sistema também tem um SYSCAT.VIEWDEP que, para cada visualização definida no banco de dados, tem uma linha para cada visualização ou tabela dependente dessa visualização. Além disso, cada visualização tem uma entrada em SYSIBM.SYSTABLES e entradas em SYSIBM.SYSCOLUMNS, pois as visualizações podem ser usadas exatamente como tabelas.

Criando uma visualização

A instrução SQL CREATE VIEW é usada para definir uma visualização. Uma instrução SELECT é usada para especificar quais linhas e colunas são apresentadas na visualização.

Por exemplo, imagine que deseja criar uma visualização que mostra somente os livros de não ficção em nossa tabela BOOKS:

CREATE VIEW NONFICTIONBOOKS AS 
       SELECT * FROM BOOKS WHERE BOOKTYPE = 'N'

Após definir essa visualização, há entradas para ela em SYSCAT.VIEWS, SYSCAT.VIEWDEP e SYSCAT.TABLES.

Para definir nomes de colunas na visualização que são diferentes daqueles da tabela base, é possível especificá-los na instrução CREATE VIEW . A instrução a seguir cria uma visualização MYBOOKVIEW que contém duas colunas: TITLE, que representa a coluna BOOKNAME, e TYPE, que representa a coluna BOOKTYPE.

CREATE VIEW MYBOOKVIEW (TITLE,TYPE) AS 
       SELECT BOOKNAME,BOOKTYPE FROM BOOKS

A instrução SQL DROP VIEW é usada para eliminar uma visualização do banco de dados. Se você eliminar uma tabela ou outra visualização na qual uma visualização é baseada, a visualização permanece definida no banco de dados, mas torna-se inoperante. A coluna VALID de SYSCAT.VIEWS indica se uma visualização é válida (Y) ou não (X). Mesmo se você recriar uma tabela base, a visualização órfã permanece inválida; é necessário recriá-la também.

É possível eliminar a visualização NONFICTIONBOOKS do banco de dados:

DROP VIEW NONFICTIONBOOKS

Não é possível modificar uma visualização; para alterar uma definição de visualização, elimine-a e recrie a mesma. Use a instrução ALTER VIEW fornecida somente para modificar tipos de referência.

Visualizações somente leitura e atualizáveis

Ao criar uma visualização, é possível defini-la como uma visualização somente leitura ou como uma visualização atualizável . A instrução SELECT de uma visualização determina se a visualização é somente leitura ou atualizável. Em geral, se as linhas de uma visualização puderem ser mapeadas para linhas da tabela base, então a visualização é atualizável. Por exemplo, a visualização NONFICTIONBOOKS, conforme definida no exemplo anterior, é atualizável, pois cada linha da visualização é uma linha da tabela base.

As regras para criar visualizações atualizáveis são complexas e dependem da definição da consulta. Por exemplo, visualizações que usam os recursos VALUES, DISTINCT ou JOIN não são atualizáveis. É possível determinar facilmente se uma visualização é atualizável verificando a coluna READONLY de SYSCAT.VIEWS: Y significa que é somente leitura e N significa que não é.

As regras detalhadas para criar visualizações atualizáveis estão documentadas na Referência de SQL do DB2 (consulte Recursos).

A visualização NONFICTIONBOOKS definida anteriormente inclui somente as linhas em que BOOKTYPE é N. Se você inserir na visualização uma linha em que BOOKTYPE é F, o DB2 insere a linha na tabela base BOOKS. No entanto, se você selecionar então na visualização, a nova linha inserida não pode ser vista por meio da visualização. Se não quiser permitir que um usuário insira linhas que estão fora do escopo da visualização, é possível definir a visualização com a opção de verificação. Definir uma visualização usando WITH CHECK OPTION indica ao DB2 para verificar se as instruções que estão usando a visualização satisfazem as condições da visualização.

A instrução a seguir define uma visualização usando WITH CHECK OPTION:

CREATE VIEW NONFICTIONBOOKS AS 
       SELECT * FROM BOOKS WHERE BOOKTYPE = 'N'
       WITH CHECK OPTION

Essa visualização ainda restringe o usuário para ver somente livros não ficção; além disso, também evita que o usuário insira linhas que não têm um valor igual a N na coluna BOOKTYPE e atualize o valor da coluna BOOKTYPE em linhas existentes para um valor diferente de N. As instruções a seguir, por exemplo, não são mais permitidas:

INSERT INTO NONFICTIONBOOKS VALUES (...,'F'); 
UPDATE NONFICTIONBOOKS SET BOOKTYPE = 'F' WHERE BOOKID = 111

Visualizações aninhadas com opção de verificação

Ao definir visualizações aninhadas, a opção de verificação pode ser usada para restringir operações. No entanto, há outras cláusulas que podem ser especificadas para definir como as restrições são herdadas. A opção de verificação pode ser definida como CASCADED ou LOCAL. CASCADED é o padrão se a palavra-chave não for especificada. Diversos cenários possíveis explicam as diferenças entre o comportamento de CASCADED e LOCAL.

Quando uma visualização for criada WITH CASCADED CHECK OPTION, todas as instruções executadas com relação à visualização devem satisfazer as condições da visualização e todas as visualizações subjacentes -- mesmo se essas visualizações não tiverem sido definidas com a opção de verificação. Suponhamos que a visualização NONFICTIONBOOKS seja criada sem a opção de verificação e que a visualização NONFICTIONBOOKS1 também seja criada com base na visualização NONFICTIONBOOKS usando a palavra-chave CASCADED:

CREATE VIEW NONFICTIONBOOKS AS 
       SELECT * FROM BOOKS WHERE BOOKTYPE = 'N'
CREATE VIEW NONFICTIONBOOKS1 AS 
       SELECT * FROM NONFICTIONBOOKS WHERE BOOKID > 100
       WITH CASCADED CHECK OPTION

As instruções INSERT a seguir não seriam permitidas, pois elas não satisfazem as condições de pelo menos uma das visualizações:

INSERT INTO NONFICTIONBOOKS1 VALUES( 10,..,'N')
INSERT INTO NONFICTIONBOOKS1 VALUES(120,..,'F')
INSERT INTO NONFICTIONBOOKS1 VALUES( 10,..,'F')

No entanto, a instrução INSERT a seguir seria permitida, pois ela satisfaz as condições de ambas as visualizações:

INSERT INTO NONFICTIONBOOKS1 VALUES(120,...,'N')

Em seguida, suponhamos que você crie uma visualização NONFICTIONBOOKS2 baseada na visualização NONFICTIONBOOKS usando WITH LOCAL CHECK OPTION. Agora, instruções executadas com relação à visualização precisam satisfazer somente condições de visualizações que têm a opção de verificação especificada:

CREATE VIEW NONFICTIONBOOKS AS 
       SELECT * FROM BOOKS WHERE BOOKTYPE = 'N'
CREATE VIEW NONFICTIONBOOKS2 AS 
       SELECT * FROM NONFICTIONBOOKS WHERE BOOKID > 100
       WITH LOCAL CHECK OPTION

Neste caso, as instruções INSERT a seguir não seriam permitidas, pois elas não satisfazem a condição BOOKID > 100 da visualização NONFICTIONBOOKS2:

INSERT INTO NONFICTIONBOOKS2 VALUES(10,..,'N')
INSERT INTO NONFICTIONBOOKS2 VALUES(10,..,'F')

No entanto, as instruções INSERT a seguir seriam permitidas mesmo que o valor N não satisfaça a condição BOOKTYPE = 'N' da visualização NONFICTIONBOOKS:

INSERT INTO NONFICTIONBOOKS2 VALUES(120,..,'N')
INSERT INTO NONFICTIONBOOKS2 VALUES(120,..,'F')

Índices

Um índice é uma lista ordenada dos valores chaves de uma coluna ou colunas de uma tabela. Há duas razões para se criar um índice:

  • Para assegurar exclusividade de valores em uma coluna ou colunas.
  • Para melhorar o desempenho de consultas com relação à tabela. O otimizador do DB2 usa índices para melhorar desempenho ao executar consultas ou apresentar resultados de uma consulta na ordem do índice.

Índices podem ser definidos como exclusivos ou não exclusivos. Índices não exclusivos permitem valores de chaves duplicados; índices exclusivos permitem somente uma ocorrência de um valor de chave na lista. Índices exclusivos permitem que um único valor nulo esteja presente. No entanto, um segundo valor nulo causaria uma duplicata e, portanto, não é permitido.

Índices são criados usando a instrução SQL CREATE INDEX . Índices também são criados de forma implícita em suporte de uma restrição PRIMARY KEY ou UNIQUE . Quando um índice exclusivo é criado, os dados de chave são verificados para exclusividade e a operação falha se duplicatas forem localizadas.

Índices são criados como crescentes, decrescentes ou bidirecionais. A opção escolhida depende de como o aplicativo acessa os dados.

Criando índices

No exemplo, você tem uma chave primária na coluna BOOKID. Frequentemente, usuários realizam procuras de título de livro, assim, um índice de BOOKNAME seria apropriado. A instrução a seguir cria um índice crescente não exclusivo na coluna BOOKNAME:

CREATE INDEX IBOOKNAME ON BOOKS (BOOKNAME)

O nome do índice, IBOOKNAME, é usado para criar e eliminar o índice. Além disso, o nome não é usado em consultas ou atualizações da tabela.

Por padrão, um índice é criado em ordem crescente, mas também é possível criar índices que são decrescentes. É possível especificar até mesmo diferentes ordens para as colunas no índice. A instrução a seguir define um índice nas colunas AUTHORID e BOOKNAME. Os valores da coluna AUTHORID são classificados em ordem decrescente e os valores da coluna BOOKNAME são classificados em ordem crescente no mesmo AUTHORID:

CREATE INDEX I2BOOKNAME ON BOOKS (AUTHOID DESC, BOOKNAME ASC)

Quando um índice é criado em um banco de dados, as chaves são armazenadas na ordem especificada. O índice ajuda a melhorar o desempenho de consultas requerendo os dados na ordem especificada. Um índice crescente também é usado para determinar o resultado da função de coluna MIN ; um índice decrescente é usado para determinar o resultado da função de coluna MAX . Se o aplicativo requer que os dados sejam ordenados na sequência oposta ao índice também, o DB2 permite a criação de um índice bidirecional. Um índice bidirecional elimina a necessidade de criar um índice na ordem reversa e elimina a necessidade de o otimizador classificar os dados na ordem reversa. Também permite a recuperação eficiente dos valores das funções MIN e MAX . para criar um índice bidirecional, especifique a opção ALLOW REVERSE SCANS na instrução CREATE INDEX :

CREATE INDEX BIBOOKNAME ON BOOKS (BOOKNAME) ALLOW REVERSE SCANS

O DB2 não permite criar diversos índices com a mesma definição. Isso se aplica mesmo a índices criados implicitamente em suporte de uma chave primária ou restrição exclusiva. Como a tabela BOOKS já possui uma chave primária definida na coluna BOOKID, tentar criar um índice na coluna BOOKID falha.

A criação de um índice leva muito tempo. O DB2 lê cada linha para extrair as chaves, classificar essas chaves e, em seguida, gravar a lista no banco de dados. Se a tabela for grande, em seguida, um espaço de tabela temporário é usado para classificar as chaves.

O índice é armazenado em um espaço de tabela. Se sua tabela reside em um espaço de tabela gerenciado pelo banco de dados, você tem a opção de separar os índices em um espaço de tabela separado. Defina isso ao criar a tabela, usando a cláusula INDEXES IN . O local dos índices de uma tabela é configurado quando a tabela é criada e não pode ser alterada, a menos que a tabela seja eliminada e recriada.

O DB2 também fornece a instrução SQL DROP INDEX para remover um índice do banco de dados. Não há como modificar um índice. Se precisar alterar um índice -- para incluir outra coluna na chave, por exemplo -- é necessário eliminar e recriar o mesmo.

Índices de armazenamento em cluster

É possível criar um índice em cada tabela como o índice de armazenamento em cluster. Um índice de armazenamento em cluster é útil quando os dados da tabela são frequentemente referidos em uma ordem específica. O índice de armazenamento em cluster define a ordem na qual dados são armazenados no banco de dados. Durante inserções, o DB2 tenta colocar novas linhas próximas às linhas com chaves semelhantes. Em seguia, durante consultas que requerem dados na sequência de índice de armazenamento em cluster, os dados podem ser recuperados mais rapidamente.

Para criar um índice como o índice de armazenamento em cluster, especifique a cláusula CLUSTER na instrução CREATE INDEX :

CREATE INDEX IAUTHBKNAME ON BOOKS (AUTHORID,BOOKNAME) CLUSTER

Essa instrução cria um índice nas colunas AUTHORID e BOOKNAME como o índice de armazenamento em cluster. Esse índice melhoraria o desempenho de consultas gravadas para listar autores e todos os livros que escreveram.

Usando colunas incluídas em índices

Ao criar um índice, você tem a opção de incluir dados da coluna extra armazenados com a chave, mas na verdade não fazem parte da chave em si e não são classificados. A principal razão para incluir colunas adicionais em um índice é melhorar o desempenho de determinadas consultas: com esses dados já disponíveis na página de índice, o DB2 não precisa acessar a página de dados para buscá-los. Colunas incluídas podem ser definidas somente para índices exclusivos. No entanto, as colunas incluídas não são consideradas ao impingir exclusividade do índice.

Suponhamos que você precise frequentemente obter uma lista de nomes de livros ordenada por BOOKID. A consulta teria a seguinte aparência:

SELECT BOOKID,BOOKNAME FROM BOOK ORDER BY BOOKID

Crie um índice que possa melhorar o desempenho:

CREATE UNIQUE INDEX IBOOKID ON BOOKS (BOOKID) INCLUDE(BOOKNAME)

Como resultado, todos os dados necessários para o resultado da consulta estão presentes no índice e nenhuma página de dados precisa ser recuperada.

Então, por que não incluir apenas todos os dados nos índices? Primeiro, isso requereria mais espaço físico no banco de dados, pois os dados da tabela seriam essencialmente duplicados no índice. Segundo, todas as cópias dos dados precisariam ser atualizadas sempre que o valor dos dados fosse atualizado e isso seria uma sobrecarga significativa em um banco de dados no qual muitas atualizações ocorrem.

Quais índices devo criar?

Considere o seguinte ao criar índices:

  • Como os índices são uma lista permanente dos valores de chaves, eles requerem espaço no banco de dados. A criação de muitos índices requer espaço de armazenamento adicional em seu banco de dados. A quantia de espaço necessária é determinada pelo comprimento das colunas-chaves. O DB2 fornece uma ferramenta para ajudar a estimar o tamanho de um índice.
  • Índices são cópias adicionais dos valores, portanto, eles devem ser atualizados se os dados da tabela forem atualizados. Se os dados da tabela forem atualizados frequentemente, considere qual impacto os índices adicionais têm no desempenho da atualização.
  • Índices melhoram de forma significativa o desempenho de consultas quando definidos nas colunas apropriadas.

O DB2 fornece uma ferramenta chamada Index Advisor para ajudar a determinar quais índices definir. O Index Advisor permite especificar a carga de trabalho que é executada com relação a uma tabela e recomenda que índices sejam criados na tabela.


Resumo

Resumo

Este tutorial foi projetado para que você se familiarize com os recursos de tipos de dados, tabelas, restrições, visualizações e índices definidos no DB2. Também mostrou como usar as instruções CREATE, ALTER e DROP para gerenciar esses objetos. Exemplos foram fornecidos para que você pudesse experimentar o uso desses objetos em uma configuração controlada.

Parte 6: Simultaneidade de Dados, apresenta o conceito de consistência de dados e os vários mecanismos usados pelo DB2 para manter consistência de dados em ambientes de usuários únicos e de multiusuário.

Para ficar de olho nesta série, marque a página da série, tutoriais de preparação para o exame 730 de DBA do DB2 9.

Recursos

Aprender

Obter produtos e tecnologias

  • Uma versão de avaliação do DB2 9 está disponível para download gratuito.
  • Faça o download do DB2 Express-C, uma versão gratuita do DB2 Express Edition para a comunidade que oferece os mesmos recursos de dados principais que o DB2 Express Edition e fornece uma base sólida para desenvolver e implementar aplicativos.

Comentários

developerWorks: Conecte-se

Los campos obligatorios están marcados con un asterisco (*).


Precisa de um ID IBM?
Esqueceu seu ID IBM?


Esqueceu sua senha?
Alterar sua senha

Ao clicar em Enviar, você concorda com os termos e condições do developerWorks.

 


A primeira vez que você entrar no developerWorks, um perfil é criado para você. Informações no seu perfil (seu nome, país / região, e nome da empresa) é apresentado ao público e vai acompanhar qualquer conteúdo que você postar, a menos que você opte por esconder o nome da empresa. Você pode atualizar sua conta IBM a qualquer momento.

Todas as informações enviadas são seguras.

Elija su nombre para mostrar



Ao se conectar ao developerWorks pela primeira vez, é criado um perfil para você e é necessário selecionar um nome de exibição. O nome de exibição acompanhará o conteúdo que você postar no developerWorks.

Escolha um nome de exibição de 3 - 31 caracteres. Seu nome de exibição deve ser exclusivo na comunidade do developerWorks e não deve ser o seu endereço de email por motivo de privacidade.

Los campos obligatorios están marcados con un asterisco (*).

(Escolha um nome de exibição de 3 - 31 caracteres.)

Ao clicar em Enviar, você concorda com os termos e condições do developerWorks.

 


Todas as informações enviadas são seguras.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=80
Zone=Information Management
ArticleID=757106
ArticleTitle=Preparação para o Exame 730 DB2 9 Fundamentals, Parte 5: Trabalhando com Objetos do DB2
publish-date=09162011