Summary : SAP
PowerDesigner provides a solution to migrate schemas to ASE from a number of
other vendors RDBMS.
A number of options exist for extracting a database schema
from Oracle and converting to ASE. SAP
PowerDesigner will complete this conversion
- taking in a schema (or schemas) directly from an Oracle database (and
many other DBMSs) and providing a
conversion of the DDL to ASE format.
Trying this with the Oracle 11g R2 HR example schema (not the biggest schema in the
world – but we are looking at the logic of this process here). I downloaded PowerDesigner version 16 (you
get a 15 day free trial).
To get connectivity to Oracle (which I run on an Centos VM)
I had to install the Oracle client software so as to get the ODBC driver.
Anyway once this is setup I loaded PowerDesigner and created a new project.
Extracting the Schema From Oracle
·
Select File -> Reverse Engineer -> Database
·
Select the relevant Oracle version
·
Select/configure data source (ODBC)
·
Select the objects you require :-
And we have a schema extracted to PowerDesigner :-
Lets drill down a bit – what does PowerDesigner show as the
Oracle DDL ?
drop
trigger HR.SECURE_EMPLOYEES
/
drop
trigger HR.UPDATE_JOB_HISTORY
/
alter
table HR.DEPARTMENTS
drop constraint DEPT_MGR_FK
/
alter
table HR.EMPLOYEES
drop constraint EMP_DEPT_FK
/
alter
table HR.EMPLOYEES
drop constraint EMP_JOB_FK
/
alter
table HR.EMPLOYEES
drop constraint EMP_MANAGER_FK
/
alter
table HR.JOB_HISTORY
drop constraint JHIST_EMP_FK
/
drop
index HR.EMP_NAME_IX
/
drop
index HR.EMP_MANAGER_IX
/
drop
index HR.EMP_JOB_IX
/
drop
index HR.EMP_DEPARTMENT_IX
/
drop
table HR.EMPLOYEES cascade constraints
/
/*==============================================================*/
/* Table:
EMPLOYEES */
/*==============================================================*/
create
table HR.EMPLOYEES
(
EMPLOYEE_ID NUMBER(6) not null,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25) constraint EMP_LAST_NAME_NN not null,
EMAIL VARCHAR2(25) constraint EMP_EMAIL_NN not null,
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE constraint EMP_HIRE_DATE_NN
not null,
JOB_ID VARCHAR2(10) constraint EMP_JOB_NN not null,
SALARY NUMBER(8,2)
constraint EMP_SALARY_MIN check (SALARY
is null or (SALARY > 0)),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4),
constraint EMP_EMP_ID_PK primary key
(EMPLOYEE_ID)
using index
pctfree 10
initrans 2
storage
(
initial 64K
next 1024K
minextents 1
maxextents unlimited
)
tablespace EXAMPLE
nologging,
constraint EMP_EMAIL_UK unique (EMAIL)
using index
pctfree 10
initrans 2
storage
(
initial 64K
next 1024K
minextents 1
maxextents unlimited
)
tablespace EXAMPLE
nologging
)
pctfree
10
initrans
1
storage
(
initial 64K
next 1024K
minextents 1
maxextents unlimited
)
tablespace
EXAMPLE
nologging
nocompress
monitoring
noparallel
/
comment
on table HR.EMPLOYEES is
'employees
table. Contains 107 rows. References with departments,
jobs, job_history
tables. Contains a self reference.'
/
comment
on column HR.EMPLOYEES.EMPLOYEE_ID is
'Primary
key of employees table.'
/
comment
on column HR.EMPLOYEES.FIRST_NAME is
'First
name of the employee. A not null column.'
/
comment
on column HR.EMPLOYEES.LAST_NAME is
'Last
name of the employee. A not null column.'
/
comment
on column HR.EMPLOYEES.EMAIL is
'Email id
of the employee'
/
comment
on column HR.EMPLOYEES.PHONE_NUMBER is
'Phone
number of the employee; includes country code and area code'
/
comment
on column HR.EMPLOYEES.HIRE_DATE is
'Date
when the employee started on this job. A not null column.'
/
comment
on column HR.EMPLOYEES.JOB_ID is
'Current
job of the employee; foreign key to job_id column of the
jobs
table. A not null column.'
/
comment
on column HR.EMPLOYEES.SALARY is
'Monthly
salary of the employee. Must be greater
than zero
(enforced by constraint emp_salary_min)'
/
comment
on column HR.EMPLOYEES.COMMISSION_PCT is
'Commission
percentage of the employee; Only employees in sales
department
elgible for commission percentage'
/
comment
on column HR.EMPLOYEES.MANAGER_ID is
'Manager
id of the employee; has same domain as manager_id in
departments
table. Foreign key to employee_id column of employees table.
(useful
for reflexive joins and CONNECT BY query)'
/
comment
on column HR.EMPLOYEES.DEPARTMENT_ID is
'Department
id where employee works; foreign key to department_id
column of
the departments table'
/
/*==============================================================*/
/* Index:
EMP_DEPARTMENT_IX */
/*==============================================================*/
create
index HR.EMP_DEPARTMENT_IX on HR.EMPLOYEES (
DEPARTMENT_ID ASC
)
pctfree
10
initrans
2
storage
(
initial 64K
next 1024K
minextents 1
maxextents unlimited
buffer_pool default
)
tablespace
EXAMPLE
nologging
/
/*==============================================================*/
/* Index:
EMP_JOB_IX */
/*==============================================================*/
create
index HR.EMP_JOB_IX on HR.EMPLOYEES (
JOB_ID ASC
)
pctfree
10
initrans
2
storage
(
initial 64K
next 1024K
minextents 1
maxextents unlimited
buffer_pool default
)
tablespace
EXAMPLE
nologging
/
/*==============================================================*/
/* Index:
EMP_MANAGER_IX */
/*==============================================================*/
create
index HR.EMP_MANAGER_IX on HR.EMPLOYEES (
MANAGER_ID ASC
)
pctfree
10
initrans
2
storage
(
initial 64K
next 1024K
minextents 1
maxextents unlimited
buffer_pool default
)
tablespace
EXAMPLE
nologging
/
/*==============================================================*/
/* Index:
EMP_NAME_IX */
/*==============================================================*/
create
index HR.EMP_NAME_IX on HR.EMPLOYEES (
LAST_NAME ASC,
FIRST_NAME ASC
)
pctfree
10
initrans
2
storage
(
initial 64K
next 1024K
minextents 1
maxextents unlimited
buffer_pool default
)
tablespace
EXAMPLE
nologging
/
alter
table HR.EMPLOYEES
add constraint EMP_MANAGER_FK foreign key
(MANAGER_ID)
references HR.EMPLOYEES (EMPLOYEE_ID)
not
deferrable
/
create
trigger HR.SECURE_EMPLOYEES before insert or update or delete on HR.EMPLOYEES
REFERENCING NEW AS NEW OLD AS OLD
BEGIN
secure_dml;
END
secure_employees;
/
create
trigger HR.UPDATE_JOB_HISTORY
after update on HR.EMPLOYEES
REFERENCING NEW AS NEW OLD AS OLD
BEGIN
add_job_history(:old.employee_id,
:old.hire_date, sysdate,
:old.job_id,
:old.department_id);
END;
/
OK – lets generate an ASE Physical model :-
·
Tools -> Generate Physical Data Model
·
Select your ASE version as the DBMS
We now have an ASE Physical Data model. Lets have a look at the EMPLOYEES
table :-
if exists
(select 1
from sysobjects
where id =
object_id('HR.SECURE_EMPLOYEES')
and type = 'TR')
drop trigger HR.SECURE_EMPLOYEES
go
if exists
(select 1
from sysobjects
where id =
object_id('HR.UPDATE_JOB_HISTORY')
and type = 'TR')
drop trigger HR.UPDATE_JOB_HISTORY
go
if exists
(select 1
from
sysobjects c
join sysconstraints s on (s.constrid = c.id and
s.tableid = object_id('HR.DEPARTMENTS'))
where name = 'DEPT_MGR_FK' and type
= 'RI')
alter table HR.DEPARTMENTS
drop constraint DEPT_MGR_FK
go
if exists
(select 1
from sysobjects c
join sysconstraints s on (s.constrid = c.id and
s.tableid = object_id('HR.EMPLOYEES'))
where name = 'EMP_DEPT_FK' and type
= 'RI')
alter table HR.EMPLOYEES
drop constraint EMP_DEPT_FK
go
if exists
(select 1
from sysobjects c
join sysconstraints s on (s.constrid = c.id and
s.tableid = object_id('HR.EMPLOYEES'))
where name = 'EMP_JOB_FK' and type
= 'RI')
alter table HR.EMPLOYEES
drop constraint EMP_JOB_FK
go
if exists
(select 1
from sysobjects c
join sysconstraints s on (s.constrid = c.id and
s.tableid = object_id('HR.EMPLOYEES'))
where name = 'EMP_MANAGER_FK' and
type = 'RI')
alter table HR.EMPLOYEES
drop constraint EMP_MANAGER_FK
go
if exists
(select 1
from sysobjects c
join sysconstraints s on (s.constrid = c.id and
s.tableid = object_id('HR.JOB_HISTORY'))
where name = 'JHIST_EMP_FK' and type
= 'RI')
alter table HR.JOB_HISTORY
drop constraint JHIST_EMP_FK
go
if exists
(select 1
from sysindexes
where id =
object_id('HR.EMPLOYEES')
and name
= 'EMP_NAME_IX'
and indid > 0
and
indid < 255)
drop index EMPLOYEES.EMP_NAME_IX
go
if exists
(select 1
from sysindexes
where id =
object_id('HR.EMPLOYEES')
and name
= 'EMP_MANAGER_IX'
and indid > 0
and indid < 255)
drop index EMPLOYEES.EMP_MANAGER_IX
go
if exists
(select 1
from sysindexes
where id =
object_id('HR.EMPLOYEES')
and name
= 'EMP_JOB_IX'
and indid > 0
and indid < 255)
drop
index EMPLOYEES.EMP_JOB_IX
go
if exists
(select 1
from sysindexes
where id =
object_id('HR.EMPLOYEES')
and name
= 'EMP_DEPARTMENT_IX'
and indid > 0
and indid < 255)
drop index EMPLOYEES.EMP_DEPARTMENT_IX
go
if exists
(select 1
from sysobjects
where id =
object_id('HR.EMPLOYEES')
and type = 'U')
drop table HR.EMPLOYEES
go
/*==============================================================*/
/* Table:
EMPLOYEES */
/*==============================================================*/
create
table HR.EMPLOYEES (
EMPLOYEE_ID numeric(6) not null,
FIRST_NAME varchar(20) null,
LAST_NAME varchar(25) not null,
EMAIL varchar(25) not null,
PHONE_NUMBER varchar(20) null,
HIRE_DATE datetime not null,
JOB_ID varchar(10) not null,
SALARY numeric(8,2) null
constraint EMP_SALARY_MIN check (SALARY
is null or (SALARY > 0)),
COMMISSION_PCT numeric(2,2) null,
MANAGER_ID numeric(6) null,
DEPARTMENT_ID numeric(4) null,
constraint EMP_EMP_ID_PK primary key
nonclustered (EMPLOYEE_ID),
constraint EMP_EMAIL_UK unique (EMAIL)
)
go
/*==============================================================*/
/* Index:
EMP_DEPARTMENT_IX */
/*==============================================================*/
create
index EMP_DEPARTMENT_IX on HR.EMPLOYEES (
DEPARTMENT_ID
ASC
)
go
/*==============================================================*/
/* Index:
EMP_JOB_IX */
/*==============================================================*/
create
index EMP_JOB_IX on HR.EMPLOYEES (
JOB_ID
ASC
)
go
/*==============================================================*/
/* Index:
EMP_MANAGER_IX */
/*==============================================================*/
create
index EMP_MANAGER_IX on HR.EMPLOYEES (
MANAGER_ID
ASC
)
go
/*==============================================================*/
/* Index:
EMP_NAME_IX */
/*==============================================================*/
create
index EMP_NAME_IX on HR.EMPLOYEES (
LAST_NAME
ASC,
FIRST_NAME
ASC
)
go
alter
table HR.EMPLOYEES
add constraint EMP_MANAGER_FK foreign key
(MANAGER_ID)
references HR.EMPLOYEES (EMPLOYEE_ID)
go
create
trigger HR.SECURE_EMPLOYEES insert or
update or delete on HR.EMPLOYEES REFERENCING NEW AS NEW OLD AS OLD
BEGIN
secure_dml;
END
secure_employees;
go
create
trigger HR.UPDATE_JOB_HISTORY update
on HR.EMPLOYEES REFERENCING NEW AS NEW OLD AS OLD
BEGIN
add_job_history(:old.employee_id, :old.hire_date,
sysdate,
:old.job_id,
:old.department_id);
END;
go
Run this into ASE and we get a table (after creating a HR
user).
1>
sp_help "HR.EMPLOYEES"
2> go
Name Owner Object_type Object_status Create_date
------------------ ----------
---------------------- --------------------------
--------------------------------------
EMPLOYEES HR user table -- none -- Feb 11 2012 12:58PM
(1 row
affected)
Column_name Type Length Prec
Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity
---------------------------- ----------------
------------ -------- ---------- ---------- ------------------------
---------------------------- -------------------------------- --------------------------------------------
--------------------
EMPLOYEE_ID numeric 4 6 0 0
NULL NULL NULL NULL 0
FIRST_NAME varchar 20 NULL
NULL 1 NULL NULL NULL NULL 0
LAST_NAME varchar 25 NULL
NULL 0 NULL NULL NULL NULL 0
EMAIL varchar 25 NULL
NULL 0 NULL NULL NULL NULL
0
PHONE_NUMBER varchar 20 NULL
NULL 1 NULL NULL NULL NULL
0
HIRE_DATE datetime 8 NULL
NULL 0 NULL NULL NULL NULL
0
JOB_ID varchar 10 NULL
NULL 0 NULL NULL NULL NULL
0
SALARY numeric 5 8 2 1
NULL
EMP_SALARY_MIN
NULL
NULL
0
COMMISSION_PCT numeric 2 2 2 1
NULL NULL NULL NULL
0
MANAGER_ID numeric 4 6 0 1
NULL NULL NULL NULL
0
DEPARTMENT_ID numeric 3 4 0 1
NULL NULL NULL NULL
0
Object
has the following indexes
index_name index_keys
index_description
index_max_rows_per_page index_fillfactor index_reservepagegap index_created index_local
----------------------------------
--------------------------------------------
----------------------------------------
---------------------------------------------- --------------------------------
---------------------------------------- --------------------------------------
------------------------
EMP_EMP_ID_PK EMPLOYEE_ID nonclustered,
unique
0
0 0 Feb 11 2012 12:58PM Global Index
EMP_EMAIL_UK EMAIL
nonclustered, unique
0 0 0 Feb 11 2012 12:58PM Global Index
EMP_DEPARTMENT_IX DEPARTMENT_ID nonclustered
0
0
0 Feb 11 2012
12:58PM Global
Index
EMP_JOB_IX JOB_ID
nonclustered
0
0
0 Feb 11 2012
12:58PM Global Index
EMP_MANAGER_IX MANAGER_ID
nonclustered
0
0
0 Feb 11 2012 12:58PM Global Index
EMP_NAME_IX LAST_NAME,
FIRST_NAME
nonclustered
0 0 0 Feb 11 2012 12:58PM Global Index
(6 rows
affected)
Got a couple errors – the triggers failed to load – well
they were not converted to T-SQL.
PowerDesigner does not attempt a conversion of the SQL code. But it is
useful that they are brought over as part of the schema.
