Gerenciando o tempo no DB2 com consistência temporal

Reforce a integridade referencial com base no tempo

Os novos recursos temporais na IBM® DB2® 10 fornecem recursos valiosos para o gerenciamento de dados baseado em tempo. Por exemplo, é possível designar um intervalo de data para cada linha de dados para indicar quando ela é considerada válida por seus aplicativos ou negócios. As tabelas ativadas para controlar cada validade de negócios são chamadas de tabelas temporais de período do aplicativo e seus períodos de negócios podem ser no passado, presente e futuro. Para uma tabela-pai e uma tabela-filho que possuem um relacionamento de chave estrangeira, a noção de integridade referencial pode ser ampliada para considerar não apenas o valor da chave tradicional, mas também o período de negócios de qualquer linha específica. Este artigo explica tal integridade referencial temporal e como a consistência baseada em tempo pode ser impingida no DB2. O conteúdo desse artigo é aplicável ao DB2 para z/OS ® e DB2 para Linux®, UNIX® e Windows®. Todas as amostras de SQL inclusas foram testadas no DB2 10.1 para LUW.

Matthias Nicola, Senior Technical Staff Member, IBM Silicon Valley Lab

Author photo: Matthias NicolaMatthias Nicola é Senior Technical Staff Member no IBM Silicon Valley Lab, em San Jose, CA. Ele se concentra no desempenho e benchmarking do DB2, XML, gerenciamento de dados temporais, analítica no banco de dados e outras tecnologias emergentes. Também trabalha próximo aos clientes e parceiros de negócios para ajudá-los a projetar, otimizar e implementar soluções do DB2. Anteriormente, Mathias trabalhou no desempenho do armazém de dados no Informix Software. Ele é Ph.D. em Ciência da Computação pela Universidade Técnica de Aachen, na Alemanha.


nível de autor Contribuidor do
        developerWorks

Martin Sommerlandt, DB2 Specialist, Consultor

作者照片:Martin SommerlandtMartin Sommerlandt é desenvolvedor de aplicativos do DB2 e administrador de banco de dados do DB2 certificado pela IBM. Durante sua permanência na IBM, ele atuou como engenheiro de desempenho no IBM Silicon Valley Lab, responsável pelos testes de desempenho e análise dos novos recursos do DB2, incluindo as tabelas temporais.



06/Ago/2012

Introdução ao gerenciamento de dados temporais com o DB2

O DB2 10 para z/OS e DB2 10 para Linux, UNIX e Windows apresenta o gerenciamentos de dados baseado em tempo que permite consultar e manipular dados no passado, presente e futuro, enquanto mantém um histórico completo de todas as alterações de dados. A coleção de recursos temporais no DB2 também é conhecida como Time Travel Query.

O DB2 suporta três tipos de tabelas temporais:

  • Tabelas temporais de período do sistema— Local onde o DB2 mantém, de maneira transparente, um histórico de linhas antigas que foram atualizadas ou excluídas ao longo do tempo. Com as novas construções na linguagem padrão de SQL, os usuários podem "voltar no tempo" e consultar o banco de dados em qualquer ponto selecionado no passado. Isso tem como base os registros de data e hora do sistema atribuídas internamente. Elas são usadas pelo DB2 usa para gerenciar a hora do sistema, também conhecida como horário da transação.
  • Tabelas temporais de período do aplicativo— Onde os aplicativos fornecem datas ou marcações de data e hora pra descrever a validade de negócios dos dados. As novas construções de SQL permitem que os aplicativos insiram, consultem, atualizem e excluam dados no passado, presente ou futuro. O DB2 aplica restrições e divisões de linhas automaticamente para manter corretamente o horário de negócios fornecido pelo aplicativo, também conhecido como horário válido.
  • Tabelas bitemporais— Gerenciam a hora do sistema e o horário de negócios. As tabelas bitemporais combinam todas as habilidades das tabelas temporais de período do sistema e tabelas temporais de período do aplicativo. Esta combinação permite gerenciar a validade de negócios dos dados enquanto o DB2 mantém um histórico completo de todas as atualizações e exclusões.

