Guia de estudos DB2 – Prova 731 – Parte 3

Esta parte de nosso estudo para a certificação DBA DB2 (731) cobre o Centro de Tarefas, gerência de índices, constraints, view e tabelas do catálogo do sistema. Por favor, ignore erros de português. Esse guia foi escrito conforme eu ia estudando, portanto, a preocupação era aprender, e não escrever bem.

3.1 Centro de tarefas
O centro de tarefas do DB2 permite que sejam criadas tarefas agendadas que podem executar comandos do DB2, comandos do SO, comandos JCL ou MVC e ainda uma combinação dos comandos anteriores. Para pode utilizar o centro de tarefas, o banco de dados tools catalog deve ser criado com o comando:

db2 create tools catalog cc create new database toolsdb

Para iniciar o centro de tarefas o comando é db2tc. As tarefas podem ser de vários tipo, podem por exemplo executar rotinas de backup, limpeza, cálculos, etc. Elas podem ser agendadas e usuários podem ser notificados via email ao final da execução.

3.2 Índices
Índices são utilizados para garantir valores únicos em tabelas e para melhorar a performance de consultas.

Podemos criar um index com o comando “create index” ou ainda quando criamos um tipo UNIQUE em uma tabela, quando criamos um campo referenciando outro campo em uma outra tabela ou ainda quando criamos uma tabela com mais que uma dimensão, um índice é criado.

Um DBA tem que pesar muito bem o benefício de se criar um índice. Quando qualquer dado de uma tabela á alterado, o índice tem que ser alterado também, e isso toma tempo de processador além de espaço em disco.

Um índice pode ser criado no momento da criação da tabela ou ainda criado a qualquer outro momento. As sintaxes são:
CREATE TABLE TEST (
column 1 definition, column 2 definition, …
) IN INDEX IN

CREATE INDEX ON
(  column 1 ,  column 2 …  )

É possível adicionar colunas em um índice:
CREATE UNIQUE INDEX IX ON EMPLOYEE (EMPNO) INCLUDE(LASTNAME,FIRSTNME)

Normalmente cria-se 1 ou 2 índices por tabela em um banco OLTP, para um banco misto são 2 a 4, e finalmente para um DW são 5 ou mais.

As tabelas de índices são residentes no mesmo table space que a tabela reside por padrão, mas você pode especificar um outro table space, o que é até uma boa prática.

3.3 Constraints

São restrições definidas pelo DBA para garantir integridade ao banco, por exemplo: validar se um campo chave não se repita, validar se um campo filho tem seu correspondente, validar o conteúdo a ser inserido, etc.

Podem ser definidos na criação da tabela ou na alteração da mesma:
ALTER TABLE EMPLOYEE  ADD CONSTRAINT check_job  CHECK (JOB IN (‘Engineer’,'Sales’,'Manager’))

CREATE TABLE EMPLOYEE  (  EMPNO INT NOT NULL PRIMARY KEY,  JOB VARCHAR(10) CONSTRAINT CHECK_JOB
CHECK (JOB IN (‘Engineer’,'Sales’,'Manager’)),  …  )

