G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
Transcript of G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 1/85
Sistemas de Bases de Dados2009/2010
DB2CONCEITOS E ANÁLISE DO SISTEMA
João Miguel Ramos 31222José Henrique Rio 31220
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 2/85
ÍNDICE DE CONTEÚDOS
Introdução.............................................................................................................................................51 - Evolução e Aplicabilidade do Sistema............................................................................................6
2 – Notas Sobre a Instalação................................................................................................................92.1 Antes da Instalação...................................................................................................................9Requisitos de Disco.....................................................................................................................9Requisitos de Memória..............................................................................................................9
2.2 Instalação ...............................................................................................................................102.3 Depois da Instalação...............................................................................................................10
3 – Cobertura do Standard SQL.........................................................................................................103.1 Data Definition Language.......................................................................................................11
Tipos de Dados..........................................................................................................................11Identificadores...........................................................................................................................11Create Table...............................................................................................................................12
Create View...............................................................................................................................13Create Index..............................................................................................................................14Create Trigger...........................................................................................................................14Alter Table.................................................................................................................................14Drop..........................................................................................................................................15
3.2 Data Manipulation Language..................................................................................................15Insert..........................................................................................................................................15Update.......................................................................................................................................15Delete........................................................................................................................................16
3.3 Data Query Language.............................................................................................................16Select.........................................................................................................................................16
4 – Armazenamento e Estrutura de Ficheiros.....................................................................................164.1 Princípios Básicos da Arquitectura DB2................................................................................174.2 Table Spaces (nível lógico).....................................................................................................18
Tipos de Table Space.................................................................................................................19Gestão de Table Spaces.............................................................................................................19
4.3 Table Spaces (nível físico)......................................................................................................20Factores de desempenho do armazenamento em disco.............................................................20
4.3 Armazenamento de Tabelas e Índices.....................................................................................22Tabelas Standard.......................................................................................................................22Tabelas Multidimensional Clustering ......................................................................................24
4.4 Particionamento de Tabelas....................................................................................................26Benefícios do particionamento de tabelas.................................................................................27Criação de partições..................................................................................................................27
4.5 Buffer Pool..............................................................................................................................285 – Indexação e Hashing.....................................................................................................................30
5.1 Índices.....................................................................................................................................30Estrutura de Índices...................................................................................................................30Usar índices relacionais para melhorar a performance.............................................................31Gestão de índices nas tabelas standard......................................................................................31Outras opções da declaração CREATE INDEX........................................................................32Clustered indexes......................................................................................................................33
Índices em tabelas particionadas...............................................................................................33
DB2 - Conceitos e Análise do Sistema
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 3/85
Índices Bitmap..........................................................................................................................345.2 Hashing...................................................................................................................................35
6 – Processamento e Optimização de Perguntas................................................................................36
6.1 Introdução à Optimização no DB2.........................................................................................36Optimizar o acesso a dados no DB2.........................................................................................36Hints..........................................................................................................................................37
6.2 Tipos de Junções.....................................................................................................................38 Nested-loop join........................................................................................................................38Merge join.................................................................................................................................39Hash join...................................................................................................................................39
6.3 Estratégias de Optimização de Junções..................................................................................40Star-Schema Joins.....................................................................................................................40Composite tables.......................................................................................................................41Efeitos da ordenação.................................................................................................................41
6.4 Estratégias de Optimização de Queries...................................................................................42Estratégias de optimização dentro de uma partição..................................................................42Estratégias de optimização para tabelas MDC..........................................................................44
6.5 Materialized Query Tables.......................................................................................................456.6 SQL Explain.............................................................................................................................45
Ferramentas de análise de planeamento....................................................................................467 – Transacções e Controlo de Concorrência....................................................................................49
7.1 Introdução às Transacções.......................................................................................................497.2 Concorrência em Transacções.................................................................................................50
Tipos de Locks..........................................................................................................................50Problemas na ausência de controlo de concorrência.................................................................51
7.3 Níveis de Isolamento em Transacções.....................................................................................55Uncommitted Read...................................................................................................................55Cursor Stability.........................................................................................................................55Read Stability............................................................................................................................56Repeatable Read........................................................................................................................56Comparação de níveis de isolamento........................................................................................57
7.4 Gestão de Locks.......................................................................................................................59Lock escalation.........................................................................................................................59Monotorização de locks............................................................................................................60Atribuição de Locks..................................................................................................................60Causas e detecção de deadlocks................................................................................................61Granularidade de Locks............................................................................................................61Comportamento do locking em tabelas particionadas..............................................................62
7.4 Save Points...............................................................................................................................637.5 Processamento de Logs de Transacções.................................................................................64
8 – Suporte para Bases de Dados Distribuídas.................................................................................658.1 Suporte à Fragmentação...........................................................................................................668.2 Two-Phase Commit.................................................................................................................67
Recuperação de erros durante o two-phase commit..................................................................708.3 Mecanismos de Replicação.....................................................................................................708.4 Processamento Distribuído de Perguntas................................................................................72
8.5 Controlo de Concorrência.......................................................................................................72
DB2 - Conceitos e Análise do Sistema 3
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 4/85
8.6 Data Suporte de Acesso a Dados em SGBD's Heterogéneos..................................................739 – Outras Características do Sistema................................................................................................73
9.1 DB2 pureXML.........................................................................................................................74
Utilizar XML com base de dados..............................................................................................74Base de dados XML..................................................................................................................74XPath.........................................................................................................................................76XQuery......................................................................................................................................76Inserir documentos XML..........................................................................................................76
9.2 SQL PL Stored Procedures......................................................................................................769.3 Segurança.................................................................................................................................77
Autoridade DBADM.................................................................................................................77Grupo PUBLIC.........................................................................................................................78
9.4 InfoSphere Warehouse............................................................................................................78Servidor de dados......................................................................................................................79
Melhoramentos da concepção e optimização do processamento OLAP..................................799.5 Suporte ao Desenvolvimento...................................................................................................80Desenvolvimento de aplicações em Java..................................................................................80Desenvolvimento de aplicações em PHP..................................................................................81
10 – Anexo..........................................................................................................................................8310.1 Experiências Adicionais com o Sistema................................................................................83
Definir uma PRIMARY KEY ..................................................................................................83Explain Plans.............................................................................................................................84Melhorando a Performance com o Design Advisor.................................................................85
Níveis de Isolamento e Locks...................................................................................................8611 – Bibliografia.................................................................................................................................87
DB2 - Conceitos e Análise do Sistema 4
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 5/85
INTRODUÇÃO
IINTRODUÇÃONTRODUÇÃO
Este trabalho encontra-se inserido no âmbito da cadeira Sistemas de Bases de Dados do Mestradoem Engenharia Informática da FCT/UNL. É objectivo do mesmo e dos autores apresentar um estudo doSistema de Gestão de Bases de Dados DB2 relacionando os conceitos adquiridos na cadeira com umexemplo real da sua implementação.
Neste trabalho começaremos por apresentar uma inicial perspectiva sobre o nascimento e evoluçãodo sistema ao longo dos anos passando depois para abordagens mais detalhadas daquelas que são ascaracterísticas básicas dum Sistema de Base de Dados em termos do armazenamento dos dados, oprocessamento de perguntas e a sua optimização, a gestão de transacções e da concorrência, bem comomais a frente, de funcionalidades adicionais como por exemplo o suporte para XML nativo.
DB2 - Conceitos e Análise do Sistema 5
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 6/85
1 - EVOLUÇÃO E APLICABILIDADE DO SISTEMA
1 - E1 - EVOLUÇÃOVOLUÇÃO EE AAPLICABILIDADEPLICABILIDADE DODO SSISTEMAISTEMA
O DB2 é um dos Sistemas de Gestão de Bases de Dados Relacionais (SGDBR) produzido pela linha deSoftware de Gestão de Informação da IBM. Existem diferentes versões do DB2 que correm desde numsimples PDA, até às mais potentes mainframes, funcionando em servidores baseados em Unix, Windows,Linux e z/OS (sistema operativo para mainframes criado pela IBM). Para além do DB2, a IBM possui outroSGDBR, o Informix, que adquiriu no ano de 2001, tendo incluindo no DB2 alguns dos conceitos deste último.
DB2, considerado por muitos o primeiro produto de bases de dados a utilizar SQL, foi o nome dado
para o SGDBR que a IBM lançou em 1983, baseado em SQL/DS para as suas mainframes, mas teve numa
fase inicial o nome de System R, quando começou em 1978.
O projecto DB2 teve início nos anos 70 pela mão de Edgar Frank Codd que, trabalhando para a IBM,
descreveu a teoria das Bases de Dados Relacionais e publicou-a em Junho de 1970. Para aplicar o seu
modelo, Codd criou uma linguagem de bases de dados relacionais que apelidou de Alpha. Porém, a IBM não
tinha grande esperança no potencial das suas ideias, deixando a implementação para grupo de
programadores que, não estando sob a supervisão deste, acabaram por violar inúmeras das ideias
fundamentais da teoria de Codd. O resultado foi a Structured English QUEry Languange ou SEQUEL. Mais
tarde, aquando do lançamento do seu primeiro produto de bases de dados relacionais, a IBM queria ter
uma sub-linguagem de linhagem comercial, denominando-a por SQL, de forma a diferenciá-la da SEQUEL.
Durante muitos anos o DB2 foi feito exclusivamente para correr nas mainframes da IBM. Mais tarde,
na década de 90, este sistema foi introduzido para outras plataformas de servidores, incluindo OS/2, Unix e
Windows, sendo mais tarde disponibilizado para Linux e para os PDAs. A inspiração para os detalhes de
implementação do DB2 veio da linguagem DL/1 da IBM e do Sistema de Gestão de Informações da empresa.
Com o passar do tempo, a IBM foi adaptando o seu sistema às realidades do mundo moderno, adicionando
a funcionalidade de bases de dados distribuídas, permitindo o acesso a uma base de dados remota, numa
LAN.
Perante uma concorrência feroz, a IBM admitiu a complexidade intransponível do gestor da base de
dados, tendo reescrito completamente o seu software nos laboratórios de Toronto. A partir desta altura, as
versões para mainframes e servidores ficaram escritas em linguagens diferentes (PL/S para as mainframes e
C++ para os servidores), embora partilhassem muitas funcionalidades similares e utilizassem uma
arquitectura comum para a optimização do SQL (o Starburst Optimizer).
Em meados dos anos 90, a IBM lançou a implementação agrupada do DB2, a que chamou DB2
Parallel Edition, correndo inicialmente em AIX. Esta edição permitia escalabilidade fornecendo uma
arquitectura “shared nothing” que permitia a auto-suficiência de cada nó e em que uma grande base de
dados fosse particionada por vários servidores de DB2, interligados com comunicações de alta velocidade.
Esta edição do DB2 era suportada por servidores com plataformas Linux, Unix e Windows e acabou por ser
renomeado DB2 Extended Enterprise Edition (EEE). Actualmente, este produto é conhecido por Database
Partitioning Feature (DPF) e é vendido como um módulo do seu DB2 Enterprise.
Em meados do ano de 2006, a IBM anunciou o “Viper”, nome de código do DB2 9 para aplicações
distribuídas e para z/OS. Com esta versão, a empresa garante que o novo DB2 é a primeira Base de Dados
DB2 - Conceitos e Análise do Sistema 6
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 7/85
1 - EVOLUÇÃO E APLICABILIDADE DO SISTEMA
Relacional que armazena o XML nativo. Outras novidades incluem o desenvolvimento baseado em OLTP
(processamento de transacções em tempo-real) para as aplicações distribuídas, o desenvolvimento baseado
em Business Intelligence e Data Warehousing (hardware/software pré-configurado, denominada
BCU(Balanced Configuration Unit))para o z/OS, mais e melhores recursos de auto-configuração e auto-gestão, adição de recursos para correr em plataformas de 64 bits (especialmente para z/OS), melhoria na
performance de armazenamento estruturado para z/OS e a continuação da padronização do vocabulário da
linguagem SQL entre z/OS e outras aplicações distribuídas. Passado um ano, o DB2 conheceu a versão 9.5.
Para as plataformas distribuídas, esta actualização apresentava 3 pontos-chave: Gestão simplificada,
fiabilidade de negócios críticos e facilidade no desenvolvimento de XML.
Em Junho de 2009, a IBM anunciou o “Cobra”, o nome de código do DB2 9.7 para LUW (plataformas
Linux, Unix e Windows). Esta versão adiciona compressão de indexes de base de dados, tabelas temporárias
e suporte de grandes objectos. Para além disto, suporta também dados em XML nativo com
particionamento por hash, particionamento de tabelas e multi-dimensional clustering. Estasfuncionalidades de XML nativo permitem aos utilizadores que trabalhem directamente sobre XML em
ambientes de Data Warehouse. Para além disto, na tentativa de “roubar” alguns utilizadores à concorrência,
tem algumas facilidades que permitem que típicos utilizadores de Oracle, comecem a trabalhar com o DB2.
Neste leque incluem-se o suporte da mais utilizada sintaxe da linguagem SQL, sintaxe de PL/SQL, sintaxe de
script, e tipos de dados do Oracle. Para além disto, melhorou também o seu modelo de concorrência, de
forma a apresentar um comportamento semelhante ao que os utilizadores de Oracle Database e Microsoft
SQL Server estão habituados. Em Outubro, a IBM lançou o DB2 pureScale. Este é uma solução para uma
base de dados clustered, ideal para cargas de OLTP (Online Transaction Processing), fazendo um auto-
balanceamento destas. Utiliza uma arquitectura tolerante a falhas e um armazenamento em discos
partilhados.
Este SGBDR é vendido em diversos tipos de edições ou licenças. Através da escolha de uma versão
com menos recursos, a IBM evita que os consumidores paguem por funcionalidades que não iriam usar,
sendo um atractivo para a escolha deste. Algumas das edições disponíveis são a Express, a Workgroup e a
Enterprise. Porém, a mais desenvolvida para Linux/Unix/Windows é o DB2 Data Warehouse Enterprise
Edition (DB2 DWE). Para o z/OS, o DB2 já vem com licença incluída. Para esta versão, o sistema possui
algumas características exclusivas: segurança Multi-Level, tabelas de tamanhos extremamente elevados e
compressão a nível de hardware. Também aqui, o DB2 sempre foi conhecido pela sua liderança na
performance OLTP, sendo usado para suportar missões críticas nas operações de negócios, estando nas
últimas versões a adquirir características de Business Intelligence.
Em Janeiro de 2006, a IBM lançou uma versão do DB2 chamada DB2 9 Express-C. Foi esta a resposta
da IBM à concorrência, que por essa altura começou a lançar versões gratuitas dos seus SGBDR,
nomeadamente o Oracle e o Microsoft SQL Server. O Express-C não apresenta limite no número de
utilizadores nem tão pouco no tamanho das bases de dados, tendo sido desenvolvido para máquinas
Windows, Linux, Solaris e Mac OS de qualquer capacidade, fazendo o sistema uso de apenas 2
processadores e 2 GB de memória RAM.
DB2 - Conceitos e Análise do Sistema 7
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 8/85
1 - EVOLUÇÃO E APLICABILIDADE DO SISTEMA
Historicamente, a liderança de mercado dos SGBD tem sido marcada pela alternância entre Oracle e
DB2, no entanto, a concorrência tem aumentado substancialmente com o crescimento de sistemas como o
Microsoft SQL Server (só disponível para Windows), o PostgreSQL, o MySQL e o Firebird. Já a versão para as
mainframes com z/OS tem tido poucos concorrentes directos, embora a Oracle tenha vindo a atrairconsumidores para os servidores baseados em Linux.
Na área dos clustered SGBD, onde as bases de dados podem crescer até vários terabytes, a IBM
oferece 2 alternativas que conseguem competir com o Oracle Real Application Clusters: o DB2 pureScale e o
DB2 Database Partitioning Feature. O DB2 pureScale consiste numa solução de base de dados agregada em
disco partilhado e que é ideal para transacções online de carga muito elevada. Já a segunda alternativa
permite particionar a base de dados por vários servidores ou num grande servidor SMP e é ideal para OAP
(Online Analytical Processing). De notar que o DB2 DPF é vendido como parte da IBM InfoSphere
Warehouse e não como um produto independentes, dado maior profundidade a este sistema. Não é,
portanto, de estranhar que a IBM seja líder de vendas de produtos de bases de dados neste domínio.
A par dos seus mais directos rivais, o DB2 pode ser administrado tanto a partir de ambientes
gráficos como através de uma linha de comandos. Através da linha de comandos, é possível fazer uso de
todas as potencialidades da base de dados, sendo, no entanto, necessário um maior nível de conhecimento
do sistema. Já o ambiente gráfico corre sobre a plataforma Java contendo uma grande variedade de
wizards, que facilitam a gestão do DB2.
DB2 - Conceitos e Análise do Sistema 8
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 9/85
2 – NOTAS SOBRE A INSTALAÇÃO
2 – N2 – NOTASOTAS SSOBREOBRE AA IINSTALAÇÃONSTALAÇÃO
Como já foi referido a cima na evolução do sistema existem diferentes versões do DB2 desde maiscomplexas destinadas a usos de grande escala em grandes instituições à utilização particular nas nossascasas.
Usufruindo duma licença de teste de 90 dias instalou-se a versão DB2 Data Server 9.7 numambiente Windows.
2.1 ANTES DA I NSTALAÇÃO
Requisitos de DiscoOs requisitos de disco para o sistema DB2 irão variar consoante a versão que se pretende instalar.
Na forma de instalação mais simples, usando o assistente de configuração, o assistente irá estimar o espaçonecessário para os componentes a instalar.
Requisitos de Memória
Em termos de memória para correr apenas um produto DB2 e as suas interfaces gráficas ummínimo de 512 Mb de memória RAM são necessários sendo recomendado 1Gb. Contudo estes valoresestão obviamente dependentes da complexidade e extensão das bases de dados. O DB2 possui um recursode auto-ajuste da memória que quando usado simplifica a tarefa de configuração da memória distribuindo
dinâmicamente os recursos de memória pelos componentes que a requerem.
2.2 I NSTALAÇÃO
Recorrendo ao assistente de configuração a instalação foi simples e intuitiva, o assistente cria umaconta windows cuja as credencias serão usadas no DB2.
Foi permitido ao utilizador escolher iniciar ou não os serviços no ínico do windows.Numa configuração inicial foi nos dada a opção de criar uma base de dados exemplo o que pode
provar-se útil.
2.3 DEPOIS DA I NSTALAÇÃO
De notar que as aplicações de software de administração da base de dados necessitam de sercorridas como administrador caso contrário não inicializam.
DB2 - Conceitos e Análise do Sistema 9
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 10/85
3 – COBERTURA DO STANDARD SQL
3 – C3 – COBERTURAOBERTURA DODO SSTANDARDTANDARD SQLSQLO SQL é a linguagem de pesquisa declarativa para bases de dados relacionais mais comum tendo
nascido nos próprios laboratórios da IBM no ínico dos anos 70 no âmbito de um projecto de bases de dadoscom o nome System R. No entanto apesar de ter nascido na IBM surgiram outras versões e de formaaproveitar os diversos contributos das várias especificações foi levado a cabo o esforço de produzir umStandard para o SQL. O resultado desse esforço encontra-se sumarizado na tabela abaixo.
Ano Nome Descrição
1986 SQL-86 Primeiro standard formal feito pela ANSI
1989 SQL-89 Pequena Revisão
1992 SQL-92 Grande Revisão
1999 SQL:1999 Matching de Expressões Regulares, Queries Recursivas, Triggers,suporte de procedimentos e controlo de fluxo, tipos não escalares,funcionalidades orientadas aos objectos
2003 SQL:2003 Funcionalidades XML, window function, colunas com valores auto-gerados
2006 SQL:2006 Funcionalidades relacionando XML e SQL
2008 SQL:2008 ORDER BY fora de definição cursores, trigger INSTEAD OF,TRUNCATE.
O sistema DB2 tal como a maior parte dos existentes não é uma implementação rigorosa até ao
mais pequeno detalhe do standard. Abaixo segue uma descrição de algumas funcionalidadasimplementadas.
3.1 D ATA DEFINITION L ANGUAGE
Encontram-se nesta secção descrições das funcionalidades básicas DDL bem como inicialmentereferência aos tipos de dados suportados e identificadores.
São descritas as opcções para principais statements do Tipo CREATE, ALTER e DROP.
Tipos de Dados
Os tipos de dados suportados pelo DB2 encontram-se no esquema 1 na página seguinte
Identificadores
O DB2 suporta a maior parte da especificação de identificadores incluindo identicadores emminúsculas e identificadores delimitados (ex: tabela “nome da tabela com espaços”). O DB2 não suportaidentificadores que terminem no caractere “_”.
DB2 - Conceitos e Análise do Sistema 10
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 11/85
3 – COBERTURA DO STANDARD SQL
DB2 - Conceitos e Análise do Sistema 11
Esquema 1: Tipos de Dados no DB2
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 12/85
3 – COBERTURA DO STANDARD SQL
Create Table
A criação de tabelas para além de suportar as opcções básicas suporta tambêm as seguintesfuncionalidades:
◦ Hierarquia de Tabelas
◦ Definição de esquema de tabela com base numa query (palavra-chave AS)
◦ Definição de esquema de tabela com base noutra tabela ou view ( LIKE )
◦ Colunas identidade (Semelhante as SEQUENCES mas associadas automaticamente à coluna
alvo)
nota: Só pode haver uma coluna identidade por tabela
◦ Opções de partições – especificação dos atributos que compôem a chave de partição
▪ Opção de usar hashing para particionamento
◦ Especificação de replicação
◦ Not Null
◦ Definição de Contraints
▪ Primary Key
▪ Foreign Key
• On Delete
◦ Cascade
◦ Set null
◦ Restrict (lançar erro)
• On Update
◦ restrict
◦ Check (asserções)
◦ Unique
DB2 - Conceitos e Análise do Sistema 12
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 13/85
3 – COBERTURA DO STANDARD SQL
Create View
Permite a criação de uma view a partir de uma query com as seguintes opções (entre outras)
◦ Views hierárquicas
◦ linhas da view são verificadas em relação as condições de pesquisa da view ( WITH CHECK
OPTION )
▪ Cascaded – A view herda as condições de todas as views das quais está dependente
▪ Local – As condições da view são aplicadas como restrições das inserções na view e nas
que dependem dela
Create IndexVer secção Indexação e Hashing.
Create Trigger
O DB2 suporta a criação de triggers através do comando CREATE TRIGGER. Um trigger define umconjunto de acções que são executadas quando ocorre um dado evento na base de dados. O DB2 permitetriggers em relação aos seguintes eventos:
◦ AFTER event ON tabela
event
▪ INSERT
▪ UPDATE (OF coluna)
▪ DELETE
Existe a opção NO CASCADE BEFORE que estipula que as acções do trigger devem ser executadasantes de qualquer alteração ser efectuada de facto na tabela alvo e que nenhum trigger será despoletadocomo consequência deste.
É possível também definir a granularidade do trigger com as opções FOR EACH ROW e FOR EACHSTATEMENT especificando respectivamente que as acções devem ser aplicadas uma vez para cada linha databela alvo ou apena uma vez para o comando que o despoletou.
Depois de especificar as condições que despoletam o trigger define-se a acção do trigger atravésdos denominados triggered-sql-statements no caso de mais que um é necessário que cada comando sejacolocado entre as palavras chave BEGIN ATOMIC e END podemos ainda definir quando estas acções são defacto aplicadas através de condições numa cláusula WHEN.
DB2 - Conceitos e Análise do Sistema 13
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 14/85
3 – COBERTURA DO STANDARD SQL
Alter Table
O comando alter table permite modificar tabelas existentes da seguinte forma (entre outras).
◦ Adicionar uma ou mais colunas a uma tabela
◦ Adicionar ou Eliminar uma definição de Primary Key (obviamente só pode haver uma)
◦ Adicionar ou eliminar restrições do tipo Unique e de referências
◦ Adicionar ou Eliminar restrições do tipo Check
◦ Alterar o comprimento uma coluna do tipo Varchar
◦ Adicionar ou Eliminar uma chave de particionamento
◦ Modificar o tipo de lock efectuado por defeito na tabela
Drop
O comando DROP elimina objectos da base de dados como por exemplo tabelas, views, triggers e
indíces.
3.2 D ATA M ANIPULATION L ANGUAGE
Para inserir, alterar e eliminar valores numa base dados são utilizados comandos que compõe umalinguagem de manipulação de dados. Descrevemos em baixo as principais implementadas pelo DB2.
Insert
O comando INSERT permite a inserção de valores em tabelas directamente na tabela ou através deviews. Estes valores podem ser inseridos como um tuplo literal [ex : ('a',1,3) ], os valores podem serdefinidos com o valor NULL ou DEFAULT [ex: ('a',NULL,DEFAULT)] ,considerando que foi definido uma valorpor defeito para coluna, ou como resultado de uma expressão [ex: ('a', 'João' CONCAT ' ' CONCAT 'Rio',3).
É possível inserir mais que um tuplo de cada vez no mesmo comando:ex: INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, ADMRDEPT)
VALUES ('B11', 'PURCHASING', 'B01'),
('E41', 'DATABASE ADMINISTRATION', 'E01')
Os valores podem também ser inseridos através do resultado duma query considerando que oresultado da query respeita o esquema da tabela onde se pretende inserir
ex: INSERT INTO T2
S E L E C T *
FROM T1
DB2 - Conceitos e Análise do Sistema 14
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 15/85
3 – COBERTURA DO STANDARD SQL
Update
O comando UPDATE é usado para alterar valores já existentes numa tabela. O DB2 contempladoistipos de updates:
▪ searched UPDATE
Usado quando queremos actualizar uma ou mais linhas com base numa condição
▪ positioned UPDATE
Usado quando queremos actualizar exactamente uma linha determinada por uma posiçãocorrente dum cursor.
Delete
O comando DELETE é usado para remover valores duma tabela e é em tudo semelhante ao UPDATE
também considerando duas formas o searched DELETE e o positioned DELETE .
3.3 D ATA QUERY L ANGUAGE
Criada uma base de dados e à parte da mais ou menos frequente alteração de valores desta o maiorobjectivo é aceder a estes dados. Tal acesso é especificado por meio de uma linguagem de Queries
(Perguntas) a qual descrevemos sucintamente a baixo em termos do que é implementado pelo DB2.
SelectO comando SELECT constitui a principal forma de acesso aos dados na base de dados, permitindo a
especificação de que colunas obter bem como condições que os resultados devem obedecer.
O DB2 respeita a sintaxe básica definida pelo standard implementando para além das cláusulasbásicas FROM e WHERE as cláusulas WITH (definição de vistas temporárias) GROUP BY, HAVING, ORDER BY.
Este tópico será aprofundado na secção de Processamento e Optimização de perguntas.
DB2 - Conceitos e Análise do Sistema 15
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 16/85
4 – ARMAZENAMENTO E ESTRUTURA DE FICHEIROS
4 – A4 – ARMAZENAMENTORMAZENAMENTO EE EESTRUTURASTRUTURA DEDE FFICHEIROSICHEIROS
Neste capítulo abordamos a arquitectura base do sistema bem como a estrutura de ficheiros e oseu armazenamento em disco.
4.1 P RINCÍPIOS B ÁSICOS DA ARQUITECTURA DB2No lado do cliente, aplicações locais ou remotas estão ligadas com a biblioteca de cliente DB2.
Clientes locais comunicam através de memória partilhada, gerida com semáforos, sendo que os clientes
remotos usam um protocolo semelhante a pipes (NPIPE) ou TCP/IP. No lado do servidor, a actividade é
controlada pelas Engine Dispatchable Units (EDUS).
A figura abaixo apresenta uma visão geral acerca da arquitectura e processos do DB2.
Esquema 2: Arquitectura DB2
DB2 - Conceitos e Análise do Sistema 16
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 17/85
4 – ARMAZENAMENTO E ESTRUTURA DE FICHEIROS
As EDUs estão representadas pelos círculos ou grupos de círculos.
As EDUs são implementadas como threads em todas as plataformas. Os agentes do DB2 são o tipo
mais comum de EDU. Estes agentes realizam a maior parte do processamento do SQL e do XQuery em
nome dos aplicativos. Prefetchers e page cleaners são outro tipo de EDUs comuns.
Um grupo de subagentes pode ser atribuído para processar pedidos das aplicações do cliente.
Multiplos subagentes podem ser atribuídos caso a máquina na qual reside o servidor tenha vários
processadores ou faça parte de um ambiente de base de dados particionada. Por exemplo, num ambiente
de multiprocessamento simétrico (SMP), vários subagentes de SMP podem explorar diferentes
processadores.
Todos os agentes e subagentes são geridos por um algoritmo de pooling que minimiza o número decriação e destruição de subagentes.
Os buffer pools são áreas da memória do servidor de bases de dados para onde as páginas de dados
do utilizador, dados de índice e dados de catálogo são movidos temporariamente e onde podem ser
modificados. Os buffer pools são um factor determinante no desempenho da base de dados, dado que os
dados podem ser acedidos com muito maior facilidade e rapidez do que se estivessem no disco.
A configuração dos buffer pools, assim como das prefetcher e page cleaners EDUs, controla a
rapidez com quem os dados podem ser acedidos pelas aplicações.
Os prefetchers recolhem os dados do disco e movem-nos para um buffer pool antes que as
aplicações necessitem deles. Por exemplo, aplicações que precisem de varrer grandes blocos de dados
teriam que os estes fossem movidos do disco para um buffer pool se não houvessem prefetchers de dados.
Os agentes das aplicações enviam pedidos de leitura assíncrona para uma fila de pedidos de prefetch. À
medida que os prefetchers ficam disponíveis, eles executam estes pedidos através da leitura contígua dos
grandes blocos ou em várias fases, de forma a trazer as páginas desejadas, do disco, para o buffer pool . Se o
utilizador tiver vários discos para armazenamento de dados, os dados podem ser distribuídos por estes. Esta
distribuição traz vantagens para o sistema, dado que permite que vários prefetchers possam recolher a
informação em paralelo.
Os page cleaners movem os dados do buffer pool de volta para o disco. Estes, são EDUs que
trabalham em background e que são independentes dos agentes da aplicação. Eles procuram por páginas
que foram modificadas e escrevem-nas no disco. Os page cleaners asseguram que haja, sempre que
possível, espaço no buffer pool para as páginas que vão sendo captadas pelos prefetchers.
Sem os as EDUs independentes, prefetchers e page cleaners, os agentes das aplicações teriam de
fazer todo o trabalho de leitura e escrita entre o buffer pool e o armazenamento em disco.
DB2 - Conceitos e Análise do Sistema 17
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 18/85
4 – ARMAZENAMENTO E ESTRUTURA DE FICHEIROS
4.2 T ABLE SPACES ( NÍVEL LÓGICO )
Os table spaces são uma interface lógica entre as tabelas lógicas e a memória física do sistema
(buffer pool ) e os contentores (discos rígidos). Através da declaração CREATE TABLESPACE, para criar um
novo table space, podemos especificar:
• O tamanho da página para um table space (4KB, 8KB, 16KB e 32KB).
• O nome do buffer pool associado com este table space.
• O tamanho do extent .
•O tamanho para prefetch.
Tipos de Table Space
Existem 3 tipos de table space:
• Normal. Utilizados para as tabelas do utilizador. Por exemplo, o table space USERSPACE1
criada aquando da criação da base de dados, é um table space do tipo normal.
• Grande. Estes table spaces são usados para separar opcionalmente os dados LOB (Large
Object). Também é utilizado para armazenar dados XML para bases de dados criadas comsuporte pureXML e o tipo de dados XML é utilizado nas colunas.
• Temporário. Existem dois tipos de table spaces temporários:
• Temporário do sistema. São usados pelo DB2 para operações internas, como
ordenações.
• Temporário do utilizador. Estes são usados para criar table spaces definidas pelo
utilizador (tabelas temporárias em memória).
Gestão de Table Spaces
Os table spaces podem ser classificados com base no modo como são geridos. Isto pode serespecificado no comando CREATE TABLESPACE.
Gerido pelo sistema
Este tipo de table space é conhecido como System Managed Storage (SMS). Isso significa que o
sistema operativo gere o armazenamento. São fáceis de gerir e os containers são directorias do sistema de
ficheiros. O espaço não é pré-alocado, crescendo os ficheiros de forma dinâmica. Depois de especificar os
containers, estes são fixados no tempo de criação e outros containers não podem ser adicionados mais
DB2 - Conceitos e Análise do Sistema 18
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 19/85
4 – ARMAZENAMENTO E ESTRUTURA DE FICHEIROS
tarde, a menos que um restauro do sistema seja utilizado. Ao usar o table space SMS, os dados das tabelas,
índice e LOB não podem ser repartidos por diferentes table spaces.
Gerido pelo armazenamento automático
Este tipo de table space é gerido pelo armazenamento automático e facilmente beneficiar de uso
semelhante ao table space SMS, mas com melhor desempenho e flexibilidade do que o table space DMS.
Por isso, começando com o DB2 9, este é o tipo de table space pré-definido. Para estes table spaces, um
utilizador especifica primeiro um grupo lógico de dispositivos de armazenamento. Não é necessário
especificar containers, uma vez que estes são automaticamente criados. O crescimento dos actuais
containers e adição de novos é totalmente gerido pelo DB2.
Para permitir o armazenamento automático, primeiro é preciso criar uma base de dados com
armazenamento automático activo (este é o comportamento padrão) e associar um conjunto de paths de
armazenamento com essa mesma base de dados. Após a criação, se for necessário, podem-se redefinir os
caminhos de armazenamento usando a operação de base de dados RESTORE.
4.3 T ABLE SPACES ( NÍVEL FÍSICO )
Por omissão, o DB2 irá escrever nos blocos do disco paralelamente em todos os containers. Por
exemplo, considerando um table space de 4K com um tamanho 8 utilizando 3 containers básicos sobre um
table space DMS, isto significa que 32KB de dados (4KB x 8 páginas de medida = 32KB) serão gravados num
disco antes de escrever para o seguinte. Isso é ilustrado na figura abaixo. De notar que as tabelas nãopartilham os blocos (extents).
Esquema 3: Armazenamento em Containers
De notar que para além da criação através de SQL puro, o aplicativo do DB2, o Control Center
permite-a através de wizards, simplificando a criação destes para utilizadores mais inexperientes.
DB2 - Conceitos e Análise do Sistema 19
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 20/85
4 – ARMAZENAMENTO E ESTRUTURA DE FICHEIROS
Factores de desempenho do armazenamento em disco
Características de hardware, tais como a configuração de armazenamento em disco, podem
influenciar fortemente a performance do nosso sistema.
A performance pode ser afectada por um ou mais dos seguintes aspectos da configuração de
armazenamento:
• Divisão do armazenamento. O sistema irá executar em diferentes situações consoante a
quantidade de dados entre os table spaces e os índices.
• Distribuição do I/O do disco. O pedido de operações de I/O ao disco a partir de vários
dispositivos e controladores pode afectar a velocidade de captura dos dados pelo gestor da
base de dados.
• Métricas de desempenho do disco do subsistema. O número de operações de disco por
segundo ou a capacidade em megabytes transferidos por segundo têm uma impacto muito
forte na performance de todo o sistema.
Impacto do table space na optimização de perguntas
Certas características dos table spaces podem afectar os planos de acesso que são escolhidos pelo
compilador de perguntas.
Estas características incluem:
• Características do container .
As características do container podem ter um impacto significativo nos custos de I/O
associados à execução de perguntas. Quando é seleccionado um plano de acesso, o
optimizador de perguntas considera estes custos de I/O, incluindo as diferenças de custos
quando acede a dados de diferentes table spaces. Duas colunas view de catálogo
SYSCAT.TABLESPACES são usadas pelo optimizador de forma a ajudar a estimar os custos de
I/O aquando do acesso a dados de um table space:
• OVERHEAD fornece uma estimativa do tempo (em milisegundos) que é necessário
pelo container antes de qualquer dado ser lido para memória. Este tempo de
overhead inclui o overhead do controlador de I/O do container tal como o tempo de
latência de acesso ao disco, que inclui o tempo de seek .
• TRANSFERRATE fornece uma estimativa do tempo (também em milisegundos) que é
requerido para ler uma página de dados para memória.
Os containers onde se encontram os table spaces podem estar alojados em diferentes
discos físicos. Porém, de forma a obter melhores resultados, todos os discos que são
utilizados, para um dado table space, deverão ter as mesmas características de OVERHEAD e
TRANSFERRATE. Se tal não acontecer, deveremos atribuir-lhes os valores médios destes,
DB2 - Conceitos e Análise do Sistema 20
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 21/85
4 – ARMAZENAMENTO E ESTRUTURA DE FICHEIROS
para obter resultados mais realistas. Poderemos obter os valores específicos para estas
colunas através da especificação do hardware ou através de experimentação. Estes valores
podem ser especificados nos predicados CREATE TABLESPACE e ALTER TABLESPACE.
• Prefetching
Ao considerar os custos de I/O de acesso aos dados de um table space, o optimizador
também tem em conta o impacto que o prefetching os dados e das páginas de índice pode
ter na performance da pergunta. O prefetching pode potencialmente reduzir o overhead
que está associado à leitura dos dados para o buffer pool .
O optimizador utiliza a informação presente nas colunas PREFETCHSIZE e EXTENTSIZE da
vista de catálogo SYSCAT.TABLESPACES para estimar a quantidade de prefetching que deverá
ocorrer.
• EXTENTSIZE só poderá ser definido aquando da criação de um table space. Um
extent size de 4 ou 8 páginas é, normalmente, suficiente.
• PREFETCHSIZE pode ser definido aquando da criação ou alteração de um table
space. O prefetch size criado por defeito é determinado pelo parâmetro de
configuração da base de dados dft_prefetch_sz.
4.3 ARMAZENAMENTO DE T ABELAS E Í NDICES
Tabelas Standard
Nas tabelas standard, os dados estão organizados logicamente por páginas de dados. Estas páginas
de dados estão agrupadas logicamente tendo em conta o extent size do table space. Por exemplo, se o
extent size for 4, as páginas de 0 a 3 fazem parte do primeiro extent, as de 4 a 7 do segundo extent, e assim
em diante.
O número de registos contidos em cada página pode variar, tendo em conta o tamanho da página
de dados e o tamanho dos registos. A maioria das páginas contém somente registos do usuário. No entanto,um pequeno número de páginas inclui registos internos, que são utilizados pelos gestor do servidor de
dados para gerir a tabela. Por exemplo, numa tabela standard, há um registo de controlo livre a cada 500
páginas de dados. Estes registos têm um mapeamento dos espaços livres para acrescentar novos registos
nas 500 páginas seguintes.
DB2 - Conceitos e Análise do Sistema 21
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 22/85
4 – ARMAZENAMENTO E ESTRUTURA DE FICHEIROS
A figura abaixo esquematiza a visão a nível lógico dos registos das tabelas e dos índices destas.
Esquema 4: Relação entre nível lógico e nível físico
Logicamente, as páginas de índice estão organizadas como uma B-Tree que consegue,
eficientemente, localizar os registos da tabela que têm uma dada chave. O número de entidades de uma
página de índice não é fixo, porém, é dependente do tamanho da chave. Para tabelas no database
managed space (DMS) table spaces, os identificadores de registo nas páginas de índice usam números de
página relativos ao table space, ao invés de usar os relativos ao objecto. Isto permite uma pesquisa noíndice para aceder directamente aos dados sem necessidade de uma página de mapeamento.
Todas as páginas de dados têm o mesmo formato. A página tem no topo um cabeçalho, seguido por
um slot directory . Cada entrada no slot directory corresponde a um registo diferente presente na página.
Uma entrada no slot directory representa o byte-offset na página de dados onde o registo começa. Entradas
com o valor -1 correspondem a registos apagados.
DB2 - Conceitos e Análise do Sistema 22
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 23/85
4 – ARMAZENAMENTO E ESTRUTURA DE FICHEIROS
Identificadores de registo e páginas
Os identificadores dos registos consistem num número de página, seguindo de um número de slot.
Os registos de índice contêm uma flag adicional, denominada ridFlag. A ridFlag guarda informação acerca
do estado das chaves no índice, como por exemplo, se foram marcadas como apagadas. Depois de o índice
ter servido para identificar o identificador de um registo, este serve para encontrar a página certa e o
respectivo slot. Depois de um registo ter um identificador atribuído, este não irá mudar enquanto a tabela
não for reorganizada.
A figura abaixo esquematiza o que foi explicado anteriormente.
Esquema 5: Registo
Quando a página de uma tabela é reorganizada, o espaço livre “bloqueado” que fica na página
depois de um registo ter sido removido, é convertido em espaço livre utilizável.
O servidor de dados DB2 suporta diferentes tamanhos de página. Deverão ser utilizados tamanho
de página maiores para trabalhos que exijam um acesso sequencial da tabela. Por exemplo, acessos
sequenciais são geralmente utilizados para aplicações de apoio à decisão ou em tabelas temporárias que
são utilizadas intensivamente. Por outro lado, pequenos tamanhos de página deverão ser utilizados quando
os acessos às tabelas tendem a ser um tanto aleatórios. Esta situação verifica-se, por exemplo, em
ambientes de transacções online.
Tabelas Multidimensional Clustering
A organização das tabelas e índices para tabelas de multidimensional clustering (MDC) é baseada
nas mesmas estruturas lógicas que as tabelas standard.
Tal como as tabelas standard, as tabelas MDC estão organizadas em páginas que contêm linhas de
dados, divididas em colunas. As linhas em cada página são identificadas pelo seus records IDs (RIDs). No
entanto, as páginas das tabelas MDC estão agrupadas em blocos extent-sized . Por exemplo, a tabela da
figura abaixo tem um extent size 4. As primeiras 4 páginas, numeradas de 0 a 3, representam o primeiro
DB2 - Conceitos e Análise do Sistema 23
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 24/85
4 – ARMAZENAMENTO E ESTRUTURA DE FICHEIROS
bloco da tabela. As 4 páginas seguintes, numeradas de 4 a 7, representam o segundo bloco na tabela.
Esquema 6: Nivel lógico e físico de tabelas MDC
DB2 - Conceitos e Análise do Sistema 24
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 25/85
4 – ARMAZENAMENTO E ESTRUTURA DE FICHEIROS
O primeiro bloco contém registos internos especiais, incluindo um registo do espaço livre (FSCR),
que é usado pelo DB2 para gerir a tabela. Nos blocos subsequentes, a primeira página contém um FSCR. O
FSCR mapeia o espaço livre, para inserir novos registos, que existe em cada página do bloco.
Tal como o nome nos diz, as tabelas MDC agrupam os dados de mais do que uma dimensão. Cada
dimensão é determinada pela coluna ou pelo conjunto de colunas que são especificadas na preposição
ORGANIZE BY DIMENSIONS de CREATE TABLE. Quando se cria uma tabela MDC, dois índices são criados
automaticamente:
• Um dimension-block index , que contém apontadores para os blocos ocupados em cada
dimensão.
• Um composite-block index , que contém todas as colunas chaves de dimensão e que é usado
para manter o clustering durante as actividades de insert e update.
O optimizador tem em conta os planos de acesso que usam índices dimension-block , quando
determina qual o melhor plano para uma determinada query . Podem ser criados, também, RID indexes, se a
análise dos planos de acesso indicarem que tal melhoraria o desempenho da pergunta.
4.4 P ARTICIONAMENTO DE T ABELAS
O particionamento de tabelas, muitas vezes referido como particionamento de intervalos, é um
esquema de organização de dados em que os dados de uma tabela estão divididos em vários objectos,
chamados de partições de dados, de acordo com os valores de uma ou mais colunas. Estes objectos de
armazenamento podem estar em diferentes table spaces, no mesmo table space, ou numa combinação de
ambos.
O DB2 9 suporta partições de dados baseadas em uma grande variedade de atributos. Um esquema
de partição muito comum é o valor da data, onde se pode decidir se se agrupam os dados por meses ou por
anos, por exemplo. Pode-se também ter atributos numéricos para decidir o particionamento, por exemplo,
os IDs dos registos. Assim, poder-se-á ter os registos com identificadores entre 1 e 1 milhão numa partição,
entre 1 milhão e 2 milhões noutra partição, e assim em diante. Outro exemplo será a divisão tendo por base
os nomes dos clientes, colocando numa partição os nomes começados por uma das letras de A-C, noutra de
D a M, e por aí fora, consoante a estimativa que o administrador faz.
Embora se possa referenciar as partições de dados por nomes ou números, a existência destas pode
ser totalmente transparente para as aplicações, ou seja, as aplicações podem continuar a aceder aos dados
especificando os nomes das tabelas e das colunas, sem terem que se preocupar em que partição é que
estes residem.
DB2 - Conceitos e Análise do Sistema 25
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 26/85
4 – ARMAZENAMENTO E ESTRUTURA DE FICHEIROS
Benefícios do particionamento de tabelas
Gestão melhorada: o DB2 9 permite que as várias partições sejam administradas
independentemente. Por exemplo, poder-se-á escolher fazer um backup e restaurar partições de dados
individuais em vez da tabela toda. Isto permite baixar o tempo de longas operações de manutenção,
transformando-as em séries de pequenas operações.
Aumento do desempenho das consultas: o optimizador do DB2 é consciente da existência de
partições. Deste modo, durante uma pesquisa, apenas as partições relevantes são consultadas. Ao não fazer
scan sobre partições irrelevantes para a query , o desempenho poderá ser bastante superior.
Fast roll-in/roll-out : o DB2 9 permite que partições de dados sejam facilmente adicionadas ou
removidas sem ter que desligar a base de dados. Esta capacidade pode ser substancialmente vantajosa nos
ambientes de Data Warehouse onde muitas vezes se precisa de carregar ou excluir dados para executar
consultas de apoio à decisão.
Melhor optimização dos custos de armazenamento: o particionamento de tabelas no DB2 9
permite uma melhor integração com os modelos de armazenamento hierárquico. Usando os mais rápidos e
melhores dispositivos de hardware apenas para as partições mais utilizadas, o DB2 permite uma
optimização dos custos totais de armazenamento de performance.
Maior capacidade para a tabela: sem particionamento, há limites para a quantidade máxima de
dados que a tabela podia guardar. No entanto, dividindo o conteúdo por vários objectos de armazenamento
ou partições de dados, cada uma capaz de suportar o máximo de dados de uma tabela não particionada,
poder-se-á criar bases de dados que são praticamente ilimitadas em termos de tamanho.
Maior flexibilidade no posicionamento de um índice: o DB2 9 permite que os índices para as
tabelas particionadas sejam armazenados nos seus próprios table spaces, em vez de serem armazenados no
table space da tabela, como acontece nas tabelas não particionadas. Esta flexibilidade no posicionamento
dos índices é particularmente útil para executar operações rápidas sobre este (eliminar índice, criar índice,
reorganizar índice), gestão do crescimento da tabela, redução da contenção do I/O, fornecendo um acesso
concorrente aos dados do índice mais eficiente.
Criação de partições
O DB2 oferece muita flexibilidade para a criação de tabelas particionadas. Supondo que temos um
conjuntos de dados de um certo ano e que queremos particioná-los por data, separando-os por trimestre. O
exemplo seguinte demonstra como fazer isto de forma simples. Para além desta sintaxe, poderemos ainda
utilizar a interface gráfica do DB2 Control Center para criar e gerir partições de dados.
CREATE TABLE orders (id INT, shipdate DATE, …) PARTITION BY RANGE(shipdate) (STARTING
‘1/1/2006’ ENDING ‘12/31/2006’ EVERY 3 MONTHS)
DB2 - Conceitos e Análise do Sistema 26
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 27/85
4 – ARMAZENAMENTO E ESTRUTURA DE FICHEIROS
Com esta operação, os dados deverão ficar distribuídos da seguinte maneira:
Esquema 7: Particionamento
4.5 BUFFER P OOL
O buffer pool fornece memória de trabalho útil e cache para as páginas da base de dados.
O buffer pool melhora o desempenho do sistema de base de dados ao permitir que os dados sejam
acedidos da memória ao invés de directamente do disco. Como a maior parte da manipulação de dados
ocorre ao nível do buffer, configurar os buffer pools é uma tarefa de extrema importância na área do tuning.
Quando uma aplicação acede a um tuplo da tabela, o gestor da base de dados procura pela página
que o contem no buffer pool . Se a encontrar aí, lê-a do disco, colocando-a no buffer. Assim, os dados
poderão ser, então, utilizados para executar a pergunta.
A memória é alocada para os buffer pools quando a base de dados é activada. A primeira aplicação
a conectar deverá causar a activação desta. Os buffer pools podem ser criados, mudados de tamanho ou
destruídos, enquanto o gestor da base de dados estiver a correr. Através do predicado CREATE BUFFERPOOLpoderemos criar um novo buffer. Porém, não é a única maneira de o fazer. Recorrendo ao Control Center, a
mesma acção pode ser efectuada, através de wizards, ao invés de SQL puro. O predicado ALTER
BUFFERPOOL pode ser utilizado para aumentar o tamanho do buffer pool . Por defeito, e se houver memória
livre suficiente, o tamanho do buffer é imediatamente aumentado, caso contrário, a memória só será
alocada aquando da reactivação da base de dados. No caso de se reduzir o tamanho do buffer, a memória é
libertada quando a transacção fizer commit . Quando a base de dados é desactivada, a memória do buffer
pool é libertada.
De forma a assegurar que existem buffer pools apropriados em cada circunstância, o DB2 cria um
pequeno sistema de buffer pools, cada um com os seguintes tamanhos de página: 4KB, 8KB, 16KB e 32KB. Otamanho de cada buffer pool é de 16 páginas. Estes buffers estão escondidos, não sendo visíveis no catálogo
do sistema nem tão pouco no sistema de buffers. Estes não poderão ser alterados directamente, sendo
utilizados pelo DB2 em algumas situações excepcionais em que os buffers convencionais não estão
disponíveis.
Quando um buffer pool é criado, o tamanho das páginas serão o que foi especificado aquando da
criação da base de dados, a não ser que se indique explicitamente o tamanho desejado. Porém, as páginas a
ser lidas para o buffer terão de ter o mesmo tamanho que as suportadas pelo buffer, pelo que estas deverão
ter tamanho igual às do table space.
DB2 - Conceitos e Análise do Sistema 27
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 28/85
4 – ARMAZENAMENTO E ESTRUTURA DE FICHEIROS
As páginas do buffer pool poderão estar a ser usadas ou não, e dirty ou clean. As páginas em uso
são páginas que estão a ser lidas ou alteradas correntemente. Se ela estiver a ser alterada só poderá ser
acedida pelo updater . No entanto, se não estiver a sofrer alterações, poderá estar a ser acedida por
inúmeros leitores concorrentes. As dirty pages contêm dados que já foram alteradas mas que ainda nãoforam escritas em disco.
As páginas permanecem no buffer pool até que a base de dados seja desligada, o espaço ocupado
por uma página seja requerido por outra, ou se a página por explicitamente despejada do buffer. O critério
utilizado quando uma página é removida porque outra requisitou o espaço é o seguinte:
• Há quanto tempo foi a página utilizada?
• Qual é a probabilidade de ser utilizada novamente?
• Que tipo de dados contém a página?
• Estava a página alterada na memória e não foi escrita no disco?
As páginas alteradas são sempre escritas no disco antes de serem sobrepostas. Quando são escritas
no disco, não são imediatamente removidas do buffer pool , a não ser que o espaço seja necessário.
DB2 - Conceitos e Análise do Sistema 28
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 29/85
5 – INDEXAÇÃO E H ASHING
5 – I5 – INDEXAÇÃONDEXAÇÃO EE HHASHINGASHING
5.1 Í NDICES
Estrutura de Índices
O gestor da base de dados utiliza uma estrutura de árvore B+ para guardar o índice.
Uma árvore B+ tem vários níveis, tal como sugere a figura abaixo.
Esquema 8: Estrutura de um Índice
O nível superior é nó da raiz. Já o último nível é o das folhas da árvore, que guardam as chaves do
índice com os respectivos apontadores que indicam as linhas da tabela que contêm os dados
correspondentes. Os nós entre a raiz e as folhas são os chamados nós intermédios.
Quando se procura por um valor particular do índice, o gestor dos índices procura pela árvore do
índice, começando na raiz. A raiz contém uma chave para cada bom nó (um que torne uma pesquisa mais
eficiente) do nível seguinte. O valor para cada uma destas chaves é o máximo existente no nó abaixo que
pretendemos apontar. Por exemplo, suponhamos que o índice possui 3 níveis, tal como o da figura. Para
encontrar um valor particular no índice, o gestor procura na raiz a primeira chave que é maior ou igual do
que a que pretendemos encontrar. Depois disto, o gestor segue o apontador, associado a este valor, que
indica um nó do nível intermédio. Este procedimento é repetido até que seja encontrado o valor pretendido
nas folhas do índice.
DB2 - Conceitos e Análise do Sistema 29
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 30/85
5 – INDEXAÇÃO E H ASHING
Um nó existente na folha pode, também, conter apontadores para os nós-folha anteriores. Estes
apontadores permitem ao gestor do índice fazer a pesquisa pelas folhas em ambas as direcções de forma a
devolver uma gama de valores depois te ter encontrado um valor que limite essa gama. Esta funcionalidade
está apenas disponível se o índice for criado com a opção ALLOW REVERSE SCANS.
No caso de se tratar de tabelas MDC, um índice de bloco é automaticamente criado para cada
dimensão da tabela. Um composite índex é também criado, contendo as chaves das colunas dos registos
presentes nessa dimensão. Estes índices contêm apontadores para os block ids (BIDs) em vez de para os
RIDs, oferecendo melhorias no acesso aos dados. Para coluna de dimensão variável presente no índice, é
acrescentado um byte adicional que guarda o tamanho actual do valor nessa coluna.
Usar índices relacionais para melhorar a performance
Os índices podem ser utilizados para melhorar o desempenho quando queremos aceder a umatabela. Índices relacionais são utilizados quando queremos aceder a dados relacionais e índices sobre XML
quando queremos aceder a dados neste formato. Apesar de o optimizador de perguntas decidir quando
utilizar um índice relacional para aceder a uma tabela relacional, é nossa decisão escolher quais os índices
que poderão provocar um melhoramento de performance e criá-los. Assim, deverão ser actualizadas as
estatísticas (através do comando RUNSTATS) sempre que criamos um índice ou depois de alterarmos o
tamanho do prefetch. Deverão ser também executadas regularmente de forma a mantê-las actualizadas,
para que o optimizador consiga determinar o melhor plano de acesso possível.
Para determinar se um índice é utilizado num pacote específico, deverá ser utilizada a ferramenta
com o explain plan. Para obter informação acerca dos índices que poderão ser explorados por um ou maisinstruções de SQL, poderemos usar o Design Advisor.
Uma das formas que o DB2 tem de optimizar a devolução dos dados é através da preposição
include (columns) na criação dos índices. As os dados pertences a columns serão incorporados no índice,
não afectando a ordenação deste. Assim, ao fazer uma pesquisa, sobre um índice, com uma projecção das
colunas columns, a simples consulta deste será suficiente, não se perdendo tempo no acesso à tabela.
Gestão de índices nas tabelas standard
Os índices no DB2 utilizam uma implementação optimizada de B-Trees que é baseada num métodode gestão eficiente e que permite elevada concorrência, recorrendo à pré-escrita num ficheiro de log. Um
índice em B-Tree é organizado como uma hierarquia de páginas que minimiza os tempos de acesso através
de uma reorganização das chaves à medida que os elementos são inseridos ou apagados.
A implementação de um índice B-Tree optimizado tem apontadores bidireccionais nas folhas que
permitem que um único índice possa fazer uma pesquisa para a frente ou para trás. As páginas de índices
estão geralmente dividas ao meio, excepto em alguns casos que apresentam uma distribuição 90/10, ou
seja, 90% das chaves de índice estão guardadas numa página e 10% noutra.
DB2 - Conceitos e Análise do Sistema 30
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 31/85
5 – INDEXAÇÃO E H ASHING
As chaves de índice apagadas são removidas da página de índice somente se houver um bloqueio
na tabela. Se as chaves não puderem ser removidas imediatamente, estas ficam marcadas como estando
apagadas e serão fisicamente removidas mais tarde.
Se tivermos activado a desfragmentação dos índices em tempo real especificando um valor positivo
para MINPCTUSED aquando da criação destes, as folhas podem ser fundidas nestas condições.
MINPCTUSED representa a percentagem mínima de espaço utilizado na página da folha do índice. Se a
quantidade de espaço utilizado cair abaixo deste parâmetro, o gestor da base de dados tentará fundir as
chaves que faltam com as presentes na página vizinha. Se houver espaço suficiente, as chaves são merged e
uma página é apagada. Dado que a desfragmentação em tempo real só ocorre quando as chaves são
removidas de uma página de índice, isto não ocorre se estas forem meramente assinaladas como apagadas.
Este tipo de desfragmentação pode melhorar a reutilização do espaço. Porém, se o valor MINPCTUSED for
demasiado elevado, o tempo que é necessário para que a fusão de duas páginas ocorra aumenta e a
probabilidade de esta ser bem sucedida diminui consideravelmente. Deste modo, o valor recomendadopara MINPCTUSED deverá ser menor ou igual que 50%.
A expressão INCLUDE da cláusula CREATE INDEX permite-nos especificar uma ou mais colunas (para
além das chaves) que desejamos para as páginas de índice. Isto inclui colunas que não estão envolvidas, à
partida, nos índices, permitindo um número muito mais elevado de perguntas às quais poderá ser dada
uma resposta com acesso somente a estes. Por outro lado, um excessivo número de colunas envolvidas nos
índices poderá não ser benéfico dado que aumentará o espaço necessário para guardar as suas páginas e,
possivelmente, os seus custos de manutenção, caso as colunas adicionais sejam frequentemente
modificadas. Este custo é menor do fazer a actualização de uma chave, mas é maior do que fazer a
actualização a colunas que não fazem parte do índice.
Outras opções da declaração CREATE INDEX
Pode criar-se um índice que permite duplicados (um índice não exclusivo) de forma a permitir um
retorno eficiente de colunas que não a chave primária e permitir valores duplicados nas colunas indexadas.
A declaração seguinte ilustra a criação de um índice não-único chamado LNAME criado a partir da
coluna LASTNAME da tabela EMPLOYEE, ordenada de forma ascendente:
CREATE INDEX LNAME ON EMPLOYEE (LASTNAME ASC)
Como será fácil de perceber, a estrutura geral é CREATE INDEX name ON table_name
(column_name order ), sendo que order só poderá ter como valores ASC ou DESC, sendo que o valor por
defeito é ASC.
Um índice único assegura que não há valores duplicados na coluna ou colunas indexadas. A
restrição é imposta no final da instrução SQL que actualiza ou insere linhas. Este tipo de índice não pode ser
criado se o conjunto de uma ou mais colunas contiver valores repetidos.
Poderemos criar um índex único em duas colunas, sendo que um das quais é coluna incluída. Neste
caso, a chave primária é definida pela coluna que não é incluída, embora ambas sejam apresentadas como
DB2 - Conceitos e Análise do Sistema 31
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 32/85
5 – INDEXAÇÃO E H ASHING
sendo chaves primárias na mesma tabela. Assim, tal como foi dito mais atrás, a preposição INCLUDE
especifica um conjunto de colunas adicionais que serão anexadas ao conjunto das colunas chaves.
Nenhuma das colunas incluídas será utilizada para impor exclusividade. A vantagem destas estará
simplesmente na melhoria do desempenho, dado que algumas perguntas, assim, só precisarão de acederao índice. Estas colunas deverão ser diferentes das que são utilizadas nas chaves, sob pena de se obter a
mensagem de erro SQLSTATE 42711.
Clustered indexes
Um índice de cluster determina como estão as linhas fisicamente ordenadas num table space. Estes
índices oferecem vantagens significativas de desempenho em algumas operações, particularmente
naquelas que envolvem muitos registos, por exemplo, operações de agrupamento, ordenação e
comparação.
Quando uma tabela tem um índice de cluster, uma operação de inserção faz com que o DB2 tente
inserir esse registo tanto quanto possível perto dos seus vizinhos, na ordem do índice. O primeiro índice que
é definido na tabela serve implicitamente como um índice de cluster, a não ser que seja especificado
CLUSTER na criação ou modificação de outro índice. Embora uma tabela possa ter vários índices, apenas um
poderá ser do tipo cluster.
Para criar um índice deste tipo, dever-se-á ter-se como padrão o do exemplo abaixo:
CREATE INDEX DEPT_IX ON EMP (DEPTNO ASC) CLUSTER;
Como resultado, todas as linhas do mesmo departamento estarão, provavelmente, juntas. Deste
modo, o DB2 poderá, normalmente, aceder a todas as linhas desse departamento numa única leitura. O uso
deste tipo de índice não garante que todas as linhas fiquem guardadas na mesma página. O
armazenamento destas dependerá do tamanho das linhas, do número de linhas e da quantidade de espaço
livre. Da mesma forma, algumas páginas podem conter linhas para mais do que um departamento.
Suponhamos que já temos um clustering index numa tabela já com dados inseridos. Ao tentarmos
criar um novo índice de cluster, o DB2 vai reconhece-lo como tal, passando este ser o índice de cluster da
tabela. Porém, os dados inseridos, ate à data, não serão reorganizados tendo em conta esta alteração. No
entanto, quando o REORG reorganizar o table space, o DB2 organiza os dados de acordo com a sequência
do novo índice. Portanto, se soubermos que desejamos um índice de cluster, deveremos defini-lo antes de
carregar dados na tabela. Se não for possível, dever-se-á criar o índice e reorganizar tabela de seguida.
Índices em tabelas particionadas
Os índices nas tabelas particionadas operam de forma similar aos das tabelas não-particionadas,
porém, são guardados de forma diferente.
Enquanto os índices das tabelas não-particionadas residem num objecto de índice partilhado, o
índice não-particionado de uma tabela particionada é criado no seu próprio objecto de índice num único
table space, ainda que as partições estejam espalhadas por diferentes table spaces. Tanto o database
DB2 - Conceitos e Análise do Sistema 32
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 33/85
5 – INDEXAÇÃO E H ASHING
managed space (DMS) como o system managed space (SMS) suportam o uso de índices em diferentes
localizações que não a dos dados da tabela. Cada índice não-particionado pode ser colocado no seu table
space. Cada table space do índice, deve usar o mesmo tipo de armazenamento que as partições de dados,
seja DMS ou SMS. Índices em grandes table spaces podem conter ate 229 páginas. Todos os table spacesdeverão estar localizados no mesmo grupo de partições da base de dados.
Um índice particionado usa um esquema de organização em que os dados do índice são distribuídos
por várias partições do índice, de acordo com o particionamento da tabela. Cada partição do índice contém
somente tuplos que se encontram na partição de dados correspondente. Todas as partições do índice para
uma dada partição da tabela residem no mesmo objecto de índice.
A partir da versão 9.7 os índices criados sobre XML em colunas XML que se encontram em tabelas
particionadas podem ser particionadas ou não-particionadas. Por defeito estes são particionados.
Vantagens de um índice não particionado:
• Permite a definição de diferentes características do table space para cada índice;
• Os índices podem ser organizados independentemente uns dos outros;
• Melhor performance em fazer cair índices;
• Contenção de I/O reduzida, que ajuda a fornecer um acesso concorrente mais eficiente ao
índice;
• Quando índices individuais são removidos, o espaço fica imediatamente disponível para osistema, sem necessidade de reorganizar o índice.
Vantagens de um índice particionado:
• Melhor desempenho no roll-in roll-out de dados;
• Menor contenção nas páginas do índice, dado que este é particionado.
Índices Bitmap
Normalmente, muitos administradores de bases de dados desejam a criação de bitmap indexes, à
semelhança do que o Oracle proporciona. O DB2, porém, não permite a criação deste tipo de índices. Ao
invés disto, o optimizador retira a carga de criar e manter um índice deste tipo, preferindo criá-los em
tempo de execução cada vez que precisa deles.
O problema com o bitmap indexes estáticos é que sofrem bastante com o impacto da mudança dos
dados. Uma operação de insert, update ou delete precisa de actualizar todos os índices. Assim, os
investigadores do DB2 tomaram a decisão de criar bitmap indexes dinâmicos.
DB2 - Conceitos e Análise do Sistema 33
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 34/85
5 – INDEXAÇÃO E H ASHING
Deste modo, não há nenhum overhead provocado pelas actualizações ou memória em disco gasta e
o DB2 consegue dar-lhe um desempenho semelhante através do acesso ao índice e manipular os RIDs em
memória. Para além disto, o DB2 tem hash joins que permitem desempenhos execelentes desde que exista
memória suficiente para executar tais algoritmos.
5.2 H ASHING
O DB2 permite criar partições com base nos valores de hash de uma ou mais colunas. Esta é a formaconvencional de distribuir os dados num ambiente de bases de dados distribuídas. Para criar tais partições,durante a declaração de CREATE TABLE, dever-se-á adicionar a preposição PARTITIONING KEY column USINGHASHING.
DB2 - Conceitos e Análise do Sistema 34
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 35/85
6 – PROCESSAMENTO E OPTIMIZAÇÃO DE PERGUNTAS
6 – P6 – PROCESSAMENTOROCESSAMENTO EE OOPTIMIZAÇÃOPTIMIZAÇÃO DEDE PPERGUNTASERGUNTAS
6.1 I NTRODUÇÃO À OPTIMIZAÇÃO NO DB2O optimizador é o cérebro do DB2. Ele analisa as instruções de SQL e determina qual o método de
acesso dísponivel mais eficiente para executar cada uma delas. O DB2 faz isso fazendo o parsing da
instrução de forma a determinar quais as tabelas e colunas respectivas que precisam de ser acedidas. O
optimizador, em seguida, consulta as informações do sistema e as estatísticas armazenadas no catálogo do
sistema, de forma a determinar o melhor método de realizar as tarefas necessárias para satisfazer o pedido
de SQL.
Esquema 9: Optimizador DB2
O optimizador é equivalente, em função, a um especialista do sistema. Um especialista do sistema é
um conjunto de normas-padrão que, quando combinadas com os dados conjunturais, retornam a opinião
de um “especialista”. No DB2, o optimizador processa pareceres de especialistas sobre os métodos de
devolver os dados com base nas informações do momento, guardadas no catálogo do DB2, e da query de
input.
Optimizar o acesso a dados no DB2
A noção de optimizar o acesso aos dados no DBMS é uma das mais poderosas capacidades do DB2.
De notar que para aceder aos dados, nós limitamo-nos a dizer ao sistema quais os dados a devolver e não a
forma como o sistema o deve fazer. Independentemente de como os dados estão fisicamente armazenados
e manipulados, o DB2 e o SQL podem, ainda, aceder-lhes. Esta separação dos critérios de acesso das
características físicas de armazenamento é a chamada independência dos dados físicos. O optimizador é o
componente que faz de interface nesta independência.
DB2 - Conceitos e Análise do Sistema 35
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 36/85
6 – PROCESSAMENTO E OPTIMIZAÇÃO DE PERGUNTAS
Se removermos os índices, o DB2 consegue aceder na mesma aos dados (embora de forma menos
eficiente). Se adicionarmos uma coluna a uma tabela que está a ser acedida, o sistema consegue manipular
os dados sem que no entanto seja preciso alterar o código do programa. Isto é possível porque os caminhos
de acesso físico aos dados do DB2 não são codificados pelos programadores nas aplicações, mas simgerados pelo próprio DB2.
Comparemos isto com os sistemas sem gestor de base de dados, em que o programador deverá
conhecer a estrutura física destes. Se houver um índice, o programador deverá escrever o código necessário
de forma a utilizá-lo. Se alguém remover o índice, o programa já não funcionará, a não ser que o
programador volte a reescrever o código. Não é assim com o DB2 e o SQL. Toda esta flexibilidade é atribuída
à capacidade do DB2 em optimizar os pedidos de manipulação de dados automaticamente.
O optimizador executa uma série de cálculos complexos com numa série de informações de que
dispõe. Para observar como funciona o optimizador, vejamos o optimizador como uma ferramenta queexecuta o processo em quatro etapas:
• Recebe e verifica a sintaxe da instrução SQL.
• Analisa o ambiente e optimiza o método de acesso de forma a satisfazer a instrução SQL.
• Cria instruções em código-máquina para executar o SQL optimizado.
• Executa as instruções ou armazena-as para futura execução.
O segundo passo deste processo é, provavelmente, o mais intrigante. Como é que o optimizador
decide como executar um vasto conjunto de instruções SQL indicadas pelo utilizador?
O optimizador tem muitos tipos de estratégias para optimizar o SQL. Como escolher qual destas é a
melhor para optimizar o acesso aos dados? A IBM não revela em profundidade os detalhes de como o
optimizador determina o melhor plano de acesso, mas o optimizador é baseado numa comparação de
custos. Isto significa que o optimizador irá sempre tentar formular um caminho de acesso para cada
consulta, de forma a minimizar o custo total da operação. Para conseguir isso, o optimizador do DB2 aplica
fórmulas de custo que avaliam e pesam 4 factores por cada potencial plano de acesso: custo de CPU, custo
de I/O, informação estatística no catálogo do sistema e a instrução de SQL inserida.
Hints
A IBM tem uma filosofia diferente da do Oracle, por exemplo, em relação à questão da optimização
do SQL. Se o DB2 não escolher o plano ideal e o pior acesso não se dever uma limitação inerente à query ,
considera-se que é um defeito do produto e prefere resolver o problema na fonte, para que todos os
utilizadores possam beneficiar também. Portanto, segundo eles, não haverá uma grande necessidade da
existência de hints.
Para outros casos, onde as limitações inerentes à consulta fazem com que seja difícil encontrar omelhor plano, o utilizador poderá influenciar o optimizador, fornecendo informações adicionais de
selectividade.
DB2 - Conceitos e Análise do Sistema 36
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 37/85
6 – PROCESSAMENTO E OPTIMIZAÇÃO DE PERGUNTAS
6.2 T IPOS DE J UNÇÕES
Esta secção descreve como o optimizador une as tabelas que forem necessárias para retornar os
resultados de uma pergunta.
Uma junção é o processo que combina informação de duas ou mais tabelas com base em algum
domínio comum de informação. Linhas de uma tabela são emparelhadas com linhas de outra quando a
informação contida nas linhas faz correspondência no critério de junção.
Dependendo da existência de um predicado de junção, bem como dos custos envolvidos,
determinados pela tabela e as estatísticas do índice, o optimizador escolhe um dos seguintes métodos de
junção:
• Nested-loop join
• Merge join
• Hash join
Quando duas tabelas se juntam, uma deles é seleccionada como tabela externa e a outra como
interna. A tabela externa é acedida primeiro e lida apenas uma vez. Se a tabela interna é lida muitas vezes,
depende do tipo de junção e dos índices que existem. Mesmo que uma consulta junte mais que 2 tabelas, o
optimizador só junta 2 tabelas de cada vez, sendo criadas, se necessário for, tabelas temporárias que
guardem os resultados intermédios.
Podemos fornecer explicitamente operadores de junção, tais como INNER ou LEFT OUTER JOIN paradeterminar como as tabelas são usadas no processo de junção. Antes de se alterar a pergunta desta
maneira, dever-se-á deixar o optimizador determinar qual a melhor forma de realizar a junção. Em seguida,
deve analisar-se o desempenho da query para decidir quando adicionar operadores de junção.
Nested-loop join
Um nested-loop join é realizado de uma das seguintes maneiras:
• Percorre a tabela interna para cada linha acedida na tabela de fora.
• Realiza uma pesquisa de índice na tabela interna para cada linha acedida na tabela exterior.
Quando é avaliada uma nested-loop join, o optimizador decide também se deve, ou não, ordenar a
tabela de fora antes de efectuar a junção. Se decidir ordenar a tabela de fora, em ordem às colunas de
junção, o número de operações de leitura para aceder às páginas em disco da tabela de dentro pode ser
reduzido, dado que é mais provável já se encontrarem no buffer pool . Se a junção utiliza um índice
altamente clusterizado para aceder à tabela interior e se a tabela exterior já se encontra ordenada, o
número de páginas de índice que necessitam de ser acedidas pode ser, também, minimizado.
DB2 - Conceitos e Análise do Sistema 37
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 38/85
6 – PROCESSAMENTO E OPTIMIZAÇÃO DE PERGUNTAS
Além disto, se o optimizador espera que a junção fará com que, mais tarde, a ordenação seja mais
cara, poderá decidir fazer a ordenação logo à cabeça, antes de efectuar a junção. Uma ordenação pode ser
requerida mais tarde, de forma a satisfazer um GROUP BY, DISTINCT, ORDER BY ou um merge join.
Merge join
Um merge join, normalmente conhecido como merge scan join ou sort merge join, requer que um
predicado seja da forma table1.column = table2.column. Isto é chamado de predicado de igualdade. O
merge join necessita que o input esteja previamente ordenado pelas colunas de junção, através de um
acesso a índice ou de uma ordenação da tabela. Este algoritmo não poderá ser utilizado para unir tabelas
por uma coluna do tipo LONG ou large object (LOB).
Neste tipo de junção, as tabelas a unir são varridas ao mesmo tempo. A tabela exterior é percorrida
uma única vez. Também a tabela interior não varrida mais que uma vez, a menos que na tabela exteriorocorram valores repetidos na coluna de junção. Se houver valores repetidos, um grupo de linhas da tabela
interior pode vir a ser varrida de novo.
Hash join
A junção por hash requer que um ou mais predicados sejam da forma table1.columnX =
table2.columnY, para os quais os tipos de coluna são os mesmos. Para colunas do tipo CHAR, o
comprimento deverá ser o mesmo. Para colunas do tipo DECIMAL, a precisão e a escala deverá ser a
mesma. O tipo, à semelhança do merge join, não poderá ser LONG nem LOB.
Primeiro, a tabela interna designada é varrida e as linhas copiadas para os buffers de memória. Os
buffers de memória são divididos em partições baseadas num valor de hash que é computado nas colunas
dos predicados de junção. Se o tamanho da tabela interna exceder o espaço disponível no sort heap, os
buffers das partições seleccionadas são escritos em tabelas temporárias.
Quando a tabela interna tiver sido processada, a segunda ou a tabela exterior é varrida e as suas
linhas são comparadas com as linhas da tabela interna, através do valor de hash calculado nas colunas dos
predicados de junção. Se o valor de hash de uma linha da tabela exterior fizer match com uma linha da
tabela interior, os valores reais de ambas são comparados.
Linhas da tabela exterior que correspondem a partição que não foram escritas em tabelas
temporárias são comparadas imediatamente com as linhas da tabela interior em memória. Se a partição
correspondente da tabela interior tiver sido escrita numa tabela temporária, a linha da tabela exterior é
escrita, também, numa tabela temporária. Finalmente, as partições correspondentes são lidas das tabelas
temporárias, os hash values das suas linhas são comparados e, caso sejam iguais, verificam-se também os
seus valores reais.
DB2 - Conceitos e Análise do Sistema 38
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 39/85
6 – PROCESSAMENTO E OPTIMIZAÇÃO DE PERGUNTAS
6.3 E STRATÉGIAS DE OPTIMIZAÇÃO DE J UNÇÕES
O optimizador utiliza vários métodos para escolher uma estratégia óptima de junção para uma
query. Entre esses métodos, encontram-se as seguintes estratégias de procura, que são determinadas pelaclasse de optimização da consulta:
• Greedy join enumeration
• Eficiente em tempo e espaço;
• Enumeração numa única direcção, isto é, uma vez que um método de join é
seleccionado para duas tabelas, ele não é alterado mais nenhuma vez durante a
optimização;
• Pode perder o melhor plano de acesso ao tentar juntar várias tabelas. Se a query juntar somente duas ou três tabelas, o plano de acesso pela enumeração greedy
join é o mesmo que o escolhido pela dynamic join enumeration. Isto é
particularmente verdade se a pergunta tem muitos predicados de junção na mesma
coluna, seja especificado explicitamente ou implicitamente gerado através do
fechamento transitivo do predicado.
• Dynamic programming join enumeration
• Requesitos de espaço e tempo aumentam exponencialmente com o aumento do
número de tabelas a juntar;
• Procura extensa e exaustiva pelo melhor plano de acesso.
O algoritmo de enumeração das junções é um determinante importante no número de
combinações de planos que optimizador explora.
Star-Schema Joins
As tabelas referenciadas numa consulta são quase sempre relacionadas por predicados de junção.
Se duas tabelas são associadas sem um predicado de junção, forma-se o produto Cartesiano destas. Num
produto Cartesiano, cada linha da primeira tabela é combinada com cada linha da segunda tabela, criando
uma tabela de resultados que consiste no produto do tamanho das duas tabelas, que é normalmente muito
grande. Uma vez que é improvável que tal plano tenha um bom resultado, o optimizador evita determinar o
custo deste tipo de operações.
As únicas excepções ocorrem quando a classe de optimização é definida como 9 ou no caso especial
dos star schemas. Um star schema contém uma tabela central chamada de fact table e outras tabelas
denominadas de dimension tables. As dimension tables têm uma única operação de junção, que as associa à
fact table, independentemente da pergunta. Cada dimension table contém valores adicionais que
expandem a informação de uma coluna particular da fact table. Uma pergunta típica consiste em vários
predicados locais que referenciam valores nas dimension tables e que contêm predicados de junção que as
DB2 - Conceitos e Análise do Sistema 39
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 40/85
6 – PROCESSAMENTO E OPTIMIZAÇÃO DE PERGUNTAS
relacionam com a fact table. Para estas queries pode ser benéfico computar o produto Cartesiano de
pequenas dimension tables antes de aceder à grande fact table. Esta técnica é benéfica quando múltiplos
predicados de junção correspondem a índices multi-coluna.
O DB2 pode reconhecer consultas em bases de dados projectadas com star schemas que têm pelo
menos duas dimension tables e que podem aumentar o espaço de procura a possíveis planos que façam a
computação do produto Cartesiano das dimension tables. Se o plano que computa o produto Cartesiano
tiver o menor custo estimado, é, então, o seleccionado pelo optimizador.
Composite tables
Quando o resultado de juntar um par de tabelas é uma nova tabela conhecida por composite table,
esta tabela, normalmente, é a tabela exterior de outra junção, com uma tabela interior. Este processo é
conhecido normalmente por “composite outer” join. Em alguns casos, particularmente quando se usa atécnica de enumeração greedy-join, é útil fazer com que o resultado da junção de duas tabelas seja a tabela
interior de uma futura junção. Quando a tabela interior de uma junção é o resultado da junção de duas ou
mais tabelas, este plano é um “composite inner” join.
Efeitos da ordenação
Quando o optimizador escolhe um plano de acesso, este considera que o impacto que a ordenação
dos dados tem no desempenho. A ordenação ocorre quando nenhum índice consegue satisfazer a
ordenação requerida para as linhas desejadas. A ordenação pode também ocorrer quando o optimizador
determina que a ordenação é menos cara que o varrimento do índice. O optimizador ordena os dados deuma das seguintes formas:
• Fazer pipe dos resultados da ordenação quando a pergunta é executada;
• Tratar internamente, com o gestor da base de dados, da ordenação.
Ordenações piped vs. non-piped
Se a lista final ordenada puder ser lida numa única passagem sequencial, os resultados podem ser
piped. Fazer pipe torna mais rápida a comunicação dos resultados da ordenação. O optimizador escolhe
canalizar os resultados da pesquisa sempre que possível.
Seja ou não a ordenação canalizada, o tempo da ordenação depende de vários factores, entre os
quais se encontram o número de linhas a ordenar, o tamanho da chave e a largura da linha. Se as linhas a
serem ordenadas ocuparem mais espaço do que o disponível no sort heap, são realizados vários passos de
ordenação, onde cada passo ordena um subconjunto do conjunto de linhas a serem ordenadas. Cada
passagem da ordenação é guardada numa tabela temporária no buffer pool . Se não houver espaço
suficiente no buffer pool , as páginas desta tabela temporária podem vir a ser escritas em disco. Quando
todos os passos da ordenação estão completos, os subconjuntos ordenados deverão ser merged num único
conjunto de linhas. Se a ordenação for piped , as linhas são tratadas directamente pelos Relational Data
Services assim que os subconjuntos se unem.
DB2 - Conceitos e Análise do Sistema 40
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 41/85
6 – PROCESSAMENTO E OPTIMIZAÇÃO DE PERGUNTAS
6.4 E STRATÉGIAS DE OPTIMIZAÇÃO DE QUERIES
Esta secção aborda as estratégias particulares que o optimizador pode utilizar dentro de uma
partição e em tabelas MDC (multi-dimensional clustering).
Estratégias de optimização dentro de uma partição
O optimizador pode escolher um plano de acesso para executar uma consulta em paralelo numa
única partição da base de dados se o grau de paralelismo for especificado quando a instrução de SQL é
compilada.
Em tempo de execução, múltiplos agentes da base de dados, denominados subagentes, são criados
para executar a consulta. O número de subagentes é menor ou igual que o grau de paralelismo especificado
na instrução dada ao sistema.
Para paralelizar um plano de acesso, o optimizador divide-o em uma porção que é executada pelo
subagente e em outra que é executada pelo agente coordenador. Os subagentes passam dados, através de
filas de espera, para o agente coordenador ou outro dos subagentes. Em bases de dados particionadas, os
subagentes podem enviar ou receber dados, através de filas, de subagentes presentes em outras partições
da base de dados.
Estratégias de varrimento intra-partição
Varrimentos relacionais e varrimentos de índice podem ser executados em paralelo na mesma
tabela ou índice. Para varrimentos relacionais paralelos, a tabela é dividida em intervalos de páginas ou
linhas, sendo cada um dos intervalos atribuído a um subagente. Cada subagente percorre o seu intervalo e,
quando acaba, é atribuído a outro intervalo. É o optimizador que determina a unidade de varrimento (uma
página ou linha) e a granularidade deste.
Varrimentos paralelos proporcionam uma distribuição uniforme do trabalho pelos subagentes. O
objecto dos scans paralelos é equilibrar a carga de trabalho pelos vários subagentes e mantê-los igualmente
ocupados. Se o número de agentes ocupados for igual ao número de processadores disponíveis e os discos
não estão sobrecarregados com trabalhos de I/O, então os recursos da máquina deverão estar a ser
utilizados de uma forma eficaz.
Outras estratégias de planos de acesso podem causar um desequilíbrio dos dados com a execução
da consulta. O optimizador escolhe as melhores estratégias paralelas que mantêm um equilíbrio de dados
entre os subagentes.
DB2 - Conceitos e Análise do Sistema 41
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 42/85
6 – PROCESSAMENTO E OPTIMIZAÇÃO DE PERGUNTAS
Estratégias de ordenação paralela intra-partição
O optimizador pode escolher uma das seguintes estratégias seguintes de ordenação paralela:
• Round-robin sort
Este é também conhecido como redistribution sort. Este método usa memória partilhada e
tenta distribuir eficientemente os dados por todos os subagentes. Este utiliza um algoritmo
de round robin de forma a proporcionar uma distribuição uniforme. Primeiro, é criado uma
ordenação individual para cada subagente. Durante a fase de inserção, os subagentes
inserem em cada uma das ordenações individuais, de uma forma round-robi n, de forma a
obter uma boa distribuição dos dados.
• Partitioned sort
Esta é semelhante à ordenação por round-robin em que uma ordenação é criada por cada
subagente. Os subagentes aplicam uma função de hash para ordenar as colunas de forma a
determinar quem qual ordenação deve ser a linha inserida. Por exemplo, se as tabelas
(interior e exterior) de uma junção forem ordenações particionadas, um subagente pode
fazer um merge join em paralelo das partições correspondentes.
• Replicated sort
Este tipo de ordenação é utilizada se cada subagente requerer o output completo da
ordenação. Uma ordenação é criada e os subagentes são sincronizados à medida que as
linhas são inseridas na ordenação. Quando a ordenação está completa, cada subagente lê
toda a ordenação. Se o número de linhas for pequeno, este tipo de sort pode ser utilizado
para reequilibrar o fluxo de dados.
• Shared sort
Este tipo de ordenação é o mesmo que a replicated sort , excepto o facto de os subagentes
abrirem um scan paralelo sobre os resultados de forma a distribuir os dados pelos vários
subagentes de uma forma similar ao round-robin sort .
Tabelas temporárias paralelas numa partição
Os subagentes podem cooperar de forma a produzir uma tabela temporária através da inserção de
dados na mesma tabela. Esta é chamada de shared temporary table. Os subagentes podem abrir scans
privados ou paralelos na tabela, dependendo se o fluxo de dados é para ser replicado ou particionado.
Estratégias de agregação paralela numa partição
Operações de agregação podem ser executadas em paralelo por subagentes. Uma operação de
agregação requer que os dados estejam ordenados nas colunas em que o agrupamento ocorre. Se um
subagente pode ter a garantia que recebe todas as linhas de uma conjunto de valores da coluna a agrupar,
então pode executar a agregação. Isto pode acontecer se a stream estiver particionada de acordo com os
DB2 - Conceitos e Análise do Sistema 42
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 43/85
6 – PROCESSAMENTO E OPTIMIZAÇÃO DE PERGUNTAS
valores das grouping columns por causa ordenamento particionado anterior.
Caso contrário, o subagente pode executar uma agregação parcial e usar outra estratégia para
completar a agregação. Algumas dessas estratégias são:
• Enviar os dados parcialmente agregados para o agente coordenador através de uma
merging table queue. O coordenador, então, completa a agregação.
• Inserir os dados parcialmente agregados num sort particionado. O sort é particionado nos
valores das colunas de agrupamento e dá a garantia que todas as linhas para um dado set
estão contidas na mesma partição.
• Se o fluxo precisa de ser replicado de forma a equilibrar o processamento, os dados
parcialmente agregados podem ser inseridos num sort replicado. Cada subagente completa
a agregação utilizando o sort replicado e recebe uma cópia idêntica do resultado deagregação.
Estratégias de junção paralela numa partição
Operações de junção podem ser realizadas em paralelo por subagentes. Junções paralelas são
determinadas através das características do fluxo de dados.
Uma junção pode ser paralelizada através da replicação ou do particionamento de um fluxo de
dados nas tabelas internas e externas da junção. Por exemplo, um nested-loop join pode ser paralelizado se
o fluxo da tabela externa for particionado para um scan paralelo e o fluxo da interna for re-avaliado
independentemente por cada subagente. Um merge join pode ser paralelizado se os fluxos de ambas as
tabelas forem particionados por valor.
Estratégias de optimização para tabelas MDC
Se criarmos tabelas MDC, o desempenho de muitas consultas pode ser melhorado, dado que o
optimizador pode aplicar estratégias adicionais de optimização. Estas estratégias são principalmente
baseadas nas melhorias de eficiência nos índices de bloco, mas a vantagem de fazer clustering em mais do
que uma dimensão também permite uma obtenção mais rápida dos dados.
DB2 - Conceitos e Análise do Sistema 43
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 44/85
6 – PROCESSAMENTO E OPTIMIZAÇÃO DE PERGUNTAS
6.5 M ATERIALIZED QUERY T ABLES
As tabelas de consultas materializadas (MQTs) são uma poderosa forma de melhorar o tempo de
resposta para queries complexas, especialmente as que podem requerer algumas das seguintes operações:
• Dados agregados de uma ou mais dimensões
• Junções e agregações de um grupo de tabelas
• Dados de um subconjunto acedido frequentemente, ou seja, de uma partição horizontal ou
vertical
• Dados repartidos de uma tabela, ou parte de uma tabela, num ambiente de bases de dados
particionadas
O conhecimento acerca das MQTs está integrado no compilador de SQL. No compilador de SQL, a
fase de reescrita da consulta e o match de queries, pelo optimizador, com as MQTs, determinam quando se
deve substituir uma MQT por uma query que aceda às tabelas de base. Se uma MQT for utilizada, o uso do
EXPLAIN poderá dar alguma informação suplementar acerca de qual a MQT utilizada.
Dado que as MQTs se comportam como tabelas convencionais em muitos aspectos, poder-se-ão
aplicar as mesmas directrizes que nestas na optimização do acesso aos dados através dos table spaces, na
criação de índices, nos pedidos de RUNSTATS, por exemplo.
Normalmente, os resultados pretendidos para as pesquisas não estão guardados, tal comodesejados, nas MQTs. Porém, o custo de computar as respostas usando as MQTs poderá ser
significativamente reduzido comparando com o custo necessário de aceder a uma grande tabela, dado que
parte da solução poderá já estar parcialmente computada. Deste modo, o uso de MQTs poderá reduzir
significativamente os custos de operações que envolvam junções, ordenações e agregações de dados.
Quanto maiores as tabelas de base forem, maior poderá a ser a melhoria nos tempos de resposta,
dado que o MQT cresce mais lentamente do que as tabelas convencionais. Os MQTs podem eliminar
efectivamente a repetição de trabalho por parte das consultas através da sua computação uma única vez e
armazenamento dos resultados numa MQT. Depois de construída, esta deverá ser refrescada, de forma a
permitir que o seu conteúdo possa vir a ser reutilizado por outras consultas.
6.6 SQL E XPLAIN
O compilador de SQL pode capturar informações acerca do plano de acesso e do ambiente, estático
ou dinâmico, das instruções de SQL. As informações recolhidas ajudam o utilizador a compreender como
são as instruções individuais de SQL executadas, de modo a este poder ajustar as suas instruções e as
configurações do gestor da base de dados, melhorando, assim, a performance do sistema.
DB2 - Conceitos e Análise do Sistema 44
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 45/85
6 – PROCESSAMENTO E OPTIMIZAÇÃO DE PERGUNTAS
Deveremos recolher e utilizar os dados do planeamento pelos seguintes motivos:
• Para perceber como o gestor da base de dados acede às tabelas e índices de forma a
satisfazer o nosso pedido;
• Para avaliar os ajustes que fizemos na base de dados.
Quando alteramos algum parâmetro do gestor da base de dados, as instruções de SQL, ou a
base de dados, deveremos examinar o explain data de forma a perceber de que modo é que
as nossas acções tiveram, ou não, impacto no desempenho do sistema.
A informação capturada inclui:
• Sequências de operações executadas para processar a consulta;
• Informações de custos;
• Predicados e estimativas de selecção para cada um deles;
• Estatísticas para todos os objectos referenciados no comando SQL no momento em que a
informação de planeamento é capturada;
• Valores para as host variables, parâmetros ou registos especiais utilizados na reoptimização
da instrução SQL.
Antes de capturar a informação de planeamento, o utilizador deverá criar tabelas relacionais nas
quais o optimizador irá guardar estes dados, e definir os registos especiais que determinam o tipo de
informação de planeamento que é capturada.
Para exibir a informação do explain, poder-se-á utilizar tanto a ferramenta da linha de comandos
como o Visual Explain. A ferramenta a utilizar determina como deverão ser configuradas as variáveis de
registo que determinam quais deverão ser os dados a recolher. Por exemplo, se o pretendermos utilizar
somente o Visual Explain, só será necessário capturar a informação no momento. Por outro lado, se for
desejo realizar uma análise detalhada com um dos utilitários da linha de comandos ou com instruções de
SQL personalizadas, deverá ser capturada toda a informação de planeamento.
Ferramentas de análise de planeamento
O DB2 oferece uma quantidade abrangente de ferramentas de explicação do planeamento que
fornecem informações detalhadas sobre o plano de acesso que o optimizador escolhe para uma instrução
SQL. As tabelas que guardam a explain data estão acessíveis em todas as plataformas suportadas e contêm
informações para instruções de SQL estáticas e dinâmicas. Muitas das ferramentas ou métodos dão-nos a
flexibilidade necessária para capturar, visualizar e analisar as informações de planeamento.
DB2 - Conceitos e Análise do Sistema 45
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 46/85
6 – PROCESSAMENTO E OPTIMIZAÇÃO DE PERGUNTAS
A informação detalhada do optimizador permite uma análise profunda de um plano de acesso que
esteja guardado nas explain tables, separado do plano de acesso efectivo. Poder-se-á utilizar um ou mais
dos seguintes métodos para obter as informações contidas nas explain tables:
• Usar o Visual Explain para visualizar as informações instantaneamente
Invocar o Visual Explain a partir do Control Center para ver uma interface gráfica de um
plano de acesso para uma dada query , permitindo a análise de instruções de SQL estáticas e
dinâmicas. O Visual Explain permite que o utilizador visualize snapshots capturados numa
outra plataforma. Por exemplo, um cliente em Windows NT poderá visualizar os grafos
gerados num servidor HP-UX.
• Usar a ferramenta db2exfmt para mostrar a informação num output pré-formatado.
• Usar as ferramentas db2expln e dynexpln
Para ver as informações acerca do plano de acesso disponível para um ou mais pacotes de
instruções de SQL estáticas, dever-se-á utilizar a ferramenta db2expln na linha de
comandos. O db2expln mostra a implementação efectiva do plano de acesso escolhido. Esta
não mostra informações do optimizador.
A ferramenta dynexpln, que faz uso do db2expln, fornece uma forma rápida de explicar
instruções de SQL dinâmicas que não contêm parameter markers. O uso do db2expln
dentro do dynexpln é feito através da transformação da instrução SQL de input numa
declaração estática num pseudo-pacote. Quando isto ocorre, as informações podem não sercompletamente precisas. Se for desejada completa precisão, dever-se-á usar o explain.
A ferramenta db2expln dá-nos uma visão compacta em inglês acerca das operações que
irão ocorrer em runtime, através de uma análise do plano de acesso gerado.
A tabela seguinte sumariza as diferentes ferramentas existentes no DB2 para a análise do
planeamento, bem como as suas características individuais.
Características VisualExplain
Explaintables
db2exfmt db2expln dynexpln
GUI-interface aOutput de texto a a a
Análise de queries de SQL estáticas(rápida e pouco precisa)
a
Suporta SQL estático a a a aSuporta SQL dinâmico a a a a aSuporta aplicações CLI a a a
Disponível para DRDA ApplicationRequesters
a
Informação detalhada dooptimizador
a a a
DB2 - Conceitos e Análise do Sistema 46
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 47/85
6 – PROCESSAMENTO E OPTIMIZAÇÃO DE PERGUNTAS
Adequado para análise de múltiplasinstruções
a a a a
Informação acessível a partir duma
aplicação
a
DB2 - Conceitos e Análise do Sistema 47
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 48/85
7 – TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA
7 – T7 – TRANSACÇÕESRANSACÇÕES EE CCONTROLOONTROLO DEDE CCONCORRÊNCIAONCORRÊNCIA
Neste capítulo discute-se como permitir que múltiplos utilizadores tenham acesso à mesma base dedados, ao mesmo tempo, sem interferirem uns com os outros, mantendo as suas operações consistentes.
Serão discutidos os conceitos de transacções, concorrência e locking.
7.1 I NTRODUÇÃO ÀS T RANSACÇÕES
Uma transacção ou unidade de trabalho consiste em um ou mais comandos de SQL que, quando
executados, devem ser considerados com um único comando, isto é, se um dos comandos falhar, toda a
transacção falha, e todas as indicações executadas até ao momento da falha também são desfeitas. Uma
transacção termina com o comando COMMIT, que também significa o começo de uma nova transacção. Na
figura seguinte poder-se-á ver um exemplo de uma transacção.
Esquema 10: Exemplo de Transacção
Neste exemplo, pretende-se transferir 100 dólares da conta Savings para a conta Checking. A
seguinte sequência de eventos é necessária para realizar a seguinte tarefa:
Debita-se $100 da conta Savigs;
Credita-se $100 para a conta Checking.
Se a sequência de eventos acima descrita não for tratada como uma única unidade de trabalho,
transacção, imagine-se o que aconteceria se uma falha eléctrica ocorresse após o débito da conta Savings e
imediatamente antes de se creditar $100 na conta Checking. O titular da conta Savings perderia $100!
DB2 - Conceitos e Análise do Sistema 48
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 49/85
7 – TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA
7.2 C ONCORRÊNCIA EM T RANSACÇÕES
A concorrência implica que diversos utilizadores possam trabalhar ao mesmo tempo nos mesmos
objectos da base de dados. O DB2 foi projectado como uma base de dados multi-utilizador. O acesso aos
dados deve ser coordenado correctamente e transparente usando um mecanismo para assegurar a
integridade e a consistência dos dados.
Esquema 11: Acesso concorrente a uma linha duma tabela
Na figura acima, há quatro aplicações, App A, App B, App C e App D que estão a tentar aceder à
mesma linha (linha 2) de uma tabela. Sem nenhum controlo de concorrência, todas as aplicações podiam
executar as operações sobre a mesma linha. Supondo que todas as aplicações estão a actualizar a coluna
“Age” na linha 2 com valores diferentes, a aplicação que faz a última actualização será provavelmente a
vencedora nesta situação. Deve ser óbvio, neste exemplo, que a utilização de algum controlo de
concorrência é necessária para garantir resultados consistentes. Este controlo de concorrência é baseado na
utilização de locks.
Os conceitos de locking e concorrência andam em conjunto. Fazer lock pára temporariamente a
execução de outras aplicações até que uma outra aplicação termine. Quantos mais locks um sistema tenha,
menos concorrência é possível. Por outro lado, quantos menos locks um sistema tenha, mais concorrência
será possível.
Tipos de Locks
Os locks são adquiridos automaticamente conforme seja necessário executar uma transacção e são
libertados quando a transacção termina (usando o comando COMMIT ou o comando ROLLBACK). Os locks
podem ser adquiridos em tabelas ou em linhas. Há três tipos básicos de locks:
• Locks partilhados (S locks) – Sob um S lock, processos concorrentes da aplicação estão
limitados a operações de leitura sobre os dados;
• Locks de update (U locks) – Sob um U lock, processos concorrentes da aplicação estão
limitados a operações de leitura sobre os dados, se estes processos não tiverem declarado
que podem, eventualmente, querer actualizar uma linha. O gestor da base de dados
assume que o processo que está actualmente a “olhar” para uma linha pode actualiza-la.
DB2 - Conceitos e Análise do Sistema 49
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 50/85
7 – TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA
• Locks exclusivos (X locks) – Sob um X lock , processos concorrentes da aplicação estão
prevenidos para qualquer tipo de acesso aos dados. Isto não é aplicável para processos que
tenham um nível de isolamento UR (veremos o que é mais à frente).
A figura abaixo, demonstra-nos o mesmo exemplo, mas, desta vez, com recurso a locks.
Esquema 12: Concorrência com locks
Por exemplo, nesta imagem, se o App B for a primeira a aceder à 2ª linha, e se estiver a executar um
UPDATE, a App B é detentora do X lock na linha. Quando a App A, App C e App D tentarem aceder à mesma
linha, não poderão fazer UPDATE por causa do lock exclusivo. Este tipo de controlo permite a consistência e
integridade dos dados.
Problemas na ausência de controlo de concorrência
Sem alguma forma de controlo de concorrência, podem acontecer os seguintes problemas.
• Lost Update
• Uncommmitted Read
• Non-repeatable Read
• Phantom Read
Lost Update
A perca de uma actualização é um problema semelhante ao que foi anteriormente explicado nesta
secção. A aplicação que executa a última actualização será a vencedora e todas as alterações anteriores
serão perdidas.
DB2 - Conceitos e Análise do Sistema 50
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 51/85
7 – TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA
Na figura abaixo existem dois pedidos a tentar actualizar a mesma linha. O que está à esquerda é o
pedido App1, e o da direita é o pedido App2. A sequência de eventos é a seguinte:
•
App1 actualiza a linha;
• App2 actualiza a mesma linha;
• App1 faz COMMIT;
• App2 faz COMMIT;
Esquema 13: Exemplo de Lost Update
A actualização do App1 é perdida quando App2 fizer a sua actualização, daí o termo “Lost Update”
(perca de actualização).
Uncommitted Read
Uma Uncommitted Read, ou leitura “suja”, permite que uma aplicação leia informações que não
tenham sido guardadas e, consequentemente não são precisas.
DB2 - Conceitos e Análise do Sistema 51
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 52/85
7 – TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA
Esquema 14: Exemplo de Uncommitted Read
A figura acima segue a seguinte sequência de eventos:
• App1 actualiza uma linha;
• App2 lê o novo valor da nova linha;
• App1 volta atrás nas mudanças que fez na linha.
A App2 está a ler dados que ainda não foram gravados, logo são dados inválidos, sendo por isso este
problema chamado de “Uncommitted Read” (leitura de dados ainda não gravados).
Non-Repeatable Read
A leitura não-repetitiva implica que não se pode obter o mesmo resultado após executar a mesma
leitura na mesma operação.
Esquema 15: Exemplo de Non-Repeatable Read
DB2 - Conceitos e Análise do Sistema 52
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 53/85
7 – TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA
Na figura acima considere-se que alguém está a tentar reservar um voo de Dallas para Honolulu. A
sequência de eventos é a seguinte:
•
App1 abre um cursor (result set ) obtendo o que se pode ver na figura acima;
• App2 elimina uma linha do result set (por exemplo, a linha com o destino San Jose);
• App2 faz COMMIT;
• App1 fecha e reabre o result set .
Neste caso, uma vez que App1 não iria obter os mesmos dados numa segunda leitura, não pode
reproduzir os dados, sendo por isso que este problema é chamado de “Non-Repeatable Read”.
Phantom Read
O problema da leitura fantasma é semelhante ao “Non-Repeatable Read”, a diferença é que
pesquisas posteriores poder-se-á obter mais linhas em vez de menos linhas.
Esquema 16: Exemplo de Phantom Read
A figura acima mostra a seguinte sequência de eventos:
• App1 abre um result set ;
• App2 adiciona uma linha à base de dados que apareceria no mesmo result set ;
• App2 faz COMMIT;
• App1 fecha e reabre o result set .
DB2 - Conceitos e Análise do Sistema 53
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 54/85
7 – TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA
Neste caso, a App1 não iria obter os mesmos dados de uma segunda leitura, iria obter mais linhas,
por isso é que este problema é chamado de “Phantom Read” (leitura fantasma).~
7.3 N ÍVEIS DE I SOLAMENTO EM T RANSACÇÕES
Pode considerar-se os níveis de isolamento como protocolos fechados onde, em função do nível de
isolamento escolhido, uma aplicação pode obter diferentes comportamentos para o lock da base de dados.
O DB2 fornece diferentes níveis de protecção para isolar os dados:
• Uncommitted Read (UR)
• Cursor Stability (CS)
• Read Stability (RS)
• Repeatable Read (RR)
Uncommitted Read
A protecção uncommitted read é também conhecida como leitura suja (dirty read). É o nível mais
baixo de isolamento e oferece maior grau de concorrência. Nenhum lock de linha é obtido para operações
de leitura, a não ser que outra aplicação tente eliminar ou alterar uma tabela. As operações de actualização
agem como se estivessem a usar o nível de isolamento cursor stability .
Problemas que ainda são possíveis de acontecer com este nível de isolamento:
• Uncommited read
• Non-repeatable read
• Phantom read
Problemas evitados com este nível de isolamento:
• Loss of update
Cursor Stability
Cursor stability é o nível de isolamento por omissão. Oferece um grau mínimo de locking.
Basicamente, com este nível de isolamento, uma aplicação que esteja a trabalhar numa linha de um result
set adquire lock . Se a linha é só para ler, o lock é mantido até que a nova linha seja obtida ou a transacção
terminada. Se a linha é actualizada, o lock é mantido até a transacção terminar. Com este nível de
isolamento, nenhuma outra aplicação poderá actualizar ou apagar uma linha enquanto um updatable
cursor estiver posicionado sobre esta.
DB2 - Conceitos e Análise do Sistema 54
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 55/85
7 – TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA
Problemas que ainda são possíveis de acontecer com este nível de isolamento:
• Non-repeatable read
• Phantom read
Problemas evitados com este nível de isolamento:
• Loss of update
• Uncommitted read
Read Stability
Com o read stability , todas as linhas que uma aplicação pede durante uma transacção adquiremlock . Para um dado cursor (result set ), é adquirido o lock para todas as linhas que constituem o result set .
Por exemplo, se se tem uma tabela com 10000 filas e a query devolve 10 linhas, só estas linhas estão sob
lock . A read stability usa um método moderado de locking. Este nível de isolamento assegura que os dados
retornados permanecem inalterados até ao momento em que a aplicação vê os dados, mesmo que sejam
usadas tabelas temporárias ou bloqueamento de linhas.
Problemas que ainda são possíveis de acontecer com este nível de isolamento:
• Phantom read
Problemas evitados com este nível de isolamento:
• Loss of update
• Uncommitted read
• Non-repeatable read
Repeatable Read
Repeatable read é o maior nível de isolamento. Este oferece o maior grau de locking e menor
concorrência. Os locks são mantidos em todas as linhas processadas para construir um result set . Por
exemplo, se uma dada pesquisa para construir um result set for feita sobre 10000 linhas, o lock será feitos
nestas todas, ainda que só 10 façam parte do resultado final. Até a transacção estar completa nenhuma
outra aplicação pode actualizar, eliminar ou inserir uma linha que possa afectar o result set .
O Repeatable read garante que uma consulta efectuada por uma aplicação mais de uma vez numa
transacção dará o mesmo resultado.
DB2 - Conceitos e Análise do Sistema 55
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 56/85
7 – TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA
Problemas evitados com este nível de isolamento:
• Loss of update
• Uncommitted read
• Non-repeatable read
• Phantom read
Comparação de níveis de isolamento
Esquema 17: Comparação de Níveis de Isolamento
A figura acima compara os diferentes níveis de isolamento para a recolha de informação. Na figura,
vemos que o nível de isolamento uncommitted read (UR) não adquire nenhum lock . O nível de isolamento
cursor stability (CS) tem adquirido um lock para a linha 1 quando está a extrair informação desta, mas
liberta-o logo que extrai a linha 2, e assim por diante. Para o isolamento read stability (RS) ou repeatable
read (RR), qualquer linha que for alvo de extracção de informação irá adquirir lock , e o lock só é libertado no
fim da transacção (momento em que faz COMMIT).
Seleccionar nível de isolamento
Dado que o nível de isolamento determina o modo como os dados estão isolados dos outros
processos enquanto estes estão a ser acedidos, deveremos escolher um nível de isolamento que equilibre
os requisitos de concorrência e de integridade dos dados.
DB2 - Conceitos e Análise do Sistema 56
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 57/85
7 – TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA
O nível de isolamento que especificarmos está em efeito durante a unidade de trabalho. As
seguintes heurísticas são usadas para determinar qual o nível de isolamento que será utilizado quando for
compilada uma instrução de SQL ou XQuery:
• Para static SQL:
• Se um nível de isolamento tiver sido especificado na instrução, o valor dessa
proposição é utilizado.
• Se um nível de isolamento não tiver sido especificado na instrução, o nível de
isolamento especificado para o pacote, quando este foi ligado à base de dados, é
usado.
• Para dynamic SQL:
• Se um nível de isolamento tiver sido especificado na instrução, o valor dessa
proposição é utilizado.
• Se um nível de isolamento não tiver sido especificado na instrução, e uma instrução
SET CURRENT ISOLATION tenha sido emitida durante a sessão, o valor do registo
CURRENT ISOLATION é utilizado.
• Se um nível de isolamento não for especificado na instrução e uma instrução do
tipo SET CURRENT ISOLATION não tiver sido emitida durante a sessão, o nível de
isolamento especificado para o pacote, quando este foi ligado à base de dados, é
usado.
• Para instruções de XQuery estáticas ou dinâmicas, o nível de isolamento do ambiente
determina o nível de isolamento que será usado quando a expressão XQuery for avaliada.
O nível de isolamento pode ser especificado de muitas maneiras.
• Ao nível da instrução
Usar a proposição WITH. A proposição WITH não poderá ser usada em subqueries. Com
opção WITH UR aplica-se somente a operações de leitura. Noutros casos, a declaração é
automaticamente alterada de UR para CS.
Este nível de isolamento substitui o nível de isolamento que é especificado no pacote em
que a instrução aparece. Poderemos especificar o nível de isolamento para as seguintes
instruções de SQL: DECLARE CURSOR, Searched DELETE, INSERT, SELECT, SELECT INTO,
Searched UPDATE.
• Para SQL dinâmico dentro da sessão corrente
Usar a instrução SET CURRENT ISOLATION para definir o nível de isolamento para SQL
dinâmico emitido numa sessão. Esta declaração define o registo especial CURRENT
DB2 - Conceitos e Análise do Sistema 57
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 58/85
7 – TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA
ISOLATION para um valor que especifica o nível de isolamento para quaisquer instruções de
SQL dinâmico dentro da sessão actual. Uma vez definido, o CURRENT ISOLATION fornece o
nível de isolamento para qualquer instrução de SQL dinâmico subsequente que é compilado
dentro da sessão, independentemente de qual o pacote em que foi emitida a instrução.Este nível de isolamento está em vigor ate que a sessão termine até que a declaração SET
CURRENT ISOLATION…RESET for emitida.
• Em tempo de pré-compilação ou ligação
Para uma aplicação escrita numa linguagem compilada suportada, usar a opção ISOLATION
dos comandos PREP ou BIND. Pode, também, utilizar-se a API sqlaprep ou sqlabndx.
7.4 GESTÃO DE LOCKS
Lock escalation
Cada lock feito pelo DB2 consome alguma memória. Quando o optimizador pensa que é melhor ter
um lock em toda a tabela, em vez de múltiplos locks de linhas fechaduras, ocorre o um aumento da
abrangência do lock , tal como ilustra a figura abaixo.
Esquema 18: Lock Escalation
DB2 - Conceitos e Análise do Sistema 58
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 59/85
7 – TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA
Existem dois parâmetros principais de configurações de bases de dados relacionados com o
escalonamento de locks:
•
LOCKLIST – A quantidade de memória (em páginas de 4K) que é reservada para gerir lockspara todas as aplicações ligadas. O padrão é de cinquenta páginas (200 K) no Windows.
• MAXLOCKS – Percentagem máxima de toda a lista de locks que uma única aplicação pode
usar. O padrão é de 22%
Portanto, se os valores padrão são utilizados, o escalonamento de locks ocorre quando uma
aplicação necessita mais de 44K de memória para locks (200K * 22% = 44K). Se, com essas configurações, o
escalonamento de locks ocorrer frequentemente, deve-se aumentar o valor de LOCKLIST e MAXLOCKS. O
escalonamento de locks não é bom para o desempenho, uma vez que reduz a concorrência.
O DB2 produz um ficheiro de log acerca dos locks. Através deste, poderemos verificar se o
escalonamento está a ocorrer ou não, e avaliar assim o desempenho, neste campo, da nossa base de dados.
Monotorização de locks
Para além do visionamento um pouco “desconfortável” do ficheiro há outra forma de ter uma visão
abrangente dos locks do sistema. Deste modo, para acompanhar o seu uso poderemos recorrer à aplicação
DB2 Lock Snapshot. Para ligar o Lock Snapshot, executa-se o comando:
UPDATE MONITOR SWITCHES USING LOCK ON
Depois de estar ligado, as informações de monotorização serão colectadas. Para obter um relatório
dos locks num determinado momento, dever-se-á executar o comando:
GET SNAPSHOT FOR LOCKS FOR APLICATION AGENT ID <handle>
Atribuição de Locks
Quando duas ou mais aplicações precisam de efectuar uma operação sobre o mesmo objecto, uma
delas pode ter de esperar para obter o lock necessário. Por omissão, um pedido vai esperar
indefinidamente. O tempo que um pedido aguarda para obter lock é controlado pelo parâmetro de
configuração LOCKTIMEOUT. O valor por omissão deste parâmetro é -1 (espera infinita).
O registo CURRENT LOCK TIMEOUT pode ser usado para definir a espera pelo lock de uma
determinada conexão. Por defeito, este registo é definido com o valor LOCKTIMEOUT. Usa-se a declaração
SET LOCK TIMEOUT para alterar o seu valor. Assim que o valor deste registo é definido, ele irá ser sempre o
mesmo em toda a transacção. Exemplo:
SET LOCK TIMEOUT = WAIT n
DB2 - Conceitos e Análise do Sistema 59
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 60/85
7 – TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA
Causas e detecção de deadlocks
Um deadlock ocorre quando duas ou mais aplicações ligadas à mesma base de dados aguardam
indefinidamente por um recurso. A espera nunca é terminada, porque cada pedido é possuidor de um
recurso que o outro precisa. Os deadlocks são, a maior parte das vezes, um problema de design da
aplicação.
Num cenário de deadlock , o DB2 irá verificar a configuração do parâmetro DLCHKTIME. Este
parâmetro tem definido o intervalo de tempo para verificar se há deadlocks. Por exemplo, se este
parâmetro está configurado para 10 segundos, o DB2 irá verificar a cada 10 segundos se um deadlock
ocorreu. Se, de facto, um deadlock aconteceu, o DB2 irá utilizar um algoritmo interno para determinar qual
das duas operações deve ser desfeita, e qual deve continuar.
Se estivermos a enfrentar vários deadlocks, deveremos reexaminar as nossas transacções e
reestruturá-las se possível. Por exemplo, quando as nossas aplicações lêem dados e pretendemposteriormente actualizá-los, deveremos ter em conta as seguintes abordagens:
• Usar a cláusula FOR UPDATE quando se fizer uma operação de selecção. Esta cláusula
garante que um U lock é definido quando um processo tenta ler dados, e não permite o
bloqueamento de linha.
• Usar as cláusulas WITH RR ou RS e USE AND KEEP UPDATE LOCKS nas perguntas. Estas
cláusulas garantem que um U lock é definido quando um processo tenta ler dados, e
permitem o bloqueio de linhas.
Granularidade de Locks
Se uma aplicação mantém um bloqueio sobre um objecto da base de dados, outra aplicação pode
não ser capaz de aceder a esse objecto. Por esta razão, os bloqueios de linha, que minimizam a quantidade
de dados bloqueados, são melhores para optimizar o nível de concorrência do que os bloqueios de bloco,
de partição de dados ou de tabela.
No entanto, os locks requerem capacidade de armazenamento e tempo de processamento, por isso,
um único lock sobre uma tabela inteira minimiza o lock overhead .
A preposição LOCKSIZE da preposição ALTER TABLE especifica a granularidade dos bloqueios ao nível
da linha, da partição de dados, do bloco ou da tabela. Os locks de linha são os utilizados por defeito. O uso
desta opção na definição da tabela não impede a ocorrência do lock escalation.
A instrução ALTER TABLE especifica os bloqueios globalmente, afectando todas as aplicações e
utilizadores que acedem a essa tabela. Aplicações individuais podem usar a instrução LOCK TABLE para
especificar bloqueios sobre toda a tabela a um nível de aplicação.
DB2 - Conceitos e Análise do Sistema 60
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 61/85
7 – TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA
O bloqueio permanente sobre a tabela definido pela instrução ALTER TABLE pode ser preferível a
uma única transacção usar o table lock através do LOCK TABLE se:
•
Se a tabela for só de leitura e necessitará sempre somente de S locks. Outros utilizadorespodem, também, obter S locks sobre a tabela.
• A tabela é acedida geralmente por aplicações que só fazem leitura sobre os dados, mas, por
vezes, é acedida por um utilizador para uma breve manutenção e esse utilizador requisita
um X lock . Enquanto o programa de manutenção estiver a correr, as aplicações de leitura
estão bloqueadas, mas, noutras circunstâncias, aplicações de leitura podem aceder à tabela
concorrentemente com um overhead de bloqueio mínimo.
Para tabelas com multidimensional clustering (MDC), pode especificar-se BLOCKINSERT com a
preposição LOCKSIZE a fim de utilizar um nível de bloqueio por bloco, mas somente durante as operações
de inserção. O BLOCKINSERT pode ser benéfico quando:
• Existem múltiplas transacções a fazer inserções em massa em células separadas.
• Inserções concorrentes na mesma célula por múltiplas transacções não estão a ocorrer, ou
está a ocorrer com um número de dados tal a serem inseridos por célula por cada uma das
transacções, que o utilizador não se preocupa que cada transacção irá inserir em blocos
diferentes.
Comportamento do locking em tabelas particionadasAlém do bloqueio sobre toda a tabela, há um lock para cada partição de uma tabela particionada.
Isto permite uma excelente granularidade e um aumento da concorrência em comparação com uma
tabela não particionada. O bloqueio por partição é identificado através do resultado do comando db2pd,
monitores de eventos, vistas administrativas e funções da tabela.
Quando uma tabela é acedida, um table lock é obtido em primeiro lugar e só depois os locks sobre
as partições são obtidos, à medida que são requisitados. Métodos de acesso e níveis de isolamento podem
necessitar o bloqueio de partições de dados que não estão representadas no conjunto de resultados.
Depois destes bloqueios sobre as partições terem sido adquiridos, estes podem ser mantidos por tantotempo quanto o bloqueio sobre tabelas. Por exemplo, um scan sobre um índex, com nível de isolamento
cursor stability , pode manter locks sobre todas as partições previamente acedidas de forma a reduzir o
custo de readquirir os locks sobre as partições mais tarde.
Locks sobre as partições carregam, também, o custo de garantir acesso aos table spaces. Para
tabelas não particionadas, o acesso aos table spaces é tratado pelo table lock . O data partition lock ocorre
mesmo quando há um lock exclusivo ou partilhado ao nível da tabela.
Uma boa granularidade permite que uma transacção tenha acesso exclusivo a uma partição de
dados específica, evitando o bloqueio de linhas enquanto outras transacções estão a tentar aceder a outras
DB2 - Conceitos e Análise do Sistema 61
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 62/85
7 – TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA
partições. Este pode ser o resultado do plano que é escolhido tendo em vista uma actualização em massa
ou devido a um lock escalation ao nível da partição. O bloqueio de tabela para muitos métodos de acesso é
normalmente intencional, mesmo que as partições estejam bloqueadas em modo exclusivo ou partilhado.
Isto permite uma maior concorrência. No entanto, se bloqueios não intencionais foram requisitados ao nívelda partição e o plano indicar que todas as partições de dados podem ser acedidas, então um lock não
intencional deverá ser escolhido ao nível da tabela de forma a prevenir deadlocks ao nível das partições
entre transacções concorrentes.
7.4 S AVE P OINTS
Um savepoint é uma forma de implementar sub-transacções num sistema de bases de dados
relacionais, indicando um ponto dentro de uma transacção para o qual esta pode ser revertida, sem afectarqualquer trabalho que tiver sido feito dentro da transacção até esse ponto.Para criar um savepoint em DB2, deverá ser executado o seguinte comando:
SAVEPOINT savepoint_name [UNIQUE] ON ROLLBACK RETAIN CURSORS [ON ROLLBACK RETAIN
LOCKS];
O comando ROLLBACK é utilizado para desistir das alterações que foram feitas na base de dados
dentro de uma unidade de trabalho ou tempo de salvamento.
A preposição TO SAVEPOINT especifica que as alterações na base de dados deverão ser feitas
parcialmente (ROLLBACK TO SAVEPOINT). Após desfeitas as operações o savepoint continuará a existir,porém quaisquer savepoints existentes serão eliminados futuramente pelo sistema. Os saveponts
existentes, se houverem alguns, são considerados como tendo sido revertidos, sendo, então, libertados e
considerados como parte do savepoint actual. Se não for fornecido um nome para o ponto, o rollback
ocorrerá até ao último savepoint definido no nível actual. Tal como foi dito anteriormente, se o ROLLBACK
for feito sem mais nenhuma propriedade, a transacção será desfeita por completo. Para além disso, após
esta operação, os savepoints dentro dessa transacção serão eliminados.
A sintaxe para esta operação é a seguinte:
ROLLBACK TO SAVEPOINT savepoint_name;
DB2 - Conceitos e Análise do Sistema 62
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 63/85
7 – TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA
7.5 P ROCESSAMENTO DE LOGS DE T RANSACÇÕES
Todas as bases de dados mantêm ficheiros de log que mantêm registo das alterações na base de
dados. Há duas estratégias de logs possíveis:
• Circular logging, em que os registos do log preenchem todo o ficheiro de logo e de seguida
voltam ao inicio do ficheiro, escrevendo por cima dos registos iniciais do log. Os registos de
log substituídos não poderão ser recuperados.
• Reter registos de log, em que o ficheiro de log é arquivado quando já não há espaço para
mais registos. Novos ficheiros de log são disponibilizados para guardar os novos registos de
log. Retendo os ficheiros de log permite recuperações do tipo roll-forward . As recuperações
roll-forward reaplicam alterações na base de dados com base em transacções concluídas
que estão registadas no log. Podemos especificar que queremos que a recuperação sejafeita até ao final do ficheiro de log ou então só até determinado ponto.
Independentemente da estratégia de log, todas as alterações de dados regulares e páginas de
índice são escritas no log buffer . Os dados escritos neste serão escritos em disco por um outro processo.
Nas seguintes circunstâncias, o processamento de perguntas deverá aguardar que os logs sejam escritos em
disco:
• Em caso de COMMIT;
• Antes que as páginas de dados correspondentes sejam escritas em disco, dado que o DB2
usa o write-ahead log. O benefício desta estratégia é que quando uma transacção fica
completa, depois de fazer COMMIT, nem todos os dados alterados e as páginas de índice,
necessitam de ser escritas em disco;
• Antes que algumas alterações sejam feitas aos metadados, a maioria das quais resultantes
da execução de instruções DDL;
• No caso de se desejar escrever mais registos no log buffer e este se encontrar cheio.
O DB2 gere a escrita dos logs em disco de maneira a minimizar o delay do processamento. Num
ambiente em que muitas transacções pequenas ocorram, a maioria dos atrasos de processamento sãocausados pelas acções de COMMIT, que têm de esperar que o log seja escrito em disco. Como resultado, o
processo de logger escreve frequentemente pequenas quantidades de registos de log em disco, com o
atraso adicional provocado pelo I/O overhead . Para equilibrar o tempo de resposta da aplicação contra tal
atraso, dever-se-á definir o parâmetro de configuração da base de dados mincommit para um valor superior
a 1. Esta definição poderá causar uma demora maior para fazer COMMIT em algumas aplicações mas será
escrita uma maior quantidade de registos de log em cada operação.
DB2 - Conceitos e Análise do Sistema 63
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 64/85
8 – SUPORTE PARA B ASES DE D ADOS DISTRIBUÍDAS
8 – S8 – SUPORTEUPORTE PARAPARA BBASESASES DEDE DDADOSADOS DDISTRIBUÍDASISTRIBUÍDAS
Uma base de dados distribuída consiste num conjunto de tabelas e de outros objectos, que estão
espalhados por diferentes sistemas de computadores interligados. Cada computador tem o seu gestor de
bases de dados relacionais para gerir as tabelas do seu ambiente. Os gestores de bases de dados
comunicam entre si cooperando de uma forma que permita a um dado gestor executar instruções SQL num
outro computador.
Os sistemas de bases de dados distribuídos são construídos sobre um protocolo formal e funções de
resquester-server . Um aplicativo solicitador suporta a aplicação até ao fim da conexão desta. Este aplicativo
transforma o pedido da aplicação num conjunto de protocolos de comunicação que se ajuste para uma
situação de uma rede de bases de dados distribuídas. Estes pedidos são recebidos e processados por um
servidor de bases de dados no outro extremo da conexão. Trabalhando em conjunto, o aplicativo solicitador
e o servidor de bases de dados têm em consideração o tipo de comunicação e a localização, de forma a que
a aplicação possa operar como se estivesse a aceder a uma base de dados local.
Um processo de uma aplicação deve conectar-se à aplicação gestor da base de dados antes de
executar as instruções de SQL que referenciam tabelas ou vistas. A instrução CONNECT estabelece uma
comunicação entre uma aplicação e o seu servidor.
Há 2 tipos de tipos de instruções de CONNECT:
• CONNECT (Type 1) suporta uma única base de dados por cada unidade de trabalho (Remote
Unit Of Work).
DB2 - Conceitos e Análise do Sistema 64
Esquema 19: Comunicação entre Clientes e Servidores DB2
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 65/85
8 – SUPORTE PARA B ASES DE D ADOS DISTRIBUÍDAS
• CONNECT (Type 2) suporta múltiplas bases de dados por cada unidade de trabalho
(Application-Directed Distributed Unit Of Work).
8.1 SUPORTE À F RAGMENTAÇÃO
O DB2 da IBM suporta a fragmentação horizontal limitada. A fim de determinar onde uma relação
deverá ser fragmentada, o administrador selecciona uma chave de distribuição, que será incorporada numa
função de hash. Para a criação de uma tabela nestas condições deveremos ter em conta a seguinte sintaxe:
CREATE TABLE name
(column_name data_type null_attribute)IN table_space_name
INDEX IN index_space_name
LONG IN long_space_name
DISTRIBUTE BY HASH (column_name)Se não for especificada explicitamente a chave de distribuição, uma das seguintes são utilizadas, por
defeito. Deveremos assegurar-nos que a utilizada será apropriada.
• Se uma chave primária for especificada na instrução de CREATE TABLE, a primeira coluna da
chave primária é usada como chave de distribuição.
• Para um grupo de múltiplas partições, se não houver nenhuma chave primária, a primeira
coluna que não tiver um campo longo, será utilizava.
• Se não houver colunas que satisfaçam os requisitos para a chave de distribuição por defeito,
a tabela é criada sem uma (isto só é permitido em grupos de uma única partição).
Deveremos ter um grande cuidado na criação da chave de distribuição. Se esta não for apropriada,
este, será um problema irremediável. A chave de distribuição só poderá ser definida aquando da criação da
tabela, não havendo hipótese e alteração futura.
A função de hash divide os dados em parcelas aproximadamente iguais, que são distribuídas. Desde
que cada linha de uma tabela tenha somente um valor de hash, as várias partições contêm conjuntos de
tuplos disjuntos, ou seja, quando quisermos localizar um tuplo, o sistema saberá univocamente onde ele se
encontra.
A abordagem do DB2 a esta questão permite que os dados sejam distribuídos pelos múltiplos nós e
permitirá, também, que uma distribuição não-uniforme dos dados esclareça diferenças no poder de
processamento dos vários nós. As partições poderão ser facilmente adicionadas ao sistema, podendo este,
desde logo, começar a fazer uso das capacidades dos novos nós. Esta arquitectura permite que DB2 consiga
a execução da query e escalabilidade paralelas da base de dados. No entanto o esquema não suporta a
fragmentação geral dos dados horizontais baseados em transacções. Estas serão tratadas, como veremos a
seguir, através de um esquema de two-phase commit . O efeito do mecanismo da fragmentação do DB2
deverá ser transparente para as aplicações com recurso a este.
DB2 - Conceitos e Análise do Sistema 65
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 66/85
8 – SUPORTE PARA B ASES DE D ADOS DISTRIBUÍDAS
8.2 T WO-P HASE C OMMIT
A figura abaixo ilustra os passos envolvidos num multisite update.
• A aplicação está preparada para o two-phase commit . Isto pode ser feito através das opções
de pré-compilação ou através da CLI (Call Level Interface).
• Quando o cliente pretende conectar-se à base de dados, primeiro conecta-se internamente
ao transaction manager (TM). O TM retorna um acknowledgement ao cliente. Se o
parâmetro de configuração do gestor de bases de dados tm_database estiver definido
como 1ST_CONN, a base de dados fica ligada ao TM enquanto a instância da aplicação
estiver activa.
DB2 - Conceitos e Análise do Sistema 66
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 67/85
8 – SUPORTE PARA B ASES DE D ADOS DISTRIBUÍDAS
• Ocorre a conexão à base de dados, sendo acknowledged logo de seguida.
• O cliente da base de dados começa a actualização da tabela SAVINGS_ACCOUNT. Isto dá
início uma unidade de trabalho. O TM responde ao cliente, providenciando um transactionID para aquela unidade de trabalho. De notar que o registo da unidade de trabalho
acontece quando ocorre a primeira instrução de SQL e não durante o estabelecimento da
transacção.
• Depois de receber o ID da transacção, o cliente regista a unidade de trabalho na base de
dados contendo a tabela SAVINGS_ACCOUNT. A resposta é enviada de volta para o cliente
para indicar se esta foi registada com êxito.
• As instruções de SQL emitidas para a base de dados SAVINGS_DB são tratadas da maneira
convencional. A resposta a cada declaração é devolvida na SQLCA quando se trabalha com
instruções SQL embebidas num programa.
• O ID da transacção é registado na FEE_DB, que contém a tabela TRANSACTION_FEE,
durante o primeiro acesso à base de dados naquela unidade de trabalho.
• Quaisquer instruções na FEE_DB são tratadas de forma normal.
• Instruções SQL adicionais podem ser corridas na SAVINGS_DB, configurando a conexão
adequadamente. Como a unidade de trabalho já está registada, o cliente não necessitará de
realizar este processo de novo.
• Conectando à base de dados CHECKING_DB. São seguidas as regras descritas em 6) e 7)
• Quando o cliente da base de dados requer que a unidade de trabalho seja commited , uma
mensagem prepare é enviada para todas as bases de dados envolvidas na unidade de
trabalho. Cada base de dados escreve um registo “PREPARED” nos ficheiros de log e
responde ao cliente.
• Depois de o cliente da base de dados receber uma resposta positiva de todas as bases de
dados, este envia uma mensagem para o TM, informando que a unidade de trabalho está
pronta a ser concluída. O TM escreve “PREPARED” no seu ficheiro de log, enviando um
reply a informar o cliente que a segunda fase do processo de commit pode, então, ter início.
• Durante a segunda fase do processo de confirmação, o cliente da base de dados envia uma
mensagem para todas as bases de dados participantes a pedir-lhes que façam commit . Cada
base de dados escreve um registo “COMMITED” no seu ficheiro de log, libertando os locks
que haviam sido adquiridos para esta unidade de trabalho. Quando a base de dados
completar o commit das alterações é enviada uma mensagem de resposta ao cliente.
• Depois do cliente da base de dados receber uma resposta positiva de todas as bases de
dados envolvidas, este envia uma mensagem ao TM a informar que a unidade de trabalho
DB2 - Conceitos e Análise do Sistema 67
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 68/85
8 – SUPORTE PARA B ASES DE D ADOS DISTRIBUÍDAS
foi concluída. O TM escreve “COMMITED” no seu ficheiro de log, indicando que a
transacção está completa, e envia uma resposta ao cliente.
Recuperação de erros durante o two-phase commit
Recuperar de condições de erro é uma tarefa normal associada à programação de aplicações,
administração de sistemas, administração de bases de dados e sistemas de operação. A distribuição das
bases de dados por vários servidores remotos aumenta o potencial da ocorrência de erros, resultado de
falhas da rede ou das comunicações. De forma a assegurar a integridade dos dados, o gestor da base de
dados fornece um processo de two-phase commit . O gestor lida com os erros da seguinte maneira:
• Erro na primeira fase
Se a base de dados comunica que falhou a preparação do commit da transacção, o cliente
da base de dados irá reverter esta durante a segunda fase do processo de commit . A
mensagem de prepare não será enviada ao TM.
Durante a segunda fase, o cliente envia uma mensagem de rollback a todas as bases de
dados participantes que prepararam o commit correctamente, durante a primeira fase.
Cada base de dados escreve “ABORT” no seu arquivo de log, libertando todos os locks
adquiridos para aquela transacção.
• Erro na segunda fase
O tratamento dos erros nesta fase depende se a segunda fase irá fazer commit ou roll back da transacção, sendo o segundo caso proveniente de um erro na primeira fase.
Se uma das bases de dados participantes falhar o commit da transacção (provavelmente
através de uma falha nas comunicações), o TM tentará repetir a acção nesta. A aplicação, no
entanto, será informada se o commit foi bem sucedido ou não. O DB2 assegura que a
transacção será confirmada, mais tarde ou mais cedo. Assim sendo, faz várias tentativas,
com o intervalo de tempo definido no parâmetro de configuração resync_interval . Todos os
locks adquiridos na base de dados serão mantidos enquanto a operação não for concluída
com sucesso.
Se o transaction manager database falhar, ele tentará re-sincronizar a transacção quando
for reiniciado. A re-sincronização tentará completar todas as transacções dúbias, ou seja,
todas aquelas que tiverem concluído a primeira fase mas que ainda não tenham concluído a
segunda fase do processo de commit .
Se uma das bases de dados participantes falhar e for reiniciada, o gestor desta consultará o
TM para saber o status da transacção, de forma a perceber se esta deverá ser revertida. Se
a transacção não estiver no log, o gestor assume que a foi revertida e irá reverter as outras
transacções dúbias desta base de dados. Caso contrário, a base de dados irá esperar por
pedidos de commit do TM.
DB2 - Conceitos e Análise do Sistema 68
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 69/85
8 – SUPORTE PARA B ASES DE D ADOS DISTRIBUÍDAS
8.3 MECANISMOS DE REPLICAÇÃO
Em conjunto com o DataJoiner, o DataPropagator forma uma solução flexível para replicações
heterogéneas.
O DataPropagator é o produto central da solução de replicação da IBM. A arquitectura
DataPropagator é baseada em três componentes principais: Administration Interface, Change Capture
Mechanisms e Apply Program.
• Administration Interface
O DataJoiner Replication Administration Tool (DRJA) é usado para definir a configuração da
replicação: Habilitar a base de dados para replicação, tabelas do registo como fonte, etc.
• Change Capture Mechanisms
No caso da replicação da fonte no DB2, o programa de captura captura as mudanças dos
dados lendo o ficheiro de log das transacções.
• Apply Program
O programa lê os dados que foram alterados da tabela e aplica-os na replicação. Apresenta
modos de replicação síncronos e assíncronos.
Detecção avançada de conflito: Detecção de conflito que garante a integridade dos dados entre
todas as réplicas e a fonte. O programa Apply bloqueia todas as réplicas ou tabelas do utilizador no
conjunto de subscrição contra transacções adicionais. Ele inicia a detecção após a captura de todas as
alterações efectuadas antes do bloqueio.
É aconselhada a modelação da aplicação para que não ocorram conflitos. Para isso é sugerido que
se utilize o update anywhere nas seguintes condições:
• Fragmentação por chave
• Fragmentação por tempo
Há a opção de ignorar os conflitos e rejeitar qualquer alteração conflituosa.
O DataPropagator permite a replicação bidireccional (“Update Anywhere”), mas somente de DB2
para DB2, para além de necessitar da noção de fonte/réplica de distribuição e consolidação.
O DataJoiner é uma ferramenta da IBM para deixar o acesso transparente para bases de dados IBM
ou não, relacionais ou não relacionais. Este não é usado apenas na replicação de dados heterogéneos, mas
também para aceder aos mesmos. Permite, também, a execução e consultas distribuídas, localizadas em
tabelas e servidores separados fisicamente.
DB2 - Conceitos e Análise do Sistema 69
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 70/85
8 – SUPORTE PARA B ASES DE D ADOS DISTRIBUÍDAS
Mecanismos de replicação suportados:
• Actualização assíncrona contínua (por evento)
Processo no qual todas as alterações feitas na origem são registadas e aplicadas em dados
de destino existentes, após a realização do commit na tabela de base.
• Actualização assíncrona em lote (por intervalo de tempo)
Processo no qual todas as alterações feitas na origem são registadas e aplicadas em dados
de destino existente em intervalos especificados.
• Actualização completa (snapshot )
Na replicação do DB2, o processo no qual todos os dados de interesse numa tabela do
utilizador são copiados para a tabela de destino, substituindo os dados existentes.
• Actualização diferencial (merge)
Na replicação do DB2, o processo no qual apenas os dados alterados são copiados para a
tabela de destino.
• Actualização em múltiplos sítios
No DB2 UDB para OS/390, o processo no qual os dados são actualizados em mais do que
uma localização, numa única unidade de trabalho.
8.4 P ROCESSAMENTO DISTRIBUÍDO DE P ERGUNTAS
O DB2 Query Patroller, incorporado no DB2 Warehouse Manager, pode interromper o comando SQL
que estiver a ser enviado para um servidor de DB2 por meio da integração do trap no código do cliente. Isto
permite que todas as instruções SQL dinâmicas, independentemente do sistema operacional, seja geridas,
programadas e controladas pelo Query Patroller. O mecanismo de repetição da consulta permite que
trabalhos que foram interrompidos, por diversas razões, sejam submetidos e executados novamente, até à
sua conclusão. É possível emitir um comando especial de iniciação global que inicie o Query Patroller em
todos os nós. Esta acção oferece um ponto único de controlo para a iniciação e interrupção do Query
Patroller.
DB2 - Conceitos e Análise do Sistema 70
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 71/85
8 – SUPORTE PARA B ASES DE D ADOS DISTRIBUÍDAS
8.5 C ONTROLO DE C ONCORRÊNCIA
Lock Time Out Avoidance: É utilizado em situações anormais em que uma certa aplicação espera
para efectuar um lock que nunca irá terminar. Evita deadlocks.
Especifica quantos segundos uma aplicação poderá esperar para obter um lock . Quando esse valor
é atingido, a aplicação aborta. Se o valor especificado for -1, então a aplicação irá esperar eternamente para
efectuar um lock .
O monitor do sistema de base de dados permite que o DB2 seja monitorizado a partir de uma única
partição. Este colecta os dados e agrega os valores presentes em todas as partições e retorna um único
resultado. Isto fornece aos administradores da base de dados um ponto único de controlo para a
monitorização de todo o data warehouse. O monitor do sistema de bases de dados reúne as informações
sobre a operação e os desempenhos das actividades da base de dados, que vão das leituras e gravações atéaos bloqueios e deadlocks.
8.6 D ATA SUPORTE DE ACESSO A D ADOS EM SGBD' S H ETEROGÉNEOS
Os utilizadores do DB2 têm a possibilidade de fazer consultas distribuídas a qualquer servidor de
bases de dados, sejam da família do DB2, ou não, desde que tenham uma API OLEDB. Isto significa que o
utilizador e as aplicações, claro está, podem usar a sintaxe do DB2 e as suas APIs para aceder aos dados que
residem em fontes heterogéneas. Com esta funcionalidade, ganha-se a capacidade de fazer referência a
várias fontes de dados numa única instrução SQL. Com o Relational Connect, as consultas distribuídas
também podem incluir bases de dados Oracle, por exemplo. Esta é a primeira fase da integração do DB2
DataJoiner no DB2 Universal Database. O DataJoiner é um produto de middleware da IBM para a integração
de fontes de dados heterogéneas.
DB2 - Conceitos e Análise do Sistema 71
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 72/85
9 – OUTRAS C ARACTERÍSTICAS DO SISTEMA
9 – O9 – OUTRASUTRAS CCARACTERÍSTICASARACTERÍSTICAS DODO SSISTEMAISTEMA
9.1 DB2 PURE XMLO pureXML é a nova tecnologia fornecida pelo DB9 para suportar o armazenamento de XML nativo.
Utilizar XML com base de dados
Os documentos XML podem ser guardados em ficheiros de texto, repositórios XML, ou base de
dados. Existem duas razões principais para muitas empresas guardarem XML em base de dados:
• Gerir grandes quantidades de dados XML é uma tarefa para as bases de dados. XML são
dados como qualquer outro tipo de dados, apenas estão num formato diferente. As
mesmas razões para guardar dados relacionais em bases de dados aplicam-se a dados XML:
as bases de dados fornecem procuras eficientes, suporte robusto para dados permanentes,
backup e restauro, suporte de transacção, performance e escalabilidade.
• Integração: através do armazenamento de documentos XML e relacionais, podem integrar-
se os novos dados XML com dados relacionais existentes e combinar SQL com XPath ou
XQuery, numa só consulta. Além disso, dados relacionais podem ser publicados como XML,
e vice-versa. Através da integração, as bases de dados podem melhorar o suporte a
aplicações WEB, SOA e Web Services.
Base de dados XML
Existem dois tipos de bases de dados para guardar dados XML:
• Base de dados XML-enabled
• Base de dados de XML nativo
Base de dados XML-enabled
Uma base de dados XML-enabled utiliza um modelo relacional para o núcleo do modelo de
armazenamento de dados. Isto requer uma organização entre o modelo de dados XML (hierárquico) e o
modelo relacional de dados, ou então guardar dados XML como character large object . Este procedimento
pode ser considerado uma tecnologia “antiga”, mas ainda é utilizada por muitos vendedores de bases de
dados. A figura abaixo mostra as duas opções para a criação de bases de dados XML-enabled.
DB2 - Conceitos e Análise do Sistema 72
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 73/85
9 – OUTRAS C ARACTERÍSTICAS DO SISTEMA
Esquema 20: XML-Enabled
Do lado esquerdo temos o método “CLOB e Varchar” para guardar documentos XML na base de
dados. Neste método, um documento XML é guardado como uma unparsed string numa coluna CLOB ou
Varchar na base de dados. Se o documento XML é guardado como uma string, quando quisermos aceder a
uma parte do documento XML, o programa irá procurar a string e realizar o seu parsing, para encontrar o
que queremos. Como poderemos perceber facilmente, este método não é muito flexível.
A outra opção, que se encontra no lado direito, para uma base de dados XML-enabled tem o nomede shredding ou decomposição. Através deste método, um documento XML inteiro é decomposto em
partes mais pequenas que são guardadas em tabelas. Este método não é bom para a flexibilidade: uma
alteração no documento XML não é facilmente propagada nas tabelas correspondentes, e mais tabelas
poderão ser criadas. Para além disto, também não é eficiente. Se precisarmos de construir o documento de
XML original, precisaremos de executar uma operação SQL dispendiosa, que ficará tanto mais cara quantas
mais tabelas estiverem interligadas.
Bases de dados em XML nativo
As bases de dados em XML nativo utilizam um modelo de dados XML hierárquico para guardar e
processar XML internamente. O formato de armazenamento é o mesmo formato de processamento: não há
mapeamento para o modelo relacional e os documentos XML. Quando comandos XPath ou XQuery são
utilizados, eles são processados nativamente pelo motor, não sendo convertidos para SQL. Esta é a razão
pela qual estas bases de dados são conhecidas como bases de dados XML nativo.
DB2 - Conceitos e Análise do Sistema 73
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 74/85
9 – OUTRAS C ARACTERÍSTICAS DO SISTEMA
XPath
O XPath é uma linguagem que pode ser usada para fazer consultas a documentos XML. As suas
expressões são caminhos completos para especificar elementos e/ou atributos. Assim, similarmente às
directorias num sistema operativo, dever-se-á indicar o caminho completo passando pelos vários nós,
separados por barras. O sinal “@” é usado para especificar um atributo. Para obter apenas o valor (o texto
do nó) de um elemento, deverá ser utilizada a função text().
XQuery
A XQuery é uma linguagem de consulta criada para XML. A XQuery suporta expressões de caminhos
para navegar na estrutura hierárquica do XML. De facto, a XPath é um subconjunto da XQuery; portanto,
tudo o que se aplica a XPath, também se aplica a XQuery. Esta linguagem suporta dados com tipo ou sem
tipo. Em XQuery não existem valores nulos porque os documentos XML omitem dados desconhecidos ouem falta. Esta, retorna sequências de dados XML. De notar, que tanto em XQuery como em XPath, as
expressões são case sensitive.
Inserir documentos XML
A inserção documentos XML numa base de dados DB2 pode ser realizada com o comando INSERT
SQL, ou a utilidade IMPORT. XQuery não pode ser utilizado com essa finalidade pois o processo de inserção
ainda não está definido no standard.
9.2 SQL PL STORED P ROCEDURES
Um stored procedure é um objecto de aplicação de bases de dados que pode encapsular comandos
SQL. Manter parte da lógica da aplicação na base de dados permite um melhor desempenho e, assim, a
quantidade de tráfego da rede entre a aplicação e a base de dados é reduzida consideravelmente. Os stored
procedures também fornecem uma posição central para armazenar o código, para que outras aplicações
possam reutilizar os mesmos procedimentos.
Os stored procedures do DB2 podem ser desenvolvidos com SQL PL, C/C++, Java, Cobol, linguagenssuportadas pela CRL (Common Language Runtime), e OLE. Estes podem ser criados com o auxílio do Data
Studio, mas apenas no caso de estarem em SQL PL ou Java.
DB2 - Conceitos e Análise do Sistema 74
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 75/85
9 – OUTRAS C ARACTERÍSTICAS DO SISTEMA
9.3 SEGURANÇA
Como poderemos ver na figura abaixo, a segurança no DB2 consiste em duas partes:
Esquema 21: Componentes de Segurança no DB2
• Autenticação
É o processo em que a identidade do utilizador é validada. A autenticação é efectuada
através de um sistema de segurança exterior ao DB2 (tipicamente pelo sistema operativo,
algum método de autenticação pela rede, ou um plugin desenvolvido especificamente para
autenticação). A autenticação baseada no SO é a opção escolhida, por omissão. Quando se
usa a autenticação baseada no SO, o nome do utilizador e a password são transferidos para
o servidor da base de dados. O servidor da base de dados invoca então a autenticação pelo
SO para validar a identidade do utilizador e a password .
• Autorização
Neste ponto, o DB2 verifica se o utilizador autenticado pode executar a operação requerida.
A informação sobre a autorização é guardada num catálogo do DB2 e num ficheiro de
configuração DBM.
Autoridade DBADM
A autoridade DBADM (DataBase ADMinistrator) é a de super utilizador para a base de dados. Para
garantir a autoridade DBADM, utiliza-se o comando GRANT como mostra o exemplo abaixo
connect to database
grant DBADM on database to user userid
De notar que apenas um utilizador com privilégios SYSADM pode garantir autoridade DBADM.
DB2 - Conceitos e Análise do Sistema 75
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 76/85
9 – OUTRAS C ARACTERÍSTICAS DO SISTEMA
Grupo PUBLIC
O DB2 define um grupo interno chamado PUBLIC. Qualquer utilizador identificado pela
autenticação do sistema operativo ou da rede é implicitamente um membro do grupo PUBLIC. Quando uma
base de dados é criada, certos privilégios estão garantidos automaticamente ao grupo PUBLIC:
• CONNECT,
• CREATETAB,
• IMPLICIT SCHEMA,
• BINDADD
No entanto, para segurança adicional, estes deverão ser revogados.
Os comandos GRANT e REVOKE
Os comandos GRANT e REVOKE são parte do standard SQL e são usados para dar ou remover
privilégios a um utilizador ou grupo de utilizadores.
Vejamos alguns exemplos:
Para garantir o privilégio SELECT na tabela T1 ao utilizador USER1:
GRANT SELECT ON TABLE T1 TO USER user1
Para garantir todos os privilégios na tabela T1 ao grupo GROUP1:
GRANT ALL ON TABLE T1 TO GROUP group1
Para revogar todos os privilégios na tabela T1 ao grupo GROUP1:
REVOKE ALL ON TABLE T1 TO GROUP group1
Para garantir o privilégio EXECUTE no procedimento p1 ao utilizador USER1:
GRANT EXECUTE ON PROCEDURE p1 TO USER user1
9.4 I NFOSPHERE W AREHOUSE
De forma a lidar com o crescente número de volumes de dados, a IBM criou o InfoSphere
Warehouse. Este fornece tudo o que é necessário para implementar uma solução flexível e com data
warehouse escalável para um warehousing dinâmico. É, provavelmente, a solução ideal para empresas que
precisam de consolidar os seus dados de mercado, silos de informação e análises de negócio, oferecendo
uma versão única destes para todos os seus operadores, no contexto e em tempo real.
DB2 - Conceitos e Análise do Sistema 76
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 77/85
9 – OUTRAS C ARACTERÍSTICAS DO SISTEMA
O InfoSphere Warehouse:
• Simplifica o desenvolvimento de data warehouse da empresa, sendo possível fazer a
implementação e a manutenção com uma solução abrangente e integrada
• Agilliza os processos de inteligência comercial, aumentando a flexibilidade, e aliando
poderosos componentes de soluções analíticas.
• Aumenta o desempenho do armazém de dados e melhora a produtividade através de novos
recursos de optimização.
Fornecendo um conjunto poderoso de capacidades, que vão para além das tradicionais warehouses,
o InfoSphere Warehouse é uma plataforma abrangente que inclui ferramentas e infra-estruturas queajudam os arquitectos e administradores deste tipo de sistemas e conseguir projectar, desenvolver e manter
um armazém de dados de uma empresa.
Servidor de dados
O DB2 9.5 é a base de funcionamento do InfoSphere Warehouse. Este fornece recursos que
permitem reduzir o custo de propriedade do ambiente e melhores capacidades de desempenho,
disponibilidade e conformidade. Através de uma arquitectura altamente escalável, distribuída e baseada em
shared-nothing, fornece um elevado desempenho para consultas de carga de trabalho elevada, tanto a
dados relacionais como a dados em XML nativo. Funcionalidade mais avançadas como o caso doparticionamento de dados, compressão de linhas, clustering multidimensional e MQTs, possibilitam que
esta seja uma solução eficaz para um ambiente dinâmico de warehousing.
Este possui melhoramentos ao nível das ferramentas de backup; das capacidades de tuning, que são
mais automáticas e simples; de uma mais rápida distribuição de dados; e de actualizações automáticas que
poderão reduzir o custo da administração da base de dados. Também a compressão de linhas trás muitas
vantagens, sobretudo à quantidade de espaço poupado no armazenamento dos dados.
Melhoramentos da concepção e optimização do processamento OLAPDe forma a suportar a análise de dados, o InfoSphere Warehouse fornece apoio directo para
analíticas optimizadas do OLAP. Este software apresenta ferramentas que permitem criar, editar, importar,
exportar e desenvolver modelos de OLAP a partir do esquema relacional de armazenamento criado.
Fornece um ambiente de fácil utilização, através de wizards, que nos dá recomendações de optimização, de
forma a melhorar o desempenho das ferramentas e aplicações que utilizam este tipo de processamento.
DB2 - Conceitos e Análise do Sistema 77
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 78/85
9 – OUTRAS C ARACTERÍSTICAS DO SISTEMA
9.5 SUPORTE AO DESENVOLVIMENTO
O DB2 possui inúmeras facilidades de integração em várias aplicações. Desta forma, disponibiliza
APIs para várias linguagens, tais como, .NET, CLI, Java, Python, Perl, PHP, Ruby, C++, C, REXX, PL/I, COBOL,RPG e FORTRAN. Para além disto, para simplificar a programação de aplicações, tem ainda integração com
vários ambientes gráficos, dos quais são exemplo o Eclipse e o Visual Studio.
Nesta secção, abordaremos os conceitos básicos de desenvolvimento de aplicações em Java e PHP,
utilizando um servidor DB2.
Desenvolvimento de aplicações em Java
O driver do IBM DB2 para JDBC foi optimizado nas últimas versões. Este inclui drivers do tipo 2 e 4.
Driver JDBC Tipo 2
O driver JDBC do tipo 2 necessita de um cliente DB2 para ser instalado onde a aplicação JDBC está a
funcionar. A figura abaixo ilustra o funcionamento de uma aplicação que utiliza este driver.
Esquema 22: Driver JDBC tipo 2
DB2 - Conceitos e Análise do Sistema 78
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 79/85
9 – OUTRAS C ARACTERÍSTICAS DO SISTEMA
Driver JDBC Tipo 4
O driver JDBC do tipo 4 não requer um cliente de DB2 para se ligar a um servidor de base de dados.
Esquema 23: Driver JDBC Tipo 4
Desenvolvimento de aplicações em PHP
O PHP é uma plataforma independente de linguagem, open source, que é adequada para o
desenvolvimento de aplicações Web. É uma das linguagens mais utilizadas na Web, actualmente. A
popularidade é baseada nas seguintes características:
• Rápida, ciclos iterativos de desenvolvimento, com uma baixa curva de aprendizagem;
• Robusta, alta performance e escalável;
• Estável e segura;
• Uma alternativa ao J2EE e ao .NET na Web;
• Fácil de integrar com sistemas/ambientes heterogéneos;
•
Comprovada mediante a implantação generalizada;
A IBM suporta o acesso a uma base de dados DB2 a partir de uma aplicação PHP através de duas
extensões:
ibm_db2:
A extensão ibm_db2 oferece uma interface de programação de aplicações procedimental para criar,
ler, escrever e actualizar operações de bases de dados para além do acesso extensivo a esta. Pode se
compilado para trabalhar com PHP 4 ou 5. Esta extensão foi desenvolvida e é mantida pela IBM, tendo todo
o suporte para procedimentos armazenados e LOBs.
DB2 - Conceitos e Análise do Sistema 79
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 80/85
9 – OUTRAS C ARACTERÍSTICAS DO SISTEMA
PDO_ODBC:
O PDO_ODBC é um driver para Objectos de Dados do PHP (PHP Data Objects – PDO), e oferece
acesso a bases de dados DB2 através de uma interface de base de dados standard orientada aos objectos
introduzidos no PHP 5.1. Pode ser compilado directamente com bibliotecas DB2 e oferece uma interface
padrão de acesso aos dados em PHP. É rápido, leve, e orientado aos objectos. A extensão PDO_ODBC utiliza
bibliotecas DB2 para acesso nativo e foi construído em PHP 5.1.
DB2 - Conceitos e Análise do Sistema 80
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 81/85
10 – ANEXO
10 – A10 – ANEXONEXO
10.1 E XPERIÊNCIAS ADICIONAIS COM O SISTEMA
Definir uma PRIMARY KEY
Para definir uma primary key a partir dum campo já existente usamos um comando da seguinteforma:
(1) ALTER TABLE tabela ADD PRIMARY KEY (coluna) ;
No entanto é necessário que algumas condições se encontrem reunidas para o sucesso destecomando. O campo que escolhemos deve ter associada uma restrição do tipo NOT NULL e não pode havervalores duplicados.
Para adicionar uma restrição do tipo NOT NULL a um campo usamos o seguinte comando:
(2) ALTER TABLE tabela ALTER COLUMN coluna SET NOT NULL ;
Podemos agora fazer o comando (1) ? Na verdade não teremos sucesso pois o comando (2) coloca a
tabela num estado denominado “Pending Reorganization” resultante do facto de o comando (2) implicaruma verificação de que realmente não existem já valores NULL e depois dessa verificação recomenda-se areorganização da tabela. Podemos forçar esta reorganização recorrendo ao comando:
(3) REORG TABLE tabela;
Agora sim obteríamos sucesso com o comando (1).Conclusão seria necessária a sequência de comandos (2) (3) (1) para adicionar uma PRIMARY KEY a
partir dum campo existente com a propriadade Nullable.
De notar que no caso da existência de um indíce que garante a propriade UNIQUE já existir no
campo este é aproveitado.
DB2 - Conceitos e Análise do Sistema 81
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 82/85
10 – ANEXO
Explain Plans
Como sabemos aquando do processamento duma query um sistema de gestão de base de dadoselabora planos de execução acabando por executar aquele que julga ser o melhor. É nos útil poder saberque plano foi esse e o Control Center do DB2 possibilita-nos isso mesmo duma forma fácil através do Access
Plan.
Por exemplo para uma query do género da seguinte:
SELECT country.name , city.name , country.populationFROM country JOIN city ON (country.code = city.country)WHERE country.population > 40000000
Temos o correspondente plano:
Podemos verificar que o sistema começa começa por aceder as tabelas que serão alvo da junção.Não é vísivel no esquema mas clicando num nó temos uma visão mais detalhada do que aí se passa epodemos concluir que nestes acessos iniciais são excluídas as colunas que não vão ser usadas pela querybem como no acesso á tabela country são logo filtrados os tuplos que não verificam a condição do where. Oque nos leva a concluir que o sistema adoptou uma estratégia que passa por fazer as selecções e as
projecções o mais cedo possível.
DB2 - Conceitos e Análise do Sistema 82
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 83/85
10 – ANEXO
Melhorando a Performance com o Design Advisor
Uma funcionalidade interessante no Control Center do DB2 é o Design Advisor. Um assistente que
nos diz o que podemos fazer para melhor a performance da nossa base de dados em função dumaworkload esperada. Workload é um conjunto de comandos SQL que prevemos que pretendemos ouprevemos que venham a ser executados na nossa base de dados. É possível definir para cada um umafrequência relativa por exemplo se um comando 'a' é em média executado o dobro das vezes que ocomando 'b' então podemos atribuir 1 para a frequência de 'b' e 2 para 'a'.
Definida a workload o assistente irá calcular que objectos seriam úteis para optimizar aperformance, entre índices , materialized query tables ou multidimensional clustering tables.
Exemplo:
Suponhamos que esperamos que na nossa base de dados virão a ser executados os comandos:
SELECT * FROM uscensus WHERE education = 'HS-grad';(frequência 3)SELECT COUNT(*) FROM uscensus a , uscensus bWHERE (a.educationnum > b.educationnum AND a.age < b.age);(frequência 5)
Em resultado o assistente diria o seguinte:
Isto significa que é possível aumentar a performance em 61.33 % se procedermos à criação dosíndices com chaves (education) e (educationnum,age).
DB2 - Conceitos e Análise do Sistema 83
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 84/85
10 – ANEXO
Níveis de Isolamento e Locks
Imaginemos que temos uma tabela t com um atributo INTEGER e outro VARCHAR:
t (a INTEGER ,b VARCHAR)
O que acontece na existência de dois processos a acederem a nossa base de dados com as seguintessequências de comandos.
<T2>: INSERT INTO t values(3,'tres');
<T1>: SELECT * FROM t;
<T2>: COMMIT;
<T1>: SELECT * FROM t;
Como já foi referido o nível de isolamento por defeito no DB2 é o cursor stability de comportamentosemelhante ao ,denominado pela ANSI , READ COMMITED que tal como o nome indica outros processos sópodem ver os efeitos que tiveram transacções já concluídas.
Portanto por defeito o primeiro SELECT não verá o tuplo inserido. Contudo é possível forçar ovisionamento desse tuplo através da cláusula WITH que especifica o modo de isolamento. Se forçamos omodo UNCOMMITED READ fazendo ' SELECT * FROM t WITH UR;' já obteríamos no resultado o tuploinserido por T1.
Vejamos agora outra situação.
<T2>: INSERT INTO t values(10,'10 do T2');
<T1>: INSERT INTO t values(10,'10 do T1');
O que acontece com nesta situação é que o T1 vai se bloquear pois T2 tem uma transacção emcurso com inserção dum tuplo com o mesmo atributo chave.
Se fizermos COMMIT no T2 ficaramos com o primeiro tuplo e T1 vai obter um erro, se fizermosROLLBACK ficaremos com o segundo tuplo.
DB2 - Conceitos e Análise do Sistema 84
7/15/2019 G16_relatoriosbd-DB2 - Conceitos e An�lise do Sistema.pdf
http://slidepdf.com/reader/full/g16relatoriosbd-db2-conceitos-e-anlise-do-sistemapdf 85/85
11 – BIBLIOGRAFIA
11 – B11 – BIBLIOGRAFIAIBLIOGRAFIA
Books / E-Books
• IBM DB2 Universal Database – SQL Reference
• IBM DB2 Universal Database – Administration Guide: Performance
• Allen, Grant, Beginning DB2: From Novice to Professional, Berkeley, Apress, 2008
Web
• http://www.ibm.com;
• http://www.globalguideline.com/articles/analysis.php?k=Optimization_of_DB2;
• http://en.wikipedia.org/wiki/IBM_DB2;
• http://pt.wikipedia.org/wiki/DB2;
• http://www.informit.com/articles/article.aspx?p=375536;
• http://it.toolbox.com/blogs/db2zos/db2-history-101-version-12-22083;
• http://webdocs.caspur.it/ibm/udb-6.1/index.htm;
• http://imasters.uol.com.br/artigo/3713/db2/armazenamento_de_dados/;
• http://www.databasejournal.com/features/article.php/3593431/DB2-Series.htm;
• http://imasters.uol.com.br/artigo/2417/db2/visual_explain/;