Dicas importantes para utilizar DB2

01 -        Sempre que for previsível um reduzido número de linhas em resposta a um comando SELECT, utilizar a cláusula OPTIMIZE FOR ‘n’ ROW.  Esse comando não terá efeito se :

·                A query usa SELECT DISTINCT;

·                A query usa GROUP BY ou ORDER BY, e não existe índice que satisfaça a ordem.

·                A query usa UNION  ou  UNION ALL.

                         

02 -        Definir LUW ( Unidade Lógica de Trabalho ) para cada programa, estabelecendo COMMIT POINT para programas batch e SYNC POINT para programas ON-LINE.                                                                                                                             

03 -        No ambiente CICS utilizar sempre processos pseudo-conversacionais. Para cada display de tela definir uma LUW completa.

04 -        Evitar grandes funções de aplicação na mesma transação. Procure dividir em    transações menores, o que melhora o desempenho e o entendimento do programa. Observar que a modularização de programas não soluciona este tipo de problema.

05 -        Programas Batch com longos tempos de execução devem ser analisados para verificar a possibilidade de divisão em várias execuções concorrentes.

06 -        Evite disponibilizar on-line funções de natureza  batch (impressão de relatórios, por exemplo).

07 -        Evite transações cuja saída seja um grande número de telas, que provavelmente não terão nenhum valor informativo e não serão usadas na prática.

08 -        Utilize preferencialmente a seguinte técnica de BROWSING :

·                Definir uma quantidade de linhas / telas a serem mostradas e fazer o acesso com uma única seqüência de statements SQL.

·                Guardar o resultado em uma área temporária e, se possível, guardar informações sobre o posicionamento do cursor, dando ao usuário a opção de ver mais telas, executando então novamente o statement SQL formando um LOOP dentro do programa. Deve-se atentar  que deverá ocorrer um LOCK na página que contém o último registro de cada CURSOR, impedindo a atualização enquanto não for atingido o SYNCPOINT ou o CURSOR não for fechado.

09 -        Prover rotinas de erro para tratar SQL codes negativos.

10 -        Usar CS ( CURSOR STABILITIY ) como opção do parâmetro ISOLATION DO BIND. Quando houver  uma  real necessidade de manter o LOCK dos registros lidos até o COMMIT POINT, para assegurar que os registros lidos não sejam alterados por outro usuário, então usamos a opção RR. O problema é que a opção RR prende todos os registros lidos, causando assim contenção de recursos e também lock escalation. Portanto, a utilização de  RR ( REPEATABLE READ ) deve ser bem justificada.

11 -        Codificar SELECT FOR UPDATE o mais tarde possível, e a liberação do UPDATE o  mais breve possível, para assegurar o menor tempo possível de lock do registro. Caso a atualização não for feita por qualquer motivo ( lógica ou erro ), liberar o registro preso.

12 -        Não usar o cursor definido para atualização ( SELECT FOR UPDATE ) numa linha  simples sem intenção de atualização, para não prender o registro desnecessariamente.

13 -        Codificar o OPEN CURSOR o mais tarde possível e CLOSE CURSOR o mais breve.

14 -        Programas BATCH que fazem UPDATE devem atentar para :

14.1 -    Rodar fora do horário do on-line, e se necessário e possível, utilizar a opção de  LOCK TABLE. Isso assegura que nenhum batch ou on-line acessará a tabela ao mesmo tempo

14.2 -    Analisar statements SQL de UPDATE/DELETE quanto ao número de registros    atualizados. Se o número for muito grande, avaliar a possibilidade de COMMITS    periódicos. Se não for usado cursor pode-se tentar dividir o statement SQL em vários    statements para possibilitar commits intermediários. Programas batch podem também ter arquivo de entrada dividido em várias partes permitindo commits mais freqüentes.

14.3 -    Programas batch devem ter o arquivo de dados de entrada classificado na seqüência    do índice cluster, se ele existir. Isso melhora a performance de execução.

14.4 -    Minimizar o processamento de SORT do DB2, principalmente em tabelas grandes,    utilizando corretamente os statements SQL que o chamam ( SELECT DISTINCT,    ORDER BY, UNION, JOIN). Índices devem ser usados para evitar SORT.

