Table Types in DB2

Question

While writing a program that interfaces Java and DB2 over the JDBC Driver, I attempted to write a program that would retrieve all data (all records contained in all tables) in a given database. When I ran the program, I found that it mostly worked. However, there were several tables that it did not collect data from. I accessed these tables from IBM's bundled Command Editor and found that they contained data. I ran

SELECT * FROM SYSCAT.COLUMNS WHERE TABNAME='Table'

against the database and found that these tables had no column records in the SYSCAT.COLUMNS table. Believing that I misspelled that table name, I ran

SELECT * FROM SYSCAT.COLUMNS

against the database and manually looked for the table under TABNAME. I could not find it. When I ran

SELECT * FROM SYSCAT.TABLES

I found the table in the list of available tables. However, in SYSCAT.TABLES, I also found that none of the tables of TYPE 'A' had any records in SYSCAT.COLUMNS. I was wondering what IBM's reasoning for this is, and how I can find out what the field names are for these particular types of tables.

TL;DR How do I get column names for DB2 without using SYSCAT.COLUMNS through a standard SQL query to a DB2 database?

Michael Sharek · Accepted Answer · 2011-06-28 19:33:44Z

6

This makes sense because tables of type 'A' are an alias to another table.

If you want to find the columns for a table of type 'A', look in the BASE_TABSCHEMA and BASE_TABNAME columns to find name of the schema and table that the alias is referencing. If you look up that table, it will be in syscolumns.

In other words you should rewrite your query this way:

SELECT * FROM SYSCAT.COLUMNS WHERE TABNAME=
(SELECT BASE_TABNAME FROM SYSCAT.TABLES WHERE TABNAME = 'Table')

BTW, the DB2 documentation contains a complete reference to the system catalog. It's here.

answered Jun 28, 2011 at 19:33

Michael Sharek

5,0792 gold badges32 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Table Types in DB2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related