SYSTABLES, SYSCOLUMNS y SYSINDEXES

Muchas veces queremos conocer la información relativa a la base de datos de una aplicación o instalación, sobre todo cuando nos enfrentamos con ella por primera vez.

Uno de los métodos más fiables y rápidos es utilizar las tablas donde DB2 guarda dicha información. Estas tablas forman parte del "table space" SYSDBASE.

A continuación mencionamos las más comunes y de mayor utilidad. Los ejemplos los haremos sobre el propio SYSDBASE por ser común en todas las instalaciones, pero dejamos como sugerencia para familiarizarnos con las mismas modificar dichas sentencias a gusto de consumidor.

SYSTABLES

Contiene información de cada tabla, vista o alias de la BD.

Algunos campos relevantes de la tabla son los siguientes:

NAME : nombre de la tabla, vista o alias
CREATOR : esquema de la tabla, vista o alias
TYPE : tipo de objeto (A=alias, T=tabla, V=vista...)
DBNAME : base de datos que contiene el "table space" de la tabla o vista
TSNAME : "table space" que contiene la tabla o vista
COLCOUNT : número de columnas de la tabla o vista
REMARKS : información adicional sobre el objeto

Gracias a esta tabla podríamos realizar una consulta a la BD para obtener información de las tablas que forman parte de una aplicación, las vistas que existen en la instalación... En este caso consultaremos las tablas que forman parte del "table space" SYSDBASE:

SELECT SUBSTR(NAME, 1, 15) AS NAME ,      
       SUBSTR(CREATOR, 1, 10) AS CREATOR ,
       SUBSTR(DBNAME, 1, 10) AS DBNAME ,  
       COLCOUNT ,                         
       REMARKS                            
  FROM SYSIBM.SYSTABLES                   
WHERE  CREATOR = 'SYSIBM'                 
AND    TYPE    = 'T'                      
AND    TSNAME  = 'SYSDBASE';              

El resultado de la consulta son el nombre de las tablas y el número de columnas que las forman:

---------+---------+---------+---------+---------+---------+-----
NAME             CREATOR     DBNAME      COLCOUNT  REMARKS       
---------+---------+---------+---------+---------+---------+-----
SYSFIELDS        SYSIBM      DSNDB06           13                
SYSTABLESPACE    SYSIBM      DSNDB06           41                
SYSTABLES        SYSIBM      DSNDB06           52                
SYSTABLEPART     SYSIBM      DSNDB06           41                
SYSTABAUTH       SYSIBM      DSNDB06           29                
SYSSYNONYMS      SYSIBM      DSNDB06            7                
SYSRELS          SYSIBM      DSNDB06           15                
SYSKEYS          SYSIBM      DSNDB06            7                
SYSINDEXPART     SYSIBM      DSNDB06           35                
SYSINDEXES       SYSIBM      DSNDB06           46                
SYSFOREIGNKEYS   SYSIBM      DSNDB06            7                
SYSCOLUMNS       SYSIBM      DSNDB06           35                
SYSCOLAUTH       SYSIBM      DSNDB06           15                

SYSCOLUMNS

Contiene información de cada columna de cada tabla y vista de la BD.

Algunos campos relevantes de la tabla son los siguientes:

NAME : nombre de columna
TBNAME : nombre de la tabla o vista que contiene la columna
TBCREATOR : esquema de la tabla o vista que contiene la columna
COLTYPE : tipo de la columna (INTEGER, FLOAT, CHAR...)
LENGTH : longitud del atributo de la columna, en caso de un decimal sería la precisión
SCALE : escala de un decimal
NULLS : indica si la columna puede contener valores nulos (Y=si, N=no)
REMARKS : información adicional sobre la columna

En este ejemplo mostraremos la forma de obtener información relativa a todos los campos de la tabla SYSCOLUMNS:

SELECT SUBSTR(TBNAME, 1, 10) AS TBNAME ,
       SUBSTR(NAME, 1, 10) AS NAME ,
       COLTYPE ,
       LENGTH ,
       SCALE ,
       NULLS ,
       REMARKS
  FROM SYSIBM.SYSCOLUMNS
 WHERE TBNAME = 'SYSTABLES';

Como resultado obtenemos todos los campos con sus datos de longitud, comentarios, etc...:

---------+---------+---------+---------+---------+---------+----
TBNAME      NAME        COLTYPE   LENGTH   SCALE  NULLS  REMARKS
---------+---------+---------+---------+---------+---------+----
SYSCOLUMNS  COLCARDF    FLOAT          8       0  N   
SYSCOLUMNS  HIGH2KEY    VARCHAR     2000       0  N   
SYSCOLUMNS  LOW2KEY     VARCHAR     2000       0  N   
SYSCOLUMNS  NAME        VARCHAR      128       0  N   
SYSCOLUMNS  STATSTIME   TIMESTMP      10       0  N   
SYSCOLUMNS  TBCREATOR   VARCHAR      128       0  N   
SYSCOLUMNS  TBNAME      VARCHAR      128       0  N   
SYSCOLUMNS  VERSION     SMALLINT       2       0  N  
SYSCOLUMNS  COLNO       SMALLINT       2       0  N 
SYSCOLUMNS  COLTYPE     CHAR           8       0  N 
SYSCOLUMNS  DEFAULT     CHAR           1       0  N 
SYSCOLUMNS  DEFAULTVAL  VARCHAR     1536       0  N 
SYSCOLUMNS  FOREIGNKEY  CHAR           1       0  N 
SYSCOLUMNS  KEYSEQ      SMALLINT       2       0  N 
SYSCOLUMNS  LENGTH      SMALLINT       2       0  N 
SYSCOLUMNS  NAME        VARCHAR      128       0  N 
SYSCOLUMNS  NULLS       CHAR           1       0  N 
...

SYSINDEXES

Contiene información de cada índice de cada tabla y vista de la BD.

Algunos campos relevantes de la tabla son los siguientes:

NAME : nombre del índice
CREATOR : esquema de la tabla o vista del índice
TBNAME : nombre de la tabla o vista del índice
TBCREATOR : esquema de la tabla o vista del índice
UNIQUERULE : indica si el índice es único (D=se permiten duplicados, U=Es único, P=Único e índice primario...)
CLUSTERING : si ha sido especificado "cluster" para el índice (Y=si, N=no)

En este ejemplo uniremos las tablas SYSINDEXES y SYSKEYS para obtener toda la información que nos interesa, ya que SYSKEYS tiene información de los campos que contienen los índices definidos en SYSINDEXES.

SELECT SUBSTR(TBNAME, 1, 10) AS TBNAME,
       SUBSTR(NAME, 1, 15) AS NAME,
       SUBSTR(COLNAME, 1, 10) AS COLNAME,
       UNIQUERULE ,
       CLUSTERING
  FROM SYSIBM.SYSINDEXES A , SYSIBM.SYSKEYS B
 WHERE A.NAME = B.IXNAME
   AND A.TBNAME = 'SYSTABLES';


El resultado son todos los índices definidos sobre la tabla SYSTABLES y los campos que los forman:

---------+---------+---------+---------+---------+---------+---
TBNAME      NAME             COLNAME     UNIQUERULE  CLUSTERING
---------+---------+---------+---------+---------+---------+---
SYSTABLES   SYSTABLES_IX     CREATOR     D           Y
SYSTABLES   SYSTABLES_IX     NAME        D           Y
SYSTABLES   SYSTABLES_IX     STATSTIME   D           Y
SYSTABLES   SYSTABLES_IX2    DBNAME      D           N
SYSTABLES   SYSTABLES_IX2    STATSTIME   D           N
SYSTABLES   SYSTABLES_IX2    TSNAME      D           N
SYSTABLES   DSNDTX01         CREATOR     P           N
SYSTABLES   DSNDTX01         NAME        P           N
SYSTABLES   DSNDTX02         CREATOR     U           N
SYSTABLES   DSNDTX02         DBID        U           N
SYSTABLES   DSNDTX02         NAME        U           N
SYSTABLES   DSNDTX02         OBID        U           N
SYSTABLES   DSNDTX03         TBCREATOR   D           N
SYSTABLES   DSNDTX03         TBNAME      D           N
...

Una posible aplicación interesante de dichas consultas sería extraer la información de la BD a ficheros para ser importada (tras formateo previo) a herramientas CASE. De esta forma, tendríamos modelos entidad-relación completamente actualizados de forma automática. Lo dejamos como sugerencia de un futuro post.