Reverse Engieering Problem in PD 16.5 SP03 PL01

[Former Member]

I am trying to reverse engieer a table from Teradata(Live DB) and convert it into a PDM.

I am having trouble converting unicode columns. For example, if in a table one column is defined as VARCHAR(200) UNICODE; when I reverse engineer this column in my PDM becomes VARCHAR(400). It doubles up all the unicode columns in length. If the column is defined as LATIN, no problem at all.

If I try to reverse engieer through a script, it gives me a correct column length for UNICODE and LATIN.

So, I was wondering is there any option in PowerDesigner to make it right while reverse engieering from a live DB? or am I simply doing something wrong?

Thanks.

[former_member186838 · Accepted solution]

Hi,

I have no teradata for testing but the select we use to find the datatype and length of a column can be found in the XDB under

Script\Objects\Column\SqlListQuery

I would test the resultset of the select for the UNICODE and LATIN columns.

If i look into the select I see that the columnlength of columntype CV (Varchar) is divided by 2

HTH

Stefan

[Former Member · Accepted solution]

Hi Stefan,

Thanks for responding. I checked the way you suggested, see attached image of how it looks like on my machine. I did not change anything, it's the way it was installed.

Can you suggest me any changes that I need to make in order to solve this problem?

I really appreciate your help.

Thanks,

Perry

[former_member186838 · Accepted solution]

Is the DB you reverse a Terradata 14 or 13?

[Former Member · Accepted solution]

It's TD 14.

[marc_ledier · Accepted solution]

Hi,

Teradata 14 reverse of column data type include an additional set-only extended attribute that you can find on TERADATAV14::Profile\Column\Extended Attributes\odbcDataType

The Set method does set the data type depending on the value set by ODBC reverse (Sixth column of TERADATAV14::Script\Objects\Column\SqlListQuery) which is

   c.ColumnType

      || ';' || trim(coalesce(c.DecimalFractionalDigits, ''))

      || ';' || trim(coalesce(c.DecimalTotalDigits, ''))

      || ';' || trim(c.CharType)

      || ';' || Trim(c.ColumnLength (format 'Z(I)'))

      || ';' || trim(coalesce(c.ColumnUDTName,'')),

For varchar column (c.ColumnType="CV"), you can see that the prc is half the ColumnLength (fifth value) and correcly used in VARGRAPHIC but not in varchar. See

<<

      Case "CV":

         if(arr(4) = "64000") then

            if(arr(3) = "4") then

               dttp = "LONG VARGRAPHIC"

            else

               dttp = "LONG VARCHAR"

            end if

         else

            if(arr(3) = "4") then

               prc = Int(CInt(arr(4))/2)

               dttp = "VARGRAPHIC(" & CStr(prc) & ")"

            else

               dttp = "VARCHAR(" & arr(4) & ")"

            end if

         end if

>>

These test do not include the CharType info that contains the multiplicator for charset (1 for latin, 2 for unicode).

You may need to change the block <<

            if(arr(3) = "4") then

               prc = Int(CInt(arr(4))/2)

               dttp = "VARGRAPHIC(" & CStr(prc) & ")"

            else

               dttp = "VARCHAR(" & arr(4) & ")"

            end if

>>

with this

<<

            prc = Int(CInt(arr(4))/2)

            if(arr(3) = "4") then

               dttp = "VARGRAPHIC(" & CStr(prc) & ")"

            elseif(arr(3) = "2") then

               dttp = "VARCHAR(" & CStr(prc) & ")"

            else

               dttp = "VARCHAR(" & arr(4) & ")"

            end if

>>

And you probably want to repeat the same for "CF" (CHAR)

Stefan, you should create a CR for this one.

Best regards,

Marc

[former_member186838 · Accepted solution]

Hi,

created CR 762767.

http://search.sybase.com/kbx/changerequests?bug_id=762767

Best regards

Stefan

[Former Member · Accepted solution]

Excellent Mark, it worked like a gem. Thank you so much.

[Former Member · Accepted solution]

Thanks Stefan for creating a CR.