Show relational structure of DB2 tables with sql developer

Question

Show relational structure of DB2 tables with sql developer

Navigation

#1 by (1 votes)

1

I am working in a DB2 environment using Oracle SQL Developer, I know I can find the structure of a table using:

SELECT * FROM Sysibm.syscolumns WHERE tbname = '[NOMBRE_TABLA]';

but this only shows me the rows of the table and its information, but it does not give me the concrete information of the rows that are foreign keys, that is, I can know what rows are foreign keys of this way:

SELECT * FROM Sysibm.syscolumns WHERE tbname = '[NOMBRE_TABLA]' AND FOREIGNKEY='S';

but not to which tables they point.

How can I show the relational structure of a table with others?

I have tried these solutions, but without success, (I think) because they are answers Oracle and not DB2

oracle-sqldeveloper

asked by Jordi Castilla 13.07.2016 в 10:50

source

1 answer

WPF does not start WCF when installed AngularJS: "angular-update-meta" does not work when sharing URLs on social networks

score 1 · Answer 1

In db2 (LUW) the relationships between tables are found in the view SYSCAT.REFERENCES :

db2 describe table syscat.references

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
CONSTNAME                       SYSIBM    VARCHAR                    128     0 No
TABSCHEMA                       SYSIBM    VARCHAR                    128     0 No
TABNAME                         SYSIBM    VARCHAR                    128     0 No
OWNER                           SYSIBM    VARCHAR                    128     0 No
OWNERTYPE                       SYSIBM    CHARACTER                    1     0 No
REFKEYNAME                      SYSIBM    VARCHAR                    128     0 No
REFTABSCHEMA                    SYSIBM    VARCHAR                    128     0 No
REFTABNAME                      SYSIBM    VARCHAR                    128     0 No
COLCOUNT                        SYSIBM    SMALLINT                     2     0 No
DELETERULE                      SYSIBM    CHARACTER                    1     0 No
UPDATERULE                      SYSIBM    CHARACTER                    1     0 No
CREATE_TIME                     SYSIBM    TIMESTAMP                   10     6 No
FK_COLNAMES                     SYSIBM    VARCHAR                    640     0 No
PK_COLNAMES                     SYSIBM    VARCHAR                    640     0 No
DEFINER                         SYSIBM    VARCHAR                    128     0 No

  15 record(s) selected.

In this article from IBM there are several SQL examples:

select  substr(R.reftabschema,1,12) as P_Schema, substr(R.reftabname,1,12) as PARENT,
        substr(R.tabschema,1,12) as C_Schema, substr (R.tabname,1,12) as CHILD,
        substr(R.constname,1,12) as CONSTNAME, 
        substr(LISTAGG(C.colname,', ') WITHIN GROUP (ORDER BY C.colname),1,20) as FKCOLS 
from syscat.references R, syscat.keycoluse C 
where 
   R.constname=C.constname and 
   R.tabschema=C.tabschema and 
   R.tabname=C.tabname
group by R.reftabschema, R.reftabname, R.tabschema, R.tabname, R.constname;