Para o restante desse artigo, presumiremos que está familiarizado com o básico sobre as tabelas temporais de período do aplicativo no DB2. A discussão da integridade referencial (RI) temporal também se aplica ao horário de negócios nas tabelas bitemporais. Deve-se saber como criar e usar tais tabelas, além de entender como o DB2 pode executar divisões de linha quando os dados forem atualizados ou excluídos para uma porção especificada do horário de negócios. O artigo ""Uma questão de tempo: gerenciamento de dados temporais no DB2" fornece uma introdução a esses tópicos.


O que é a integridade referencial temporal?

Um exemplo

Nesta seção, revisitaremos a integridade referencial (RI) tradicional em um banco de dados relacional e, então, expandiremos a discussão para RI temporal. Nosso cenário envolve um negócio fictício que recebe produtos de fornecedores, os vende para os clientes e, ocasionalmente, realiza promoções que oferecem os produtos selecionados a preços com desconto por períodos de tempo limitados. Para ajudar a entender como é possível implementar e impingir a RI temporal, usaremos esse cenário de aplicativos e dados de amostra.

RI tradicional

Lista 1 mostra duas tabelas envolvidas no gerenciamento desses negócios. A tabela-pai product_avail contém uma linha para cada produto disponível e identifica o fornecedor que provê esse produto. A tabela-filho promotion possui uma linha para cada oferta especial, definida por um ID de promoção, o respectivo ID do produto e o preço reduzido. Ambas as tabelas devem ter colunas adicionais, mas para simplicidade, essas poucas colunas são suficientes no momento.

Lista 1. Exemplo simples de integridade referencial entre a tabela-pai e a tabela-filho
CREATE TABLE product_avail(
    prodID     INTEGER NOT NULL, 
    supplier   VARCHAR(32),
  PRIMARY KEY(prodID) );

CREATE TABLE promotion(
    promoID    INTEGER NOT NULL, 
    prodID     INTEGER NOT NULL, 
    price      DECIMAL(10,2), 
  PRIMARY KEY(promoID),
  FOREIGN KEY (prodID) REFERENCES product_avail(prodID) );

Uma vez que uma promoção pode ser oferecida apenas para um produto disponível, todas as linhas na tabela promotion devem conter um prodID que exista na tabela product_avail. Esta condição é chamada de integridade referencial e é declarada e impingida pela restrição de chave estrangeira (FK) na tabela de promoção. Devido a essa restrição, qualquer tentativa de inserir ou atualizar uma promoção com um prodID que não exista na tabela product_avail é rejeitada pelo DB2.

Horário da apresentação

Em nossos negócios fictícios, diferentes fornecedores podem prover o mesmo produto em momentos diferentes do ano. De maneira semelhante, podemos optar por realizar diferentes promoções para o mesmo produto em diferentes momentos. Uma vez que o tempo é uma dimensão tão importante nos negócios, criamos as tabelas de Lista 1 como tabelas temporais de período do aplicativo, o que significa que incluímos um período BUSINESS_TIME em ambas as tabelas (consulte Lista 2).

O período BUSINESS_TIME consiste em um par de colunas de data ou registro de data e hora, além de uma declaração PERIOD que define esse par de colunas como um período. Também optamos por estender a definição de chave primária com as palavras-chave opcionais BUSINESS_TIME WITHOUT OVERLAPS, que indicam que várias linhas podem ter o mesmo valor de prodID contanto que os períodos BUSINESS_TIME dessas linhas não se sobreponham. A mesma extensão de chave primária é aplicada à tabela promotion.

Como a coluna prodID na tabela product_avail agora pode ter valores duplicados, ela não pode mais ser referenciada como uma chave estrangeira na tabela promotion. Será necessário impingir a RI de outra forma.

Lista 2. Tabelas da Listagem 1 agora como tabelas temporais de período do aplicativo
CREATE TABLE product_avail(
    prodID       INTEGER NOT NULL,
    supplier     VARCHAR(32),
    avail_start  DATE NOT NULL,
    avail_end    DATE NOT NULL,
    PERIOD BUSINESS_TIME (avail_start, avail_end),
    PRIMARY KEY(prodID, BUSINESS_TIME WITHOUT OVERLAPS)  );
                
CREATE TABLE promotion(
    promoID    INTEGER NOT NULL,
    prodID     INTEGER NOT NULL,
    price      DECIMAL(10,2),
    promo_start  DATE NOT NULL,
    promo_end    DATE NOT NULL,
    PERIOD BUSINESS_TIME (promo_start, promo_end),
    PRIMARY KEY(promoID, BUSINESS_TIME WITHOUT OVERLAPS)  );

