Difficulty in partitioning table in DB2 for z/OS 8.1

I am trying to partition a table in DB2 for z/OS ( Version 8.1) and get an SQL
Error Code of -20183. <br />
<br />
Can you help as to why I am unable to partition? <br />
<br />
The table space in which I am creating table is not defined to be partition by
growth. It is just a regular Table space.<br />
<br />
I use UDB for Windows to connect to DB2 8.1 running under Z/OS and execute the
following DDL ( snippet of DDL given below ) .<br />
<br />
CREATE TABLE S_TRANSACTION_E10_PARTITIONED<br />
(<br />
 TRANSACTION_ID  NUMERIC(19)  NOT NULL <br />
  GENERATED BY DEFAULT <br />
  AS IDENTITY (<br />
   START WITH 1,<br />
   INCREMENT BY 1,<br />
   NO CACHE,<br />
   NO CYCLE<br />
   ),<br />
 PARENT_TRANSACTION_ID  NUMERIC(19)  ,<br />
..<br />
PROCESS_DATE DATE<br />
.. <br />
)<br />
<p />
<br />
PARTITION BY (PROCESS_DATE)<br />
<br />
( PARTITION 0 ENDING AT ('12/31/2007'),<br />
PARTITION 1 ENDING AT ('01/31/2008'),<br />
PARTITION 2 ENDING AT ('02/28/2008'),<br />
PARTITION 3 ENDING AT ('03/31/2008'),<br />
PARTITION 4 ENDING AT ('04/30/2008'),<br />
PARTITION 5 ENDING AT ('05/31/2008'),<br />
PARTITION 6 ENDING AT ('06/30/2008'),<br />
PARTITION 7 ENDING AT ('07/31/2008'),<br />
PARTITION 8 ENDING AT ('08/31/2008'),<br />
PARTITION 9 ENDING AT ('09/30/2008'),<br />
PARTITION 10 ENDING AT ('10/31/2008'),<br />
PARTITION 11 ENDING AT ('11/30/2008'),<br />
PARTITION 12 ENDING AT ('12/31/2008') )<br />
 <br />
 IN SPDB01.SPE10<br />
;<br />
<br />
When I run this, I get the following error message:<br />
<br />
CREATE TABLE S_TRANSACTION_E10_PARTITIONED ( TRANSACTION_ID  NUMERIC(19)  NOT
NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH 1, INCREMENT BY 1, NO CACHE,
NO CYCLE ), PARENT_TRANSACTION_ID  NUMERIC(19) ..... ) PARTITION BY
(PROCESS_DATE) ( PARTITION 0 ENDING AT ('12/31/2007'), PARTITION 1 ENDING AT
('01/31/2008'), PARTITION 2 ENDING AT ('02/28/2008'), PARTITION 3 ENDING AT
('03/31/2008'), PARTITION 4 ENDING AT ('04/30/2008'), PARTITION 5 ENDING AT
('05/31/2008'), PARTITION 6 ENDING AT ('06/30/2008'), PARTITION 7 ENDING AT
('07/31/2008'), PARTITION 8 ENDING AT ('08/31/2008'), PARTITION 9 ENDING AT
('09/30/2008'), PARTITION 10 ENDING AT ('10/31/2008'), PARTITION 11 ENDING AT
('11/30/2008'), PARTITION 12 ENDING AT ('12/31/2008') ) IN SPDB01.SPE10<br
/>
DB21034E  The command was processed as an SQL statement because it was not a
<br />
valid Command Line Processor command.  During SQL processing it returned:<br
/>
SQL0969N  There is no message text corresponding to SQL error "-20183"
in the <br />
message file on this workstation.  The error was returned from module <br
/>
"DSNXIPKY" with original tokens
"TSO#VXS.S_TRANSACTION_E10_PARTITIONED".  <br />
SQLSTATE=428FT<br />
<br />
SQL10007N Message "20183" could not be retrieved.  Reason code:
"4".<br />
<hr />
Basically it returns error sql code of -20183 which reads as below as per IBM's
manual:<br />
<br />
THE PARTITIONED, ADD PARTITION, ADD PARTITIONING KEY, ALTER PARTITION, ROTATE
PARTITION, OR PARTITION BY RANGE CLAUSE SPECIFIED ON CREATE OR ALTER FOR name IS
NOT VALID<br />
Explanation:<br />
<br />
The PARITIONED, ADD PARTITION, ADD PARTITIONING KEY, ALTER PARTITION, and ROTATE
PARTITION clauses cannot be specified on ALTER TABLE, CREATE INDEX, or CREATE
TABLE if:<br />
<br />
    * The table is a non-partitioned table.<br />
    * The table is a materialized query table.<br />
    * A materialized query table is defined on this table.<br />
    * |The table is in a partition-by-growth table |space. |<br />
