sql-magazine 073 tuning do oracle

SQL73.indb 1 25/02/2010 14:30:12

Proj

eto/

Mod

elag

em

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?

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

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

VocêÊj‡ÊprecisouÊdeÊumÊn’velÊdeÊsuporteÊdiferenciado?

VocêÊgostariaÊdeÊmelhoresÊganhos/margens?Ê

©Ê2009ÊFairComÊCorporation

the right-size Database

Então é hora de conhecer o:

C

M

Y

CM

MY

CY

CMY

K

0904HammerBRFullx1a.pdf 1 10/28/09 8:41 AM

SQL73.indb 2 25/02/2010 14:30:14

06 – Estudos de Caso – Projeto de Banco de Dados para Reembolso[ Mauro Pichiliani ]

Modelo Pré-Fabricado

Primeiros Passos em Banco de Dados

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

Easy22 – A Linguagem PL/Java do PostgreSQL – Parte 2[ Clailson de Almeida ]

Primeiros Passos em Banco de Dados

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

Dia a Dia54 – Usando CDC e Trigger em Auditorias no SQL Server 2008[ Tulio Rosa ]

Dia a Dia46 – Oracle Performance Diagnostics & Tuning[ Ricardo Portilho Proni ]

Desafio SQL62 – Desafio SQL[ Wagner Crivelini ]

Dia a Dia29 – Administrando o SQL Server com uma Ferramenta Web[ Nilton Pinheiro ]

Banc

o de

Dad

os/ P

ersi

stên

cia

Proj

eto/

Mod

elag

em Sumário

Brinde na web desta edição

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

1) JSP e Apache Derby-Parte 1- Apresentando o aplicativo que será desenvolvido.

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.

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

3Vídeos

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/feedbackPara votar, você vai precisar do código de banca desta edição, que é: haloosdt

Dê seu feedback sobre esta edição!

Dê

seu Feedback

sob

re esta edição

Olá, eu sou o DevMan! Desta pá-gina em diante, eu estarei lhe aju-dando 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:

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?

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

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

VocêÊj‡ÊprecisouÊdeÊumÊn’velÊdeÊsuporteÊdiferenciado?

VocêÊgostariaÊdeÊmelhoresÊganhos/margens?Ê

©Ê2009ÊFairComÊCorporation

the right-size Database

Então é hora de conhecer o:

C

M

Y

CM

MY

CY

CMY

K

0904HammerBRFullx1a.pdf 1 10/28/09 8:41 AM

SQL73.indb 3 25/02/2010 14:30:14

Expediente Editorial

Rodrigo Oliveira Spínola

[email protected] 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.

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

Ano 7 - 73ª Edição 2010 - ISSN 1677918-5 - Impresso no Brasil

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 Leitorwww.devmedia.com.br/mancad/(21) 3382-5038

