I'm taking this exam on Wensday. There is one question I don't understand
from test (online) exam:
For which objects (Table, View, Index...) SQL source (DDL) is held in system
catalog and for which objects isn't?
One more question: using which DB2 tool (Query Patroller, Rational
Connect...) you can do a select on a DB2 and Oracle from one line of code
(one SELECT)?
Pierre Saint-Jacques - 04 Jul 2006 20:53 GMT
I'll correct myself before someone else does.
The TEXT of the definition is in SYSIBM.SYVIEWS and that is the only object
that has it like that.
So I guess your answer should be View only.
Apologies, Pierre.

Signature
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
>
> One more question: using which DB2 tool (Query Patroller, Rational
> Connect...) you can do a select on a DB2 and Oracle from one line of code
> (one SELECT)?
> I'm taking this exam on Wensday. There is one question I don't understand
> from test (online) exam:
>
> For which objects (Table, View, Index...) SQL source (DDL) is held in
> system catalog and for which objects isn't?
The View DDL is in the catalog. For the others, the information about
columns, etc are in the various catalog tables.
Rhino - 03 Jul 2006 17:36 GMT
>> I'm taking this exam on Wensday. There is one question I don't understand
>> from test (online) exam:
[quoted text clipped - 4 lines]
> The View DDL is in the catalog. For the others, the information about
> columns, etc are in the various catalog tables.
In other words, the DDL for CREATE VIEW is stored explicitly in the DB2
Catalog.
For other objects, the DDL is not stored explicitly but the detailed
description of the object is found in the appropriate tables of the catalog.
For instance, if you look at SYSIBM.SYSTABLES, you will find all of the
attributes of every table in your database; each attribute is stored in a
separate column. Therefore, the exact DDL that was used to create these
objects is not kept but all of the information is available by reading the
catalog tables and views.
Since the real DDL is more convenient in some cases than the catalog
definitions, many DBAs store all DDL that get executed. In most shops, the
DBAs create all of the higher-level objects and use scripts to create the
objects. They keep the scripts after they have been executed. Then they have
both the original script, which shows the real DDL, and can get all of the
details from the catalog as well.
--
Rhino
John Smith - 04 Jul 2006 12:56 GMT
> The View DDL is in the catalog. For the others, the information about
> columns, etc are in the various catalog tables.
As much as I remember, test question was to choose 2 objects which have
complete DDL definitions (CREATE...) stored in system catalog. Are you sure
the views are the only objects?
John Smith - 04 Jul 2006 13:29 GMT
>> The View DDL is in the catalog. For the others, the information about
>> columns, etc are in the various catalog tables.
>
> As much as I remember, test question was to choose 2 objects which have
> complete DDL definitions (CREATE...) stored in system catalog. Are you
> sure the views are the only objects?
What about triggers?
Sanjuro - 05 Jul 2006 14:23 GMT
Yes, trigger definitions are stored in syscat tables.
Regarding accessing DB2 and Oracle from single line of code, Relational
connect can be used in a federated environment to have native read
access to variety of database sources.
Cheers,
Ashrujit
> >> The View DDL is in the catalog. For the others, the information about
> >> columns, etc are in the various catalog tables.
[quoted text clipped - 4 lines]
>
> What about triggers?
John Smith - 06 Jul 2006 12:27 GMT
Mark A - 04 Jul 2006 18:09 GMT
>> The View DDL is in the catalog. For the others, the information about
>> columns, etc are in the various catalog tables.
>
> As much as I remember, test question was to choose 2 objects which have
> complete DDL definitions (CREATE...) stored in system catalog. Are you
> sure the views are the only objects?
In your OP, the choices you gave were "(Table, View, Index...)". Among those
three, only the DDL of the View is stored.
There are other objects that have their DDL stored in the Catalog. You can
check out the Catalog definitions in the SQL Reference Vol 1 Appendix D to
find the answer.
Pierre Saint-Jacques - 04 Jul 2006 20:50 GMT
You can find this in your DB2 Admin. reference Handdbook where all the
tables are described.
The SYSIBM.SYSTABLES catalog tables has a column that identifies the type of
object (V=View,T=Table,A=Alias) as well as a column PACKED_DESC which has a
compacted definition of the object. There's also an INDEX_DESC column in
SYSIBM.SYSINDEXES.
I believe but not definite that this where the db2look utility gets its info
when you use it to extract the DDL from an object
As a matter of fact, it has add'l columns, REL_DESC, TRIG_DESC,AST_DESC, and
so on where what is related to the table is stored.
To answer your question, I'd say all of the above as mentioned in your
question.
HTH, Pierre.

Signature
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
>>
>> I'm taking this exam on Wensday. There is one question I don't understand
[quoted text clipped - 5 lines]
> The View DDL is in the catalog. For the others, the information about
> columns, etc are in the various catalog tables.