PowerDesigner reverse engineer loses partition information

[Former Member]

I am trying to learn more about partitioning, and created a test partitioned table in PowerDesigner 16.1 EBF3 (the company resists upgrades).

Below are two scripts generated by PD. The first one is from when I built the table, and the second one is a reverse engineer of the table generated by the first script on the ASE 15.7 database. Important partition information in the table is missing, and the local index is not correctly formed. I have no way of knowing if the table and index are defined correctly on the database itself, since other SQL editors (e.g. DBeaver) generates similar DDL. The only Sybase/SAP app I have to access the database is isql.

Is this fixed in the latest PD version? If so, maybe I can make the case for the IT department to finally upgrade.

ORIGINAL SCRIPT

if exists (select 1
            from  sysindexes
           where  id    = object_id('test_part')
            and   name  = 'Index_1'
            and   indid > 0
            and   indid < 255)
   drop index test_part.Index_1
go
if exists (select 1
            from  sysobjects
            where id = object_id('test_part')
            and   type = 'U')
   drop table test_part
go
/*==============================================================*/
/* Table: test_part                                             */
/*==============================================================*/
create table test_part (
   id                   int                            null,
   date                 datetime                       null,
   item                 varchar(50)                    null,
   price                money                          null
)
partition by range
 (price)
    (
         p1
        values <= (100)
        on "default",
         p2
        values <= (500)
        on "default",
         p3
        values <= (1000)
        on "default"
    )
go
/*==============================================================*/
/* Index: Index_1                                               */
/*==============================================================*/
create unique index Index_1 on test_part (
id ASC
)
local index
     p1
    on "default",
     p2
    on "default",
     p3
    on "default"
go

Reverse Engineered Script

if exists (select 1
            from  sysindexes
           where  id    = object_id('guest.test_part')
            and   name  = 'Index_1'
            and   indid > 0
            and   indid < 255)
   drop index test_part.Index_1
go
if exists (select 1
            from  sysobjects
            where id = object_id('guest.test_part')
            and   type = 'U')
   drop table guest.test_part
go
/*==============================================================*/
/* Table: test_part                                             */
/*==============================================================*/
create table guest.test_part (
   id                   int                            null,
   date                 datetime                       null,
   item                 varchar(50)                    null,
   price                money                          null
)
lock allpages
with compression = none
 partition by range
go
/*==============================================================*/
/* Index: Index_1                                               */
/*==============================================================*/
create index Index_1 on guest.test_part (
id ASC
)
go

[Former Member]

Further on this, I reversed engineered the script above and the model it produced looks correct. The model shows the partitions in both the table and index.

My primary motivation on this task is to identify which tables in the database are partitioned. At this stage, I can't even be certain that tables I am pretty sure are partitioned, really are.

Help please.

[former_member200945]

I use 16.6.1.1 to reverse engineer your original script and it works(see below).You can download 16.6 eval version to have a try. Please google search for the download site.

if exists (select 1
from sysindexes
where id = object_id('test_part')
and name = 'Index_1'
and indid > 0
and indid < 255)
drop index test_part.Index_1
go

if exists (select 1
from sysobjects
where id = object_id('test_part')
and type = 'U')
drop table test_part
go

/*==============================================================*/
/* Table: test_part */
/*==============================================================*/
create table test_part (
id int null,
date datetime null,
item varchar(50) null,
price money null
)
partition by range
(price)
(
p1
values <= (100)
on "default",
p2
values <= (500)
on "default",
p3
values <= (1000)
on "default"
)
go

/*==============================================================*/
/* Index: Index_1 */
/*==============================================================*/
create unique index Index_1 on test_part (
id ASC
)
local index
p1
on "default",
p2
on "default",
p3
on "default"
go

[Former Member]

Hi Phillip,

How are things?

Question, did you reverse engineer the script or run the script on ASE and then reverse engineer the database?

Because when I reverse engineer the script it does work. What doesn't work is when I reverse engineer the database on a table I know has partitions and local indexes.

I am looking through a database that has hundreds of tables, to see which ones are partitioned.

So please confirm that PD 16.6.1.1 properly reverse engineers the database.

There's no way I'll be able to install the eval version on my work computer. I'll have to do that at home, but then I don't have access to the ASE server at work. If you say it will work, I'll try to cobble together a solution.

[former_member200945]

I'll find a ASE 15.7 DB to run the test.

[Former Member]

Thanks Phillip. I appreciate it.

[Former Member]

Bump.

I'd like to know if the script posted above has been used to create the table and index in an ASE database, and then if PowerDesigner v16.6.x was successful in reverse engineering it and retaining the partitioning settings.

Otherwise, I can't figure out how, in a database with hundreds of tables, to find the ones that are partitioned.