+ Reply to Thread
Results 1 to 10 of 10

Update Catalog Tables

  1. Update Catalog Tables

    Is it possible to manually update system catalog tables using SQL command
    for DB2 UDB?

    Thanks

    Luke



  2. Re: Update Catalog Tables

    These tables are updated during the operation of a database; for example, when a table is created. You cannot explicitly create or drop these tables, but you can query and view their content. It´s not recommended update manualy this tables.

  3. Re: Update Catalog Tables

    "Luke Xu" wrote in message
    news:eeeqcg$21g08$1@news.boulder.ibm.com...
    > Is it possible to manually update system catalog tables using SQL command
    > for DB2 UDB?
    >
    > Thanks
    >
    > Luke


    You can update some of the statistics in the DB2 Catalog. For example if you
    want DB2 think to think there are 100,000 rows for purposes of query
    optimization and access plan selection (when there are really 100 rows) you
    can update the CARD on SYSCAT.TABLES. Some other stats are also updatable.



  4. Re: Update Catalog Tables

    In article , linkxu@hotmail.com
    says...
    > Is it possible to manually update system catalog tables using SQL command
    > for DB2 UDB?
    >


    Only the SYSSTAT views are updatable. With V8 the following SYSSTAT
    views are defined:

    SYSSTAT.COLDIST
    SYSSTAT.COLGROUPDIST
    SYSSTAT.COLGROUPDISTCOUNTS
    SYSSTAT.COLGROUPS
    SYSSTAT.COLUMNS
    SYSSTAT.FUNCTIONS
    SYSSTAT.INDEXES
    SYSSTAT.ROUTINES
    SYSSTAT.TABLES
    SYSSTAT.XMLSTATS

  5. Re: Update Catalog Tables

    "Gert van der Kooij" wrote in message
    news:MPG.1f75422e56bfe1e5989687@news.software.ibm.com...
    >
    > Only the SYSSTAT views are updatable. With V8 the following SYSSTAT
    > views are defined:
    >
    > SYSSTAT.COLDIST
    > SYSSTAT.COLGROUPDIST
    > SYSSTAT.COLGROUPDISTCOUNTS
    > SYSSTAT.COLGROUPS
    > SYSSTAT.COLUMNS
    > SYSSTAT.FUNCTIONS
    > SYSSTAT.INDEXES
    > SYSSTAT.ROUTINES
    > SYSSTAT.TABLES
    > SYSSTAT.XMLSTATS


    Not true. As I mentioned, you can update the CARD on SYSCAT.TABLES

    update syscat.tables set card = 3 where tabname = 'TEST4' and tabschema =
    'DB2INST1'
    DB20000I The SQL command completed successfully.



  6. Re: Update Catalog Tables

    In article , nobody@nowhere.com
    says...

    >
    > Not true. As I mentioned, you can update the CARD on SYSCAT.TABLES
    >
    > update syscat.tables set card = 3 where tabname = 'TEST4' and tabschema =
    > 'DB2INST1'
    > DB20000I The SQL command completed successfully.
    >


    I thought so myself, I've done that in the past also. But the docs
    didn't agree with it, so I thought that it might have changed :)

    Copied from http://tinyurl.com/rq6ko :

    System catalog views

    The database manager creates and maintains two sets of system catalog
    views that are defined on top of the base system catalog tables.

    * SYSCAT views are read-only catalog views that are found in the
    SYSCAT schema. SELECT privilege on these views is granted to PUBLIC by
    default.
    * SYSSTAT views are updatable catalog views that are found in the
    SYSSTAT schema. The updatable views contain statistical information that
    is used by the optimizer. The values in some columns in these views can
    be changed to test performance. (Before changing any statistics, it is
    recommended that the RUNSTATS command be invoked so that all the
    statistics reflect the current state.)

  7. Re: Update Catalog Tables

    Luke Xu wrote:

    > Is it possible to manually update system catalog tables using SQL command
    > for DB2 UDB?


    Others have answered that this is not possible (with the exception of
    statistics information). But my question to you is this: what exactly do
    you have in mind? Why do you want to update the metadata?

    --
    Knut Stolze
    DB2 Information Integration Development
    IBM Germany

  8. Re: Update Catalog Tables

    Knut,

    For example, I want to update TEXT in SYSROUTINES with blank value. The
    procedure is still avilable to run, but the source code is blank if you want
    to view.

    My idea maybe stupid. But is there a way to encrpy SQL procedure? Someone
    suggests using GET ROUTINE and PUT ROUTINE, I don't know how it works.

    I asked Oracle DBA, Oracle database has a Wrap utility which can be used to
    do it.

    Thanks

    Luke



    "Knut Stolze" wrote in message
    news:eem0tl$1lo6g$3@news.boulder.ibm.com...
    > Luke Xu wrote:
    >
    >> Is it possible to manually update system catalog tables using SQL command
    >> for DB2 UDB?

    >
    > Others have answered that this is not possible (with the exception of
    > statistics information). But my question to you is this: what exactly do
    > you have in mind? Why do you want to update the metadata?
    >
    > --
    > Knut Stolze
    > DB2 Information Integration Development
    > IBM Germany




  9. Re: Update Catalog Tables

    I've always known (thought ??) that as SYSADMIN,SYSCTRL and DBADM on ghad
    direct update on the statistics columns of the SYSIBM.SYS88 tables.
    I verified this by doing:
    db2 connect to ses
    and
    D:\SQLLIB\BIN>db2 update sysibm.systables set card=card+1 where
    name='SESCOURS'
    DB20000I The SQL command completed successfully.
    That still works beautifully.
    So yes, one can update the SYSSTATS views but one can also directly update
    the stats columns in the base tables.
    Of course, if one likes to play russian roulette with a fully loaded gun,
    one always can...
    Regards, Pierre.

    --
    Pierre Saint-Jacques
    SES Consultants Inc.
    514-737-4515
    "Gert van der Kooij" a écrit dans le message de news:
    MPG.1f75d449beab9463989688@news.software.ibm.com...
    > In article , nobody@nowhere.com
    > says...
    >
    >>
    >> Not true. As I mentioned, you can update the CARD on SYSCAT.TABLES
    >>
    >> update syscat.tables set card = 3 where tabname = 'TEST4' and tabschema =
    >> 'DB2INST1'
    >> DB20000I The SQL command completed successfully.
    >>

    >
    > I thought so myself, I've done that in the past also. But the docs
    > didn't agree with it, so I thought that it might have changed :)
    >
    > Copied from http://tinyurl.com/rq6ko :
    >
    > System catalog views
    >
    > The database manager creates and maintains two sets of system catalog
    > views that are defined on top of the base system catalog tables.
    >
    > * SYSCAT views are read-only catalog views that are found in the
    > SYSCAT schema. SELECT privilege on these views is granted to PUBLIC by
    > default.
    > * SYSSTAT views are updatable catalog views that are found in the
    > SYSSTAT schema. The updatable views contain statistical information that
    > is used by the optimizer. The values in some columns in these views can
    > be changed to test performance. (Before changing any statistics, it is
    > recommended that the RUNSTATS command be invoked so that all the
    > statistics reflect the current state.)



  10. Re: Update Catalog Tables

    Pierre Saint-Jacques wrote:

    > I've always known (thought ??) that as SYSADMIN,SYSCTRL and DBADM on ghad
    > direct update on the statistics columns of the SYSIBM.SYS88 tables.
    > I verified this by doing:
    > db2 connect to ses
    > and
    > D:\SQLLIB\BIN>db2 update sysibm.systables set card=card+1 where
    > name='SESCOURS'
    > DB20000I The SQL command completed successfully.
    > That still works beautifully.
    > So yes, one can update the SYSSTATS views but one can also directly update
    > the stats columns in the base tables.
    > Of course, if one likes to play russian roulette with a fully loaded gun,
    > one always can...


    And, of course, no one should access the SYSIBM tables directly in the first
    place. Those tables are not documented and, thus, can change and vanish
    any time. And IBM is not to blame if the application breaks.

    --
    Knut Stolze
    DB2 Information Integration Development
    IBM Germany

+ Reply to Thread