Figura 1 mostra as tabelas preenchidas com informações sobre três produtos e duas promoções. Por exemplo, o produto 9105 é entregue pela empresa fornecedora A desde 1º de janeiro de 2012 até 1º de junho de 2012, o que significa que o último dia de entrega é 31 de maio de 2012. (Observe que os períodos são sempre especificados de uma maneira inclusiva ou exclusiva, as datas de encerramento especificadas não fazem mais parte do período de validade.) De 1º de junho em diante, o produto 9015 é fornecido pela empresa B, encerrando em 1º de setembro. Durante setembro e outubro, não recebemos fornecimento do produto 9015, mas durante novembro e dezembro este produto está novamente disponível na empresa A. Quando não temos um fornecedor para um produto, então ele é considerado como fora do estoque.

Figura 1. Dados de amostra
Dados de amostra

A chave primária (prodID, BUSINESS_TIME WITHOUT OVERLAPS) garante que dois fornecedores não entreguem o mesmo produto simultaneamente. Caso desejemos permitir que vários fornecedores entreguem o mesmo produto simultaneamente, podemos definir a chave primária como (prodID, supplier, BUSINESS_TIME WITHOUT OVERLAPS).

A tabela promotion na Figura 1 define duas promoções para o produto 9105. A primeira promoção oferece o produto por $19,95 de 15 de janeiro de 2012 a 15 de março de 2012. Isto é, o último dia de desconto no valor é 14 de março de 2012. A segunda promoção começa em 1º de maio e termina em 1º de julho de 2012, com o preço de $16,95. Em todos os outros momentos, o preço normal para o produto 9105 está em vigor. Esse preço é armazenado em outro lugar.

RI temporal

Os dados na Figura 1 preenchem um relacionamento de chave estrangeira tradicional entre as duas tabelas, pois cada promoção referencia um ID de produto que existe na tabela product_avail. Porém, nossos negócios também devem garantir que uma promoção seja oferecida apenas durante um período quando o respectivo produto estiver realmente disponível. Imagine a confusão e o descontentamento entre os clientes caso anunciássemos um desconto para um produto que não está disponível em nenhuma de nossas lojas.

Falando de forma mais geral, pode-se desejar impingir um relacionamento temporal entre os períodos de negócios das linhas-pai e das linhas filho em duas tabelas relacionadas. Os exemplos de tais relacionamentos incluem o seguinte:

  • Igualdade do período— O período dos negócios da linha filho deve ser idêntico ao período de uma única linha pai. Em nosso exemplo, essa condição deve impingir que a duração de uma promoção sempre corresponde ao período durante o qual o período está disponível de um fornecedor específico. Uma FK tradicional no ID do produto e as colunas do início e término do período podem impingir esse relacionamento.
  • Restrição de período— O período dos negócios da linha filho deve estar completamente contido nos períodos de uma ou várias linhas pai.
  • Restrição de período, pai único— O período dos negócios da linha filho deve estar completamente contido no período de uma única linha pai.
  • Sobreposição— O período dos negócios da linha filho deve ser sobreposto pelo período de uma linha pai.
  • Início dentro do período— O período dos negócios da linha filho deve iniciar dentro do período de uma linha pai.
  • Início após— O período dos negócios da linha filho deve começar após o período de uma linha pai.

Figura 2 visualiza os períodos dos negócios dos dados de amostra em Figura 1 e nos permite examinar alguns dos relacionamentos temporais listados acima. Cada linha da tabela é representada por um retângulo cujo comprimento representa a duração do período de negócios de tal linha. As linhas da tabela product_avail são mostradas em azul e as linhas da tabela promotion estão em verde.

Podemos fazer as seguintes observações na Figura 2:

  • As promoções com promoID 16 e 17 não preenchem as condições de igualdade de período com suas linhas pai, pois os períodos de promoção são menores do que os períodos de suas linhas-pai na tabela product_avail.
  • A promoção 16 preenche a condição de restrição do período — e ainda a restrição de período com um pai único,— pois seu período de oferta está contido em um período durante o qual o produto 9105 é fornecido pela empresa fornecedora A.
  • A promoção 17 satisfaz a restrição de período (com várias linhas pai), pois seu período de oferta consiste nos meses de maio e junho, em que o produto é fornecido pelas empresas A e B, sem lacuna no fornecimento. A promoção 17 não satisfaz a restrição de período com uma única linha pai.
  • As promoções 16 e 17 preenchem os relacionamentos de sobreposição e início dentro do período com a primeira linha do produto
