Projeto Físico de Banco de Muito além do CREATE TABLE...

Devido a vários e-mails que recebi após meu último artigo (Administração de Dados: Novos tempos... Velhos problemas...) solicitando que escrevesse algo sobre a implementação de um Modelo de Dados lógico para o ambiente relacional, resolvi , em vez de apresentar itens sobre modelagem de dados ER, abordar um assunto extremamente importante para o sucesso de um projeto de construção de sistemas: O Processo de Projeto Físico de Banco de Dados.

Agradeço, sinceramente, aqueles que me enviaram os e-mails e, conforme prometido, segue no final deste artigo, sugestão de solução para o exercício apresentado no artigo anterior. Salientamos que procuramos retratar na sugestão de solução, os principais objetos para satisfazer as necessidades do cenário apresentado, deixando de lado alguns detalhamentos que eventualmente fariam parte de uma solução mais detalhada.

A reunião estava agendada para manhã de uma terça-feira do mês de fevereiro. O local era o prédio principal do centro administrativo de uma grande instituição bancária do país, mais especificamente na sala 5. Como pauta teríamos a analise do banco de dados de um grande sistema que estava apresentando problemas de performance no ambiente de produção na plataforma mainframe. O sistema fora construído utilizando metodologia estruturada, linguagem Cobol, monitor de transações CICS e o SGBD DB2 da IBM. Teríamos como participantes da reunião:

• Dois analistas da empresa contratada que desenvolveu o sistema para o Banco;
• Um Administrador de dados do banco (AD);
• Um Administrador de Banco de Dados do banco (DBA);
• Gerente da Administração de Banco de Dados do Banco;
• Um Consultor externo: O Caldo, Eu.

Fui contratado para auxiliar na avaliação da situação e diagnosticar onde estava o problema. Já havia analisado o Modelo Lógico de dados e constatei que a qualidade era boa, além de estar aderente a todas as regras e normas e padrões da organização, pois o AD o havia homologado. Também tinha recebido os DDLs das tabelas (comandos SQL de criação da Tabelas) que foram implementadas. Confrontando os dois, Modelo Lógico versus tabelas, pude, graças a anos de experiência (e vários tombos, quando atuei como DBA), constatar que possivelmente um trabalho muito importante não havia sido considerado: O Projeto Físico de Banco de Dados.

O amigo leitor pode estar indagando em pensamento: Mas como poderia achar que tinha algum eventual problema somente comparando os modelos (lógico X implementado)? É que os dois eram exatamente iguais, ou seja, toda Entidade tinha se tornado uma Tabela com sua estrutura de atributos preservada, até mesmo hierarquias de Generalização/ Especialização estavam idênticas tanto no Modelo Lógico como no físico. Além disso, o conjunto de tabelas estava normalizado até a 3ª forma normal, ou seja, nenhum tipo de redundância, e não havia encontrado nenhum tipo de tabela de consolidação, ou de apoio; todas eram somente os espelhos das Entidades de negócio mapeadas no Modelo Lógico.

Claro que isso não significa que existe de fato um problema (algumas vezes este é o projeto ideal!), mas, como já mencionei, a experiência me fez notar que nestes casos eventualmente não existiu trabalho efetivo de projeto para criação do banco de dados; há um certo “cheiro”, indícios de que o DBA simplesmente implementou Tabelas, quando deveria seguir os seguintes passos:

• Analisar,
• eventualmente transformar,
• e então implementar!

Bem, voltemos à nossa reunião... Cerca de meia hora após o horário combinado (atraso em reunião: coisa super rara não é mesmo?), os analistas da empresa que desenvolveu o sistema chegam e deu-se inicio a reunião. Após breve introdução feita pelo gerente do banco um dos analistas pronunciou-se estendendo sobre a mesma uma enorme folha de papel:

― Aqui estão as Tabelas implementadas. Nosso DBA, lá da empresa, não vê nenhum tipo de anomalia que pudesse ― Antes que o mesmo pudesse terminar seu discurso, o DBA do Banco interrompeu dizendo:

― Já monitoramos alguns programas e aqui estão os relatórios que apontam uma série de acessos ineficientes, como varreduras seqüências em Tabelas, inúmeros Joins utilizando várias Tabelas, entre outros problemas. Sem falar no utilitário de Reorganização que está levando horas para a Tabela x! ― Imediatamente um dos analistas da empresa debruçou-se sobre os relatórios e, com olhos de lince, analisava os documentos apresentados. Eu apenas observava, aguardando oportunidade de manifestar-me... Após algum tempo de silêncio tumular o gerente indaga ao analista:

― Tudo bem? ― O analista então sacou sua metralhadora verbal e disparou a questionar o DBA do Banco:

― Mas este Joins são necessários, pois o programa precisa obter estas informações! ― Uma breve pausa e continuou ― E aqui diz que o acesso é sequencial e “varre” todas as linhas da Tabela, mas o programa esta selecionando linhas que obedecem a uma condição, ou seja, eu filtrei! Qual o problema? -E quanto ao utilitário de reorganização, eu nem o havia solicitado por enquanto!

O DBA então sacou também “suas armas” e em meio a um verdadeiro “tiroteio” de palavras, desordenado e sem objetivo, estabeleceu-se verdadeiro caos. Necessário era entrar na linha de tiro e interromper aquele duelo para que a reunião alcançasse seu objetivo; afinal, tinha sido contratado para diagnosticar problemas e não para assistir passivamente uma discussão nada produtiva. Assim, mesmo sem analisar em minúcias as Tabelas, já tinha verificado que minhas suspeitas eram pertinentes: Não havia sido feito um Projeto Físico de Banco de Dados!

Para tornar isto visível e iniciar um dialogo produtivo, iniciei minha estratégia:

― Senhores, importam-se se eu fizer algumas perguntas? ― Questionei olhando em direção ao gerente do Banco. O gerente deu-me anuência para falar enquanto os outros colegas, que notaram minha presença, finalmente, ficaram atentos. Continuei: ― Foram analisados os acessos sobre o Modelo Lógico? Ou ainda, existiu um confronto entre o Modelo funcional e o Modelo de Dados?

Todos ali presentes abaram a cabeça negativamente. Prossegui com minhas perguntas.

― Foram identificadas as Tabelas criticas com relação a volumes?

Novamente obtive a negativa. ― Efetuei outras questões e a resposta, através da mais contundente das linguagens, a do silencio, foi a mesma: não! Mediante as respostas obtidas, arrematei:

― Bem, senhores, podemos e iremos identificar e corrigir alguns pontos na atual implementação, mas poderá ser bastante traumático, pois sabemos que ao corrigirmos erros após implantação arcamos, quase sempre, com custos muito altos. Mas vamos fazer o que deve e pode ser feito! Todavia, recomendo que a empresa institua o processo de Projeto Físico de Banco de Dados formalmente, pois ele é a bússola que orienta o DBA para realizar implementações de banco de dados de sucesso. Lembro, que o trabalho de DBA vai muito além de somente criar Tabelas; é necessário projetar, arquitetar a base cuidadosamente; desempenhar a função de DBA na fase de construção de um banco de dados é muito, mais muito mais que implementar objetos.

O DBA do banco alegou que os cronogramas de projetos, quase sempre, estabelecem prazos irreais e devido a este fato muitas vezes somente há tempo para implementar, pois, caso contrário, a “bomba” estoura sempre na área de banco de dados.

A colocação, embora pertinente, não faz com que devamos aceitar passivamente tal situação. É necessário que as organizações repensem a questão de prazos e acordos de níveis de serviços, fazendo com que os mesmos sejam adequados a conferir qualidade aos projetos de sistema, evitando os altos custos com manutenções posteriores, além de perda de prestigio junto às áreas usuárias.