Kaline Dolabella – Gerente de Marketing e [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 [email protected]

Corpo Editorial

Editor GeralRodrigo Oliveira Spínola

[email protected]

Sub EditoresArilo Cláudio Dias Neto, Eduardo Oliveira Spínola

e Ricardo Rezende

Capa e DiagramaçãoRomulo Araujo

[email protected]

Revisão e SupervisãoThiago Vincenzo - [email protected]

Coordenação GeralDaniella Costa - [email protected]

Na Webwww.devmedia.com.br/sqlmagazine/pagina.asp

DistribuiçãoFernando Chinaglia Dist. S/A

Rua Teodoro da Silva, 907

Grajaú - RJ - 206563-900

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 [email protected]

EDITORIAL

Observamos atualmente um cenário em que as or-ganizaçõ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 mui-to 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. Nes-te contexto, a SQL Magazine destaca nesta edição uma matéria muito interessante intitulada: Oracle Performan-ce 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í[email protected]

Mais conteúdo SQL por menos!

Assinatura

SQL73.indb 4 25/02/2010 14:30:20

SQL73.indb 5 25/02/2010 14:30:22

6 SQL Magazine - Estudos de Caso – Projeto de Banco de Dados para Reembolso

Mauro [email protected]

É bacharel em Ciência da Computação, mes-tre pelo ITA (Instituto Tecnológico de Aero-ná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).

De que se trata o artigo?Modelagem da estrutura de armazenamento de da-dos para um sistema de controle dos reembolsos.

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

Em que situação o tema é útil?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 apresen-tado 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, pode-se 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.

Estudos de Caso – Projeto de Banco de Dados para ReembolsoModele um banco de dados para um sistema que controla o reembolso de despesas

Os funcionários e colaboradores de uma empresa precisam de diversos recursos para realiza-

rem as suas atividades. Dentre os prin-cipais 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, aleatorie-dade 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 fir-mado 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 justi-ficado e realizado com fins profissionais e que atendam aos objetivos do negócio.

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

Seção Banco de Dados/PersistênciaSeção Projeto/Modelagem

SQL73.indb 6 25/02/2010 14:30:24

Edição 72 - SQL Magazine 7

PROJetO

Assim como qualquer outro recurso da empresa que é dispo-nibilizado 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 apresen-tado 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árioPara entender como funcionará o controle de reembolso em

uma empresa é preciso primeiro delimitar o escopo e esco-lher 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 dire-tamente 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 res-ponsá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 estabeleci-mento, CPF ou CNPJ, telefone e informações de contato e op-cionalmente 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 departa-mento 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 gas-tar. 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 funcio-nário recebe um cartão de crédito da empresa e paga todos os gastos da viagem com este cartão corporativo. Opcional-mente 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

SQL73.indb 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 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 estima-tiva 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ça-mento 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.

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 vice-presidentes. No início do fluxograma o funcionário deve indi-car 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 empre-sas. 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 fun-cionário cadastra a viagem e indica que utilizará a modalidade Reembolso pleno. Após a aprovação do superior e do departa-mento de contabilidade o funcionário recebe uma notificação, efetua os gastos e faz o lançamento das notas e comprovantes.

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

SQL73.indb 8 25/02/2010 14:30:25


PROJetO

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 indi-ca 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 entidadesA partir da explicação de funcionamento do reembolso des-

crita na seção anterior podemos começar a modelagem indi-cando 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.

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 par-tir 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.

Esta delimitação quer dizer que é preciso identificar e abstrair certas características dos cenários de modo a melhorar o mode-lo 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á es-tã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á armaze-nados na entidade FUNCIONÁRIOS.

Outra entidade que também pode ficar de fora do modelo final é a entidade FORNECEDORES, pois apesar de ser im-portante 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 transfor-madas 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

N o t a d o D e v M a nAtributo 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 valo-res: 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 valo-res de atributos do tipo flag.

Figura 3. Fluxograma da modalidade de reembolso Reembolso pleno

SQL73.indb 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 ape-nas 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 esti-mativa 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 deta-lhes 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 rela-cionamento 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 à noti-ficação do funcionário. Esta notificação deve ser realizada de acordo com os contatos individuais de cada funcio-nário, que são modelados em uma entidade chamada

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 iden-tificador 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_FUN-CIONARIO 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 identi-ficador interno (quatro últimos dígitos do cartão ou número do cheque) e também um atributo para observações de uso deste

SQL73.indb 10 25/02/2010 14:30:25


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_TIPO-COMPROVANTE e contém atributos para o nome do compro-vante (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 COMPRO-VANTES. Esta entidade contém um identificador único (ID_GASTO) do gasto, um atributo que é chave estrangeira para a entidade TIPO_COMPROVANTE (ID_TIPOCOM-PROVANTE), um atributo que é chave estrangeira para a entidade VIAGEM_FUNCIONARIO (ID_VIAGEM), e atributos para armazenar o CNPJ ou CPF, nome e telefo-ne 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_PAGAMEN-TO, 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 es-trangeira 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,

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 6. Entidades VIAGEM_FUNCIONARIO, NOTIFICA_FUNCIONARIO e CONTATOS_FUNCIONARIO adicionadas ao modelo de controle de reembolso

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

SQL73.indb 11 25/02/2010 14:30:26


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

ConclusãoEste artigo mostrou como modelar as principais entidades de

um sistema que controla o reembolso de despesas dos funcio-ná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 mo-delagem 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 da-dos, que pode servir como ponto de partida para a modelagem

N o t a d o D e v M a n

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.

Artigo “Análise de Requisitos com Casos”, escrito pelo Kleber Xavier e publicado na edição

número 49 da revista Java Magazine.

Referências

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.

N o t a d o D e v M a n

Software ERP: ERP (do inglês Enterprise Resource Planning) são os sistemas de in-formaçõ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 sis-tê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.


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

Dê

seu Feedback

sob

re esta edição

www.infnet.edu.br - [email protected] - Central de Atendimento: (21) 2122-8800

E D U C A Ç Ã O S U P E R I O R O R I E N T A D A A O M E R C A D O

PÓS-GRADUAÇÃO

Engenharia de Software: Desenvolvimento Java

Engenharia de Software: Desenvolvimento .NET

GRADUAÇÃO

Engenharia de ComputaçãoAnálise e Desenv. de Sistemas

FORMAÇÕES

Desenvolvedor Java

Desenv. Java: Sist. Distribuídos

Gestor de TI

Desenvolvedor Web .NET 2008

MCITP Server Administrator

SQL Server 2008

Acesse nosso site e conheça todos os nossos programas: www.infnet.edu.br/esti

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.

r/esti

TURMASNO RIO DEJANEIRO

Modéstia à parte, suamelhor opção para

se destacar no mercado!

SQL73.indb 12 25/02/2010 14:30:27


PROJetO

www.infnet.edu.br - [email protected] - Central de Atendimento: (21) 2122-8800

E D U C A Ç Ã O S U P E R I O R O R I E N T A D A A O M E R C A D O

PÓS-GRADUAÇÃO

Engenharia de Software: Desenvolvimento Java

Engenharia de Software: Desenvolvimento .NET

GRADUAÇÃO

Engenharia de ComputaçãoAnálise e Desenv. de Sistemas

FORMAÇÕES

Desenvolvedor Java

Desenv. Java: Sist. Distribuídos

Gestor de TI

Desenvolvedor Web .NET 2008

MCITP Server Administrator

SQL Server 2008

Acesse nosso site e conheça todos os nossos programas: www.infnet.edu.br/esti

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.

r/esti

TURMASNO RIO DEJANEIRO

Modéstia à parte, suamelhor opção para

se destacar no mercado!

SQL73.indb 13 25/02/2010 14:30:27

14 SQL Magazine - Conheça na Prática os Novos Recursos do PostgreSQL 8.4

Carlos Eduardo Smaniotowww.datapower.com.br

[email protected]

Diretor da Data Power Technology, uma em-presa 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. Traba-lha com projetos. NET e JAVA adotando ban-co de dados Oracle/PostgreSQL e prestação de serviço como por exemplo, treinamento IN-COMPANY.

De que se trata o artigo?O artigo demonstra o projeto de um sistema de uma companhia de passagem aérea, apresen-tando 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 mode-lagem 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 mode-lagem de sistemas com características simila-res 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.

Conheça na Prática os Novos Recursos do PostgreSQL 8.4Projete 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, fun-cionários e bilhetes emitidos. Para isso, serão demonstradas algumas das no-vidades 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 PostgreSQLAntes 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 dei-xar o SGDB mais competitivo contra ou-tros SGBDs como o SQL Server e Oracle no que diz respeito a funcionalidades, outras melhorias buscam praticidade,

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.

Seção Projeto/Modelagem

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

Seção Banco de Dados/Persistência

SQL73.indb 14 25/02/2010 14:30:28


POStgReSQL

O arquivo postgresql.confPara 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 con-figuraçã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 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

recursivas

Conhecidas como CTEs (Common Table Expressions and Recursive Queries). O Principal uso da CTEs é possibilitar subconsultas complexas sem criar tabelas

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 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.

Melhoria de desempenho com índices hash Í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.

Semi-joins e Anti-joins 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

Configuração automática para o Free Space Map 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.

Editor de Function 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

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.

SQL73.indb 15 25/02/2010 14:30:28


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.

Na Figura 1, estamos dizendo que o vôo é o elemento prin-cipal 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 funcio-nario) ao seu respectivo departamento (tabela departamento). É um relacionamento N para 1.

Povoando o Banco de DadosAs 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 depar-tamento 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 compu-taçã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

Figura 1. Tabela voos e as tabelas auxiliares

Figura 2. Tabela funcionario e auxiliar

N o t a d o D e v M a n 1Detalhando as Windows FunctionsAs Windowing functions são uma implementação de suma importância intro-

duzida 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 funcionalida-des 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.

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 # maximum XID age before forced vacuum425. #vacuum_freeze_min_age = 50000000426. #vacuum_freeze_table_age = 150000000

SQL73.indb 16 25/02/2010 14:30:28


POStgReSQL

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-08-13’, 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

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. SELECT id_funcionario, funcionario, cargo, departamento, salario, avg(salario)2. OVER (PARTITION BY departamento)::numeric(9,2) AS media_departamento3. FROM funcionario4. INNER JOIN departamento ON5. departamento.id_departamento = funcionario.id_departamento6. ORDER BY media_departamento;

e a média por departamento de uma única vez de uma forma ex-tremamente 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 re-lacionamento 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.

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 siste-ma. 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 calcu-laram 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,

SQL73.indb 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 COLUNASO 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.

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 recur-sividade é 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.

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_parti-da, 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

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 quanti-dade 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.

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 WHERE 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 WHERE 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;

SQL73.indb 18 25/02/2010 14:30:29


POStgReSQL

N o t a d o D e v M a n 2

Consultas Recursivas – Common Table ExpressionAs consultas recursivas eliminam boa parte da programação PL/PGSQL e lógica cen-

tralizada 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 en-tender 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;

O resultado da consulta acima seria 3! Mas como funciona isso?

Na consulta exemplo acima, através do WITH RECURSIVE criamos uma “COMMON TA-BLE EXPRESSION”, ou seja, é a regra de negócios para que a consulta recursiva funcione. O comando WITH é finalizado com a sentença:

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:

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 traba-lhar 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 SE-LECT após ele; lembrando que este select finaliza com a invocação da própria CTE para se tornar recursiva.

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 po-demos 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.

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.

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 Post-greSQL 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/PGS-QL. Podemos executá-la da forma descrita na Listagem 12.

Listagem 11. Função que irá reduzir a disponibilidade de vendas.

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 qtd_alterada := existente - $2; raise notice ‘DISPONIVEL: %’, qtd_alterada; UPDATE vôos SET qtd_disponivel = qtd_alterada::int4 WHERE vôo_num = $1; ELSE RAISE 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);

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.

SQL73.indb 19 25/02/2010 14:30:29


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.

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

SELECT func_substrai_qtd(‘BZ-2010001’);

Comparativo entre a Versão 8.3 e 8.4 do PostgreSQLUma 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 in-teressantes 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 reali-zada 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 ve-locidade, 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 cus-to maior, entretanto, os testes do Dr. Shah “provam” um acrés-cimo geral entre o último release da série 8.3 e o novo 8.4

ConclusãoExistem estudos que demonstram superioridade de desem-

penho entre o PostgreSQL e bancos comerciais como o DB2, SQL Server e Oracle. Em uma migração que realizamos de

dados do Oracle para o PostgreSQL, uma arquivo EDI de im-portaçã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, con-sideramos 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 interessan-tes, tornando as tarefas bastante simples.

Download do projeto deste artigowww.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.3http://blogs.sun.com/jkshah/resource/pgcon2009_8384p.pdf

Links


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

Dê

seu Feedback

sob

re esta edição

SQL73.indb 20 25/02/2010 14:30:33


POStgReSQL

SQL73.indb 21 25/02/2010 14:30:39

22 SQL Magazine - A Linguagem PL/Java do PostgreSQL

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 ci-dade 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.

De que se trata o artigo?Descrição e demonstração da preparação do am-biente do banco de dados para receber a máquina virtual Java, utilizando-a para a instalação da lin-guagem procedural PL/Java e desenvolvimento de funções que usam classes Java.

Para que serve?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 configu-rado 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 recur-sos do mesmo com a utilização e manipulação direta dos dados pelo Java.

A Linguagem PL/Java do PostgreSQLParte 2 – Instalação e Preparação do Ambiente

Nesta série de artigos estamos demonstrando a utilização da linguagem PL/Java no Pos-

tgreSQL, ampliando ainda mais as possibilidades de desenvolvimento de funções e triggers, através da linguagem Java.

Na primeira parte deste artigo, des-crevemos algumas características da linguagem procedural PL/Java, e mos-tramos 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.

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




SQL73.indb 22 25/02/2010 14:30:40


POStgReSQL

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

Pré-requisitos para Execução do PL/JavaAntes 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 uti-lizado 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).

Configuração do Ambiente do ServidorComo 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:

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 resul-tado não seja este, devemos verificar as ocorrências de erros e proceder novamente com a instalação do Java.

[root@localhost ~]# java -versionjava 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_LI-BRARY_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 compar-tilhados 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.

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

[root@localhost ~]# ldconfig

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.

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

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

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 biblio-tecas são escritas em C e servem para leitura, criação e modi-ficaçã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éto-do 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

