What's the appropriate way to define AUTOMATIC storage containers for a DB2 range partioned table/tablespace? I am having some issues with my definitions...Please Help

Dear Gurus,

I want to use Automatic Storage for partitioning my tablespace and table but I suspect I am doing somethig wrong. Ca someone show me an xample of a working Auto Storage definition they have used or that they know that works. What I have is as follows:

--Storage group definition:
CREATE STOGROUP "DATA01" ON '/rapmq/db2data22',
      '/rapmq/db2data23',
      '/rapmq/db2data24',
      '/rapmq/db2data25',
      '/rapmq/db2data26',
      '/rapmq/db2data27',
      '/rapmq/db2data28',
      '/rapmq/db2data29',
      '/rapmq/db2data30',
      '/rapmq/db2data31',
      '/rapmq/db2data32',
      '/rapmq/db2data33',
      '/rapmq/db2data34',
      '/rapmq/db2data12',
      '/rapmq/db2data13''
       OVERHEAD 6.725000
       DEVICE READ RATE 100.000000
       DATA TAG NONE;

CREATE STOGROUP "INDEX01" ON '/rapmq/db2idx11',
      '/rapmq/db2idx12',
      '/rapmq/db2idx13',
      '/rapmq/db2idx14',
      '/rapmq/db2idx15',
      '/rapmq/db2idx16',
      '/rapmq/db2idx17'
       OVERHEAD 6.725000
       DEVICE READ RATE 100.000000
       DATA TAG NONE;



--Tablespace efinition - This is just the first of 5 tablespace definition number 1 thru 5 (PTL01-PTL05)
CREATE LARGE TABLESPACE "TS8K_PTL01_DAT"
      IN DATABASE PARTITION GROUP "IBMDEFAULTGROUP"
      PAGESIZE 8 K
      MANAGED BY AUTOMATIC STORAGE
      AUTORESIZE YES
      EXTENTSIZE 32
      BUFFERPOOL "BP8K_PTL01"
      OVERHEAD INHERIT
      TRANSFERRATE INHERIT
      DATA TAG INHERIT
      USING STOGROUP "DATA01"
      NO FILE SYSTEM CACHING;

CREATE LARGE TABLESPACE "TS8K_PTL01_IDX"
      IN DATABASE PARTITION GROUP "IBMDEFAULTGROUP"
      PAGESIZE 8 K
      MANAGED BY AUTOMATIC STORAGE
      AUTORESIZE YES
      EXTENTSIZE 32
      BUFFERPOOL "BP8K_PTL01"
      OVERHEAD INHERIT
      TRANSFERRATE INHERIT
      DATA TAG INHERIT
      USING STOGROUP "INDEX01"
      NO FILE SYSTEM CACHING;

-- Table definition and table assignment
------------------------------------------------
-- DDL Statements for Table "APPROD"."PRODUCT_TRANS_LOG"
------------------------------------------------

