0

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?

Christian
  • 22,585
  • 9
  • 80
  • 106

2 Answers2

5

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'
AngocA
  • 7,655
  • 6
  • 39
  • 55
1

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?

Charles
  • 21,637
  • 1
  • 20
  • 44