Este artigo explica como coletar um SQL Performance Monitor e como entender quantas consultas SQL estão usando o SQE e por que outras instruções SQL continuam a usar o CQE. O SQE é um mecanismo de consulta estratégico no DB2 para i, com melhor desempenho, escalabilidade e conjuntos de ferramentas como o SQL Plan Cache.

Scott L. Forstie, Senior Software Engineer, IBM

Photo of Scott ForstieScott Forstie é um engenheiro de software senior na IBM, assim como o chefe de desenvolvimento SQL para DB2 para IBM i em Rochester, Minnesota. Antes de trabalhar com o DB2, ele trabalhou com a ativação de UNIX(R) para os sistemas AS/400(R) e S/390(R).



11/Dez/2009

Em todos os releases do sistema operacional desde V5R2, o DB2 para IBM i tem executado a transição de mais consultas do Classic Query Engine (CQE) para o SQL Query Engine (SQE). Para o IBM i 6.1, o tema do aprimoramento SQE segue adiante com a adição do seguinte suporte de SQE:

  • Suporte de National Language Sort Sequence (NLSS)
  • Suporte de tradução CCSID, incluindo funções UPPER e LOWER
  • aprimoramentos de CASE
  • Suporte de User Defined Table Function (UDTF)t
  • Correlação Lateral

Este artigo explica como coletar um SQL Performance Monitor e como entender quantas consultas SQL estão usando o SQE e por que outras instruções SQL continuam a usar o CQE. O SQE é um mecanismo de consulta estratégico no DB2 para i, com melhor desempenho, escalabilidade e conjuntos de ferramentas como o SQL Plan Cache.

Coletando os dados

Os SQL Performance Monitors podem ser coletados e analisados a partir do System i Navigator. Uma abordagem alternativa é usar os comandos Start Database Monitor (STRDBMON) e End Database Monitor (ENDDBMON) para coletar os dados e produzir as suas próprias consultas para analisar o arquivo resultante de saída do monitor. Este artigo concentra-se na abordagem do System i Navigator para a coleta e a análise de dados. Em ambas as abordagens, a autoridade especial do usuário *JOBCTL é necessária para coletar os dados, pois o exemplo coleta detalhes de monitor por todas as tarefas. Se a tarefa-alvo do SQL Performance Monitor for um uma tarefa específica e essa tarefa foi iniciada pelo usuário atual, a autoridade especial do usuário *JOBCTL não é exigida.

A partir do System i Navigator, expanda a seção de bancos de dados para a máquina, clique com o botão direito do mouse em SQL Performance Monitors e selecione New.

System i Navigator

Atribua um nome ao novo monitor e selecione um esquema (biblioteca) para os dados.

SQL Performance Monitor Wizard

Escolha entre os vários pré-filtros do SQL Performance Monitor. Os pré-filtros limitam a quantidade de dados coletados, o que é uma consideração importante para o desempenho e o tamanho da saída do monitor. Por exemplo, eu defini a estimativa mínima de tempo de execução de consultas como 0, pois queria evitar a coleta de detalhes sobre instruções SQL que não fossem de consultas.

SQL Performance Monitor Wizard

Para ajudar o meu exemplo, eu monitorei todas as tarefas (existentes e futuras) no sistema inteiro.

SQL Performance Monitor Wizard

A tela de confirmação indica que estamos prontos para ativar o monitor.

SQL Performance Monitor Wizard

O novo monitor é adicionado automaticamente à lista dos SQL Performance Monitors. Quando o estado do monitor é iniciado, são coletadas as instruções SQL que correspondem aos critérios do pré-filtro para as tarefas monitoradas. Um clique com o botão direito do mouse mostra as opções disponíveis ao usuário. O monitor pode então ser analisado enquanto estiver sendo executado ou após ter sido concluído (desligado).


Usuário, cuidado

Um monitor público como o deste exemplo pode coletar uma grande quantidade de dados em uma máquina com muita atividade. Tome cuidado ao decidir o tempo e a duração do SQL Performance Monitor.

System i Navigator

