Data Dictionary

Database
Enthusiast

Data Dictionary

Is there any table/view in the data dictionary that we can query to find out if a table is a multiset table, a set table, a global temp table, etc.?
8 REPLIES
Senior Apprentice

Re: Data Dictionary

Etc. is probably included in dbc.tables :-)
But Set/Multiset and temp table info isn't, so you have to query dbc.tvm directly:

If it's a table (TableKind = 'T') -> CheckOpt = 'Y' Multiset else Set

If CommitOpt in ('D', 'P') then it's a Global Temp Table

Dieter

Re: Data Dictionary

Will u pls tell me the meaning of NULL in the column CHECKOPT of table TVM
Senior Apprentice

Re: Data Dictionary

CheckOpt stores totally different infomation (of course this is bad practice, but i didn't implement it):
If it's a table then set/multiset, if it's a view then it indicates if "WITH CHECK OPTION" was included in the view's source.

It's NULL if that object is neither table nor view.

Dieter
Enthusiast

Re: Data Dictionary

Thank you very much for the response.

Is there also a column in dbc.tvm that indicates whether or not the table has a partitioned primary index defined?
Enthusiast

Re: Data Dictionary

Never mind on this one. I found the information I need and the column is in dbc.indices.

Re: Data Dictionary

Hi,
i did a query: sel * from dbc.indices
and found out that the Index Type for some tables was 'Q' and 'K' other than the usual types 'P' & 'S' .what could these possibly mean can anyone tell me please?

Re: Data Dictionary

Hi,
i did a query: sel * from dbc.indices
and found out that the Index Type for some tables was 'Q' and 'K' other than the usual types 'P' & 'S' .what could these possibly mean can anyone tell me please?
Senior Apprentice

Re: Data Dictionary

There's a whole manual dealing with those systemtable-columns: Data Dictionay Manual

Q -> PPI
K -> PK within Create Table implemented as UPI

Dieter