Maximize o uso de SQE com DB2 criado por DDS para bancos de dados i

Impulsione sua performance SQL

Os aplicativos que utilizam SQL possuem a melhor performance quando são processados por DB2® para Mecanismo de Consulta i SQL (SQE). Entretanto, o uso de SQE pode ser dificultado se o seu banco de dados utilizar arquivos lógicos chaveados contendo critérios selecionar/omitir. Este artigo descreve como utilizar o atributo de opções de pesquisa IGNORE_DERIVED_INDEX para maximizar o uso de Mecanismos de Consulta SQL com bancos de dados que contenham arquivos lógicos chaveados.

Kent Milligan, Senior DB2 Consultant, IBM

Kent Milligan é consultor sênior DB2 para i na Ativação de Soluções ISV IBM para a plataforma i da IBM. Depois de se formar na Universidade de Iowa em 1989, Kent passou os primeiros oito anos de sua carreira na IBM como membro da equipe de desenvolvimento DB2 em Rochester. Ele fala e escreve regularmente sobre vários tópicos sobre DB2 para bancos de dados relacionais i.



15/Mar/2010

Durante muitos anos, os clientes i IBM tiraram proveito dos benefícios de um banco de dados relacional integrado ao sistema operacional — esse banco de dados é conhecido como DB2 para i. Um dos benefícios mais importantes do DB2 integrado para banco de dados i é a capacidade de suportar interfaces múltiplas.

DB2 para i inclui tanto uma interface SQL e uma interface nativa (não SQL) . Essas duas interfaces podem ser usadas de maneira intercambiável. Os objetos DB2 criados com a interface nativa DDS (Data Definition Specifications) podem ser acessados e modificados por aplicativos que utilizam SQL. De maneira similar, os aplicativos usando interface nativa de registro de nível podem escrever e ler dados de objetos DB2 criados com SQL. Essa incrível flexibilidade fornecida pelo DB2 integrado para banco de dados i tem permitido aos aplicativos i da IBM a evoluírem com o passar do tempo e alavancar novas tecnologias SQL, como expressões OLAP e objetos de sequência sem que os desenvolvedores tenham que recriar todo o seu banco de dados.

O DB2 para Mecanismo de Consulta i SQL (SQE) tem evoluído de maneira similar à dos aplicativos i da IBM que estão alavancando melhorias SQL. O primeiro estágio do SQE foi entregue há algum tempo com o release V5R2 para melhorar a performance das interfaces de acesso de dados SQL. Os recursos e performances do SQE têm melhorado drasticamente em cada novo release desde o V5R2. Alguns clientes IBM viram o desempenho de consultas complexas melhorar em mais de 10 vezes com o SQE.

Além das melhorias de performance, a outra boa notícia é que a lista de recursos SQL não suportados pelo SQE continua a diminuir. A partir da versão 6.1 do DB2 para i, os únicos recursos não suportados são:

  • Referência de arquivo lógico na cláusula FROM
  • Arquivos lógicos chaveados selecionar/omitir definidos na tabela referenciada ou arquivo físico
  • Sequências de classificação ICU 2.6.1
  • Interfaces não SQL (OPNQRYF, Query/400, QQQQRY API)

O DB2 para i não pode usar SQE para processar uma instrução SQL se a solicitação SQL ou ambiente do aplicativo contiver um ou mais recursos não suportados. Para links com maiores informações sobre SQE, consulte a seção Recursos deste artigo.

Suporte SQE para arquivos lógicos

Enquanto o Mecanismo de Consulta SQL não suporta alguns objetos DB2 criados por DDS, a lista de recursos não suportados demonstra que nem todos os objetos DDS são suportados. O SQE pode processar instruções SQL que fazem referência a arquivos físicos. Além disso, o otimizador de consulta SQE pode usar arquivos lógicos chaveados para aumentar a velocidade de classificação ou seleção de dados durante a execução de uma instrução SQL.

O otimizador SQE inicial suporta arquivos lógicos chaveados. Entretanto, esse suporte era limitado a arquivos lógicos simples que são equivalentes a um índice SQL. Toda vez que o otimizador de consultas SQE encontrava um arquivo lógico chaveado com derivações, como mapeamento de campo ou critério selecionar/omitir, a instrução SQL associada era novamente roteada para o Classic Query Engine (CQE) para a execução.

