Question: How-to "simple" compare two schemas?

Toad Data Modeler
1

Hi @ll,

triggered by the TDM 3.2.4.3 announcement, I installed and tried this new release. I imported a TDM2 XML model (DMX).

For production, we still use the 2.25 version as it supports the SAPdatabase MaxDB natively. I’m still patiently waiting for TDM3 tosupport it too :wink:

A) Simple compare

My intention was to find differences between two schemes, as we get the fabulous “ORA-00904 / invalid column name” error without any additional information. I know about Oracle tracing to find the errors, but here I’d like to find the errors using TDM3.

How will I find the missing column(s) if comparing two schemes? I’ve Reverse Engineered both schemes “ZVDC” and “DC25” and using the new Comparator dialog I get too much differences.

Example: the tables ACTIVITY_DEF and KEYFIGURE exist in both schemes.

In the Comparator dialog my result is:

DC25.ACTIVITY_DEF … (not exists)
DC25.KEYFIGURE … (not exists)

(not exists) … ZVDC.ACTIVITY_DEF
(not exists) … ZVDC.KEYFIGURE

So, the tables exist in both schemes. Furthermore they are created using the identical DDL script. Can I switch off the schema name in the Comparator?

In the Settings dialog I deselected the “Tablespaces” option - still the same result.

B) Case Sensitivity

While comparing views, I noticed case sensitivity. In the model, the schema name is in lower case “zvdc”. The reverse engineering reads schema “ZVDC”, so that all views are displayed as different. Can I switch of case sensititvity?

C) Compare with variables e.g. Primary Keys

From the imported model, the primary key name is “_entname__PK” - in the TDM2 it is defined as %entname%_PK (variable with suffix <_PK>). The PK name from Reverse Engineering is for example KEYFIGURE_PK. How to resolve the TDM2 variables during compare?

Thanks in advance for any hints and assistance!

Best regards,
Christian.

2

Hi Christian,

A) If you wish to ignore schema information, open both your models and remove the schemas via Model | Schema/Owner Assignement. This way you can remove schema from tables, but also other objects. Then do the comparison.

B) In general, there is not such functionality, however, you can change object names and its content via scripting. Have a look at the following page:

http://modeling.inside.quest.com/entry.jspa?externalID=1363&categoryID=35
This script changes all attribute and entity names to lower case. You can get some inspiration and write a script that will lower case whatever you want. :slight_smile:

C) Variables like %entName% etc. are not supported in version 3.2. It is on our ToDo list.

Regards,

Vaclav

3

Hi Vaclav,

thanks for your reply.

Ad A): the menu item was what I searched for, thanks.

Honestly I’m afraid that the functionality doesn’t work properly. I reverse engineered a schema “DC25”. Then I selected the
Schema / Owner Assignment and selected “None” to delete the “DC25” schema . No difference was found after clikcing “Execute” and the “Close”. I tried for “PUBLIC” and “ZVDC” schema names - no difference.

I tried the same for the imported TDM2 schema. At first, it didn’t apply the changes too.

After clicking back and forth for about 15 minutes, saving the models and so on, the schema owner was reset eventually. But I can’t reproduce after what step the changes were applied.

I assume that it’s a bug?

Diagnosis would be easier if you would display confirmation dialog or a protocol line in the Message Explorer like

'Changed <123> entities to owner/schema '.

Ad B): Scripting was an option in TDM2 and I’ll perform the changes in 2.25 scripts before exporting.

Ad C): I’ll replace the %entname% variable myself then using 2.25 scripting - as in B).

Best regards,
Christian.

4

Hello Christian,

AD a) Unfortunately, I failed to simulate the problem. It works fine for me in the imported and reversed model as well.

Diagnosis would be easier if you would display confirmation dialog or a protocol line in the Message Explorer like

'Changed <123> entities to owner/schema '.

Thanks for your suggestion! We will consider it. CR # 53 214.

Thanks.

Regards,

Vladka