Figura 2. Linha de tempo dos períodos de product_avail (parte superior, azul) e períodos de promoção (parte inferior, verde)
Linha de tempo dos períodos de product_avail (parte superior, azul) e períodos de promoção (parte inferior, verde)

Com base nos seus próprios requisitos de aplicativos e caso de uso, é necessário determinar que relacionamento temporal impingir entre as linhas-pai e filho.

Para nosso exemplo de produto e promoção, escolhemos a restrição do período (com uma ou várias linhas pai) conforme o relacionamento desejado para definir a RI temporal. Por essa razão, o restante desse artigo assume a restrição do período como a noção para a RI temporal.

Violações de RI temporal

Nosso departamento de marketing espera uma diminuição nas vendas durante agosto e setembro e decide oferecer o produto 9105 por apenas $15,95 durante esse período. O INSERT a seguir é emitido para a promoção 18:

INSERT INTO promotion (promoID, prodID, price, promo_start, promo_end)
VALUES (18, 9105, '2012-08-01', '2012-10-01');

Essa nova promoção não viola a RI tradicional, pois a chave estrangeira 9105 existe na tabela product_avail. No entanto, é possível ver em Figura 3 que a promoção 18 viola nossa noção de RI temporal, isto é, a restrição de período. Essa nova promoção ocorre durante todo o mês de setembro, mas não temos fornecedor para o produto 9105 durante setembro (e não mantemos estoque).

Caso tenhamos escolhido a condição sobreposição ou início dentro do período par definir a RI temporal, a promoção 18 não estaria em violação.

Figura 3. A promoção 18 viola a RI temporal
A promoção 18 viola a RI temporal

Podemos detectar e evitar essa violação de RI temporal ao descobrir que a data de encerramento da promoção 18, 2012-10-01, está fora de qualquer período de negócios existente para o produto 9105 na tabela product_avail. Para corrigir a situação, a promoção 18 precisa ser modificada ou excluída da tabela promotion.

Agora, consideremos outra promoção inserida com a seguinte declaração INSERT para a promoção 19:

INSERT INTO promotion (promoID, prodID, price, promo_start, promo_end)
VALUES (19, 9105, '2012-08-01', '2012-12-01');

Essa promoção tem início em 1º de agosto de 2012 e término em 1º de dezembro de 2012. Embora as datas de início e de encerramento fiquem dentro dos períodos de disponibilidade do produto, a RI temporal (restrição de período) é violada pelo fato de haver uma lacuna de fornecedor entre essas duas datas. Conforme ilustrado na Figura 4, a promoção 19 é válida de agosto a novembro, mas não temos fornecimento durante setembro e outubro.

Figura 4. A promoção 18 viola a RI temporal
A promoção 18 viola a RI temporal

O exemplo da promoção 19 ilustra um fato importante: se os períodos pai para um valor chave específico puder ter lacunas, as violações de restrição não poderão ser detectadas apenas ao verificar se as datas de início e de encerramento estão contidas em períodos pai existentes. Entretanto, caso tenha certeza de que não há lacunas de período entre as linhas-pai com o mesmo valor da chave, a restrição do período é mais fácil de ser impingida.

A promoção 17 ilustra outra observação importante: para impingir a restrição de período com uma ou várias linhas pai, geralmente não é suficiente verificar se as datas de início ou encerramento de um período filho estão contidas no mesmo período pai. Tal verificação poderia impingir a restrição de período em um único pai e rejeitar a promoção 17.


Opções para impingir a RI temporal

