Reverse Engr into erwin

[Little, Douglas]

Thanks in advance for thinking about my problem.

As I suspect you know, CA Erwin doesn't support Postgres or greenplum.
But they do support ODBC for reverse engineering.

When I reverse, Erwin executes the standard ODBC metadata queries for the system catalog.
The process works fine, but I'm finding that the datatype that's returned is the internal PG type instead of the standard set of ANSI types.
Eg, a BIGINT column returns as INT8.

I could live with the non-standard lables, if the resulting DDL would execute. But PGadmin doesn't know the data types.

I've traced the same function to PGadmin and find that it is submitting a query with the format_type(type oid) function to convert to ANSI types.

I've installed the newest PG ODBC driver and the problem remains.

I'm hoping that there is a setting in ODBC that I can use to have the ANSI datatypes returned.
Short of that, Is there anyway to change the Query that's submitted

It's querying the pg_attribute, pg_type type with the schema name specified Pg_catalog, so I don't think I can over ride the select. Perhaps a rule might work.

Any suggestions on moving forward?

Thanks



Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz Worldwide
500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax [phone] | Cell 847-997-5741
***@orbitz.com<mailto:***@orbitz.com>
[cid:***@01CE02F5.99F2EAC0] orbitz.com<http://www.orbitz.com/> | ebookers.com<http://www.ebookers.com/> | hotelclub.com<http://www.hotelclub.com/> | cheaptickets.com<http://www.cheaptickets.com/> | ratestogo.com<http://www.ratestogo.com/> | asiahotels.com<http://www.asiahotels.com/>

[Edson Richter]

I don't know about ErWin. If you look for alternatives that would
include a tool change, then continue reading.
I do use DBWrench, is working fairly well.

Prós:
- Multiple diagrams for same database/schema (can reduce the amount of
tables you are looing), with some glyphs to help organize them
- True reverse and forward engineering (work most of time)
- Smart rename/recreate with data move when alter is not an option
- Support triggers, tables, schemas, functions, indexes, unique
constraints, primary and foreign keys
- Intuitive interface
- Capable of conversion between 4 databases supported (PostgreSQL,
Oracle, MS SQL Server, and Access)
- Use can tweak data types
- Latest release (that I don't own) support PostgreSQL arrays and other
special data types
- Written in Java, runs everywhere (I do run in Windows and Linux)
- Diagrams saved in XML format, it is easy to implement versioning

Cons:
- No check constraints support
- No index operator class
- Bugs in function/procedure reverse engineering/forward enginnering
- Support is so-so, no big deal since the tool work most of time
- Oriented to upgrades (need to buy new version to get some fixes for
known bugs)

You can get 30 day trial (at least, it was available at time I was
evaluating for buy).

PS: I don't work for the company that sell. I'm just a (almost happy)
customer that feels if they have more huge user base, they would add
more features quicker.


Regards,

Edson

Post by Little, Douglas
Thanks in advance for thinking about my problem.
As I suspect you know, CA Erwin doesn't support Postgres or greenplum.
But they do support ODBC for reverse engineering.
When I reverse, Erwin executes the standard ODBC metadata queries for the system catalog.
The process works fine, but I'm finding that the datatype that's
returned is the internal PG type instead of the standard set of ANSI
types.
Eg, a BIGINT column returns as INT8.
I could live with the non-standard lables, if the resulting DDL would
execute. But PGadmin doesn't know the data types.
I've traced the same function to PGadmin and find that it is
submitting a query with the format_type(type oid) function to convert
to ANSI types.
I've installed the newest PG ODBC driver and the problem remains.
I'm hoping that there is a setting in ODBC that I can use to have the
ANSI datatypes returned.
Short of that, Is there anyway to change the Query that's submitted
It's querying the pg_attribute, pg_type type with the schema name
specified Pg_catalog, so I don't think I can over ride the select.
Perhaps a rule might work.
Any suggestions on moving forward?
Thanks
*Doug Little*
**
Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz Worldwide
500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 |
Fax [phone] | Cell 847-997-5741
| ebookers.com <http://www.ebookers.com/> | hotelclub.com
<http://www.hotelclub.com/> | cheaptickets.com
<http://www.cheaptickets.com/> | ratestogo.com
<http://www.ratestogo.com/>| asiahotels.com <http://www.asiahotels.com/>