Question regarding catalog tables

hisathishkumar · New User Joined: 27 Nov 2007 Posts: 1 Location: HYDERABAD

I have a question regarding catalog tables...

which of the following will affect the catalog tables??
A) ALTER
B)DROP
C)SELECT

zero · New User Joined: 01 Dec 2007 Posts: 21 Location: Hyderabad

Hi,
catalog tables are nothing but system tables which are used by DB2 to store metadata (Information about tables - the different columns in a table, their definitions, their properties like unique key, nullability ... , tablespaces, indexes, views and other objects).

Below are a few of the DB2 catalog tables :

SYSTABLES :
SYSTABSTATS :
SYSCOLUMNS :
SYSINDEXES :
SYSKEYS :
SYSFOREIGNKEYS

above tables are used for different purposes.

now, coming to your question,

ALTER - this is used to modify the structure of the table. like, adding a column, increasing the size of CHAR field,
...
so, i think, ALTER will definitely affects catalog tables.

DROP - dropping a table means all the information of the table is removed from the before said tables. so, DROP also affects catalog tables.

SELECT - SELECT statement just uses the catalog information in catalog tables while fetching data from tables. so, it does not affect catalog tables.

hope this helps,

thanks,
zero

stodolas · Posted: Sat Dec 01, 2007 10:43 pm

Select can also be used to affect the system tables

SELECT INTO SYSIBM.SYSCOLUMNS

would definetly affect that table.

Debraj Roy · New User Joined: 20 Dec 2007 Posts: 1 Location: Bangalore

Can you explain what is the function of this statement:??

SELECT INTO SYSIBM.SYSCOLUMNS

Warm Regards,
Debraj

stodolas · Posted: Mon Jan 28, 2008 6:17 pm

Bah, I referenced it wrong for DB2. That is a programming statement.
publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000994.htm

see: expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci1054790,00.html
for what I was getting at.