15 -        Para garantir a seqüência no SELECT é necessário utilizar  ORDER BY.

16 -        Definir indicator variable quando usar funções que possam dar resultado nulo.

17 -        JOINs de 5 ou mais tabelas devem ser prototipados e analisados para permitir que     índices do DB2 possam ser utilizados.

18 -        Utilizar filtros do DB2 evitando pesquisar as linhas da tabela através de teste dentro    do programa. Colocar o maior número possível de predicados na cláusula WHERE.      

19 -        Evitar  reunir no mesmo statement SQL, funções distintas de processamento, o que   pode levar à não utilização de índices.

20 -        Evitar SQL dinâmico em projetos estruturados, devido ao seu custo (BIND    DINAMICO). Em algumas situações o SQL dinâmico poderá ser mais rápido que o    SQL estático ( por exemplo LIKE ‘char%’).

21 -        Só usar CURSOR se houver real necessidade. Considerar a utilização de    statements UPDATE / DELETE sem CURSOR.

22 -        A utilização da cláusula DISTINCT pode implicar na não utilização de índice e executar um SORT. Portanto o comando:

SELECT DISTINCT col5 FROM TAB5                    deve ser substituído por :

SELECT col5 FROM TAB5 GROUP BY col5

23 -        Evitar usar DISTINCT se apenas uma linha é retornada. A utilização do DISTINCT só se justifica quando há possibilidade de retorno de linhas duplicadas.

24 -        De uma forma geral codifique na cláusula WHERE os predicados unidos por AND na    ordem do mais restritivo para o menos restritivo. Se não houver índices nas colunas    e for usado o predicado ‘ = ‘ , a pesquisa será feita na ordem apresentada. Por  exemplo:

...WHERE SEX = ‘ F ‘ AND  JOB = ‘CLERK ‘            deve ser substituído por:

...WHERE JOB = ‘CLERK ‘ AND  SEX = ‘F’

25 -        O otimizador considera que um predicado de igualdade ( = ) é mais restritivo que    predicados de intervalo. Se isso não for verdade a performance pode ser melhorada   usando a cláusula IN como alternativa à igualdade. Por exemplo :

... WHERE EDLEVEL > 18 AND SEX = ‘ F ‘              deve ser substituído por:

... WHERE EDLEVEL > 18 AND SEX IN ( ‘ F ‘, ‘ Q ‘ )

supondo que não existe o valor ‘ Q ‘ para sexo. Neste caso o predicado > será processado antes do predicado  IN.

26 -        Utilizar VALIDATE (BIND)  em produção.

27 -        Utilizar USE/COMMIT como opção de BIND para permitir maior concorrência.

28 -        Utilizar ALLOCATE / DEALLOCATE quando não se visualiza problemas de    concorrência e queremos assegurar que todos os recursos estejam disponíveis ao    programa antes de começar a execução.

29 -        Prototipar SQL’s complexos e críticos. Eventualmente um tablespace scan pode ser   mais rápido que a utilização de índices, principalmente se múltiplos índices são    usados. Medições de execução ajudam a entender e encontrar a melhor solução.

30 -        Evitar o produto cartesiano que ocorre quando as colunas do JOIN não são    corretamente especificadas. Isso conduz a um resultado errado e demorado.

31 -        Considerar a utilização da seguinte técnica para testar se a tabela está vazia:

SELECT MIN(A)  FROM  TAB1 

onde  A  é a primeira coluna da chave primária.

32 -        Ao definir variáveis que serão usadas para comparações com colunas, utilizar o mesmo tipo de dado, mesmo tamanho e mesma escala. Caso contrário os índices não serão usados nestas colunas.

33 -        Colunas comparadas devem ter o mesmo tipo de dado, mesmo tamanho e mesma    escala, para permitir ao DB2 a utilização de índices. Se isso não está ocorrendo,    verificar projetos lógico e físico.

34 -        Se possível utilizar JOIN’s no lugar de SUBQUERIES. De um modo geral é mais    eficiente.

35 -        Não usar SELECT * . Fornecer o nome somente das colunas que serão utilizadas.