Normalmente, há quatro opções para manipulação de RI temporal:

  1. Sem execução. Não defina nenhuma restrição quando souber que a RI temporal não é relevante para seus aplicativos ou nunca é violada.
  2. Impingir nos aplicativos. Uma boa opção caso os aplicativos já realizem a execução da RI temporal. Caso contrário, isso pode levar a mais complexidade do aplicativo e coloca uma responsabilidade sobre os desenvolvedores de aplicativos.
  3. Impingir usando acionadores. Crie acionadores para as operações de inserção, atualização e exclusão para impingir a RI temporal.
  4. Impingir usando os procedimentos armazenados. Crie procedimentos armazenados que impingem a RI temporal para várias linhas em operações em massa.

No restante desse artigo, fornecemos alguns exemplos para as opções 3 e 4, acionadores e procedimentos armazenados, para impingir a restrição de período entre uma linha filho e uma ou várias linhas pai. As amostras de código foram testadas no DB2 10 para Linux, UNIX e Windows®, mas os mesmos conceitos gerais também se aplicam ao DB2 para z/OS®.


Impingindo a RI temporal com acionadores

Para impingir a RI temporal, é possível criar acionadores para alguns ou todos os eventos a seguir:

  • Inserção de linhas em uma tabela-filho.
    • A RI temporal pode ser violada se o período de uma nova promoção de produto não estiver contida em um período de negócios para esse produto. Um acionador pode detectar essa condição e rejeitar a inserção de uma promoção.
  • Atualização das linhas na tabela-filho
    • A RI temporal pode ser violada se a chave primária ou o período de negócios ou uma linha filho for alterada. O acionador pode rejeitar a atualização, caso seja necessário.
  • Exclusão de linhas da tabela-pai.
    • A RI temporal pode ser violada caso um produto seja excluído enquanto uma promoção relacionada ainda exista. Nesse caso, o acionador podm implementar uma regra de exclusão de sua escolha, como:
      • Rejeição e recuperação da operação de exclusão.
      • Aplicação do efeito cascata na exclusão e remoção das linhas associadas na tabela-filho.
      • Modificação das linhas filho de alguma outra maneira definida pelos aplicativos.
  • Atualização das linhas na tabela-pai.
    • A RI temporal pode ser violada caso a chave primária ou o período de negócios de uma linha pai seja alterada. Neste caso, o acionador pode rejeitar a atualização ou pode excluir ou modificar as linhas relacionadas na tabela-filho.

As seções a seguir fornecem acionadores de amostra para inserções e atualizações das linhas da tabela-filho.

Acionadores de RI temporal básicos (sem lacunas)

Nessa seção, supomos que os períodos para qualquer produto na tabela-pai (product_avail) nunca tem lacunas. Essa suposição implica que a violação da RI temporal da promoção 19 na Figura 4 não pode ocorrer e, assim, o acionador não precisa realizar a verificação para esse caso especial. Isso permite a implementação de um acionador simples. Posteriormente, discutiremos como esse acionador pode ser estendido para manipular as lacunas.

Lista 3 mostra um acionador que é executado sempre que uma nova linha é inserida na tabela promotion . O acionador verifica a RI temporal para a nova linha, o que significa que ele verifica se o período da nova linha de promoção está contido no(s) período(s) de uma ou várias linhas-pai para o mesmo ID de produto.

O acionador executa duas verificações.Primeiro, ele verifica se a data de início da promoção inserida está contida no período de negócios de uma linha da product_avail com um prodID correspondente. Em seguida, ele verifica uma condição semelhante para a data de encerramento da promoção inserida. Caso algum desses testes falhar (por exemplo, se a contagem de linhas-pai correspondentes for zero), a RI temporal é violada e surge um erro. Caso as duas datas estejam contidas em períodos das linhas-pai correspondentes, a RI temporal é mantida e a inserção é concluída. Caso contrário, o acionador rejeita a inserção, sinaliza um estado de SQL customizado e emite uma mensagem de que a RI temporal foi violada.

Lista 3. Acionador para impingir a RI temporal na inserção de uma linha filho
CREATE TRIGGER promotion_insert_RI
BEFORE INSERT ON promotion
REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    IF (
        -- Is promotion.promo_start part of an existing product_avail period?
        (SELECT COUNT(*) count
        FROM product_avail p
        WHERE p.ProdID = new.ProdID
        AND p.avail_start >= new.promo_start 
        AND p.avail_end < new.promo_start)=0
    OR
        -- Is promotion.promo_end part of an existing product_avail period?
        (SELECT COUNT(*) count
        FROM product_avail p
        WHERE p.ProdID = new.ProdID
        AND p.avail_start > new.promo_end
        AND p.avail_end <= new.promo_end)=0
     ) -- promotion.promo_start AND promotion.promo_end must BOTH be 
    -- part of ANY period
   THEN SIGNAL SQLSTATE 'RI999' 
     SET MESSAGE_TEXT='PROMOTION PERIOD IS NOT FULLY CONTAINED IN EXISTING
                    PRODUCT PERIODS!';
    END IF;
