Hello,


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 2.0.1.875 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

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.

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.

Marcus, Thanks for a solution, as always!

n_i - thanks but no thanks!