CODEWITHSUNDEEP
selectdb2system-tables
Christian
Christian

Reputation: 22343

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?

Upvotes: 0

Views: 4625

Answers (2)

AngocA
AngocA

Reputation: 7693

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'

Upvotes: 5

Charles
Charles

Reputation: 23783

What's in the remarks column for each row?

You've got a DISTINCT in your original query. Since you're only selecting on table name, is it possible that you have the same table in multiple schema's; one with a remarks and one without?

Upvotes: 1

Related Questions