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.
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.
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.
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.
CORPTABLES:
The resulting profile entry would looks as follows:
ENGINE SQLRDB SET SERVER CORPTABLES:
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
CORPTABLES:
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.
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.
Tip: An orderly approach is to create all of the synonyms, repeat the reconfiguration step as needed, then edit the Access Files.
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 | |
Feedback |