allow CREATE TABLE LIKE another table and propagate table partition limits

Today, it is possible to create a PBG tablespace and then use CREATE TABLE like another table and the new table is in the new ts.

CREATE TABLESPACE ZPOLCL IN DDB01P SEGSIZE 64;

CREATE TABLE TPOLCL LIKE DBCCLP1.TPOL IN DDB01P.ZPOLCL;

If the source table was PBG then this is a very simple method to create a new table that is physically and logically the same as the original. Theoretically, we can put the same amount of data into the new table and it should fit.

we can use INSERT or LOAD or redirectred recovery to put data into the new target!

BUT, if the original table was in a PBR tablespace then we cannot CREATE a new table like the original. The partition limits are not passed into the new tablespace.

creating the new partitioned tablespace is pretty easy. But we cannot put a table into the new ts if the new table is defined with CREATE TABLE LIKE another table.

CREATE TABLESPACE ZPOLIL IN DDB01P SEGSIZE 64 NUMPARTS 7;

CREATE TABLE TPOLIL LIKE DBCILP1.TPOL IN DDB01P.ZPOLIL;

When you use the new table, even to do a new a simple SELECT COUNT(*) you get sqlcode -653

SELECT COUNT(*) AS CNT FROM DBCDBP1.TPOLIL;

DSNT408I SQLCODE = -653, ERROR: TABLE DBCDBP1.TPOLIL IN PARTITIONED TABLE SPACE DDB01P.ZPOLIL IS NOT AVAILABLE BECAUSE ITS PARTITIONED INDEX HAS NOT BEEN CREATED

https://www.ibm.com/docs/en/db2-for-zos/12?topic=esc-653

Obviously, I do not want to use partitioning index. That is old-fashioned.

It would be nice if DB2 DDL for CREATE TABLE LIKE "another" would have some syntax to propagate the table partition limits.

for example: CREATE TABLE TPOLIL LIKE DBCILP1.TPOL IN DDB01P.ZPOLIL WITH PARTITION LIMITS;

The workaround today is to use some other tool to GENERATE DDL of the original table with the partition limits and then manually tweak the generated DDL. This is fine... but it is cut and paste and can have human errors.

It seems logical that Db2 "CREATE TABLE like another table" should more easily handle partitioned tables!

I really like CREATE TABLE LIKE ANOTHER TABLE... it should handle more scenarios!

this is what I must do today

Create table DBCDBP1.TPOLIL (

COL01 CHAR(2) NOT NULL ,

COL02 CHAR(10) NOT NULL ,

<all other columns -> 100s of columns>

COL279 DATE ,

COL280 DATE ,

COL281 CHAR(2) NOT NULL

With Default )

in DDB01P.ZPOLIL

Partition by (COL01, COL02)

(Partition 1 ending at ('A1','5219999999') ,

Partition 2 ending at ('A1','5309999999') ,

Partition 3 ending at ('A1','5399999999') ,

Partition 4 ending at ('A1','5709999999') ,

Partition 5 ending at ('A1','5859999999') ,

Partition 6 ending at ('A1','7409999999') ,

Partition 7 ending at (MAXVALUE,MAXVALUE) )

;