Forum Stats

  • 3,780,926 Users
  • 2,254,456 Discussions
  • 7,879,496 Comments

Discussions

Index and foreign keys incorrectly renamed by SQL DM - version 19.4.0.350

I have a pretty continual battle trying to get 'expected' names for indexes, foreign key attributes, etc.

For example, I create the second index in a table and Naming Rules correctly names it TABL_2_IDX. SQL DM then renames this to something like TABL_2_IDXv1. If I change the name, SQL DM will change it again to something like TABL_2_IDXv3.

Similar things happen when I add a foreign-key relation ship - the new attribute is correctly named PTBL_ID but SQL DM then renames it to PTBL_IDv1. Sometimes I can fix this by removing and re-adding the relationship several times until the name 'sticks'.

Any idea as to what is going on an how to stop it?

Answers

  • SchwabW
    SchwabW Member Posts: 9 Red Ribbon

    I did observer a similar behavior.

    One permanent Fix was opening the design using the older 18.4 version. 18.4 did give a warning and fixed the issue.

    After saving the design, i was able to continue working with 19.4.

    Since I noticed other "automatic changes" I am for now working with 18.4.

    AndyHPhilip Stoyanov-Oracle
  • AndyH
    AndyH Member Posts: 759 Bronze Trophy

    What warning did you get?

    Did you have any issues in downgrading to 18.4 instead of 19.4?

  • SchwabW
    SchwabW Member Posts: 9 Red Ribbon

    In 19.4 after Importing for Data Dictionary I received an Alert Box with content: something like that "renaming from Key_xxxxV1 failed because this key already exists. I got stuck with this issue the Alert Box

    Performing that same Import again with 18.4 showed the Same Alert but I was able to continue, the issue was solved.

    After the "FIX by 18.4", I was able to continue working with 19.4. Performing an Import Data Dictionary did not give that warning.

    Question: Downgrading to 18.4 does not cause any problems.

    I recommend an Import Data Dictionary and check the reported modified views, synonyms. 19.4 lost / changed synonyms and I had two views reported to have changed Statements which are stable since 2018.

    I am still investigating where the View Changes came from. I have not changed them according to GIT, no Developer has changed these and the correct versions are on our databases.

    I am working the last two weeks with 18.4 downgraded from 19.4 and all works fine.

    Walter

  • AndyH
    AndyH Member Posts: 759 Bronze Trophy

    My case seems slightly different - the 'renaming' is occurring during the creation of new tables within SQLDM i.e. from an already imported data dictionary during which no errors were reported.

    18.4 doesn't appear to be available to download - versions 19.1 to 20.2 are offered. I'll try 20.2 in case that resolves the issue (it's not mentioned as a fixed item for the release) and if that fails see how 19.1 deals with this.

  • AndyH
    AndyH Member Posts: 759 Bronze Trophy

    @SchwabW 20.2 had the same 'renaming' feature.

    Fixed it by manually changing the index names in the XML files. SQLDDM seemed happy with the names when loaded and when saved.

    No idea what causes the issue in the first place!

  • Philip Stoyanov-Oracle
    Philip Stoyanov-Oracle Member Posts: 3,367 Employee

    For example, I create the second index in a table and Naming Rules correctly names it TABL_2_IDX. SQL DM then renames this to something like TABL_2_IDXv1. If I change the name, SQL DM will change it again to something like TABL_2_IDXv3.

    I can reproduce the problem with TABL_2_IDXV1 suffix at the moment index is created in table dialog. However if you change the name of the index and that name is unique then DM doesn't change it. If you apply naming standards and your naming template for index is {table}_IDX then you'll end up with indexes ending with Vx because that template doesn't provide uniqueness in the index name in case of more than one index in table. DM comes with default index for indx {table}_{column}_IDX - however since the column(s) are not known at the moment of creation the name becomes TABL_2__IDX.

    Similar things happen when I add a foreign-key relation ship - the new attribute is correctly named PTBL_ID but SQL DM then renames it to PTBL_IDv1. Sometimes I can fix this by removing and re-adding the relationship several times until the name 'sticks'

    I cannot reproduce that - can you provided steps. If you create more than one foreign key between two tables then you'll end up with created FK columns ending with Vx. You need to provide template that produce unique names in case of more than one FK between tables. In DM 20.3 you can define fkRole dynamic property on foreign key or on related relationship in logical model and use fkRole in you template for FK column name


    Philip

  • AndyH
    AndyH Member Posts: 759 Bronze Trophy

    Sorry, it's very sporadic and I can't create reproducible steps. One table will be perfect, but the next will just be impossible to get 'right' - it's as if there were a part of the system that is meant to remember things get confused when things are renamed!

    The last time this happened my fix was to modify the XML files themselves to have the correct names.