Topic
  • 15 replies
  • Latest Post - ‏2012-05-15T04:50:32Z by X9GX_Azmir_Zakaria
SystemAdmin
SystemAdmin
374 Posts

Pinned topic Reverse engineering problem

‏2012-02-08T17:04:24Z |
Hi,

I'm having problems with the reverse engineering not pulling in which fields are keys, nor the field descriptions. Connecting to a DB2 (i5/OS) database, with the db2 version V5R4.

I start by right clicking on Data Models and selecting New Physical Data Model. Then choose Database DB2 for i5/OS, and the version V5R4, and select Create from reverse engineering. On the next screen, I pick Database, to reach the Connections screen. Here I use the driver properties:

Drivers: AS/400 Toolbox for Java Default
Host: smalldev
username: david
default schema: proddata
Connection URL (auto created): jdbc:as400:smalldev;prompt=false

Doesn't seem you can edit the connection url yourself.

The next screen shows all the schemas on the server (Not the objects/tables of the default schema I picked before). I select the schema I want, which is proddata, and I leave the rest of the screens at their default settings. Once done, it'll have grabbed all the tables in that schema. It all seems to be there, except as started before, it doesn't have the information which fields are keys, and it didn't grab the field descriptions. Though it did grab the table description into the label text field of the tables properties.

Question is, does the application not support the ability for it to grab which fields are keys, and the field descriptions, or is there a setting I can change?

