Catálogo DB2 — Consultando Metadados com SYSIBM

O que é o catálogo DB2

O catálogo é um banco de dados interno do DB2, acessível via SQL normal. Todas as tabelas ficam no schema SYSIBM e são atualizadas automaticamente pelo DB2 sempre que você cria, altera ou apaga objetos, roda utilitários ou faz BIND de programas.

As principais famílias de tabelas do catálogo:

SYSIBM.SYSTABLES — tabelas e views

SYSTABLES lista todos os objetos tabulares: tabelas reais (T), views (V), aliases (A) e tabelas de trabalho internas. As colunas mais úteis:

Exemplos de consulta:

-- Listar todas as tabelas de um schema
SELECT NAME, TSNAME, CARDF, NPAGES
  FROM SYSIBM.SYSTABLES
 WHERE CREATOR = 'BANCODB2'
   AND TYPE    = 'T'
 ORDER BY NAME;

-- Encontrar tabelas pelo nome (parcial)
SELECT CREATOR, NAME, TSNAME
  FROM SYSIBM.SYSTABLES
 WHERE NAME LIKE 'CONTA%'
   AND TYPE = 'T'
 ORDER BY CREATOR, NAME;

-- Tabelas com estatísticas desatualizadas (CARDF = -1)
SELECT CREATOR, NAME
  FROM SYSIBM.SYSTABLES
 WHERE CARDF = -1
   AND TYPE  = 'T'
 ORDER BY CREATOR, NAME;

SYSIBM.SYSCOLUMNS — colunas e tipos

SYSCOLUMNS descreve cada coluna de cada tabela. É a primeira consulta a fazer quando você herda um programa sem documentação e precisa entender a estrutura de uma tabela.

-- Estrutura completa de uma tabela
SELECT COLNO, NAME, COLTYPE, LENGTH, SCALE, NULLS
  FROM SYSIBM.SYSCOLUMNS
 WHERE TBCREATOR = 'BANCODB2'
   AND TBNAME    = 'CONTA_CORRENTE'
 ORDER BY COLNO;

-- Procurar coluna pelo nome em todas as tabelas
SELECT TBCREATOR, TBNAME, COLNO, COLTYPE, LENGTH
  FROM SYSIBM.SYSCOLUMNS
 WHERE NAME = 'NR_AGENCIA'
 ORDER BY TBCREATOR, TBNAME;

SYSIBM.SYSINDEXES e SYSKEYS — índices

SYSINDEXES lista os índices existentes. SYSKEYS detalha quais colunas compõem cada índice e em que ordem. As duas tabelas se juntam por IXNAME e IXCREATOR.

SYSIBM.SYSINDEXES — colunas principais

SYSIBM.SYSKEYS — colunas do índice

-- Todos os índices de uma tabela com suas colunas
SELECT I.NAME         AS INDICE,
       I.UNIQUERULE,
       I.CLUSTERING,
       I.CLUSTERRATIO,
       K.COLSEQ,
       K.COLNAME,
       K.ORDERING
  FROM SYSIBM.SYSINDEXES I
  JOIN SYSIBM.SYSKEYS    K
    ON K.IXNAME    = I.NAME
   AND K.IXCREATOR = I.CREATOR
 WHERE I.TBCREATOR = 'BANCODB2'
   AND I.TBNAME    = 'CONTA_CORRENTE'
 ORDER BY I.NAME, K.COLSEQ;

-- Índices com cluster ratio baixo (candidatos a REORG)
SELECT TBCREATOR, TBNAME, NAME, CLUSTERRATIO
  FROM SYSIBM.SYSINDEXES
 WHERE CLUSTERING    = 'Y'
   AND CLUSTERRATIO >= 0
   AND CLUSTERRATIO  < 85
 ORDER BY CLUSTERRATIO;

SYSIBM.SYSTABLESPACE — tablespaces

SYSTABLESPACE descreve os tablespaces — os containers físicos onde as tabelas vivem. Cada tablespace pode conter uma ou mais tabelas (dependendo do tipo: simple, segmented ou partitioned).

-- Tablespaces com lock no nível de página (candidatos a ROW)
SELECT DBNAME, NAME, LOCKSIZE, LOCKMAX, PGSIZE
  FROM SYSIBM.SYSTABLESPACE
 WHERE LOCKSIZE <> 'ROW'
 ORDER BY DBNAME, NAME;

-- Tablespaces particionados
SELECT DBNAME, NAME, PARTITIONS, PGSIZE, COMPRESS
  FROM SYSIBM.SYSTABLESPACE
 WHERE PARTITIONS > 0
 ORDER BY DBNAME, NAME;

SYSIBM.SYSPLAN e SYSDBRM — planos e módulos

