Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010

    Unanswered: URGENT! Distribution information!


    I am deeply distressed now (like you care right ) not being able to find a mechanism to conclusively tell the distribution key on a table. Again, I mean the distribution key and not the partition key.

    Quick background:

    Our DB environment was upgraded from 7 to 9 some time back. Now when I use TOAD I see table scripts stating "PARTITIONING USING (X) BY HASHING" but no "DISTRIBUTE BY" clause. My understanding was that this would cause the DBMS to go off the first column of the primary key (if there was one, if not, first non-long column in the table) for distributing data.

    However, the DBA mentioned that TOAD was misleading and that the
    "PARTITIONING USING (X) BY HASHING" clause was in fact being processed by the DBMS as a "DISTRIBUTE BY" clause. He suggested that the Toad script was only a result of backward compatibility.

    My question - is there something within the database that I could use to find this information without getting misled by TOAD?

    Caution - dbpartitionnum is not a solution as it only helps simulate distribution with the argument key. Reading suggests sqlugtpi API is an option, but I have no idea how I use it. (I recognize one option is to trust my DBA, but I am only trying to be independent doing this analysis instead of bothering the DBA every time).

    As always, your help is much appreciated!

    - G

  2. #2
    Join Date
    May 2003
    Use db2look to pull the DDL of object in a database. You can get all objects or objects for a pariticular table. See the Command Reference manual for details on db2look.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    Why is it urgent?

    "PARTITIONING USING (X) BY HASHING" is DB2 v7 syntax, which will not work with more recent DB2 versions, obviously. It means the same thing, however, as "DISTRIBUTE BY HASH (X)". All tools, including Toad and db2look, obtain this information from the catalog tables. SYSCAT.COLUMNS.PARKEYSEQ indicates distribution columns for each table.

  4. #4
    Join Date
    Oct 2010
    Marcus, Thanks for a solution, as always!

    n_i - thanks but no thanks!

Posting Permissions

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