Skip to Content
avatar image
Former Member

PowerDesigner reverse engineer loses partition information

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
Add comment
10|10000 characters needed characters exceeded

  • 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.

  • 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.

  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    Nov 01, 2016 at 05:24 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 02, 2016 at 11:09 PM

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

    Add comment
    10|10000 characters needed characters exceeded