Skip to main content
Windward

DB2 - Overriding Metadata Selects

Overview

Each flavor of DB2 has distinct selects to return the metadata. We provide a means to set these selects if you are using a flavor that we do not presently support.

When you do this, we ask that you provide us with the selects that work for the flavor of DB2 you use, along with the specification of the flavor and the classname of the ADO.NET connector used. We can then add this in to our system.

Resolution

You need to set the below values in the config file.

  • This is winword.exe.config (or excel.exe.config or powerpnt.exe.config) for AutoTag and this file is located in C:\Program Files (x86)\Microsoft Office\Office15 or C:\Program Files\Microsoft Office\Office15. (Office15 for Office 2013, the number is different for other versions of Office.)
  • For the engine this is web.config (ASP.NET) or app.exe.config (applications). However, this code is not at present used by the engine, only AutoTag.

This is set as follows:

<configuration>
    <appSettings>
        <add key="db2-select-schema" value="select CURRENT SCHEMA from SYSIBM.SYSDUMMY1"/>
    </appSettings>
</configuration>

 

This is the key value pairs for DB2 on Windows (the default):

db2-select-schema select CURRENT SCHEMA from SYSIBM.SYSDUMMY1
db2-select-tables-system select TABNAME, REMARKS, TABSCHEMA from SYSCAT.TABLES where TYPE in ('T','N') and (TABSCHEMA = 'SYSCAT' OR TABSCHEMA = 'SYSIBM' OR TABSCHEMA = 'SYSIBMADM' OR TABSCHEMA = 'SYSSTAT' OR TABSCHEMA <> 'SYSTOOLS') order by TABSCHEMA, TABNAME
db2-select-tables-user select TABNAME, REMARKS, TABSCHEMA from SYSCAT.TABLES where TYPE in ('T','N') and TABSCHEMA <> 'SYSCAT' and TABSCHEMA <> 'SYSIBM' AND TABSCHEMA <> 'SYSIBMADM' AND TABSCHEMA <> 'SYSSTAT' AND TABSCHEMA <> 'SYSTOOLS' order by TABSCHEMA, TABNAME
db2-select-tables-user-owned elect TABNAME, REMARKS, TABSCHEMA from SYSCAT.TABLES where TYPE in ('T','N') and TABSCHEMA <> 'SYSCAT' and TABSCHEMA <> 'SYSIBM' AND TABSCHEMA <> 'SYSIBMADM' AND TABSCHEMA <> 'SYSSTAT' AND TABSCHEMA <> 'SYSTOOLS' AND USER = OWNER order by TABSCHEMA, TABNAME
db2-select-tables-all select TABNAME, REMARKS, TABSCHEMA from SYSCAT.TABLES where TYPE in ('T','N') order by TABNAME
db2-select-views-system select VIEWNAME, VIEWSCHEMA from SYSCAT.VIEWS where VIEWSCHEMA = 'SYSCAT' OR VIEWSCHEMA = 'SYSIBM' OR VIEWSCHEMA = 'SYSIBMADM' OR VIEWSCHEMA = 'SYSSTAT' OR VIEWSCHEMA = 'SYSTOOLS' OR VIEWSCHEMA = 'SYSCATV82' order by VIEWSCHEMA, VIEWNAME
db2-select-views-user select VIEWNAME, VIEWSCHEMA from SYSCAT.VIEWS where VIEWSCHEMA <> 'SYSCAT' and VIEWSCHEMA <> 'SYSIBM' AND VIEWSCHEMA <> 'SYSIBMADM' AND VIEWSCHEMA <> 'SYSSTAT' AND VIEWSCHEMA <> 'SYSTOOLS' AND VIEWSCHEMA <> 'SYSCATV82' order by VIEWSCHEMA, VIEWNAME
db2-select-views-user-owned select VIEWNAME, VIEWSCHEMA from SYSCAT.VIEWS where VIEWSCHEMA <> 'SYSCAT' and VIEWSCHEMA <> 'SYSIBM' AND VIEWSCHEMA <> 'SYSIBMADM' AND VIEWSCHEMA <> 'SYSSTAT' AND VIEWSCHEMA <> 'SYSTOOLS' AND VIEWSCHEMA <> 'SYSCATV82' order by VIEWSCHEMA, VIEWNAME
db2-select-views-all select VIEWNAME, VIEWSCHEMA from SYSCAT.VIEWS order by VIEWNAME
db2-select-columns select TABSCHEMA, TABNAME, COLNAME, REMARKS, TYPENAME from SYSCAT.COLUMNS where TABSCHEMA <> 'SYSCAT' and TABSCHEMA <> 'SYSIBM' AND TABSCHEMA <> 'SYSIBMADM' AND TABSCHEMA <> 'SYSSTAT' AND TABSCHEMA <> 'SYSTOOLS' order by TABSCHEMA, TABNAME, COLNO
db2-select-procedures-system select PROCNAME, REMARKS, PROCSCHEMA from SYSCAT.PROCEDURES where PROCSCHEMA = 'SYSCAT' or PROCSCHEMA = 'SYSIBM' or PROCSCHEMA = 'SYSIBMADM' or PROCSCHEMA = 'SYSSTAT' or PROCSCHEMA = 'SYSTOOLS' or PROCSCHEMA = 'SQLJ' or PROCSCHEMA = 'SYSPROC' or PROCSCHEMA = 'SYSFUN' order by PROCNAME
db2-select-procedures-user select PROCNAME, REMARKS, PROCSCHEMA from SYSCAT.PROCEDURES where PROCSCHEMA <> 'SYSCAT' and PROCSCHEMA <> 'SYSIBM' AND PROCSCHEMA <> 'SYSIBMADM' AND PROCSCHEMA <> 'SYSSTAT' AND PROCSCHEMA <> 'SYSTOOLS' AND PROCSCHEMA <> 'SQLJ' AND PROCSCHEMA <> 'SYSPROC' AND PROCSCHEMA <> 'SYSFUN' order by PROCNAME
db2-select-procedures-user-owned select PROCNAME, REMARKS, PROCSCHEMA from SYSCAT.PROCEDURES where PROCSCHEMA <> 'SYSCAT' and PROCSCHEMA <> 'SYSIBM' AND PROCSCHEMA <> 'SYSIBMADM' AND PROCSCHEMA <> 'SYSSTAT' AND PROCSCHEMA <> 'SYSTOOLS' AND PROCSCHEMA <> 'SQLJ' AND PROCSCHEMA <> 'SYSPROC' AND PROCSCHEMA <> 'SYSFUN' order by PROCNAME
db2-select-procedures-all select PROCNAME, REMARKS, PROCSCHEMA from SYSCAT.PROCEDURES order by PROCNAME
db2-select-parameters SELECT TYPENAME, PARMNAME, LENGTH, ROWTYPE FROM SYSIBM.SYSROUTINEPARMS WHERE ROUTINENAME = ?
db2-select-pk-fk SELECT SYSIBM.SYSTABCONST.NAME, SYSIBM.SYSTABCONST.CONSTRAINTYP, SYSIBM.SYSKEYCOLUSE.TBCREATOR, SYSIBM.SYSKEYCOLUSE.TBNAME, SYSIBM.SYSKEYCOLUSE.COLNAME, SYSIBM.SYSRELS.REFTBCREATOR, SYSIBM.SYSRELS.REFTBNAME, SYSIBM.SYSRELS.PKCOLNAMES FROM SYSIBM.SYSTABCONST JOIN SYSIBM.SYSKEYCOLUSE ON (SYSIBM.SYSTABCONST.NAME = SYSIBM.SYSKEYCOLUSE.CONSTNAME) AND (SYSIBM.SYSTABCONST.CONSTRAINTYP = 'F') JOIN SYSIBM.SYSRELS ON SYSIBM.SYSTABCONST.NAME = SYSIBM.SYSRELS.RELNAME

Notes

  1. The selection of tables, views, and stored procedures can be limited to user (as opposed to system), user owned, system, or all. The appropriate select is used based on the user's selection in the datasource connection dialog.
  2. If there's a better way to do this for the Windows version of DB2, please let us know.
  3. You must return the columns in the same order.
  4. db2-select-pk-fk is used to read the PK-FK relationships. This is what we use to automatically calculate the joins. For each relationship it needs to return the columns, in order: unused, unused, FK_Schema, FK_Table, FK_Column, PK_Schema, PK_Table, PK_COLUMN. This is 8 columns, with the first 2 ignored (long story as to why it is this way).
  • Was this article helpful?