Ao final todos concordaram com nossos apontamentos e após esta reunião tive a grata oportunidade de conduzir um trabalho para sistematização dos processos da área de DBA do banco, entre eles, o PROJETO FÍSICO DE BANCO DE DADOS. Conseguimos corrigir os problemas mais críticos no sistema, mas, como eu temia, os custos e o impacto foram muito grandes.

Em um de meus artigos (SQL MAGAZINE – 22) narrei uma cena fictícia para elucidar o problema de falta de avaliação formal para modelos de dados. Contudo, a cena narrada acima é baseada em fato real e somente os nomes foram omitidos por uma questão obvia de ética. Em meus cursos, palestras, seminários ou mesmo em artigos, gosto sempre de fornecer uma visão de contexto do processo técnico que estou abordando. Seguindo esta linha veremos no próximo tópico, uma visão geral do processo de Projeto Físico de Banco de Dados.

Antes, porém, deixo ao amigo uma pergunta:

Sua empresa possui um processo formal de Projeto Físico de Banco de Dados?

O processo de Projeto físico de Banco de Dados

Podemos, em linhas gerais, definir o objetivo do processo de Projeto Físico de Banco de dados, da seguinte forma:

Especificar o Modelo Físico de Banco de Dados, levando em consideração o Modelo de Dados lógico (previamente homologado – vide SQL MAGAZINE 22 meu artigo sobre Avaliaçãoe Homologação de Modelos de Dados) e informações sobre volumes, acessos e necessidade de disponibilidade, visando garantir uma implementação com ótima performance, e assegurando aspectos como padronização, portabilidade, disponibilidade e capacidade de recuperação tempestiva dos dados.

Abaixo uma visão geral do processo, como mostra a Figura 1.

Para entendermos o contexto acima apresentado, façamos primeiramente uma rápida exploração nas Entradas do processo.

• O Modelo de Dados lógico: Modelo de Dados que visa representar o Negócio (Escopo do projeto). Normalmente elaborado utilizando o modelo Entidade-Relacionamento.
• O Mapa de Acesso Lógico (MAL): Outro conceito importante é o de Mapa de Acesso Lógico, chamado, em algumas empresas, de MAL. Esta figura importantíssima tão relegada nos últimos anos em varias organizações, é tão útil e fundamental que deveria der chamado de BOM! O Mapa de acesso lógico é um instrumento onde o desenvolvedor especifica como as funções do sistema irão “bombardear” o Modelo de Dados. Além de registrar informações sobre acessos, também pode apresentar dados sobre a periodicidade que determinada função será executada o tipo de processamento (batch ou on-line), entre outros.

Em meus trabalhos de consultoria já tive a grata oportunidade de especificar vários modelos de Mapa de Acesso Lógico, inclusive com tipos de representações diferentes. Alguns representados no nível de funções, outros já no nível de transações e programas. Em cada empresa estudamos a melhor solução, todavia, independentemente do modelo adotado o importante é possuir este tipo de documentação como parte integrante da documentação exigida pela metodologia de desenvolvimento de sistemas. A prática nos mostrou que é melhor trabalharmos no nível de macro-especificação de programas, já com apresentação dos comandos que irão acessar o Banco de dados. A pergunta que cabe aqui é: Precisamos preencher o MAL para todos os programas do sistema?

Não; recomendamos que sejam estabelecidos critérios para selecionar somente aqueles considerados mais críticos em relação à performance, requisitos do negócio, entre outros aspectos. É salutar o desenvolvimento de uma ferramenta para a elaboração do MAL, agilizando e tornando confiável o preenchimento, pois podemos efetuar consistências validando os dados em arquivos de Modelos de dados e repositórios. Abaixo segue um pequeno exemplo (não completo), na figura 3, retratando um modelo básico de MAL, apenas para entendimento.

O Relatório de apoio