Quando a opção Analyze for escolhida, você verá uma tela de visão geral. A visão geral contém informações úteis resumidas do monitor, incluindo um indicador do número de consultas que usaram SQE e CQE. Uma das abordagens de análise consiste em selecionar a opção Summary para consultas de CQE. Isso ajudará você a entender o motivo dessas consultas usarem o CQE.

Query Engine Use

A visualização padrão mostra todas as colunas de monitor. Minha sugestão é usar a opção Columns no menu suspenso View para limitar e ordenar as colunas. Para monitores maiores, essa técnica melhorará o desempenho do System i Navigator. Para qualquer monitor, essa técnica tornará mais fácil a análise dos dados.

Query Engine Use

Para este exercício, eu escolhi Operation para ver o comando ou o nome da instrução SQL, ao lado do Motivo do CQE. A contagem de uso (usage Count) e o tempo médio de execução (average runtime) fornecerão o contexto necessário para entender o impacto das consultas no desempenho.

Select Columns

Os dados do exemplo mostram como algumas consultas estão usando uma API de interfaces não-SQL (comando RUNQRY, comando OPNQRYF e o QQQQRY(), enquanto a outra instância de uso do CQE está ligada a um INSERT com instrução VALUES. O INSERT com VALUES é um caso especial, pois ele não seria beneficiado pela execução através do SQE, porque a instrução carece de critérios de seleção, agrupamento e ordenação.

Query Engine Use

Motivos para o uso do CQE

As informações da visão geral ajudam a determinar o quão importante é a questão do uso do CQE versus SQE para o seu ambiente ou aplicativo. Uma vez que você tenha decidido que é justificada a análise do uso do CQE, os diferentes motivos para o uso do CQE irão ditar se alguma ação pode ser tomada para deslocar mais trabalhos de mecanismos de consulta para o SQE.

A Tabela 1 relaciona os motivos restantes para o uso do CQE no IBM i 6.1. A coluna central mostra os valores que seriam observados durante a análise do SQL Performance Monitor, e a última coluna define os valores que aparecem dentro da coluna QVC43 do arquivo de saída do monitor. O valor QVC43 só seria interessante para aqueles que trabalham diretamente com o arquivo de saída do monitor, ao invés de usar o recurso de análise do System i Navigator.

Tabela 1.
Motivo do CQEValor do SQL Performance MonitorValor da coluna QVC43 (quando QQRID=3014)
Inserção simplesINSERT_VALUES_OR_INSERT_TARGET'IO'
Tabelas demais na consultaGREATER_THAN_256_TABLES'NF'
Não é uma consulta SQL ou a consulta não foi executada através de uma interface SQLNON_SQL_INTERFACE'NS'
Havia uma tabela distribuída na consultaDISTRIBUTED_TABLE'DF'
Um acionador de leitura estava definido na tabela consultadaREAD_TRIGGER'RT'
Havia um arquivo descrito por programa na consultaIDDU_LINKED_DEFINITION'PD'
WHERE CURRENT OF foi usada na tabela particionadaCOMPLEX_UPDATE_WHERE_CURRENT_ON_PARTITIONED_TABLE'WC'
WHERE CURRENT OF foi usada na tabela particionadaCOMPLEX_UPDATE_WHERE_CURRENT_ON_PARTITIONED_TABLE'WC'
Um arquivo lógico DDS foi especificado na definição da consultaDDS_LOGICAL_FILE'LF'
Um arquivo lógico DDS com uma chave derivada ou critério select/omit foi encontrado sobre uma tabela consultadaDERIVED_KEY_OR_SELECT_OMIT_LOGICAL_FILE'DK'

Um caso especial

O último motivo nessa tabela é especial porque há um controle operacional para influenciar se o SQE é usado ou não. Antes do IBM i 6.1, quando um arquivo lógico contendo uma chave derivada ou critério select/omit existia sobre uma tabela, o comportamento padrão seria o de usar o CQE. No 6.1, o comportamento padrão foi modificado para usar SQE. Há uma opção QAQQINI (Query options file) que controla a escolha. O nome da opção QAQQINI é IGNORE_DERIVED_INDEX. Antes do 6.1, o padrão para IGNORE_DERIVED_INDEX era *NO. No IBM i 6.1, o padrão foi modificado para *YES.

Através da mudança desse valor, o otimizador do SQE receberá permissão para ignorar arquivos lógicos chaveados que foram definidos com o critério select/omit ou sem a palavra-chave Dynamic Select (DYNSLT). O plano de consulta resultante será criado sem qualquer consideração à existência de arquivos lógicos derivados.

Use qualquer uma das três instruções SQL seguintes para ativar um QAQQINI para permitir que o SQE seja levado em conta para o uso quando índices de chaves derivadas existirem sobre uma tabela.

1) UPDATE <yourlib>.QAQQINI  SET QQVAL = '*YES' WHERE  QQPARM = 'IGNORE_DERIVED_INDEX'
2) UPDATE <yourlib>.QAQQINI  SET QQVAL = '*DEFAULT' WHERE  QQPARM = 'IGNORE_DERIVED_INDEX'
3) DELETE FROM <yourlib>.QAQQINI WHERE QQPARM = 'IGNORE_DERIVED_INDEX'