36 -        Não utilize expressões aritméticas nas comparações. Faça as operações aritméticas    antes das comparações.

... WHERE   col5 = :hv1   +   15             deve ser substituída por :

              hv1   =   hv1  +  15

              ... WHERE   col5 =  :hv1

37 -        Às vezes é possível substituir uma expressão por  BETWEEN  :

... WHERE  INTEGER(COL6 / 7)  = 2                          pode ser substituída por :

              ... WHERE COL6  BETWEEN  14  AND  20

38 -        Predicados com funções escalares não são considerados para utilização de índices    pelo otimizador Portanto, devem ter seu uso analisado e prototipado para verificar    a performance.

39 -        Especificar nomes das colunas no INSERT, para tornar o programa independente de    alterações nas colunas da tabela.

40 -        SELECT’s com NOT BETWEEN, NOT LIKE, NOT IN, NOT = e  IS NOT NULL, não    usam índices e devem ser analisados para possível substituição por outros, em caso    de má performance.

41 -        A utilização de LIKE com variáveis host em tabelas grandes deve ser bem    analisada por não utilizar índices. Deve-se avaliar a substituição por BETWEEN.

42 -        Utilizar as funções BUILT-IN ou agregações de SQL ao invés de fazê-las de    maneira convencional no programa de aplicação.

43 -        No SELECT, utilizar sempre que possível o JOIN do SQL ao invés de fazer JOIN por aplicação ( a cada leitura de linha da tabela A acessa-se a tabela B para fazer os    casamentos).

44 -        JOINS de tabelas grandes devem ser analisados com cuidado. A utilização de índices adequados e a estratégia do JOIN são fatores muito importantes para a performance. Além disso devem ser fornecidos predicados para restringir ao máximo as linhas selecionadas.

45 -        Para índices de múltiplas colunas, em caso de não utilização do índice por baixa    cardinalidade, pode-se tentar aumentar a cardinalidade especificando-se  >= 0 ou branco para a próxima coluna.  Por exemplo :

...WHERE COL5  =  X                 pode ser substituído por:

...WHERE COL5 = X  AND COL7 >=  0

46 -        Evite teste de existência antes da inclusão de registros. Melhor incluir o registro e    testar o SQLCODE.

47 -        Evitar statements SQL redundantes, como por exemplo a mesma linha ser    recuperada em módulos diferentes usados pelo mesmo programa.

48 -        Atualizar somente as colunas realmente necessárias, evitando atualizar a linha inteira.

49 -        Especificar predicados redundantes no JOIN quando usar BETWEEN. Isso permite ao otimizador avaliar qual predicado tem menor fator de filtro, levando a uma escolha    mais correta da tabela outer do JOIN.

50 -        Utilizar BETWEEN ao invés de >= e <=. Isso diminui o fator de filtro, aumentando a    possibilidade de uso de índice.

51 -        Avaliar a utilização de UNION ALL ao invés de UNION, se é sabido que o conjunto    resultante não contém duplicatas. UNION implica no uso do SORT para eliminar    duplicidade de linhas e UNION ALL não utiliza SORT.

52 -        Se o Select possui cláusula GROUP BY a classificação será ascendente a menos que exista índice definido para essa coluna. Nesse caso, a sequência obedecerá a sequência do índice, que pode estar descendente.

53 -        Colunas GROUP BY não precisam ser referenciadas pelo Select, mas colunas referenciadas pelo Select  que não sejam função precisam ser agrupadas pelo GROUP BY. Esse erro é frequente.

54 -        Nunca use Subquery correlacionada, pois é executada a cada linha devolvida ao Select externo.

55 -        Use o Order By só quando necessário pois gera CPU adicional

56 -        Considere Sort Externo no lugar de Order By para resultados maiores que 1.000.000 linhas.

57 -   Utilize o Explain para listar alternativas de estratégias e verificação eficiência de instrução SQL.

58 -        Em colunas VARCHAR, não preencher o tamanho total com espaços (MOVE SPACES TO ...), para não inviabilizar a característica da coluna. Colunas Varchar totalmente preenchidas, sem necessidade, acarretam desperdício de espaço em disco.