asked on

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

Kent Olsen

Hi Enyimba,

What error are you getting?

Kent

Enyimba

ASKER

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 Olsen

Can you post the LOAD statement?

And the last few lines of file ptl_messages.msg

Enyimba

ASKER

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

ASKER CERTIFIED SOLUTION

waynezhu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kent Olsen

>> REPLACE INTO PERISCOPE.PRODUCT_TRANS_LOG

Are you trying to load into a new SMS tablespace?

Enyimba

ASKER

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

SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Enyimba

ASKER

Thanks