Topic
  • 4 replies
  • Latest Post - ‏2014-09-11T01:52:01Z by reital
SystemAdmin
SystemAdmin
1632 Posts

Pinned topic Reverse Engineer a Physical Model from a DDL file

‏2013-01-03T13:00:22Z |
Navigating through the Data Studio V3.2 Information Center http://pic.dhe.ibm.com/infocenter/dstudio/v3r2/index.jsp

Designing and modeling | Physical and domain data modeling | Creating a physical data model by using a wizard

I have found this statement. "You can also choose to reverse engineer the model from a database or a DDL file. If you choose to reverse engineer from a database, you must provide connection information on the next pages of the wizard. If you choose to reverse engineer from a DDL file, you must provide the path to a DDL file in the next pages of the wizard."

However, I have been unable to find any description of the format of the DDL file that the wizard can read to reverse engineer a database to a physical model. I have tried using a unaltered db2look output file without success. I have edited the db2look file to remove all embedded blanks, double quotes, comment lines (-- text), removed all bufferpool, tablespace, alias, triggers, etc. without success. I am not able to connect to any database from Data Studio to reverse engineer a database because of security restrictions in place.

If anyone has been able to create a physical model from a DDL file, would you be willing to sharing this secret?
Updated on 2013-01-28T15:40:27Z at 2013-01-28T15:40:27Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Reverse Engineer a Physical Model from a DDL file

    ‏2013-01-28T14:53:31Z  
    Hello Van,

    After looking through your file the main issue is that you have embedded routines that require different termination characters. Unless you specify, DB2LOOK will just use ";" for everything.

    1. use the -td option with db2look

    db2look -d sample -e -td ! -o sampleDDL.sql
    This will put a ! character between statements, but leave semicolons inside the body of all routines (as required).

    2. At this point you still need to tell the runners what the statement termination character is.
    add the following to your script (assuming ! is the term char you chose).

    --<ScriptOptions statementTerminator="!"/>
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Reverse Engineer a Physical Model from a DDL file

    ‏2013-01-28T15:40:27Z  
    Hello Van,

    After looking through your file the main issue is that you have embedded routines that require different termination characters. Unless you specify, DB2LOOK will just use ";" for everything.

    1. use the -td option with db2look

    db2look -d sample -e -td ! -o sampleDDL.sql
    This will put a ! character between statements, but leave semicolons inside the body of all routines (as required).

    2. At this point you still need to tell the runners what the statement termination character is.
    add the following to your script (assuming ! is the term char you chose).

    --<ScriptOptions statementTerminator="!"/>
    Thank you for the help. I will recreate my DDL using the statement termination character - ! as advised.
  • askadian
    askadian
    1 Post

    Re: Reverse Engineer a Physical Model from a DDL file

    ‏2014-09-09T12:35:18Z  
    Thank you for the help. I will recreate my DDL using the statement termination character - ! as advised.

    Hi,

    I am using IDA to create a Physical Model from a DDL Script(see attached). I am getting a lot of errors. Could someone please have a look and help me out.

    Thanks

    Attachments

  • reital
    reital
    9 Posts

    Re: Reverse Engineer a Physical Model from a DDL file

    ‏2014-09-11T01:52:01Z  
    • askadian
    • ‏2014-09-09T12:35:18Z

    Hi,

    I am using IDA to create a Physical Model from a DDL Script(see attached). I am getting a lot of errors. Could someone please have a look and help me out.

    Thanks

    Hi,

        Could you share more, what version of IDA you are using, how you get the sql files and what  db vendor of this sql file ?

     

    Thanks.