Extraction Queries for IBM Db2 Database

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

Core Connector Core connectors are included with all Alation platform tiers (subject to each tier’s connector limits) and are fully supported by Alation.

Schema

Ensure your query has a column labelled as CATALOG in the SELECT list.

SELECT DISTINCT
    TRIM(SCHEMANAME) AS SCHEMA,
    '' AS CATALOG
FROM syscat.schemata
WHERE SCHEMANAME  NOT IN ('''')
    AND SCHEMANAME NOT IN ( 'SYSIBMTS' , 'SYSCAT' , 'SYSIBMADM' , 'SYSIBM' ,
        'SYSPROC' , 'SYSPUBLIC' , 'NULLID' , 'SQLJ' , 'SYSIBMINTERNAL' ,
        'SYSSTAT' , 'SYSFUN' , 'SYSTOOLS');

Table

Ensure your query has columns labelled as CATALOG, TABLE_NAME, TABLE_TYPE, REMARKS in the SELECT list.

SELECT
    '' AS CATALOG,
    TRIM(TABSCHEMA) AS SCHEMA,
    TABNAME AS TABLE_NAME,
    TYPE AS TABLE_TYPE,
    REMARKS AS REMARKS,
    ALTER_TIME AS ALTER_TIME,
    CREATE_TIME AS CREATE_TIME,
    OWNER AS OWNER
FROM
    SYSCAT.TABLES
WHERE TABSCHEMA  NOT IN ('''')
    AND TABSCHEMA NOT IN  ( 'SYSIBMTS' , 'SYSCAT' , 'SYSIBMADM' , 'SYSIBM' ,
        'SYSPROC' , 'SYSPUBLIC' , 'NULLID' , 'SQLJ' , 'SYSIBMINTERNAL' ,
        'SYSSTAT' , 'SYSFUN' , 'SYSTOOLS')
    AND TYPE IN ('T');

View

Ensure your query has columns labelled as CATALOG, VIEW_NAME, VIEW_CREATE_STATEMENT, 'VIEW' AS VIEW_TYPE, REMARKS in the SELECT list.

SELECT
    '' AS CATALOG,
    TRIM(VIEWSCHEMA) AS SCHEMA,
    VIEWNAME AS VIEW_NAME,
    'VIEW' AS VIEW_TYPE,
    '' AS  REMARKS,
    TEXT AS VIEW_CREATE_STATEMENT
FROM
    SYSCAT.VIEWS
WHERE VIEWSCHEMA  NOT IN ('''')
    AND VIEWSCHEMA NOT IN  ( 'SYSIBMTS' , 'SYSCAT' , 'SYSIBMADM' , 'SYSIBM' ,
    'SYSPROC' , 'SYSPUBLIC' , 'NULLID' , 'SQLJ' , 'SYSIBMINTERNAL' ,
    'SYSSTAT' , 'SYSFUN' , 'SYSTOOLS');

Column

Ensure your query has columns labelled as CATALOG, TABLE_NAME, TYPE_NAME, DATA_TYPE, COLUMN_NAME, ORDINAL_POSITION, IS_NULLABLE, REMARKS, COLUMN_DEFAULT in the SELECT list.

SELECT
  '' AS CATALOG,
  TRIM(c.TABSCHEMA) AS SCHEMA,
  c.TABNAME AS TABLE_NAME,
  c.COLNAME AS COLUMN_NAME,
  CASE
      WHEN c.TYPENAME = 'VARCHAR' THEN 'VARCHAR('||c.STRINGUNITSLENGTH||')'
      WHEN c.TYPENAME = 'DECIMAL' THEN 'DECIMAL('||c.LENGTH||')'
      WHEN c.TYPENAME = 'REAL' THEN 'REAL('||c.LENGTH||')'
      WHEN c.TYPENAME = 'CLOB' THEN 'CLOB('||c.STRINGUNITSLENGTH||')'
      WHEN c.TYPENAME = 'DBCLOB' THEN 'DBCLOB('||c.STRINGUNITSLENGTH||')'
      WHEN c.TYPENAME = 'CHARACTER' THEN 'CHARACTER('||c.STRINGUNITSLENGTH||')'
      WHEN c.TYPENAME = 'BLOB' THEN 'BLOB('||c.LENGTH||')'
      WHEN c.TYPENAME = 'GRAPHIC' THEN 'GRAPHIC('||c.STRINGUNITSLENGTH||')'
      WHEN c.TYPENAME = 'VARGRAPHIC' THEN 'VARGRAPHIC('||c.STRINGUNITSLENGTH||')'
      ELSE c.TYPENAME
  END AS TYPE_NAME,
      c.TYPENAME AS DATA_TYPE,
      c.COLNO AS ORDINAL_POSITION,
      nulls AS IS_NULLABLE,
      c.DEFAULT AS COLUMN_DEF,
      c.REMARKS AS REMARKS,
      CASE
          WHEN c.TYPESTRINGUNITS IS NOT NULL AND c.TYPENAME IN ('CHARACTER','VARCHAR','CLOB', 'DBCLOB','GRAPHIC','VARGRAPHIC')
          THEN c.STRINGUNITSLENGTH ELSE c.LENGTH
      END AS MAX_LENGTH,
      null AS COLUMN_DEFAULT,
      c.SCALE AS NUMERIC_SCALE
FROM SYSCAT.COLUMNS AS c,
  SYSCAT.TABLES AS t
WHERE c.TABSCHEMA = t.TABSCHEMA
  AND c.TABNAME = t.TABNAME
  AND c.TABSCHEMA  NOT IN ('''')
  AND c.TABSCHEMA NOT IN  ( 'SYSIBMTS' , 'SYSCAT' , 'SYSIBMADM' ,
        'SYSIBM' , 'SYSPROC' , 'SYSPUBLIC' , 'NULLID' , 'SQLJ' ,
        'SYSIBMINTERNAL' , 'SYSSTAT' , 'SYSFUN' , 'SYSTOOLS' );

Primary Key

Ensure your query has columns labelled as CATALOG, TABLE_NAME, COLUMN_NAME in the SELECT list.

SELECT
    '' AS CATALOG,
    TRIM(COL.TBCREATOR) AS SCHEMA,
    COL.TBNAME AS TABLE_NAME,
    INDEX.NAME AS INDEX_NAME,
    KEYSEQ,
    COL.NAME AS COLUMN_NAME
FROM
    SYSIBM.SYSCOLUMNS AS COL
    INNER JOIN SYSIBM.SYSINDEXES AS INDEX
        ON COL.TBNAME = INDEX.TBNAME
WHERE COL.KEYSEQ > 0
    AND COL.TBCREATOR  NOT IN ('''')
    AND COL.TBCREATOR NOT IN  ( 'SYSIBMTS' , 'SYSCAT' , 'SYSIBMADM' ,
        'SYSIBM' , 'SYSPROC' , 'SYSPUBLIC' , 'NULLID' , 'SQLJ' ,
        'SYSIBMINTERNAL' , 'SYSSTAT' , 'SYSFUN' , 'SYSTOOLS');

Foreign Key

Ensure your query has columns labelled as PK_CATALOG, PK_SCHEMA, PK_TABLE, PK_COLUMN, FK_CATALOG, FK_SCHEMA, FK_TABLE, FK_COLUMN in the SELECT list.

SELECT
    '' AS FK_CATALOG,
    TRIM(FK.TABSCHEMA) AS FK_SCHEMA,
    FK.TABNAME AS FK_TABLE,
    FK.CONSTNAME AS FK_NAME,
    FKCOL.COLNAME AS FK_COLUMN,
    TRIM(FK.REFTABSCHEMA) AS PK_SCHEMA,
    FK.REFTABNAME AS PK_TABLE,
    FK.REFKEYNAME AS PK_NAME,
    PKCOL.COLNAME AS PK_COLUMN,
    null AS PK_CATALOG
FROM
    SYSCAT.REFERENCES FK
    INNER JOIN SYSCAT.KEYCOLUSE FKCOL
        ON FK.TABSCHEMA = FKCOL.TABSCHEMA
        AND FK.TABNAME = FKCOL.TABNAME
        AND FK.CONSTNAME = FKCOL.CONSTNAME
    INNER JOIN SYSCAT.KEYCOLUSE PKCOL
        ON FK.REFTABSCHEMA = PKCOL.TABSCHEMA
        AND FK.REFTABNAME = PKCOL.TABNAME
        AND FK.REFKEYNAME = PKCOL.CONSTNAME
        AND FKCOL.COLSEQ = PKCOL.COLSEQ
WHERE FK.TABSCHEMA  NOT IN ('''')
    AND FK.TABSCHEMA NOT IN  ( 'SYSIBMTS' , 'SYSCAT' , 'SYSIBMADM' ,
      'SYSIBM' , 'SYSPROC' , 'SYSPUBLIC' , 'NULLID' , 'SQLJ' ,
      'SYSIBMINTERNAL' , 'SYSSTAT' , 'SYSFUN' , 'SYSTOOLS');

Function

Ensure your query has columns labelled as CATALOG, FUNCTION_NAME, REMARKS in the SELECT list.

SELECT
    DISTINCT '' AS CATALOG,
    SPECIFICNAME AS FUNCTION_NAME,
    TRIM(ROUTINESCHEMA) AS SCHEMA,
    NULL AS REMARKS,
    NULL AS FUNCTION_DEFINITION
FROM
    SYSIBM.SYSROUTINEPARMS
WHERE ROUTINESCHEMA NOT IN ('''')
    AND ROUTINESCHEMA NOT IN ( 'SYSIBMTS' , 'SYSCAT' , 'SYSIBMADM' ,
        'SYSIBM' , 'SYSPROC' , 'SYSPUBLIC' , 'NULLID' , 'SQLJ' ,
        'SYSIBMINTERNAL' , 'SYSSTAT' , 'SYSFUN' , 'SYSTOOLS');

Function Definition

Please ensure your query has columns labelled as CATALOG, FUNCTION_NAME, ARG_NAME, TYPE_NAME, ARG_TYPE, ARG_DEF in the SELECT list.

SELECT
    '' AS FUNCTION_CAT,
    TRIM(SRP.ROUTINESCHEMA) AS SCHEMA,
    SRP.ROUTINETYPE AS FUNCTION_TYPE,
    SRP.ROUTINENAME AS FUNCTION_NAME,
    SRP.ROUTINENAME AS ARG_NAME,
    SRP.ROUTINETYPE AS ARG_TYPE,
    SRP.SPECIFICNAME AS SPECIFIC_NAME,
    SRP.TYPENAME AS TYPE_NAME,
    SRP.PARMNAME AS COLUMN_NAME,
    null AS COLUMN_DEFAULT,
    SRP.ROWTYPE AS ROW_TYPE,
    R.TEXT AS CREATE_TEXT
FROM
    SYSIBM.SYSROUTINEPARMS SRP
    INNER JOIN SYSCAT.ROUTINES R
        ON SRP.ROUTINESCHEMA = R.ROUTINESCHEMA
        AND SRP.ROUTINENAME = R.ROUTINENAME
        AND SRP.ROUTINETYPE = R.ROUTINETYPE
WHERE SRP.ROUTINESCHEMA NOT IN ('''')
    AND SRP.ROUTINESCHEMA NOT IN  ( 'SYSIBMTS' , 'SYSCAT' , 'SYSIBMADM' ,
      'SYSIBM' , 'SYSPROC' , 'SYSPUBLIC' , 'NULLID' , 'SQLJ' ,
      'SYSIBMINTERNAL' , 'SYSSTAT' , 'SYSFUN' , 'SYSTOOLS')
    AND SRP.ROWTYPE IN ('P', 'O', 'B', 'C');

Index

Ensure your query has columns labelled as CATALOG, TABLE_NAME, COLUMN_NAME, REMARKS in the SELECT list.

SELECT
    '' AS CATALOG,
    TRIM(TABSCHEMA) AS SCHEMA,
    TABNAME AS TABLE_NAME,
    INDNAME AS INDEX_NAME,
    INDEXTYPE AS INDEX_TYPE,
    LTRIM(COLNAMES,'+-') AS COLUMN_NAME,
    NULL AS FILTER_CONDITION,
    NULL AS ASC_OR_DESC,
    0 AS ORDINAL_POSITION
FROM
    SYSCAT.INDEXES
WHERE TABSCHEMA  NOT IN ('''')
    AND TABSCHEMA NOT IN  ( 'SYSIBMTS' , 'SYSCAT' , 'SYSIBMADM' , 'SYSIBM' ,
        'SYSPROC' , 'SYSPUBLIC' , 'NULLID' , 'SQLJ' , 'SYSIBMINTERNAL' ,
        'SYSSTAT' , 'SYSFUN' , 'SYSTOOLS');