Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2013
    Posts
    4

    Unanswered: reorgchk - no contents in results

    I did a redirected restore from the prod to the dev environment and tried to do a reorgchk on dev but the results does not contain any real info.

    >db2 connect to dbname1

    Database Connection Information

    Database server = DB2/AIX64 9.7.7
    SQL authorization ID = dbname1
    Local database alias = dbname1

    dbname1@Servername1>db2 reorgchk

    Doing RUNSTATS ....


    Table statistics:

    F1: 100 * OVERFLOW / CARD < 5
    F2: 100 * (Effective Space Utilization of Data Pages) > 70
    F3: 100 * (Required Pages / Total Pages) > 80

    SCHEMA.NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
    ----------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------

    Index statistics:

    F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
    F5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > MIN(50, (100 - PCTFREE))
    F6: (100 - PCTFREE) * (Amount of space available in an index with one less level / Amount of space required for all keys) < 100
    F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20
    F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20

    SCHEMA.NAME INDCARD LEAF ELEAF LVLS NDEL KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD PCT_PAGES_SAVED F4 F5 F6 F7 F8 REORG
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
    for indexes that are not in the same sequence as the base table. When multiple
    indexes are defined on a table, one or more indexes may be flagged as needing
    REORG. Specify the most important index for REORG sequencing.

    Tables defined using the ORGANIZE BY clause and the corresponding dimension
    indexes have a '*' suffix to their names. The cardinality of a dimension index
    is equal to the Active blocks statistic of the table.

    dbname1 is the name of the database and of the db2 instance user.

    In the original prod environment I get results with exactly the same commands.
    Any ideas why?

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    What's the result of the following query in both databases:

    select count(1) from syscat.tables where type='T' and owner=user
    Regards,
    Mark.

  3. #3
    Join Date
    Nov 2013
    Posts
    4
    Hi Mark,
    Thank you!
    it gives 118 in the prod database and 0 in the redirected restore database in dev.
    Regards,
    L

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    something very wrong in the restored database. By the above you are stating that you have 118 tables in your prod environment and 0 in your restored environment.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by dav1mo View Post
    something very wrong in the restored database. By the above you are stating that you have 118 tables in your prod environment and 0 in your restored environment.
    Or it may just mean that the instance owner in production is different from the one in development.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Nov 2013
    Posts
    4
    Hi there,

    Your feedback is much appreciated!

    It is so - the instance owner in prod is db2iprod and the instant owner in dev is db2idev. When I created a user in dev, called db2iprod, and run the query as db2iprod, I get 118 in the restored database on dev also! It seems as if the owner<>user in the restored database on dev.

    As db2iprod on DEV server

    select count(1) from syscat.tables where type='T' and owner=user
    1
    -----------
    118


    select owner, user from syscat.tables where type='T'
    OWNER 2
    ------- -------
    SYSIBM DB2IPROD (only first row)

    (It actually surprises me that db2 think 'SYSIBM'='DB2IPROD' )

    As db2idev (db2instance user ) on DEV server:

    select count(1) from syscat.tables where type='T' and owner=user
    1
    -----------
    0

    1 Row(s) affected


    select owner, user from syscat.tables where type='T'
    OWNER 2
    ------- -------
    SYSIBM DB2IDEV (only first row)


    DB2IDEV has DBADM on the restored database.

    It seems as if I should transfer more ownership?
    Any idea how to do that?

    Regards,

    L

  7. #7
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    You may use "transfer ownership" statement with " preserve privileges" clause...
    ssumit

  8. #8
    Join Date
    Nov 2013
    Posts
    4
    Thanks Ssumit,

    I did a transfer of ownership:
    TRANSFER OWNERSHIP OF TABLE creator.name to user db2idev PRESERVE PRIVILEGES;
    on all the relevant tables.

    And the reorgchk works fine!!

    Thank you so much.

    Regards,

    L

  9. #9
    Join Date
    Nov 2004
    Posts
    67
    Quote Originally Posted by ssumit View Post
    You may use "transfer ownership" statement with " preserve privileges" clause...
    ssumit,

    Thank you for writing about "transfer ownership". I didn't know it exists. I have used it today for the first time.

Posting Permissions

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