Make PD generate DDL with desired FK index name

[Former Member]

Can't seem to find how to get PowerDesigner to generate DDL with the desired "create index MY_INDEX_NAME . . . "

    It appears to be managed in the LDM under Tools/Generate Physical Data Model/Detail Tab/FK index names.  The default is %REFR%_FK which seems to generate a name such as "Reference_123", i.e., the name PD systematically assigns.  I have tried changing the default to "%REFRCODE%" which I would expect to pick-up the Code value I assign by double-clicking on the reference and over-writing Reference_123 with MY_INDEX_NAME.

    I think I have seen this work occasionally, but it is not consistent.  I thought the inconsistency was due to PD not recognizing a change to the parameter FK index names from %REFR% to "REFRCODE%.  However, forcing a name change, e.g., MY_INDEX_NAME to "MY_INDEX_NAME2" doesn't seem to always get the desired result.

    In the PDM under Database/Edit Current DBMS, General Tab, Script/objects, Index on the Create function I found: create[. . . ] %INDEX% on [%TABLQUALIFIER%]%TABLE% (%CIDXLIST%).  The argument %INDEX% clearly controls naming the index in the DDL; if I change it to a literal such as "ABC" then the index name in the DDL appears as "ABC".  Changing the parameter %INDEX% to "REFRCODE" results in a [NULL] index name in the DDL.

    Where do I make a change to my PD set-up to get the DDL to produce the desired name in the DDL "create index MY_INDEX_NAME . . ."?

     Incidentally, a related objective is to get PD to generate the desired name in the DDL for the corresponding "add constraint "MY_INDEX_NAME . . . " statement.  I have successfully done this in the PDM under Database/Edit Current DBMS, General Tab, Script/Objects/Reference by replacing the default arguments with simply %REFR%.  If that works I don't see why the same edit to Index on the Create function doesn't work.

[former_member200945]

In DBMS, go to Script/Objects, click Reference. You will see predefined system variables

REFER is available. So it works for Reference.

If you click Index, it doesn't have REFER.

There are several way to achieve your goal.

But customizing DBMS is not a recommended because later on you will face migration issue when move to higher version.

First of all, create an extension in PDM:

Now, if you bring up an index's property, click Extended Attributes tab, you will see entry indexName with empty value.

We want to use this variable to save desired index name.

Let's run an experiment:

You set My_index to indexName for an index. Save the change.

Go to Tools->Execute Commands->Edit/Run Script. Run the following code:

set model=ActiveModel

set tables=model.tables

for each t in tables

  set indexes=t.indexes

  for each ix in indexes

      if ix.getExtendedAttribute("indexName")<>"" then

         ix.code=ix.getExtendedAttribute("indexName")

         ix.name=ix.getExtendedAttribute("indexName")

      end if

  next

next

The index name is My_index.

Now the question becomes how can I get correct value for indexName?

If reference code is what you want, you can run the following code to collect the value.

set model=ActiveModel

set references=model.references

for each r in references

   output r.code

   'Save each r to vb object, say Dictionary.

next

Then you can call setExtendedAttribute for each index. This will set correct value for indexName.

Based on above code, you can create transformation.It'll automate the whole process.