<br />
      If a PARTITION BY clause was specified and the table is in a
partition-by-growth, |then the clause must be PARTITION BY GROWTH.<br />
<br />
Additionally:<br />
<br />
    * |The ADD PARTITIONING KEY clause cannot be specified if the |table is
already complete by having established either table-based partitioning |or
index-based partitioning.<br />
    * |The ROTATE PARTITION clause cannot be specified if: |<br />
          o |The table definition is incomplete.<br />
          o |The table contains only one partition.<br />
<br />
System action:<br />
<br />
The statement cannot be executed.<br />
SQLSTATE:<br />
<br />

There could be a problem in the definition of the table space. You must define
there the number of partitions like this:<br />
<br />
CREATE TABLESPACE SPE10<br />
  IN SPDB01<br />
       USING STOGROUP ???	<br />
		PRIQTY     -1<br />
     	        SECQTY    -1  <br />
		<b>NUMPARTS 10</b><br />
	        CLOSE     NO<br />
	       ;<br />
<br />

Yes, definitely.<br />
<br />
The correct sequence is Tablespace Creation ---&gt; Table Creation
---&gt; Partitioning Index Creation ---&gt; Non Partitioned Index
Creation.<br />
<br />
In our case this should sound like:<br />
<br />
CREATE TABLESPACE <i>TSNAME</i><br />
IN <i>DBNAME</i><br />
USING STOGROUP <i>sgname</i><br />
PRIQTY <i>quantity1</i><br />
SECQTY <i>quantity2</i><br />
NUMPARTS 10<br />
BUFFERPOOL BP1<br />
CLOSE NO<br />
;<br />
<br />
CREATE TABLE S_TRANSACTION_E10_PARTITIONED<br />
(<br />
TRANSACTION_ID NUMERIC(19) NOT NULL<br />
GENERATED BY DEFAULT<br />
AS IDENTITY (<br />
START WITH 1,<br />
INCREMENT BY 1,<br />
NO CACHE,<br />
NO CYCLE<br />
),<br />
PARENT_TRANSACTION_ID NUMERIC(19) ,<br />
..<br />
PROCESS_DATE DATE<br />
..<br />
) IN <i>DBNAME.TSNAME</i> ;<br />
<p />
CREATE INDEX <i>INDEXNAME</i> <br />
ON S_TRANSACTION_E10_PARTITIONED (EMPNO ASC) <br />
USING STOGROUP <i>sgname</i><br />
PRIQTY <i>quantity1</i><br />
SECQTY <i>quantity2</i><br />
ERASE NO<br />
CLUSTER<br />
PARTITION BY (PROCESS_DATE)<br />
(PARTITION 0 ENDING AT ('12/31/2007'),<br />
PARTITION 1 ENDING AT ('01/31/2008'),<br />
PARTITION 2 ENDING AT ('02/28/2008'),<br />
PARTITION 3 ENDING AT ('03/31/2008'),<br />
PARTITION 4 ENDING AT ('04/30/2008'),<br />
PARTITION 5 ENDING AT ('05/31/2008'),<br />
PARTITION 6 ENDING AT ('06/30/2008'),<br />
PARTITION 7 ENDING AT ('07/31/2008'),<br />
PARTITION 8 ENDING AT ('08/31/2008'),<br />
PARTITION 9 ENDING AT ('09/30/2008'),<br />
PARTITION 10 ENDING AT ('10/31/2008'),<br />
PARTITION 11 ENDING AT ('11/30/2008'),<br />
PARTITION 12 ENDING AT ('12/31/2008') )<br />
BUFFERPOOL BP2 <br />
CLOSE NO<br />

Many Thanks both George &#38; Andy,<br />
<br />

Hello,<br />
Table space SPDB01.SPE10 has to be a 12 partition table space.<br />
You also start with partition 1 not 0.<br />
<br />
Regards,<br />