Table is created using:

CREATE TABLE BK
(CUSTOMER VARCHAR(80),
REGION CHAR(5),
PURCHASEDATE DATE,
PURCHASEYEARMONTH INTEGER)
DISTRIBUTE BY HASH (REGION)
ORGANIZE BY DIMENSIONS (PURCHASEYEARMONTH)
PARTITION BY RANGE("PURCHASEDATE")
(PART "PD022009" STARTING('2009-02-01') ENDING('2009-02-28') IN "PD022009",
PART "PD032009" STARTING('2009-03-01') ENDING('2009-03-31') IN "PD032009");



To check if a table was created using "DISTRIBUTE BY HASH" and "ORGANIZE BY DIMENSIONS":

$ db2 "select PARTITION_MODE, CLUSTERED from syscat.tables where TABNAME = 'BK'"

PARTITION_MODE CLUSTERED
-------------- ---------
H Y


To get a list of columns used with "DISTRIBUTE BY HASH":

$ db2 "select colname from syscat.columns where tabname = 'BK' and partkeyseq != 0"

COLNAME
--------------------------------------------------------------------------------------------------------------------------------
REGION


To get a list of columns used with "ORGANIZE BY DIMENSIONS":

$ db2 "select colname from syscat.coluse where tabname = 'BK'"

COLNAME
--------------------------------------------------------------------------------------------------------------------------------
PURCHASEYEARMONTH


To get a list of table partitions and tablespaces they're in:

$ db2 "select substr(datapartitionname,1,15) as TABLEPART,tbspaceid from syscat.datapartitions where tabname = 'BK'"

TABLEPART TBSPACEID
--------------- -----------
PD022009 10
PD032009 11



Is there a column in the catalog that will tell me explicitly whether a table is range partitioned or not and column used with "PARTITION BY RANGE"?

I can't seem to find a column that will tell me explicitly if it's range partitioned.

I think the following can also be used to check if a table is range partitioned:

$ db2 "select distinct substr(TABNAME,1,20) from syscat.datapartitions where datapartitionname != 'PART0'"

db2 "select substr(TABNAME,1,20) from syscat.tables where TBSPACE is NULL and TYPE = 'T'"


Does anyone know how to get a column used with "PARTITION BY RANGE"

I guess it depends on the DB2 version, but did you check SYSCAT.DATAPARTITIONEXPRESSION?

db2 "select substr(datapartitionexpression,1,20) from syscat.datapartitionexpression where tabname = 'BK'"

1
--------------------
PURCHASEDATE



Merci!

As regards the syntax below:
...
PARTITION BY RANGE("PURCHASEDATE")
(PART "PD022009" STARTING('2009-02-01') ENDING('2009-02-28') IN "PD022009",
PART "PD032009" STARTING('2009-03-01') ENDING('2009-03-31') IN "PD032009");


I would like to know, if I don't specify the ENDING(VALUE) for each partition, does it automatically assume the ending value to be the next STARTING(VALUE)?

Have you tried reading the fine manual?

No I haven't. I confess...and that's why I didn't start a new thread. Since I found one existing I just posted at the tail.
Also I had to complete editing a script file urgently and thus I posted here hoping to get a quickfix. I will however read the manual ASAP.