Another question is how can you select which tables to reverse engineer? After selecting the schema, I don't see an option of picking which tables you'd want, but instead grabs all 200ish of them.
Updated on 2012-05-15T04:50:32Z at 2012-05-15T04:50:32Z by X9GX_Azmir_Zakaria
  • null
    null
    60 Posts

    Re: Reverse engineering problem

    ‏2012-02-08T19:47:21Z  
    For physical file, keys will be reverse engineering as index. Check if the index was created in
    the physical model, the description of field should be brought as description of index.

    If you use reverse engineering wizard to create physical model, you can not select tables. But there
    are several ways to bring particular tables to physial model: 1. first apply filter on subfolder
    "tables" in data source explorer, then reverse engineering 2.simply drag & drop tables from data source
    explorer to physical model.

    Regards,
    -April
  • SystemAdmin
    SystemAdmin
    374 Posts

    Re: Reverse engineering problem

    ‏2012-02-08T20:59:23Z  
    • null
    • ‏2012-02-08T19:47:21Z
    For physical file, keys will be reverse engineering as index. Check if the index was created in
    the physical model, the description of field should be brought as description of index.

    If you use reverse engineering wizard to create physical model, you can not select tables. But there
    are several ways to bring particular tables to physial model: 1. first apply filter on subfolder
    "tables" in data source explorer, then reverse engineering 2.simply drag & drop tables from data source
    explorer to physical model.

    Regards,
    -April
    Hi April,

    Thanks for the reply. I found the field descriptions/column header in the label text field for reach field, which is great. And that was a great tip on the drag and drop from the data explorer to reverse select tables.

    Reverse engineering still doesn't appear to be creating any kind of indication of the fields marked as keys. Those tables don't even have any indexes listed as you had mentioned. Any ideas?
  • null
    null
    60 Posts

    Re: Reverse engineering problem

    ‏2012-02-08T21:50:18Z  
    Hi April,

    Thanks for the reply. I found the field descriptions/column header in the label text field for reach field, which is great. And that was a great tip on the drag and drop from the data explorer to reverse select tables.

    Reverse engineering still doesn't appear to be creating any kind of indication of the fields marked as keys. Those tables don't even have any indexes listed as you had mentioned. Any ideas?
    Do you have SELECT privilege on system table QSYS.QADBXREF? Which it required to reverse keys of physical file. By default iSeries do not ship SELECT privileges to PUBLIC.

    Regards,
    -April
  • SystemAdmin
    SystemAdmin
    374 Posts

    Re: Reverse engineering problem

    ‏2012-02-09T20:27:21Z  
    Thanks for the help.
  • X9GX_Azmir_Zakaria
    X9GX_Azmir_Zakaria
    7 Posts

    Re: Reverse engineering problem

    ‏2012-05-08T12:43:03Z  
    Hi,

    I'm facing same error. But i have full authority to QSYS object since my id have *ALLOBJ.

    I'm still cannot get key field. Anyone can help ?

    Thanks in advance.
    Azmir
  • null
    null
    60 Posts

    Re: Reverse engineering problem

    ‏2012-05-08T16:38:27Z  
    Hi,

    I'm facing same error. But i have full authority to QSYS object since my id have *ALLOBJ.

    I'm still cannot get key field. Anyone can help ?

    Thanks in advance.
    Azmir
    Hi, Azmir

    Can you run the following statement and check if you can get anything back?

    SELECT DBXFIL, DBXREM
    FROM QSYS.QADBXREF
    AND DBXLIB ='schema'
    AND DBXFIL = 'table'

    replace schema,table with your schema name and table name.

    Thank,
    -April
  • X9GX_Azmir_Zakaria
    X9GX_Azmir_Zakaria
    7 Posts

    Re: Reverse engineering problem

    ‏2012-05-10T09:33:30Z  
    • null
    • ‏2012-05-08T16:38:27Z
    Hi, Azmir

    Can you run the following statement and check if you can get anything back?

    SELECT DBXFIL, DBXREM
    FROM QSYS.QADBXREF
    AND DBXLIB ='schema'
    AND DBXFIL = 'table'

    replace schema,table with your schema name and table name.

    Thank,
    -April
    Hi Xiang Li,

    Im successfully can run that statement. Any idea ?

    By the way, im using V7R1.

    Thanks,
    Azmir
  • null
    null
    60 Posts

    Re: Reverse engineering problem

    ‏2012-05-10T16:22:16Z  
    Hi Xiang Li,

    Im successfully can run that statement. Any idea ?

    By the way, im using V7R1.

    Thanks,
    Azmir
    Hi, Azmir

    What's the value you get back on: DBXFIL, DBXREM?

    Thanks,
    -April
  • X9GX_Azmir_Zakaria
    X9GX_Azmir_Zakaria
    7 Posts

    Re: Reverse engineering problem

    ‏2012-05-10T23:45:01Z  
    • null
    • ‏2012-05-10T16:22:16Z
    Hi, Azmir

    What's the value you get back on: DBXFIL, DBXREM?

    Thanks,
    -April
    Hi April,

    It don't show any value in Navigator, but in 5250 show value.

    See attachment for screen-shot.
    Thanks,
    Azmir
  • X9GX_Azmir_Zakaria
    X9GX_Azmir_Zakaria
    7 Posts

    Re: Reverse engineering problem

    ‏2012-05-11T11:55:49Z  
    Hi April,

    It don't show any value in Navigator, but in 5250 show value.

    See attachment for screen-shot.
    Thanks,
    Azmir
    Hi,

    I notice attach document is missing, so i upload again.
    Thank,
    Azmir
  • null
    null
    60 Posts

    Re: Reverse engineering problem

    ‏2012-05-11T16:28:11Z  
    Hi,

    I notice attach document is missing, so i upload again.
    Thank,
    Azmir
    Hi, Azmir

    On the 5250, QADBXREF is not qualified. Do you get the same results if you run

    SELECT DBXFIL, DBXREM FROM QSYS/QADBXREF WHERE DBXLIB = 'STHD0SYSP' AND DBXFIL = 'SSSPAR'

    Thank,
    -April
  • X9GX_Azmir_Zakaria
    X9GX_Azmir_Zakaria
    7 Posts

    Re: Reverse engineering problem

    ‏2012-05-12T01:23:21Z  
    • null
    • ‏2012-05-11T16:28:11Z
    Hi, Azmir

    On the 5250, QADBXREF is not qualified. Do you get the same results if you run

    SELECT DBXFIL, DBXREM FROM QSYS/QADBXREF WHERE DBXLIB = 'STHD0SYSP' AND DBXFIL = 'SSSPAR'

    Thank,
    -April
    Hi April,

    Like i said from my previous post. From 5250, it return me some value. But from Navigator, it show nothing.

    Im not sure if this is authority issue since my id is qsecofr equivalent.
    Thanks,
    Azmir
  • X9GX_Azmir_Zakaria
    X9GX_Azmir_Zakaria
    7 Posts

    Re: Reverse engineering problem

    ‏2012-05-14T05:03:15Z  
    Hi April,

    Like i said from my previous post. From 5250, it return me some value. But from Navigator, it show nothing.

    Im not sure if this is authority issue since my id is qsecofr equivalent.
    Thanks,
    Azmir
    Hi April,

    I ran SQL from Navigator. Now give me same result with 5250.

    See attachment.

    Thanks,
    Azmir
  • null
    null
    60 Posts

    Re: Reverse engineering problem

    ‏2012-05-14T16:32:48Z  
    Hi April,

    I ran SQL from Navigator. Now give me same result with 5250.

    See attachment.

    Thanks,
    Azmir
    Hi, Azmir

    From the screen shot, you connect to different system (S103532P vs Iaspdev01) . That's why
    you got different returned result.

    Thanks,
    -April
  • X9GX_Azmir_Zakaria
    X9GX_Azmir_Zakaria
    7 Posts

    Re: Reverse engineering problem

    ‏2012-05-15T04:50:32Z  
    • null
    • ‏2012-05-14T16:32:48Z
    Hi, Azmir

    From the screen shot, you connect to different system (S103532P vs Iaspdev01) . That's why
    you got different returned result.

    Thanks,
    -April
    Hi April,
    How stupid i am. Very sorry wasting your time.

    Its working now.. Superb..

    Thanks and Regards,
    Azmir