Com o release DB2 para i 6.1, o otimizador de consultas SQE necessita somente passar a solicitação SQL ao CQE para arquivos lógicos chaveados selecionar/omitir — no nível do release 6.1, o otimizador suporta arquivos lógicos chaveados contendo mapeamentos de campo chaveados derivados. Exemplos de mapeamentos de campo chaveados incluem o mapeamento de um campo chaveado para um comprimento diferente ou configuração Coded Character Set Identifier (CCSID).


Exemplo de suporte a arquivo lógico SQE

Para assegurar que o conceito de suporte a arquivo lógico SQE seja entendido, revisaremos um cenário simples. Suponhamos que haja um arquivo físico (MYPF) que possui um arquivo lógico chaveado (LF2) definido sobre ele. Como mostra a Listagem 1, a fonte DDS para L2 contém critérios selecionar/omitir para o nome de campo REGNFLD.

Listagem 1. DDS para o arquivo lógico LF2
*************** Beginning of data *************************************
                R PFREC                     PFILE(MYPF)                
                  CTYNAME       30A                                    
                  CTYID                                                
                  REGNFLD                                              
                K CTYID                                                
                S REGNFLD                   COMP(EQ 'EAST')            
****************** End of data ****************************************

Os relacionamentos entre LF2 e MYPF são mostrados graficamente na Figura 1. Além do LF2, o arquivo MYPF possui dois índices definidos sobre ele: um índice SQL chamado IX1 e um arquivo lógico chaveado chamado LF1. O critério selecionar/omitir contido no LF2 faz o DB2 classificá-lo como um índice derivado. Nesse exemplo, o LF2 é o único índice que o otimizador de consultas classificaria como índice derivado.

Figura 1. Relacionamentos do Índice e do arquivo lógico com o arquivo físico MYPF
Hierarchy of the MYPF indexes and logical files. Programs using the native interface access the LF2 keyed logical file.

Para este cenário, a instrução SQL de exemplo SELECT mostrada na Listagem 2 está incluída em um aplicativo para recuperar dados do MYPF.

Listagem 2. Instrução de exemplo SELECT
  SELECT ctyname, regnfld FROM mypf 
   WHERE ctyname='ROCHESTER'

Quando o aplicativo é executado, o otimizador de consulta SQE é o primeiro a processar a instrução SQL. Uma das primeiras tarefas que o otimizador SQE executa é determinar os índices no arquivo MYPF para determinar se algum dos índices pode ser usado para aumentar a velocidade da consulta. Quando o otimizador SQE detecta que um dos índices contém a derivação selecionar/omitir, ele interrompe imediatamente o processamento a passa a execução da instrução SELECT para o CQE


Processo de tomada de decisão SQE

Como descrito na seção anterior, a mera associação de um arquivo lógico selecionar/omitir impede o SQE de processar qualquer instrução SQL que faz referência a um arquivo lógico MYPF. Esse processo de decisão SQE é mostrado abaixo na Figura 2.

Figura 2. Arquivo lógico derivado prevenindo o uso do SQE
MYPF hierarchy showing SQL requests not being able to use SQE because of the select/omit criteria in LF2.

A falta de suporte do Mecanismo de Consulta SQL para arquivos lógicos derivados pode ser problemática, visto que é comum aplicativos i da IBM possuírem uma mistura de interface similar a da Figura 2— um conjunto de objetos DB2 originalmente criado a partir da interface DDS está sendo agora acessado por programas usando interface de nível de registro nativa e também interfaces SQL.


Permitindo o uso de SQE em ambientes DDS

Obviamente, a IBM não sugere que os clientes excluam seus arquivos lógicos chaveados derivados somente para permitir que o SQE seja usado com mais frequência em solicitações SQL. Isso quebraria qualquer programa usando esses mesmos arquivos lógicos. Assim, a IBM criou uma nova opção para o arquivo de opções de consulta QAQQINI para permitir que o SQE seja usado em ambientes mistos sem a necessidade de excluir qualquer arquivo lógico.

O nome da nova opção QAQQINI é IGNORE_DERIVED_INDEX. Essa foi a primeira opção introduzida no release V5R3 do DB2. Especificando um valor *YES para essa opção, estaremos fazendo o otimizador de consulta SQE ignorar qualquer arquivo lógico chaveado derivado que ele encontre durante o processo de otimização. Quando estiver criando um plano de consulta para executar a instrução SQL, o otimizador SQE usa os índices SQL ou os arquivos lógicos chaveados não-derivados para a tabela DB2. O otimizador de consulta SQE não é usado por programas que usam interface de nível de registro nativa, então a opção IGNORE_DERIVED_INDEX não tem efeito algum em programas que usam um arquivo lógico chaveado derivado.

