Reverse Engineering issues
Roger Carlsson
a couple of weeks ago I wrote to this newsgroup regarding "RE" issues,
but not a single answer/reply!!!!
I will post those questions again, but first i must say i agree with
those who complaining about this product, e.g. the report generator.
We have to put o lot of efforts in adjusting SQL in the DBMS resource
file e.g. instead of using system catalog views (recommended by IBM)
Sybase using the base system catalog tables (SQL query to reverse object
check constraints), using syscat.procedures instead of syscat.routines etc.
Still, we can not updating the physical model by using the re tool in
PowerDesigner, since the result is incorrect.
Reverse Engineering issues (PowerDesigner):
- For some index, the property UNIQUE is ignored.
- For some tables, check constraints are ignored.
- For every table with more than one check constraint, the first check
constraint is defined as a check constraint but the rest is defined as
business rules.
- If you reverse once again, the first check constraint will be
defined as a business rule and hence we have duplicating CC.
- By default, only procedures are listed in the window "Database
Reverse Engineering Selection Window".
- By editing DBMS properties we can list functions and procedures, but
we can only retrieve SQL statements for either funtions or procedures
(depending on which system variables, FUNC/PROC, that was used)
- We cannot reverse custom comments for views/MQT:s
- Number of columns is incorrect in Foreign keys, that refer to
Alternate keys.
- Triggers for views are ignored.
We have created a sample db and pdm, that exemplifies the above problems.
But before we sending it to Sybase, maybe someone could help us with som
issues or?
We are using PD version 12.5 and DBMS DB2 UDB 9.x Common Server.
/roger
Mark Brady
> Hello,
> a couple of weeks ago I wrote to this newsgroup regarding "RE" issues,
> but not a single answer/reply!!!!
If there's a bug, if UNIQUE is ignored during RE, what do you want the
community to say?
Perhaps we could offer a guide to submitting a bug, but that would
sound didactic. Bugs are bugs - submit them. You'd be amazed how fast
you'll see them repaired.
> I will post those questions again, but first i must say i agree with
> those who complaining about this product, e.g. the report generator.
I think even Sybase recognizes there's much room for improvement.
> We have to put o lot of efforts in adjusting SQL in the DBMS resource
> file e.g. instead of using system catalog views (recommended by IBM)
> Sybase using the base system catalog tables (SQL query to reverse object
> check constraints), using syscat.procedures instead of syscat.routines etc.
Yes, agreed. I've mentioned the same thing to Engineers about Oracle.
There are packages which produce XML descriptions of objects. This
would be FAR more accurate than trolling through catalog views. But
these packages post-date the catalog views. PD grew up without them.
It will take time to change over. I think they will when they see how
the accuracy can be increased.
> Still, we can not updating the physical model by using the re tool in
> PowerDesigner, since the result is incorrect.
>
> Reverse Engineering issues (PowerDesigner):
> - For some index, the property UNIQUE is ignored.
Sounds like a bug.
> - For some tables, check constraints are ignored.
Sounds like a bug.
> - For every table with more than one check constraint, the first check
> constraint is defined as a check constraint but the rest is defined as
> business rules.
Sounds like a bug.
> - If you reverse once again, the first check constraint will be
> defined as a business rule and hence we have duplicating CC.
Sounds like a bug.
> - By default, only procedures are listed in the window "Database
> Reverse Engineering Selection Window".
there are multiple tabs at the bottom of the window, did you see them?
or is that the only tab?
> - By editing DBMS properties we can list functions and procedures, but
> we can only retrieve SQL statements for either funtions or procedures
> (depending on which system variables, FUNC/PROC, that was used)
Sounds like a bug.
> - We cannot reverse custom comments for views/MQT:s
Sounds like a bug.
> - Number of columns is incorrect in Foreign keys, that refer to
> Alternate keys.
Sounds like a bug.
> - Triggers for views are ignored.
Sounds like a bug.
>
> We have created a sample db and pdm, that exemplifies the above problems.
> But before we sending it to Sybase, maybe someone could help us with som
> issues or?
The best you could hope from us is you post the PDM, the script to
build the database and the issue and someone is willing to replicate
it, to see if they have it too, but that's what support will do
anyways.
I know it's not the answer you wanted, it's just the answer you were
desperately avoiding.
Jim Egan [TeamSybase]
It is likely that most of your issues could be problems within the DBMS definition. I
don't see most of these problems in Oracle so it is not the overall product that is causing
your issue, it is the way the index is defined in the XDB file. Often times this is
something that you can fix yourself - though you shouldn't have to if Sybase has the DBMS
definition right.
--
Jim Egan [TeamSybase]
billbunke
Although this feature should work more reliably than you've
experienced, and that is the responsibility of Sybase, I've
taken comfort in better understanding what a tool is or is
not doing at a detailed level. I've found that a more
detailed diagnosis can help lead to a quicker and more
effective response and solution.
Towards that end, I was wondering if you might try and
directly run the R-E SQL from the .xdb file, to see what
catalog results are returned. Examples might be the
IDX:SqlListQuery, TBL:SqlChckQuery, and others that relate
to your problems.
Just a thought.
Bill
Iaroslav
> the first check constraint is defined as a check
> constraint but the rest is defined as business rules.
Very similar problem was reported by me for Oracle DBMS one
year ago with no result. The respond was that this is not a
bug:
> When reverse engineering, PD via script, PD has no way
> of knowing if the constraint should be a table level or
> column level when the constraint is generated outside of
> the table.
> As a result, they are reverse engineered as table level
> constraints. In regards to the constraints being reversed
> as business rules, this is how PD implements mutliple
table
> level constraints.
> This issue is not a bug. This is normal and expected
behavior.
And this is just one example. After several attempts I lost
any
hope and didn't even bother to report any problems.
Just recently I did it again (see my posts on this forum
from
Oct 28-29) and created new case (Case# 11487019) related to
R/E process. It's already more than a week - complete
silence.
R/E functionality in PD definitely must be reviewed by
Sybase and significantly improved.
At the moment existing R/E logic doesn't pass very simple
test: 1) model is used to create database generation script
2) then this script is R/E into a new model and
3) new model doesn't match with the original one (for
example, column level constraints in original model never
end up as such in a new model).