SQL73.indb 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 uti-lizar 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 ambien-te 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 necessida-des 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á desa-bilitar 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.

Download do PL/JavaCom 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.3-1.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/JavaPara 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 reconheci-mento deste módulo.

Vamos então criar um diretório chamado pljava, dentro do diretório /var/lib/pgsql e descompactar o arquivo pljava-i686-pc-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.3- 1.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 post-master 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 recursiva-mente o dono dos arquivos e diretório no caminho /var/lib/pgsql/pljava. Depois, com o comando chmod, trocamos as per-missõ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.


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é mes-mo, 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 smar-tcard para mostrar uma mensagem de erro traduzida, validações de dados ou até mesmo informações úteis para o usuário.

SQL73.indb 24 25/02/2010 14:30:40


POStgReSQL

Configuração do PostgreSQLPara que o módulo PL/Java seja reconhecido e

funcione corretamente, é necessário configurar o servidor PostgreSQL, modificando o arquivo post-gresql.conf do diretório /var/lib/pgsql/data.

Existem duas formas de fazer com que o Post-greSQL 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.

dynamic_library_path = ‘$libdir:<diretório de instalação do pljava>’

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_varia-ble_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

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 = <diretório de instalação do pljava>/pljava.jar

Listagem 5. Configuração dos parâmetros do PL/Java.

pljava.classpath = ‘/var/lib/pgsql/pljava/pljava.jar’pljava.statement_cache_size = 10pljava.release_lingering_savepoints = truepljava.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.

Listagem 4. Mudança das permissões de acesso dos arquivos

do PL/Java.

chown postgres:postgres -R /var/lib/pgsql/pljavachmod 700 -R /var/lib/pgsql/pljava

SQL73.indb 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.

vmoptions

Define as opções de inicialização para a JVM, por exemplo:

pljava.vmoptions = ‘-Xms64M –Xmx128M -Xbatch’

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.

debug

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

N o t a d o D e v M a n 2Prepared 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, pode-mos 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 state-ment é “SELECT * FROM CLIENTES WHERE CODIGO > ? AND CATEGORIA = ?”.

Instalação do PL/Java nas Bases de DadosA 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 lingua-gem 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 lin-guagem 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, utili-zando 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 é utilizan-do 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 colo-cado 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 6. Criação da base sql_magazine_pljava e instalção da linguagem

PL/Java.

createdb -U postgres sql_magazine_pljava -E LATIN1 -T template0psql -U postgres sql_magazine_pljava -1 -f /var/lib/pgsql/pljava/install.sql

Listagem 7. Opções do programa de deploy do PL/Java.

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

SQL73.indb 26 25/02/2010 14:30:41


POStgReSQL

A sintaxe do comando pode ser vista abaixo, e na Tabela 2 podemos ver cada opção e sua descrição.

java -cp <classpath para os arquivos “.jar” do deploy e do driver JDBC do PostgreSQL> 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 SQLJComo foi comentado na seção anterior, depois da insta-

laçã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

-installInstala 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.

-reinstall

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.

-uninstallRemove 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.

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

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

-database <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 <host name> Nome do host da conexão. O default é localhost.

-port <port number> 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_entryTabela 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).

TABLE sqlj.classpath_entryTabela que armazena as informações de configuração do classpath dos pacotes JAR dentro do servidor

de banco de dados.

TABLE sqlj.typemap_entryTabela 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

SQL73.indb 27 25/02/2010 14:30:41



Objeto SequenceEste 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.

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

ConclusãoAo longo deste artigo podemos demonstrar como é rela-

tivamente 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 demonstra-remos como são escritas as classes Java para utilização com o PL/Java. Até a próxima.

Site Oficial do PostgreSQLhttp://www.postgresql.org/

Site do projeto PL/Java no PgFoundryhttp://pgfoundry.org/projects/pljava/

Download do PL/Javahttp://pgfoundry.org/frs/?group_id=1000038

Site Oficial do Javahttp://java.sun.com/

Doc API Java Standard Edition 6http://java.sun.com/javase/6/docs/api/

Download do Java 6http://www.java.com/pt_BR/download/manual.jsp

Links





Dê

seu Feedback

sob

re esta edição

SQL73.indb 28 25/02/2010 14:30:42


SQL SeRveR

Nilton Pinheiro [email protected]

É formado em Análise de Sistemas e pós-graduado 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 traba-lha 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.

De que se trata o artigo?O artigo apresenta uma solução para gerencia-mento de bancos de dados do SQL Server a partir de uma ferramenta que utiliza a Web como plata-forma 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 ge-renciamento 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 possi-bilita o gerenciamento de bancos de dados através da Web, possibilitando o gerenciamento remoto dos bancos de dados do SQL Server.

Administrando o SQL Server com uma Ferramenta WebConheça o SQL Server Web Data Administrator

Iniciaremos 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 fer-ramenta 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.




SQL73.indb 29 25/02/2010 14:30:48

30 SQL Magazine - Administrando o SQL Server com uma Ferramenta Web

No artigo de hoje será apresentada esta ferramenta, descre-vendo passo-a-passo como realizar sua instalação e configu-raçã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 AdministratorO 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 funcionali-dades 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.

Instalando o SQL Server Web Data AdministratorComo 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.

• 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 Win-

dows 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 anterior-mente, 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 es-trutura 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).

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

SQL73.indb 30 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).

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 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 IniciarProgramasFerramentas 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,

clique com o botão direito sobre a barra de tarefas do Win-dows 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 6. Selecionando o botão Personalizar

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

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

SQL73.indb 31 25/02/2010 14:30:48


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 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 tam-bé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

é 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:

- http://www.microsof t.com/downloads/details. aspx? FamilyID = 0856eacb - 4362- 4b 0d- 8edd- aab15c5e 04f 5 &DisplayLang=en: .NET Framework 2.0 32bits.- http://www.microsof t.com/downloads/details. aspx? familyid=B44A0000-ACF8-4FA1-AFFB-40E78D788B00 &displaylang=en: .NET Framework 2.0 64bits.

Figura 9. Selecionando a opção Properties do site SqlWebAdmin

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.

SQL73.indb 32 25/02/2010 14:30:49


SQL SeRveR

Trabalhando com o SQL Server Web Data Administrator

Uma vez que o SQL Server Web Data Administrator está instalado e configurado corretamente, é possível acessar a fer-ramenta de qualquer estação de uma rede utilizando o Internet Explorer e usando a URL http://nome_servidor/SqlWebAd-min/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 Win-dows 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 WINXP-DEV 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).


SQL Server Management StudioSQL 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 Ser-ver 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 Enter-prise 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 Inte-gração, Edição compactada do SQL Server e Serviço de Notificação.

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

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, consul-tar, 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 12. Janela principal do SQL Server Web Data Administrator

SQL73.indb 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 DadosPara 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 sele-cionando 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 sele-cionado 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

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 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

SQL73.indb 34 25/02/2010 14:30:50


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.

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

Exportando e Importando Dados e ObjetosDuas 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 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 tam-bé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_SQL-MAGAZINE 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_SQLMAGA-ZINE. Para mostrar a importação, vamos alterar o script apenas substituindo o nome DB_SQLMAGAZINE por DB_SQLMAGA-ZINE2 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 proce-dure usp_SQLMAG com o sufixo 2. Na Figura 22 podemos ver as tabelas criadas no banco de dados DB_SQLMAGAZINE2.

SQL73.indb 35 25/02/2010 14:30:51


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

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

Criando Logins e UsuáriosO 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

Figura 22. Visualizando as tabelas do banco de dados DB_SQLMAGAZINE2

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 pode-mos ver a página de criação de logins durante a criação do login user_sqlmag.

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

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 qual-quer tarefa sobre este banco de dados.

SQL73.indb 36 25/02/2010 14:30:52


SQL SeRveR

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

ConclusãoComo vimos no decorrer deste artigo, o SQL Server Web

Data Administrator é uma ótima alternativa para quem





Dê

seu Feedback

sob

re esta edição

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 bro-wser 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.

SQL73.indb 37 25/02/2010 14:30:53

38 SQL Magazine - Compressão de dados no SQL Server

Vladimir Michel Magalhães [email protected]

Bacharel em Ciência da Computação pela Univer-sidade 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 con-sultor 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/

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.

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 princi-pais 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 econo-mizar 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 interes-sante para minimizar tais problemas.

Compressão de dados no SQL Server

Quando falamos em bancos de dados, um assunto recorren-te é o espaço utilizado para

armazená-los. Bancos de dados normal-mente 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, mui-tas vezes, não há espaço disponível, e é necessário comprar mais discos, o que representa um custo a mais para a em-presa. 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 es-paço disponível de forma que não seja necessária uma nova compra.