END@

Um acionador similar é necessário caso esperemos que as colunas prodID, promo_startou promo_end de uma promoção possam ser atualizadas. Por exemplo: uma atualização dos valores de promo_start ou promo_end pode mover ou aumentar o período de promoção e potencialmente violar a RI temporal. A lógica do acionador é a mesma que na Lista 4, exceto que o acionador é disparado apenas se alguma dessas três colunas for afetada por uma instrução atualizada.

Lista 4. Acionador para impingir a RI temporal na atualização de uma linha filho
CREATE TRIGGER promotion_update_RI
BEFORE UPDATE OF prodID, promo_start, promo_end ON promotion
REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
        -- same trigger body as in Listing 3
END IF;
END@

A seguir, discutiremos como esses acionadores podem ser aprimorados para lidar corretamente com lacunas nos períodos pai.


Acionadores de RI temporal que verificam se há lacunas

Se as linhas-pai para um determinado valor da chave podem ter lacunas entre seus períodos de negócios, como em nosso exemplo de disponibilidade do produto, então a verificação de RI temporal no acionador precisa ser mais sofisticada. O acionador precisa ser aprimorado para também detectar "violações de lacuna" como na promoção 19 na Figura 4. Lista 5 e Lista 6 mostram duas maneiras alternativas de codificação de um acionador que verifica a restrição de período com a verificação de lacuna necessária.

O acionador na Lista 5 seleciona as linhas-pai da tabela product_avail cujos períodos são sobrepostos ao período da nova linha sendo inserida na tabela promotion. Para cada uma dessas linhas pai, uma autojunção externa esquerda tabela product_avail recupera todas as linhas-pai "next" (próximo) cujo período comece quando o anterior acabou. (current.avail_end = next.avail_start) (isto é, sem lacuna). As linhas resultantes dessa junção externa esquerda que têm o valor NULL na coluna next.avail_start representam uma lacuna nos períodos das linhas pai. As outras condições no acionador garantem que as datas de início e encerramento da nova promoção estão contidas nos períodos pai existentes.

Caso não haja linha pai na tabela product_avail cujo valor de prodID corresponda ao prodID da promoção recentemente inserida, então a junção externa cria um conjunto de resultados vazio. Nesse caso, o SUM(start_check)+SUM(end_and_gap_check) na condição de nível superior SELECT produz NULL. Este valor NULL faz com que a função COALESCE retorne -1 e permita que o teste de integridade falhe conforme necessário.

Lista 5. Impingindo a RI temporal mediante a inserção de uma linha filho, considerando as lacunas do período pai
CREATE OR REPLACE TRIGGER promotion_insert_RI_withGaps
BEFORE INSERT ON promotion
REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    IF ( SELECT COALESCE(SUM(start_check)+SUM(end_and_gap_check), -1) check 
        FROM (
        SELECT CASE WHEN new.promo_start < current.avail_start
                AND current.avail_start = 
                        (SELECT MIN(avail_start) 
                        FROM product_avail
                        WHERE prodID = new.prodID 
                        AND avail_start < new.promo_end
                        AND avail_end > new.promo_start)
                THEN -1-- PROMOTION.PROMO_START is out of product_avail range!
                ELSE  0-- PROMOTION.PROMO_START is in range of current or previous 
                        -- product_avail period
            END start_check,
            CASE WHEN current.avail_end < new.promo_end
                THEN CASE WHEN next.avail_start IS NULL
                          THEN -1-- PROMOTION is out of product_avail range, 
                                -- or product_avail contains gaps!
                ELSE  0-- PROMOTION.PROMO_END is out of current 
                                -- product_avail period, but a connecting 
                                -- product_avail period exists
                        END ELSE 0 -- PROMOTION.PROMO_END is in a product_avail period
                END end_and_gap_check
            FROM product_avail AS current
            LEFT OUTER JOIN product_avail AS next
                        ON current.avail_end = next.avail_start
                        AND current.prodID = next.prodID
            WHERE current.prodID = new.prodID 
            AND current.avail_start < new.promo_end 
            AND current.avail_end > new.promo_start
        ) )<0 THEN SIGNAL SQLSTATE 'RI999'
            SET MESSAGE_TEXT='PROMOTION PERIOD VIOLATES TEMPORAL RI!';
    END IF;