Já dissertamos sobre duas entradas, porém ainda o DBA não tem instrumentos suficientes para trabalhar no Projeto Físico de Banco de Dados, pois necessita de algumas informações adicionais. Podemos elaborar este relatório isolando as informações por aquilo que chamo de Tabela foco. Mas o que significa isso Caldo?

É simples. Com base nos volumes e no perfil de acesso - analisados no Modelo de Dados e no MAL - escolhemos as Tabelas que podemos considerar mais criticas, e estas é que irão compor o nosso relatório:

Estas informações são fundamentais e deverão ser preenchidas pela equipe de desenvolvimento, sendo que se automatizarmos o processo através de uma ferramenta de apoio, muitas informações podem derivar das Entradas anteriores (Modelo de Dados e MAL). Alguns dados que este relatório pode conter:

• Necessidade de Backup por período;
• “Janelas” para execução de utilitários (como reorganização, por exemplo);
• Concorrência entre Batch e On-line § Quantidade de usuários concorrentes;
• Necessidade de expurgo.

Algumas destas informações são aferidas por estimativa com base nos levantamentos executados pela equipe de análise. Claro que além das Entradas o processo possui:

• Itens Regulatórios (Padrões, regras, Políticas de Desenvolvimento, Estrutura Tecnológica do SGBD);
• Itens de Suporte (Profissionais, SGBD);
• Saídas (Modelo Físico);
• Atividades. Com respeito às Atividades podemos sintetizar dizendo que o DBA deverá agora analisar o Modelo de Dados Lógico, o MAL e Relatório de Apoio para:
• Definir os índices a serem criados;
• Definir tipos de colunas adequados;
• Identificar Tabelas com grandes volumes de dados para eventual particionamento;
• Identificar hierarquias de generalização/especialização (Entidades Super-Tipos e SubTipos) para decidir o número de Tabelas a ser implementado;
• Descrição de Domínios discretos para eventual implementação de Constraints;
• Decidir se a Integridade Referencial será garantida pelo SGBD ou aplicação;
• Definir parâmetros para garantir a disponibilidade dos dados conforme requisitos (Nível de Lock, Acessos Concorrentes...)

Alguns exemplos

Abaixo apresentamos alguns exemplos (exemplos fictícios, porém baseados em casos reais) de como o projeto físico pode auxiliar na qualidade da implementação.

Cenário: Organização Bancária.

1. Em foco: Tabela (DOC_TRNSF - Movimentação de Transferência Eletrônica de Recursos) que possuía um volume estimado de 50.000.000 de linhas. Área de pagamento e compensação. Com base no MAL e no Relatório de apoio aferimos as seguintes informações:
   • O número de usuários concorrentes estimado era da ordem de 9000, baseados no número de agências e usuários com permissão para executar a principal transação de acesso à tabela (ambiente CICS Mainframe)
   • Cada usuário em média executando dez vezes ao dia a referida transação.
   • Grande número de inserções e inclusões de linhas efetuadas diariamente.
   • “Janela” batch muito pequena para execução de utilitários sobre a tabela. Principais requisitos de qualidade a serem garantidos pelo Projeto físico neste problema:
   • Disponibilidade dos dados evitando Timeouts e/ou Deadlocks
   • Evitar a degradação dos dados em termos de armazenamento, o que certamente favoreceria problemas de performance nas leituras e manutenções.

   A tabela foi armazenada em vários arquivos físicos diferentes, utilizando o que chamamos no DB2 zOS de *Tablespace Particionado. O particionamento permitiu a distribuição de linhas por agencias (“range” em cada arquivo), favorecendo a concorrência e performance de acesso. Esta técnica ainda nos concebeu a oportunidade de executar o utilitário de Reorganização de Dados por arquivo físico, podendo desta forma aproveitar as pequenas “janelas” batch para reorganizar a tabela gradativamente.

   * Tablespace – Arquivo do tipo VSAM – LSDS, utilizado para armazenar tabelas DB2 no ambiente mainframe.

