Professional Web Applications Themes

Adding indexes to Catalog tables ... ? - IBM DB2

"Paul Reddin" <paulabacus.co.uk> wrote in message news:1fd2a603.0306300541.32e335c5posting.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 ...

  1. #1

    Default Re: Adding indexes to Catalog tables ... ?


    "Paul Reddin" <paulabacus.co.uk> wrote in message
    news:1fd2a603.0306300541.32e335c5posting.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


    Rhino Guest

  2. #2

    Default Re: Adding indexes to Catalog tables ... ?

    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
    Paul Reddin Guest

Similar Threads

  1. Adding Pages to a Horizontal format catalog??
    By Marco_A_SantaMaria@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 4
    Last Post: October 18th, 02:00 PM
  2. Tables - adding to editable area
    By geovino in forum Macromedia Dynamic HTML
    Replies: 5
    Last Post: December 18th, 03:37 PM
  3. Adding records to tables
    By Mike1500 in forum Dreamweaver AppDev
    Replies: 0
    Last Post: March 17th, 07:11 PM
  4. InDesign 2.0 Indexes with Tables
    By Mark_Conner@adobeforums.com in forum Adobe Indesign Windows
    Replies: 0
    Last Post: May 10th, 10:31 PM
  5. DB2 Catalog Tables Monitoring
    By Amir in forum IBM DB2
    Replies: 2
    Last Post: August 13th, 10:25 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139