O impacto desta opção QAQQINI é representado graficamente na Figura 3. Especificar um valor *YES para a opção IGNORE_DERIVED_INDEX remove de maneira eficaz todos os arquivos lógicos chaveados derivados da lista de índices considerados pelo otimizador SQE. Assim, definindo a opção IGNORE_DERIVED_INDEX para *YES indica que há índices SQL suficientes e arquivos lógicos não-derivados para o otimizador utilizar no tempo de execução da instrução SQL.

Figura 3. Opção QAQQINI IGNORE_DERIVED_INDEX = *YES permitindo o uso do SQE
SQL requests are now able to use SQE because IGNORE_DERIVED_INDEX is set to *YES and the select/omit criteria in LF2 is ignored

Se um banco de dados possui uma boa estratégia de indexação, a performance é geralmente melhor com o SQE executando uma instrução SQL sem o uso de um arquivo lógico chaveado derivado do que a performance do CQE usando um arquivo lógico chaveado derivado na implementação do tempo de execução. Entretanto, há casos nos quais o plano de consulta CQE que utiliza arquivos lógicos selecionar/omitir chaveados foi mais bem executado do que a implementação SQE. Como resultado, é uma boa ideia planejar a execução de alguns testes e análises de seus aplicativos SQL antes de mudar seus sistemas de produção para usar a opção IGNORE_DERIVED_INDEX.


O Valor padrão de IGNORE_DERIVED_INDEX mudou no IBM i 6.1

Devido ao fato de se esperar que a performance SQL usando SQE seja tipicamente melhor, a IBM modificou o valor padrão da opção IGNORE_DERIVED_INDEX de *NO para *YES no realease IBM i 6.1. Então, com o DB2 para i 6.1, se uma instrução SQL fizer referência a um arquivo físico associado a um arquivo lógico selecionar/omitir chaveado, o SQE processará a solicitação SQL sem a necessidade de definir explicitamente a opção IGNORE_DERIVED_INDEX no arquivo de opções de consulta.

Essa mudança para a configuração padrão do IGNORE_DERIVED_INDEX também significa que devemos executar alguns testes de performance antes e depois de atualizar para IBM i 6.1, caso o ambiente possua aplicativos que usem o SQL em bancos de dados que contenham arquivos lógicos selecionar/omitir chaveados. O teste e a análise de desempenho podem descobrir algumas instâncias raras onde os tempos de resposta para instruções SQL seriam mais rápidos usando um plano de consulta CQE que possua um arquivo lógico selecionar/omitir chaveado. Nessas situações raras, pode-se precisar definir aplicativos seletivamente para usarem o valor *NO para a opção IGNORE_DERIVED_INDEX com o IBM i 6.1.

Suporte de índice SQL derivado

O release DB2 para i 6.1 também inclui suporte para índices SQL derivado. O valor da opção IGNORE_DERIVED_INDEX não exerce impacto sobre o uso de índices SQL derivados do otimizador de consulta SQE. Na verdade, somente o otimizador de consulta SQE tem a capacidade de usar os novos índices SQL derivados em planos de consulta — mais uma razão para querermos o maior número possível de instruções sendo processadas pelo Mecanismo de Consulta SQL.

Listagem 3. Exemplos de índices SQL derivados
CREATE INDEX ix_uCompName ON cust(UPPER(company_name))

CREATE INDEX ix_FullName ON emp(CONCAT(CONCAT(FName,' '), LName))

Configurando e usando um arquivo de opção QAQQINI

Mesmo sendo um usuário do DB2 para i de longa data, há grandes chances de você nunca ter tido a necessidade de usar o arquivo de opções QAQQINI para substituir as configurações padrões do otimizador e mecanismo de consulta DB2 para i. Aqui estão as etapas necessárias para usar o arquivo QAQQINI para selecionar a opção IGNORE_DERIVED_INDEX para permitir um maior uso do Mecanismo de Pesquisa SQL.

Etapa 1. Crie um arquivo de opção QAQQINI

use o comando do sistema CRTDUPOBJ (Criar um objeto duplicado) para criar seu próprio arquivo QAQQINI a partir do modelo mestre incluso em cada sistema na biblioteca QSYS. O exemplo na Listagem 4 mostra o comando CRTDUPOBJ sendo usado para criar uma cópia do arquivo QAQQINI na biblioteca chamada MYLIB.

Listagem 4. Exemplo de comando CRTDUPOBJ
 CRTDUPOBJ OBJ(QAQQINI) FROMLIB(QSYS) OBJTYPE(*FILE)
                      TOLIB(MYLIB) DATA(*YES) TRG(*YES)