Quando você executa BIND PLAN, o DB2 registra o plano em SYSPLAN e cada módulo DBRM vinculado em SYSDBRM. Isso é fundamental para rastrear qual versão de um programa está em produção.

SYSIBM.SYSPLAN

SYSIBM.SYSDBRM

-- Planos com BIND recente (últimos 7 dias)
SELECT NAME, CREATOR, BINDDATE, BINDTIME, ISOLATION, OPERATIVE
  FROM SYSIBM.SYSPLAN
 WHERE BINDDATE >= CHAR(CURRENT DATE - 7 DAYS, ISO)
 ORDER BY BINDDATE DESC, BINDTIME DESC;

-- Planos inoperantes (precisam de REBIND)
SELECT NAME, CREATOR, BINDDATE
  FROM SYSIBM.SYSPLAN
 WHERE OPERATIVE = 'N'
 ORDER BY NAME;

-- Ver qual DBRM está dentro de um plano
SELECT D.NAME     AS DBRM,
       D.PLNAME   AS PLANO,
       D.PRECOMP  AS DT_PRECOMP
  FROM SYSIBM.SYSDBRM D
 WHERE D.PLNAME = 'PGMCONTA'
 ORDER BY D.NAME;

SYSIBM.SYSPACKAGE — pacotes

Ambientes modernos usam BIND PACKAGE em vez de BIND PLAN direto. Cada programa COBOL gera um pacote independente, e um plano lista esses pacotes. Isso permite rebind de um único programa sem recompilar o plano inteiro.

-- Todos os pacotes de uma collection
SELECT COLLID, NAME, VERSION, BINDDATE, ISOLATION, OPERATIVE
  FROM SYSIBM.SYSPACKAGE
 WHERE COLLID = 'BANCODB2'
 ORDER BY NAME;

-- Pacotes inoperantes na collection
SELECT COLLID, NAME, BINDDATE
  FROM SYSIBM.SYSPACKAGE
 WHERE COLLID    = 'BANCODB2'
   AND OPERATIVE = 'N'
 ORDER BY NAME;

-- Verificar o que está dentro de um pacote (statements SQL)
SELECT SEQNO, STMTNO, SECTNO,
       SUBSTR(STMT, 1, 80) AS SQL_INICIO
  FROM SYSIBM.SYSPACKSTMT
 WHERE COLLID = 'BANCODB2'
   AND NAME   = 'PGMCONTA'
 ORDER BY SEQNO;

SYSIBM.SYSCOPY — histórico de backup

SYSCOPY registra toda operação de COPY, REORG, RECOVER e LOAD executada em tablespaces e índices. É a tabela que o DB2 consulta internamente para saber qual backup usar durante um RECOVER.

-- Último COPY de cada tablespace de um database
SELECT TSNAME,
       MAX(TIMESTAMP) AS ULTIMO_COPY,
       MAX(ICTYPE)    AS TIPO
  FROM SYSIBM.SYSCOPY
 WHERE DBNAME  = 'BANCODB2'
   AND ICTYPE  IN ('F', 'I')
   AND STYPE   = 'S'
 GROUP BY TSNAME
 ORDER BY ULTIMO_COPY DESC;

-- Histórico completo de um tablespace
SELECT ICTYPE, TIMESTAMP, DSNAME, SHRLEVEL
  FROM SYSIBM.SYSCOPY
 WHERE DBNAME = 'BANCODB2'
   AND TSNAME = 'TSCONTA'
 ORDER BY TIMESTAMP DESC
 FETCH FIRST 20 ROWS ONLY;

-- Tablespaces SEM COPY nos últimos 30 dias (risco de DR)
SELECT T.DBNAME, T.NAME AS TSNAME
  FROM SYSIBM.SYSTABLESPACE T
 WHERE T.DBNAME = 'BANCODB2'
   AND NOT EXISTS (
       SELECT 1
         FROM SYSIBM.SYSCOPY C
        WHERE C.DBNAME    = T.DBNAME
          AND C.TSNAME    = T.NAME
          AND C.ICTYPE    = 'F'
          AND C.TIMESTAMP >= CHAR(CURRENT TIMESTAMP - 30 DAYS, ISO)
   )
 ORDER BY T.NAME;

Consultas práticas do dia a dia

As consultas abaixo são usadas regularmente em ambientes de produção para diagnóstico e auditoria.

Quais tabelas existem no tablespace X?

SELECT CREATOR, NAME, CARDF, NPAGES
  FROM SYSIBM.SYSTABLES
 WHERE DBNAME = 'BANCODB2'
   AND TSNAME = 'TSCONTA'
 ORDER BY NAME;

Qual tablespace e database contêm a tabela X?