CREATE TABLE "APPROD"."PRODUCT_TRANS_LOG"  (
              "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (  
                START WITH +1  
                INCREMENT BY +1  
                MINVALUE +1  
                MAXVALUE +2147483647  
                CYCLE  
                CACHE 32768  
                NO ORDER ) ,
              "POST_DATE" INTEGER NOT NULL ,
              "POST_TIME" INTEGER NOT NULL ,
              "TRAN_DATE" INTEGER ,
              "TRAN_TIME" INTEGER ,
              "TRAN_TYPE" CHAR(4 OCTETS) NOT NULL ,
              "PRODUCT_KEY" INTEGER NOT NULL ,
              "QTY" DOUBLE NOT NULL ,
              "AMOUNT" INTEGER ,
              "STORE_KEY" INTEGER NOT NULL ,
              "MEMO" VARCHAR(256 OCTETS) )  
                  PARTITION BY RANGE (STORE_KEY)
                  (STARTING FROM (0)       ENDING (20)   IN TS8K_PTL01_DAT  INDEX IN  TS8K_PTL01_IDX,
                   STARTING FROM (21)        ENDING (40)   IN TS8K_PTL02_DAT  INDEX IN  TS8K_PTL02_IDX,
                   STARTING FROM (41)        ENDING (60)   IN TS8K_PTL03_DAT  INDEX IN  TS8K_PTL03_IDX,
                   STARTING FROM (61)        ENDING (80)   IN TS8K_PTL04_DAT  INDEX IN  TS8K_PTL04_IDX,
                   STARTING FROM (81)        ENDING (100)  IN TS8K_PTL05_DAT  INDEX IN  TS8K_PTL05_IDX,
                   STARTING FROM (101)  ENDING (120)  IN TS8K_PTL01_DAT  INDEX IN  TS8K_PTL01_IDX,
                   STARTING FROM (121)  ENDING (140)  IN TS8K_PTL02_DAT  INDEX IN  TS8K_PTL02_IDX,
                   STARTING FROM (141)  ENDING (160)  IN TS8K_PTL03_DAT  INDEX IN  TS8K_PTL03_IDX,
                   STARTING FROM (161)  ENDING (180)  IN TS8K_PTL04_DAT  INDEX IN  TS8K_PTL04_IDX,
                   STARTING FROM (181)  ENDING (200)  IN TS8K_PTL05_DAT  INDEX IN  TS8K_PTL05_IDX,
                   STARTING FROM (201)  ENDING (220)  IN TS8K_PTL01_DAT  INDEX IN  TS8K_PTL01_IDX,
                   STARTING FROM (221)  ENDING (240)  IN TS8K_PTL02_DAT  INDEX IN  TS8K_PTL02_IDX,
                   STARTING FROM (241)  ENDING (260)  IN TS8K_PTL03_DAT  INDEX IN  TS8K_PTL03_IDX,
                   STARTING FROM (261)  ENDING (280)  IN TS8K_PTL04_DAT  INDEX IN  TS8K_PTL04_IDX,
                   STARTING FROM (281)  ENDING (300)  IN TS8K_PTL05_DAT  INDEX IN  TS8K_PTL05_IDX,
                   STARTING FROM (301)  ENDING (320)  IN TS8K_PTL01_DAT  INDEX IN  TS8K_PTL01_IDX,
                   STARTING FROM (321)  ENDING (340)  IN TS8K_PTL02_DAT  INDEX IN  TS8K_PTL02_IDX,
                   STARTING FROM (341)  ENDING (360)  IN TS8K_PTL03_DAT  INDEX IN  TS8K_PTL03_IDX,
                   STARTING FROM (361)  ENDING (380)  IN TS8K_PTL04_DAT  INDEX IN  TS8K_PTL04_IDX,
                   STARTING FROM (381)  ENDING (400)  IN TS8K_PTL05_DAT  INDEX IN  TS8K_PTL05_IDX,
                   STARTING FROM (401)  ENDING (420)  IN TS8K_PTL01_DAT  INDEX IN  TS8K_PTL01_IDX,
                   STARTING FROM (421)  ENDING (440)  IN TS8K_PTL02_DAT  INDEX IN  TS8K_PTL02_IDX,
                   STARTING FROM (441)  ENDING (460)  IN TS8K_PTL03_DAT  INDEX IN  TS8K_PTL03_IDX,
                   STARTING FROM (461)  ENDING (480)  IN TS8K_PTL04_DAT  INDEX IN  TS8K_PTL04_IDX,
                   STARTING FROM (481)  ENDING (500)  IN TS8K_PTL05_DAT  INDEX IN  TS8K_PTL05_IDX,
                   STARTING FROM (501)  ENDING (520)  IN TS8K_PTL01_DAT  INDEX IN  TS8K_PTL01_IDX,
                   STARTING FROM (521)  ENDING (540)  IN TS8K_PTL02_DAT  INDEX IN  TS8K_PTL02_IDX,
                   STARTING FROM (541)  ENDING (560)  IN TS8K_PTL03_DAT  INDEX IN  TS8K_PTL03_IDX,
                   STARTING FROM (561)  ENDING (580)  IN TS8K_PTL04_DAT  INDEX IN  TS8K_PTL04_IDX,
                   STARTING FROM (581)  ENDING (600)  IN TS8K_PTL05_DAT  INDEX IN  TS8K_PTL05_IDX,
                   STARTING FROM (601)  ENDING (620)  IN TS8K_PTL01_DAT  INDEX IN  TS8K_PTL01_IDX,
                   STARTING FROM (621)  ENDING (1152) IN TS8K_PTL02_DAT  INDEX IN  TS8K_PTL02_IDX,
                   STARTING FROM (1153) ENDING (1172) IN TS8K_PTL03_DAT  INDEX IN  TS8K_PTL03_IDX,
                   STARTING FROM (1173) ENDING (1192) IN TS8K_PTL04_DAT  INDEX IN  TS8K_PTL04_IDX,
                   STARTING FROM (1193) ENDING (1212) IN TS8K_PTL05_DAT  INDEX IN  TS8K_PTL05_IDX,
                   STARTING FROM (1213) ENDING (1232) IN TS8K_PTL01_DAT  INDEX IN  TS8K_PTL01_IDX,
                   STARTING FROM (1233) ENDING (1252) IN TS8K_PTL02_DAT  INDEX IN  TS8K_PTL02_IDX,
                   STARTING FROM (1253) ENDING (1272) IN TS8K_PTL03_DAT  INDEX IN  TS8K_PTL03_IDX,
                   STARTING FROM (1273) ENDING (1292) IN TS8K_PTL04_DAT  INDEX IN  TS8K_PTL04_IDX,
                   STARTING FROM (1293) ENDING (1461) IN TS8K_PTL05_DAT  INDEX IN  TS8K_PTL05_IDX,
                   STARTING FROM (1462) ENDING (1710) IN TS8K_PTL01_DAT  INDEX IN  TS8K_PTL01_IDX,
                   STARTING FROM (1711) ENDING (1730) IN TS8K_PTL02_DAT  INDEX IN  TS8K_PTL02_IDX,
                   STARTING FROM (1731) ENDING (1796) IN TS8K_PTL03_DAT  INDEX IN  TS8K_PTL03_IDX)  
              ORGANIZE BY ROW;