Bancos de dados em geral são bastante passíveis de compressão, visto a nature-za 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




SQL73.indb 38 25/02/2010 14:30:54


SQL SeRveR

a menor quantidade de I/O, já que a quantidade de Bytes ar-mazenados 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 2005O SQL Server 2005 já dispunha de alguns recursos para

permitir a compressão de dados. Uma delas era a habili-dade 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 se-cundá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, de-pois 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, indepen-dentemente 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 recomen-dar 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á armaze-nado 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



/*Torna o filegroup Secondary apenas leitura*/ALTER DATABASE [Compressao] MODIFY FILEGROUP [SECONDARY] READ_ONLYGO

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 “Compac-tar este disco para economizar espaço”. Por fim, reinicie o serviço do SQL Server.

Figura 1. Ativando a compactacao de unidade NTFS

SQL73.indb 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.

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 coman-dos podem ser vistos na Listagem 2.

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 SMALLINTDECLARE @MENOR TINYINT, @MAIOR TINYINT

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

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

SET @CONTADOR += 1;END

Antes de ativar a compressão, vamos seguir alguns passos descritos na Listagem 3. Primeiramente, vamos verificar o es-paç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_estima-ted_rowsize_reduction_for_vardecimal (linha 3). Agora iremos ativar a compressão Vardecimal no banco de dados, com a SP sp_db_var-decimal_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).

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

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

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_row-len_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 conside-raçã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,

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

/*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’, 18. GO

/*Exibe o tamanho ocupado pela tabela, após a compressão VarDecimal*/9. EXEC sp_spaceused ‘CompressaoVarDecimal’10. GO

SQL73.indb 40 25/02/2010 14:30:56


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 2008Com o SQL Server 2008, ambas as formas de compressão pre-

sentes 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 to-dos 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 an-teriormente, 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 re-presentar uma grande economia de espaço. A Figura 4 mostra um exemplo do 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 repe-tiçã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 “dicio-ná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 ha-verá valores do dicionário apontando para valores de prefixo de coluna. Um exemplo deste funcionamento pode ser visto na Figura 6.

Figura 4. Funcionamento da compressão de linha Figura 6. Funcionamento da técnica de dicionário de página

SQL73.indb 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” repre-sentando 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 dicio-nário de página.

Uma vantagem interessante destes novos métodos de compressão comparados a compressão NTFS é que dife-rentemente 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.

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), pro-positalmente 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 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 compres-são de linha (coluna data).

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 no-vamente 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

/*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).

Listagem 4. Criando a tabela para os testes

1. USE Compressao;2. GO3. CREATE TABLE Compressao(4. CODIGO INT NOT NULL IDENTITY(1,1) PRIMARY KEY,5. NOME CHAR(50)6. );7. GO8. DECLARE @QUANTIDADE INT, @CONTADOR INT9. DECLARE @NOME CHAR(50)10. SET @CONTADOR = 1;11. SET @QUANTIDADE = 30000;12. WHILE @CONTADOR <= @QUANTIDADE13. BEGIN14. SET @NOME = (15. ‘MARI’ 16. + CHAR(64+(@CONTADOR%26)) + CHAR(64+(@CONTADOR%26))17. + ‘ SANTOS ‘ 18. + CHAR(64+(@CONTADOR%26)) + CHAR(64+(@CONTADOR%26))19. );20. INSERT INTO Compressao (NOME) VALUES (@NOME);21. SET @CONTADOR += 1;22. 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 9. Tamanho ocupado pela tabela depois de ativada a compressão de linha

SQL73.indb 42 25/02/2010 14:30:56


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 – co-luna 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 provavel-mente 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 Colu-nas 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 capaci-dades 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 R2Apesar 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 ga-nho algum. Então neste caso, mesmo que você habilite a compres-sã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

SQL73.indb 43 25/02/2010 14:30:57


Estimando os ganhos com a compressãoNormalmente, 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 for-nece 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, uti-lizamos a SP sp_estimate_data_compression_savings. Primeiro desativamos a compressão da tabela (linha 3). Depois estima-mos 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_savings6. @index_id = 1, --tipo de indíce, no caso, Clustered7. @partition_number = 1, --Partição a ser comprimida, no caso, 1 para tabela não particionada8. @schema_name = ‘dbo’, --schema9. @object_name = ‘Compressao’, --nome do objeto a ser comprimido10. @data_compression = ‘ROW’ --tipo de compressão, no caso linha/*Estimando a compressão de Página*/11. EXEC sp_estimate_data_compression_savings12. @index_id = 1, --tipo de indíce, no caso, Clustered13. @partition_number = 1, --Partição a ser comprimida, no caso, 1 para tabela não particionada14. @schema_name = ‘dbo’, --schema15. @object_name = ‘Compressao’, --nome do objeto a ser comprimido16. @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á apre-sentado na Figura 12. A coluna “size_with_requested_com-pression_setting(KB)” exibe o tamanho estimado com o tipo

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 ReaisA 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/sqlserverstorageengi-ne/archive/2009/08/16/update-on-data-compression-in-sql-server-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.

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 INTDECLARE @NOME CHAR(50)

SET @CONTADOR = 1;SET @QUANTIDADE = 30000;

WHILE @CONTADOR <= @QUANTIDADEBEGIN

SET @NOME = ( ‘MARI’ + CHAR(64+(@CONTADOR%26)) + CHAR(64+(@CONTADOR%26)) + ‘ SANTOS ‘ + CHAR(64+(@CONTADOR%26)) + CHAR(64+(@CONTADOR%26)) );

INSERT INTO CompressaoUnicode (NOME) VALUES (@NOME); SET @CONTADOR += 1;END;