No momento de criar uma constraint o banco verifica se a mesma pode ser criada, vendo se não existe algum dado que a quebre. É possível criar uma constraint “not forced”, o que quer dizer, que o valor não tem que ser obrigatoriamente o que está sendo inserido, alterado:
SEX CHAR(1) NOT NULL    CONSTRAINT  SEXOK    CHECK (SEX IN (‘M’,'F’))    NOT ENFORCED    ENABLE QUERY OPTIMIZATION,

O ENABLE QUERY OPTIMIZATION vai otimizar a query para os valores que estão no check, então, quando se efetuar um select filtrando por um valor diferente de M ou F, o resultado não será trazido, pois a query estará otimizada para os valores M e F. (Essa pergunta sempre cai na prova)

É possível desabilitar a otimização:
ALTER TABLE EMPDATA
ALTER CHECK SEXOK DISABLE QUERY OPTIMIZATION

3.4 Views

Uma view é uma “tabela virtual” derivada de uma ou mais tabelas. Quando você altera os dados em uma view, o campo da tabela original, também é alterado, desde que o DB2 possa determinar a origem do dado, porém, você pode definir se a view poderá sofrer operações tais como update, delete, etc.

Sintaxe do comando:
CREATE VIEW view-name (column list) AS (fullselect)

Exemplo de comando de criação:
CREATE VIEW TELEPHONE_BOOK AS  (  SELECT FIRST_NAME, LAST_NAME, EXTENSION FROM PERSONNEL  )

Podemos criar views a partir da união de duas ou mais tabelas. É possível alterar dados de uma view, porém tenha em mente que ao fazer isso, você estará automaticamente alterando a tabela da qual a view é originada.Quando criamos uma view utilizando o parâmetro “WITH CHECK OPTION”, garantimos que só será feito um update na mesma se for utilizado a cláusula where.

Uma View inoperante é uma view que não tem mais função, por exemplo, a tabela a qual a mesma se referencia foi deletada ou não se tem privilégios sobre a mesma.

3.5 Tabelas do catálogo so sistema

Estas tabelas são utilizadas para manter nosso banco de dados e contem informações tais como definição das tabelas, permissões, etc. Elas ficam no esquema SYSCATSPACE. Quando um banco é criado, é dado permissão de select para todo mundo (public) nestas tabelas.Para remover o privilégio de select em uma tabela o comando é:

REVOKE SELECT ON SYSCAT.DBAUTH FROM PUBLIC

Para dar permissão, trocamos o Revoke por Grant.

As tabelas do catálogo mais úteis são:

• SYSCAT.COLUMNS: Onde estão as colunas de TODAS as tabelas do banco.
• SYSCAT.INDEXCOLUSE: Mostra as colunas que são índice.
• SYSCAT.INDEXES: Mostra os índices de cada tabela.
• SYSCAT.TABLES: Mostra todas as tabelas do sistema.
• SYSCAT.VIEWS: Mostra todas as tabelas do sistema.

3.6 Forçando dados únicos

A clausula UNIQUE força que determinado campo em uma tabela tenha um valor único, por exemplo, o CPF, não será repetido nas outras linhas da tabela. Quando uma primary key é criada, o campo automaticamente será do tipo UNIQUE. Também pode-se criar um campo que não seja PK mas seja do tipo Unique, por exemplo:

CREATE TABLE EMPLOYEE  (  ….,
SOCINS CHAR(11) NOT NULL UNIQUE,
…,  )

Finalmente pode ser criado um índice único, como em:
CREATE UNIQUE INDEX UNIQUE_EMPLOYEE ON EMPLOYEE(EMPNO)

Voltar para o índice do guia.

Classe exemplo de conexão JAVA + DB2

Criei uma classe em java com um exemplo de conexão com o banco de dados DB2.

A classe executa ainda uma query em uma tabela X e um insert.

Baixe clicando aqui -> conecta

Renomeie o arquivo de odt para .java. O wordpress não permite efetuar upload de .java então tive que renomear.

Editado: Segue aqui a segunda parte do exemplo. Um menu com várias opções (incluir, excluir, Listar, etc) e classes de conexão e manipulação de clientes em banco.

Enjoy.

Update parcial de dados XML no DB2

Vários posts atráz, eu falei de DB2 Pure XML (http://jmmwrite.wordpress.com/2007/10/04/db2-purexml-entendendo-e-aplicando/) e citei a stored procedure DB2XMLFUNCTIONS.XMLUPDATE, que permite fazer update dentro de conteudos XML.

Pois bem, encontrei um link muito bom no developer works que mostra como implementar a mesma em seu banco de dados.

Aqui está o material: http://www.ibm.com/developerworks/db2/library/techarticle/dm-0605singh/

Ainda para quem gosta de banco de dados, não necessariamente DB2, recomendo dar uma olhada em meus posts anteriores aqui -> http://jmmwrite.wordpress.com/category/db2/

Enjoy!

Livro de DB2 em português, grátis

Meus amigos embaixadores Andrea Rodacki e da Caroline Perin fizeram um ótimo trabalho de tradução do livro“GETTING STARTED WITH DB2 Express-C”.

O livro pode ser utilizado para ter uma boa introdução sobre o DB2. Recomendadíssimo!

O mesmo pode ser baixado gratuitamente daqui: http://www.ibm.com/developerworks/wikis/display/DB2/FREE+Book-+Getting+Started+with+DB2+Express-C

Enjoy!

DBJMIN – Cliente de banco de dados multiplataforma e open source

Recomendo aos desenvolvedores de plantão darem uma olhada no software DBJMIN do meu amigo José Damico. DBJMIN é um cliente de multiplos bancos de dados inspirado no phpMyAdmin.

Com ele você se conecta em um banco de dados qualquer sem a necessidade de instalar nada em sua máquina, e assim, pode realizar instruções SQL.

O DBJMIN tem versões WEB e Desktop.

Pode se conectar no DB2, MySQL, Portgresql, Firebird, Derby e Oracle.

O melhor de tudo é que ele é open source e multiplataforma!

Para baixar, eis o link: http://dcon.com.br/dbjmin/.

Enjoy!

Boas práticas em SQL para desenvolvedores

Quem nunca ouviu alguem reclamar: “o sistema está lento hoje!!!”? Nestes relatos de degradação de desempenho, frequentemente levantamos que esta degradação é decorrente de instruções SQL mal estruturadas ou ainda banco de dados mal planejado, o que, num efeito cascata, só é sentido conforme o sistema vai sendo utilizado, as tabelas sendo povoadas, etc. O SGDB começa a exigir muito processamento, memória e a gerar gargalos na rede, causando assim, efeitos no desempenho da aplicação e na rede!

Outro fato é que atualmente, boa parte dos desenvolvedores desenvolvem código SQL sem ter muito conhecimento sobre fundamentos de banco de dados. Tal falta de conhecimento gera a produção de código ineficiente e com baixa performance. É comum ver equipes de desenvolvimento que não tem um DBA, ficando assim, o desenvolvedor com a tarefa de criar um banco de dados.

Com estes problemas em mente, resolvi criar este post com algumas dicas para que os desenvolvedores tenham algum conteúdo básico e rápido e melhorem suas instruções SQL e a criação de banco de dados.

Tentarei ser o mais genérico possível para conseguir cobrir os bancos de dados mais utilizados atualmente (DB2, Oracle, MySql, Postgres, etc), porém, algumas dicas podem não ser aplicáveis a todos os bancos.

É importante lembrar que praticamente todas as dicas são “debatíveis” em diferentes cenários, portanto, fiquem a vontade para comentar.

Vamos lá:

1- Normalize seu banco de dados. Isso quer dizer básicamente, divida tabelas grandes em tabelas menores e remova redundancia, ou seja, que dados estejam duplicados sem real necessidade.

2. Em instruções select, evite usar “*”. Seja restritivo, traga somente os campos realmente necessários, isso alivia a memória do servidor, diminue tráfego na rede, etc. Algumas pessoas defendem que tambem não devem ser criados determinados campos, por exemplo, você tem A + B e pretende guardar C onde C = A + B. Ao invéz de criar uma coluna para armazenar C, passe a utilizar “select (A+B) AS C from tabela”. Esse pensamento pode não ser necessariamente válido para Dws. Vamos supor que você tem uma tabela enorme com dados sobre salário por ano. Você pode armazenar o percentual de ajuste e o valor ajustado, fazendo ai uma “desnormalização” para que o comando que vai recuperar os valores do salário não “frite” a CPU forçando-a a fazer muitas contas e perdendo muito desempenho.

3. Existe muito debate sobre essa: Não utilize seu banco de dados para armazenar imagens, ao invéz disso, armazene a URL. Vale lembrar que os bancos de dados atuais estão cada vez mais aprimorados na manipulação de imagens, portanto, aqui abre-se espaço para uma enorme discussão, benchmarck, etc.

4. Para obter maior performance, utilize chaves primárias numéricas ou ainda campos pequenos nas chaves.

5. Utilizando-se stored procedures e functions ao invéz de escrever código no seu programa, vai garantir maior desempenho e segurança para seu sistema como um todo.

6. Utilize o conceito de transações. Vários problemas podem ocorer, por exemplo, a rede cair. Aprenda sobre commit e rollback.

7. Use sempre o tipo de dados correto para armazenar os dados. Por exemplo, não armazene sexo, que vai ser M ou F em um campo Varchar, use apenas 1 caractere: CHAR(1).

8. Evite o uso de cursores, eles consomem muito tempo já que “navegam” registro por registro.

9. Otimize a clausula WHERE: Simples exemplos são o uso de “>” e “>=”. Se você quer retornar todas as pessoas de uma tabela que tem idade “> 3”, use no where “>=4”, dessa forma o banco não fará o scan das páginas até encontrar o 3. Esse princípio é válido desde que você tenha um índice na idade.

10. Quando possivel, crie instruções SQL idênticas, pois no momento da execução de uma instrução, o banco compila a mesma e a preserva em memória, na próxima execução, não vai precisar compilar novamente. Uma ótima técnica para fazer isso é utilizar variáveis nas suas instruções ao invés de passar parametros para o banco.

11. Utilize os mecanismos do banco de dados para persistência: Primary Key, Foreign Key, etc são feitos e otimizados para isso.

12. Quando possivel, trave (lock) uma tabela para executar alguma operação que vai demandar muito acesso a esta tabela, por exemplo, se você vai alterar a estrutura de uma tabela grande ou importar dados neste tabela (falando-se de tabelas realmente grandes).

13. Sempre utilize o nome das colunas em instruções SELECT, INSERT, UPDATE evitando utilizar “*”.

14. Evite utilizar o operador “LIKE”, ele pode facilmente fazer o desempenho de um banco de dados ruir!

15- Utilize EXISTS ao invéz de COUNT para verificar se existe um determinado registro em uma tabela. É comum ver desenvolvedores fazendo um “select count(X) from Y” para verificar se o COUNT é maior que 0. Utilizando-se EXISTS, o sgbd vai parar no primeiro registro encontrado, se utilizar count, o banco vai varrer toda a tabela.

16- Em joins de tipos de dados diferentes, o SGBD vai ter que converter o tipo hierarquicamente inferior para o outro tipo a fim de efetuar a comparação, e assim, não vai utilizar um índice caso exista.

17. Sobre índices:

• Não crie indices em campos que são alterados constantemente, pois o banco vai ter que atualizar toda sua estrutura de índices em qualquer update feito no campo.
• Prefira criar os indices em chaves primárias e estrangeiras, e em suas queries, utilize estes índices.
• Não tenha muitos índices em seu banco, só o necessário: uma breve explicação sobre o motivo disso, é que o banco de dados mantem toda uma estrutura para gerenciar os índices, então, quanto mais índices, mais tempo/processamento o SGBD vai utilizar para a manutenção dos mesmos.
• No momento de importação/importação de uma base de dados, não exporte/importe índices. Isso vai consumir mais tempo/processamento. Tambem pode-se não fazer backup de índices.
• Não crie índices em colunas que possuem pouca variação de valores.

18. Entenda os fundamentos de banco de dados. Uma ótima leitura é o livro “Sistema de Banco de Dados”, de Abraham Silberschatz, Henry F. Korth e S. Sudarshan lançado no Brasil pela Editora Elsevier.

DB2 Information Center

Nos eventos do Academic Initiative eu falo muito de DB2 e tambem oriente vários alunos no desenvolvimento de software utilizando DB2. É muito comum as pessoas me perguntarem alguma coisa sobre DB2 e eu responder: “Dê uma olhada no Information Center”. O fato é que eu raramente lembro o link de cabeça, mas como não esqueço o link do meu blog, farei assim agora: “procure no meu blog que lá eu coloquei o link!”.

Enfim, aqui está o link: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp

Para quem não sabe, o information center é uma espécie de manual online completíssimo! Ele não se limita a explicar as coisas, mas dá exemplos, o que ajuda no entendimento. Para quem busca certificação, é um bom companheiro!

Enjoy!

DB2 pureXML: entendendo e aplicando

O Banco de Dados DB2 versão 9 para Linux, Unix e Microsoft Windows foi anunciado como revolucionário por razão da sua capacidade de trabalhar com XML nativamente. Por se tratar de um produto relativamente novo, há na literatura disponível poucos estudos que abordem de maneira objetiva e prática suas características em relação ao XML. Portanto, escrevi um artigo em conjunto com o professor Dr. Alex Poleto, da FEMA-ASSIS, que apresenta as características e funcionalidades do DB2 e demonstra o uso do mesmo em um cenário definido, com a intenção de contribuir com a comunidade acadêmica e profissional gerando um material de consulta que poderá servir de base para novos estudos.

Fiz uma apresentação de 15 minutos (que foi péssima devida ao pouco tempo) na FEMA-Assis no dia 04/10/2007 mostrando um pouco do artigo.

A apresentação e o artigo podem ser baixados nos seguintes links:

Artigo- http://dcon.com.br/jd.comment/juliano/Artigo_Juliano_v9.doc
Apresentação- http://dcon.com.br/jd.comment/juliano/DB2_pureXML_public.ppt

Foram retiradas algumas figuras da apresentação por questões de direitos autorais.

Espero que aproveitem.

Por favor, podem sugerir melhorias, criticar, etc.

Abraços,

Juliano