ALTER TABLE "APPROD"."PRODUCT_TRANS_LOG" PCTFREE 0;

 --
 CREATE UNIQUE INDEX "APPROD"."STOREKEY_PTL" ON "APPROD"."PRODUCT_TRANS_LOG"(STORE_KEY) PARTITIONED;


-- DDL Statements for Primary Key on Table "APPROD"."PRODUCT_TRANS_LOG"

ALTER TABLE "APPROD"."PRODUCT_TRANS_LOG"
      ADD CONSTRAINT "PKEY_200" PRIMARY KEY
            ("ID");



-- DDL Statements for Indexes on Table "APPROD"."PRODUCT_TRANS_LOG"

SET NLS_STRING_UNITS = 'SYSTEM';

CREATE INDEX "APPROD"."I_PTL_PK_SK_TD" ON "APPROD"."PRODUCT_TRANS_LOG"
            ("PRODUCT_KEY" ASC,
             "STORE_KEY" ASC,
             "TRAN_DATE" ASC)
                NOT PARTITIONED IN "TS8K_PTL_IDX"
            COMPRESS NO
            INCLUDE NULL KEYS DISALLOW REVERSE SCANS;

-- DDL Statements for Indexes on Table "APPROD"."PRODUCT_TRANS_LOG"

SET NLS_STRING_UNITS = 'SYSTEM';

CREATE INDEX "APPROD"."I_PTL_SK_TD_TT" ON "APPROD"."PRODUCT_TRANS_LOG"
            ("STORE_KEY" ASC,
             "TRAN_DATE" ASC,
             "TRAN_TIME" ASC)
                NOT PARTITIONED IN "TS8K_PTL_IDX"            
            COMPRESS NO
            INCLUDE NULL KEYS DISALLOW REVERSE SCANS;

-- DDL Statements for Indexes on Table "APPROD"."PRODUCT_TRANS_LOG"

SET NLS_STRING_UNITS = 'SYSTEM';

CREATE INDEX "APPROD"."I_PTL_SK_TT_TD_PK" ON "APPROD"."PRODUCT_TRANS_LOG"
            ("STORE_KEY" ASC,
             "TRAN_TYPE" ASC,
             "TRAN_DATE" ASC,
             "PRODUCT_KEY" ASC)
                NOT PARTITIONED IN "TS8K_PTL_IDX"            
            COMPRESS NO
            INCLUDE NULL KEYS ALLOW REVERSE SCANS;

-- DDL Statements for Indexes on Table "APPROD"."PRODUCT_TRANS_LOG"

SET NLS_STRING_UNITS = 'SYSTEM';

CREATE INDEX "APPROD"."I_PTL_TD_SK_PK" ON "APPROD"."PRODUCT_TRANS_LOG"
            ("TRAN_DATE" ASC,
             "STORE_KEY" ASC,
             "PRODUCT_KEY" ASC)
                NOT PARTITIONED IN "TS8K_PTL_IDX"            
            COMPRESS NO
            INCLUDE NULL KEYS DISALLOW REVERSE SCANS;