SELECT CREATOR, NAME, DBNAME, TSNAME
  FROM SYSIBM.SYSTABLES
 WHERE NAME = 'CONTA_CORRENTE';

Quais índices cobrem a coluna X?

SELECT K.IXNAME, K.COLSEQ, K.ORDERING,
       I.UNIQUERULE, I.CLUSTERING
  FROM SYSIBM.SYSKEYS    K
  JOIN SYSIBM.SYSINDEXES I
    ON I.NAME    = K.IXNAME
   AND I.CREATOR = K.IXCREATOR
 WHERE I.TBCREATOR = 'BANCODB2'
   AND I.TBNAME    = 'CONTA_CORRENTE'
   AND K.COLNAME   = 'NR_AGENCIA'
 ORDER BY K.IXNAME, K.COLSEQ;

Quando foi o último BIND do programa X?

SELECT NAME, COLLID, BINDDATE, BINDTIME, OPERATIVE
  FROM SYSIBM.SYSPACKAGE
 WHERE NAME = 'PGMCONTA'
 ORDER BY BINDDATE DESC, BINDTIME DESC
 FETCH FIRST 1 ROW ONLY;

Quantas linhas cada tabela tem (resumo de cardinalidade)?

SELECT CREATOR, NAME,
       DECIMAL(CARDF, 15, 0) AS LINHAS_ESTIMADAS
  FROM SYSIBM.SYSTABLES
 WHERE CREATOR = 'BANCODB2'
   AND TYPE    = 'T'
   AND CARDF  >= 0
 ORDER BY CARDF DESC;

Verificar autorizações em uma tabela

SELECT GRANTEE, GRANTOR, SELECTAUTH, INSERTAUTH,
       UPDATEAUTH, DELETEAUTH, ALTERAUTH
  FROM SYSIBM.SYSTABLEAUTH
 WHERE TCREATOR = 'BANCODB2'
   AND TTNAME   = 'CONTA_CORRENTE'
 ORDER BY GRANTEE;

Acessando o catálogo em programas COBOL

Você pode acessar o catálogo em qualquer programa COBOL com DB2 embutido, da mesma forma que acessa qualquer tabela. A diferença é que as tabelas SYSIBM não têm DCLGEN — você declara as variáveis host manualmente ou usa DCLGEN explícito.

Exemplo: verificar se uma tabela existe antes de processar

       WORKING-STORAGE SECTION.

       01  WS-NOME-TABELA         PIC X(18).
       01  WS-CREATOR             PIC X(8).
       01  WS-QTD                 PIC S9(9) COMP.

           EXEC SQL
               SELECT COUNT(*)
                 INTO :WS-QTD
                 FROM SYSIBM.SYSTABLES
                WHERE CREATOR = :WS-CREATOR
                  AND NAME    = :WS-NOME-TABELA
                  AND TYPE    = 'T'
           END-EXEC

           EVALUATE SQLCODE
               WHEN 0
                   IF WS-QTD = 0
                       DISPLAY 'TABELA NAO ENCONTRADA'
                   ELSE
                       PERFORM PROCESSAR-DADOS
                   END-IF
               WHEN OTHER
                   DISPLAY 'ERRO AO CONSULTAR CATALOGO: '
                           SQLCODE
           END-EVALUATE

Exemplo: listar colunas de uma tabela em runtime

       WORKING-STORAGE SECTION.

       01  WS-COLNO               PIC S9(4) COMP.
       01  WS-COLNAME             PIC X(18).
       01  WS-COLTYPE             PIC X(8).
       01  WS-LENGTH              PIC S9(4) COMP.
       01  WS-NULLS               PIC X(1).

       PROCEDURE DIVISION.

           EXEC SQL
               DECLARE CSR-COLUNAS CURSOR FOR
               SELECT COLNO, NAME, COLTYPE, LENGTH, NULLS
                 FROM SYSIBM.SYSCOLUMNS
                WHERE TBCREATOR = :WS-CREATOR
                  AND TBNAME    = :WS-NOME-TABELA
                ORDER BY COLNO
           END-EXEC

           EXEC SQL OPEN CSR-COLUNAS END-EXEC

           PERFORM UNTIL SQLCODE <> 0
               EXEC SQL
                   FETCH CSR-COLUNAS
                     INTO :WS-COLNO, :WS-COLNAME,
                          :WS-COLTYPE, :WS-LENGTH, :WS-NULLS
               END-EXEC
               IF SQLCODE = 0
                   DISPLAY WS-COLNO
                           ' '
                           WS-COLNAME
                           ' '
                           WS-COLTYPE
               END-IF
           END-PERFORM

           EXEC SQL CLOSE CSR-COLUNAS END-EXEC