END @

A melhor abordagem para entender a lógica nesse acionador é criar figuras como Figura 4 e passar pelas condições e expressões CASE.

Lista 5 é apenas uma solução possível. Pode-se encontrar outras formas no SQL para codificar as mesmas condições. Uma alternativa é mostrada na Lista 6. O corpo do acionador na Lista 6 verifica três condições. A primeira instrução SELECT verifica se a data de início da promoção inserida está dentro do período de negócios de uma linha de produto com o mesmo valor da chave. A segunda instruçãoSELECT verifica se a data de encerramento da nova promoção está dentro do período de uma linha de produto correspondente. E a terceira SELECT verifica se há lacunas entre os períodos das linhas-pai relevantes na tabela product_avail.

Lista 6. Impingindo a RI temporal mediante a inserção de uma linha filho, considerando as lacunas do período pai
CREATE OR REPLACE TRIGGER promotion_insert_RI_withGaps
BEFORE INSERT ON promotion
REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    IF (
        -- verify that a product row exists with prodID = promtion.prodID
        -- and whose period contains promo_start
        (SELECT COUNT(*)
        FROM product_avail prod
        WHERE new.prodID = prod.prodID
        AND prod.avail_start <= new.promo_start
        AND new.promo_start < prod.avail_end)   < 0 )
    OR ( -- verify that a product row exists with prodID = promotion.prodID 
        -- and whose period contains promo_end
        (SELECT COUNT(*)
        FROM product_avail prod
        WHERE new.prodID = prod.prodID
        AND prod.avail_start < new.promo_end
        AND new.promo_end <= prod.avail_end)   < 0 )
    OR ( -- check for any gaps between the relevant product rows that
        -- have prodID = promotion.prodID
        (SELECT COUNT(*)
        FROM product_avail prod
        WHERE new.prodID = prod.prodID
        AND new.promo_start < prod.avail_end
        AND prod.avail_end < new.promo_end
        AND NOT EXISTS (
                SELECT *
                FROM product_avail prod2
                WHERE prod2.prodID = prod.prodID
                    AND prod2.avail_start <= prod.avail_end
                    AND prod.avail_end < prod2.avail_end) )   > 0 )
    THEN SIGNAL SQLSTATE 'RI999'
        SET MESSAGE_TEXT='PROMOTION PERIOD VIOLATES TEMPORAL RI!';
    END IF;
END @

Impingindo a RI temporal com procedimentos armazenados

Caso esteja adicionando, alterando ou excluindo muitas linhas em uma tabela, pode ser mais eficiente verificar a RI temporal com as operações baseadas em conjuntos em um procedimento armazenado em vez de com um acionador que é ativado uma vez para cada linha afetada. Por exemplo, caso realize operações de importação ou carregamento ou exclusões e atualizações em massa, é possível que deseje usar um procedimento armazenado para verificar a RI temporal.

Por exemplo, o procedimento na Lista 6 verifica a RI temporal entre a tabela product_avail e a tabela promotion. Ele presume que os períodos para um determinado produto na tabela product_avail não contém lacunas. A principal lógica de RI nesse procedimento é análoga à lógica no acionador na Lista 3.

Ao criar tal procedimento, é possível escolher como deseja manipular a RI temporal. A manipulação da violação na Lista 6 segue uma abordagem simples: se houver pelo menos uma linha de promoção que viole a RI temporal, o procedimento sinaliza um estado de SQL personalizado e emite uma mensagem de que a RI temporal foi violada.