2. Em foco: Tabela que possuía um volume estimado de 500.000 de linhas (Contrato de Empréstimo). Área de Empréstimos e Garantias. A Chave Primária da Tabela era a coluna NR_CONTR_EMPT (Numero do Contrato de Empréstimo). Com base no MAL e no Relatório de apoio aferimos as seguintes informações:
   • Vários programas Batch requisitavam, nas seleções de dados, pesquisas pela coluna NR_UND (Numero da Unidade), bem como reclamavam receber os dados classificados pela referida coluna em ordem descendente.
   • A mesma coluna sofria processamento semelhante nas transações do ambiente on-line.
   • Principais requisitos de qualidade a serem garantidos pelo Projeto físico neste problema:
   • Performance na recuperação dos dados
   • Evitar “sort” demasiado pela coluna NR_UND

   Foi criado um Índice pela coluna NR_UND em ordem descendente e CLUSTER, ou seja, forçando com que as linhas da Tabela fiquem classificadas fisicamente pela coluna NR_UND. Com esta técnica aproximamos as linhas com os mesmo valores de NR_UND e ganhamos em numero de I/Os necessários na recuperação dos dados e ainda evitamos “sorts” que seriam oriundos de clausulas ORDER BY. Estabelecemos periodicidade de reorganização semanal para garantir o nível de “clusterização” em torno de 90%. Lembramos que este banco de dados foi implementado de DB2 zOS.

3. Em foco: Duas Tabelas RCLM_CLIENTE (Reclamações de Clientes) e TP_MTVO_RCLM (Tipo do motivo da Reclamação) que possuíam um volume estimado de 30.000 e 150 de linhas respectivamente. Área de Ouvidoria. Com base no MAL e no Relatório de apoio aferimos as seguintes informações:
   • Uma transação critica no ambiente on-line que necessitava ler as duas tabelas em conjunto, pois sempre que recuperava uma reclamação, necessitava obter a descrição do motivo. Além da descrição do motivo o código também deveria aparecer para o usuário, como informativo.
   • O numero de usuários concorrentes estimado era da ordem de 4000 baseado no número de agencias e usuários com permissão para executar a transação. Cada usuário em média executando cinco vezes ao dia a referida transação.
   • A tabela de Tipos de Motivos de Reclamações tinha perfil estável, ou seja, poderiam existir poucas inclusões, mas alterações e exclusões seriam quase que inexistentes.
   • Principais requisitos de qualidade a serem garantidos pelo Projeto físico neste problema:
   • Alta performance na execução da transação acima referida.

   A tabela RCLM_CLIENTE foi Desnormalizada, ferindo a terceira forma normal, ou seja, a coluna DS_MTVO foi redundada. Desta feita evitamos o “JOIN” de tabelas e eliminamos cerca de 20000 acessos/ dia a tabela TP_MTVO_RCLM. A estabilidade da coluna DS_MTVO foi fundamental para esta decisão.

   Procuramos com os exemplos acima apresentar uma pálida amostra do que realmente é este trabalho incrível de projetar bancos de dados!

Conclusões

Serei bem sucinto e objetivo em minha conclusão, relembrando alguns pontos: DBA de desenvolvimento não é sinônimo de “implementador”! O DBA de desenvolvimento é o Arquiteto que deverá projetar o coração do sistema: O Banco de Dados! Para isso as empresas devem propiciar o contexto favorável para que o DBA exerça suafunção em sua plenitude, ou seja, devem definir o Processo de Projeto Físico de Banco de Dados que, como vimos, vai muito, mais muito além do que um simples CREATE TABLE!

Um abraço do CALDO (ou Knor, ou Maggi, ou... aceito sugestões de outros apelidos, masacho que o pessoal já esgotou o repertório). Muita Saúde e Sucesso a todos e BONS PROJETOS!

Abaixo sugestão de solução do exercício apresentado no artigo anterior. Dividimos em duas visões para melhor apresentação; conferindo detalhe paraos objetos do modelo que fazem referencia a Gestão de Projetos.