/*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

Listagem 7. Testando a compressão em colunas Unicode

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

SQL73.indb 44 25/02/2010 14:30:57


SQL SeRveR

Figura 12. Estimando os ganhos com a compressão

Impacto na performance Notas

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

40%-60% PAGE. Grande quantidade de consultas em intervalo sequencial.

Aplicação DW (Grande volume de IO)

1% PAGE. 500 usuários, 1500 Transações / sec. OLTP com algumas

consultas para relatórios.

11% 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.

2% - 3% PAGE. Aplicação OLTP.

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

tabela 3. Ganho de performance com a compressão

ConclusãoO recurso de compressão pode trazer enormes ganhos,

principalmente na economia de espaço em disco. Conside-rando 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

Vardecimalhttp://msdn.microsoft.com/en-us/library/bb508963(SQL.90).aspx

Blog do time do SQL Server Storage Enginehttp://blogs.msdn.com/sqlserverstorageengine/archive/2009/08/16/update-on-data-compression-in-sql-server-2008-rtm.aspx

Criando tabelas e índices com compressãohttp://msdn.microsoft.com/en-us/library/cc280449.aspx

Estimando os ganhos com a compressãohttp://msdn.microsoft.com/en-us/library/cc280574.aspx

SQL Server 2008 R2http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx

Links



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

Dê

seu Feedback

sob

re esta edição

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.

SQL73.indb 45 25/02/2010 14:30:58

46 SQL Magazine - Oracle Performance Diagnostics & Tuning

Ricardo Portilho [email protected]

http://portilho.profissionaloracle.com.br

Com cerca de 20 anos de experiência profis-sional, 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 - Cen-tro 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 palestran-te 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 Cer-tified Database Associate.

De que se trata o artigo?Este artigo trata do método de diagnóstico de pro-blemas 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 uti-liza um banco de dados Oracle não é satisfatório

Oracle Performance Diagnostics & Tuning

Um 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 antigosAntes 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 do-minado 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 me-ados 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

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 processa-mento, 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.




SQL73.indb 46 25/02/2010 14:30:59


OR ACLe

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 in-dicadores, 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 pos-sí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), identi-ficar 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 sis-tema 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 empre-gada 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 com-prou 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 comple-tamente da experiência do profissional, e de alguma sorte.

O método corretoAntes de executar um ajuste no SGBD Oracle, em resposta a

um problema de desempenho, devemos ter um diagnóstico cor-reto. Sem um correto diagnóstico, não faz sentido prosseguir.

O desempenho de processos computacionais é medido por ve-locidade. 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).


SGASystem Global Area (SGA) é uma terminologia usada no SGBD Oracle que repre-

senta 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 ou-tras á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 computa-cionais, 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 hou-vesse 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 Mana-ger, 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, en-quanto 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 otimiza-do 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 ViewsAs principais Dynamic Performance Views (“Visualizações Dinâ-

micas de Desempenho”) que você utilizará para fazer um diag-nó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 de-talhe é excelente, e é isto que permite um diagnóstico preciso

SQL73.indb 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 fle-xí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 so-zinha, 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_WAITFROM V$SESSION_WAIT WORDER BY SECONDS_IN_WAIT DESC;

SID EVENT SECONDS_IN_WAIT---------- --------------------------------------- ---------------53 SQL*Net message from client 10337046 SQL*Net message from client 10277544 SQL*Net message from client 10140223 SQL*Net message from client 9917338 SQL*Net message from client 9823172 SQL*Net message from client 9557458 SQL*Net message from client 9506033 SQL*Net message from client 93662101 SQL*Net message from client 9197257 SQL*Net message from client 9188832 SQL*Net message from client 88720208 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 SECON-DS_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, execu-tado várias vezes seguidas durante o período em que o sistema

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 NAME2 FROM V$EVENT_NAME3 WHERE WAIT_CLASS = ‘Idle’4 ORDER BY NAME;

NAME----------------------------------------------------------------ASM background timerauto-sqltune: wait graph updateclass slave waitcmon timerDIAG idle waitdispatcher timerEMON slave idle waitfbar timergcs remote messageges remote messageHS message to agenti/o slave waitIORM Scheduler Slave Idle Waitjobq slave waitJOX Jit Process SleepJS external jobKSV master waitLNS ASYNC archive logLNS ASYNC dest activationLNS ASYNC end of logLogical Standby Apply DelayLogMiner: builder idleLogMiner: client waiting for transactionLogMiner: generic process sleepLogMiner: preparer idleLogMiner: reader idleLogMiner: reader waiting for more redoLogMiner: slave waiting for activate messageLogMiner: waiting for processes to soft detachMRP redo arrivalparallel recovery coordinator waits for slave cleanupparallel recovery slave idle waitparallel recovery slave next changePINGpipe getPL/SQL lock timerpmon timerpool server timerPX Deq Credit: need bufferPX Deq Credit: send blkdPX Deq: Execute ReplyPX Deq: Execution MsgPX Deq: Index Merge ClosePX Deq: Index Merge ExecutePX Deq: Index Merge ReplyPX Deq: Join ACKPX Deq: kdcphc_ackPX Deq: kdcph_maiPX Deq: Msg FragmentPX Deq: Parse ReplyPX Deq: Table Q NormalPX Deq: Table Q SamplePX Deq: Txn Recovery ReplyPX Deq: Txn Recovery StartPX Deque waitPX Idle Waitrdbms ipc messageSGA: MMAN sleep for component shrinkshared server idle waitsimulated log write delaysingle-task messagesmon timerSpace Manager: slave idle waitSQL*Net message from clientStreams AQ: deallocate messages from Streams PoolStreams AQ: delete acknowledged messagesStreams AQ: emn coordinator idle waitStreams AQ: qmn coordinator idle waitStreams AQ: qmn slave idle waitStreams AQ: RAC qmn coordinator idle waitStreams AQ: waiting for messages in the queueStreams AQ: waiting for time management or cleanup tasksStreams capture: waiting for archive logStreams fetch slave: waiting for txns

SQL73.indb 48 25/02/2010 14:30:59


OR ACLe


I/OI/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 al-gum 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.

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 basi-camente 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, deixan-do a interconexão com as interfaces de entrada e saída.

SQL> SELECT SID, EVENT, SECONDS_IN_WAIT2 FROM V$SESSION_WAIT W3 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 ORDER BY SECONDS_IN_WAIT DESC;

SID EVENT SECONDS_IN_WAIT---------- --------------------------------------- ---------------758 latch free 1610 db file sequential read 1710 latch free 0172 buffer busy waits 0321 buffer busy waits 0482 buffer busy waits 0663 buffer busy waits 0473 buffer busy waits 0375 buffer busy waits 0709 buffer busy waits 0765 buffer busy waits 0739 buffer busy waits 029 db file sequential read 035 db file sequential read 043 db file sequential read 0138 db file sequential read 0161 db file sequential read 0200 db file sequential read 0727 db file sequential read 0762 db file sequential read 0766 db file sequential read 0767 db file sequential read 0772 db file sequential read 0799 db file sequential read 0455 db file sequential read 0471 db file sequential read 0498 db file sequential read 0462 db file scattered read 0

548 db file scattered read 0604 db file scattered read 0655 db file scattered read 0669 db file scattered read 0677 db file scattered read 0722 db file scattered read 0152 db file scattered read 048 db file sequential read 0266 db file scattered read 0213 db file scattered read 0223 db file scattered read 0400 db file scattered read 0405 db file scattered read 0458 db file scattered read 0352 db file sequential read 0366 db file sequential read 0374 db file sequential read 0387 db file sequential read 0408 db file sequential read 0447 db file sequential read 0668 db file sequential read 0715 db file sequential read 0504 db file sequential read 0516 db file sequential read 0580 db file sequential read 0607 db file sequential read 0221 db file sequential read 0333 db file sequential read 0322 db file sequential read 0262 db file sequential read 0257 db file sequential read 0241 db file sequential read 0232 db file sequential read 0228 db file sequential read 0175 db file sequential read 0167 db file sequential read 0658 db file sequential read 0

65 rows selected.

Listagem 3. Comando SELECT para verificar os Wait Events não ociosos das sessões atuais

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

Streams: waiting for messagesVKTM Init Wait for GSGAVKTM Logical Idle Waitwait for unread message on broadcast channelwait for unread message on multiple broadcast channelswatchdog main loopWCR: replay client notifyWCR: replay clockWCR: replay paused

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 repetida-mente 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 apa-recer 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).

Estes dois Wait Events também podem ser minimizados facilmen-te, aumentando-se o Cache (Nota DevMan 3) (parâmetro DB_CA-CHE_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

SQL73.indb 49 25/02/2010 14:31:00


SQL> SELECT NAMEFROM V$EVENT_NAMEWHERE WAIT_CLASS = ‘User I/O’ORDER BY 1;

NAME----------------------------------------------------------------BFILE readbuffer read retrycell multiblock physical readcell single block physical readcell smart file creationcell smart index scancell smart table scancell statistics gatherData file init writeDatapump dump file I/Odb file parallel readdb file scattered readdb file sequential readdb file single writedbms_file_transfer I/Odbverify readsDG Broker configuration file I/Odirect path readdirect path read tempdirect path syncdirect path writedirect path write tempexternal table misc IOexternal table readexternal table writelocal write waitLog file init writeread by other sessionsecurefile direct-read completionsecurefile direct-write completionShared IO Pool IO Completion


SQL> SELECT NAME

FROM V$EVENT_NAMEWHERE WAIT_CLASS = ‘System I/O’ORDER BY 1;

NAME----------------------------------------------------------------ARCH random i/oARCH sequential i/oArchiver slave I/Ocell smart incremental backupcell smart restore from backupcontrol file parallel writecontrol file sequential readcontrol file single writedb file parallel writeDBWR slave I/Oio donekfk: async disk IOksfd: async disk IOLGWR random i/oLGWR sequential i/oLGWR slave I/OLNS ASYNC control file txnLog archive I/Olog file parallel writelog file sequential readlog file single writeNetwork file transferrecovery readRFS random i/oRFS sequential i/oRFS writeRMAN backup & recovery I/ORMAN Disk slave I/ORMAN Tape slave I/OStandby redo I/O


Listagem 5. Comandos SELECT para verificar os Wait Events de I/O

Views da OWI, como demonstrado na Listagem 6. Neste exemplo vemos que realmente a View V$SYSTEM_EVENT nos engana-ria 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, sepa-rando 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


CacheNa área da computação, cache é um dispositivo de acesso rápido, interno a um siste-

ma, 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.

Listagem 4. Comando SELECT para verificar SQL executado por uma sessão.

SQL> SELECT A.SQL_TEXT2 FROM V$SQLTEXT A, V$SESSION B3 WHERE A.ADDRESS = B.SQL_ADDRESS4 AND A.HASH_VALUE = B.SQL_HASH_VALUE5 AND B.SID = 7546 ORDER BY PIECE;

N o t a d o D e v M a n 4PerlPerl é 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, Ma-cintosh, 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 de-senvolvimento 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.

SQL73.indb 50 25/02/2010 14:31:00


OR ACLe

SQL> SELECT EVENT, SUM(TOTAL_TIMEOUTS)2 FROM V$SESSION_EVENT3 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 EVENT10 ORDER BY 2 DESC;

EVENT SUM(TOTAL_TIMEOUTS)---------------------------------------------- -------------------latch free 73351io done 10958undo segment extension 2509enqueue 570log file switch completion 232log file sync 51buffer deadlock 28LGWR wait for redo copy 11log buffer space 3HS message to agent 0SQL*Net break/reset to client 0SQL*Net more data from client 0control file sequential read 0db file parallel write 0db file sequential read 0direct path read 0process startup 0log file single write 0log file sequential read 0log file parallel write 0library cache load lock 0imm op 0direct path write (lob) 0direct path write 0direct path read (lob) 0wait list latch free 0row cache lock 0refresh controlfile command 0db file single write 0db file scattered read 0db file parallel read 0control file parallel write 0SQL*Net more data to client 0async disk IO 0buffer busy waits 0

35 rows selected.

SQL> SELECT EVENT, AVERAGE_WAIT, TOTAL_TIMEOUTS2 FROM V$SYSTEM_EVENT3 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 ORDER BY TOTAL_TIMEOUTS DESC;

EVENT AVERAGE_WAIT TOTAL_TIMEOUTS-------------------------------------- ------------ --------------undo segment extension 0 8707775latch free 2 1250783enqueue 164 240635PX Deq Credit: send blkd 86 138120PX Idle Wait 194 30457PL/SQL lock timer 202 15139io done 1 10958PX Deq: Table Q Normal 2 6152virtual circuit status 2918 3887

PX Deq Credit: need buffer 9 2536dispatcher timer 5815 1943buffer deadlock 0 1927log file switch completion 12 850PX Deq: Execute Reply 3 651log file sync 1 208log buffer space 24 121inactive session 98 109PX Deq: Signal ACK 0 55buffer busy waits 0 50slave TJ process wait 1 27index block split 1 20LGWR wait for redo copy 0 11local write wait 11 7kksfbc child completion 2 5reliable message 66 2control file heartbeat 391 1wait list latch free 1 0process startup 3 0async disk IO 0 0sbtinit 15 0sbtbackup 80 0direct path write (lob) 0 0direct path read (lob) 0 0direct path write 0 0direct path read 0 0db file parallel read 2 0db file parallel write 3 0db file single write 0 0db file scattered read 0 0db file sequential read 1 0HS message to agent 0 0SQL*Net break/reset to client 0 0SQL*Net more data from dblink 0 0SQL*Net message from dblink 0 0SQL*Net more data from client 3 0SQL*Net more data to dblink 0 0SQL*Net more data to client 0 0SQL*Net message to dblink 0 0single-task message 4 0PX Deq: Table Q Get Keys 1 0PX Deq: Parse Reply 0 0PX Deq: Msg Fragment 0 0PX Deq: Join ACK 0 0BFILE internal seek 0 0BFILE read 0 0BFILE open 0 0BFILE get length 0 0BFILE closure 0 0library cache load lock 8 0library cache pin 3 0row cache lock 0 0switch logfile command 3 0log file parallel write 0 0log file single write 0 0log file sequential read 3 0refresh controlfile command 0 0control file parallel write 0 0control file single write 1 0control file sequential read 0 0sbtclose2 1181 0sbtend 0 0sbtinfo2 24 0sbtinit2 0 0sbtremove2 6 0sbtwrite2 3 0imm op 0 0

76 rows selected.

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

Listagem 7. Comandos para criar a tabela de testes.

C:\Users\Ricardo>set ORACLE_SID=TEST11GR1C:\Users\Ricardo>set ORACLE_HOME=c:\oracle\product\11.1.0\db_1C:\Users\Ricardo>sqlplus SCOTT/TIGERSQL*Plus: Release 11.1.0.7.0 - Production on Seg Ago 31 08:32:51 2009Copyright (c) 1982, 2008, Oracle. All rights reserved.Conectado a:Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionSQL> CREATE TABLE PESSOAS (NOM_PESSOA VARCHAR2(255));Tabela criada.SQL> EXIT;

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

SQL73.indb 51 25/02/2010 14:31:00


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 neces-sá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 com-pilaçõ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.

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

C:\Users\Ricardo>sqlplus / AS SYSDBA

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 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SET PAGESIZE 1000SQL> SET LINESIZE 210SQL> SELECT SIDFROM V$SESSIONWHERE USERNAME = ‘SCOTT’;

SID———-137

SQL> SELECT EVENT, AVERAGE_WAIT, TOTAL_TIMEOUTSFROM V$SESSION_EVENTWHERE SID = 137ORDER BY AVERAGE_WAIT;

EVENT ------------------------AVERAGE_WAIT TOTAL_TIMEOUTS----------------------------- ------------ --------------latch: row cache objects 0 0SQL*Net message to client 0 0SQL*Net message from client ,02 0latch: shared pool ,02 0db file single write ,03 0events in waitclass Other ,04 0control file sequential read ,09 0control file parallel write ,1 0log file sync ,38 0Data file init write ,39 0db file sequential read ,5 0db file scattered read 2,49 0log file switch completion 4,84 0


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

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, ‘<person.tsv’);while () { my $linha = $_; chomp $linha; my @pessoa = split(/\t/, $linha); if ($pessoa[0] eq ‘name’) {next;}; $pessoa[1] =~ s/\/guid\///; $pessoa[0] =~ s/\’//g; my $sql = “INSERT INTO pessoas (nom_pessoa) VALUES (‘$pessoa[0]’)”; $sth = $dbh->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.pl10:3410:44INSERTs finalizados. Pressione ENTER.\n

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ãoNeste artigo abordamos como diagnosticar e solucionar proble-

mas de desempenho de forma precisa, utilizando a Oracle Wait Interface. Embora a OWI seja o único método que você precisa co-nhecer 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.

SQL73.indb 52 25/02/2010 14:31:01


OR ACLe


Redo LogsNo 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, tam-bé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 Chan-ge Number (“Número de Alteração de Sistema”), ou SCN.

Listagem 11. Comandos para adicionar Redo Logs

SQL> COL MEMBER FORMAT A70SQL> SELECT * FROM V$LOGFILE;

GROUP# STATUS TYPE MEMBER IS_------ ------ ------ -------------------------------------- ---3 ONLINE C:\ORACLE\ORADATA\TEST11GR1\REDO03.LOG NO2 ONLINE C:\ORACLE\ORADATA\TEST11GR1\REDO02.LOG NO1 ONLINE C:\ORACLE\ORADATA\TEST11GR1\REDO01.LOG NO

SQL> ALTER DATABASEADD LOGFILE ‘C:\ORACLE\ORADATA\TEST11GR1\REDO04.LOG’SIZE 1G;

Banco de dados alterado.





Listagem 12. Comando para executar o Script Perl



SQL> SELECT EVENT, AVERAGE_WAIT, TOTAL_TIMEOUTS2 FROM V$SESSION_EVENT3 WHERE SID = 1424 ORDER BY AVERAGE_WAIT;

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


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, ‘<person.tsv’);while (){my $linha = $_;chomp $linha;my @pessoa = split(/\t/, $linha);if ($pessoa[0] eq ‘name’) {next;};$pessoa[1] =~ s/\/guid\///;$pessoa[0] =~ s/\’//g;my $sql = “INSERT INTO pessoas (nom_pessoa) VALUES (?)”;$sth = $dbh->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.



SQL> SELECT EVENT, AVERAGE_WAIT, TOTAL_TIMEOUTS2 FROM V$SESSION_EVENT3 WHERE SID = 1424 ORDER BY AVERAGE_WAIT;

EVENT AVERAGE_WAIT TOTAL_TIMEOUTS--------------------------- ------------ --------------latch: cache buffers chains 0 0SQL*Net message to client 0 0events in waitclass Other ,01 0SQL*Net message from client ,02 0log file sync ,64 0

Wikipediahttp://www.wikipedia.org/

Oracle On-Line Documentationhttp://tahiti.oracle.com/

Referências



Dê

seu Feedback

sob

re esta edição

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

SQL73.indb 53 25/02/2010 14:31:01

54 SQL Magazine - Usando CDC e Trigger em Auditorias no SQL Server 2008

Tulio [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ên-cia 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/

De que se trata o artigo?Nesse artigo será demonstrado o recurso Change Data Capture (CDC) presente no MS SQL Server 2008 e como utilizar Trigger para registrar as alte-rações ocorridas nas tabelas. Abordaremos a me-lhor 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, for-necendo informações importantes para uma auditoria.

Usando CDC e Trigger em Auditorias no SQL Server 2008Conhecendo o recurso Change Data Capture

Será abordado nesse artigo o Chan-ge 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 armaze-nar 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.

Exemplo usando TriggerO SQL Server tem três tipos de trigger:

DML, DDL e de Logon. Utilizaremos as triggers DML, que serão disparadas




SQL73.indb 54 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 Cliente_Logtr_cliente_updatetr_cliente_deletetr_cliente_insert

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 ocor-ridas 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.

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 le-tra 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 1. Código de criação da base de dados e das tabelas.

use mastergo

-- Criar a base de dadoscreate database Testegouse Testego

-- Criar a tabela ClienteCREATE TABLE dbo.Cliente( ID_Cliente int NOT NULL IDENTITY (1, 1), Nome varchar(50) NOT NULL, Idade int NOT NULL) GOALTER TABLE dbo.Cliente -- Adiciona ID_Cliente como chave primaria ADD CONSTRAINT PK_Cliente_1 PRIMARY KEY CLUSTERED (ID_Cliente)GO

-- Criar a tabela Cliente_LogCREATE 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)) GOALTER TABLE dbo.Cliente_Log -- Adiciona LOG_ID como chave primaria ADD CONSTRAINT PK_Cliente_Log_1 PRIMARY KEY CLUSTERED (LOG_ID)GO

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

SQL73.indb 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 interna-mente 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, ve-rificamos 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 retorna-das 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 in-serir 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 Clien-te_Log que os campos Log_Usuario, Log_Data e Log_Ope-raçã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 Testego

-- Mostra o conteudo das tabelas antes do Insertselect * from Clienteselect * from Cliente_Log

-- Insere 3 registrosinsert 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 Insertselect * from Clienteselect * 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

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 CDCO 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 sal-vas primeiramente nos arquivos de log e posteriormente na tabela de origem.

Através de um processo de captura, as informações sobre as alte-raçõ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 Fornece-dor, 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.

Listagem 4 . Excluir e alterar registros na tabela Cliente

USE Testego

-- Mostra o conteudo das tabelas antes do Delete e Updateselect * from Clienteselect * from Cliente_Log

-- Excluie um registrodelete Cliente where ID_Cliente = 2

-- Altera um registroupdate Cliente set Nome = ‘Fulana’ where ID_Cliente = 3

-- Mostra o conteudo das tabelas depois do Delete e Updateselect * from Clienteselect * from Cliente_Log

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

SQL73.indb 56 25/02/2010 14:31:05


SQL SeRveR

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, deve-mos 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.

Figura 5. Funcionamento do CDC

Listagem 6. Código para habilitar o CDC e incluir a tabela.

use TesteCDCgo

-- Habilitando a base de dadosexec sys.sp_cdc_enable_dbgo

-- Adicionando a tabelaexec sp_cdc_enable_table @source_schema = ‘dbo’, @source_name = ‘Fornecedor’, @role_name = ‘CDC_Role’, @supports_net_changes = 1Go

Quando executamos a SP sp_cdc_enable_db para habilitar a base de dados para utilização do CDC, são criados os se-guintes 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 in-formaçõ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,

Listagem 5. Código da criação da base e tabela.

use mastergo

-- Criar a base de dadoscreate database TesteCDCgouse TesteCDCgo

-- Criar a tabela FornecedorCREATE TABLE dbo.Fornecedor( ID_Fornecedor int NOT NULL IDENTITY (1, 1), Nome varchar(50) NOT NULL, Telefone int NOT NULL) GOALTER TABLE dbo.Fornecedor -- Adiciona chave primariaADD CONSTRAINT PK_Fornecedor_1 PRIMARY KEY CLUSTERED (ID_Fornecedor)GO

Figura 6. Resultado da verificação se o CDC está habilitado

Listagem 7. Verificação se o CDC esta habilitado.

use mastergo

select name, is_cdc_enabledfrom sys.databaseswhere is_cdc_enabled = 1

-- Tabelause TesteCDCgo

select name, is_tracked_by_cdcfrom sys.tableswhere is_tracked_by_cdc = 1

SQL73.indb 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 Forne-cedor 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 8. Tabela criada pela SP sp_cdc_enable_table

Figura 7. Objetos criados quando habilitado o CDC

Listagem 8. Alterando o tempo de retenção

use TesteCDCgo

exec sp_cdc_change_job @job_type=’cleanup’, @retention=52494800go

Listagem 9. Operações na tabela de fornecedor.

USE TesteCDCgo

-- Mostra o conteudo das tabelas antes do Insertselect * from Fornecedorselect * from cdc.dbo_Fornecedor_CT

-- Insere 3 registrosinsert 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 parteselect * from Fornecedorselect * from cdc.dbo_Fornecedor_CT

Para realizar consultas nas tabelas de sistemas termina-das 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 TesteCDCgo

declare @from_lsn binary(10), @to_lsn binary(10) set @from_lsn = sys.fn_cdc_get_min_lsn(‘dbo_Fornecedor’) -- Menor dataset @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’);

Se observarmos a Figura 10, veremos que o primeiro re-sultado (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.

Figura 9. JOB criado quando incluído a tabela

SQL73.indb 58 25/02/2010 14:31:05


SQL SeRveR

Para visualizar a data que está no campo __$start_lsn po-demos 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 apresenta-mos 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 pri-meiros 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_For-necedor, Nome e Telefone).

O quarto e quinto registro da tabela cdc.dbo_Fornece-dor_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 re-gistra 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.

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

Figura 10. Conteúdo das tabelas Fornecedor e dbo_Fornecedor_CT

Listagem 11. Alteração e exclusão de registros.

USE TesteCDCgo

-- Alteracaoupdate dbo.Fornecedor set Nome = ‘Tulio Rosa’ where ID_Fornecedor = 1

-- Exclusaodelete dbo.Fornecedor where ID_Fornecedor = 2

-- Mostra o conteudo das tabelas depois da alteracao e exclusao-- Esperar 5 segundos antes de executar essa parteselect * from Fornecedorselect * from cdc.dbo_Fornecedor_CT

Listagem 12 . Adicionando o campo Estado.

use TesteCDCgo

-- Adicionando o campo estadoalter table dbo.Fornecedor add Estado char(2)GO

SQL73.indb 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 (ins-truçõ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 TesteCDCgo

select * from Fornecedorselect * from cdc.dbo_Fornecedor_CT

select OBJECT_NAME(source_object_id) as ‘Tabela’, OBJECT_NAME(OBJECT_ID) as ‘Tabela_CT’, ddl_command as ‘DDL’, ddl_time as ‘Data’from cdc.ddl_history

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

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 TesteCDCgo

-- Retirando a tabelaexec sp_cdc_disable_table @source_schema = ‘dbo’, @source_name = ‘Fornecedor’, @capture_instance = ‘all’go

-- Adicionando a tabelaexec sp_cdc_enable_table @source_schema = ‘dbo’, @source_name = ‘Fornecedor’, @role_name = ‘CDC_Role’, @supports_net_changes = 1go

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

use TesteCDCgo

-- InserindoInsert Fornecedor(Nome, Telefone,Estado) values (‘Fabiola’,5555,’GO’)

-- Alteracaoupdate dbo.Fornecedor set Estado = ‘DF’ where ID_Fornecedor = 3

Para melhorar a visualização do histórico (tabelas _CT), po-demos 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 13. Resultado da alteração do novo campo

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

SQL73.indb 60 25/02/2010 14:31:06


SQL SeRveR

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.

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

create proc cdc.s_historico @tabela varchar(100), @data_inicial varchar(23), @data_final varchar(23)asexec(‘select sys.fn_cdc_map_lsn_to_time(__$start_lsn) as Data, case __$operation when 1 then ‘’D’’ when 2 then ‘’I’’ when 3 then ‘’UA’’ when 4 then ‘’UD’’ end as Operacao, *into #tempfrom cdc.’ + @tabela + ‘ where sys.fn_cdc_map_lsn_to_time(__$start_lsn) between ‘’’ + @data_inicial + ‘’’ and ‘’’ + @data_final + ‘’’ alter table #temp drop column __$start_lsnalter table #temp drop column __$operationalter table #temp drop column __$update_maskalter table #temp drop column __$seqvalalter table #temp drop column __$end_lsn

select *from #temporder by Data‘)

Listagem 17. Utilização da SP cdc.s_historico.

use TesteCDCgoexec cdc.s_historico ‘dbo_Fornecedor_CT’,’2009-11-01’,’2009-11-30’go

A Tabela 2 mostra o significado dos códigos do campo Ope-raçã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 CDCComo 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,

Figura 14. Resultado da SP cdc.s_historico



Dê

seu Feedback

sob

re esta edição

SQL Server Developer Center

http://msdn.microsoft.com/en-us/library/cc645937.aspx

http://msdn.microsoft.com/en-us/library/ms189799.aspx

Links

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 mo-mentos críticos, isto é, onde esteja ocorrendo um grande nu-mero 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.

ConclusãoDemonstramos nesse artigo duas formas de armazenar infor-

maçõ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 de-monstrados 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 desempe-nho do banco seja prejudicado.

SQL73.indb 61 25/02/2010 14:31:06

62 SQL Magazine - Desafio SQL

Wagner [email protected]

Atua a mais de 15 anos na área TI, parti-cularmente com Business Intelligence. En-genheiro 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.

De que se trata o artigo?Desenvolvimento de soluções para problemas coti-dianos enfrentados por DBAs e desenvolvedores de aplicações para banco dados.

Para que serve?Fornecer conceitos de utilização de funcionalidades do padrão SQL ANSI na resolução de problemas en-frentados 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, gati-lhos e agendamentos (ou stored procedures, triggers e jobs).

Desafio SQL

E voltamos para acompanhar mais um capítulo da história da em-presa fictícia ItsMyBusiness (leia

nota DevMan 1).A empresa está realizando um pro-

jeto 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.

E a empresa ItsMyBusiness continua crescendo. E seu

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] 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




SQL73.indb 62 25/02/2010 14:31:07


DeSAFIO SQL

Figura 1. Modelo de dados simplificado da empresa ItsMyBusiness

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

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

SQL73.indb 63 25/02/2010 14:31:08



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 despre-tensioso (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, espe-cialmente 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 pedi-do até o momento em que se entregam os produtos ao cliente.

Resposta do desafioVamos 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 va-mos 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 tra-duzir 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.

tblPedidoHistorico�

codPedido

�

int

�

codProduto

�

int

�

codPedidoStatus

�

int

�

DataEvento

�

datetime

�

Observacao

�

varchar(50)

� Column Name� Data Type� Allow Nulls

Figura 3. Estrutura da tabela dbo.tblPedidoHistorico

Para simplificar, deixaremos o campo OBSERVACAO inal-terado, aproveitando que ele aceita valores nulos. Então, nos-sa 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).

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 siteEste 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 alte-raçã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 INSER-TED (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 1. Criação do procedimento armazenado spPedidoHistoricoINSERT

1 CREATE PROCEDURE dbo.spPedidoHistoricoINSERT 2 (@Pedido INT, @Produto INT, @Status INT)3 AS4 INSERT INTO dbo.tblPedidoHistorico 5 VALUES(@Pedido, @Produto, @Status, GETDATE() , NULL) ;

Listagem 2. Gatilho dbo.trgPedDet_ins

1 CREATE TRIGGER dbo.trgPedDet_ins2 ON dbo.tblPedidoDetalhe3 AFTER INSERT4 AS5 BEGIN6 --usaremos valores da “tabela” virtual INSERTED7 DECLARE @PedidoNovo INT8 DECLARE @ProdutoNovo INT9 10 SELECT @PedidoNovo = codPedido FROM INSERTED11 SELECT @ProdutoNovo = codProduto FROM INSERTED12 -- codigo status 1 = pedido aceito13 EXEC dbo.spPedidoHistoricoINSERT @PedidoNovo,@ProdutoNovo,1 14 END ;

SQL73.indb 64 25/02/2010 14:31:08


DeSAFIO SQL

Status 2 e/ou 102: cartão de crédito validado/rejeitadoA 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 spPedidoHisto-ricoINSERT, 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/recusadoNa realidade, estes dois passos são “simbólicos”, porque o nosso

fluxo de operação (Figura 2) prevê que o pedido passa automati-camente 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 estoqueO próximo passo é verificar se o produto está disponível em es-

toque. 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 quanti-dade 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_ins2 ON dbo.tblPedidoHistorico3 AFTER INSERT4 AS5 BEGIN6 --usaremos valores da “tabela” virtual INSERTED7 DECLARE @PedidoNovo INT8 DECLARE @ProdutoNovo INT910 SELECT @PedidoNovo = codPedido FROM INSERTED11 SELECT @ProdutoNovo = codProduto FROM INSERTED12 13 IF (SELECT codPedidoStatus FROM INSERTED) = 214 EXEC dbo.spPedidoHistoricoINSERT @PedidoNovo,@ProdutoNovo,315 ELSE16 BEGIN17 IF (SELECT codPedidoStatus FROM INSERTED) = 10218 EXEC dbo.spPedidoHistoricoINSERT 19 @PedidoNovo, @ProdutoNovo , 10320 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.

O método mais simples de fazermos este monitoramen-to 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 mes-mo 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 arti-gos mostrando como criar estes jobs e não vou me estender nesta questão. A t ítulo de exemplo, reco-mendo o artigo da MSDN “Como criar um trabalho” (http://msdn.microsoft.com/pt-br/library/ms190268.aspx).


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 funcio-nam de maneira muito parecida com o SQL Server.

SQL73.indb 65 25/02/2010 14:31:09


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.

Listagem 4. Procedimento spPedidoSaldo_sel

1 CREATE PROCEDURE dbo.spPedidoSaldo_sel 2 AS3 BEGIN4 DECLARE @Pedido INT5 DECLARE @Produto INT6 DECLARE @QTDPedida NUMERIC(11,2)7 DECLARE @QTDSaldo NUMERIC(11,2)8 9 --so pedidos cujo ultimo status é igual a 310 DECLARE curPedidoItem3 CURSOR FAST_FORWARD FOR11 SELECT codPedido, codProduto12 FROM dbo.tblPedidoHistorico13 GROUP BY codPedido, codProduto14 HAVING MAX(codPedidoStatus) = 3 15 OPEN curPedidoItem3 16 FETCH NEXT FROM curPedidoItem3 17 INTO @Pedido, @Produto18 WHILE @@FETCH_STATUS = 0 19 BEGIN20 --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 pedida25 SELECT @QTDPedida = QTD FROM dbo.tblPedidoDetalhe26 WHERE codPedido = @Pedido AND codProduto = @Produto27 28 IF @QTDSaldo >= @QTDPedida29 EXEC dbo.spPedidoHistoricoINSERT @Pedido,@Produto,430 31 FETCH NEXT FROM curPedidoItem3 32 INTO @Pedido, @Produto33 END 34 CLOSE curPedidoItem3 35 DEALLOCATE curPedidoItem3 36 END

Listagem 5. Gatilho dbo.trgNFDet_ins

1 CREATE TRIGGER dbo.trgNFDet_ins2 ON dbo.tblPedidoDetalhe3 AFTER INSERT4 AS5 BEGIN6 --usaremos valores da “tabela” virtual INSERTED7 DECLARE @PedidoNovo INT8 DECLARE @ProdutoNovo INT9 10 SELECT @PedidoNovo = codPedido 11 FROM dbo.tblNotaFiscal12 WHERE codNotaFiscal = (SELECT codNotaFiscal FROM INSERTED)13 14 SELECT @ProdutoNovo = codProduto 15 FROM dbo.tblLote16 WHERE codLote = (SELECT codLote FROM INSERTED)17 -- codigo status 5 = nota fiscal emitida13 EXEC dbo.spPedidoHistoricoINSERT @PedidoNovo,@ProdutoNovo,5 14 END ;

Listagem 6. Atualização do gatilho dbo.trgPedHist_ins

1 ALTER TRIGGER dbo.trgPedHist_ins2 ON dbo.tblPedidoHistorico3 AFTER INSERT4 AS5 BEGIN6 --usaremos valores da “tabela” virtual INSERTED7 DECLARE @Pedido INT8 DECLARE @Produto INT910 SELECT @Pedido = codPedido FROM INSERTED11 SELECT @Produto = codProduto FROM INSERTED12 13 IF (SELECT codPedidoStatus FROM INSERTED) = 214 EXEC dbo.spPedidoHistoricoINSERT @Pedido, @Produto, 315 ELSE16 BEGIN17 IF (SELECT codPedidoStatus FROM INSERTED) = 10218 BEGIN19 EXEC dbo.spPedidoHistoricoINSERT @Pedido, @Produto, 10320 EXEC dbo.spPedidoHistoricoINSERT @Pedido, @Produto, 821 END22 ELSE23 IF (SELECT codPedidoStatus FROM INSERTED) = 724 EXEC dbo.spPedidoHistoricoINSERT @Pedido, @Produto, 825 END26 END ;



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

Dê

seu Feedback

sob

re esta edição

Status 5: nota fiscal emitidaApós o serviço de faturamento (que não cabe aqui entrar-

mos 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 fis-cal, 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 transportadoraO 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 spPe-didoHistoricoINSERT passando o status 6 como um dos argumentos.

Status 7: pedido entregueMais 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 fechadoO 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), acres-centando 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 apre-sentados aqui são muito usados no dia-a-dia e acredito que lhes serão úteis.

SQL73.indb 66 25/02/2010 14:31:10


DeSAFIO SQL

SQL73.indb 67 25/02/2010 14:31:13


SQL73.indb 68 25/02/2010 14:31:17

sql-magazine 073 tuning do oracle

Documents

sql magazine

vdeo aulas

apache derby

projeto de banco

net magazine

artigo por artigo

size databaseento

dados38 compresso de