Re: ERD for Synon db without Synon?

["Don Tully" <dtully@xxxxxxxxxx>]

Loyd,

Sorry for the delayed response, I am trying to catch up on my inbox of 1000
e-mails.

I have a product called ADBREL (Application Database Relations) which
documents a database in something similar to Synon ERD relationships:  The
field naming convention used in the database is AABBBBBB.
    1.  FILE   Known by     BBBBBB
    2.  FILE   Qualified by   BBBBBB
    3.  FILE   Owned by     FILE          (Role_Name)
    4.  FILE   Refers to       FILE          (Role_Name)
    5.  FILE   Extension of  FILE           (Role_Name)
    6.  FILE   Refers to      Role_Name   Base_Field   ROLE_NAME
Role_Field
    7.  FILE   Refers to      Role_Name   Base_Field   CONSTANT     'ABC'
    8.  FILE   Refers to      Role_Name   Base_Field   *FILE

ADBREL has been in development/production for over 10 years having
designed/generated complex applications with over 2000 tables.  Normally
these tables are implemented with full RI including many triggers which are
all generated from ADBREL.  ADBREL is a component of an application
generator (RPGLE) called GENSYS.   ADBREL is constructed at a very high
level of abstraction so field names do not get in the way.

In the last couple of years I have done a lot of work attacking the problem
set that you are working on, i.e., Retro design of a database back to an
abstract format (ADBREL).  I have an SQL generator (WRKRCD) that can join up
to 30 files without the need to specify file prefix or base field names.
If role names are involved then the join requires the Role_Name (or field
names with/without file prefix, or alias search expressions) which then
supplies field names to WRKRCD.  The SQL statement generated can be
executed, syntax checked, and/or written to a script file.  The join fields
can be supplied in JOIN, EXISTS, or ORDER BY formats.

Example 1:       This uses a natural join so no ADBREL setup is necessary.
This is the way it runs out of the box in 1 second.
Command:        WRKRCD FILE(ORDITM)  JOIN(ITMMAS)
Results in:          Select a.*, b.*
                         From ORDITM a
                         Join ITMMAS b on a.OIco# = b.IMco# and a.OIitem =
b.IMitem

Example 2:       This uses Role_Names (BILLTO and SHIPTO) that must be
defined in ADBREL using format 6.
Command:        WRKRCD FILE(ORDHDR) JOIN(BILLTO SHIPTO)
Results in:         Select a.*,b.*, c.*
                        From ORDHDR a
                        Join ADRMAS b on a.OHco# = b.AMco# and a.OHadbill  =
b.AMadd#
                        Join ADRMAS c on a.OHco# = b.AMco# and a.OHadship =
c.AMadd#

ADBREL can automatically create all of the natural joins and can greatly
assist in creating the Role-Name joins.  Once you supply a role name
relationship, all such "Owned by", "Refers to", etc. relationships can be
generated.

The tools can do lots more than described here, but I think you get the
gist.  This tool is very flexible with many *SRVPGMs and Procedures that
could be quickly structured to your immediate needs.

Feel free to respond on or off-line.  I would be happily to assist you in
any way I can.

Thanks,
Don Tully
Tully Consulting LLC
920 996-0448

"Goodbar, Loyd (ETS - Water Valley)"
<LGoodbar@xxxxxxxxxxxxxx> wrote in message
news:EA985E321704D411BD0F009027B1030C07D342B1@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Vern,
>
> >From what I can see, the QSQGNDDL API provides the same functionality
that
> "Generate SQL" does from the iSeries Navigator. I briefly reviewed the
> database functions in Navigator yesterday. Today I found the redpaper "DB2
> UDB Database Navigator and Reverse Engineer", which mentions the ANSI SQL
> generate option.
>
> There's also a "User Defined Relationship" option that looks like what I'm
> after. From the paper, "This function is likely used to illustrate a
> referential integrity constraint that is implemented on the application
> logic and not defined in the database."
>
> The project team I'm on is implementing Hyperion planning, analysis, and
> reporting. One of our requirements is to provide drill-down into our ERP
> data from Hyperion. I assume I need to define the drill-down relationships
> to Essbase (houses the Hyperion data). Our ERP system is composed of
around
> 1,200 tables for which I need to define relationships. I'm hoping to get
> financials and the major inventory management tables defined in short
order,
> as they are the majority of drill-down focus in phase 1.
>
> Thanks,
> Loyd
>
> Loyd Goodbar
> Senior programmer/analyst
> BorgWarner
> E/TS Water Valley
> 662-473-5713