Lista 7. Procedimento armazenado para verificar a RI temporal em massa
CREATE PROCEDURE check_temporal_integrity()
BEGIN
    IF (SELECT COUNT(*) FROM (
        SELECT *  -- This SELECT obtains all promotion rows that violate temporal RI 
        FROM (
            SELECT prodID, promo_start, promo_end,
                -- check whether a product row contains promotion.promo_start
                (SELECT COUNT(*) FROM product_avail p
                  WHERE p.ProdID = promotion.ProdID
                    AND p.avail_start <= promotion.promo_start
AND p.avail_end > promotion.promo_start) c1,
                -- check whether a product_avail row contains promotion.promo_end
                (SELECT COUNT(*) FROM product_avail p
                   WHERE p.ProdID = promotion.ProdID
                      AND p.avail_start < promotion.promo_end
                      AND p.avail_end >= promotion.promo_end) c2
            FROM promotion) S
    -- count all promotion rows, for which either the first or the second check fails
            WHERE c1 = 0 OR c2 = 0
    ) T) > 0
    THEN SIGNAL SQLSTATE 'RI999'
        SET MESSAGE_TEXT='AT LEAST ONE PROMOTION VIOLATES TEMPORAL RI!';
    ELSE SIGNAL SQLSTATE 'RI000'
        SET MESSAGE_TEXT='TEMPORAL RI HAS BEEN SUCCESSFULLY VERIFIED!';
    END IF;
END@

Em vez de apenas contar as linhas com violações, também é possível retornar seus valores da chave principal, inseri-los em uma tabela de exceções ou executar qualquer ação personalizada que atenda os requisitos de aplicativos. Por exemplo, o procedimento na Lista 8 copia as linhas com violações de promotion em uma tabela separada e as exclui da tabela promotion.

Lista 8. Procedimento armazenado para copiar e excluir as linhas que violam a RI temporal
CREATE PROCEDURE check_temporal_integrity()
BEGIN
    INSERT INTO promo_exception
       SELECT promoID, prodID, price, promo_start, promo_end
       FROM (
            SELECT promoID, prodID, price, promo_start, promo_end,
                -- check whether a product row contains promotion.promo_start
                (SELECT COUNT(*) FROM product_avail p
                  WHERE p.ProdID = promotion.ProdID
                    AND p.avail_start <= promotion.promo_start
                    AND p.avail_end > promotion.promo_start) c1,
                -- check whether a product_avail row contains promotion.promo_end
                (SELECT COUNT(*) FROM product_avail p
                  WHERE p.ProdID = promotion.ProdID
                    AND p.avail_start < promotion.promo_end
                      AND p.avail_end >= promotion.promo_end) c2
                FROM promotion
            ) S
            -- select all promotion rows, for which either the first
            -- or the second check fails 
            WHERE c1 = 0 OR c2 = 0;
                
    DELETE FROM promotion
    WHERE promoID IN (SELECT promoID FROM promo_exception);
END@

Resumo

Este artigo explicou a noção de RI temporal e descreveu como os acionadores e procedimentos armazenados podem ser usados para impingi-la. A RI temporal entre duas tabelas temporais de período do aplicativo consiste em duas condições. Primeiro, para cada linha na tabela-filho, há uma ou mais linhas correspondentes na tabela-pai com o mesmo valor da chave. Segundo, o período de negócios de qualquer linha filho deve satisfazer o relacionamento temporal com o período de negócios das linhas-pai relacionadas. Por exemplo, pode-se desejar impingir que os períodos filho são idênticos aos períodos pai ou que eles estão contidos em um ou vários períodos pai. Diferentes cenários de aplicativos podem requerer que diferentes relacionamentos temporais sejam impingidos.

Impingir a RI temporal no nível do aplicativo não é trivial. Isso pode ser realizado no nível do banco de dados com acionadores ou procedimentos armazenados. As amostras nesse artigo podem atuar como um ponto de partida ao escrever seus próprios acionadores ou procedimentos para verificação de integridade temporal.

Recursos

Aprender

Obter produtos e tecnologias

  • Crie seu próximo projeto de desenvolvimento com o o software de teste IBM, disponível para download diretamente no developerWorks.
  • Agora é possível usar o DB2 gratuitamente. Faça o download do O DB2 Express-C, uma versão gratuita do DB2 Express Edition para a comunidade que oferece os mesmos recursos de dados centrais que o DB2 Express Edition e fornece uma base sólida para desenvolver e implementar aplicativos.

Discutir

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=828724
ArticleTitle=Gerenciando o tempo no DB2 com consistência temporal
publish-date=08062012