Thursday, August 4, 2022
Tip of the Day: Maintaining Efficiency with QualKeys in Foreign Data Sources
Product.......: R:BASE X.5 and R:BASE X.5 Enterprise (Version 10.5)
Build.........: 10.5.4.20616 or higher
Sections......: ODBC
Keywords......: SATTACH, QualKey, QUALCOLS, DSN, Data Source
Adding foreign data sources to an R:BASE database allows applications
to access tables from multiple databases simultaneously.
When you access other databases, you are working with the actual
data, not a copy. Not only can you view information from other
databases, but you can modify and add information, depending on the
access rights provided.
The first steps in attaching foreign data sources include being
connecting to the R:BASE database, establishing the data source, and
then connecting that DSN (data source name). In doing so, multiple
data sources may be connected to concurrently.
Attaching foreign data source tables may be done with the SATTACH
command or selecting "Utilities > Attach SQL Database Tables" from
the main menu bar. It is with the table attach/SATTACH where the
method and R:BASE settings must be considered to maintain ideal
performance with the foreign data source.
When tables are attached, a QualKey column definition is assigned for
columns that uniquely identifies a row, similar to a primary key or
unique key. The QualKey is assigned based upon the method the table
is attached to the database, and is used by R:BASE to link server data.
There are three different methods in which QualKeys are specified
when tables are attached, and are ordered below by efficiency:
01.
SATTACH TableName USING ColumnList - uses the specified column(s) to
assigned the QualKey that uniquely identify the rows. The ColumnList
is not limited to a single column, but the more columns that are
specified, then those column values must also match in WHERE clause
conditions. A primary key should be specified as the ColumnList value.
02.
SATTACH TableName - automatically inquires with the foreign data
source to find the optimal primary key column defined for the table
being attached. If the column inquiry fails or does not find a
primary key column, then QualKeys are defined based on the value for
the QUALOCOLS setting, starting with the first column of the table.
The number of columns assigned a QualKey is limited by the QUALCOLS value.
03.
SATTACH TableName USING ALL - assigns all columns as QualKeys
When performing a direct UPDATE to the foreign table, the USING ALL
approach is the slowest in processing, which means that to qualify a
row for updating, all of the column values must match. Rather, if a
primary key exists, specify the column with USING PrimaryKeyColName
instead, as this way, only the primary key value must match (which is
all that should be needed). When updating a row on the foreign table,
R:BASE must count how many rows match that row, and there should only
be one matching row.
The origin of the QUALCOLS setting came into being when R:BASE first
started attaching foreign data sources. QUALCOLS specifies the number
of QualKeys to assign to SQL attached tables.
If a primary key was not found for the SATTACHed table, and a USING
ColumnList was not specified for what columns uniquely identify a
row, then R:BASE assigns a set of columns to identify that row
starting with the first column of the table. The number of columns
used was limited by the value for QUALCOLS. The word was derived by
knowing that R:BASE could QUALify a row by using a set of COLumnS.
As noted, QUALCOLS is only used when 1) no primary key can be found
for the table or 2) the attach process does not specify a USING
clause in the SATTACH command.
As is it more likely for a primary key to be located at the beginning
(first or second column) of a table structure, setting QUALCOLS to 2
would assign QualKeys to the first two columns. The following command
sets QUALCOLS to 2:
SET QUALCOLS 2
To determine what columns have been assigned a QualKey for foreign
data sources, use the LIST TABLE command. QualKeys are defined in the
column attributes.
R>LIST TABLE Albums
Table: albums No Lock(s)
Descr: Server table - Albums BluzVan
Last structure modification: 08/03/2022 11:39 AM
No. Column Name Attributes
--- ------------------ -----------------------------------------
1 Album_ID Type : INTEGER QualKey
2 AlbumTitle Type : TEXT 40
3 ReleaseDate Type : DATE
4 RecordLabel Type : TEXT 30
5 Artist_ID Type : TEXT 10
6 MusicType Type : TEXT 10
7 Comments Type : NOTE 4092
Current number of rows: SERVER
The Albums table was attached from a DSN assigned to the BluzVan
sample database. The "SATTACH Albums" command was used, and R:BASE
was able to determine the Album_ID column as the primary key, and
assign the QualKey accordingly.
For additional information on working with foreign data sources,
please review the "Using SQL Data Sources" technical document:
From The Edge: https://www.razzak.com/fte
Article Title: Using SQL Data Sources in R:BASE X.5 and R:BASE X.5
Enterprise (Version 10.5)
Chapters include the following topics:
. Foreign Data Sources and ODBC
. How ODBC Works
. ODBC Commands
. Setting Up Data Sources
. Connecting Data Sources and Tables
. Working with Data Sources
. Disconnecting Data Sources and Tables
Very Best R:egards,
Razzak.
R:BASE Technologies, Inc.
https://www.rbase.com
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to rbase-l+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/0MKpCY-1oJbtc0aEC-0007Qv%40mrelay.perfora.net.
