Shared columns between two tables in DB2

Question

My question is duplicate of this SO question but I want to do it for DB2 database while other question was asked for SQL Server.

I have two tables TABLE1 and TABLE2 in Schema BP. I wish to find names of columns shared between these two tables.

There are many schema on DB Server,

I don't see any generic answers there which would be applicable for all types of DBs.

score 1 · Answer 1 · answered Aug 05 '16 at 06:32

1

A DB2 DBA at my office told me this and its giving me correct results,

SELECT BP.BP_COL FROM
(SELECT COLNAME AS BP_COL FROM SYSCAT.COLUMNS WHERE TABNAME='TABLE1' AND TABSCHEMA='BP' ) BP1
INNER JOIN
(SELECT COLNAME AS AR_COL FROM SYSCAT.COLUMNS WHERE TABNAME='TABLE2' AND TABSCHEMA='BP' ) BP2
ON BP1.BP_COL=BP2.AR_COL
WITH UR;

answered Aug 05 '16 at 06:32

Sabir Khan

9,826
7
45
98

So DB2 doesn't have INFORMATION_SCHEMA?!? – jarlh Aug 05 '16 at 06:39
yes , I think so. I tried to execute queries based on ' INFORMATION_SCHEMA` and get **undefined name** error. – Sabir Khan Aug 05 '16 at 06:43
Thanks for answering. I expected all major dbms products to implement INFORMATION_SCHEMA, apparently I was wrong. – jarlh Aug 05 '16 at 06:55
Oracle is the other major database that doesn't implement INFORMATION_SCHEMA according to Wikipedia. https://en.wikipedia.org/wiki/Information_schema – Aug 05 '16 at 06:57
DB2 for i, actually has information_schema. So it depends on your platform (and version?) – Charles Aug 05 '16 at 12:45
The data in SYSCAT is the same as INFORMATION_SCHEMA -- it is easy to define a nickname if you need the name "INFORMATION_SCHEMA" for some reason. The standard does not require specific names -- just contents. – Hogan Aug 05 '16 at 14:30

Hogan · Accepted Answer · 2016-08-05T14:33:23.383

I'm not sure why @SabirKhan answer has so many sub-queries -- just join the meta information to itself -- an inner join will ensure you get results from both tables.

SELECT A.COLNAME AS DUP 
FROM SYSCAT.COLUMNS A 
JOIN SYSCAT.COLUMNS B ON A.COLNAME = B.COLNAME AND  B.TABNAME='TABLE2' AND B.TABSCHEMA='BP' 
WHERE A.TABNAME='TABLE1' AND A.TABSCHEMA='BP'

As for you not seeing answers that work with all database platforms. You are correct -- there are no such answers -- database platforms vary a lot.

score 0 · Answer 3 · answered Aug 05 '16 at 16:37

FWiW: For more than just the effective INTERSECT of the names, the following shows that as well as the unmatched names betwixt; the specification of the qualified SYSCOLUMNS or similar catalog VIEW and the corresponding column names may be specific to the DB2 variant such that adjustments are likely required, but the following was successful, exactly as shown, using the IBM DB2 for i 7.1 SQL:

Setup:

 create table bp.TABLE1 (in_both char, common char, only_in_t1 char )
 ;
 create table bp.TABLE2 ( only_in_t2 char, in_both char, common char)
 ;

Query of the columns:

 SELECT t1_col, t2_col                                              
 from ( select char(   column_name, 25) as t1_col                   
        from syscolumns                                             
        where    table_name = 'TABLE1' and    table_schema='BP' ) as t1
 full outer join                                                    
      ( select char(   column_name, 25) as t2_col                   
        from syscolumns                                             
        where    table_name = 'TABLE2' and    table_schema='BP' ) as t2
   on t1_col = t2_col                                               
 ; -- report from above query, with headings, follows [where a dash indicates NULL value]:
 T1_COL                     T2_COL    
 IN_BOTH                    IN_BOTH   
 COMMON                     COMMON    
 ONLY_IN_T1                 -         
 -                          ONLY_IN_T2

Shared columns between two tables in DB2

3 Answers3