Can someone show me what is wrong with this definition?

Any hwlp will e highly appreciated.

Thanks

Enyimba
EnyimbaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi Enyimba,

What error are you getting?


Kent
EnyimbaAuthor Commented:
Kent,

I am getting system full error: LOAD FROM PRODUCT_TRANS_LOG.ixf of ixf messages ptl_messages.msg REPLACE INTO SCHEMA.PRODUCT_TRANS_LOG NONRECOVERABLE
SQL0968C  The file system is full.

I have attached the definition of the storage group, filesystem and available space, the tablespace definition and finally the table definition. What will you do differently in terms of the definitions attached?

Thanks for coming to my assistance (again!)

Enyimba
Partitioning-and-Automatic-Storage.txt
Kent OlsenDBACommented:
Can you post the LOAD statement?

And the last few lines of file ptl_messages.msg
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

EnyimbaAuthor Commented:
ko,

Here we go:
LOAD Statement:
LOAD FROM PERISCOPE_PRODUCT_TRANS_LOG.ixf of ixf messages ptl_messages.msg REPLACE INTO PERISCOPE.PRODUCT_TRANS_LOG NONRECOVERABLE
SQL0968C  The file system is full
.

ptl_messages.msg
SQL1224N  The database manager is not able to accept new requests, has
terminated all requests in progress, or has terminated the specified request
because of an error or a forced interrupt.  SQLSTATE=55032


I also have a snippet of db2diag.log about when this was happening. See attached.

Thanks

Enyimba
db2diag-log-Disk-Full-error.txt
waynezhuCommented:
db2diag.log revealed /rapmq/db2home is full.
You can monitor the space usage to find out exactly what are filling up.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kent OlsenDBACommented:
>> REPLACE INTO PERISCOPE.PRODUCT_TRANS_LOG

Are you trying to load into a new SMS tablespace?
EnyimbaAuthor Commented:
kdo,
No, I have tried loading into automatic storage with defined storage groups...what I am looking for is an example of a range partitioned table and the associated tablespace definition.

Thanks

Enyimba
Kent OlsenDBACommented:
My Apologies.  I'm involved in a couple of discussions on a similar topic and got them confused.

There's nothing magical about the tablespace definition.  Create 1 or more tablespaces as you normally would.  If you expect that there will be more data than you want to put into a single tablespace, create two or more tablespaces and assign them to their own devices.

Here's the first 25 lines of the CREATE TABLE command that you posted:

CREATE TABLE "APPROD"."PRODUCT_TRANS_LOG"  (
              "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (  
                START WITH +1  
                INCREMENT BY +1  
                MINVALUE +1  
                MAXVALUE +2147483647  
                CYCLE  
                CACHE 32768  
                NO ORDER ) ,
              "POST_DATE" INTEGER NOT NULL ,
              "POST_TIME" INTEGER NOT NULL ,
              "TRAN_DATE" INTEGER ,
              "TRAN_TIME" INTEGER ,
              "TRAN_TYPE" CHAR(4 OCTETS) NOT NULL ,
              "PRODUCT_KEY" INTEGER NOT NULL ,
              "QTY" DOUBLE NOT NULL ,
              "AMOUNT" INTEGER ,
              "STORE_KEY" INTEGER NOT NULL ,
              "MEMO" VARCHAR(256 OCTETS) )  
                  PARTITION BY RANGE (STORE_KEY)
                  (STARTING FROM (0)       ENDING (20)   IN TS8K_PTL01_DAT  INDEX IN  TS8K_PTL01_IDX,
                   STARTING FROM (21)        ENDING (40)   IN TS8K_PTL02_DAT  INDEX IN  TS8K_PTL02_IDX,
                   STARTING FROM (41)        ENDING (60)   IN TS8K_PTL03_DAT  INDEX IN  TS8K_PTL03_IDX,
                   STARTING FROM (61)        ENDING (80)   IN TS8K_PTL04_DAT  INDEX IN  TS8K_PTL04_IDX,
                   STARTING FROM (81)        ENDING (100)  IN TS8K_PTL05_DAT  INDEX IN 

Open in new window


Between the column list and the table parameters, insert the tablespace list.  That would be after line 19 in the code above.

  IN tablespace1, tablespace2, etc.

That will distribute the partitions and data across multiple tablespaces.  The range key that you've defined looks reasonable.  


Kent
EnyimbaAuthor Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.