Adding indexes to Catalog tables ... ?

[Rhino]

"Paul Reddin" <paul@abacus.co.uk> wrote in message
news:1fd2a603.0306300541.32e335c5@posting.google.c om...

> Hi,
>
> We have a significant performance problem, that manifests itself by
> millions of reads against SYSIBM.SYSRELS table (card is 315 rows)?
>
> The application doing the reads is Toplink (W2K client) - it is
> refreshing its view of several user tables by reading the catalog via
> the SYSIBM.SQL* Stored Procedures, and 'describe' static statements
> (via jdbc)?
> - I have observed this from snapshoting its execution.
>
> We need to improve the performance of this significantly, but I cannot
> see what we can do at the database server level (Linux)??
>
> e.g Is it possible to 'add' indexes to sysibm.sysrels (I've been
> starting to look at db2ocat etc...
>

I'm not familiar with Toplink but you can't add indexes to system objects
like SYSIBM.SYSRELS. If you try, you get this error:

SQL0607N "CREATE INDEX" is not defined for system objects. SQLSTATE=42832

When you look this error up in the Messages Reference, it says:

SQL0607N "<operation>" is not defined for system objects.

Explanation: The "<operation>" specified in the SQL statement cannot be
performed on system objects. One of the following was attempted:

a.. DROP or ALTER a system owned object such as a system catalog table,
built-in function or built-in data type
b.. COMMENT ON a system owned built-in function.
c.. INSERT or DELETE on a system catalog table.
d.. UPDATE directly on a system catalog table. Some columns of a subset of
system catalog tables are updateable. For UPDATE operation on these catalog
tables, updateable views in SYSSTAT schema should be used. For a description
of updateable catalog views (SYSSTAT views), see the SQL Reference.
e.. CREATE or DROP of an index on a system table
f.. CREATE of a trigger on a system table
g.. A non-updateable system table was identified in the FROM clause of a
SELECT statement containing a FOR UPDATE clause. For a list of updateable
system catalogs see the SQL Reference.
h.. DROP or ALTER of a system table space.
i.. DROP or ALTER of a system nodegroup.
j.. REDISTRIBUTE of the IBMCATGROUP or IBMTEMPGROUP nodegroups.
The statement cannot be processed.

User Response: Do not attempt to modify any system objects except for those
columns of the system catalog tables which are updateable through updateable
SYSSTAT views. See the SQL Reference for more information.

sqlcode: -607

sqlstate: 42832




Maybe you should talk to the folks who wrote Toplink and see if they have a
solution. It sounds as if their code might not be as efficient as it could
be. I can't see why they'd want to read SYSIBM.SYSRELS millions of times.
(Actually, I question their use of SYSIBM.SYSRELS in the first place; I
thought that all users, including vendors, were supposed to use
SYSCAT.REFERENCES, not SYSIBM.SYSRELS, for looking up referential
constraints.) Sounds like there might be a bug in their code.

Maybe you should check for a fixpack or new version of Toplink *first*; if
there isn't anything later than your version, you could raise a problem
report with them and throw the problem back in their court.

Rhino

[Paul Reddin]

Rhino,

Thanks for your thoughts.

Toplink is now owned by Oracle, so it should be 'fun' to go to them and
ask them to solve a DB2 related performance problem.

I guessed it was the SYSIBM.SQL* or SQL describe statements that were
implicitly accessing SYSIBM.SYSRELS and not the code directly referencing
it.

Paul