DB2 - SQL - Dicas importantes para utilizar DB2



Desenvolvido por DORNELLES Carlos Alberto - Analista de Sistemas - Brasília DF. - cad_cobol@hotmail.com

DB2 - SQL - Dicas importantes para utilizar DB2
  1. 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.

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

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

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

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

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

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

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

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

    • 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

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

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

    • 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 evitandopesquisar 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 ocorrequando 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 parapossí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.