Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

    Unanswered: "PARTITION BY RANGE" from catalog

    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"?

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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"

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I guess it depends on the DB2 version, but did you check SYSCAT.DATAPARTITIONEXPRESSION?

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    db2 "select substr(datapartitionexpression,1,20) from syscat.datapartitionexpression where tabname = 'BK'"

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



    Merci!

  5. #5
    Join Date
    Apr 2012
    Posts
    32
    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)?

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by S. BASU View Post

    I would like to know
    Have you tried reading the fine manual?

  7. #7
    Join Date
    Apr 2012
    Posts
    32
    Quote Originally Posted by n_i View Post
    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.

Posting Permissions

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