Deve-se usar o comando CRTDUPOBJ pois há acionadores fornecidos pela IBM associados ao arquivo QAQQINI. Deve-se especificar *YES para o parâmetro TRG (Acionar) para assegurar que esses acionadores sejam propagados para sua cópia do arquivo QAQQINI.

Observe também que o comando CRTDUPOBJ continua com o mesmo nome de QAQQINI para o arquivo de destino. Um nome de arquivo QAQQINI é necessário para que o DB2 reconheça que o arquivo é arquivo de opção de consulta.

O último parâmetro que merece atenção no comando CRTDUPOBJ é TOLIB. Esse parâmetro controla a biblioteca onde o arquivo QAQQINI é criado. No exemplo na Listagem 4, o arquivo é criado na biblioteca chamada MYLIB.

Etapa 2. Defina valores no arquivo de opção QAQQINI

A seguir, deve-se especificar um valor para a opção IGNORE_DERIVED_INDEX. Devido ao fato de o próprio arquivo QAQQINI ser uma tabela DB2, pode-se usar qualquer interface de banco de dados para definir um valor para IGNORE_DERIVED_INDEX. Uma das maneiras mais simples de alterar o valor é usar uma instrução SQL UPDATE como mostra a Listagem 5.

Listagem 5. Exemplo de atualização do QAQQINI
	UPDATE MyLib/QAQQINI
	SET QQVAL = '*YES'
            WHERE QQPARM = 'IGNORE_DERIVED_INDEX'

Etapa 3. Escopo do uso de um arquivo de opção QAQQINI

A etapa final é dizer ao DB2 para i onde encontrar o arquivo QAQQINI. É possível fazer isso na primeira etapa. Como padrão, o DB2 procura por um arquivo QAQQINI na biblioteca QUSRSY. Assim, caso crie seu arquivo QAQQINI na biblioteca QUSRSYS, os valores da opção de consulta contidos no arquivo QAQQINI influenciarão todos os aplicativos e usuários no sistema. Se um arquivo de opção de consulta não for encontrado em QUSRSYS, o DB2 para i usa os valores padrões para todas as opções de consulta.

Pode-se usar o comando CHGQRYA (Modificar Atributos da Consulta) para prever o efeito do seu arquivo QAQQINI em um aplicativo ou trabalho específico. Ao invés de procurar por um arquivo QAQQINI na biblioteca QUSRSYS, o DB2 para i usa o arquivo QAQQINI na biblioteca especificada no parâmetro QRYOPTLIB (Biblioteca das Opções de Consulta) do comando.

A Listagem 6 mostra um exemplo de comando CHGQRYA que redireciona o DB2 para i para usar o arquivo QAQQINI na biblioteca MYLIB ao invés de usar a biblioteca padrão QUSRSYS.

Listagem 6. Exemplo de comando CHGQRYA
	CHGQRYA QRYOPTLIB(MYLIB)

A localização do arquivo QAQQINI especificada em um comando CHGQRYA permanece sendo usada para a duração do trabalho ou sessão do usuário, ou até o parâmetro QRYOPTLIB ser alterado por outro comando CHGQRYA.

Para os aplicativos que usam interfaces padrões da indústria, como ODBC e JDBC, o middleware do banco de dados fornecido com o sistema operacional IBM i inclui suporte para uma propriedade de conexão QAQQINI. Quando essa propriedade de conexão é especificada, os drivers IBM executam o comando CHGQRYA em nome do aplicativo. A Tabela 1 mostra a propriedade de conexão usada por cada interface de suporte.

Tabela 1. Suporte middleware do IBM i para a propriedade de conexão QAQQINI.
InterfacePropriedade de conexão
Caixa de Ferramentas IBM para Java JDBC Driverqaqqinilib
Developer Kit IBM para Java Native JDBC Driverqaqqinilib
IBM System i Access para Windows ODBC DriverQAQQINILIB ou QAQQINILibrary
IBM System i Access para Windows .NET ProviderQueryOptionsFileLibrary
IBM System i Access para Windows OLE DB Provider'Query Options File Library'

Suporte de substituição do arquivo QAQQINI

Outro novo recurso fornecido no release DB2 para i 6.1 é o procedimento armazenado de sistema OVERRIDE_QAQQINI na biblioteca QSYS2. Ele permite o uso de chamadas de procedimentos armazenados para controlar opções QAQQINI como IGNORE_DERIVED_INDEX, ao invés de trabalhar diretamente com um arquivo de opções QAQQINI. Isso é realmente útil quando queremos modificar temporariamente um arquivo de opções QAQQINI para a execução de uma ou duas instruções SQL.

