Using Multiple Rdb DBMS Files
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:
- If a single Rdb DBMS file with a common set of tables is
available as a dictionary, you can configure this Rdb file as the
default connection. The steps that follow are as simple as the standard
synonym creation process, with the addition of a manual edit to
the resulting Access File to point the TABLENAME=value at
the desired ATTACH alias. Completing Setup and Metadata Creation Steps for Multiple Rdb DBMS Files illustrates
this process.
- If an Rdb DBMS file is not available as a single dictionary,
after configuring an initial connection, creating metadata, and
editing the Access File TABLENAME= attribute, you can reconfigure
and repeat the synonym creation and Access File editing steps as
many times as necessary for the additional Rdb targets.
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.
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.
- 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.
- 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:
- If a generic application
setup batch file with the desired logical is available and does
not force a process exit, you can call that batch file from EDAENV.COM
and avoid the individual entries described above.
- If the desired logicals are already available, you can skip
this step altogether because the logicals are automatically issued
when you boot your machine.
- Assuming you have edited the EDAENV file as described in step
2, start or restart your server.
- 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:
- 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:
- The semi-colon and END
statements are required.
- The ATTACH statements must follow the initial SET SERVER command.
(Placement before the SET SERVER command will cause a crash.)
- You can issue additional SET SERVER statements after the ATTACH statements.
- 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.
- For new installations (that is, those with
no prior metadata), continue with Step 7.
- In releases prior to 7.6.8, a synonym was either prefixed or
not prefixed depending on how the metadata was created or, possibly,
edited at a later time for a specific value. If the metadata for
a given table is available and the TABLENAME=value attribute
in the Access File points at the appropriate SQL alias.tablename (or
is not prefixed for data that uses the default connection), then
there is no need to modify the given synonym in order to access the
data. If all synonyms are of this type and no additional new metadata
is desired, you are done.
- If additional new synonyms are desired, or if prefixes on existing
synonyms need to change, continue with Step 7 to create the new
metadata and then go to Step 8 to edit the Access File TABLENAME=value for
the existing and new synonyms.
- 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:
- 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 contains a special character.
- 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:
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:
- When the SET SERVER command was removed, as required under the
prior configuration method, the Web Console synonym creation capability
was disabled. Since It is no longer necessary to remove the SET
SERVER command, keeping the command will enable you can to create
synonyms for Rdb from the Web Console without any secondary configurations.
- The need to remove the SET SERVER command and turn APT to OFF
may have provided sufficient justification for configuring a secondary
server to be used exclusively for JOIN and remote (SUFFIX=EDA) purposes
under the prior method. Under the new method, these reasons have
been eliminated. Therefore, you may wish to consider eliminating
the secondary server in order to save maintenance overhead and computer cycles.
Using the explicit ATTACH ALIAS steps described in this document
also provides clearer information about how data is being accessed.
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.