[SOLVED] SYSIBM.SYSCOLUMNS query shows columns twice if using REMARKS

SYSIBM.SYSCOLUMNS query shows columns twice if using REMARKS

I've noticed a strange behaviour while working with DB2 and the SYSIBM.SYSCOLUMNS which only appears in one table.

This is my query:

select distinct NAME, COLTYPE, LENGTH, SCALE, REMARKS from SYSIBM.SYSCOLUMNS where  TBNAME='Table1';

Normally the result shows every table once.

|===================================|
| NAME |COLTYPE|LENGTH|SCALE|REMARKS|
|col1  | ..... |......|.....|.......|
|col2  | ..... |......|.....|.......|
|col3  | ..... |......|.....|.......|
|===================================|

But if I use one specific table, let's call it Table2, every column shows up twice:

|===================================|
| NAME |COLTYPE|LENGTH|SCALE|REMARKS|
|col1  | ..... |......|.....|.......|
|col1  | ..... |......|.....|.......|
|col2  | ..... |......|.....|.......|
|col2  | ..... |......|.....|.......|
|col3  | ..... |......|.....|.......|
|col3  | ..... |......|.....|.......|
|===================================|

But only if I include the REMARKS in the select-statement. If I remove the REMARKS from my select, everything works as expected.

What could be the cause of that problem? Is there something wrong with my query?

Solution

You have not considered schemas. There could be table2 in schema A and schema B.

IN LUW:

Personally, I recommend the syscat schema in order to query the catalog. The sysibm are internal to DB2 and these tables could change between versions.

select TABSCHEMA, TABNAME, COLNAME, TYPENAME, LENGTH, SCALEREMARKS
from syscat.columns
where tabname = 'TABLE2'