Uma alternativa à mudança do arquivo QAQQINI seria usar o procedimento QSYS2.OVERRIDE_QAQQINI(). Essa abordagem processual ao gerenciamento do QAQQINI é benéfico àqueles aplicativos ou instalações em que há uma necessidade do uso temporário de um valor de parâmetro QAQQINI.

Etapa 1:
-- Establish QAQQINI override 
(QTEMP.QAQQINI is created based upon the current QAQQINI being used in the job)
--
call qsys2.override_qaqqini(1, '', '');
Etapa 2:
-- Override the existing 'IGNORE_DERIVED_INDEX' value with *YES
--
call qsys2.override_qaqqini(2, 'IGNORE_DERIVED_INDEX', '*YES');

A partir deste ponto, o SQE será usado para esse caso até que a substituição tenha sido descartada (etapa 3) ou a conexão termine.

Etapa 3:
-- Discard override values and resume using the previous QAQQINI file
--
--
call qsys2.override_qaqqini(3, '', '');

Como a contagem acontece

Um aprimoramento recente ao DB2 para i 6.1 faz com que seja possível entender o uso do otimizador no nível da instrução SQL dentro do SQL Performance Monitor. O cenário de exemplo acima depende desse aprimoramento. Para aqueles que preferem trabalhar diretamente com o arquivo de saída do monitor do banco de dados, os detalhes desta seção serão necessários.

A coluna QQSMINT5 dentro do registro 1000 (QQRID=1000) representa o uso do otimizador. Há cinco valores possíveis que podem aparecer nessa coluna.

  • null = O arquivo do monitor do banco de dados antecede o aprimoramento
  • 0 = Não se aplica a esta instrução porque nenhum dos dois mecanismos de consulta foi necessário ou usado
  • 1 = O SQE (SQL Query Engine) foi usado
  • 2 = O CQE (Classic Query Engine) foi usado
  • 3 = O CQE direct foi usado (instruções como INSERT WITH VALUES)

O valor QQSMINT5 é correto para as tarefas ativas, até aquelas que estão reutilizando Open Data Paths (ODPs) e cursores SQL. Para entender os motivos do uso do CQE, será necessário examinar o registro 3014 associado. O registro 3014 é gerado durante a abertura completa do cursor SQL, o que explica por que o motivo do CQE poderia ser obscurecido pelas instruções SQL em modo de reutilização.

As seguintes PTFs V6R1 devem ser carregadas para que se possa usar essa melhoria ao campo QQSMINT5.

  • SI33874
  • SI33875
  • SI33876
  • SI34001
  • SI34002

Após a mudança para o IBM i 6.1, faça a avaliação do seu uso do CQE e do SQE. O DB2 para investimento em i é focado firmemente por trás do SQE, e deve ser possível observar o uso expandido do SQE no 6.1. Algumas instalações podem possuir opções de arquivos de consultas QAQQIN configurados com IGNORE_DERIVED_INDEX com '*NO' Utilize os aprimoramentos e conjuntos de ferramentas descritos neste artigo para entender a extensão da atividade do CQE e pensar na possibilidade de remover qualquer impedimento para o uso do SQE.

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=455951
ArticleTitle=Avalie o uso do SQE no DB2 para i 6.1
publish-date=12112009