SQL-magazine 073 Tuning Do Oracle

SQL73.indb 1

25/02/2010 14:30:12

1

10/28/09

8:41 AM

Projeto/ Modelagem

0904HammerBRFullx1a.pdf

6VkÊÃiÊÃiÌi «ÀiÃÃ>`Ê«À ÃiÕÊvÀiVi`À `iÊ >VÊ`iÊ >`Ã¶ VocêÊest‡ÊcansadoÊdeÊtaxasÊdeÊdistribuiç‹oÊ eÊmanutenç‹oÊmuitoÊelevadas? VocêÊgostariaÊdeÊflexibilidadeÊparaÊalinharÊoÊ modeloÊdeÊlicenciamentoÊdoÊbancoÊdeÊ dadosÊaoÊseuÊmodeloÊdeÊneg—cios?

C

M

VocêÊnecessitaÊdeÊsuporteÊadequadoÊaÊ diferentesÊplataformas?

Y

CM

MY

VocêÊj‡ÊprecisouÊdeÊumaÊcorreç‹oÊouÊ novasÊfuncionalidadesÊnoÊprodutoÊantesÊ daÊpr—ximaÊvers‹o?

CY

CMY

K

VocêÊj‡ÊprecisouÊdeÊumÊn’velÊ deÊsuporteÊdiferenciado? VocêÊgostariaÊdeÊmelhoresÊ ganhos/margens?Ê

Então é hora de conhecer o:

the right-size Database

>ÝiÊÃiÕÊ- Ê}À?ÌÃÊUÊÜÜÜ°v>ÀV°VÊUÊ££ÎnÇÓnäÓ ©Ê2009ÊFairComÊCorporation

SQL73.indb 2

25/02/2010 14:30:14

Projeto/ Modelagem

Modelo PréFabricado

Primeiros Passos em Banco de Dados

[ Mauro Pichiliani ]

Sumário

14 – Conheça na Prática os Novos Recursos do PostgreSQL 8.4 [ Carlos Eduardo Smanioto ]

22 – A Linguagem PL/Java do PostgreSQL – Parte 2

Easy

Banco de Dados/ Persistência

06 – Estudos de Caso – Projeto de Banco de Dados para Reembolso

[ Clailson de Almeida ]

Dia a Dia

Primeiros Passos em Banco de Dados

Dia a Dia

Dia a Dia

Desafio SQL

29 – Administrando o SQL Server com uma Ferramenta Web [ Nilton Pinheiro ]

38 – Compressão de dados no SQL Server [ Vladimir Michel Magalhães ]

46 – Oracle Performance Diagnostics & Tuning [ Ricardo Portilho Proni ]

Olá, eu sou o DevMan! Desta página em diante, eu estarei lhe ajudando a compreender com ainda mais facilidade o conteúdo desta edição. Será um prazer contar com sua companhia! Confira abaixo o que teremos nesta revista:

54 – Usando CDC e Trigger em Auditorias no SQL Server 2008 [ Tulio Rosa ]

62 – Desafio SQL [ Wagner Crivelini ]

Brinde na web desta edição 1) JSP e Apache Derby-Parte 1- Apresentando o aplicativo que será desenvolvido.

3

2) JSP e Apache Derby-Parte 2- Realizando a validação do usuário e da senha digitados na tela de login. 3) JSP e Apache Derby-Parte 3- Concluindo a inclusão, alteração e exclusão de registros.

Vídeos

Para visualizar acesse o link: http://www.devmedia.com.br/articles/listcomp.asp?keyword=sql73&codigobanca=haloosdt

Dê s

Gostou das vídeo aulas? O portal www.devmedia.com.br possui mais de 2 mil vídeo aulas e dezenas de cursos online sobre desenvolvimento de software! Agora você pode comprar as vídeo aulas que preferir e fazer sua própria combinação de vídeos! Saiba mais em www.devmedia.com.br/creditos

Dê seu feedback sobre esta edição!

Feedback eu sobre e s

edição ta SQL73.indb 3

A .NET Magazine tem que ser feita ao seu gosto. Para isso, precisamos saber o que você, leitor, acha da revista! Dê seu voto sobre esta edição, artigo por artigo, através do link: www.devmedia.com.br/sqlmagazine/feedback Para votar, você vai precisar do código de banca desta edição, que é: haloosdt

25/02/2010 14:30:14

Expediente

Editorial EDITORIAL

O Ano 7 - 73ª Edição 2010 - ISSN 1677918-5 - Impresso no Brasil Corpo Editorial Editor Geral Rodrigo Oliveira Spínola [email protected] Sub Editores Arilo Cláudio Dias Neto, Eduardo Oliveira Spínola e Ricardo Rezende

Atendimento ao Leitor A DevMedia conta com um departamento exclusivo para o atendimento ao leitor. Se você tiver algum problema no recebimento do seu exemplar ou precisar de algum esclarecimento sobre assinaturas, exemplares anteriores, endereço de bancas de jornal, entre outros, entre em contato com: Cristiany Queiroz – Atendimento ao Leitor www.devmedia.com.br/mancad/

Capa e Diagramação Romulo Araujo [email protected]

(21) 3382-5038

Revisão e Supervisão Thiago Vincenzo - [email protected] Coordenação Geral Daniella Costa - [email protected] Na Web www.devmedia.com.br/sqlmagazine/pagina.asp Distribuição Fernando Chinaglia Dist. S/A Rua Teodoro da Silva, 907 Grajaú - RJ - 206563-900

Kaline Dolabella – Gerente de Marketing e Atendimento [email protected] (21) 3382-5038

Publicidade Para informações sobre veiculação de anúncio na revista ou no site e para fechar parcerias ou ações específicas de marketing com a DevMedia, entre em contato com: Kaline Dolabella [email protected]

Fale com o Editor! É muito importante para a equipe saber o que você está achando da revista: que tipo de artigo você gostaria de ler, que artigo você mais gostou e qual artigo você menos gostou. Fique a vontade para entrar em contato com os editores e dar a sua sugestão! Se você estiver interessado em publicar um artigo na

revista ou no site SQL Magazine, entre em contato com os editores, informando o título e mini-resumo do tema que você gostaria de publicar: Rodrigo Oliveira Spínola - Editor da Revista [email protected]

bservamos atualmente um cenário em que as organizações estão cada vez mais dependentes de sistemas de informações. Um dos efeitos desta dependência é o aumento das exigências sobre requisitos de qualidade e desempenho. Este último é especialmente crítico em sistemas que manipulam uma quantidade muito grande de informações. Melhorar o desempenho de aplicações pode envolver diversos fatores. Um deles é justamente o ajuste fino dos bancos de dados onde as informações são mantidas. Neste contexto, a SQL Magazine destaca nesta edição uma matéria muito interessante intitulada: Oracle Performance Diagnostics & Tuning. O artigo, de autoria de Ricardo Proni, trata do método de diagnóstico de problemas de desempenho em banco de dados Oracle baseado em tempo. Tudo isto de forma bastante prática. Além desta matéria, a SQL Magazine traz nesta edição outras matérias muito interessantes envolvendo assuntos como PostgreSQL, SQL Server, Desafio SQL e Projeto de Banco de Dados.

Rodrigo Oliveira Spínola [email protected]

Rodrigo Oliveira Spínola [email protected] Editor Chefe da SQL Magazine, WebMobile e Engenharia de Software Magazine. Doutorando e Mestre em Engenharia de Software pela COPPE/ UFRJ - o maior centro de ensino e pesquisa em engenharia da América Latina. Diretor de Operações da Kali Software (www.kalisoftware.com). Autor de diversos artigos científicos sobre Engenharia de Software publicados em revistas e conferências renomadas, dentro e fora do país.

Assinatura

Mais conteúdo SQL por menos! A revista SQL Magazine é parte integrante da assinatura SQL PLUS. Para mais informações sobre o pacote SQL PLUS, acesse: http://www.devmedia.com.br/sqlmagazine/pagina.asp

SQL73.indb 4

25/02/2010 14:30:20

SQL73.indb 5

25/02/2010 14:30:22

Seção Projeto/Modelagem

Seção Banco de Dados/Persistência

Nesta seção você encontra artigos sobre projeto, análise ou modelagem de dados

Estudos de Caso – Projeto de Banco de Dados para Reembolso Modele um banco de dados para um sistema que controla o reembolso de despesas De que se trata o artigo?

Em que situação o tema é útil?

Modelagem da estrutura de armazenamento de dados para um sistema de controle dos reembolsos.

O modelo de dados para um sistema que gerencia o reembolso de despesas de funcionários em viagens a trabalho e por conta da empresa. O modelo apresentado neste artigo é útil para a construção de sistemas que lidem com o ressarcimento de valores decorrentes de gastos, além de gerenciar a aprovação e notificação do ressarcimento. A partir do modelo sugerido, podese modificar as entidades, atributos e relacionamentos para adequar a estrutura de acordo com outros tipos de sistemas que lidem com o reembolso de despesas e custos de uma empresa.

Para que serve? Oferecer um modelo inicial para a construção de um sistema que gerencia o ressarcimento de valores, podendo ser adaptado para diversos outros projetos que necessitem de um controle de contas e gastos que devem ser justificados.

O Mauro Pichiliani [email protected]

É bacharel em Ciência da Computação, mestre pelo ITA (Instituto Tecnológico de Aeronáutica) e MCP, MCDBA e MCTS. Trabalha há mais de 8 anos utilizando diversos bancos de dados, como o SQL Server, Oracle e MySQL. É colunista de banco de dados do web site iMasters. (http://www.imasters.com.br).

6 SQL73.indb 6

s funcionários e colaboradores de uma empresa precisam de diversos recursos para realizarem as suas atividades. Dentre os principais recursos destacam-se pequenas despesas e gastos com fornecedores diversos que são comuns em situações onde o profissional deve se deslocar para poder realizar o seu trabalho. Devido à natureza destas despesas e características como baixo custo, aleatoriedade e independência de um fornecedor

específico, o profissional possui uma certa liberdade para escolher onde e como estas despesas serão pagas. Além disso, este gasto é suportado por algum acordo firmado entre o profissional e a empresa, que deve indicar como será o ressarcimento do que foi gasto por meio de um processo de reembolso, ou seja, a empresa pagará para o funcionário aquilo que ele gastou com o seu dinheiro desde que o gasto seja justificado e realizado com fins profissionais e que atendam aos objetivos do negócio.

SQL Magazine - Estudos de Caso – Projeto de Banco de Dados para Reembolso 25/02/2010 14:30:24

Projeto

Assim como qualquer outro recurso da empresa que é disponibilizado para seus funcionários e colaboradores, gerenciar o reembolso dos gastos requer um modelo adequado para organizar e controlar o caixa, pois caso contrário há margem para abusos e uso indevido dos recursos. Além disso, é preciso armazenar informações relacionadas à aprovação, estimativa, saldo, histórico, notas fiscais e outras entidades que compõem um modelo de banco de dados utilizado por um sistema de controle de reembolso. A partir deste cenário este artigo apresentará como montar um modelo de banco de dados que pode ser utilizado por qualquer empresa que tenha um acordo de reembolso com seus funcionários ou colaboradores. O modelo conta com diversas entidades que abordam os principais aspectos relacionados ao controle e gerenciamento de despesas em uma empresa que possui um departamento de vendas cujos funcionários precisam viajar para atender aos seus clientes. Apesar de contemplar diversas situações, o modelo apresentado neste artigo é simples e pode ser estendido para diferentes tipos de controle de reembolso ou adaptado visando a integração com o módulo de contas a pagar e receber de um software ERP já customizado e implantado na empresa.

Entendendo o cenário Para entender como funcionará o controle de reembolso em uma empresa é preciso primeiro delimitar o escopo e escolher um cenário típico onde há a utilização desta forma de pagamento. Neste artigo vamos considerar uma empresa de tamanho médio que possui um departamento de vendas com 50 funcionários ou colaboradores e que cada funcionário deste departamento possui um superior imediato. Neste cenário é comum encontrar diversas oportunidades de vendas em locais distantes, ou seja, é preciso enviar os vendedores até o local para uma visita aos clientes, fornecedores ou parceiros. Na maioria das vezes o transporte é controlado e pago diretamente pela empresa, porém as despesas com alimentação, hospedagem, translado e outros itens devem ser pagos pelo funcionário e, dependendo do tipo de acordo de reembolso, a empresa deve efetuar o ressarcimento dos custos no início do próximo mês com prazo máximo de 60 dias. Antes de começar a detalhar como funcionará o modelo responsável pelos dados do reembolso é preciso definir como ele funciona no dia a dia e explicar alguns detalhes. O primeiro ponto a ser entendido diz respeito à utilização do reembolso. Na empresa de exemplo nem todos os funcionários possuem acesso ao programa de reembolso. Isso quer dizer que apenas aqueles que realmente precisem utilizar esta forma pagamento terão acesso a ela. Além disso, é preciso formalizar a adesão de um funcionário a este recurso através de um contrato escrito e assinado por ambas as partes (empresa e funcionário) para evitar problemas legais e também para coibir abusos. Após a assinatura do contrato um documento com algumas regras práticas para o reembolso é entregue ao funcionário. Este documento assume o formato de uma cartilha cujas regras dizem que o funcionário deve sempre utilizar o bom senso nos

gastos, procurar o melhor preço, evitar fornecedores e produtos paralelos, buscar sempre gastos com produtos que possuam uma boa relação custo/benefício, evitar exageros e agir com responsabilidade e prudência. Esta cartilha também indica alguns limites para certos tipos de gastos como, por exemplo, valor máximo da diária de hotel, limite com alimentação por dia, regras sobre a compra de moeda estrangeira e outros. A regra mais importante da cartilha diz respeito à solicitação de recibos e notas fiscais. A regra é simples: todo gasto que não tiver um recibo, nota fiscal ou comprovante equivalente não será reembolsado. O documento que comprova o gasto deve conter dados obrigatórios como o valor, nome do estabelecimento, CPF ou CNPJ, telefone e informações de contato e opcionalmente uma assinatura do responsável. Não há exceções a esta regra: se um gasto foi feito sem o documento comprovando tal ação o funcionário terá que arcar com o custo da despesa, porém pode-se trabalhar com documentos digitais. Todos os gastos estão sujeitos a auditorias internas conduzidas pelo departamento contábil da empresa. A cartilha também deixa claro que antes do reembolso é preciso aprovação do superior imediato da empresa e também do departamento contábil. Por fim, uma pequena cláusula no contrato indica que se houver abuso ou comportamento de má fé o funcionário estará sujeito a multas e cancelamento do uso de reembolsos. Três modalidades de reembolso foram definidas na empresa devido aos diversos níveis de hierarquia dentro do departamento de vendas e aos diferentes tipos de despesas necessárias às viagens dos funcionários: 1) Recebimento em espécie. Nesta modalidade o funcionário deve primeiro elaborar um orçamento com os prováveis custos durante a viagem. Assim que este orçamento for aprovado o funcionário recebe a quantia solicitada em espécie para gastar. Ao final da viagem é preciso lançar as notas no sistema e também devolver o valor que sobrou; 2) Cartão de crédito da empresa. Nesta modalidade o funcionário recebe um cartão de crédito da empresa e paga todos os gastos da viagem com este cartão corporativo. Opcionalmente pode-se deixar um talão de cheque da empresa com o funcionário; 3) Reembolso pleno. Nesta modalidade o funcionário efetua os gastos de acordo com os limites estabelecidos na cartilha e paga as contas com seus próprios recursos. Ao final da viagem é preciso lançar as notas no sistema e aguardar a notificação de reembolso. Cada uma das três modalidades possui um fluxo operacional diferente e cheio de detalhes que envolvem o cadastramento da viagem, estimativas de custos, aprovações, lançamentos de notas, notificações e outros. Cada funcionário do departamento de vendas pode utilizar qualquer uma das três modalidades, desde que tenha assinado o respectivo contrato e que esteja ciente das normas de utilização de cada uma das modalidades. Utilizaremos fluxogramas para simplificar o entendimento das modalidades iniciando pela Figura 1, que traz o fluxograma da modalidade Recebimento em espécie. Este tipo de modalidade

Edição 72 - SQL Magazine SQL73.indb 7

7

25/02/2010 14:30:24

é recomendada para viagens internacionais ou para locais que já possuem fornecedores conhecidos e com preços tabelados para os funcionários da empresa.

Figura 2. Fluxograma da modalidade de reembolso Cartão de crédito da empresa

Figura 1. Fluxograma da modalidade de reembolso Recebimento em espécie

No início do fluxograma o funcionário deve cadastrar a viagem e incluir uma estimativa razoável de gastos, inclusive indicando em qual moeda deseja receber a quantia solicitada. Em seguida é preciso aguardar pelas aprovações do superior imediato e do departamento de contabilidade. Caso a estimativa não seja aprovada o funcionário deve alterar a estimativa e submetê-la novamente. Depois das aprovações o funcionário é notificado e o sistema deve verificar se o saldo de viagens anteriores é suficiente para cobrir as despesas desta nova solicitação. Caso positivo, apenas a diferença é entregue em espécie. Caso contrário, o valor total é entregue para o funcionário, que efetua gastos e faz o lançamento das despesas no sistema. Após o lançamento o funcionário devolve o dinheiro que sobrou, caso haja algum, e espera pela aprovação deste lançamento pelo superior e pela contabilidade. Na hipótese de alguma negação da aprovação é preciso alterar o lançamento de acordo com a observação do superior ou da contabilidade. Se o funcionário gastou mais do que foi solicitado ele recebe o reembolso dentro do prazo estabelecido e é notificado pelo sistema. Se não houver nenhum reembolso o funcionário apenas recebe uma notificação informando que o lançamento de despesas está correto. A segunda modalidade de reembolso, Cartão de crédito da empresa, é parecida com a primeira, porém possui algumas diferenças. A Figura 2 mostra o fluxograma da modalidade Cartão de crédito da empresa.

8 SQL73.indb 8

A modalidade de reembolso Cartão de crédito da empresa é utilizada nas situações onde os gastos são grandes ou podem apenas ser feitos com o cartão de crédito, como compras pela internet. Apenas certos funcionários da hierarquia podem utilizar esta modalidade como, por exemplo, coordenadores, gerentes, diretores, membros do conselho presidencial e vicepresidentes. No início do fluxograma o funcionário deve indicar a viagem ou o motivo pelo qual está requisitando o cartão de crédito corporativo. Nota-se que logo em seguida ele já recebe o cartão sem precisar de uma aprovação, pois de acordo com o seu cargo o sistema já reconhece que este funcionário tem um cargo de confiança na hierarquia e é responsável o suficiente para receber o cartão. Em seguida o funcionário faz os gastos e lança as despesas junto com os comprovantes. Após o lançamento é preciso seguir o mesmo fluxo de aprovação da modalidade Recebimento em espécie, ou seja, é preciso a aprovação do superior e do departamento de contabilidade. Se houver algum reembolso feito em dinheiro este deve ser pago e notificado ao funcionário. Por fim, é preciso devolver o cartão corporativo para a contabilidade, a não ser em casos especiais onde o cartão fica alocado de forma permanente com o funcionário. Alternativamente pode-se entregar um talão de cheques da empresa para os funcionários ao invés do cartão de crédito, porém o fluxo operacional continua o mesmo. A terceira e última modalidade de reembolso, cujo nome é Reembolso pleno, é a modalidade mais comum nas empresas. Geralmente utiliza-se esta forma de reembolso quando as despesas não são grandes e a viagem é curta. Além disso, este tipo de modalidade diminui a probabilidade de riscos de abuso por parte dos funcionários, uma vez que eles é que devem pagar os custos primeiro para depois receber o valor na forma de reembolso. A Figura 3 apresenta o fluxograma da modalidade Reembolso pleno. O fluxograma da modalidade de reembolso Reembolso pleno é bem parecido com as outras modalidades. Inicialmente o funcionário cadastra a viagem e indica que utilizará a modalidade Reembolso pleno. Após a aprovação do superior e do departamento de contabilidade o funcionário recebe uma notificação, efetua os gastos e faz o lançamento das notas e comprovantes.


Projeto

Figura 3. Fluxograma da modalidade de reembolso Reembolso pleno

Após o lançamento é preciso que o superior e o departamento de contabilidade aprovem o lançamento e as despesas para só então efetuar o reembolso e notificar o funcionário. Os fluxogramas das três modalidades de reembolso são bem parecidos e contém pequenas diferenças entre si, o que indica que é possível juntar os três fluxogramas em apenas um. Entretanto, para tornar mais didática a explicação dos fluxos que representam os principais passos de cada modalidade, optou-se por apresentar os fluxogramas de forma separada. Recomenda-se também que a especificação do uso de cada modalidade de reembolso seja representada com artefatos de software mais formais, tais como diagramas de Casos de Uso e diagramas de Estados, pois desta maneira a equipe do projeto contará com mais artefatos para o entendimento, modelagem, especificação e documentação do sistema.

Modelagem das entidades A partir da explicação de funcionamento do reembolso descrita na seção anterior podemos começar a modelagem indicando quais são as principais entidades do modelo Entidade Relacionamento. A Figura 4 apresenta uma versão inicial do modelo com os nomes de 14 entidades relevantes para o banco de dados que gerenciará as modalidades de reembolso.

Esta delimitação quer dizer que é preciso identificar e abstrair certas características dos cenários de modo a melhorar o modelo para que ele represente a realidade apenas nos aspectos que fazem sentido para o banco de dados relacionado ao contexto do reembolso. Na Figura 4 podemos notar que as entidades FUNCIONÁRIOS e CARGOS fazem parte do modelo, mas no modelo final não as detalharemos. Estas entidades geralmente já estão presentes em sistemas de RH (Recursos Humanos) ou de controle de acesso e não faz muito sentido repeti-las no banco de dados. Devido a isso, o modelo do sistema de reembolso vai utilizar apenas chaves estrangeiras para a entidade de funcionário e não conterá nenhuma relação com a entidade de cargos, pois a partir de um funcionário é possível obter o seu cargo. Esta abstração, ou seja, deixar de fora o detalhamento destas duas entidades, apresenta um refinamento no modelo, focando no objetivo e eliminando elementos que já existem em outro sistema. Outro detalhe importante é que os funcionários que devem aprovar algo, sejam superiores ou funcionários da contabilidade, são todos considerados funcionários já armazenados na entidade FUNCIONÁRIOS. Outra entidade que também pode ficar de fora do modelo final é a entidade FORNECEDORES, pois apesar de ser importante contar com algumas informações sobre fornecedores, raramente o mesmo fornecedor será cadastrado devido à aleatoriedade dos locais de despesas. Os dados relevantes do fornecedor serão armazenados em uma entidade que contém os comprovantes. Aliás, o levantamento inicial identificou as entidades NOTA FISCAL, COMPROVANTES e RECIBOS, que detalham a forma na qual o funcionário comprova seus gastos. Para simplificar, o modelo final contará apenas com a entidade COMPROVANTES, que terá um atributo responsável por indicar qual é o tipo do comprovante. Outra abstração do modelo inicial diz respeito às entidades CARTÕES e CHEQUES, que representam os cartões de crédito e talões de cheque da empresa. Estas duas entidades serão transformadas em apenas uma entidade que armazena o recurso de pagamento ao invés de duas entidades separadas. A primeira entidade que detalharemos é a principal entidade do nosso modelo: VIAGEM_FUNCIONARIO. Esta entidade é utilizada em qualquer uma das três modalidades de reembolso, pois a atividade cadastrar viagem aparece nos três fluxogramas

Nota do DevMan Figura 4. Quatorze entidades iniciais do modelo de controle de reembolso

As entidades apresentadas na Figura 4 representam apenas o levantamento inicial do que será modelado, ou seja, a partir da explicação das modalidades de reembolso criaram-se diversas entidades. A partir dessas entidades o modelo será construído por sucessivos refinamentos e melhorias. Porém, é importante delimitar o escopo e identificar até que ponto as entidades modeladas fazem parte ou não do banco de dados.

Atributo flag: Existem diversas definições do que é um atributo flag. A mais comum diz que um atributo flag é aquele que permite apenas dois tipos de valores: 0 ou 1, também referenciados como SIM/NÃO ou VERDADEIRO/FALSO (TRUE/ FALSE). Geralmente atributos flag são utilizados para modelar características ou aspectos binários que podem existir ou não sem nenhum tipo de meio termo. A implementação depende do banco de dados em questão, mas é comum o uso do tipo de dados booleano onde apenas 0 ou 1 são utilizados para armazenar os valores de atributos do tipo flag.


9

25/02/2010 14:30:25

descritos na seção anterior. Esta entidade possui um atributo com o identificador sequencial, que é a chave primária da entidade, e um atributo para armazenar o identificador do funcionário que se relaciona a uma entidade já existente em um sistema de RH ou de acesso que não será modelada para evitar duplicidade no banco de dados. Da forma que a entidade VIAGEM_FUNCIONARIO foi modelada é preciso cadastrar uma nova viagem para cada funcionário, pois a viagem será associada com o reembolso que é individual. Uma tela do sistema pode facilitar a entrada de dados apresentando apenas uma vez os campos para digitação dos dados da viagem e apresentar uma planilha ou grid para a indicação de vários funcionários para uma mesma viagem. A entidade também contém atributos para indicar a data de partida e estimativa de data de retorno, a descrição do local para onde o funcionário está indo e também um atributo que permite a entrada de um texto longo para descrever o motivo da viagem, cliente ou parceiro a ser visitado, a companhia de viagem e outros detalhes relevantes. Como é preciso a aprovação tanto do superior imediato como do departamento de contabilidade, a entidade possui dois atributos para armazenar o status da aprovação. Este status é armazenado por meio de atributos flag que geram o status atual da solicitação da viagem, ou seja, indicam se a viagem não foi aprovada (valor 0) ou se foi aprovada com sucesso (valor 1). Nota-se que a partir do conteúdo destes atributos o sistema automaticamente pode informar para o usuário qual é o status da viagem e por isso não foi modelado um atributo específico para indicar o status como, viagem aprovada, pendente ou negada. Também é preciso incluir atributos para indicar quais são os funcionários que fazem as aprovações, ou seja, quem é o funcionário superior e quem é o funcionário do departamento de contabilidade que fará a aprovação. Por fim, a entidade possui um atributo para armazenar a estimativa de gastos da modalidade Recebimento em espécie, um atributo de texto livre e longo para informações sobre o transporte (número da passagem aérea, horário de partida e chegada, etc.) e também qual será a moeda utilizada no destino da viagem. Quando o funcionário está começando a preencher os detalhes da viagem ele já deve indicar qual será a modalidade de reembolso utilizada. Para armazenar esta informação é preciso criar no modelo a entidade MODALIDADE_REEMBOLSO, que contém um identificador único e atributos para armazenar o nome da modalidade (Recebimento em espécie, Cartão de crédito da empresa ou Reembolso pleno) e observações de cada modalidade. Além disso, é preciso adicionar na entidade VIAGEM_FUNCIONARIO um atributo para indicar o relacionamento com a entidade MODALIDADE_REEMBOLSO. A Figura 5 apresenta as entidades MODALIDADE_REEMBOLSO e VIAGEM_FUNCIONARIO junto com o detalhamento de seus atributos em um modelo entidade relacionamento parcial. A próxima entidade a ser modelada diz respeito à notificação do funcionário. Esta notificação deve ser realizada de acordo com os contatos individuais de cada funcionário, que são modelados em uma entidade chamada

10 SQL73.indb 10

CONTATOS_FUNCIONARIO. Esta entidade conterá um identificador único do contato, uma chave estrangeira para a entidade de funcionários, o número de telefone, o endereço de e-mail e também endereços na rede de microblogs, como Twitter e um endereço para a rede social Orkut. Outras formas de notificação podem ser utilizadas de acordo com as formas de entrar em contato com o funcionário, porém este modelo apenas irá considerar as formas de contato descritas acima.

Figura 5. Entidades VIAGEM_FUNCIONARIO e MODALIDADE_REEMBOLSO do modelo de controle de reembolso

Para associar uma forma de contato do funcionário com uma viagem é preciso criar uma nova entidade, que se chamará NOTIFICA_FUNCIONARIO. Esta entidade contém o identificador da viagem e o identificador do contato. Além disso, foram criados atributos flags que indicarão quais contatos serão utilizados para notificar a aprovação da viagem e o reembolso das despesas, que podem assumir a forma de um telefonema, um e-mail, uma mensagem SMS, uma mensagem do tipo direct no Twitter ou uma mensagem na área pública (scrap) ou privada (depoimento) do Orkut, dependendo do endereço cadastrado nesta rede social. A Figura 6 apresenta o modelo parcial com as entidades VIAGEM_FUNCIONARIO, CONTATOS_FUNCIONARIO e NOTIFICA_FUNCIONARIO. Após a modelagem das entidades responsáveis pela notificação do funcionário é preciso indicar como os cartões de crédito e talões de cheque devem ser associados a um funcionário. A entidade RECURSO_PAGAMENTO conterá um identificar único, o nome do recurso (cartão, cheque, travellers check, etc.), uma chave estrangeira para a entidade de funcionários que indica com quem este recurso está atualmente, um atributo indicando o limite máximo de gasto deste recurso, um identificador interno (quatro últimos dígitos do cartão ou número do cheque) e também um atributo para observações de uso deste


Projeto

recurso de pagamento. Nota-se que as instâncias da entidade RECURSO_PAGAMENTO são associadas ao funcionário e não a uma viagem e que cada vez que o recurso trocar de funcionário é preciso alterar o atributo ID_RESPONSAVEL_ATUAL. A seguir devemos modelar a entidade que armazena o tipo de comprovante, que recebe o nome TIPO_COMPROVANTE. Esta entidade possui uma chave primária no atributo ID_TIPOCOMPROVANTE e contém atributos para o nome do comprovante (nota fiscal, recibo, comprovante, etc.) e um atributo flag que indica se este comprovante possui valor fiscal ou não. O lançamento dos comprovantes de gastos nas viagens do funcionário será armazenado na entidade COMPROVANTES. Esta entidade contém um identificador único (ID_GASTO) do gasto, um atributo que é chave estrangeira para a entidade TIPO_COMPROVANTE (ID_TIPOCOMPROVANTE), um atributo que é chave estrangeira para a entidade VIAGEM_FUNCIONARIO (ID_VIAGEM), e atributos para armazenar o CNPJ ou CPF, nome e telefone de contato do fornecedor, armazenados nos atributos CNPJ, CPF, NOME_CONTATO, DATA e TEL_CONTATO, respectivamente. A entidade também possui atributos para armazenar a data do comprovante (DATA_COMRPOVANTE), descrição para indicar circunstâncias, detalhes e motivo do gasto (DESC_GASTO), qual a forma e condições de pagamento (FORMA_PAGAMENTO) e um atributo flag para indicar se o gasto foi para se obter um serviço ou um produto. A Figura 7 apresenta o modelo parcial com as entidades já modeladas anteriormente e as novas entidades RECURSO_PAGAMENTO, COMPROVANTES e TIPO_COMPROVANTE. A última entidade a ser modelada armazenará as informações a respeito do reembolso da viagem. Esta entidade conterá uma chave primária no atributo ID_REEMBOLSO e uma chave estrangeira no atributo ID_VIAGEM, que fará o relacionamento com a entidade VIAGEM_FUNCIONARIO. A entidade conta também com um atributo para armazenar o valor do reembolso,

Figura 6. Entidades VIAGEM_FUNCIONARIO, NOTIFICA_FUNCIONARIO e CONTATOS_FUNCIONARIO adicionadas ao modelo de controle de reembolso

o saldo do funcionário no momento em que o reembolso for feito, as informações sobre os identificadores dos usuários que aprovam (superior e contabilidade) e os atributos flags para identificar o status das aprovações. Assim que o reembolso for aprovado é preciso armazenar a data do reembolso, o número do cheque caso esta seja a forma de pagamento escolhida ou as informações bancárias para pagamento (banco, agência e conta). Por fim, qualquer observação a respeito do pagamento deve ser indicada em um atributo de texto livre. A Figura 8 apresenta o diagrama com o modelo de banco de dados final para o sistema de controle de reembolso de despesas.

Figura 7. Modelo anterior com a adição das entidades RECURSO_PAGAMENTO, COMPROVANTES e TIPO_COMPROVANTE


11

25/02/2010 14:30:26

Figura 8. Modelo final contendo todas as entidades do banco de dados para controle de reembolsos

Nota do DevMan

Nota do DevMan

12 SQL73.indb 12

de novas entidades relacionadas com o reembolso de despesas de uma empresa e também para outros fins, como o controle de gastos com matéria prima, ingredientes, gerenciamento de conta de materiais para projetos e outros tipos de situações que necessitem de ressarcimento posterior dos gastos. Referências Artigo “Análise de Requisitos com Casos”, escrito pelo Kleber Xavier e publicado na edição número 49 da revista Java Magazine.

Dê seu feedback sobre esta edição! A SQL Magazine tem que ser feita ao seu gosto. Para isso, precisamos saber o que você, leitor, acha da revista! Dê seu voto sobre este artigo, através do link: www.devmedia.com.br/sqlmagazine/feedback


Este artigo mostrou como modelar as principais entidades de um sistema que controla o reembolso de despesas dos funcionários de uma empresa durante viagens. O modelo apresentado foi gerado a partir de um cenário típico onde funcionários/ colaboradores precisam viajar para atender clientes, parceiros, ou fornecedores e efetuam gastos durante esta viagem. Um modelo de dados foi elaborado a partir dos fluxogramas das três modalidades de reembolso utilizadas pela empresa. Este modelo contém entidades, atributos e relacionamentos que representam viagens, gastos, comprovantes, aprovações, notificações, recurso de pagamento e outros aspectos relacionados ao processo de reembolso de acordo com as modalidades especificadas. O modelo de dados foi apresentado de acordo com a modelagem de cada uma das entidades e seus relacionamentos. Apesar de ser apenas uma sugestão, as principais entidades do cenário descrito foram contempladas pelo modelo de dados, que pode servir como ponto de partida para a modelagem

Dê s

Conclusão

Travellers checks: O traveller check, ou cheque de viagens, é uma forma cômoda e segura de levar dinheiro em viagens internacionais. É aceito em hotéis e em diversos estabelecimentos comerciais em todo o mundo, como lojas e restaurantes, e também pode ser facilmente trocado pelo dinheiro local em instituições financeiras ou postos de câmbio. Os estabelecimentos comerciais, no entanto, podem não aceitar este tipo de pagamento, forçando a troca de um traveller check pela moeda local em uma casa de câmbio ou instituição financeira. O traveller check requer a assinatuda do portador, pode ser em dólar, euro ou outras moedas e é aceito prinicipalmente nos Estados Unidos e raramente em países cuja moeda local não seja o dólar. Neste caso, será preciso realizar uma operação de câmbio, como compra de moeda em espécie.

edição ta

Software ERP: ERP (do inglês Enterprise Resource Planning) são os sistemas de informações que integram todos os dados e processos de uma organização em um único sistema. A integração pode ser vista sob a perspectiva funcional (sistemas de finanças, contabilidade, recursos humanos, fabricação, marketing, etc.) e sob a perspectiva sistêmica (sistema de processamento de transações, sistemas de informações gerenciais, sistemas de apoio a decisão, etc.). Estes sistemas envolvem a parte administrativa da empresa e geralmente são de missão crítica, pois envolvem processos fundamentais para o funcionamento da empresa.


Projeto

Modéstia à parte, sua melhor opção para se destacar no mercado! A Escola Superior da Tecnologia da Informação oferece as melhores opções em cursos, formações, graduações e pós-graduações para profissionais de desenvolvimento e programação. São programas voltados para a formação de profissionais de elite, com aulas 100% práticas, corpo docente atuante no mercado, acesso à mais atualizada biblioteca de TI do Rio, laboratórios equipados com tecnologia de ponta, salas de estudo e exames.

PÓS- GRADUAÇÃO

Engenharia de Software: Desenvolvimento Java Engenharia de Software: Desenvolvimento .NET GRADUAÇÃO

Engenharia de Computação Análise e Desenv. de Sistemas F ORMAÇÕES

Desenvolvedor Java Desenv. Java: Sist. Distribuídos Gestor de TI Desenvolvedor Web .NET 2008 MCITP Server Administrator SQL Server 2008

r/esti Acesse nosso site e conheça todos os nossos programas: www.infnet.edu.br/esti TURMAS NO RIO DE JANEIRO www.infnet.edu.br - [email protected] - Central de Atendimento: (21) 2122-8800

EDUCAÇÃO SUPERIOR ORIENTADA AO MERCADO Edição 72 - SQL Magazine SQL73.indb 13

13

25/02/2010 14:30:27



Nesta seção você encontra artigos sobre banco de dados, SQL ou persistência

Conheça na Prática os Novos Recursos do PostgreSQL 8.4 Projete um Sistema de Companhia Aérea com o novo PostgreSQL

O

objetivo deste artigo é modelar e projetar o sistema para uma companhia aérea que desejar um maior controle de seus vôos, funcionários e bilhetes emitidos. Para isso, serão demonstradas algumas das novidades da versão 8.4 do PostgreSQL, cuja versão saiu no início de Julho de 2009. Dentre as novidades, podemos citar o windowing functions, consultas recursivas e criação de valor padrão em parâmetros de função.

Novas Funcionalidades do PostgreSQL Carlos Eduardo Smanioto www.datapower.com.br [email protected]

Diretor da Data Power Technology, uma empresa de consultoria e Business Soluctions na área de T.I. É Formado em Ciências da Computação, atuando a mais de 10 anos com banco de dados PostgreSQL e segurança da informação. Possui em seu currículo diversos artigos, palestras realizadas. Trabalha com projetos. NET e JAVA adotando banco de dados Oracle/PostgreSQL e prestação de serviço como por exemplo, treinamento IN-COMPANY.

14 SQL73.indb 14

Antes de iniciarmos a modelagem e projeto do sistema de uma companhia aérea, precisamos conhecer alguns dos novos recursos disponibilizados pela nova versão 8.4 do PostgreSQL, que será utilizado para o projeto do banco de dados deste sistema. O PostgreSQL 8.4 possui mais de 293 novidades, muitas mudanças para deixar o SGDB mais competitivo contra outros SGBDs como o SQL Server e Oracle no que diz respeito a funcionalidades, outras melhorias buscam praticidade,

De que se trata o artigo? O artigo demonstra o projeto de um sistema de uma companhia de passagem aérea, apresentando algumas das novas funcionalidades do PostgreSQL 8.4

Para que serve? Através deste artigo, o leitor terá uma visão prática das decisões de projeto a serem tomadas na modelagem de um sistema que gerencia tarefas de uma companhia aérea, e ainda uma visão ampla sobre a nova versão do PostgreSQL, onde não apenas são apresentadas as principais funcionalidades, mas também foi ensinado como explorá-las.

Em que situação o tema é útil? Este artigo ajuda o DBA PostgreSQL na modelagem de sistemas com características similares ao projeto utilizado como estudo de caso, e ainda auxilia a equipe de desenvolvimento a decidir qual é o momento certo de realizar um upgrade de versão do PostgreSQL.

segurança e velocidade. Neste momento, iremos apresentar algumas que mais chamam a atenção. Vamos conhecê-las? Elas estão citadas na Tabela 1.

SQL Magazine - Conheça na Prática os Novos Recursos do PostgreSQL 8.4 25/02/2010 14:30:28

postgresql

O arquivo postgresql.conf Para entender melhor os novos recursos do PostgreSQL, precisamos conhecer alguns novos elementos incluídos no arquivo de configuração do PostgreSQL: o postgresql.conf. É lógico que o postgresql.conf continua basicamente o mesmo, exceto que ele se tornou maior, ele esta agora 65 linhas a mais. Um dos principais motivos para isso é a configuração de certificado SSL, nova feature do PostgreSQL que permite a utilização de um certificado de segurança, deixando assim o sistema mais seguro. Existem também novidades no gerenciamento de I/O, conforme apresentado na Listagem 1. Esta configuração permite ao PostgreSQL realizar um número “n” de operações de I/O no disco por sessão. A documentação sugere que este valor seja o número de HDs disponível para o PostgreSQL em um RAID do tipo 0 ou 1. Funcionalidades

Caso seja RAID 5, contar os HD desconsiderando o HD que esta separado para ser paridade. Por exemplo, em um RAID 0 ou 1 ou RAID 0+1, se o sistema possuir 4 HDs, o valor de effective_io_concurrency será 4; mas em um RAID 5, se forem 4 HDs, o valor será 3. Também foi adicionado à configuração o seguinte trecho apresentado na Listagem 2. Estas configurações dizem ao Vacuum a idade em transações. Quando os valores em idade transacional são atingidos pelo sistema, o VACUUM reinicia estes valores dentro da tabela. A cada nova transação, esta idade é acrescida. É uma espécie de “sequence” das transações. Em resumo, esta nova versão está demonstrando a vontade da comunidade PostgreSQL em realmente manter o “elefante” como uma solução para o mercado não somente pelo quesito custo, mas pelo quesito desempenho e funcionalidades. O que isso faz

Windowing Functions

As Funções de Janelas ou “agregados de janelas” são um conjunto de functions que permitem que você faça operações como count, sum, e rank sobre (over) um subconjunto de seus dados, isso sem agrupamento. Com as windows functions, podemos gerar um relatório que demandava várias consultas com uma única consulta. Estas windows functions também ampliam o suporte do PostgreSQL para aplicações do tipo B.I. (Business Intelligense). Veremos mais sobre como isso funciona mais adiante. Expressões de tabelas comuns e consultas Conhecidas como CTEs (Common Table Expressions and Recursive Queries). O Principal uso da CTEs é possibilitar subconsultas complexas sem criar tabelas recursivas temporárias e principalmente permitir a criação de consultas recursivas. Isso foi uma importante implementação para o PostgreSQL, pois esta capacidade já existia no SQL Server desde a versão 2005. ALTER SEQUENCE, VIEW, DATABASE Foi melhorada a lógica do comando ALTER para cada tipo de objeto. Por exemplo, agora é possível reiniciar um sequence para voltar ao valor inicial através de um simples ALTER SEQUENCE RESTART. É possível agora adicionar novas colunas em uma view diretamente pelo comando ALTER VIEW ADD COLUMN O Alter também ganhou poderes no DATABASE. Agora podemos mover fisicamente os objetos de um Banco de Dados para um novo tablespace. Isso significa que em caso de falta de espaço, via ALTER podemos mover uma tabela para outra partição, unidade, enfim, um lugar com espaço disponível. Parâmetros DEFAULT e Variáveis para Functions É possível agora criar um número variável de argumentos para uma function. Além disso, é possível definir um valor padrão para argumento em funções. Assim, quando a função for invocada e não passar argumentos para ele, o valor padrão assume como argumento. Estas mudanças facilitam a migração do SQL Server e Sysbase para PostgreSQL. Restauração paralela A ferramenta pg_restore agora pode restaurar dados e criar objetos paralelamente, ou seja, enquanto esta criando a tabela2, ele está subindo os dados da tabela1. Isso gerou um aproveitamento melhor do hardware. Segurança por Coluna É possível nesta versão atribuir não somente segurança na Tabela (GRANTs/REVOKE) mas também por coluna. Configuração Regional (locale) por DB Melhoria de desempenho com índices hash Semi-joins e Anti-joins

Configuração automática para o Free Space Map

Editor de Function

A configuração locale pode ser configurada individualmente por DB, ou seja, podemos ter um PT_BR.UFT8 para um banco e ISO para outro. Nas versões anteriores, o LOCALE era configurado por cluster; ou na criação do cluster através com comando initdb ou via postgresql.conf. Índices hash do PostgreSQL agora localizam linhas simples mais rápido do que os índices B-Tree, e tornaram-se úteis para a indexação de campos identificação em alguns bancos de dados. O Sistema agora consegue escolher o melhor método de execução para subconsultas semanticamente semelhantes. Desta forma, o desenvolvedor não precisa verificar o que seria mais rápido: t1.c1 = t2.c2 ou t2.c2 = t1.c1 ? Exemplo de Semi-join: SELECT D.deptno, D.dname FROM dept D WHERE EXISTS ( SELECT 1 FROM emp E WHERE E.deptno = D.deptno ) ORDER BY D.deptno Fim da configuração de max_fsm_page. Nas versões anteriores, o PostgreSQL usava a Shared Memory para alocar um mapa com informações de páginas vazias, ou seja, lugares que podem ser ocupados com novos registros, por exemplo após um delete. Este Mapa agora é dinâmico e fica no próprio HD. A grande vantagem disso é reduzir I/O, ou seja, aumenta o desempenho da aplicação. Agora o psql possui o /ef nome_da_function que abre um editor com a function para ser editada.

Tabela 1. Algumas das 300 novas funcionalidades da série 8.4


15

25/02/2010 14:30:28

Listagem 1. postgresql.conf – pedaço do novo arquivo conf 139. # - Asynchronous Behavior 140. 141. #effective_io_concurrency = 1

# 1-1000. 0 disables prefetching

Listagem 2. postgresql.conf – pedaço do novo arquivo conf 400. #autovacuum_freeze_max_age = 200000000 forced vacuum 425. #vacuum_freeze_min_age = 50000000 426. #vacuum_freeze_table_age = 150000000

# maximum XID age before

Vamos agora demonstrar algumas possibilidades desta nova versão com a incorporação do SQL ANSI 2008.

Projetando o sistema de uma companhia aérea: a Brazilian Airlines Nosso projeto modelo consiste em desenhar um banco de dados para a nossa fictícia companhia área que surgiu no Brasil, a BRAZILIAN AIRLINES. Esta companhia fez um estudo tecnológico e resolveu adotar o PostgreSQL como banco de dados para o sistema que estão desenvolvendo. A princípio, o sistema irá precisar controlar os funcionários e gerar um MAPA de vôo com possibilidade de escalas e logicamente um controle de seus aviões com informações sobre quantidade de passageiros, para que possa vender os bilhetes para um determinado vôo sem vender passagem fora do limite. Por isso, o sistema precisa: 1) O pessoal do RH quer saber informações sobre o salário dos departamentos; 2) Exibir na tela os vôos que os Engenheiros de vôo criaram; 3) Na venda do Bilhete, ter um controle de venda dentro do limite do avião selecionado para um vôo. Ao longo deste artigo, veremos como atender às demandas requisitadas para o sistema em questão, demonstrando novas funcionalidades do PostgreSQL 8.4. Para começar, vamos conhecer a configuração das tabelas, conforme as Figuras 1 e 2 nos mostra.

Figura 2. Tabela funcionario e auxiliar

Na Figura 1, estamos dizendo que o vôo é o elemento principal do modelo, através da tabela voos, pois só podemos vender uma passagem aérea se existir um vôo cadastrado. Isso também justifica a coluna “voo_num” na tabela bilhete. Observe na tabela voos a última coluna, qtd_disponivel: esta coluna contém a quantidade de passagens que podem ser vendidas para cada vôo cadastrado. Além disso, um vôo só pode existir se existir um avião disponível para aquele vôo, por isso relacionamos a tabela voos com a tabela aviao. Na Figura 2, nós atribuímos os funcionários (tabela funcionario) ao seu respectivo departamento (tabela departamento). É um relacionamento N para 1.

Povoando o Banco de Dados As Listagens 3 a 7 apresentam códigos SQL que iremos seguir em nosso projeto para preencher as tabelas criadas com dados que serão usados para demonstrar as novas funcionalidades disponibilizadas no PostgreSQL 8.4.

Requisição 1) O pessoal do RH quer saber informações sobre o salário dos departamentos Na Brazilian Airlines, existem 8 departamentos com diversos funcionários vinculados a estes departamentos. O departamento de Engenharia de vôo aloca os pilotos, co-pilotos, aeromoças que ganham os maiores salários da companhia. Os chefes de cada departamento ganham um salário diferenciado por ocupar um cargo de maior responsabilidade. Neste exemplo, a diretoria da empresa quer saber a média de salários dos departamentos. Como o departamento de computação adotou o PostgreSQL 8.4, os analistas podem solucionar a solicitação da diretoria usando uma Windows Function (ver Nota DevMan 1). Vamos ver como a Windowing Function pode ser muito útil na hora de gerar um relatório. Iremos realizar uma consulta que irá nos mostrar o salário do funcionário, departamento pertencente

Nota do DevMan 1 Detalhando as Windows Functions As Windowing functions são uma implementação de suma importância introduzida na linguagem SQL:2003 (revisão realizada em 2003 na SQL ANSI). Esta revisão da SQL permite funções de agregação sobre os dados. Estas funcionalidades permitem não somente ao PostgreSQL agradar aos desenvolvedores do SQL Server e Oracle, como também veio para facilitar ao desenvolvedores de muitos anos PostgreSQL. Figura 1. Tabela voos e as tabelas auxiliares

16 SQL73.indb 16


postgresql

e a média por departamento de uma única vez de uma forma extremamente simples! Tal consulta está descrita na Listagem 8. A consulta irá gerar a média salarial por departamento, mas como funciona isso? Antes da Windowing Functions, para gerar um resultado que iremos ver a seguir, precisaríamos usar uma consulta muito complexa agrupada com GROUP BY, talvez até mesmo um subselect ou outras técnicas, isso dependeria de como o desenvolvedor enxergasse a solução. Mas agora, quando usamos funções de agrupamento (como AVG, por exemplo) podemos definir o mecanismo deste agrupamento com a sintaxe OVER (PARTITION BY tabela). Veremos na Listagem 9 como seria a sintaxe em português do código SQL apresentado na Listagem 8, para ficar mais claro. A Windowing Functions só surtiu efeito porque existe um relacionamento entre as tabelas funcionário e departamento, mas podemos perceber claramente o vínculo entre os comandos AVG() e o OVER(Partition By) que gera o agrupamento em função da tabela especificada. A consulta da Listagem 8 gera o resultado apresentado na Figura 3.

Listagem 3. Inserindo dados na tabela aviao INSERT INTO aviao (aviao_num, aviao_modelo, aviao_fabricante, aviao_ultima_revisao, aviao_habilitado_vôo, qtd_passageiros) VALUES (‘A330’, ‘Boing Airbus’, ‘2004’, ‘2009-05-10’, true, 400); INSERT INTO aviao (aviao_num, aviao_modelo, aviao_fabricante, aviao_ultima_revisao, aviao_habilitado_vôo, qtd_passageiros) VALUES (‘EMB-120’, ‘EMBRAER EMB-120 Brasilia’, ‘1983’, ‘2009-0813’, true, 250); INSERT INTO aviao (aviao_num, aviao_modelo, aviao_fabricante, aviao_ultima_revisao, aviao_habilitado_vôo, qtd_passageiros) VALUES (‘ERJ-145’, ‘EMBRAER ERJ-145’, ‘1995’, ‘2009-10-27’, true, 350);

Listagem 4. Inserindo dados na tabela voos INSERT INTO vôos (vôo_num, cidade_partida, cidade_chegada, hora_ saida, hora_chegada, aviao_num, qtd_disponivel) VALUES (‘BZ-2010001’, ‘RIO DE JANEIRO’, ‘SAO PAULO’, ‘07:00:00’, ‘08:00:00’, ‘EMB-120’, 245); INSERT INTO vôos (vôo_num, cidade_partida, cidade_chegada, hora_ saida, hora_chegada, aviao_num, qtd_disponivel) VALUES (‘BZ-2010004’, ‘RIO DE JANEIRO’, ‘BAURU’, ‘07:00:00’, ‘10:00:00’, ‘ERJ-145’, 345); INSERT INTO vôos (vôo_num, cidade_partida, cidade_chegada, hora_ saida, hora_chegada, aviao_num, qtd_disponivel) VALUES (‘BZ-2010002’, ‘SAO PAULO’, ‘BAURU’, ‘08:30:00’, ‘09:30:00’, ‘EMB-120’, 248); INSERT INTO vôos (vôo_num, cidade_partida, cidade_chegada, hora_ saida, hora_chegada, aviao_num, qtd_disponivel) VALUES (‘BZ-2010003’, ‘SAO PAULO’, ‘GOIANIA’, ‘09:00:00’, ‘14:00:00’, ‘A330’, 398); INSERT INTO vôos (vôo_num, cidade_partida, cidade_chegada, hora_ saida, hora_chegada, aviao_num, qtd_disponivel) VALUES (‘BZ-2010005’, ‘RIO DE JANEIRO’, ‘GOIANIA’, ‘07:00:00’, ‘13:00:00’, ‘A330’, 399);

Listagem 5. Inserindo dados na tabela bilhete INSERT INTO bilhete (bilhete_num, valor, desconto, valor_total, viagem_classe, nome_passageiro, vôo_num) VALUES (‘B2-0000001’, 100, 0, 100, 2, ‘Joao Alverto Silva’, ‘BZ-2010001’); . . PARA DOWNLOAD DISPONÍVEL COM DADOS COMPLETOS, VEJA LINK NO FINAL DO ARTIGO . INSERT INTO bilhete (bilhete_num, valor, desconto, valor_total, viagem_classe, nome_passageiro, vôo_num) VALUES (‘B1-0000013’, 585, 0, 585, 1, ‘Ronaldo Mazzio’, ‘BZ-2010005’);

Listagem 6. Inserindo dados na tabela departamento

Figura 3. Resultado do Select com Windowing function

Com isso atendemos ao primeiro pedido solicitado para o sistema da Brazilian Airlines.

Requisição 2) Exibir na tela os vôos que os Engenheiros de vôo criaram Agora iremos atender ao segundo pedido feito para o sistema. Os Engenheiros de Vôo da Brazilian Airlines criaram um plano de vôo para atender as cidades de Bauru, Goiania, Rio de Janeiro e São Paulo. Desta forma, a companhia aéria cobre 3 grandes cidades, além de Bauru, interior do Estado de São Paulo, devido a interligação rodoviária que esta cidade tem com o centro-oeste paulista. Os Engenheiros criaram também os horários de saída e calcularam a hora aproximada de chegada. Rio de Janeiro e São Paulo possuem mais possibilidades de vôo do que Goiânia e Bauru devido à baixa demanda. Entretanto, pode acontecer de um determinado vôo fazer um percurso maior para atingir uma cidade próxima devido a alguns fatores como, por exemplo,

INSERT INTO departamento (id_departamento, departamento, ramal_ departamento) VALUES (1, ‘Marketing’, ‘6110’); . . PARA DOWNLOAD DISPONÍVEL COM DADOS COMPLETOS, VEJA LINK NO FINAL DO ARTIGO . INSERT INTO departamento (id_departamento, departamento, ramal_ departamento) VALUES (8, ‘Faturamento’, ‘6117’);

Listagem 7. Inserindo dados na tabela funcionario INSERT INTO funcionario (id_funcionario, funcionario, id_ departamento, cargo, salario, recebe_cesta_basica, recebe_plano_ saude, emprestimo_consignado, chefe_departamento) VALUES (2, ‘Marcos Nobrega’, 1, ‘Publicitário’, 1500, true, true, false, false); . . DOWNLOAD DISPONÍVEL COM DADOS COMPLETOS, VEJA LINK NO FINAL DO ARTIGO . INSERT INTO funcionario (id_funcionario, funcionario, id_ departamento, cargo, salario, recebe_cesta_basica, recebe_plano_ saude, emprestimo_consignado, chefe_departamento) VALUES (46, ‘Estevao Almeira’, 8, ‘Contador’, 4500, true, true, false, true);

Listagem 8. Média de salário por departamento usando Windowing Functions 1. 2. 3. 4. 5. 6.

SELECT id_funcionario, funcionario, cargo, departamento, salario, avg(salario) OVER (PARTITION BY departamento)::numeric(9,2) AS media_departamento FROM funcionario INNER JOIN departamento ON d epartamento.id_departamento = funcionario.id_departamento O RDER BY media_departamento;


17

25/02/2010 14:30:28

a necessidade de se ter o avião o mais cheio possível. Este procedimento é chamado de “Escala”. São consideradas todas as possibilidades para se chegar ao destino. Listagem 9. Explicando o código da Listagem 8 DA TABELA de funcionários, RETORNE APENAS OS DADOS DAS COLUNAS O ID do funcionario, O nome do funcionário, O cargo do funcionario, O departamento ao qual ele está vinculado, O seu salário, e A MÉDIA de salario SOBRE A ÓTICA de todos os registros da tabela departamento (ou seja, a média salarial por departamento)

Na Figura 4 estão apresentadas as rotas definidas pela empresa, onde podemos perceber que clientes do Rio de Janeiro podem voar até Bauru diretamente ou com Escala em São Paulo, além de voarem direto para Goiânia ou fazer escala em São Paulo para isso. O mesmo acontece com São Paulo.

Listagem 10. Consulta aos vôos criados. WITH RECURSIVE consulta_recursiva (vôo_num, cidade_partida, cidade_chegada, conexao, hora_saida, hora_chegada) AS ( SELECT f.vôo_num, f.cidade_partida, f.cidade_chegada, 0, hora_saida, hora_chegada::text FROM vôos f W HERE f.cidade_partida = ‘SAO PAULO’ OR f.cidade_partida = ‘RIO DE JANEIRO’ UNION ALL SELECT DISTINCT tabela.vôo_num,recursivo.cidade_partida, (recursivo.cidade_chegada || ‘ <= escala em; com destino para => ‘ || tabela.cidade_chegada):: varchar(100) AS cidade_chegada, recursivo.conexao + 1, recursivo.hora_saida, (recursivo.hora_saida::time + tabela.hora_chegada:: interval)::text FROM consulta_recursiva recursivo, vôos tabela W HERE recursivo.cidade_chegada = tabela.cidade_partida ) SELECT DISTINCT vôo_num, cidade_partida, cidade_chegada,conexao, hora_ saida,hora_chegada FROM consulta_recursiva ORDER BY hora_chegada;

A diferença de alimentar uma CTE com um VALUE() e um SELECT é que este “alimento” dado ao CTE via SELECT pode conter vários registros, e automaticamente os parâmetro serão testados com todos os registros deste SELECT. Por isso, independente do segundo SELECT, já será exibido algum resultado em função do primeiro SELECT. O segundo SELECT só irá gerar dados quando ele atingir sua condição WHERE: recursivo.cidade_chegada = tabela.cidade_partida, caso contrário, o PostgreSQL irá exibir mais um registro “puro” do primeiro SELECT. Podemos perceber isso mais claramente observando o resultado da Figura 5.

Figura 5. Resultado da Consulta aos Vôos

Figura 4. Rota aérea definida pela Brazilian Airlines

Os analistas do departamento de computação foram felizes em adotar o novo PostgreSQL, porque contaram com consultas recursivas (Nota DevMan 2), uma nova possibilidade nesta versão novo do PostgreSQL. Com isso, fica mais simples exibir os possíveis vôos disponíveis pela companhia aérea, conforme descrito na Listagem 10. Como explicado na Nota DevMan 2, para haver recursividade é necessário um valor inicial. Na Listagem 10, a consulta recursiva é iniciada com o valor carregado de um SELECT cuja condição admite apenas vôos com partida de São Paulo ou Rio de Janeiro, pois os engenheiros de vôo da Brazilian Airlines centralizaram a logística de vôo nestas duas cidades para gerar o plano de vôo.

18 SQL73.indb 18

Vamos dar uma observada de perto nos registros de número 6 e 7, vôo_num BZ-2010002 e BZ-2010003 que possuem ambos a coluna conexão = 1. Isso indica que estes dois vôos saíram do Rio de Janeiro para São Paulo e de lá seguiram o destino final, Bauru e Goiânia, respectivamente. Vejam como foi fácil gerar este mapa, mas como funciona isso? A resposta é recursividade! Nós geramos as possibilidade de vôos com saída de São Paulo e Rio de Janeiro e o PostgreSQL se encarregou de gerar o resultado. Com isso, atendemos a mais um pedido requerido para o sistema. Vamos agora atender ao ultimo pedido solicitado.

Requisição 3) Na venda do Bilhete, vender somente a quantidade liberada para um determinado vôo Os Engenheiros de vôo da Brazilian Airlines definiram que para um determinado vôo deve-se vender apenas uma quantidade x de bilhetes. Esta quantidade é limitada pela quantidade de poltronas do avião ou pelo peso que ele carrega. Por isso, ao se criar um vôo, os Engenheiros atribuem a quantidade máxima permitido de venda na coluna qtd_disponivel.


postgresql

A cada bilhete vendido, o sistema invoca uma função que o pessoal da computação criou chamada “func_substrai_qtd(voo_ num varchar(15), qtd int4)” (descrita na Listagem 11) que irá atualizar a tabela vôos, cuja estrutura está sendo relembrada na Figura 6.

Para a execução, basta apenas informar o número do vôo e a quantidade de bilhetes que foram vendidos. No PostgreSQL 8.4, existe a possibilidade de se definir um valor padrão para os parâmetros de uma function. Observe que isso foi feito para a nossa função logo na primeira linha da Listagem 10 através do comando “default”, onde foi indicado que o valor 1 deveria ser o valor padrão. Listagem 11. Função que irá reduzir a disponibilidade de vendas.

Figura 6. Detalhes da tabela voos

A Listagem 11 é apenas uma simples função que realiza um update diminuindo a disponibilidade de venda de bilhetes para um determinado vôo informado pelo primeiro parâmetro, voo_num (varchar(15)). O diferencial nesta função é a palavra “default” no segundo parâmetro. Podemos usar este comando para automatizar alguns procedimentos na programação PL/ PGSQL, pois quando este parâmetro for NULO, então o PostgreSQL irá adotar o valor padrão definido, por isso, podemos fazer o que a Listagem 12 nos mostra. A função da Listagem 11 é uma function escrita em PL/PGSQL. Podemos executá-la da forma descrita na Listagem 12.

CREATE OR REPLACE FUNCTION func_substrai_qtd(varchar(15), integer default 1) RETURNS BOOLEAN AS $$ DECLARE existente int4; qtd_alterada int4; saida boolean; BEGIN SELECT qtd_disponivel INTO existente FROM vôos WHERE vôo_num = $1; IF (existente >= $2) THEN q td_alterada := existente - $2; raise notice ‘DISPONIVEL: %’, qtd_alterada; U PDATE vôos SET qtd_disponivel = qtd_alterada::int4 WHERE vôo_num = $1; ELSE R AISE NOTICE ‘NAO SUBTRATIU, EXISTENTE e remover: %, %’, existente, $2; END IF; RETURN saida; END; $$ LANGUAGE plpgsql;

Listagem 12. Executando a função func_subtrai_qtd. SELECT func_substrai_qtd(‘BZ-2010001’, 1);

Nota do DevMan 2 Consultas Recursivas – Common Table Expression As consultas recursivas eliminam boa parte da programação PL/PGSQL e lógica centralizada na camada Server ou Client. A solução do item 2 irá depender da utilização deste nova implementação, e para esclarecer o funcionamento primeiro vamos entender como montar uma consulta recursiva, no mesmo molde dos primeiros passos de recursividade quando estamos aprendendo a programar, fazendo um exemplo de somatória recursiva: WITH RECURSIVE soma_recursiva(parametro) AS ( VALUES (1) UNION ALL SELECT parametro +1 FROM coisa WHERE parametro < 2 )

SELECT sum(parametro) FROM soma_recursiva: Ao analisarmos apenas a última linha, na verdade não estamos realizando um SELECT da tabela soma_recursiva e sim de uma expressão, como se fosse uma função criada em PL/PGSQL. Portanto, a lógica da recursividade fica dentro desta expressão chamada de COMMON TABLE EXPRESSION ou simplesmente CTE. O que unifica os parâmetros enviados para uma CTE com as tabelas que iremos trabalhar dentro de uma CTE é justamente a sintaxe UNION ALL que irá entregar o parâmetro enviado pela última linha (SELECT sum(paramentro) FROM soma_recursiva) para o SELECT após ele; lembrando que este select finaliza com a invocação da própria CTE para se tornar recursiva.

SELECT sum(parametro) FROM soma_recursiva;

O resultado da consulta acima seria 3! Mas como funciona isso? Na consulta exemplo acima, através do WITH RECURSIVE criamos uma “COMMON TABLE EXPRESSION”, ou seja, é a regra de negócios para que a consulta recursiva funcione. O comando WITH é finalizado com a sentença:

Antes do UNION ALL temos o VALUE (): Esta função realiza o mesmo serviço quando usado dentro do INSERT, ou seja, define valores para os campos. Sendo assim, através do VALUE() iremos definir os valores iniciais dos parâmetros criados na CTE, porém podemos definir estes valores iniciais através de um SELECT, ou seja, só se usa o VALUE() quando sabemos de fato os valores de início para que nossa consulta recursiva funcione. Como no exemplo acima, o valor inicial da CTE “soma_recursiva” tem “VALUE” 1 para a variável parâmetro, e usamos a função SUM() para incrementá-lo.

SELECT parâmetro1, parametro2, ..., da common table expression FROM nome da common table expression. É justamente neste momento que a recursividade começa. Vamos analisar a consulta acima de traz para frente:


19

25/02/2010 14:30:29

SELECT func_substrai_qtd(‘BZ-2010001’);

Comparativo entre a Versão 8.3 e 8.4 do PostgreSQL Uma questão importante para quem está na versão 8.3.8 é saber o quanto está perdendo em permanecer nesta versão. O que demonstramos em nosso projeto fictício são recursos interessantes que auxiliam o desenvolvedor de forma produtiva, mas e no desempenho? O Dr. Jignesh K. Shah, Engenheiro da SUN, realizou uma palestra muito interessante na PGCON 2009 que foi realizada na Universidade de Ottawa em Maio, onde realizou um comparativo de desempenho entre as duas versões. Os dados completos estão em seu blog, citado ao final na Seção links - “Performance Comparison of PostgreSQL 8.4 VS PostgreSQL 8.3”. Fazendo um resumo de seus comparativos, pode-se concluir que: O PostgreSQL 8.4 está cerca de 5% mais rápido. Isso significa que em muitos pontos a nova versão ganhou em velocidade, mas em outras perdeu. Para não assustar os leitores, este resultado é previsível, pois a nova versão ganhou novas funcionalidades (mais de 293) que geram custo para trazer um resultado, por exemplo, a nova ACL por coluna. Agora esta versão tem um novo nível de check para realizar. É assim mesmo, novas funcionalidades podem gerar um custo maior, entretanto, os testes do Dr. Shah “provam” um acréscimo geral entre o último release da série 8.3 e o novo 8.4

Existem estudos que demonstram superioridade de desempenho entre o PostgreSQL e bancos comerciais como o DB2, SQL Server e Oracle. Em uma migração que realizamos de

20 SQL73.indb 20

Download do projeto deste artigo www.datapower.com.br/sql/2010/projeto_01.zip NOTA “Novidades do PostgreSQL 8.4” www.postgresql.org/about/press/presskit84.html.br ENTENDA “Recursividade no DB2” http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/rzajq/rzajqrecursive.htm ENTENDA “Recursividade no SQL Server” http://msdn.microsoft.com/en-us/library/ms186243.aspx Performance Comparison of PostgreSQL 8.4 VS PostgreSQL 8.3 http://blogs.sun.com/jkshah/resource/pgcon2009_8384p.pdf

Dê seu feedback sobre esta edição! A SQL Magazine tem que ser feita ao seu gosto. Para isso, precisamos saber o que você, leitor, acha da revista! Dê seu voto sobre este artigo, através do link:


Conclusão

Links

Dê s

Listagem 13. Executando a função func_subtrai_qtd em informar parâmetro.

dados do Oracle para o PostgreSQL, uma arquivo EDI de importação demorava 30 minutos para ser importado no Oracle, já no PostgreSQL apenas 20 segundos. Entretanto, o Oracle possuía tantos recursos que para um projeto mais complexo o PostgreSQL não atenderia. Desta forma, nesta nova versão o PostgreSQL, além de 5% mais rápido que a versão 8.3.8, ganhou muitos recursos que permite ir mais longe. Existe a promessa para que na versão 8.5 o PostgreSQL venha com ferramentas nativas para Clusterização como por exemplo REPLICAÇÃO SINCRONA. Isso possibilita uma concorrência muito grande com o Oracle RAC, lembrando ainda da questão financeira já que o PostgreSQL é FREE. Para apoiar na análise mais prática desta nova versão do PostgreSQL, realizamos a modelagem e projeto de um banco de dados de uma companhia aérea fictícia. Neste contexto, consideramos algumas situações comuns de serem encontradas no dia-a-dia de um DBA onde os novos recursos podem ser empregados, e onde apresentam resultados muito interessantes, tornando as tarefas bastante simples.

edição ta

O que vemos na Listagem 13 é a mesma função anterior, enviando por parâmetro apenas o varchar(15) que seria o vôo de número “BZ-2010001”. Isso fará com que o valor assumido pela função para o parâmetro quantidade de bilhetes vendidos seja igual a 1. Isso acontece, como dito anteriormente, porque definimos que o integer (int4) receberia por padrão o valor 1 se acaso não fosse especificado um valor.

www.devmedia.com.br/sqlmagazine/feedback


postgresql


21

25/02/2010 14:30:39




A Linguagem PL/Java do PostgreSQL Parte 2 – Instalação e Preparação do Ambiente De que se trata o artigo? Descrição e demonstração da preparação do ambiente do banco de dados para receber a máquina virtual Java, utilizando-a para a instalação da linguagem procedural PL/Java e desenvolvimento de funções que usam classes Java.

Para que serve? Clailson de Almeida [email protected]

Graduado em Tecnologia em Processamento de Dados e pós-graduado em Administração de Banco de Dados pela Universidade Tiradentes (UNIT), no estado de Sergipe. Já atuou como desenvolvedor WEB, com forte utilização de Java. Já trabalhou 2 anos como Analista de Sistemas e possui experiência no desenvolvimento com bancos de dados Oracle, SQL Server e PostgreSQL, sendo que, deste último, tem 6 anos de utilização, dos quais 4 anos e meio como DBA. Atualmente trabalha em uma softhouse privada, na cidade de Aracaju, Sergipe, como gerente da área de banco de dados. Possui certificação PostgreSQL, pela Pearson Vue, com o título de “PostgreSQL CE (PostgreSQL Certified Engineer Open Source Software) 8 Silver”. Realiza consultoria especializada em banco de dados e ministra cursos e treinamentos em PostgreSQL. Está estudando atualmente para a certificação OCA Oracle.

22 SQL73.indb 22

N

esta série de artigos estamos demonstrando a utilização da linguagem PL/Java no PostgreSQL, ampliando ainda mais as possibilidades de desenvolvimento de funções e triggers, através da linguagem Java. Na primeira parte deste artigo, descrevemos algumas características da linguagem procedural PL/Java, e mostramos como os módulos, classes e toda estrutura do Java trabalha junto com o backend do SGBD, para que as funções e triggers utilizem as classes Java desenvolvidas. Nesta segunda parte, descreveremos como preparar um servidor de banco de dados PostgreSQL para utilizar o PL/ Java no desenvolvimento de funções.

Descrever a configuração do servidor de banco de dados para utilizar a linguagem Java. Serve também para mostrar como o PostgreSQL pode ser configurado para reconhecer os objetos compartilhados do Java e do módulo PL/Java, bem como a instalação da linguagem PL/Java.

Em que situação o tema é útil? Possibilidade de migração das regras de negócio, escritas em Java, da camada de aplicação para o banco de dados, aumentando os recursos do mesmo com a utilização e manipulação direta dos dados pelo Java.

Preparação esta que vai desde a instalação da JVM (a máquina virtual Java), passando pela configuração do PostgreSQL, até a instalação propriamente dita da linguagem.

SQL Magazine - A Linguagem PL/Java do PostgreSQL 25/02/2010 14:30:40

postgresql

A versão do PL/Java aqui discutida é a 1.4.0, com o PostgreSQL na versão 8.3.7, em ambiente Linux (CentOS 5.2).

Pré-requisitos para Execução do PL/Java

Depois da alteração do arquivo, devemos recarregar as configurações com o utilitário ldconfig, bastando digitá-lo na linha de comando.

Antes de iniciarmos o download e instalação do PL/Java, precisamos ter instalado no servidor de banco de dados: • PostgreSQL com versão a partir da 8.0.3 (detalhes e comentários para cada versão acima ou abaixo desta, podem ser vistos na primeira parte deste artigo); • O driver JDBC do PostgreSQL (necessário apenas se for utilizado o programa Deployer – comentado mais a frente – para instalação do PL/Java); • Runtime do Java (JRE) com versão a partir da 1.4 ou GCJ a partir da 4.0.x (apenas para Linux).

[root@localhost ~]# ldconfig

Configuração do Ambiente do Servidor

set PATH=%PATH%;%JAVA_HOME%\bin;%JAVA_HOME%\bin\client

Como um dos pré-requisitos para o funcionamento do PL/ Java, precisamos instalar o runtime do Java no servidor de banco de dados. Para isso, devemos acessar o site da Sun (ver seção Links) e fazer o download da versão mais recente. Neste artigo foi usado o arquivo jre-6u15-linux-i586-rpm. bin (Java 6, update 15). Feito o download, devemos tornar o arquivo binário executável e instalar o mesmo, com os comandos abaixo:

Assim como no Linux, para esta configuração ficar permanente é necessário editar a variável PATH na seção Variáveis de Ambiente, que está na guia Avançado das Propriedades do Sistema (botão direito do mouse em Meu Computador>Propriedades do Sistema>Avançado>Variáveis de Ambiente) (Figura 1).

Listagem 1. Conteúdo do arquivo “ld.so.conf” ... $JAVA_HOME/lib/i386/server $JAVA_HOME/lib/i386/client $JAVA_HOME/lib/i386/ native_threads ...

No sistema operacional Windows, basta configurar a variável de ambiente PATH, como mostrado abaixo, na linha de comando do prompt do DOS.

chmod a+x jre-6u15-linux-i586-rpm.bin ./jre-6u15-linux-i586-rpm.bin

Com o término da instalação e a execução do comando java -version, será obtido o resultado mostrado abaixo. Caso o resultado não seja este, devemos verificar as ocorrências de erros e proceder novamente com a instalação do Java. [root@localhost ~]# java -version java version “1.6.0_15” Java(TM) SE Runtime Environment (build 1.6.0_15-b03) Java HotSpot(TM) Client VM (build 14.1-b02, mixed mode, sharing)

Com o Java instalado, é necessário dizer ao serviço do PostgreSQL (postmaster), bem como às outras aplicações que utilizam o Java, onde encontrar os objetos compartilhados utilizados pelo Java Runtime Environment (JRE). Falando especificamente do PostgreSQL, esta configuração do Java só se aplica ao postmaster, ou seja, ao processo servidor (backend). Para os clientes, como o psql, isto não é necessário. Em ambientes Linux/Unix, a variável de ambiente LD_LIBRARY_PATH deve ser configurada como apresentado a seguir. Esta configuração aponta para os diretórios onde estão os objetos compartilhados do Java. export LD_LIBRARY_PATH=$JAVA_HOME/lib/i386/server:$JAVA_HOME/lib/ i386/client:$JAVA_HOME/lib/i386/native_threads

Alternativamente, podemos editar o arquivo /etc/ld.so.conf para incluir os caminhos dos diretórios dos objetos compartilhados do Java (Listagem 1). Esta edição pode ser feita em qualquer editor de texto do Linux, e o objetivo desta modificação é deixar permanente a configuração do path das bibliotecas Java, mesmo depois do boot do servidor.

Figura 1. Configuração permanente do PATH no Windows

No ambiente Linux/Unix, além da configuração do caminho de procura para os objetos compartilhados do Java, pode ser necessário resolver um conflito entre a versão da biblioteca libzip.so, incluída no JRE, e a versão da biblioteca libz.so, do sistema operacional, usada pelo PostgreSQL. Convém notar que a libzip.so já contem a libz.so. Estas bibliotecas são escritas em C e servem para leitura, criação e modificação de arquivos compactados. É importante dizer também que este problema só acontece em algumas plataformas, por exemplo, servidores com o sistema operacional Debian. O sintoma do problema é um InternalError na classe java. util.zip.Inflater, quando é feito a tentativa de carregar a classe de compressão utilizando o método init(). Este método pode utilizar um parâmetro chamado nowrap, que diz à classe java.util.zip.Inflater se é para utilizar o suporte de compressão compatível com o método GZIP ou não. Se true, a classe utiliza métodos de otimização nativas da ZLIB. É neste ponto que o erro pode acontecer, pois estes métodos podem


23

25/02/2010 14:30:40

ter implementações diferentes, caso a versão da libzip.so seja diferente da libz.so. Para verificar as versões de libzip.so e de libz.so, podemos utilizar o utilitário strings na linha de comando (Listagem 2). Listagem 2. Versões das bibliotecas libzip.so e libz.so [root@localhost ~]# strings $JAVA_HOME/lib/i386/libzip.so | fgrep Copyright deflate 1.1.3 Copyright 1995-1998 Jean-loup Gailly inflate 1.1.3 Copyright 1995-1998 Mark Adler [root@localhost ~]# strings /usr/lib/libz.so.1.2.3 | fgrep Copyright deflate 1.2.3 Copyright 1995-2005 Jean-loup Gailly inflate 1.2.3 Copyright 1995-2005 Mark Adler

Neste caso, há diferença de versões, mas em nosso ambiente de teste, executando o CentOS 5.2, não houve conflito. Mas se ocorresse, o problema poderia ser resolvido de quatro maneiras diferentes, dependendo apenas das necessidades do administrador e habilidades na recompilação do PostgreSQL: • Se possível, utilizar uma nova versão do JRE que tenha a mesma versão da libz.so. Esta é a solução mais adequada. Infelizmente, para saber se a versão é compatível é necessário instalar o JRE e executar o utilitário strings, ou verificar alguma nota de liberação da versão do Java (release notes); • Modificar a variável de ambiente do Linux, chamada de LD_PRELOAD (ler Nota DevMan 1), para apontar para a biblioteca libzip.so, do JRE. Isto forçará o uso da versão da biblioteca que está no JRE. Essa solução fará com que o postmaster use a libzip.so no lugar da libz.so; • Reconfigurar o código fonte do PostgreSQL com a opção --without-zlib, recompilar e reinstalar o mesmo. Isto irá desabilitar todo o suporte de compressão do serviço postmaster. Para ter de volta o suporte, é preciso recompilar novamente o PostgreSQL, voltando também o conflito entre as versões das bibliotecas de compressão; • Obter uma versão da libz.so que corresponda à versão da libzip.so, utilizada pelo JRE, e fazer uma reconfiguração na execução do serviço do PostgreSQL para apontar para esta nova versão da libz.so.

Nota do DevMan 1 LD_PRELOAD É uma variável de ambiente que aponta para bibliotecas compartilhadas criadas pelo usuário. Estas bibliotecas podem conter nomes de funções com os mesmos nomes das funções do sistema operacional que se deseja sobrescrever, ou até mesmo, novas funcionalidades. É utilizada quando desejamos colocar uma biblioteca criada entre a chamada de um programa e a sua própria biblioteca, sem ter que sobrescrever a biblioteca original. Por exemplo, pode-se usar a LD_PRELOAD para sobrescrever uma função da biblioteca dos módulos de um leitor de cartão smartcard para mostrar uma mensagem de erro traduzida, validações de dados ou até mesmo informações úteis para o usuário.

24 SQL73.indb 24

Download do PL/Java Com os pré-requisitos verificados e o Java instalado no servidor de banco de dados, podemos prosseguir com o download do PL/ Java. Os arquivos necessários para a instalação da linguagem procedural podem ser encontrados no pgFoundry, o site oficial do grupo de desenvolvimento do projeto PL/Java, e de outros projetos externos (add-ons) para o PostgreSQL (ver seção Links). Acessando o site devemos clicar na seção Arquivos, onde iremos encontrar os pacotes binários de instalação da versão 1.4.0 do PL/Java (o arquivo pljava-i686-pc-linux-gnu-pg8.31.4.0.tar.gz). Estão disponíveis instaladores para as versões mais recentes do PostgreSQL (8.1, 8.2 e 8.3), para Linux 32 e 64 bits, e para Windows 32 bits. Todos os pacotes binários para download foram montados para utilizarem a configuração padrão de uma JVM. Também é disponibilizado o código fonte do PL/Java para compilação em outras plataformas. O PL/Java pode ser compilado com o GNU GCJ (conforme explicado na primeira parte deste artigo). Entretanto, não existem pacotes binários pré-compilados para GCJ, mas o utilitário make, no ambiente Linux, contém o necessário para a construção e compilação destes pacotes binários.

Instalação do Pacote Binário do PL/Java Para a instalação do pacote binário basta descompactá-lo em qualquer diretório no sistema de arquivo, uma vez que o servidor do PostgreSQL será configurado para o reconhecimento deste módulo. Vamos então criar um diretório chamado pljava, dentro do diretório /var/lib/pgsql e descompactar o arquivo pljava-i686pc-linux-gnu-pg8.3-1.4.0.tar.gz (conforme os comandos da Listagem 3). Listagem 3. Criação e descompactação do pacote binário do PL/Java. [root@localhost ~]# mkdir /var/lib/pgsql/pljava [root@localhost ~]# cp pljava-i686-pc-linux-gnu-pg8.3-1.4.0. tar. gz /var/lib/pgsql/pljava [root@localhost ~]# cd /var/lib/pgsql/pljava [root@localhost ~]# tar xvf pljava-i686-pc-linux-gnu-pg8.31.4.0.tar.gz

Com o pacote descompactado, vamos mudar as permissões de acesso do diretório pljava e dos seus arquivos. Assim, apenas o usuário postgres poderá acessá-los. Isso é feito porque ele é o super usuário master, dono do serviço postmaster e de toda estrutura de diretórios do agrupamento de banco de dados. Portanto, aumentaremos a segurança deixando o diretório pljava com as mesmas permissões de acesso das outras pastas e arquivos que estão dentro do caminho /var/lib/pgsql. Na Listagem 4, com o comando chown, trocamos recursivamente o dono dos arquivos e diretório no caminho /var/lib/ pgsql/pljava. Depois, com o comando chmod, trocamos as permissões de leitura, escrita e execução, ou seja, nenhum outro usuário ou grupo pode ter acesso a estes arquivos, apenas o usuário postgres.


postgresql

Listagem 4. Mudança das permissões de acesso dos arquivos do PL/Java. chown postgres:postgres -R /var/lib/pgsql/pljava chmod 700 -R /var/lib/pgsql/pljava

Configuração do PostgreSQL Para que o módulo PL/Java seja reconhecido e funcione corretamente, é necessário configurar o servidor PostgreSQL, modificando o arquivo postgresql.conf do diretório /var/lib/pgsql/data. Existem duas formas de fazer com que o PostgreSQL encontre os objetos e classes do PL/Java. A primeira é descompactar o pacote binário dentro de um diretório de procura do serviço postmaster (nome do serviço do PostgreSQL), como o diretório data. A outra forma é dizer ao postmaster onde encontrar tais objetos, através da variável dynamic_library_path, no arquivo postgresql.conf. A configuração default desta variável é mostrada abaixo: #dynamic_library_path = ‘$libdir’

Devemos retirar o comentário da linha (apagando a “#”) e adicionar o caminho onde se encontram os arquivos que foram extraídos do pacote binário do PL/Java, de acordo com o código abaixo.

descrições são exibidos na Tabela 1. A Listagem 5 mostra um exemplo de configuração dessas variáveis. Normalmente o código Java é carregado nas bases de dados através das funções SQL do PL/Java (install_jar/replace_jar/remove_jar) – que serão detalhadas na parte três desta série. Mas estas funções, bem como a maior parte do código que compõe o módulo PL/Java, são escritas em Java. Portanto, é necessário dizer ao PostgreSQL onde encontrar os objetos que fazem parte da linguagem procedural. Esta configuração é realizada através do parâmetro classpath do PL/Java, como descrito abaixo. pljava.classpath = /pljava.jar

Listagem 5. Configuração dos parâmetros do PL/Java. pljava.classpath = ‘/var/lib/pgsql/pljava/pljava.jar’ pljava.statement_cache_size = 10 pljava.release_lingering_savepoints = true pljava.vmoptions = ‘-Xmx64M’ pljava.debug = false

Com as modificações realizadas no arquivo postgresql.conf, basta reiniciar o servidor do PostgreSQL para que o novo módulo seja reconhecido e a linguagem procedural PL/Java possa ser instalada nas bases de dados.

dynamic_library_path = ‘$libdir:’

No Windows, deve-se usar o ponto e vírgula (;) como separador de bibliotecas, e barras duplas (//) como separador de diretórios. Vamos modificar esta variável deixando-a no formato: dynamic_library_path = ‘$libdir:/var/lib/pgsql/pljava’

Podemos alterar também o valor da variável log_min_messages para podermos visualizar mais informações sobre a execução das classes no log do PostgreSQL, como saída de informações de buffers ou mensagens de erro que são repassadas para eventos de try-catch, assim como outras informações relevantes para debug das classes. log_min_messages = info

O PL/Java faz uso da variável custom_variable_classes, na seção CUSTOMIZED OPTIONS, para poder configurar o módulo do PL/Java. Dessa forma, precisamos alterar esta variável e incluir uma variável de classe chamada pljava, conforme o código: custom_variable_classes = ‘pljava’

Com isso, podemos configurar os parâmetros utilizados pelo PL/Java. Estes parâmetros e suas


25

25/02/2010 14:30:41

Parâmetro

Descrição

classpath

Define o classpath que a JVM usa quando carrega a biblioteca inicial do PL/Java. Utilizado apenas quando não se usa o GCJ.

statement_cache_size

Define o tamanho do cache para Prepared Statements (ler Nota DevMan 2).

release_lingering_savepoints

Se true, savepoints em espera serão liberados na saída da função, ou seja, se foi criado um savepoint em espera, dentro da função, o mesmo poderá ficar aguardando o retorno do commit dos bancos de dados distribuídos, mesmo depois da função finalizar sua execução. Se false, os savepoints serão desfeitos (rollback). Isto é útil para transações com servidores distribuídos. Define as opções de inicialização para a JVM, por exemplo: pljava.vmoptions = ‘-Xms64M –Xmx128M -Xbatch’

vmoptions

debug

Neste caso, estamos configurando a JVM para utilizar inicialmente 64 MB de memória, podendo atingir até 128 MB (opções Xms e Xmx, respectivamente). Estamos dizendo também para a JVM desabilitar qualquer compilação em background (opção Xbatch). Este parâmetro segue o mesmo formato das opções de inicialização de uma JVM comum. Se true, fará com que o processo postgres fique em standby na primeira chamada à linguagem Java, com o intuito de verificar, linha por linha, o código interno do PL/Java, na linguagem C. Portanto, esta variável só é útil para debug do PL/Java se for necessário verificar um bug no módulo PL/Java ou no desenvolvimento de novas funcionalidades. Segue o mesmo princípio de debug de uma classe Java em uma IDE, como o Netbeans.

Tabela 1. Parâmetros do PL/Java

Nota do DevMan 2 Prepared Statements É um objeto da API Java que permite o desenvolvimento de templates de queries SQL que podem ser reutilizadas para efetuar comandos iguais com diferentes valores de parâmetros. Essencialmente o que é feito é criar a query, que pode ser de qualquer tipo (INSERT, UPDATE, etc.), deixando os valores das variáveis indefinidos. Então, podemos especificar valores para os elementos indefinidos antes de executar a query, além de podermos executar o mesmo comando várias vezes, bastando trocar os valores das variáveis de entrada. Um exemplo de query que é montada em um prepared statement é “SELECT * FROM CLIENTES WHERE CODIGO > ? AND CATEGORIA = ?”.

Instalação do PL/Java nas Bases de Dados A instalação da linguagem procedural PL/Java pode ser feita de duas maneiras. A mais simples é executando o arquivo install.sql dentro da base de dados. Este arquivo está dentro do diretório onde foi descompactado o pacote binário. No nosso caso, o arquivo se encontra no diretório /var/lib/pgsql/pljava. Existe também o arquivo uninstall.sql para remover a linguagem PL/Java do database. A estrutura criada será explicada na próxima seção. Tanto para a execução do arquivo install.sql como para o uninstall.sql, é necessário que o usuário que esteja executando tenha privilégios de super usuário. Para demonstrar esta primeira forma de instalação da linguagem procedural PL/Java, iremos criar uma base de dados chamada sql_magazine_pljava e executar o arquivo install. sql (Listagem 6).

Com a instalação concluída, basta acessar a base de dados e verificar as tabelas e funções criadas no esquema sqlj, que foi criado para guardar toda a estrutura do PL/Java, separando-a da estrutura e esquemas do usuário. Depois disso, iremos executar o arquivo uninstall.sql para desinstalar o PL/Java e podermos instalá-lo novamente, utilizando a segunda forma de instalação. psql -U postgres sql_magazine_pljava -1 -f /var/lib/pgsql/pljava/ uninstall.sql

A segunda forma de instalar a linguagem PL/Java é utilizando um programa de deploy que nos permite instalar, reinstalar e remover a linguagem da base de dados. Este programa já é disponibilizado pelo PL/Java e precisa que o usuário que o está executando tenha privilégios de super usuário. Também é necessária a utilização do driver JDBC do PostgreSQL no seu classpath. Para verificar as opções do programa deploy, basta executar o comando abaixo. java -cp /var/lib/pgsql/pljava/deploy.jar:/var/lib/pgsql/pljava/ postgresql-8.3-605.jdbc4.jar org.postgresql.pljava.deploy.Deployer

Observe que na opção -cp (classpath), do comando java, foi informado o caminho do pacote JAR do deploy, que está em / var/lib/pgsql/pljava. Foi informado também onde o programa encontrará o driver JDBC do PostgreSQL. Neste caso, estamos utilizando o driver para a versão 6 do Java e o mesmo foi colocado no diretório onde o pacote do PL/Java foi descompactado. Com a execução do comando acima, será mostrada a lista de opções disponíveis do programa deploy (Listagem 7). Listagem 7. Opções do programa de deploy do PL/Java.

Listagem 6. Criação da base sql_magazine_pljava e instalção da linguagem PL/Java. createdb -U postgres sql_magazine_pljava -E LATIN1 -T template0 psql -U postgres sql_magazine_pljava -1 -f /var/lib/pgsql/ pljava/install.sql

26 SQL73.indb 26

usage: java org.postgresql.pljava.deploy.Deployer {-install | -uninstall | -reinstall} [ -host ] # default is localhost [ -port ] # default is blank [ -database ] # default is name of current user [ -user ] # default is name of current user [ -password ] # default is no password


postgresql

A sintaxe do comando pode ser vista abaixo, e na Tabela 2 podemos ver cada opção e sua descrição. java -cp org.postgresql.pljava.deploy.Deployer [ opções ]

Utilizando a mesma base de dados sql_magazine_pljava, onde já instalamos o PL/Java pelo método mais simples (utilizando o arquivo install.sql), vamos instalar novamente a linguagem utilizando o programa de deploy, como mostrado a seguir:

java -cp /var/lib/pgsql/pljava/deploy.jar:/var/lib/pgsql/pljava/ postgresql-8.3-605.jdbc4.jar org.postgresql.pljava.deploy.Deployer -install -user postgres -database sql_magazine_pljava

Estrutura do Esquema SQLJ Como foi comentado na seção anterior, depois da instalação do PL/Java, seja pela execução do arquivo install.sql ou pelo programa de deploy do PL/Java, são criadas várias tabelas e funções dentro de um novo esquema na base de dados, chamado de sqlj. A Tabela 3 mostra a descrição de cada objeto e funciona como um dicionário de dados da

Opção

Descrição Instala a linguagem procedural PL/Java na base de dados juntamente com as funções e tabelas que o PL/Java utiliza. A instalação falhará se a linguagem já estiver criada. Reinstala a linguagem procedural PL/Java na base de dados juntamente com as funções e tabelas que o PL/Java utiliza. Este comando executará efetivamente um drop em todos os pacotes JAR que foram carregados na base de dados. Por isso será necessário carregar novamente todos os JAR instalados. Remove a linguagem procedural PL/Java da base de dados juntamente com as funções e tabelas que o PL/Java utiliza e todos os JAR carregados.

-install

-reinstall

-uninstall -user

Nome do usuário que irá se conectar a base de dados. O default é o usuário do sistema operacional corrente.

-password

Senha do usuário que irá se conectar a base de dados. O default é sem senha.

-database

O nome do banco de dados destino onde a linguagem PL/Java será manipulada. O default é a base com o mesmo nome do usuário da conexão. Ou seja, se o comando for executado com o usuário carlos e sem especificação do banco, o PostgreSQL irá procurar uma base de dados com o nome “carlos”.

-host

Nome do host da conexão. O default é localhost.

-port

Número da porta da conexão. O default é vazio.

Tabela 2. Descrição das opções do programa de deploy do PL/Java Estrutura

Descrição

SCHEMA sqlj

Esquema onde estão instalados os objetos para utilização da linguagem PL/Java.

LANGUAGE java

Linguagem marcada como TRUSTED.

LANGUAGE javaU

Linguagem marcada como NOT TRUSTED.

SEQUENCE sqlj.jar_entry_entryid_seq

Objeto sequence (ler Nota DevMan 3) utilizado pela tabela sqlj.jar_entry.

SEQUENCE sqlj.jar_repository_jarid_seq

Objeto sequence utilizado pela tabela sqlj.jar_repository.

SEQUENCE sqlj.typemap_entry_mapid_seq

Objeto sequence utilizado pela tabela sqlj.typemap_entry.

TABLE sqlj.jar_repository

Tabela que armazena as informações dos pacotes JAR das classes desenvolvidas pelo usuário.

TABLE sqlj.jar_entry TABLE sqlj.classpath_entry TABLE sqlj.typemap_entry

Tabela que armazena as informações das classes que compõem cada pacote JAR instalado. Possui uma linha para cada classe, bem como uma coluna que guarda seu código compilado (.class). Tabela que armazena as informações de configuração do classpath dos pacotes JAR dentro do servidor de banco de dados. Tabela que armazena as informações de tipos complexos criados para utilização com as funções em PL/Java.

FUNCTION sqlj.java_call_handler

Interpretador/manipulador da linguagem java.

FUNCTION sqlj.javau_call_handler

Interpretador/manipulador da linguagem javaU.

FUNCTION sqlj.install_jar

Função que instala os pacotes JAR criados pelo usuário.

FUNCTION sqlj.replace_jar

Função que atualiza os pacotes JAR criados pelo usuário.

FUNCTION sqlj.remove_jar

Função que remove os pacotes JAR criados pelo usuário.

FUNCTION sqlj.set_classpath

Função que define um classpath para o pacote JAR instalado.

FUNCTION sqlj.get_classpath

Função para recuperar o classpath do pacote JAR instalado.

FUNCTION sqlj.add_type_mapping

Função para adicionar um tipo complexo na linguagem PL/Java.

FUNCTION sqlj.drop_type_mapping

Função para remover um tipo complexo na linguagem PL/Java.

Tabela 3. Estrutura criada pela instalação do PL/Java


27

25/02/2010 14:30:41

estrutura criada. Os detalhes de utilização serão descritos na terceira parte desta série.

Conclusão

Dê seu voto sobre este artigo, através do link: www.devmedia.com.br/sqlmagazine/feedback

sobre e s

A SQL Magazine tem que ser feita ao seu gosto. Para isso, precisamos saber o que você, leitor, acha da revista!

Objeto Sequence Este objeto é utilizado dentro do banco de dados para fazer uma espécie de “autoincremento”, utilizando números sequenciais positivos ou negativos para uma coluna específica, geralmente uma coluna de chave primária, ou quando uma aplicação necessita utilizar valores numéricos sequenciais em uma tabela. Esses valores são gerados automaticamente pelo banco de dados.

Links Site Oficial do PostgreSQL http://www.postgresql.org/

Feedback eu

edição ta


Dê s

Ao longo deste artigo podemos demonstrar como é relativamente simples a configuração do servidor de banco de dados PostgreSQL e a instalação da linguagem procedural PL/Java. Mais detalhes sobre as funções disponibilizadas pelo módulo PL/Java serão apresentados no próximo artigo, onde exemplificaremos algumas funções, bem como demonstraremos como são escritas as classes Java para utilização com o PL/Java. Até a próxima.

Nota do DevMan 3

Site do projeto PL/Java no PgFoundry http://pgfoundry.org/projects/pljava/ Download do PL/Java http://pgfoundry.org/frs/?group_id=1000038 Site Oficial do Java http://java.sun.com/ Doc API Java Standard Edition 6 http://java.sun.com/javase/6/docs/api/ Download do Java 6 http://www.java.com/pt_BR/download/manual.jsp

28 SQL73.indb 28



sql server Seção Banco de Dados/Persistência


Administrando o SQL Server com uma Ferramenta Web Conheça o SQL Server Web Data Administrator De que se trata o artigo?

I Nilton Pinheiro [email protected]

É formado em Análise de Sistemas e pósgraduado em Redes Corporativas. Possui 8 anos de experiência com SQL Server, dos quais a 7 trabalha como DBA em uma conceituada instituição financeira em São Paulo. Como um Beta Tester Microsoft trabalha com o SQL Server 2008 desde seu Beta 1. Possui as certificações MCDBA, MCTS: SQL Server 2005, MCITP: SQL Server 2005 e MCSE, é MVP (Microsoft Most Valuable Professionals) em SQL Server e fundador do portal www.mcdbabrasil.com.br.

niciaremos este artigo fazendo uma pergunta: alguma vez você teve a necessidade de administrar um servidor ou instância do SQL Server utilizando uma ferramenta que pudesse funcionar a partir de um browser? E se eu lhe disser que esta ferramenta existe? E o melhor, que ela é totalmente grátis? Pois bem, é verdade que pouca gente sabe, mas ainda na época do SQL Server 2000 a Microsoft disponibilizou um ferramenta chamada “SQL Server Web Data Administrator” que tinha, ou melhor, tem como objetivo permitir que você execute tarefas administrativas em um servidor SQL Server utilizando, por exemplo, o Internet Explorer ou Mozilla Firefox e podendo assim acessar a ferramenta de qualquer estação da sua rede. Na verdade, como é uma ferramenta Web, você pode acessá-la até remotamente, basta que para isso você tenha acesso à rede interna da sua empresa através da internet.

O artigo apresenta uma solução para gerenciamento de bancos de dados do SQL Server a partir de uma ferramenta que utiliza a Web como plataforma de execução, chamada SQL Server Web Data Administrator. O artigo descreve como instalá-la, configurá-la e utilizá-la para o gerenciamento de um banco de dados.

Para que serve? Gerenciar bancos de dados é sempre um desafio. Problemas não escolhem hora para ocorrer. Nesse contexto, ter conhecimento de uma ferramenta que possibilita o gerenciamento de banco de dados através da Web é bastante importante, pois permite a intervenção de um DBA, quando necessário, sem a obrigação de estar fisicamente presente no servidor. Com isso, ganhamos em praticidade e tempo para gerenciamento de bancos de dados no SQL Server.

Em que situação o tema é útil? Este tema se torna útil para profissionais e empresas que precisam de um gerenciamento instantâneo de seus bancos de dados no SQL Server, e que podem usufruir dos recursos de uma ferramenta que possibilita o gerenciamento de bancos de dados através da Web, possibilitando o gerenciamento remoto dos bancos de dados do SQL Server.


29

25/02/2010 14:30:48

No artigo de hoje será apresentada esta ferramenta, descrevendo passo-a-passo como realizar sua instalação e configuração, alguns de seus principais recursos e como usá-la para executar algumas tarefas básicas de administração.

Conhecendo o SQL Server Web Data Administrator O SQL Server Web Data Administrator é uma ferramenta desenvolvida em ASP.NET e teve sua primeira versão liberada em Abril/2004 no próprio site de download da Microsoft. A primeira versão funcionava apenas com SQL Server 7 ou SQL Server 2000, e era uma excelente alternativa para usuários que possuíam o SQL Server 2000 Desktop Engine (MSDE 2000), isso porque diferente das demais edições do SQL Server 2000, a edição Desktop Engine não possui nenhuma ferramenta administrativa. Com a chegada do SQL Server 2005, uma nova versão da ferramenta passou a ser desenvolvida e disponibiliza através do site CodePlax e agora também pode ser utilizada para executar tarefas básicas de administração em qualquer edição do SQL Server 2005. Como você verá no decorrer deste artigo, a instalação da ferramenta é bastante simples e uma vez configurada permite que você execute tarefas como: • Criar e editar bancos de dados no SQL Server 7/2000 e 2005 ou Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) e SQL Server 2005 Express. • Criar e executar consultas (queries) ad-hoc sobre os bancos de dados e salvá-las em seu disco rígido. • Executar comandos Transact-SQL. • Exportar e importar dados e estruturas de objetos. • Gerenciar logins, usuários e papéis (roles). • Criar, editar, renomear e excluir tabelas. • Criar, editar e excluir stored procedures. • Visualizar e editar as propriedades dos bancos de dados. Como se pode notar, a ferramenta possui muitas funcionalidades e permite que você administre um servidor SQL Server de forma bastante simples. Como é uma ferramenta web, pode inclusive ser uma ótima alternativa para quem quer acessar o SQL Server através da internet usando uma ferramenta Web ou ainda simplesmente para acessar um servidor SQL Server usando qualquer estação de uma rede e sem precisar instalar as ferramentas administrativas do SQL Server nas estações.

• Microsoft .NET Framework 2.0. • Internet Explorer 5.5 ou superior. • Servidor de Aplicação (como o Internet Information Services (IIS) ou Apache). Apesar de não consta nada sobre Apache na documentação, acreditamos que ele também possa ser usado. No entanto, iremos utilizar apenas o IIS neste artigo. • Ferramentas de conectividade do SQL Server 2000 SP4 ou superior. Neste artigo estaremos utilizando um servidor com Windows Server 2003 Service Pack 2, .NET Framework 2.0, Internet Explorer 6.0 e Internet Information Services (IIS) 6.0. Uma vez feito o download do arquivo zip citado anteriormente, execute a instalação seguindo os passos descritos abaixo: 1. Uma vez que tenhamos garantida a configuração dos prérequisitos para instalação da ferramenta SQL Server Web Data Administrator, localize o arquivo SqlWebAdmin.zip (que você já deve ter feito o download no site do CodePlex) e extraia os arquivos para o caminho C:\SqlWebAdmin. Caso você ainda não esteja com o Internet Information Services instalado, nos endereços abaixo você encontra dois artigos com um passo-a-passo para a instalação dos mesmos no Windows Server 2003 e também no Windows XP: - http://www.baboo.com.br/absolutenm/templates/content.asp? articleid=9761: Instalando o IIS no Windows XP. - http://imasters.uol.com.br/artigo/1345/servidores_windows/ configurando_o_iis_60: Instalando o IIS 6.0 no Windows Server 20003. 2. Concluída a extração dos arquivos, navegue pela estrutura de arquivos até encontrar a última pasta de nome SqlWebAdmin. Clique com o botão direito sobre a pasta e como apresentado na Figura 1, selecione a opção Properties (Propriedades).

Instalando o SQL Server Web Data Administrator Como será demonstrado no decorrer deste tópico, a instalação do SQL Server Web Data Administrator é bastante simples e inicia com o download do arquivo SqlWebAdmin.zip no portal CodePlax no endereço http://www.codeplex.com/SqlWebAdmin/ Release/ProjectReleases.aspx. Porém, antes de iniciar a instalação da ferramenta, a máquina onde esta será instalada deverá satisfazer alguns requisitos como segue: • Windows Server ou Professional com SP4, Windows Server 2003 ou ainda Windows XP SP2 ou superior.

30 SQL73.indb 30

Figura 1. Selecionando a opção Properties da pasta SqlWebAdmin

SQL Magazine - Administrando o SQL Server com uma Ferramenta Web 25/02/2010 14:30:48

sql server

3. Na janela de propriedades da pasta SqlWebAdmin, selecione a guia Web Sharing (Compartilhamento Web) e clique sobre a opção Share this folder (Compartilhar esta pasta). Na janela Edit Alias simplesmente selecione a opção “Execute (includes scripts)” [Executar (inclui scripts)] e clique em Ok (Figura 2).

clique com o botão direito sobre a barra de tarefas do Windows e selecione a opção Propriedades (Properties) conforme apresentado na Figura 5. Na janela de Propriedades da Barra de tarefas e do menu Iniciar, selecione a guia Menu ‘Iniciar’, clique sobre a opção Menu ‘Iniciar’ clássico e depois sobre o botão Personalizar... (Figura 6). Em Opções avançadas do menu ‘Iniciar’ (Figura 7) selecione a opção Exibir ‘Ferramentas administrativas’ e clique em OK para confirmar a configuração. A partir deste momento o item Ferramentas Administrativas já deverá estar sendo apresentado no menu Iniciar Programas.

Figura 2. Opção Execute durante o compartilhamento da pasta SqlWebAdmin

Neste momento a janela de propriedades da pasta SqlWeb Admin estará semelhante à apresentada na Figura 3. Após isso clique em Ok.

Figura 4. Janela de administração do Internet Information Services (IIS)

Figura 5. Opção Propriedades na barra de tarefas do Windows

Figura 3. Criando um compartilhamento Web para a pasta SqlWebAdmin

4. Antes de começar a usar a ferramenta, ainda é preciso fazer algumas configurações no IIS. Para isso, acesse o menu IniciarProgramasFerramentas Administrativas e selecione a opção Internet Information Services (IIS) Manager. Será então aberta a janela principal de administração do IIS que pode ser vista na Figura 4. Caso você esteja utilizando o Windows 2000 Professional ou Windows XP, pode ser que não encontre a opção “Ferramentas Administrativas” no item de menu Programas. Neste caso,

Figura 6. Selecionando o botão Personalizar


31

25/02/2010 14:30:48

é porque você não está com o Microsoft .NET Framework 2.0 instalado. Neste caso, instale o .NET Framework 2.0 e depois prossiga com a configuração. Caso necessário, você pode fazer o download do .NET Framework 2.0 nos seguintes links: - htt p:// w w w.microsof t.com/dow nloads/details. aspx? Fa mi lyI D = 0 8 5 6 e a c b - 4 3 62- 4 b 0 d - 8 e d d - a a b1 5 c 5 e 0 4 f 5 &DisplayLang=en: .NET Framework 2.0 32bits. - htt p:// w w w.microsof t.com/dow nloads/details. aspx? familyid=B44A0000-ACF8-4FA1-AFFB-40E78D788B00 &displaylang=en: .NET Framework 2.0 64bits.

Figura 7. Selecionando a opção de Ferramentas Administrativas

5. Aberta a janela principal do IIS, expanda o nó que contém o nome do seu computador (no caso deste artigo SRVSQL2008). Em seguida, no nó Web Service Extensions selecione a extensão “All Unknow ISAPI Extensions” e depois clique sobre o botão “Allow” (Figura 8). A extensão ISAPI (IIS Application Program Interfaces) estende as habilidades das páginas web e sua ativação é um dos requisitos para executar páginas ASP (Active Server Pages) no IIS 6.0. Figura 9. Selecionando a opção Properties do site SqlWebAdmin

Figura 8. Ativando a extensão ISAPI no Internet Information Services

6. Após a ativação da extensão, expanda o nó Web Sites e também “Default Web Sites”. Como apresentado na Figura 9, clique com o botão direito sobre a pasta SqlWebAdmin e selecione a opção Properties (Propriedades). 7. Na janela de Propriedades do site, selecione a guia ASP. NET e no campo “ASP.NET version” selecione a versão 2.0.50727 e clique sobre o botão Ok (Figura 10). Caso esta versão não apareça como uma opção para você, certamente

32 SQL73.indb 32

Figura 10. Configurando a versão do ASP.NET nas propriedades do site SqlWebAdmin

Isso conclui a instalação e configurações necessárias para a utilização da ferramenta.


sql server

Trabalhando com o SQL Server Web Data Administrator

Nota do DevMan 1

Uma vez que o SQL Server Web Data Administrator está instalado e configurado corretamente, é possível acessar a ferramenta de qualquer estação de uma rede utilizando o Internet Explorer e usando a URL http://nome_servidor/SqlWebAdmin/default.aspx, onde nome_servidor é o nome do servidor onde você instalou o SQL Server Web Data Administrator. Diante da janela de login (Figura 11), o processo de logon é exatamente igual ao executado pelo Query Analyzer do SQL Server 2000 ou ainda o SQL Server Management Studio do SQL Server 2005 (Nota DevMan 1). Ou seja, você pode usar o modo de autenticação SQL (usando um usuário do SQL Server) ou ainda autenticação Integrada do Windows (usando um login do Windows). Para demonstrar a vocês a utilização de alguns recursos da ferramenta, utilizaremos uma estação com Windows Vista e iremos nos conectar a uma instância remota do SQL Server 2005 Express utilizando o login sa (administrador do SQL Server). Quando digo “instância remota” quero dizer que o SQL Server 2005 Express está instalado em outra máquina da minha rede, chamada WINXPDEV. Notem que neste cenário estamos utilizando três máquinas diferentes, o SQL Server Web Data Administrator está instalado em um servidor Windows Server 2003 SP2 de nome SRVSQL2008, o SQL Server 2005 Express está instalado sobre o Windows XP SP2 em uma segunda máquina da rede que responde pelo nome WINXPDEV e estaremos utilizando ou acessando a ferramenta através de uma terceira máquina que possui o Windows Vista SP1 e responde pelo nome WINVISTA. Ao abrir o Internet Explorer no Windows Vista e colocar a URL http://srvsql2008/SqlWebAdmin/default.aspx, a página de login do SQL Server Web Data Administrator é carregada e podemos então entrar com os dados de conexão conforme apresentada na Figura 11. No exemplo da Figura 11 estamos estabelecendo uma conexão com o servidor SQL Server 2005 Express utilizando o login sa (administrador do SQL Server Express).

Após efetuar o login temos acesso à página principal do SQL Server Web Data Administrator (apresentada na Figura 12). Nesta página podemos notar a presença de alguns menus do lado esquerdo os quais permite a execução de tarefas como: • Gerenciamento de Banco de Dados: opções para criar, consultar, editar e excluir banco de dados e seus respectivos objetos (tabelas, views, procedures, usuários, roles, etc). • Importação de dados: permite importar dados e objetos de um banco de dados a partir de arquivos de scripts .sql contendo comandos Transact-SQL. • Exportação de dados: permite exportar dados e estruturas dos objetos de um banco de dados selecionado salvando o resultado como um arquivo de script .sql. • Gerenciamento de Segurança: permite gerenciar logins e associar logins a fixed server roles, como por exemplo, tornar um login administrador do SQL Server.

Figura 11. Janela de login do SQL Server Web Data Administrator com os dados de logon

Figura 12. Janela principal do SQL Server Web Data Administrator

SQL Server Management Studio SQL Server Management Studio é uma ferramenta incluída no SQL Server 2005 para configuração, gerenciamento e administração de todos os componentes do Microsoft SQL Server. A ferramenta inclui editores de script e ferramentas gráficas que trabalham com objetos e funcionalidades do servidor. Uma funcionalidade central do SQL Server Management Studio é o Object Explorer, que permite ao usuário consultar, selecionar e atuar sobre qualquer objeto do servidor. A Microsoft criou também uma ferramenta gráfica de configuração chamada SQL Server Management Studio Express (SSMSE) para o SQL Server Express. Assim como todos os produtos “Express” da Microsoft, ela pode ser baixada gratuitamente. As limitações da ferramenta estão associadas à impossibilidade de gerenciamento de Serviços de Análise do SQL Server, Serviços de Integração, Notificação de Serviços, Serviços de Relatório ou Edição do SQL Server 2005 para dispositivos móveis. A versão full do SQL Server Management Studio combina as funcionalidades do Enterprise Manager, Query Analyzer e Analysis Manager, incluídos nas versões anteriores do SQL Server, em um único ambiente. Além disso, SQL Server Management Studio trabalha com todos os componentes do SQL Server tais como Serviço de Relatório, Serviço de Integração, Edição compactada do SQL Server e Serviço de Notificação.


33

25/02/2010 14:30:49

A partir deste ponto, veremos alguns dos recursos oferecidos pelo SQL Web Data Administrator.

Criando e Editando um Banco de Dados

SQL Server 2000 ou SQL Server Management Studio do SQL Server 2005. No exemplo da Figura 17 demonstramos a execução de um script que cria a tabela TB_SQLMAG e inclui três novos registros, cria a procedure usp_sqlmag e

Para criar um banco de dados no Web Data Administrator, basta selecionar o link “Create new database”, localizado no canto superior direito na página de databases. Ao clicar sobre o link, uma nova página será apresentada solicitando que você informe um nome para o novo banco de dados. No exemplo da Figura 13 estamos solicitando a criação do banco de dados DB_SQLMAGAZINE.

Figura 13. Informando um nome para o novo banco de dados

Após criar o novo banco de dados, você notará a presença dos menus Tables, Stored Procedures, Query, Properties, Users e Roles. Estes menus também estão disponíveis quando você seleciona um banco de dados na lista de databases apresentada na página principal e através deles é possível executar tarefas como: • Criar, editar, renomear e excluir tabelas ou procedures. • Executar consultas ad-hoc e comandos Transact-SQL selecionando o menu Query. • Visualizar as propriedades do banco de dados e também configurar a propriedade Automatically grow file tanto para o arquivo de dados quanto para o arquivo de log. Esta opção funciona exatamente como as opções que temos no Enterprise Manager do SQL Server 2000 ou SQL Server Management Studio do SQL Server 2005 permitindo que você configure os arquivos de dados e log do banco de dados para expandir automaticamente na medida em que mais espaço é necessário para alocar os dados. • Criar, editar e excluir usuários ou database roles. • Editar databases roles e atribuir ou remover usuários das roles. Na Figura 14 você pode ver estes menus após termos selecionado o banco de dados AdventureWorks. Como um exemplo, para criar ou editar um stored procedure (SP) existente basta selecionar o menu “Stored Procedures”. Estando na página “Stored Procedures” (Figura 15), você pode criar uma nova SP clicando sobre o link “Create new stored procedure” localizado no canto superior direito da página, ou ainda editar ou excluir uma stored procedure utilizando os links “edit” ou “delete” referente à SP desejada. Na Figura 16 temos um exemplo de edição da SP GetOrdersSignedProc do banco de dados AdventureWorks. Através do menu Query você também poderá executar suas consultas ou comando Transact-SQL da mesma forma que os fazem nas ferramentas gráficas Query Analyzer do

34 SQL73.indb 34

Figura 14. Menus Tables que permite a criação ou gerenciamento de tabelas em um banco de dados

Figura 15. Página de gerenciamento de Stored Procedures do SQL Web Data Administrator

Figura 16. Editando uma stored procedures no SQL Web Data Administrator


sql server

logo em seguida executa a procedure sobre o banco de dados DB_SQLMAGAZINE. Note que também existem opções para você salvar a query (Save Query) e carregar um arquivo de script existente (Load Query). Observe que o resultado da execução da query é apresentado logo abaixo do painel de edição de código.

Exportando e Importando Dados e Objetos Duas funcionalidades também muito úteis no SQL Server Web Data Administrator são as opções para exportar e importar dados e objetos de um banco de dados. Ao acessar o item Export no menu SERVER TOOLS você será direcionado para a página “Export Database” apresentada na Figura 19. Nesta página você pode selecionar um banco de dados e selecionar também o que deseja exportar. É possível exportar apenas os scripts de criação do database, tabelas e procedures ou ainda exportar também os dados das tabelas em formato de INSERT.

Figura 17. Executando consultas no SQL Web Data Administrator

No menu Properties você pode visualizar informações sobre as propriedades do banco de dados e ainda configurar as opções de “Automatically grow file” para os arquivos de dados e log de um banco de dados. Na Figura 18 temos a página de propriedades para o banco de dados AdventureWorks. Observe que por default a opção “Automatically grow file” está ativada tanto para o arquivo de dados quanto para o de log. Isso significa que na medida em que mais espaço é necessário para armazenar os dados, o SQL Server expande os arquivos de dados e log automaticamente de acordo com o valor em Megabytes e Percentagem configurados para os arquivos de dados e log respectivamente.

Figura 18. Visualizando as propriedades do banco de dados AdventureWorks

Figura 19. Página de exportação de dados e objetos do SQL Web Data Administrator

Na Figura 20 é exibido um exemplo com a exportação dos objetos do banco de dados DB_SQLMAGAZINE. Note que no exemplo a tabela TB_SQLMAG e seus respectivos dados e também a procedure usp_sqlmag são exportados para um arquivo de extensão .sql. Uma vez que você possui um script Transact-SQL, você pode então usar o menu Import para importar os scripts para dentro de um banco de dados existente ou ainda importar um script completo para criação de um banco de dados e seus respectivos objetos. Para melhor exemplificar, na página de exportação de banco de dados (Figura 20) exportamos o banco de dado DB_SQLMAGAZINE utilizando as opções default. Isso gerou um script me permitindo criar um novo banco de dados com as mesmas configurações e objetos que o banco de dados DB_SQLMAGAZINE. Para mostrar a importação, vamos alterar o script apenas substituindo o nome DB_SQLMAGAZINE por DB_SQLMAGAZINE2 e através do menu Import vamos executar a importação do script criando assim um novo banco de dados de nome DB_SQLMAGAZINE2. O resultado da execução do script pode ser visto na Figura 21. Note que da mesma forma que é possível carregar um script para criar um banco de dados completo, você também pode usar a opção de Import apenas para criar alguns objetos sobre um banco de dados. Como exemplo, usamos a opção de Import para importar um script para recriar a tabela TB_SQLMAG e a procedure usp_SQLMAG com o sufixo 2. Na Figura 22 podemos ver as tabelas criadas no banco de dados DB_SQLMAGAZINE2.


35

25/02/2010 14:30:51

Figura 20. Exportando os objetos e dados do banco de dados DB_SQLMAGAZINE

você poderá criar e editar logins, incluir ou remover os papéis de servidor do SQL Server (fixed server roles) e dar acesso aos bancos de dados existentes. Na Figura 23 podemos ver a página de criação de logins durante a criação do login user_sqlmag.

Figura 21. Criando uma cópia do banco de dados DB_SQLMAGAZINE através da opção Import

Figura 23. Criando um login através do SQL Server Web Data Administrator

Figura 22. Visualizando as tabelas do banco de dados DB_SQLMAGAZINE2

Criando Logins e Usuários O SQL Server Web Data Administrator também permite que você gerencie logins e usuários em um servidor SQL Server. Selecionando o menu Security na página principal

36 SQL73.indb 36

Após ter criado um login você também poderá acessar um banco de dados e criar um usuário para o respectivo login, permitindo assim que no futuro o usuário possa se conectar ao banco de dados. Para fazer isso, você deve selecionar um banco de dados na lista de databases da página principal e utilizar o menu ‘‘User”, selecionando então o link “Create new user” no canto superior direito da página “Database User”. Como apresentado na Figura 24, durante a criação do usuário você também pode informar a quais databases roles o usuário deverá ser incluído. Tarefa esta que também pode ser feita selecionado o menu Roles. No exemplo da Figura 24, o usuário user_sqlmag é adiciondo à role db_owner, o que permite a este usuário executar qualquer tarefa sobre este banco de dados.


sql server

A SQL Magazine tem que ser feita ao seu gosto. Para isso, precisamos saber o que você, leitor, acha da revista!

Conclusão

Dê seu voto sobre este artigo, através do link:

Como vimos no decorrer deste artigo, o SQL Server Web Data Administrator é uma ótima alternativa para quem

www.devmedia.com.br/sqlmagazine/feedback


sobre e s

Figura 24. Criando um usuário com SQL Server Web Data Administrator

Feedback eu

edição ta


Dê s

possui um servidor SQL Server 7, 2000 ou ainda 2005 e não tem acesso as ferramentas administrativas gráficas que acompanham o produto. Como vimos, com esta ferramenta é possível executar praticamente todas as tarefas básicas da administração de uma base de dados SQL Server e ainda tem-se a grande vantagem de poder ser acessada via browser através de qualquer máquina de uma rede, isso sem precisar instalar nas máquinas qualquer outra ferramenta do SQL Server. No entanto, existe um requisito muito importante que devemos estar atento: no servidor onde instalarmos o SQL Server Web Data Administrator é preciso também instalar as ferramentas de conectividades do SQL Server 2000 SP4 ou uma versão superior.

37

25/02/2010 14:30:53




Compressão de dados no SQL Server De que se trata o artigo? O artigo aborda o tema de compressão de dados no SQL Server 2005 e o novo recurso disponibilizado pela versão 2008, descrevendo como utilizá-lo e seu benefícios.

Q Vladimir Michel Magalhães [email protected]

Bacharel em Ciência da Computação pela Universidade Federal do Rio Grande do Norte (UFRN), trabalhou durante vários anos como instrutor de informática do Senac/RN e atualmente trabalha como Analista de Banco de Dados da Universidade Potiguar (UnP), além de ser consultor de empresas, com foco na área de banco de dados. É DBA certificado Microsoft em SQL Server 2005 e 2008, além de outras certificações. http://vladimir-magalhaes.spaces.live.com/

38 SQL73.indb 38

uando falamos em bancos de dados, um assunto recorrente é o espaço utilizado para armazená-los. Bancos de dados normalmente ocupam um grande espaço de armazenamento e não é incomum ver Administradores de Bancos de Dados (DBAs) tendo que negociar mais espaço para garantir o crescimento das bases sem problemas. O problema é que, muitas vezes, não há espaço disponível, e é necessário comprar mais discos, o que representa um custo a mais para a empresa. Por mais que o custo por Gigabyte tenha decrescido bastante nos últimos anos, a compra de novos discos sempre representa um custo, e nenhum gestor vai aceitá-lo sem uma boa justificativa, normalmente questionando se não há uma forma de aperfeiçoar o uso do espaço disponível de forma que não seja necessária uma nova compra.

Para que serve? Comprimir os dados armazenados em um banco de dados é bastante útil para se obter ganho de espaço e de desempenho, o que consiste em um dos principais desafios no gerenciamento de bancos de dados com grande volume de dados.

Em que situação o tema é útil? Em qualquer ambiente onde se deseje economizar espaço de armazenamento do banco de dados, a aplicação de recursos de compressão de dados providos pelo SQL Server 2005 e 2008 se apresenta como uma alternativa interessante para minimizar tais problemas.

Bancos de dados em geral são bastante passíveis de compressão, visto a natureza repetitiva dos dados armazenados. Outro ponto a considerar é que com a compressão há uma série de outros ganhos que podem ser conseguidos, como o aumento de desempenho devido

SQL Magazine - Compressão de dados no SQL Server 25/02/2010 14:30:54

sql server

a menor quantidade de I/O, já que a quantidade de Bytes armazenados será menor, além de um menor espaço utilizado para os backups. O SQL Server dispõe de uma série de recursos, desde a versão 2005, que permitem fazer melhor uso do espaço em disco, garantido uma razoável economia. Ao longo deste artigo conheceremos um pouco sobre estes recursos de compressão de dados.

Compressão no SQL Server 2005 O SQL Server 2005 já dispunha de alguns recursos para permitir a compressão de dados. Uma delas era a habilidade de comprimir dados utilizando a compressão NTFS do Windows. Para utilizar tal recurso, você deve mover os dados que deseja comprimir para um arquivo de dados secundário (normalmente arquivos .ndf) e configurá-lo como read-only (somente leitura). Você pode até configurar todo o banco de dados como somente leitura, o que permitiria comprimir todos os arquivos de dados e até o arquivo de log, o que normalmente só faz sentido quando se tem apenas uma grande quantidade de dados históricos, pois há a perda de desempenho inerente a qualquer forma de compressão, devido ao processo de compressão/descompressão. Outra forma de compressão presente na versão 2005, depois de aplicado o Service Pack 2, é a compressão de dados numeric/decimal, chamada de vardecimal. Para utilizar esta forma de compressão, você deve ativá-la no banco de dados e então em cada tabela nas quais deseja utilizar tal recurso. O vardecimal funciona de forma semelhante ao varchar, só que para dados numéricos. Quando você define o tamanho máximo de um campo numeric/decimal, o SQL Server define a quantidade de Bytes que o mesmo irá ocupar, independentemente do valor que é armazenado. Imagine então que você definiu um campo decimal(18,4). O SQL Server definirá um tamanho de 9 Bytes para cada registro deste campo, quer você utilize ele ou não. Caso você armazene neste campo o valor 12.3, que poderia ser armazenado em um campo decimal(3,1) e que ocuparia 5 Bytes, você estaria desperdiçando 4 Bytes por registro. Caso então você ative a compressão através do vardecimal e armazene o mesmo valor, este ocuparia 3 Bytes, ou seja, menos até que o decimal “puro”, representando um ganho de 6 Bytes por registro. Vale lembrar que o vardecimal não é garantia de ganho de espaço. Dependendo da situação, este pode representar até um aumento no espaço utilizado, então podemos recomendar um estudo mais aprofundado do mesmo e uma análise caso a caso antes de utilizar tal recurso. Para testar a compressão NTFS, criaremos um banco de dados com o nome de Compressao, de acordo com a Listagem 1. Neste banco de dados, criaremos uma tabela no FileGroup Secondary, que depois será modificado para read only. Veja que além do arquivo de dados primário chamado Compressao1.mdf, criamos um arquivo de dados secundário, chamado Compressao2.ndf, que será armazenado na unidade F:.

Listagem 1. Criando o banco de dados de exemplo e testando a compressão NTFS USE [master] GO CREATE DATABASE [Compressao] ON PRIMARY ( NAME = N’Compressao1’, FILENAME = N’D:\Compressao1.mdf’ , SIZE = 51200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ) LOG ON ( NAME = N’Compressao_log’, FILENAME = N’D:\Compressao_log.ldf’ , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO ALTER DATABASE [Compressao] ADD FILEGROUP [SECONDARY] GO ALTER DATABASE [Compressao] ADD FILE ( NAME = N’Compressao2’, FILENAME = N’F:\Compressao2. ndf’ , SIZE = 51200KB , FILEGROWTH = 10240KB ) TO FILEGROUP [SECONDARY] GO INSERT INTO CompressaoNTFS (NOME) VALUES (‘TESTE 01’); GO INSERT INTO CompressaoNTFS (NOME) VALUES (‘TESTE 02’); GO INSERT INTO CompressaoNTFS (NOME) VALUES (‘TESTE 03’); GO /*Torna o filegroup Secondary apenas leitura*/ ALTER DATABASE [Compressao] MODIFY FILEGROUP [SECONDARY] READ_ ONLY GO

Agora iremos habilitar a compressão na unidade de disco F:, onde se encontra o Filegroup “Secondary”. Para isso, primeiro pare o serviço do SQL Server. Depois, vá até as propriedades da unidade, clicando com o botão direito do mouse sobre a mesma e depois na opção Propriedades. Na janela que aparece (Figura 1), habilitamos a caixa “Compactar este disco para economizar espaço”. Por fim, reinicie o serviço do SQL Server.

Figura 1. Ativando a compactacao de unidade NTFS


39

25/02/2010 14:30:55

A Figura 2 mostra o arquivo de dados secundário do banco de dados na unidade NTFS com compressão ativada.

Listagem 3. Testando a compressão VarDecimal /*Exibe o tamanho ocupado pela tabela, antes da compressão VarDecimal*/ 1. EXEC sp_spaceused ‘CompressaoVarDecimal’ 2. GO /*Exibe uma estimativa do tamanho da tabela caso seja ativada a compressão VarDecimal */ 3. EXEC sys.sp_estimated_rowsize_reduction_for_vardecimal ‘CompressaoVarDecimal’ 4. GO

Figura 2. Arquivo de dados em unidade com compressão NTFS ativada

Neste caso, não é preciso fazer mais nada no SQL Server, visto que a compressão é controlada pelo Windows. É importante lembrar que nesse caso, não podemos alterar ou inserir novas informações na tabela. Neste momento, para testarmos a compressão Vardecimal criaremos uma nova tabela (CompressaoVarDecimal) com cinco campos decimal, e vamos inserir alguns registros na tabela através de um loop que gera números randômicos. Os comandos podem ser vistos na Listagem 2.

/*Ativa a compressão VarDecimal no banco de dados*/ 5. EXEC sp_db_vardecimal_storage_format ‘Compressao’, ‘ON’ 6. GO /*Ativa a compressão VarDecimal na tabela*/ 7. EXEC sp_tableoption ‘CompressaoVarDecimal’, ‘vardecimal storage format’, 1 8. GO /*Exibe o tamanho ocupado pela tabela, após a compressão VarDecimal*/ 9. EXEC sp_spaceused ‘CompressaoVarDecimal’ 10. GO

A Figura 3 mostra os resultados da execução dos comandos da Listagem 3.

Listagem 2. Criando a tabela sem compressão USE Compressao; GO CREATE TABLE CompressaoVarDecimal( CODIGO INT NOT NULL IDENTITY(1,1) PRIMARY KEY, VALOR1 DECIMAL(18,4) NOT NULL, VALOR2 DECIMAL(18,4) NOT NULL, VALOR3 DECIMAL(18,4) NOT NULL, VALOR4 DECIMAL(18,4) NOT NULL, VALOR5 DECIMAL(18,4) NOT NULL ) ON [PRIMARY]; GO DECLARE @CONTADOR SMALLINT, @QUANTIDADE SMALLINT DECLARE @MENOR TINYINT, @MAIOR TINYINT SET SET SET SET

@CONTADOR = 1; @QUANTIDADE = 30000; @MENOR = 1 @MAIOR = 99

WHILE @CONTADOR <= @QUANTIDADE BEGIN INSERT INTO CompressaoVarDecimal (VALOR1,VALOR2,VALOR3,VALOR4,VALOR5) VALUES ( ROUND(((@MAIOR - @MENOR -1) * RAND() ROUND(((@MAIOR - @MENOR -1) * RAND() ROUND(((@MAIOR - @MENOR -1) * RAND() ROUND(((@MAIOR - @MENOR -1) * RAND() ROUND(((@MAIOR - @MENOR -1) * RAND() )

Figura 3. Espaço ocupado antes e depois de ativada a compressão VarDecimal

+ + + + +

@MENOR), @MENOR), @MENOR), @MENOR), @MENOR),

0) 0) 0) 0) 0)

+ + + + +

0.1, 0.1, 0.1, 0.1, 0.1

ET @CONTADOR += 1; S END

Antes de ativar a compressão, vamos seguir alguns passos descritos na Listagem 3. Primeiramente, vamos verificar o espaço ocupado pela mesma utilizando a stored procedure (SP) sp_spaceused (linha 1). Depois, iremos estimar o espaço ocupado pela tabela caso seja ativada a compressão Vardecimal, e comparar este valor com o tamanho atual da tabela, com a SP sys.sp_estimated_rowsize_reduction_for_vardecimal (linha 3). Agora iremos ativar a compressão Vardecimal no banco de dados, com a SP sp_db_vardecimal_storage_format (linha 5) e, em seguida, na tabela, com a SP sp_tableoption (linha 7). Após isso, vamos novamente verificar o espaço ocupado pela tabela, com a SP sp_spaceused (linha 9).

40 SQL73.indb 40

Veja que no primeiro resultado é exibido que os dados da tabela ocupavam originalmente um espaço total de 1728 KB (coluna data). Em seguida, o próximo resultado mostra que, em média, cada registro da tabela ocupa 56 KB (coluna avg_rowlen_fixed_format) e que a estimativa é de que depois de ativada a compressão VarDecimal, cada registro ocupe 36 KB (coluna avg_rowlen_vardecimal_format), o que coincide com a estimativa feito no início do artigo, com uma economia de 4 Bytes por campo Decimal, que no nosso caso eram 5 (4 x 5B = 20B), e um total de 20 Bytes de economia por registro. Por fim, o terceiro resultado mostra o espaço ocupado pela tabela após a ativação da compressão VarDecimal, com os dados ocupando um total de 1192 KB (coluna data). Comparando-se o espaço ocupado antes e depois da ativação da compressão VarDecimal, vemos uma economia de mais de 30%. Obviamente este resultado pode variar dependendo da situação. Um ponto importante que também deve ser levado em consideração ao se utilizar a compressão vardecimal é que para todo tipo de dados variável, como o varchar por exemplo, para cada registro armazenado na tabela, podem ser armazenados 2 Bytes de offset,


sql server

que servem para indicar o comprimento daquela coluna em disco, já que esta não possui um tamanho fixo. Além disto, caso a sua tabela tenha alguma coluna de tamanho variável, o SQL Server armazena 2 Bytes para indicar o número de colunas de tamanho variável da tabela. Então, caso sua tabela não possua nenhuma outra coluna variável e você adicione uma para tirar vantagem do armazenamento variável, leve em conta estes 2 Bytes extras por registro. Isto também explica porque não faz sentido criar uma coluna do tipo Varchar (2), já que apenas o espaço gasto com o offset ocuparia os mesmos 2 Bytes caso a coluna fosse Char(2), sem contar os Bytes para armazenar o valor em si da coluna.

Compressão no SQL Server 2008 Com o SQL Server 2008, ambas as formas de compressão presentes no SQL Server 2005 continuam disponíveis, mas há novas formas que podem ser utilizadas: Row Compression (compressão em nível de registro) e Page Compression (compressão em nível de página), mas estas funcionalidades estão presentes apenas nas edições Enterprise e Developer do SQL Server 2008. Cada tabela, índice ou partição pode utilizar o seu tipo de compressão independente das outras, mas apenas um tipo de compressão para cada uma. Quando utilizando compressão de linha, o SQL Server 2008 armazena todos os tipos de dados de tamanho fixo (char, smallint, integer, etc..) como se fossem tipos de dados variáveis, ou seja, ele utiliza a mesma estratégia do vardecimal para todos os tipos de dados de tamanho fixo. Por exemplo, se você utilizar um campo do tipo integer, que por padrão ocupa 4 Bytes, mas armazenar neste campo o valor 2, que poderia ser armazenado em um campo tinyint de 1 Byte, o SQL Server irá ocupar apenas o 1 Byte necessário. Vale lembrar que isto não muda de forma alguma a forma de utilização destes tipos de dados, então você não precisa fazer qualquer alteração em sua aplicação para tirar vantagem desta novidade. Outra diferença é que o offset necessário para indicar o tamanho de um campo de tamanho variável ou de um campo de tamanho fixo, mas com a compressão ativada, é de apenas 4 bits para todas as colunas de tamanho menor ou igual a 8 Bytes, evitando assim o problema existente para campos Varchar(2), que foi citado anteriormente, ou seja, para o mesmo campo integer que citamos agora a pouco, seriam gastos 1 Byte para armazenar o valor do campo, mais outros 4 bits para o offset. Além disto, com esta forma de compressão, qualquer campo que esteja com o valor NULL e qualquer campo numérico com o valor 0 não ocupam nenhum espaço em disco, o que em várias situações pode representar uma grande economia de espaço. A Figura 4 mostra um exemplo do funcionamento da compressão de linha.

Figura 4. Funcionamento da compressão de linha

Já o método de compressão de página minimiza a redundância dos valores em colunas em um ou mais registros dentro de uma mesma página de dados. Isto é feito utilizando duas técnicas de compressão: prefixo da coluna e dicionário de página. A técnica de prefixo da coluna trata os padrões de repetição no início dos valores armazenados, o que para um índice na coluna nome, por exemplo, seria bem comum, já que os valores são ordenados e provavelmente seriam similares. Imagine então que você tem uma coluna que está em uma mesma página de dados e possui os valores “Maria Antônia”, “Maria dos Santos”, “Maria Santos”, “Maria”, “Mario” e “Mario de Santos”. Como podemos perceber, os prefixos dos valores desta coluna são bem redundantes. Neste caso, o que provavelmente iria ocorrer era a definição de um prefixo “Mari” que seria armazenado em um local chamado “CI Structure”, e os registros apontando para este prefixo da seguinte forma: “1a Antônia”, “1a dos Santos”, “1 Santos”, “1a”, “1o” e “1o de Santos” (não obrigatoriamente desta forma, isto é um exemplo e tudo depende dos dados existentes na página). Um exemplo deste funcionamento pode ser visto na Figura 5.

Figura 5. Funcionamento da técnica de prefixo de coluna

Já a técnica de dicionário de página basicamente é um “dicionário” criado também na “CI Structure”, onde são armazenados os valores com certa repetição dentro da coluna. O dicionário de página é construído em cima da estrutura de prefixos da coluna, o que levará a uma situação onde provavelmente haverá valores do dicionário apontando para valores de prefixo de coluna. Um exemplo deste funcionamento pode ser visto na Figura 6.

Figura 6. Funcionamento da técnica de dicionário de página


41

25/02/2010 14:30:56

Utilizando então a mesma coluna do exemplo anterior, terminaríamos com algo similar ao seguinte: “2 Antônia”, “2 dos 3”, “1 3”, “2”, “4” e “4 de 3”, onde foram criadas 3 “palavras” no dicionário desta página, com o “1” representando o prefiro “Mari”, o “2” representando “1a”, o “3” representando “Santos” e o “4”representando “1o”. Vejam que, como esperado, o resultado do processamento com a técnica de prefixo de coluna, como “1a” (Maria) e “1o” (Mario), sofreu nova compressão com a técnica de dicionário de página. Uma vantagem interessante destes novos métodos de compressão comparados a compressão NTFS é que diferentemente deste último, os dados vão para a memória com compressão, permitindo assim manter uma maior quantidade de dados em cache, com a mesma quantidade de memória, diminuindo assim o I/O, como mostra a Figura 7, retirada de um artigo da HP sobre o assunto.

Listagem 4. Criando a tabela para os testes 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22.

USE Compressao; GO CREATE TABLE Compressao( C ODIGO INT NOT NULL IDENTITY(1,1) PRIMARY KEY, N OME CHAR(50) ); GO DECLARE @QUANTIDADE INT, @CONTADOR INT DECLARE @NOME CHAR(50) SET @CONTADOR = 1; SET @QUANTIDADE = 30000; WHILE @CONTADOR <= @QUANTIDADE BEGIN SET @NOME = ( ‘MARI’ + CHAR(64+(@CONTADOR%26)) + CHAR(64+(@CONTADOR%26)) + ‘ SANTOS ‘ + CHAR(64+(@CONTADOR%26)) + CHAR(64+(@CONTADOR%26)) ); I NSERT INTO Compressao (NOME) VALUES (@NOME); S ET @CONTADOR += 1; END;

/*Exibe o tamanho ocupado pela tabela, antes da compressão */ 23. EXEC sp_spaceused ‘Compressao’ 24. GO

Listagem 5. Testando a compressão de Linha (ROW) 1. USE Compressao; 2. GO /*Força o Rebuild da Tabela, ativando a compressão de Linha*/ 3. ALTER TABLE Compressao REBUILD WITH (DATA_COMPRESSION = ROW); 4. GO /*Exibe o tamanho ocupado pela tabela, após a compressão de Linha*/ 5. EXEC sp_spaceused ‘Compressao’ 6. GO

Figura 7. Número de leituras físicas com e sem a compressão

A Listagem 4 mostra os comandos necessários para criar a tabela que utilizaremos em nossos exemplos (Linhas 3 a 6). Nesta mesma listagem, estão os comandos com os quais inserimos 30000 registros na tabela (Linhas 8 a 22), propositalmente com bastante redundância, para servirem de base para os testes. Por fim, utilizamos novamente a SP sp_spaceused para verificar o tamanho da tabela. A Figura 8 mostra o tamanho da tabela antes da ativação da compressão.

Figura 9. Tamanho ocupado pela tabela depois de ativada a compressão de linha

Neste caso, houve um ganho de mais de 50% (de 1880KB para 832 KB – coluna data) no espaço ocupado. Obviamente, este resultado foi obtido devido à grande taxa de repetição gerada pelo script de inserção de dados, mas o que também não quer dizer que altas taxas de compressão possam ser obtidas. A Listagem 6 mostra um script semelhante ao anterior, mas agora ativando a compressão de página (linha 3) e novamente verificando o tamanho da tabela após a compressão (linha 5). Listagem 6. Testando a compressão de Página (PAGE) 1. USE Compressao; 2. GO

Figura 8. Tamanho ocupado pela tabela antes da compressão

Vejamos agora na Listagem 5 como utilizar a compressão de página. Nele mostramos como ativar a compressão (linha 3) e depois verificamos novamente o tamanho da tabela (linha 5). A Figura 9 mostra o tamanho da tabela depois da compressão de linha (coluna data).

42 SQL73.indb 42

/*Força o Rebuild da Tabela, ativando a compressão de Página*/ 3. ALTER TABLE Compressao REBUILD WITH (DATA_COMPRESSION = PAGE); 4. GO /*Exibe o tamanho ocupado pela tabela, após a compressão de Página*/ 5. EXEC sp_spaceused ‘Compressao’ 6. GO

A Figura 10 mostra o tamanho da tabela depois de ativada a compressão de página (coluna data).


sql server

Vejam que neste caso houve um ganho de mais de 80% (de 1880KB, valor da coluna data na Figura 8, para 344 KB – coluna data da Figura 10) no espaço ocupado. Novamente, este resultado foi obtido devido à grande taxa de repetição gerada pelo script de inserção de dados, mas taxas bem razoáveis de compressão tendem a ser obtidas.

Figura 10. Tamanho ocupado pela tabela depois de ativada a compressão de página

Considerações e restrições ao utilizar a compressão de dados Novamente, devemos analisar quando a compressão será benéfica ou não. Apesar de diminuir o uso de espaço em disco, a compressão aumenta o consumo de CPU, principalmente a compressão por página. Alguns estudos demonstram um aumento de 3% a 5%. Então para cada caso devemos analisar se o gargalo do servidor é espaço em disco ou CPU. Caso seja CPU, pode não ser interessante utilizar a compressão. Em caso contrário, esta provavelmente trará mais ganhos do que perdas, apesar de que em geral não é recomendado habilitar a compressão em tabelas que sejam freqüentemente atualizadas. Outros pontos importantes são que com a compressão ativada, aumenta o tempo de Rebuild dos índices e que ela não funciona com campos Unicode. Para a primeira situação, uma solução fácil seria repensar os horários de manutenção dos índices, já no segundo não há muito que fazer. A compressão também não é feita automaticamente assim que ativada devido ao impacto que isto poderia gerar em um banco de dados em produção. A cada atualização ou inserção de registros é que o SQL Server faz a compressão do registro ou da página alterada. Caso você deseje forçar a compressão, basta fazer um Rebuild da tabela. Além disto, a compressão não pode ser utilizada se o tamanho máximo do registro ultrapassar 8060 Bytes e ela não pode ser utilizada junto com o recurso de Colunas Esparsas. Além disso, ela não pode ser utilizada em colunas do tipo BLOB (XML, MAX, TEXT, etc.), a não ser que estas sejam armazenadas in-row, o que muitas vezes não é possível, além de ser um recurso que futuramente será removido do SQL Server. No caso dos campos BLOB, as possíveis soluções seriam fazer a compressão através da aplicação, o que força uma alteração da aplicação e também impede o uso das capacidades de pesquisa e atualização parcial presentes no SQL Server para campos BLOB. Uma outra e melhor solução é utilizar o recurso de FileStream do SQL Server 2008 ou armazenar os arquivos de dados em uma unidade NTFS com compressão ativada.

Compressão no SQL Server 2008 R2 Apesar de ainda não estar em sua versão RTM (atualmente está na versão CTP2), já há uma novidade relativa à compressão na versão 2008 R2 que veio a público. Nesta versão, será possível utilizar a compressão em colunas NCHAR e NVARCHAR, apenas com compressão de linha, não sendo possível comprimir outros tipos de colunas, nem mesmo do tipo NVARCHAR(MAX). O SQL Server 2008 R2 implementa para os campos Unicode um tipo de compressão chamada SCSU (Simple Compression Scheme for Unicode Data ou esquema simples de compressão para dados Unicode), que consiste em um tipo de compressão padrão para dados Unicode. Neste caso, a taxa de compressão dependerá do idioma utilizado. A Tabela 1 mostra a taxa de compressão para alguns idiomas, mas vale lembrar que para grande parte dos idiomas europeus, o ganho será de 50%. Locale Compression percentIdioma

Porcentagem de Compressão

Inglês

50%

Alemão

50%

Hindi

50%

Turco

48%

Vietnamita

39%

Japonês

15%

Tabela 1. Exemplos de taxa de compressão de campos Unicode

A compressão Unicode é habilitada ou desabilitada como parte da compressão de linha ou página sem necessidade de nenhum novo comando, bastando apenas ativar a compressão de linha ou página na tabela que contem a coluna Unicode. Isto implica que quem já utiliza a compressão de linha ou página precisará apenas realizar um Rebuild da tabela para ter o benefício da compressão, ou apenas os novos registros serão comprimidos. Isso implica também que todos os scripts existentes continuam funcionando sem necessidade de nenhuma alteração. Para alguns idiomas, como o coreano, a compressão não traz ganho algum. Então neste caso, mesmo que você habilite a compressão, caso o SQL Server 2008 R2 perceba que não haverá ganhos naquela coluna ele não aplicará a compressão internamente. A Listagem 7 apresenta um exemplo de compressão em uma tabela (CompressaoUnicode) com colunas Unicode. Na Figura 11 podemos ver o resultado da compressão em uma tabela contendo colunas Unicode.

Figura 11. Compressão em coluna Unicode


43

25/02/2010 14:30:57

Listagem 7. Testando a compressão em colunas Unicode USE Compressao; GO CREATE TABLE CompressaoUnicode( CODIGO INT NOT NULL IDENTITY(1,1) PRIMARY KEY, NOME NCHAR(50) -- utilizamos o tipo NCHAR, ao invéz de CHAR ); GO DECLARE @QUANTIDADE INT, @CONTADOR INT DECLARE @NOME CHAR(50) SET @CONTADOR = 1; SET @QUANTIDADE = 30000; WHILE @CONTADOR <= @QUANTIDADE BEGIN SET @NOME = ( ‘MARI’ + CHAR(64+(@CONTADOR%26)) + CHAR(64+(@CONTADOR%26)) + ‘ SANTOS ‘ + CHAR(64+(@CONTADOR%26)) + CHAR(64+(@CONTADOR%26)) ); NSERT INTO CompressaoUnicode (NOME) VALUES (@NOME); I SET @CONTADOR += 1; END;

Estimando os ganhos com a compressão Normalmente, antes de começar a utilizar a compressão estimamos os possíveis ganhos da utilização deste recurso, além de realizar alguns testes de impacto. O SQL Server fornece meios de fazer essa estimativa de ganhos, tanto no modo gráfico como também através de comandos T-SQL. Para estimar a compressão através de comandos T-SQL, utilizamos a SP sp_estimate_data_compression_savings. Primeiro desativamos a compressão da tabela (linha 3). Depois estimamos os ganhos da compressão com a compressão de Linha (linha 5) e finalmente com a compressão de página (linha 11), como mostra a Listagem 8. Listagem 8. Estimando a compressão 1. USE Compressao; 2. GO /*Força o Rebuild da Tabela, desativnado a compressão */ 3. ALTER TABLE Compressao REBUILD WITH (DATA_COMPRESSION = NONE); 4. GO /*Estimando a compressão de Linha*/ 5. EXEC sp_estimate_data_compression_savings 6. @index_id = 1, --tipo de indíce, no caso, Clustered 7. @partition_number = 1, --Partição a ser comprimida, no caso, 1 para tabela não particionada 8. @schema_name = ‘dbo’, --schema 9. @object_name = ‘Compressao’, --nome do objeto a ser comprimido 10. @data_compression = ‘ROW’ --tipo de compressão, no caso linha /*Estimando a compressão de Página*/ 11. EXEC sp_estimate_data_compression_savings 12. @index_id = 1, --tipo de indíce, no caso, Clustered 13. @partition_number = 1, --Partição a ser comprimida, no caso, 1 para tabela não particionada 14. @schema_name = ‘dbo’, --schema 15. @object_name = ‘Compressao’, --nome do objeto a ser comprimido 16. @data_compression = ‘PAGE’ --tipo de compressão, no caso página

Neste exemplo, estimamos a compressão com a tabela utilizada nos exemplos anteriores. O resultado está apresentado na Figura 12. A coluna “size_with_requested_compression_setting(KB)” exibe o tamanho estimado com o tipo

44 SQL73.indb 44

/*Exibe o tamanho ocupado pela tabela, antes da compressão Unicode*/ EXEC sp_spaceused ‘CompressaoUnicode’ GO /*Força o Rebuild da Tabela, ativando a compressão Unicode através da compressão de Linha*/ ALTER TABLE CompressaoUnicode REBUILD WITH (DATA_COMPRESSION = ROW); GO /*Exibe o tamanho ocupado pela tabela, após a compressão de Linha*/ EXEC sp_spaceused ‘CompressaoUnicode’ GO /*Força o Rebuild da Tabela, ativando a compressão Unicode através da compressão de Página*/ ALTER TABLE CompressaoUnicode REBUILD WITH (DATA_COMPRESSION = PAGE); GO /*Exibe o tamanho ocupado pela tabela, após a compressão Unicode */ EXEC sp_spaceused ‘CompressaoUnicode’ GO

de compressão selecionado. A estimativa neste caso foi bem razoável, visto os valores exibidos (832KB, como visto na coluna data da Figura 9, x 848KB para compressão de linha e 344KB, como visto na coluna data da Figura 10, x 360KB para compressão de página). Algumas pessoas questionam o fato de não poderem estimar os ganhos da compressão com outras edições que não seja a versão Enterprise antes de decidir pela compra da mesma. Para isso, é aconselhado utilizar a versão Developer para realizar os testes e tomar a decisão.

Benchmarks de Casos Reais A equipe da Storage Engine do SQL Server divulgou alguns números fornecidos por clientes e também de pesquisas feitas por terceiros. Eles dão uma visão bem interessante do quanto se pode ganhar com a compressão e a que custo. No endereço http://blogs.msdn.com/sqlserverstorageengine/archive/2009/08/16/update-on-data-compression-in-sqlserver-2008-rtm.aspx é possível encontrar este e muitos outros resultados sobre ambientes onde foi utilizada a compressão, que estão resumidos nas Tabelas 2 e 3. Ganho de espaço com a compressão

Notas

70%

PAGE. Aplicação de Data Warehouse.

40%

PAGE. Aplicação Web OLTP.

62%

PAGE. Aplicação de Data Warehouse.

38%, 21%

PAGE, ROW.

80%, 50%

PAGE, ROW.

52%

PAGE.

50%, 15%

PAGE, ROW.

81%

PAGE. Aplicação ERP.

35%

PAGE.

Tabela 2. Ganho de espaço com a compressão


sql server

Figura 12. Estimando os ganhos com a compressão

40%-60%

2% - 3%

PAGE. Grande quantidade de consultas em intervalo sequencial. Aplicação DW (Grande volume de IO) PAGE. 500 usuários, 1500 Transações / sec. OLTP com algumas consultas para relatórios. PAGE. Grande quantidade de insert, update e delete, que leva a um aumento no uso de CPU. Uma melhor escolha teria sido a escolha de ROW Compression. PAGE. Aplicação OLTP.

3%

PAGE. Aplicação ERP – transações pequenas.

1% 11%

Tabela 3. Ganho de performance com a compressão

Conclusão

Links Vardecimal http://msdn.microsoft.com/en-us/library/bb508963(SQL.90).aspx Blog do time do SQL Server Storage Engine http://blogs.msdn.com/sqlserverstorageengine/archive/2009/08/16/update-on-datacompression-in-sql-server-2008-rtm.aspx Criando tabelas e índices com compressão http://msdn.microsoft.com/en-us/library/cc280449.aspx Estimando os ganhos com a compressão http://msdn.microsoft.com/en-us/library/cc280574.aspx SQL Server 2008 R2 http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx

Dê seu feedback sobre esta edição! A SQL Magazine tem que ser feita ao seu gosto. Para isso, precisamos saber o que você, leitor, acha da revista!

Feedback eu

Dê seu voto sobre este artigo, através do link: www.devmedia.com.br/sqlmagazine/feedback


sobre e s

O recurso de compressão pode trazer enormes ganhos, principalmente na economia de espaço em disco. Considerando a situação atual dos bancos de dados, com a chamada explosão dos dados, com bancos de dados cada vez maiores, Very Large DataBases (VLDBs) e o crescente armazenamento de dados binários, este recurso pode ser realmente útil, desde que utilizado nas situações corretas. Muitas vezes apenas a economia de espaço em disco que é feita é mais do que o suficiente para justificar a compra de

uma licença do SQL Server Enterprise Edition, permitindo que você usufrua de todos os outros recursos que esta edição oferece, além da compressão.

Dê s

Notas PAGE. Aplicação Web OLTP. Grande número de transações.

edição ta

Impacto na performance 5%

45

25/02/2010 14:30:58




Oracle Performance Diagnostics & Tuning

U

m assunto de grande interesse tratando-se do SGBD Oracle é o famoso Tuning, ou como prefiro chamar, Performance Diagnostics & Tuning. Prefiro utilizar este nome porque mais importante que o ajuste é o diagnóstico.

Os métodos antigos

Ricardo Portilho Proni [email protected] http://portilho.profissionaloracle.com.br

Com cerca de 20 anos de experiência profissional, Ricardo Portilho Proni é Oracle ACE, e já trabalhou em grande parte dos maiores bancos de dados Oracle do Brasil. É atualmente o Team Leader do CES - Centro de Excelência Solvo, unidade dedicada às atividades de consultoria, arquitetura, pré-venda e suporte 3o. Nível da Solvo S/A, empresa especializada na implementação e suporte de ambientes de missão crítica e atuação em toda a América Latina. Também é Conselheiro do GPO, foi palestrante do V ENPO sobre Load Balance em RAC. Possui as certificações OCP 10g, OCE RAC, OCE Linux, MCP, MCDBA, MCTS: SQL Server 2005, Certified MySQL DBA, e IBM DB2 Certified Database Associate.

46 SQL73.indb 46

Antes de lembrar dos antigos métodos de Performance Diagnostics & Tuning, gostaria de dizer aos leitores que trago uma boa notícia: Performance Diagnostics & Tuning é fácil. Gosto de dizer isso a quem me pergunta sobre Performance Diagnostics & Tuning, pois este assunto foi por muito tempo envolto em grande mistério, sendo dominado apenas pelos mais experientes profissionais em banco de dados Oracle. Esta imagem do Performance Diagnostics & Tuning foi fixada, na minha opinião, pelos antigos métodos adotados (entre meados dos anos 80 e início dos anos 90) pelos próprios DBAs, e que eram recomendados em manuais e livros, e praticados pelos próprios consultores da Oracle. O antigo método consistia geralmente em seguir um Checklist, onde o DBA

De que se trata o artigo? Este artigo trata do método de diagnóstico de problemas de desempenho em banco de dados Oracle baseado em tempo.

Para que serve? Para identificar, rapidamente e com precisão, qual o gargalo que está restringindo o desempenho de uma aplicação que utiliza um banco de dados Oracle.

Em que situação o tema é útil? Quando o desempenho de uma aplicação que utiliza um banco de dados Oracle não é satisfatório

deveria verificar vários itens, e vários indicadores conhecidos como Hit Ratios (Buffer Cache Hit Ratio e Library Hit Ratio eram os principais, dentre muitos). Esta lista de itens que eram verificados deve ser bem conhecida para a maioria dos leitores: o consumo de processamento, memória, I/O e rede do sistema operacional, o uso da SGA e PGA, a fragmentação de tabelas e índices, a quantidade de sessões e cursores, eram alguns entre centenas de outros indicadores.

SQL Magazine - Oracle Performance Diagnostics & Tuning 25/02/2010 14:30:59

Oracle

O jogo era o seguinte: verificar a maior quantidade possível de indicadores, elaborar uma teoria sobre a lentidão baseada neles, e alterar algo que teoricamente melhore um ou mais destes indicadores, e medir novamente para ver se as alterações surtiam o efeito esperado. Mais do que isto, o DBA esperava que suas alterações não piorassem ainda mais o cenário. Em particular, o Buffer Cache Hit Ratio (que é a proporção de leituras que são feitas no Cache de dados da instância Oracle, ao invés do disco físico do sistema operacional) era mais atentamente observado pelos DBAs. Geralmente, se este estava abaixo de 90%, o DBA aumentava o parâmetro DB_CACHE_SIZE até onde era possível, e verificava se o número de leituras nos discos diminuía. Esta técnica, embora muito empregada até hoje, é fundamentalmente falha, pois o que o Oracle considera uma leitura física, pode não ser para o sistema operacional, pois este tem seu próprio Cache para o filesystem, assim como os discos rígidos, ou até mesmo o Storage pode ter o seu. Uma alternativa a este método, executada por DBAs menos experientes (ou mais desesperados) era analisar os parâmetros atuais do Oracle (são centenas, como você deve saber), identificar um ou mais deles que teoricamente tem relação com a lentidão verifica, e então alterá-los, e observar o resultado. Um método comum também era o de, através de utilitários do sistema operacional (como o top no Linux, ou topas no AIX), observar qual processo de usuário consumia mais recursos, identificar o SQL que este está fazendo, e tentar elaborar alguma melhoria nele. Outra técnica de Performance Diagnostics & Tuning muito empregada era adicionar hardware: se o banco de dados era considerado lento, aumentava-se a memória, e a SGA (Nota DevMan 1) (e/ou a PGA, que é a área de memória dos processos de usuário), ou até mesmo a quantidade de processadores, até que o desempenho ficasse satisfatório. Infelizmente, para infelicidade de quem comprou e pagou pelo upgrade, nem sempre isto acontecia. Aliás, há casos onde aumentar os recursos de hardware piorou o cenário. Citei todos estes métodos no tempo passado, mas vejo todos muito utilizados até hoje. Reconheço que todos estes métodos, que fique bem claro, funcionavam e ainda funcionam, e que os Hit Ratios não são de maneira alguma inúteis, apenas incompletos. O grande problema é que todos estes métodos dependiam completamente da experiência do profissional, e de alguma sorte.

O método correto Antes de executar um ajuste no SGBD Oracle, em resposta a um problema de desempenho, devemos ter um diagnóstico correto. Sem um correto diagnóstico, não faz sentido prosseguir. O desempenho de processos computacionais é medido por velocidade. Quanto mais rápido, melhor o desempenho. E quando se trata de velocidade, o desempenho só pode ser medido pelo TEMPO. O tempo é a única constante onde podemos nos basear sobre o desempenho de um processo computacional. Portanto, para um correto diagnóstico de um problema de desempenho, deve-se saber onde o tempo é gasto. Finalmente, para saber-se onde o tempo é gasto no Oracle, não há uma forma mais direta que observar os Wait Events (Eventos de Espera).

Nota do DevMan 1 SGA System Global Area (SGA) é uma terminologia usada no SGBD Oracle que representa uma área de memória compartilhada responsável por armazenar todas as informações referentes aos processos do Oracle. Essa área é dividida em várias outras áreas de memória que cada instância do banco de dados ocupa no SGA.

Os Wait Events são eventos de espera por recursos computacionais, físicos (discos rígidos, memória, rede, etc.) ou lógicos (latches, locks, etc.). Estes recursos são limitados, e são os únicos limitadores do desempenho de um comando SQL. Se não houvesse espera por recursos computacionais, todos os comandos SQLs seriam finalizados instantaneamente. Os Wait Events do Oracle são implementados pela OWI, a Oracle Wait Interface. Esta implementação mede as esperas diretamente do kernel do Oracle, e não é nova, foi introduzida na versão 7.0.12. Desde então, os Wait Events monitorados pela OWI cresceram consideravelmente, de 104 Wait Events na 7.0.12, para 220 no 8i, 400 no 9i, e mais de 800 no 10g. Este progresso mostra como a própria Oracle acredita e investe neste método de diagnóstico de problemas de performance. Sabem aquela telinha bonita de desempenho do Enterprise Manager, com um gráfico legal? Pois é, aqueles gráficos nada mais são do que os Wait Events do Banco de Dados. Mas, como eu prefiro o bom e velho SQL*Plus, este artigo irá demonstrar como encontrar os gargalos do banco de dados sem a ajuda do Enterprise Manager. Traçando um paralelo da OWI com a vida real, pense em quanto tempo você leva para ir de São Paulo ao Rio de Janeiro, de avião. Embora o tempo de vôo leve menos que 30 minutos, no tempo total a viagem completa pode levar umas 6 horas: é necessário ir até o aeroporto, comprar a passagem, fazer o Check In, despachar a bagagem, etc. Prosseguindo com a metáfora, o avião é o comando SQL, enquanto todas as outras tarefas necessárias para se completar a viagem são os Waits Events. Então, se você precisa otimizar este tempo de 6 horas, onde é mais fácil e produtivo otimizar? O processo pode ser otimizado em vários pontos: comprando-se previamente a passagem, levando apenas bagagem de mão, fazendo outro caminho para chegar ao aeroporto, etc. Mas otimizar o tempo de vôo é o mais difícil (e caro, pode ser necessário trocar o avião), e o que surtirá menos efeito no tempo total da viagem. Mesmo se comprarmos um Concorde, o tempo total da viagem diminuiria de 6 horas para 5 horas e 45 minutos.

Dynamic Performance Views As principais Dynamic Performance Views (“Visualizações Dinâmicas de Desempenho”) que você utilizará para fazer um diagnóstico de desempenho com a OWI serão as descritas a seguir: • V$SESSION_WAIT: Esta é a principal View da OWI, e mostra com detalhes os Waits Events das sessões atuais. O nível de detalhe é excelente, e é isto que permite um diagnóstico preciso


47

25/02/2010 14:30:59

do gargalo. Por exemplo, no evento db file sequential read, é exibido exatamente em qual arquivo está ocorrendo a leitura. Como esta View é muito precisa, e só tem com conteúdo o que estava acontecendo exatamente no momento de sua consulta, geralmente é necessário executá-la várias vezes seguidas, para poder observar o gargalo em tempo real; • V$SESSION_EVENT: Esta é uma versão um pouco mais flexível da V$SESSION_WAIT, pois mostra de forma acumulada os maiores gargalos sofridos no passado recente das sessões atuais, como demonstraremos mais adiante; • V$SYSTEM_EVENT: Esta View mostra todos os Waits Events acumulados da instância, desde que ela iniciou. Isto é muito útil para executar uma primeira análise em um ambiente que você não conhece, para se familiarizar sobre qual seria o problema geral de lentidão. Mas como é acumulada, esta View pode dar uma idéia errada do problema de desempenho se utilizada sozinha, e deve ser utilizada em conjunto com as outras Views. Vamos então, na Listagem 1, analisar um banco de dados de produção para definir onde está a lentidão, para demonstrar como utilizar a OWI. Listagem 1. Comando SELECT para verificar os Wait Events das sessões atuais SQL> SELECT SID, EVENT, SECONDS_IN_WAIT FROM V$SESSION_WAIT W ORDER BY SECONDS_IN_WAIT DESC; SID EVENT SECONDS_IN_WAIT ---------- ----------------------------------------------------53 SQL*Net message from client 103370 46 SQL*Net message from client 102775 44 SQL*Net message from client 101402 23 SQL*Net message from client 99173 38 SQL*Net message from client 98231 72 SQL*Net message from client 95574 58 SQL*Net message from client 95060 33 SQL*Net message from client 93662 101 SQL*Net message from client 91972 57 SQL*Net message from client 91888 32 SQL*Net message from client 88720 208 SQL*Net message from client 88371

O evento SQL*Net message from client significa que o Oracle já processou o que foi requisitado pela sessão, e está aguardando que outro comando seja executado pelo usuário. Por exemplo, pode ser uma sessão de SQL*Plus aberta, mas sem uso. Este é um dos Wait Events considerados Idle (Ociosos) pela documentação, e portando, não afetam o desempenho do banco de dados. Veja na Listagem 2 como obter da View V$EVENT_NAME (a coluna WAIT_CLASS está disponível no 10gR1 em diante) uma listagem dos Wait Events ociosos. Portanto, vamos excluir alguns deles de nossa análise para melhor claridade, e executar a consulta novamente conforme Listagem 3. É importante salientar que, mesmo que a coluna SECONDS_IN_WAIT mostre o valor 0, estes Wait Events não devem ser desprezados. Um mero segundo gasto em um destes Wait Events é muito, e em um sistema perfeitamente escalável, nenhum evento não ocioso seria visto neste SELECT. Sabendo disso, e observando-se o resultado do SELECT, executado várias vezes seguidas durante o período em que o sistema

48 SQL73.indb 48

era considerado lento pelo cliente, fica claro que o pior gargalo do sistema é causado pelo evento latch free (que será melhor explicado mais adiante), e em seguida o pior gargalo é de I/O. Para observar o SQL que está causando estas WAITs (troque o 758 abaixo pelo SID que pegou no SELECT anterior), use o SELECT demonstrado na Listagem 4. Os Wait Events da Listagem 5 são relacionados a I/O (discos rígidos) (Nota DevMan 2). Se estes Wait Events aparecerem Listagem 2. Comando SELECT para verificar os Wait Events ociosos. SQL> SELECT NAME 2 FROM V$EVENT_NAME 3 WHERE WAIT_CLASS = ‘Idle’ 4 ORDER BY NAME; NAME ---------------------------------------------------------------ASM background timer auto-sqltune: wait graph update class slave wait cmon timer DIAG idle wait dispatcher timer EMON slave idle wait fbar timer gcs remote message ges remote message HS message to agent i/o slave wait IORM Scheduler Slave Idle Wait jobq slave wait JOX Jit Process Sleep JS external job KSV master wait LNS ASYNC archive log LNS ASYNC dest activation LNS ASYNC end of log Logical Standby Apply Delay LogMiner: builder idle LogMiner: client waiting for transaction LogMiner: generic process sleep LogMiner: preparer idle LogMiner: reader idle LogMiner: reader waiting for more redo LogMiner: slave waiting for activate message LogMiner: waiting for processes to soft detach MRP redo arrival parallel recovery coordinator waits for slave cleanup parallel recovery slave idle wait parallel recovery slave next change PING pipe get PL/SQL lock timer pmon timer pool server timer PX Deq Credit: need buffer PX Deq Credit: send blkd PX Deq: Execute Reply PX Deq: Execution Msg PX Deq: Index Merge Close PX Deq: Index Merge Execute PX Deq: Index Merge Reply PX Deq: Join ACK PX Deq: kdcphc_ack PX Deq: kdcph_mai PX Deq: Msg Fragment PX Deq: Parse Reply PX Deq: Table Q Normal PX Deq: Table Q Sample PX Deq: Txn Recovery Reply PX Deq: Txn Recovery Start PX Deque wait PX Idle Wait rdbms ipc message SGA: MMAN sleep for component shrink shared server idle wait simulated log write delay single-task message smon timer Space Manager: slave idle wait SQL*Net message from client Streams AQ: deallocate messages from Streams Pool Streams AQ: delete acknowledged messages Streams AQ: emn coordinator idle wait Streams AQ: qmn coordinator idle wait Streams AQ: qmn slave idle wait Streams AQ: RAC qmn coordinator idle wait Streams AQ: waiting for messages in the queue Streams AQ: waiting for time management or cleanup tasks Streams capture: waiting for archive log Streams fetch slave: waiting for txns


Oracle

Cont.: Listagem 2. Comando SELECT para verificar os Wait Events ociosos

Nota do DevMan 2

Streams: waiting for messages VKTM Init Wait for GSGA VKTM Logical Idle Wait wait for unread message on broadcast channel wait for unread message on multiple broadcast channels watchdog main loop WCR: replay client notify WCR: replay clock WCR: replay paused

I/O I/O é uma sigla para Input/Output, em português E/S ou Entrada/Saída. Este termo é utilizado quase que exclusivamente no ramo da computação (ou informática), indicando entrada (inserção) de dados por meio de algum código ou programa, para algum outro programa ou hardware, bem como a sua saída (obtenção de dados) ou retorno de dados, como resultado de alguma operação de algum programa, consequentemente resultado de algum input.

83 linhas selecionadas.

muito na V$SESSION_WAIT, certamente há um problema global de I/O no banco de dados. Não temos espaço para explicar todos estes Wait Events, mas dois deles merecem atenção especial, pois são os que mais ocorrem: • db file scattered read - Um Full Table Scan (“Varredura Completa de Tabela”) está ocorrendo: se este evento aparecer repetidamente na V$SESSION_WAIT, pode significar um SELECT que teria um desempenho melhor com um índice apropriado; • db file sequential read - Leitura de índice: se este evento aparecer repetidamente na V$SESSION_WAIT para um mesmo SID, pode significar leituras desnecessárias de índice, como aquelas forçadas por um HINT, onde um Full Table Scan seria melhor (sim, isto acontece muito).

As interfaces de entrada e saída são responsáveis pela conexão entre as várias partes de um sistema computacional baseado na arquitetura de Von-Neumann. Esta interface é responsável por conectar fisicamente o processador e a memória do sistema ao barramento, tornando-se o terceiro elemento do sistema computacional proposto. Ao contrário do que se pode pensar, a interface de entrada e saída não é só o conector físico e sim também o responsável pela comunicação lógica entre o barramento e o dispositivo. Essa função de conexão foi basicamente desenvolvida para que seja possível a comunicação entre vários dispositivos, fazendo com que a velocidade do barramento seja mais bem aproveitada e ainda tanto os periféricos quanto os elementos essenciais tenham programação/produção mais voltada ao seu desempenho, deixando a interconexão com as interfaces de entrada e saída.

Estes dois Wait Events também podem ser minimizados facilmente, aumentando-se o Cache (Nota DevMan 3) (parâmetro DB_CACHE_SIZE), o que irá diminuir o acesso aos discos rígidos. Agora, para termos uma visão mais completa das causas de lentidão do banco de dados, iremos fazer SELECTs nas outras Listagem 3. Comando SELECT para verificar os Wait Events não ociosos das sessões atuais SQL> SELECT SID, EVENT, SECONDS_IN_WAIT 2 FROM V$SESSION_WAIT W 3 WHERE EVENT NOT IN (‘SQL*Net message from client’, 4 ‘SQL*Net message to client’, 5 ‘pmon timer’, ‘smon timer’, 6 ‘rdbms ipc message’, ‘jobq slave wait’, 7 ‘rdbms ipc reply’, ‘i/o slave wait’, 8 ‘PX Deq: Execution Msg’) 9 O RDER BY SECONDS_IN_WAIT DESC; SID ---------758 610 710 172 321 482 663 473 375 709 765 739 29 35 43 138 161 200 727 762 766 767 772 799 455 471 498 462

EVENT SECONDS_IN_WAIT --------------------------------------- --------------latch free 1 db file sequential read 1 latch free 0 buffer busy waits 0 buffer busy waits 0 buffer busy waits 0 buffer busy waits 0 buffer busy waits 0 buffer busy waits 0 buffer busy waits 0 buffer busy waits 0 buffer busy waits 0 db file sequential read 0 db file sequential read 0 db file sequential read 0 db file sequential read 0 db file sequential read 0 db file sequential read 0 db file sequential read 0 db file sequential read 0 db file sequential read 0 db file sequential read 0 db file sequential read 0 db file sequential read 0 db file sequential read 0 db file sequential read 0 db file sequential read 0 db file scattered read 0

548 604 655 669 677 722 152 48 266 213 223 400 405 458 352 366 374 387 408 447 668 715 504 516 580 607 221 333 322 262 257 241 232 228 175 167 658

db db db db db db db db db db db db db db db db db db db db db db db db db db db db db db db db db db db db db

file file file file file file file file file file file file file file file file file file file file file file file file file file file file file file file file file file file file file

scattered read scattered read scattered read scattered read scattered read scattered read scattered read sequential read scattered read scattered read scattered read scattered read scattered read scattered read sequential read sequential read sequential read sequential read sequential read sequential read sequential read sequential read sequential read sequential read sequential read sequential read sequential read sequential read sequential read sequential read sequential read sequential read sequential read sequential read sequential read sequential read sequential read

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

65 rows selected.


49

25/02/2010 14:31:00

Listagem 4. Comando SELECT para verificar SQL executado por uma sessão. SQL> SELECT A.SQL_TEXT 2 FROM V$SQLTEXT A, V$SESSION B 3 WHERE A.ADDRESS = B.SQL_ADDRESS 4 AND A.HASH_VALUE = B.SQL_HASH_VALUE 5 AND B.SID = 754 6 ORDER BY PIECE;

Views da OWI, como demonstrado na Listagem 6. Neste exemplo vemos que realmente a View V$SYSTEM_EVENT nos enganaria se fosse observada isoladamente, pois mostra como maior gargalo e evento undo segment extension, o que pode ter ocorrido apenas em um momento isolado da vida da instância. Já a View V$SESSION_EVENT confirma nosso diagnóstico anterior de que os maiores gargalos são causados por latches e I/O. Os exemplos anteriores foram executados em um ambiente de produção 9i. Vamos agora demonstrar na Listagem 7, como simular um problema de desempenho e diagnosticá-lo, em um ambiente de testes isolado, um 11gR1 em Windows. E o mais importante, iremos solucionar o problema. Após criar a tabela, irei inserir nomes de pessoas fictícias, extraídos de um arquivo texto, com cerca de 900.000 linhas. Na Listagem 8 está o script Perl (Nota DevMan 4) que faz estes INSERTs. Ele abre o arquivo texto, lê linha a linha, separando os campos (que estão limitados por tabulações), e usa o primeiro campo na instrução de INSERT. Perl é uma linguagem muito útil para administração de bancos de dados em geral, entre outras coisas. É mais fácil de programar do que Java ou C++, e muito mais poderosa do que Shell Script. Embora

Nota do DevMan 3 Cache Na área da computação, cache é um dispositivo de acesso rápido, interno a um sistema, que serve de intermediário entre um operador de um processo e o dispositivo de armazenamento ao qual esse operador acede. A vantagem principal na utilização de uma cache consiste em evitar o acesso ao dispositivo de armazenamento - que pode ser demorado -, armazenando os dados em meios de acesso mais rápidos.

Nota do DevMan 4 Perl Perl é uma linguagem de programação estável e multiplataforma, usada em aplicações de missão crítica em todos os setores, sendo destacado o seu uso no desenvolvimento de aplicações web.Permite a criação de programas em ambientes UNIX, MSDOS,Windows, Macintosh, OS/2 e outros sistemas operacionais.Além de ser muito utilizada para programação de formulários www e em tarefas administrativas de sistemas UNIX - onde a linguagem nasceu e se desenvolveu - possui funções muito eficientes para manipulação de textos. Perl é uma das linguagens preferidas por administradores de sistema e é especialmente versátil no processamento de cadeias (strings),manipulação de texto e no pattern matching implementado através de expressões regulares, além de ser bastante adequada para o desenvolvimento de projetos utilizando uma metodologia ágil. A linguagem Perl já foi portada para mais de 100 diferentes plataformas e é bastante usada em desenvolvimento web, finanças e bioinformática.

Listagem 5. Comandos SELECT para verificar os Wait Events de I/O SQL> SELECT NAME FROM V$EVENT_NAME WHERE WAIT_CLASS = ‘User I/O’ ORDER BY 1; NAME ---------------------------------------------------------------BFILE read buffer read retry cell multiblock physical read cell single block physical read cell smart file creation cell smart index scan cell smart table scan cell statistics gather Data file init write Datapump dump file I/O db file parallel read db file scattered read db file sequential read db file single write dbms_file_transfer I/O dbverify reads DG Broker configuration file I/O direct path read direct path read temp direct path sync direct path write direct path write temp external table misc IO external table read external table write local write wait Log file init write read by other session securefile direct-read completion securefile direct-write completion Shared IO Pool IO Completion

FROM V$EVENT_NAME WHERE WAIT_CLASS = ‘System I/O’ ORDER BY 1; NAME ---------------------------------------------------------------ARCH random i/o ARCH sequential i/o Archiver slave I/O cell smart incremental backup cell smart restore from backup control file parallel write control file sequential read control file single write db file parallel write DBWR slave I/O io done kfk: async disk IO ksfd: async disk IO LGWR random i/o LGWR sequential i/o LGWR slave I/O LNS ASYNC control file txn Log archive I/O log file parallel write log file sequential read log file single write Network file transfer recovery read RFS random i/o RFS sequential i/o RFS write RMAN backup & recovery I/O RMAN Disk slave I/O RMAN Tape slave I/O Standby redo I/O 30 linhas selecionadas.

31 linhas selecionadas. SQL> SELECT NAME

50 SQL73.indb 50


Oracle

Listagem 6. Comandos SELECT para verificar os maiores Wait Events das sessões atuais, e para verificar os Wait Events de toda a instância SQL> SELECT EVENT, SUM(TOTAL_TIMEOUTS) 2 FROM V$SESSION_EVENT 3 WHERE EVENT NOT IN (‘SQL*Net message from client’, 4 ‘SQL*Net message to client’, 5 ‘pmon timer’, ‘smon timer’, 6 ‘rdbms ipc message’, ‘jobq slave wait’, 7 ‘rdbms ipc reply’, ‘i/o slave wait’, 8 ‘PX Deq: Execution Msg’) 9 GROUP BY EVENT 10 ORDER BY 2 DESC; EVENT SUM(TOTAL_TIMEOUTS) ---------------------------------------------- ------------------latch free 73351 io done 10958 undo segment extension 2509 enqueue 570 log file switch completion 232 log file sync 51 buffer deadlock 28 LGWR wait for redo copy 11 log buffer space 3 HS message to agent 0 SQL*Net break/reset to client 0 SQL*Net more data from client 0 control file sequential read 0 db file parallel write 0 db file sequential read 0 direct path read 0 process startup 0 log file single write 0 log file sequential read 0 log file parallel write 0 library cache load lock 0 imm op 0 direct path write (lob) 0 direct path write 0 direct path read (lob) 0 wait list latch free 0 row cache lock 0 refresh controlfile command 0 db file single write 0 db file scattered read 0 db file parallel read 0 control file parallel write 0 SQL*Net more data to client 0 async disk IO 0 buffer busy waits 0 35 rows selected. SQL> SELECT EVENT, AVERAGE_WAIT, TOTAL_TIMEOUTS 2 F ROM V$SYSTEM_EVENT 3 W HERE EVENT NOT IN(‘SQL*Net message from client’, 4 ‘SQL*Net message to client’, 5 ‘pmon timer’, ‘smon timer’, 6 ‘rdbms ipc message’, ‘jobq slave wait’, 7 ‘rdbms ipc reply’, ‘i/o slave wait’, 8 ‘PX Deq: Execution Msg’) 9 O RDER BY TOTAL_TIMEOUTS DESC; EVENT AVERAGE_WAIT TOTAL_TIMEOUTS -------------------------------------- ------------ -------------undo segment extension 0 8707775 latch free 2 1250783 enqueue 164 240635 PX Deq Credit: send blkd 86 138120 PX Idle Wait 194 30457 PL/SQL lock timer 202 15139 io done 1 10958 PX Deq: Table Q Normal 2 6152 virtual circuit status 2918 3887

Listagem 7. Comandos para criar a tabela de testes. C:\Users\Ricardo>set ORACLE_SID=TEST11GR1 C:\Users\Ricardo>set ORACLE_HOME=c:\oracle\product\11.1.0\db_1 C:\Users\Ricardo>sqlplus SCOTT/TIGER SQL*Plus: Release 11.1.0.7.0 - Production on Seg Ago 31 08:32:51 2009 Copyright (c) 1982, 2008, Oracle. All rights reserved. Conectado a: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 Production With the Partitioning, OLAP, Data Mining and Real Application Testing option SQL> CREATE TABLE PESSOAS (NOM_PESSOA VARCHAR2(255)); Tabela criada. SQL> EXIT;

PX Deq Credit: need buffer dispatcher timer buffer deadlock log file switch completion PX Deq: Execute Reply log file sync log buffer space inactive session PX Deq: Signal ACK buffer busy waits slave TJ process wait index block split LGWR wait for redo copy local write wait kksfbc child completion reliable message control file heartbeat wait list latch free process startup async disk IO sbtinit sbtbackup direct path write (lob) direct path read (lob) direct path write direct path read db file parallel read db file parallel write db file single write db file scattered read db file sequential read HS message to agent SQL*Net break/reset to client SQL*Net more data from dblink SQL*Net message from dblink SQL*Net more data from client SQL*Net more data to dblink SQL*Net more data to client SQL*Net message to dblink single-task message PX Deq: Table Q Get Keys PX Deq: Parse Reply PX Deq: Msg Fragment PX Deq: Join ACK BFILE internal seek BFILE read BFILE open BFILE get length BFILE closure library cache load lock library cache pin row cache lock switch logfile command log file parallel write log file single write log file sequential read refresh controlfile command control file parallel write control file single write control file sequential read sbtclose2 sbtend sbtinfo2 sbtinit2 sbtremove2 sbtwrite2 imm op

9 5815 0 12 3 1 24 98 0 0 1 1 0 11 2 66 391 1 3 0 15 80 0 0 0 0 2 3 0 0 1 0 0 0 0 3 0 0 0 4 1 0 0 0 0 0 0 0 0 8 3 0 3 0 0 3 0 0 1 0 1181 0 24 0 6 3 0

2536 1943 1927 850 651 208 121 109 55 50 27 20 11 7 5 2 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

76 rows selected.

não seja o escopo deste artigo, recomendo fortemente os leitores a procurar mais sobre a Perl. Para utilizá-la no Windows, é possível baixar gratuitamente a suíte ActivePerl, da Active State. Na Listagem 9 demonstro como executar este script e qual a saída gerada. Com o script finalizado, irei verificar onde ele gastou mais tempo, como é demonstrado na Listagem 10. Não há segredo: o maior tempo médio deste caso é o log file switch completion. Consultando a documentação de Wait Events do RDBMS Oracle, vemos que este


51

25/02/2010 14:31:00

Listagem 8. Script Perl para inserir nomes de um arquivo texto no banco de dados

Listagem 10. Comandos verificar os maiores Wait Events causados pelos Inserts

use strict; use warnings; use DBD::Oracle;

C:\Users\Ricardo>sqlplus / AS SYSDBA

system(“time /T”); my $dbh = DBI->connect(“dbi:Oracle:host=localhost;sid=TEST11GR1;p ort=1523”, ‘SCOTT’, ‘TIGER’, {RaiseError => 1, AutoCommit => 0}); my $sth; open(PESSOAS, ‘prepare($sql); $sth->execute(); } $dbh->commit(); $sth->finish(); close PESSOAS; system(“time /T”); print ‘INSERTs finalizados. Pressione ENTER.\n’; my $go = <>; $dbh->disconnect(); exit;

Listagem 9. Comando para executar o Script Perl. C:\temp>perl Insert_Oracle.pl 10:34 10:44 INSERTs finalizados. Pressione ENTER.\n

evento ocorre quando o Oracle já utilizou todos os Redo Logs (Nota DevMan 5), da forma circular de sempre, e precisou usar o primeiro novamente, mas as informações que estavam nele ainda não foram gravadas nos datafiles (pelo DBWR), portanto, não podem ser sobrescristas, pois seriam necessárias em um eventual recover. Finalizado o diagnóstico, vamos ao ajuste. Para reduzir a ocorrência deste problema, já que o Oracle quer mais Redo Logs, daremos Redo Logs a ele, como é demonstrado na Listagem 11. Agora podemos executar o script novamente, como está na Listagem 12. Ora, o tempo continuou mesmo, mesmo reduzindo a maior Wait Event, como está na Listagem 13. A armadilha deste caso é que os grandes tempos médios dos Wait Events de recursos físicos (como log file sync, db file sequential read e SQL*Net message from client) esconde o péssimo tempo médio de um Wait Event lógico: latch: shared pool. Coisas lógicas devem ser mais rápidas que coisas físicas! Neste caso, 20 milissegundos para acessar uma estrutura de memória é muito tempo, muito mais grave do que 900 milissegundos para completar um switch. Novamente consultando a documentação dos Wait Events do RDBMS Oracle, vemos que este evento é causado por compilações excessivas de comandos SQL. Bom, vamos alterar o script para utilizar SQLs reutilizáveis, na versão Perl para as variáveis bind (que é a forma utilizada pelo Oracle para reutilizar comandos SQL similares), como é demonstrado na Listagem 14. E então podemos verificar o tempo resultante desta alteração na Listagem 15.

52 SQL73.indb 52

SQL*Plus: Release 11.1.0.7.0 - Production on Seg Ago 31 10:24:48 2009 Copyright (c) 1982, 2008, Oracle. All rights reserved. Conectado a: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SET PAGESIZE 1000 SQL> SET LINESIZE 210 SQL> SELECT SID FROM V$SESSION WHERE USERNAME = ‘SCOTT’; SID ———137 SQL> SELECT EVENT, AVERAGE_WAIT, TOTAL_TIMEOUTS FROM V$SESSION_EVENT WHERE SID = 137 ORDER BY AVERAGE_WAIT; EVENT ------------------------AVERAGE_WAIT TOTAL_TIMEOUTS ----------------------------- ------------ -------------latch: row cache objects 0 0 SQL*Net message to client 0 0 SQL*Net message from client ,02 0 latch: shared pool ,02 0 db file single write ,03 0 events in waitclass Other ,04 0 control file sequential read ,09 0 control file parallel write ,1 0 log file sync ,38 0 Data file init write ,39 0 db file sequential read ,5 0 db file scattered read 2,49 0 log file switch completion 4,84 0 13 linhas selecionadas.

O tempo caiu de 10 para 4 minutos, um resultado muito bom. Vamos ver como ficaram muito menores os Wait Events desta execução na Listagem 16.

Conclusão Neste artigo abordamos como diagnosticar e solucionar problemas de desempenho de forma precisa, utilizando a Oracle Wait Interface. Embora a OWI seja o único método que você precisa conhecer para executar um excelente diagnóstico, de forma alguma este artigo abordou todos os seus aspectos. Recomendo que você leia atentamente a documentação da própria Oracle a respeito. Algumas recomendações importantes baseadas em minha experiência que quero deixar aos leitores: • Observe os Wait Events de todo sistema ao qual tiver acesso, mesmo que este sistema tenha um bom desempenho. Sabendo como se comportam os Wait Events de um sistema com boa escalabilidade, você saberá identificar um sistema ruim quando se deparar com um, mesmo que o cliente não esteja reclamando dele; • Após o ajuste de desempenho, se o tempo do comando SQL ficou satisfatório, o processo de diagnóstico deve ser encerrado. Não caia na tentação de eliminar todos os Wait Events simplesmente porque você pode eliminá-los. Você não conseguirá eliminar todos os Wait Events, aceite isso. Faça o que for necessário para que o tempo de execução fique satisfatório para o cliente, e siga em frente.


Oracle

Nota do DevMan 5 Redo Logs No ambiente do Oracle, Redo logs são arquivos em um formato proprietário que registram um histórico de todas mudanças feitas ao banco de dados. Cada arquivo de Redo Log consiste de registros de Redo. Um registro de Redo, também chamado de entrada de Redo, possui um grupo de vetores de mudanças, e cada um descreve ou representa uma mudança feita a um único bloco no banco de dados. Por exemplo, se um usuário atualiza (UPDATE) um valor de salário em uma tabela contendo dados referentes a empregados, o SGBD gera um registro de Redo contendo vetores de mudanças que descrevem as alterações ao bloco do segmento de dados da tabela. E se o usuário confirma então a alteração (COMMIT), o Oracle gera outro registro de Redo e lhe atribui um System Change Number (“Número de Alteração de Sistema”), ou SCN.

Listagem 11. Comandos para adicionar Redo Logs SQL> COL MEMBER FORMAT A70 SQL> SELECT * FROM V$LOGFILE; GROUP# STATUS TYPE MEMBER IS_ ------ ------ ------ -------------------------------------- --3 ONLINE C:\ORACLE\ORADATA\TEST11GR1\REDO03.LOG NO 2 ONLINE C:\ORACLE\ORADATA\TEST11GR1\REDO02.LOG NO 1 ONLINE C:\ORACLE\ORADATA\TEST11GR1\REDO01.LOG NO SQL> ALTER DATABASE ADD LOGFILE ‘C:\ORACLE\ORADATA\TEST11GR1\REDO04.LOG’ SIZE 1G; Banco de dados alterado. SQL> ALTER DATABASE ADD LOGFILE ‘C:\ORACLE\ORADATA\TEST11GR1\REDO05.LOG’ SIZE 1G; Banco de dados alterado. SQL> ALTER DATABASE ADD LOGFILE ‘C:\ORACLE\ORADATA\TEST11GR1\REDO06.LOG’ SIZE 1G;

Listagem 14. Script Perl para inserir nomes de um arquivo texto no banco de dados utilizando variáveis Bind use strict; use warnings; use DBD::Oracle; system(”time /T”); my $dbh = DBI->connect(”dbi:Oracle:host=localhost;sid=TEST11GR1 ;port=1523?, ‘SCOTT’, ‘TIGER’, {RaiseError => 1, AutoCommit => 0}); my $sth; open(PESSOAS, ‘prepare($sql); $sth->execute($pessoa[0]); } $dbh->commit(); $sth->finish(); close PESSOAS; system(“time /T”); print ‘INSERTs finalizados. Pressione ENTER.\n’; my $go = <>; $dbh->disconnect(); exit;

Listagem 15. Comando para executar o Script Perl. C:\temp>perl Insert_Oracle.pl 11:30 11:34 INSERTs finalizados. Pressione ENTER.\n

Listagem 16. Comandos verificar os maiores Wait Events causados pelos Inserts SQL> SELECT EVENT, AVERAGE_WAIT, TOTAL_TIMEOUTS 2 FROM V$SESSION_EVENT 3 W HERE SID = 142 4 O RDER BY AVERAGE_WAIT; EVENT AVERAGE_WAIT TOTAL_TIMEOUTS --------------------------- ------------ -------------latch: cache buffers chains 0 0 SQL*Net message to client 0 0 events in waitclass Other ,01 0 SQL*Net message from client ,02 0 log file sync ,64 0

Banco de dados alterado.

Listagem 12. Comando para executar o Script Perl C:\temp>perl Insert_Oracle.pl 11:13 11:23 INSERTs finalizados. Pressione ENTER.\n

Se tiverem dúvidas quanto a este artigo de Oracle Diagnostics Performance & Tuning, ou qualquer outro assunto, não hesitem em me escrever.

Listagem 13. Comandos verificar os maiores Wait Events causados pelos Inserts

8 linhas selecionadas.

Wikipedia http://www.wikipedia.org/


Dê s

Oracle On-Line Documentation http://tahiti.oracle.com/



EVENT AVERAGE_WAIT TOTAL_TIMEOUTS ---------------------------- ------------ -------------latch: cache buffers chains 0 0 latch: row cache objects 0 0 SQL*Net message to client 0 0 events in waitclass Other 0 0 latch: shared pool ,02 0 SQL*Net message from client ,02 0 db file sequential read ,57 0 log file sync ,92 0

Referências

edição ta

SQL> SELECT EVENT, AVERAGE_WAIT, TOTAL_TIMEOUTS 2 FROM V$SESSION_EVENT 3 WHERE SID = 142 4 ORDER BY AVERAGE_WAIT;

53

25/02/2010 14:31:01




Usando CDC e Trigger em Auditorias no SQL Server 2008 Conhecendo o recurso Change Data Capture De que se trata o artigo?

S Tulio Rosa [email protected]

Bacharel em Sistemas de Informação com certificações em MS SQL Server, trabalha exclusivamente com banco de dados desde 2001, com conhecimentos das versões 6.5, 7, 2000, 2005 e 2008 do MS SQL Server, experiência na administração de banco de dados em empresas de grande porte com ambiente de alta disponibilidade, experiência em administração de dados em fabrica de software e atualmente trabalha como DBA da Politec S/A e da Praxe Tecnologia. http://tuliorosa.blogspot.com/

54 SQL73.indb 54

erá abordado nesse artigo o Change Data Capture (CDC), recurso que permite o registro de todas as modificações ocorridas nos dados das tabelas. Será demonstrada também a utilização de trigger para o registro das alterações nos dados das tabelas. O CDC foi implementado no SQL Server com o objetivo de identificar os dados que sofreram alterações. Sua principal utilização é no processo de ETL (Extração Transformação e Carga), processo responsável por efetuar a carga de dados em DW (Data Warehouse). A sintaxe utilizada nos exemplos de Trigger é do SQL Server, mas a lógica da utilização de Trigger para armazenar informações sobre alterações nas tabelas pode ser utilizada em outros bancos de dados. Nesse artigo vamos apresentar dois exemplos, o primeiro utilizando trigger, e o segundo, o recurso CDC.

Nesse artigo será demonstrado o recurso Change Data Capture (CDC) presente no MS SQL Server 2008 e como utilizar Trigger para registrar as alterações ocorridas nas tabelas. Abordaremos a melhor forma de utilizá-los em auditorias no banco de dados.

Para que serve? Para identificar as alterações ocorridas nas tabelas, fornecendo condições de comparação e recuperação de dados que foram excluídos e/ou alterados. Possibilita identificar também a inclusão de novos registros nas tabelas.

Em que situação o tema é útil? Quando se deseja armazenar históricos das alterações ocorridas nos dados das tabelas. Esse recurso permite o registro das atividades ocorridas nas tabelas das bases de dados, fornecendo informações importantes para uma auditoria.

Exemplo usando Trigger O SQL Server tem três tipos de trigger: DML, DDL e de Logon. Utilizaremos as triggers DML, que serão disparadas

SQL Magazine - Usando CDC e Trigger em Auditorias no SQL Server 2008 25/02/2010 14:31:04

sql server

quando ocorrer a execução de instruções DML (insert, delete ou update) nas tabelas em que elas estiverem vinculadas. Em nosso exemplo criaremos uma base de dados chamada Teste, uma tabela na qual conterá os dados de exemplo, que chamaremos de Cliente, uma tabela para registrar as alterações, que chamaremos de Cliente_Log, e três triggers que serão responsáveis por atualizar a tabela Cliente_Log sempre que ocorrer alterações nos dados da tabela Cliente (Figura 1).

Cliente

tr_cliente_update tr_cliente_delete tr_cliente_insert

Cliente_Log

Figura 1. Exemplo usando Trigger

Para criar o ambiente necessário para o exemplo, abra o Microsoft SQL Server Management Studio (SSMS) e efetue o logon no SQL Server. Feito isso, teremos uma tela semelhante a da Figura 2.

Figura 2. Tela do SSMS

Criaremos inicialmente uma base de dados chamada Teste e duas tabelas chamadas Cliente e Cliente_Log (Listagem 1). A tabela Cliente é a nossa tabela principal, Cliente_Log é uma tabela que contém todos os campos da tabela principal, mais quatro campos adicionados no final. Esses campos serão utilizados para guardar informações sobre as operações ocorridas na tabela principal, são eles: • Log Usuário – contém o usuário que estava conectado na instância do SQL Server quando ocorreu a operação; • Log Data – data e hora que ocorreu a operação; • Log Operação – tipo da operação que ocorreu, podendo ser Insert, Delete ou Update; • Log ID – identificador sequencial. Após a criação das tabelas, vamos criar três triggers, uma para cada operação (insert, update e delete), apresentadas na Listagem 2.

Listagem 1. Código de criação da base de dados e das tabelas. use master go -- Criar a base de dados create database Teste go use Teste go -- Criar a tabela Cliente CREATE TABLE dbo.Cliente ( ID_Cliente int NOT NULL IDENTITY (1, 1), Nome varchar(50) NOT NULL, Idade int NOT NULL ) GO ALTER TABLE dbo.Cliente -- Adiciona ID_Cliente como chave primaria ADD CONSTRAINT PK_Cliente_1 PRIMARY KEY CLUSTERED (ID_Cliente) GO -- Criar a tabela Cliente_Log CREATE TABLE dbo.Cliente_Log ( ID_Cliente int NOT NULL, Nome varchar(50) NOT NULL, Idade int NOT NULL, Log_Usuario varchar(30) NOT NULL, Log_Data datetime NOT NULL, Log_Operacao char(1) NOT NULL, Log_ID int NOT NULL IDENTITY (1, 1) ) GO ALTER TABLE dbo.Cliente_Log -- Adiciona LOG_ID como chave primaria ADD CONSTRAINT PK_Cliente_Log_1 PRIMARY KEY CLUSTERED (LOG_ID) GO

Observando o código da Listagem 2, notamos que as diferenças entre cada SP é mínima, são as instruções indicando quando serão disparadas (after insert, after delete ou after update), a letra indicando qual o tipo de operação (I, U ou D), e a origem das informações (tabela inserted ou deleted). Como já informamos, elas serão disparadas quando operações de insert, update e delete são efetuadas na tabela Cliente. A instrução after delete presente na trigger tr_d_Cliente_Log indica que essa trigger executará a instrução SQL quando uma operação de delete for executada na tabela Cliente. Dessa mesma forma, as outras triggers serão disparadas quando as demais operações forem executadas, de acordo com a instrução presente em cada uma (after insert e after update). Listagem 2. Código de criação de trigger. USE [Teste] GO CREATE trigger [dbo].[tr_d_Cliente_Log] ON [dbo].[Cliente] after delete as insert Cliente_Log select *,system_user,getdate(),’D’ from deleted GO CREATE trigger [dbo].[tr_i_Cliente_Log] ON [dbo].[Cliente] after insert as insert Cliente_Log select *,system_user,getdate(),’I’ from inserted GO CREATE trigger [dbo].[tr_u_Cliente_Log] ON [dbo].[Cliente] after update as insert Cliente_Log select *,system_user,getdate(),’U’ from deleted GO


55

25/02/2010 14:31:04

As instruções SQL presentes nas triggers, quando executadas, inserem registros na tabela Cliente_Log fazendo um Select na tabela inserted ou deleted. Essas tabelas são utilizadas internamente pelo SQL Server para fazer o controle das operações DML, e podem ser referenciadas nas triggers. A tabela inserted contém os registros que estão sendo inseridos na tabela, enquanto deleted armazena os registros que estão sendo apagados. Analisando a instrução Select da trigger tr_d_Cliente_Log, verificamos que está sendo feita uma consulta nas informações que estavam na tabela Cliente antes de serem apagadas, utilizando a tabela interna deleted. Posteriormente é executada a instrução Insert que insere na tabela Cliente_Log as informações retornadas da consulta da tabela interna deleted. As instruções presentes nas triggers fazem uso das funções getdate() para obter a data e hora atual, e da system_user para obter o usuário que está conectado na instância do SQL Server. Para testar a trigger de insert (tr_i_Cliente_Log), vamos inserir três registros na tabela Cliente, conforme a Listagem 3. A Figura 3 mostra os registros nas tabelas após a inserção dos três registros na tabela Cliente. Nota-se na tabela Cliente_Log que os campos Log_Usuario, Log_Data e Log_Operação foram preenchidos pela trigger. Esses campos contêm informações importantes sobre o que ocorreu na tabela, podendo contribuir significativamente em um processo de auditoria no banco de dados.

Figura 3. Conteúdo das tabelas após a inserção de três registros Listagem 3. Inserir três registros na tabela Cliente. USE Teste go -- Mostra o conteudo das tabelas antes do Insert select * from Cliente select * from Cliente_Log -- Insere 3 registros insert Cliente (Nome, Idade) values (‘Tulio’,35) insert Cliente (Nome, Idade) values (‘João’,25) insert Cliente (Nome, Idade) values (‘Maria’,15) -- Mostra o conteudo das tabelas depois do Insert select * from Cliente select * from Cliente_Log

Para exemplificar as operações de exclusão e alteração de registros, execute o código da Listagem 4. A Figura 4 mostra os registros nas tabelas após a exclusão e alteração dos registros na tabela Cliente. Observando o campo Log_Operação da tabela Cliente_Log, temos a informação do tipo de operação que ocorreu quando foi

56 SQL73.indb 56

Listagem 4 . Excluir e alterar registros na tabela Cliente USE Teste go -- Mostra o conteudo das tabelas antes do Delete e Update select * from Cliente select * from Cliente_Log -- Excluie um registro delete Cliente where ID_Cliente = 2 -- Altera um registro update Cliente set Nome = ‘Fulana’ where ID_Cliente = 3 -- Mostra o conteudo das tabelas depois do Delete e Update select * from Cliente select * from Cliente_Log

Figura 4. Conteúdo das tabelas após a exclusão e alteração de registros

inserido o registro, sendo “I” para inserção, “D” para exclusão e “U” para atualização. Note que a tabela Cliente_Log contém todos os registros que estavam antes das operações de exclusão e atualização na tabela Cliente. Por exemplo, na tabela Cliente temos o nome “Fulana”, se olharmos na tabela Cliente_Log, consta o nome “Maria”, ou seja, o registro antes da operação de atualização. Vale ressaltar que quando utilizamos triggers para inserir informações em tabelas estamos utilizando mais recursos do servidor, isto é, uma operação que iria inserir um registro na base de dados, nesse caso, passa a inserir dois registros. Por isso, é importante fazer uma análise de quais tabelas são realmente importantes e necessitam de uma tabela de Log.

Exemplo usando o CDC O Change Data Capture (CDC) utiliza os log (arquivos .ldf) para identificar as alterações ocorridas nas tabelas. Quando uma operação DML é executada, essas informações são salvas primeiramente nos arquivos de log e posteriormente na tabela de origem. Através de um processo de captura, as informações sobre as alterações ocorridas na tabela que estão nos arquivos de log são lidas e salvas também em tabelas de “histórico do CDC” (Figura 5). Em nosso exemplo sobre o CDC, iremos criar uma base chamada TesteCDC, adicionar uma tabela chamada Fornecedor, habilitar a base para utilização do CDC e incluir a tabela Fornecedor no processo de CDC. Para criar o ambiente necessário para o exemplo, abra o SQL Server Management Studio e efetue o logon no SQL Server. Feito isso, teremos uma tela semelhante a da Figura 2. Agora criaremos uma base chamada TesteCDC e a tabela Fornecedor, como apresenta o código da Listagem 5.


sql server

Figura 6. Resultado da verificação se o CDC está habilitado Listagem 7. Verificação se o CDC esta habilitado. use master go select name, i s_cdc_enabled from sys.databases where is_cdc_enabled = 1

Figura 5. Funcionamento do CDC Listagem 5. Código da criação da base e tabela. use master go -- Criar a base de dados create database TesteCDC go use TesteCDC go -- Criar a tabela Fornecedor CREATE TABLE dbo.Fornecedor ( ID_Fornecedor int NOT NULL IDENTITY (1, 1), Nome varchar(50) NOT NULL, Telefone int NOT NULL ) GO ALTER TABLE dbo.Fornecedor -- Adiciona chave primaria ADD CONSTRAINT PK_Fornecedor_1 PRIMARY KEY CLUSTERED (ID_ Fornecedor) GO

Após a criação da base de dados e da tabela, usaremos a stored procedure sp_cdc_enable_db para habilitar a base de dados para utilizar o CDC, e a SP sp_cdc_enable_table para incluir a tabela no processo CDC (Listagem 6). Para verificar quais bases de dados estão com o recurso CDC habilitado, podemos fazer uma consulta na tabela sys. Database, buscando pelo campo is_cdc_enabled igual a 1. Para verificar quais tabelas estão incluídas no CDC, devemos consultar a tabela sys.Tables, buscando pelo campo is_tracked_by_cdc igual a 1. A Listagem 7 mostra as duas consultas, tendo o resultado mostrado na Figura 6. Listagem 6. Código para habilitar o CDC e incluir a tabela. use TesteCDC go -- Habilitando a base de dados exec sys.sp_cdc_enable_db go -- Adicionando a tabela exec sp_cdc_enable_table @ source_schema = ‘dbo’, @ source_name = ‘Fornecedor’, @ role_name = ‘CDC_Role’, @ supports_net_changes = 1 Go

-- Tabela use TesteCDC go select name, i s_tracked_by_cdc from sys.tables where is_tracked_by_cdc = 1

Quando executamos a SP sp_cdc_enable_db para habilitar a base de dados para utilização do CDC, são criados os seguintes objetos no SQL Server: um Schema chamado cdc, um usuário chamado cdc e cinco tabelas de sistemas que conterão informações sobre o CDC (Figura 7). Quando executamos a SP sp_cdc_enable_table para incluir a tabela no CDC, é criada uma nova tabela de sistema com o nome da tabela de origem contendo no final a identificação _CT. Essa tabela contém todos os campos da tabela de origem mais cinco campos de controle, conforme a Figura 8. Em nosso exemplo também será criada, se não existir, uma Role chamada CDC_Role, que será responsável por permitir o acesso às informações nas tabelas _CT quando o usuário não tiver permissão de db_owner. Ao executar a SP sp_cdc_enable_table pela primeira vez, serão criados dois JOBs (Figura 9). No nosso exemplo, o primeiro é chamado cdc_dbo_TesteCDC_capture, o qual será iniciado junto com a instância do SQL Server e será responsável pela leitura das informações sobre alterações que foram registradas nos arquivos de log. As alterações identificadas serão salvas na tabela de sistema que foi criada com a identificação _CT. O segundo JOB criado é o cdc_TesteCDC_cleanup, responsável por apagar as informações antigas das tabelas _CT. Por default, esse JOB é executado diariamente às 2:00 hs, e a cada execução são apagados os registros das tabelas _CT que tenham mais de 72 horas (antigos). Caso seja necessário manter por mais tempo as informações nas tabelas _CT, podemos alterar o tempo de retenção utilizando a SP sp_cdc_change_job. O tempo máximo permitido é de 100 anos, o valor para o parâmetro da SP chamado retention tem que ser informado em minutos. A Listagem 8 mostra a alteração para 100 anos. Uma boa prática é manter um período pequeno (um mês,


57

25/02/2010 14:31:05

dependendo da quantidade de transações) e criar uma rotina para descarte dos registros antigos, salvando-os em outro SQL Server ou em arquivos texto (.txt), para o caso de ser necessária a consulta dessas informações no futuro. Após habilitar a base de dados para utilizar o CDC e incluir a tabela de exemplo Fornecedor, iremos inserir três registros nesta tabela e depois selecionar os registros nas tabelas Fornecedor e cdc.dbo_Fornecedor_TC para mostrar o que ocorreu (veja o código na Listagem 9). A Figura 10 mostra o resultado obtido após a execução do código da Listagem 9.

Figura 9. JOB criado quando incluído a tabela Listagem 8. Alterando o tempo de retenção use TesteCDC go exec sp_cdc_change_job @job_type=’cleanup’, @retention=52494800 go

Listagem 9. Operações na tabela de fornecedor. USE TesteCDC go -- Mostra o conteudo das tabelas antes do Insert select * from Fornecedor select * from cdc.dbo_Fornecedor_CT -- Insere 3 registros insert Fornecedor(Nome, Telefone) values (‘Tulio’,1111) insert Fornecedor (Nome, Telefone) values (‘João’,2222) insert Fornecedor (Nome, Telefone) values (‘Maria’,3333) -- Mostra o conteudo das tabelas depois do Insert -- Esperar 5 segundos antes de executar essa parte select * from Fornecedor select * from cdc.dbo_Fornecedor_CT

Figura 7. Objetos criados quando habilitado o CDC

Para realizar consultas nas tabelas de sistemas terminadas em _CT podemos também utilizar a função (FN) cdc. fn_get_net_changes_dbo_Fornecedor. Essa função é criada no momento em que adicionamos a tabela Fornecedor no CDC. A Listagem 10 demonstra a utilização da FN. Listagem 10. Usando a FN cdc.fn_get_net_changes_dbo_Fornecedor. use TesteCDC go declare @from_lsn binary(10), @to_lsn binary(10) set @from_lsn = sys.fn_cdc_get_min_lsn(‘dbo_Fornecedor’) -- Menor data set @to_lsn = sys.fn_cdc_get_max_lsn() -- Maior data select * from cdc.fn_cdc_get_net_changes_dbo_Fornecedor(@from_lsn, @to_lsn, ‘all’);

Figura 8. Tabela criada pela SP sp_cdc_enable_table

58 SQL73.indb 58

Se observarmos a Figura 10, veremos que o primeiro resultado (tabela Fornecedor) retornou os registros que foram inseridos. O segundo resultado retornou o mesmo conteúdo da tabela Fornecedor mais algumas informações adicionais. Dentre estas informações, as mais importantes são __$start_ lsn, que contém a data e a hora que a operação foi executada, e __ $operation, que contém o tipo de operação.


sql server

Para visualizar a data que está no campo __ $start_lsn podemos fazer uso da função sys.fn_cdc_map_lsn_to_time. A Tabela 1 mostra o significado dos códigos do campo __$operation. Código

Descrição

1

Exclusão

2

Inserção

3

Antes da alteração

4

Após a alteração

Tabela 1. Códigos do campo __$operation

Para entender melhor o exemplo, vamos alterar um registro e excluir outro (veja a Listagem 11). Na Figura 11 apresentamos os resultados após a alteração e exclusão dos registros. O primeiro resultado mostra a tabela Fornecedor, que contém agora dois registros. Para saber o que ocorreu nessa tabela, recorremos à tabela cdc.dbo_Fornecedor_CT. Observando cdc.dbo_Fornecedor_CT, temos nos três primeiros registros a informação de inclusão, pois no campo __ $operation está presente o código 2. O conteúdo inserido é o que consta nos últimos campos desses registros (ID_Fornecedor, Nome e Telefone). O quarto e quinto registro da tabela cdc.dbo_Fornecedor_CT contém informações sobre as alterações ocorridas

na tabela Fornecedor. O campo __ $operation com código 3 informa que o registro contém os dados que existiam na tabela Fornecedor antes da alteração, e no quinto registro (__$operation = 4) os dados após a alteração. O CDC não registra o usuário que executou a instrução DML na tabela. A Listagem 12 mostra como fazer para adicionar o campo Estado na tabela Fornecedor.

Listagem 11. Alteração e exclusão de registros. USE TesteCDC go -- Alteracao update dbo.Fornecedor set Nome = ‘Tulio Rosa’ where ID_ Fornecedor = 1 -- Exclusao delete dbo.Fornecedor where ID_Fornecedor = 2 -- Mostra o conteudo das tabelas depois da alteracao e exclusao -- Esperar 5 segundos antes de executar essa parte select * from Fornecedor select * from cdc.dbo_Fornecedor_CT

Listagem 12 . Adicionando o campo Estado. use TesteCDC go -- Adicionando o campo estado alter table dbo.Fornecedor add Estado char(2) GO

Figura 10. Conteúdo das tabelas Fornecedor e dbo_Fornecedor_CT

Figura 11. Resultado após a alteração e exclusão dos registros


59

25/02/2010 14:31:06

Como foi habilitado o CDC na tabela Fornecedor, ao adicionar um novo campo as informações referentes a alterações (instruções DDL) são armazenadas na tabela cdc.ddl_history. A Listagem 13 mostra como efetuar a consulta nessa tabela, e o resultado pode ser conferido na Figura 12. Listagem 13 . Consulta nas tabelas após adicionar o novo campo. use TesteCDC go select * select *

from Fornecedor from cdc.dbo_Fornecedor_CT

select from

OBJECT_NAME(source_object_id) as ‘Tabela’, O BJECT_NAME(OBJECT_ID) as ‘Tabela_CT’, d dl_command as ‘DDL’, d dl_time as ‘Data’ cdc.ddl_history

um registro e alterá-lo, de acordo com a Listagem 15. O novo resultado pode ser conferido na Figura 13. Listagem 14 . Retira e adiciona novamente a tabela Fornecedor. use TesteCDC go -- Retirando a tabela exec sp_cdc_disable_table @ source_schema = ‘dbo’, @ source_name = ‘Fornecedor’, @ capture_instance = ‘all’ go -- Adicionando a tabela exec sp_cdc_enable_table @ source_schema = ‘dbo’, @ source_name = ‘Fornecedor’, @ role_name = ‘CDC_Role’, @ supports_net_changes = 1 go

Listagem 15. Efetuando alterações no novo campo.

Como pode ser verificado na Figura 12, o novo campo foi adicionado na tabela Fornecedor, mas não foi adicionado na tabela Fornecedor_CT. Para resolver esse problema a forma mais simples é retirar e adicionar novamente a tabela Fornecedor no CDC, conforme a Listagem 14. É importante observar que quando retiramos uma tabela do CDC, a tabela corespondente _CT é apagada, perdendo todo o seu conteúdo. Então, se existe a intenção de manter os dados das tabelas _CT, os registros dessas tabelas precisam ser copiados para outra tabela antes da operação de retirar a tabela do CDC. Para verificar a utilização do novo campo, iremos inserir

use TesteCDC go -- Inserindo Insert Fornecedor(Nome, Telefone,Estado) values (‘Fabiola’,5555,’GO’) -- Alteracao update dbo.Fornecedor set Estado = ‘DF’ where ID_Fornecedor = 3

Para melhorar a visualização do histórico (tabelas _CT), podemos criar uma stored procedure para formatar o resultado destas tabelas. A Listagem 16 mostra o código de criação da SP. Ela recebe como parâmetros o nome da tabela, a data inicial

Figura 12. Resultado das consultas após adicionar o novo campo

Figura 13. Resultado da alteração do novo campo

60 SQL73.indb 60


sql server

Figura 14. Resultado da SP cdc.s_historico

create proc cdc.s_historico @tabela varchar(100), @data_inicial varchar(23), @data_final varchar(23) as exec (‘ select s ys.fn_cdc_map_lsn_to_time(__$start_lsn) as Data, case __$operation w hen 1 then ‘’D’’ w hen 2 then ‘’I’’ w hen 3 then ‘’UA’’ w hen 4 then ‘’UD’’ e nd as Operacao, * into #temp from c dc.’ + @tabela + ‘ where s ys.fn_cdc_map_lsn_to_time(__$start_lsn) between ‘’’ + @data_inicial + ‘’’ and ‘’’ + @data_final + ‘’’ alter table #temp drop column __$start_lsn alter table #temp drop column __$operation alter table #temp drop column __$update_mask alter table #temp drop column __$seqval alter table #temp drop column __$end_lsn select * from # temp order by Data ‘)

Listagem 17. Utilização da SP cdc.s_historico. use TesteCDC go exec cdc.s_historico ‘dbo_Fornecedor_CT’,’2009-11-01’,’2009-11-30’ go

A Tabela 2 mostra o significado dos códigos do campo Operação, da Figura 14. Operação

Descrição

I

Inserido

D

Apagado

UA

Dados antes da alteração

UD

Dados após a alteração

Tabela 2. Códigos do campo Operação

Considerações sobre Trigger e CDC

Demonstramos nesse artigo duas formas de armazenar informações sobre as operações (insert, update e delete) que ocorrem nas tabelas, através de Triggers e CDC. Podemos utilizar essas informações para entender o que ocorreu na tabela e quando, o que é muito útil em um processo de auditoria. Imagine o seguinte cenário: um funcionário da empresa que tenha acesso ao sistema de contas a pagar e receber altera de forma fraudulenta o valor que a empresa tem a receber de um determinado cliente. Através da utilização dos recursos demonstrados nesse artigo é possível identificar quando foi feita a alteração, por quem e ainda teremos condições de corrigir o valor alterado, voltando ao valor inicial. Para concluir, vale destacar que a utilização dos recursos de triggers ou do CDC aumentarão o consumo de recursos do servidor onde está instalado a instância do SQL Server. Por isso, tenha cuidado ao utilizar estes recursos e evite que o desempenho do banco seja prejudicado. Links SQL Server Developer Center http://msdn.microsoft.com/en-us/library/cc645937.aspx http://msdn.microsoft.com/en-us/library/ms189799.aspx




Como podemos observar, tanto a utilização dos recursos de Triggers como CDC inserem registros em tabelas na base de dados, e com isso temos o aumento de utilização de recursos do servidor onde está instalado a instância do SQL Server. A utilização do CDC é mais vantajosa que as Triggers devido ao fato das atualizações serem buscadas dos arquivos de log,

Conclusão

Dê s

Listagem 16. Criação da SP para retornar o resultado formatado.

de forma assíncrona, enquanto as Triggers fazem a inclusão dos registros em todas as tabelas de forma síncrona. Outra vantagem do CDC é que podemos pará-lo em momentos críticos, isto é, onde esteja ocorrendo um grande numero de transações (atividade intensa no banco de dados), e voltar a atualizar as tabelas _CT quando a carga no servidor estiver mais baixa, sem perda de informações. Para suspender temporariamente o CDC, utilizamos a SP sys.sp_ cdc_stop_job, e para voltar a utilizar o CDC, utilizamos a SP sys. cdc_start_job. É importante saber que o arquivo de log aumentará enquanto o serviço estiver suspenso, uma vez que as informações sobre as alterações nos dados das tabelas são salvas nos arquivos de log, e ficam disponíveis até o CDC fazer a leitura. Após voltar a utilizar o CDC as informações que estavam nos arquivos de log são liberadas para serem descartadas ou incluídas em backup.

edição ta

e a data final. Estas datas são referentes ao período em que se deseja visualizar as alterações. A Listagem 17 mostra a sua utilização. Observando o resultado na Figura 14, temos a data em que ocorreu a operação, o tipo da operação e os campos do registro da tabela de origem.

61

25/02/2010 14:31:06




Desafio SQL

De que se trata o artigo?

E a empresa ItsMyBusiness continua crescendo. E seu

Desenvolvimento de soluções para problemas cotidianos enfrentados por DBAs e desenvolvedores de aplicações para banco dados.

sistema de vendas on-line também está crescendo e em larga escala. Novos processos e novas implementações tem sido necessárias para acompanhar a evolução dos negócios e resolver os problemas que não foram previstos no momento do projeto. E justamente neste momento surge a velha discussão de onde a regra de negócio deve estar: camada de aplicação, camada intermediária ou diretamente no banco de dados. Wagner Crivelin, como bom DBA, é partidário de manter a regra de negócios diretamente no banco de dados e, com esta filosofia, vem nos brindar com mais um desafio. E caso você tenha alguma dúvida, sugestão de tema ou até mesmo um novo desafio, envie um e-mail para mim. Aproveite. Ricardo Rezende - Editor Técnico [email protected]

Wagner Crivelini [email protected]

Atua a mais de 15 anos na área TI, particularmente com Business Intelligence. Engenheiro formado pela UNICAMP, trabalha na IBM na unidade de IBM Global Account. Profissional com certificação em DB2 (IBM Certified Solution Designer DB2 Business Intelligence). Colunista convidado do portal internacional www.SQLServerCentral.com . Atualmente ocupa a posição de DBA DB2.

62 SQL73.indb 62

Para que serve?

E

voltamos para acompanhar mais um capítulo da história da empresa fictícia ItsMyBusiness (leia nota DevMan 1). A empresa está realizando um projeto para melhorar o controle sobre os pedidos recebidos, já definiu o modelo de dados e o fluxo de operação, que são exibidos nas Figuras 1 e 2. A Tabela 1 mostra os códigos de status que consideramos neste fluxo e uma breve descrição de cada um. O script para criação desta base de dados está disponível no portal da SQL Magazine. Este script adota um padrão SQL, de tal modo que ele roda, com pequenas alterações, em SQL SERVER, DB2, ORACLE, FIREBIRD e POSTGRES. As alterações necessárias para cada SGBD são apresentadas como comentários.

Fornecer conceitos de utilização de funcionalidades do padrão SQL ANSI na resolução de problemas enfrentados no dia-a-dia na recuperação de informações do banco de dados.

Em que situação o tema é útil? Criação de procedimentos armazenados, gatilhos e agendamentos (ou stored procedures, triggers e jobs).

Feitas as preliminares, agora falta trabalhar numa outra parte do projeto: como sincronizar os eventos ocorridos com as mudanças de status do pedido. Sempre existiu e sempre existirá o debate sobre onde que esta lógica de negócios deve ser implementada. Cada tribo defende o seu ponto de vista: uns defendem que a lógica deve ficar na aplicação, outros dizem que ela deve residir dentro do próprio banco de

SQL Magazine - Desafio SQL 25/02/2010 14:31:07

D ESAFIO S QL

dados e existem também aqueles que gostam de tratar tudo através de serviços intermediários (web, windows, etc). É claro que eu (e provavelmente você também) faço parte de uma destas tribos e também vou adotar um dos “partidos”. O desafio de hoje é implementar toda lógica de atualização dos status do pedido usando apenas recursos do banco de dados, tomando por base as informações apresentadas: • no diagrama do modelo do banco (Figura 1) • no diagrama do fluxo de trabalho (Figura 2) • e na lista de códigos de status considerados (Tabela 1). Ou seja, podemos escolher usar procedimentos armazenados, funções e gatilhos, conforme a necessidade. Boa sorte.

codPedidoStatus

Descrição do status

1

pedido registrado no site

2

cartão de crédito validado

3

pedido aceito

4

item do pedido disponível em estoque

5

nota fiscal emitida

6

remessa do pedido para transportadora

7

pedido entregue

8

pedido finalizado

102

cartão de crédito rejeitado

103

pedido recusado

Tabela 1. Códigos das ações relacionadas a um pedido

Figura 1. Modelo de dados simplificado da empresa ItsMyBusiness

Figura 2. Fluxo das operações relacionadas a um item de pedido


63

25/02/2010 14:31:08

Resposta do desafio Vamos agora fazer um estudo detalhado dos eventos e ações a serem executadas para todo o fluxo de operações de um pedido. Esta tarefa é longa para cobrirmos num artigo, já que vamos criar vários objetos diferentes e eu vou mostrar aqui os conceitos que vamos usar em cada um deles. A sintaxe das linguagens usadas em cada SGBD varia consideravelmente, mas os conceitos que vou mostrar são aplicáveis a todos os SGBDs. Por isso, para simplificar, vou me limitar a mostrar a criação dos objetos usando apenas uma linguagem, T-SQL, que é usada pelo SQL Server. Fica a seu critério transcrever esta lógica para o seu SGBD. A primeira tarefa que temos é analisar as Figuras 1 e 2 e traduzir os eventos de mudança de status em ações no banco de dados. De forma geral, todos os eventos exibidos na Figura 2 (do evento 1 ao 8, sem exceção) se traduzem numa única ação no banco de dados: a inserção de um novo registro na tabela dbo.tblPedidoHistorico, identificando um novo status para um dado item do pedido. A Figura 3 mostra em detalhes a estrutura da tabela de histórico.

Column Name

Data Type

codPedido

int

codProduto

int

codPedidoStatus

int

DataEvento

datetime

Observacao

varchar(50)

Allow Nulls

Figura 3. Estrutura da tabela dbo.tblPedidoHistorico

Para simplificar, deixaremos o campo OBSERVACAO inalterado, aproveitando que ele aceita valores nulos. Então, nossa ação será sempre uma declaração de inserção do tipo INSERT INTO dbo.tblPedidoHistorico VALUES(PedidoAtual, ProdutoAtual, StatusNovo, DataAtual, NULL)

Esta declaração SQL será usada para todas as mudanças de status do pedido. Por esta razão, é uma boa idéia transformála num procedimento armazenado. Este procedimento pode ser invocado por outros objetos do banco de dados, assim como por aplicações e/ou serviços externos. Assim, conforme combinamos, mostro aqui o script para criação deste procedimento no SQL Server (Listagem 1). Listagem 1. Criação do procedimento armazenado spPedidoHistoricoINSERT 1 2 3 4 5

64 SQL73.indb 64

Nós acompanhamos nesta coluna o dia-a-dia da empresa fictícia ItsMyBusiness, que é uma empresa de varejo que fez recentemente o seu site de e-commerce. E o site está vendendo muito! O sucesso é tanto que um projetinho despretensioso (e mal feito) de criar um mecanismo para vendas pela internet está começando a fazer água... E a empresa precisa consertar as coisas antes que os problemas fiquem mais sérios. Uma série de correções e melhorias tem sido feitas no site da ItsMyBusiness, especialmente na base de dados, que é o que nos interessa. Recentemente foi lançado um projeto para a empresa ter um controle melhor sobre o ciclo de vendas de cada pedido que recebe no seu site. Trata-se de um novo processo de acompanhamento do status do pedido, que vai desde o registro do pedido até o momento em que se entregam os produtos ao cliente.

Precisamos agora identificar quais eventos disparam esta ação e também quais valores usaremos para cada campo a ser inserido. Vamos analisar o fluxo de operações (Figura 2) e estabelecer os eventos passo a passo, ou seja, eventos relacionados com cada código de status do pedido.

Status 1: pedido registrado no site

tblPedidoHistorico

�

Nota do DevMan 1

CREATE PROCEDURE dbo.spPedidoHistoricoINSERT (@Pedido INT, @Produto INT, @Status INT) AS INSERT INTO dbo.tblPedidoHistorico V ALUES(@Pedido, @Produto, @Status, GETDATE() , NULL) ;

Este evento ocorre logo que se registra o pedido no banco de dados da ItsMyBusiness. Porém não basta termos o número do pedido. Para fins de definição de status, precisamos conhecer o número do pedido e o código do produto em questão. Portanto, o evento que devemos considerar para fins de alteração de status de pedido é a inserção na tabela de detalhe dos pedidos, dbo.tblPedidoDetalhe. Vamos usar um gatilho do tipo AFTER INSERT, ou seja, o gatilho é disparado assim que um novo registro for inserido na tabela de detalhes do pedido. Vamos recuperar o código do pedido e o código do produto em questão buscando as informações na tabela virtual INSERTED (veja nota DevMan 2). Para completar, deveremos informar qual é o código do status que vamos considerar. Isso é simples: basta olhar a Tabela 1 e veremos que este evento está associado ao código 1. Com todas as informações em mãos, finalmente inserimos um novo registro, agora na tabela de histórico do pedido (dbo. tblPedidoHistorico). A Listagem 2 mostra o script deste gatilho. Listagem 2. Gatilho dbo.trgPedDet_ins 1 CREATE TRIGGER dbo.trgPedDet_ins 2 ON dbo.tblPedidoDetalhe 3 AFTER INSERT 4 AS 5 BEGIN 6 --usaremos valores da “tabela” virtual INSERTED 7 DECLARE @PedidoNovo INT 8 DECLARE @ProdutoNovo INT 9 10 SELECT @PedidoNovo = codPedido FROM INSERTED 11 SELECT @ProdutoNovo = codProduto FROM INSERTED 12 -- codigo status 1 = pedido aceito 13 EXEC dbo.spPedidoHistoricoINSERT @PedidoNovo,@ProdutoNovo,1 14 END ;


D ESAFIO S QL

Status 2 e/ou 102: cartão de crédito validado/rejeitado A validação do cartão de crédito do cliente é feita mediante um web service. Sendo assim, quem gerencia esta etapa é a aplicação. A ação executada na aplicação será checar a resposta do web service e invocar o procedimento armazenado spPedidoHistoricoINSERT, apresentado na Listagem 1. O cuidado principal neste passo é que o código de status será igual a 2 se o cartão for validado, e 102 se o cartão for rejeitado.

Status 3 e/ou 103: pedido aceito/recusado Na realidade, estes dois passos são “simbólicos”, porque o nosso fluxo de operação (Figura 2) prevê que o pedido passa automaticamente do status 2 para 3 (assim como do status 102 para 103). A melhor maneira de implementar isso é criando um gatilho na própria tabela de histórico do pedido. Não importa que este gatilho cause a inserção de um novo registro na própria tabela. Contanto que a lógica deste gatilho seja bem implementada, não teremos problema com isso. Este gatilho funcionará de forma muito parecida com o gatilho dbo.trgPedDet_ins (Listagem 2), apenas acrescentando os testes condicionais na sua lógica para sabermos se é caso de cartão validado ou rejeitado, como vemos na Listagem 3.

Status 4: item do pedido disponível em estoque O próximo passo é verificar se o produto está disponível em estoque. Aqui cabe um comentário importante. Observe na Figura 2 que o fluxo não prevê a hipótese de existir uma quantidade insuficiente do produto no estoque. Ou remetemos a quantidade total do produto requerida no pedido, ou não enviamos nada. Isto de fato é uma limitação do modelo, mas agora não é o momento de discutirmos isso. Para todos os efeitos práticos, o arquiteto da aplicação já fez um bom trabalho criando um modelo de dados que permite que se fature separadamente os itens de um pedido. Listagem 3. Gatilho dbo.trgPedHist_ins 1 CREATE TRIGGER dbo.trgPedHist_ins 2 ON dbo.tblPedidoHistorico 3 AFTER INSERT 4 AS 5 BEGIN 6 --usaremos valores da “tabela” virtual INSERTED 7 D ECLARE @PedidoNovo INT 8 DECLARE @ProdutoNovo INT 9 10 SELECT @PedidoNovo = codPedido FROM INSERTED 11 SELECT @ProdutoNovo = codProduto FROM INSERTED 12 13 IF (SELECT codPedidoStatus FROM INSERTED) = 2 14 EXEC dbo.spPedidoHistoricoINSERT @PedidoNovo,@ProdutoNovo,3 15 ELSE 16 BEGIN 17 IF (SELECT codPedidoStatus FROM INSERTED) = 102 18 EXEC dbo.spPedidoHistoricoINSERT 19 @PedidoNovo, @ProdutoNovo , 103 20 END 21 END ;

Feita esta consideração, vamos definir o evento que nos interessa. Nós temos que monitorar a quantidade do produto disponível em estoque e, assim que ela for maior ou igual à quantidade solicitada no pedido, atualizamos o status do pedido para o código 4.

Nota do DevMan 2 Para trabalhar com gatilhos, o SQL Server oferece duas tabelas virtuais: INSERTED e DELETED. Estas tabelas só podem ser usadas dentro dos gatilhos e elas retornam as informações dos registros que são tratados nas declarações DML. A tabela virtual INSERTED contém o registro completo que acaba de ser inserido na tabela. A tabela DELETED tem o registro completo que foi excluído da tabela. E nas declarações de UPDATE, por exemplo, teremos as duas tabelas virtuais populadas, DELETED com o registro antigo e INSERTED com o novo registro. Cada SGBD usa uma terminologia diferente, mas os conceitos são basicamente os mesmos. No Oracle e no DB2, por exemplo, temos as tabelas NEW e OLD, que funcionam de maneira muito parecida com o SQL Server.

O método mais simples de fazermos este monitoramento no SQL Server é habilitando um job que será executado periodicamente. Digamos que o job seja configurado para rodar a cada 15 minutos. Neste caso, a “rotina” será executada, conferindo o saldo em estoque de todos os itens de pedido que estejam com status 3. Se houver saldo suficiente, o item do pedido mudará para o status 4. Do contrário, este item permanece com o mesmo status até que a rotina seja executada novamente. Devemos ter um cuidado especial com esta rotina, porque os pedidos devem ser analisados sequencialmente e em ordem de cadastramento. Assim vamos garantir que os pedidos mais antigos serão atendidos primeiro. Por simplicidade, a “rotina” que vamos executar será um procedimento armazenado. E o sequenciamento dos itens de pedido será feito usando-se um cursor. Dois detalhes importantes: 1. Nós devemos consultar apenas os pedidos que tem o último status igual a 3. Por isso é preciso uma consulta de sumarização (GROUP BY) para identificar os pedidos corretamente. 2. Neste modelo, pedidos são controlados por produto, mas os saldos são controlados por lote de produto. Então nós só teremos o saldo em estoque correto após fazermos a sumarização das quantidades de todos os lotes do mesmo produto. Isto nos leva ao script da Listagem 4. Muito bem, temos aí o procedimento a ser agendado. Quanto ao método de agendamento, existem vários. No SQL Server, a maneira mais fácil de fazer isso é criando um job no SQL Server Management Studio, que é a ferramenta gráfica de administração do SGBD. Um job nada mais é do que uma tarefa agendada que será controlada por um serviço especial do SQL Server (SQL Agent). Existe uma infinidade de artigos mostrando como criar estes jobs e não vou me estender nesta questão. A t ít ulo de exemplo, reco mendo o artigo da MSDN “Como criar um trabalho” (http://msdn.microsoft.com/pt-br/library/ms190268.aspx).


65

25/02/2010 14:31:09

Listagem 5. Gatilho dbo.trgNFDet_ins 1 CREATE TRIGGER dbo.trgNFDet_ins 2 O N dbo.tblPedidoDetalhe 3 AFTER INSERT 4 AS 5 BEGIN 6 --usaremos valores da “tabela” virtual INSERTED 7 DECLARE @PedidoNovo INT 8 DECLARE @ProdutoNovo INT 9 10 SELECT @PedidoNovo = codPedido 11 FROM dbo.tblNotaFiscal 12 WHERE codNotaFiscal = (SELECT codNotaFiscal FROM INSERTED) 13 14 SELECT @ProdutoNovo = codProduto 15 FROM dbo.tblLote 16 W HERE codLote = (SELECT codLote FROM INSERTED) 17 -- codigo status 5 = nota fiscal emitida 13 EXEC dbo.spPedidoHistoricoINSERT @PedidoNovo,@ProdutoNovo,5 14 END ;

Listagem 6. Atualização do gatilho dbo.trgPedHist_ins 1 ALTER TRIGGER dbo.trgPedHist_ins 2 ON dbo.tblPedidoHistorico 3 AFTER INSERT 4 AS 5 BEGIN 6 --usaremos valores da “tabela” virtual INSERTED 7 DECLARE @Pedido INT 8 DECLARE @Produto INT 9 10 SELECT @Pedido = codPedido FROM INSERTED 11 SELECT @Produto = codProduto FROM INSERTED 12 13 IF (SELECT codPedidoStatus FROM INSERTED) = 2 14 EXEC dbo.spPedidoHistoricoINSERT @Pedido, @Produto, 3 15 ELSE 16 BEGIN 17 IF (SELECT codPedidoStatus FROM INSERTED) = 102 18 BEGIN 19 EXEC dbo.spPedidoHistoricoINSERT @Pedido, @Produto, 103 20 EXEC dbo.spPedidoHistoricoINSERT @Pedido, @Produto, 8 21 END 22 ELSE 23 IF (SELECT codPedidoStatus FROM INSERTED) = 7 24 EXEC dbo.spPedidoHistoricoINSERT @Pedido, @Produto, 8 25 END 26 END ;

66 SQL73.indb 66

Após o serviço de faturamento (que não cabe aqui entrarmos em detalhes), as notas fiscais serão emitidas. E então nós deveremos passar estes pedidos para o status 5. Este evento deve acontecer logo após a inserção das notas na tabela correspondente (tblNotaFiscalDetalhe). Por isso teremos mais uma vez um gatilho, agora na tabela de detalhes da nota fiscal. A ação é praticamente a mesma da que mostramos na Listagem 2, com uma exceção. Nós precisamos ter o pedido correspondente à nota fiscal, mas isso é registrado na tabela dbo.tblNotaFiscal , que será gravada antes da tabela de detalhes. Então o código do Pedido não vai existir na tabela virtual INSERTED. Cabe a nós fazermos uma consulta à tabela de notas fiscais para extrairmos esta informação. Da mesma forma, a tabela INSERTED só terá o código do lote faturado e não o código do produto. Mais uma vez, deveremos fazer uma consulta. Veja a Listagem 5.

Status 6: remessa do pedido para transportadora O status 6 acontece quando a transportadora retira o pacote correspondente a cada pedido na expedição da ItsMyBusiness. Portanto, neste caso, quem gerencia esta etapa é a aplicação. A aplicação vai invocar o procedimento spPedidoHistoricoINSERT passando o status 6 como um dos argumentos.

Status 7: pedido entregue Mais uma vez a aplicação vai cuidar desta mudança de status, porque dependemos de informações vindas da transportadora para providenciar a atualização do nosso banco de dados.

Status 8: pedido fechado O status 8 é outro processo automático. Todo pedido que chegar ao status 7 (pedido entregue) ou status 103 (pedido recusado), deve passar automaticamente para o status 8. Portanto, aqui basta alterarmos o gatilho que criamos para a tabela de histórico de pedido (Listagem 3), acrescentando este teste. O script atualizado e definitivo é mostrado na Listagem 6. Assim finalmente terminamos nosso desafio. Apesar das simplificações que foram necessárias para fazer este artigo ficar um pouco mais didático, os conceitos apresentados aqui são muito usados no dia-a-dia e acredito que lhes serão úteis.



O conceito de job do SQL Server é um pouco diferente do que se tem em outros SGBDs. Normalmente outros SGBDs trabalham em ambiente UNIX (ou assemelhados) e o que se faz, em geral, é criar um SHELL script que será agendado no CRON, um agendador de tarefas nativo dos sistemas operacionais da família UNIX.

Status 5: nota fiscal emitida

Dê s

1 CREATE PROCEDURE dbo.spPedidoSaldo_sel 2 AS 3 BEGIN 4 DECLARE @Pedido INT 5 DECLARE @Produto INT 6 DECLARE @QTDPedida NUMERIC(11,2) 7 DECLARE @QTDSaldo NUMERIC(11,2) 8 9 --so pedidos cujo ultimo status é igual a 3 10 DECLARE curPedidoItem3 CURSOR FAST_FORWARD FOR 11 SELECT codPedido, codProduto 12 FROM dbo.tblPedidoHistorico 13 GROUP BY codPedido, codProduto 14 H AVING MAX(codPedidoStatus) = 3 15 OPEN curPedidoItem3 16 FETCH NEXT FROM curPedidoItem3 17 INTO @Pedido, @Produto 18 WHILE @@FETCH_STATUS = 0 19 B EGIN 20 -saldo eh controlado por lote de produto. 21 SELECT @QTDSaldo = SUM(QTD) FROM dbo.tblSaldoEstoque 22 WHERE codLote in (SELECT codLote 23 FROM dbo.tblLote WHERE codProduto = @Produto) 24 -qtd pedida 25 SELECT @QTDPedida = QTD FROM dbo.tblPedidoDetalhe 26 W HERE codPedido = @Pedido AND codProduto = @Produto 27 28 I F @QTDSaldo >= @QTDPedida 29 EXEC dbo.spPedidoHistoricoINSERT @Pedido,@Produto,4 30 31 FETCH NEXT FROM curPedidoItem3 32 INTO @Pedido, @Produto 33 END 34 C LOSE curPedidoItem3 35 DEALLOCATE curPedidoItem3 36 END

edição ta

Listagem 4. Procedimento spPedidoSaldo_sel


D ESAFIO S QL


67

25/02/2010 14:31:13

68 SQL73.indb 68


SQL-magazine 073 Tuning Do Oracle

Recommend Documents