O procedimento OVERRIDE_QAQQINI suporta os três parâmetros a seguir:

  • Código de função
    • 1 = Cria um arquivo QAQQINI temporário na biblioteca QTEMP baseado nos conteúdos de um arquivo ativo QAQQINI atual. Invoca o comando CHGQRYA para especificar o uso de um novo arquivo QAQQINI na biblioteca QTEMP.
    • 2 = Atualiza o arquivo temporário QAQQINI com a opção QAQQINI fornecida.
    • 3 = Descarta o arquivo temporário QAQQINI.
  • Nome de opção QAQQINI
  • Valor de opção QAQQINI

O procedimento OVERRIDE_QAQQINI somente aplica as opções de consulta especificadas no trabalho (ou conexão) que invoque os procedimentos armazenados, pois o arquivo de opções de consulta temporário está localizado na biblioteca QTEMP.

Como você deve imaginar a partir dos valores dos três códigos de função suportados pelo procedimento OVERRIDE_QAQQINI, o uso de procedimentos armazenados substituídos é, normalmente, um processo em três etapas. A Listagem 7 mostra um exemplo de como usá-lo.

Listagem 7. Exemplo OVERRIDE_QAQQINI
/* Create, activate, and populate temporary query options file */
CALL qsys2/override_qaqqini(1, '', '');
CALL qsys2/override_qaqqini(2, 'IGNORE_DERIVED_INDEX', '*YES');
	
	
/* Run SQL statement with IGNORE_DERIVED_INDEX option set to *YES */
SELECT ctyname, regnfld FROM mypf WHERE ctyname='ROCHESTER' ;
	
/* Discard temporary query options file */
CALL qsys2/override_qaqqini(3, '', '');

No exemplo acima, queremos que o aplicativo execute somente uma instrução SELECT com a opção IGNORE_DERIVED_INDEX definida como *YES. Antes de executar a solicitação SQL, deve-se fazer duas chamadas para o procedimento OVERRIDE_QAQQINI. A primeira chamada utiliza o código de função 1 para criar e ativar um arquivo de opções QAQQINI temporário. A segunda chamada utiliza o código de função 2 para alterar a opção IGNORE_DERIVED_INDEX para *YES no arquivo de opções temporário. Depois de executar a instrução SELECT, o procedimento OVERRIDE_QAQQINI é chamado uma última vez com o código de função 3 para desativar e remover o arquivo de opções QAQQINI temporário.


Conclusão

Com um bom entendimento da opção IGNORE_DERIVED_INDEX do arquivo de opção de consulta QAQQINI, você agora é capaz de impulsionar a performance de seu aplicativo SQL maximizando o uso do Mecanismo de Consulta SQL — mesmo quando seu banco de dados contiver arquivos lógicos chaveados com derivações!

Recursos

Aprender

Discutir

Comentários

developerWorks: Conecte-se

Los campos obligatorios están marcados con un asterisco (*).


Precisa de um ID IBM?
Esqueceu seu ID IBM?


Esqueceu sua senha?
Alterar sua senha

Ao clicar em Enviar, você concorda com os termos e condições do developerWorks.

 


A primeira vez que você entrar no developerWorks, um perfil é criado para você. Informações no seu perfil (seu nome, país / região, e nome da empresa) é apresentado ao público e vai acompanhar qualquer conteúdo que você postar, a menos que você opte por esconder o nome da empresa. Você pode atualizar sua conta IBM a qualquer momento.

Todas as informações enviadas são seguras.

Elija su nombre para mostrar



Ao se conectar ao developerWorks pela primeira vez, é criado um perfil para você e é necessário selecionar um nome de exibição. O nome de exibição acompanhará o conteúdo que você postar no developerWorks.

Escolha um nome de exibição de 3 - 31 caracteres. Seu nome de exibição deve ser exclusivo na comunidade do developerWorks e não deve ser o seu endereço de email por motivo de privacidade.

Los campos obligatorios están marcados con un asterisco (*).

(Escolha um nome de exibição de 3 - 31 caracteres.)

Ao clicar em Enviar, você concorda com os termos e condições do developerWorks.

 


Todas as informações enviadas são seguras.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=80
Zone=Information Management
ArticleID=471180
ArticleTitle=Maximize o uso de SQE com DB2 criado por DDS para bancos de dados i
publish-date=03152010