Using Multiple Rdb DBMS Files

Reference:

In native Rdb, within the same session you can access two or more tables that exist in different physical Rdb DBMS files by issuing one or more ATTACH ALIAS commands using the following syntax,

ATTACH 'ALIAS alias FILENAME filename'

and referencing a specific ALIAS or RDB$HANDLE for the current default file in each SQL request you issue.

Although you can accomplish the same task using the Adapter for the Rdb DBMS, the adapter metadata-creation facility is limited to the single default connection handle. Therefore, if you wish to use multiple Rdb tables, you must add one or more SQL Passthru ATTACH commands to the server profile (or a group or user profile), and complete a few extra metadata-creation steps, as described in Completing Setup and Metadata Creation Steps for Multiple Rdb DBMS Files.

If you follow these instructions, you will be able to JOIN across Rdb DBMS files and access Rdb data remotely on a subserver from a client connection (using a SUFFIX=EDA synonym).

The details of the extra steps for metadata creation will vary by site, however, two general approaches are available:

Important: The Adapter for Rdb has been stabilized. Therefore, Information Builders has no plans to support multiple connections directly through the adapter, except by using the configuration techniques described in this topic.


Top of page

Example: Completing Setup and Metadata Creation Steps for Multiple Rdb DBMS Files

This example illustrates the setup and metadata creation steps for multiple Rdb DBMS files. The example assumes that an Rdb DBMS file with all tables is available as a template dictionary and for use in metadata creation. You can adapt this example to suit your needs.

  1. Determine what Rdb files are needed. For this example, the file names are:
    CORPTABLES.RDB, 
    CORPDATA2006.RDB
    CORPDATA2007.RDB
    CORPDATA2008.RDB

    These files reside in the directory DISK$DUA0:[RDB].

    CORPTABLES is an empty DBMS template that is cloned for each year's data and provides a dictionary for use in the synonym creation process. In addition, logicals that match the physical file names are used to avoid the Rdb 31-character physical limit and to facilitate coding in case the location changes in the future.

  2. To ensure that the logicals are always available (and known to the server at start up time), edit the following file
    EDACONF [.BIN]EDAENV.COM

    and add a logical name reference (of 31 characters or less) for each Rdb DBMS file to be accessed. For example:

    $ DEFINE CORPTABLES   DISK$DUA0:[RDB]CORPTABLES.RDB
    $ DEFINE CORPDATA2006 DISK$DUA0:[RDB]CORPDATA2006.RDB
    $ DEFINE CORPDATA2007 DISK$DUA0:[RDB]CORPDATA2007.RDB
    $ DEFINE CORPDATA2008 DISK$DUA0:[RDB]CORPDATA2008.RDB

    Save your changes.

    Note:

  3. Assuming you have edited the EDAENV file as described in step 2, start or restart your server.
  4. During Rdb adapter configuration, choose the standard options for enabling Rdb data access and supply the following logical name for the server:
    CORPTABLES:

    The resulting profile entry would looks as follows:

    ENGINE SQLRDB SET SERVER CORPTABLES:
  5. Using the Web Console's edit facility or another editor, modify the global profile, edasprof.prf (or possibly a group or user profile if you are configuring for group or personal profile-based behavior) to add an ATTACH ALIAS command for each of the additional Rdb files. This step will make the Rdb database files visible as aliases to the default database. For this example, the following code was added after the SET SERVER line in the profile:
    ENGINE SQLRDB SET SERVER CORPTABLES:
    SQL RDB ATTACH 'ALIAS CORPDATA2006 FILENAME CORPDATA2006:' ;
    END
    SQL RDB ATTACH 'ALIAS CORPDATA2007 FILENAME CORPDATA2007:' ;
    END
    SQL RDB ATTACH 'ALIAS CORPDATA2008 FILENAME CORPDATA2008:' ;
    END 

    Note:

  6. At this point, if you have metadata from a prior installation it is good practice to determine if you can use your existing metadata as is, if you need to modify it, or if you need to create new (additional) metadata that is distinct from what already exists. Depending on your needs, you may be done or you may need to continue with either Step 7 or Step 8.
  7. Whether you are creating a synonym for the first time or re-creating a synonym that existed in a prior release, you can use the Web Console or the DMC synonym creation facility. When prompted, select the desired Rdb tables using the connection:
    CORPTABLES:
  8. After any needed metadata has been created, edit the Access Files that needs to change (using the Web Console editor or another editor) to have a TABLENAME= value that is prefixed with the appropriate ALIAS reference.

    To complete this step from the Web Console, locate the synonym whose Access File you need to edit, click it, and select Edit Access File as Text from the menu.

    For instance, in the Access File TABLENAME entry for the 2007 Sales table initially looks like this:

    TABLENAME='RDB$HANDLE.SALES',

    The single quotation marks are required since the $ in RDB$HANDLE is regarded as a special character.

    The edited version would look as follows:

    TABLENAME='CORPDATA2007.SALES',

    In this instance, the single quotation marks are optional since CORPDATA2007 does not contain a special character.

  9. Repeat step 8, as many time as necessary to account for all metadata whose access requires an ATTACH ALIAS command.

    Tip: As an alternative to editing each file individually, you can create a batch script that edits the RDB$HANDLE string for groups of files at one time.

Note:


Top of page

x
Reference: Considerations For Sites That Have Upgraded From a Release Prior to 768

Releases prior to 768 recommended similar metadata creation and Access File editing steps (with TABLENAME= prefix.table and a SERVER= prefix value) for JOINs, and, in addition, required that the profile be edited to eliminate the SET SERVER command and to include the SQL APT=OFF command at runtime.

While not explicitly documented, this method enabled remote access (using SUFFIX=EDA) to Rdb data because APT=OFF allowed Rdb to pick up data using the SERVER= value from the Access File.

While it is not necessary for sites that currently use this configuration method to change or reconfigure in Version 7 Release 6.8, there are distinct advantages in doing so:

Note that it is not necessary to remove the SERVER= value from the Access Files since this information is not used in this context and is, therefore, ignored.


WebFOCUS