Download Acces Plus

Version 13
Access Plus
This document applies to Adabas D Version 13 and to all subsequent releases.
Specifications contained herein are subject to change and these changes will be reported in subsequent
release notes or new editions.
© Copyright Software AG 2004
All rights reserved.
The name Software AG and/or all Software AG product names are either trademarks or registered
trademarks of Software AG. Other company and product names mentioned herein may be trademarks of
their respective owners.
Adabas D: Access Plus
AccessPlus
Table of Contents
AccessPlus .
.
.
.
.
.
.
.
.
.
.
AccessPlus
.
.
.
.
.
.
.
.
.
.
Introduction .
.
.
.
.
.
.
.
.
.
.
Introduction
.
.
.
.
.
.
.
.
.
.
Installing AccessPlus .
.
.
.
.
.
.
.
.
Installing AccessPlus
.
.
.
.
.
.
.
.
Requirements
.
.
.
.
.
.
.
.
.
Starting AccessPlus .
.
.
.
.
.
.
.
Connecting to the Database
.
.
.
.
.
.
The Attaching Wizard .
.
.
.
.
.
.
.
The Attaching Wizard .
.
.
.
.
.
.
.
Setting the Options .
.
.
.
.
.
.
.
Selecting the Tables to be Attached .
.
.
.
.
Attaching the Tables .
.
.
.
.
.
.
.
The Upsizing Wizard .
.
.
.
.
.
.
.
.
The Upsizing Wizard
.
.
.
.
.
.
.
.
Upsizing
.
.
.
.
.
.
.
.
.
.
Scenario .
.
.
.
.
.
.
.
.
.
.
Limitations of Monolithic Applications .
.
.
.
Load Distribution: Client and Server
.
.
.
.
Advantages of a Client/Server Architecture .
.
.
Preparations .
.
.
.
.
.
.
.
.
.
Backing up the Microsoft Access Database .
.
.
Selecting a Microsoft Access Database .
.
.
.
Exporting Microsoft Access Tables to Adabas .
.
Setting the Options .
.
.
.
.
.
.
.
Selecting the Tables .
.
.
.
.
.
.
.
Converting Identifiers .
.
.
.
.
.
.
.
Migrating the Tables .
.
.
.
.
.
.
.
The Upsizing Report .
.
.
.
.
.
.
.
Verifying the Success of Migration .
.
.
.
.
Problems That Can Occur During the Migration Process
What You Can Expect from Upsizing .
.
.
.
.
What You Can Expect from Upsizing .
.
.
.
.
Which Applications Benefit from Upsizing .
.
.
What Difficulties You Can Expect .
.
.
.
.
SQL and Jet Engine .
.
.
.
.
.
.
.
.
SQL and Jet Engine .
.
.
.
.
.
.
.
.
Applications Using the Jet Engine .
.
.
.
.
Applications without Jet Engine
.
.
.
.
.
Programming the ODBC API
.
.
.
.
.
Programming with the RDO Data Model .
.
.
Only a Preliminary Step .
.
.
.
.
.
.
.
Only a Preliminary Step .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
1
1
2
2
3
3
4
4
5
6
6
6
7
7
8
8
8
9
9
10
11
12
13
13
13
14
16
17
18
19
19
22
24
24
24
25
27
27
31
33
33
35
36
36
i
Adabas D: Access Plus
AccessPlus
AccessPlus
Introduction
Installing AccessPlus
The Attaching Wizard
The Upsizing Wizard
What You Can Expect from Upsizing
SQL and Jet Engine
Only a Preliminary Step
1
Introduction
Adabas D: Access Plus
Introduction
Adabas AccessPlus is an add-in library for Microsoft. AccessPlus contains two wizards, the Adabas D
Attaching Wizard and the Adabas D Upsizing Wizard , which make it considerably easier to attach
Adabas tables to Access databases and to migrate existing Access tables to Adabas.
For explanations of how to work with the Attaching Wizard and the Upsizing Wizard, see Sections "The
Attaching Wizard" and "The Upsizing Wizard".
Three different Adabas AccessPlus versions are distributed in order to support the different Access
versions such as Microsoft Access 2.0 (16 bits), Microsoft Access 95 (32 bits), and Microsoft Access 97
(32 bits). These versions are identical in their functionality. They operate on all databases of Adabas D if
an ODBC driver has been installed.
The user guidance texts within the Upsizing Wizard always appear in English even if you installed another
localized version of Microsoft Access or Microsoft Windows.
2
Adabas D: Access Plus
Installing AccessPlus
Installing AccessPlus
Before you can work with AccessPlus, you must install it in Microsoft Access as an add-in. This can be
done using the Microsoft Access Add-in Manager. If you did not select the Add-in Manager when
installing Access, use your Microsoft disks or CD to install it.
Installation Using the Add-in Manager
1. To start the Microsoft Access Add-in Manager, choose Add-ins from the File menu, followed by the
Add-in Manager item. The Add-in Manager displays a list of all the add-ins (libraries) in your
Microsoft Access directory.
2. To install AccessPlus as an add-in, click the Add New button. Microsoft Access displays the Add
New Library Dialog. Change to the Adabas installation directory, and select the AccessPl
subdirectory. According to the Access version used, load the ACCESSPL.MDA file from the
subdireectory 2.0 (for the 16-bit version), or 7.0 (for the Office 95 version), or 8.0 (for the Office 97
version). Click the Ok button.
Microsoft Access then copies the add-in library to your Microsoft Access directory and inserts a new
item in the list of available add-ins.
3. Once AccessPlus is installed, click the Close button.
A message appears, telling you that you must restart Microsoft Access before the AccessPlus option
will appear in the Add-in menu.
See also: "Using the Add-in Manager to Install Add-in" in Chapter 15 of the Microsoft Access
manual entitled "Building Applications".
If the Add-in Manager is not made available to you, you must make the necessary changes to the
MSACC20.INI file manually.
Installation Without the Add-in Manager
1. Copy the ACCESSPL.MDA file from the Adabas installation directory to your Microsoft Access 2.0
directory.
2. Load the MSACC20.INI file in an editor. This file is located in the Windows directory.
3. Enter the following line in the [Menu Add-Ins] section:
Adabas
AccessPlus==Adabas_D_AccessPlus()
4. Enter the following line in the [Libraries] section:
accesspl.mda=rw
5. Save the MSACC20.INI file, and exit the editor.
3
Installing AccessPlus
Adabas D: Access Plus
The next time you call Microsoft Access, Adabas D Access Plus should appear in the Add-ins menu.
This chapter covers the following topics:
Requirements
Starting AccessPlus
Connecting to the Database
Requirements
AccessPlus places the following requirements on the server and client:
Server
Adabas D Database Version 12
Client
Adabas AccessPlus
Adabas OBDC driver
ODBC data source
Microsoft Access Version 2, Version 7 (Office 95), or Version 8 (Office 97)
Microsoft Access Service Pack (recommended)
To find out how to install the Adabas ODBC driver, see the "Installation under Windows" manual. To find
out how to create an ODBC data source, see the "User Manual ODBC".
Starting AccessPlus
To start AccessPlus, proceed as follows:
1. Open the Microsoft Access database to which you wish to attach the Adabas tables or from which
you wish to migrate Microsoft Access tables to Adabas.
If you wish to use the Upsizing Wizard, you should activate the Exclusive-option when you open the
database to ensure that your database cannot be changed by other users during the migration process.
2. 2.To start AccessPlus choose Adabas D AccessPlus from the Add-ins menu.
In the screen displayed then, you can call either the Attaching Wizard or the Upsizing Wizard by
clicking on the corresponding button.
You can move from one dialog box to another using the Next> and <Back buttons. Once you have
made a selection in a dialog box, click the Next> button to branch to the next dialog box. The <Back
button takes you back to the previous dialog box.
4
Adabas D: Access Plus
Installing AccessPlus
The Cancel button cancels all processing and terminates Adabas AccessPlus.
The Help button calls help information for the relevant dialog box.
Once you have completed all the dialog boxes and have reached the last box, the Attach or Upsize button
becomes available, which you can click to start the process.
Connecting to the Database
When you start one of the Adabas wizards, a list of the available Adabas data sources appears.
If there is no data source for the desired Adabas database, click on the New button. The ODBC
Administrator is called and you can create a new ODBC data source .
Select the appropriate data source, and then click on the Next button. The Adabas connect dialog box
appears, in which you must enter the user name and password of the Adabas database user. Once you have
confirmed your entries by clicking on the Ok button, a connection is set up to the database.
Once the connection to the database has been set up successfully, the next dialog box appears, in which
you can set various options for the Attaching Wizard or Upsizing Wizard.
5
The Attaching Wizard
Adabas D: Access Plus
The Attaching Wizard
The Attaching Wizard provides you with a simple and convenient means of selecting tables from an
Adabas database and attaching them to a Microsoft Access database. You can reduce the length of the list
of Adabas tables by specifying a search pattern, and you can view the definition of an individual table
before attaching it.
This chapter covers the following topics:
Setting the Options
Selecting the Tables to be Attached
Attaching the Tables
Setting the Options
Select Tables
In the Select Tables box, you can specify a search pattern for Owner and Table Name to limit the selection
of Adabas tables that appear in the list. The default for the Owner is the user name of the current database
user, and the default for Table Name is *. This means that all tables belonging to the user are displayed.
When you click on the Next button to change to the next dialog box, a list of Adabas tables that match the
specification is displayed.
Table name without owner
Set this option if you wish that the name of the table attached to Microsoft Access is not prefixed with the
owner name.
6
Adabas D: Access Plus
The Attaching Wizard
Save login ID and password locally
Select Save login ID and password locally if you do not wish to be asked for the user parameters when
you first open the table in a new Microsoft Access session. In this way, Microsoft Access saves the user
name and the password for the attached table and automatically uses it to set up the connection to the
Adabas database.
Selecting the Tables to be Attached
On the left-hand side of the dialog box you will find the list of Adabas tables to which you currently have
access. Enter all the Adabas tables that you wish to attach to your Microsoft Access application on the
right-hand side. You must select at least one Adabas table in order to start the process.
Use the arrow buttons to move individual tables or all tables from the list on the left to the list on the right
or vice versa. Use the Add button to move the currently selected entry from the list on the left to the list on
the right.
The Info button lets you view the definition of a selected Adabas table. This is useful if you wish to know
what fields are contained in a table before linking it to your Microsoft Access application.
Attaching the Tables
Once you have selected all the ABABAS tables that you wish to attach to your Microsoft Access
application, click on the Attach button to start attaching them.
The selected Adabas tables are then attached to the Microsoft Access database one after the other. A status
window indicates which Adabas table is currently being attached.
Once the tables have been attached, you can use them in Microsoft Access.
Click on the Close button to exit AccessPlus.
7
The Upsizing Wizard
Adabas D: Access Plus
The Upsizing Wizard
The Upsizing Wizard allows you to migrate Microsoft Access tables to ABABAS. Various attributes of
the Microsoft Access tables can also be transferred to Adabas.
You can also modify your Microsoft Access database in such a way that your queries, forms and reports
are based on the exported Adabas tables instead of the local Microsoft Access tables.
These guidelines are intended for users whose existing Microsoft Access database application is no longer
powerful enough due to an increase in the number of users and the size of the database and who are
looking for a solution.
This chapter covers the following topics:
Upsizing
Scenario
Limitations of Monolithic Applications
Load Distribution: Client and Server
Advantages of a Client/Server Architecture
Preparations
Backing up the Microsoft Access Database
Selecting a Microsoft Access Database
Exporting Microsoft Access Tables to Adabas
Setting the Options
Selecting the Tables
Converting Identifiers
Migrating the Tables
The Upsizing Report
Verifying the Success of Migration
Problems That Can Occur During the Migration Process
Upsizing
Upsizing allows you to distribute the components of a database application within a client/server
architecture so as to take full advantage of your configuration and thus improve the performance of the
database application.
8
Adabas D: Access Plus
The Upsizing Wizard
Scenario
This scenario has as its starting point an expanded database application based on Microsoft Access, the
database product from Microsoft. As a result of the support provided by Microsoft Access for generating
tables, queries, input screens and printed reports, applications that started out as single-user solutions for
one or only a small number of users often expand and grow on the functional level. Applications of this
type provide the best support for the user group within which (and out of which) they are created, since
they are precisely tailored to meet the group’s specific needs. When they reach a certain size and exceed
Microsoft Access’s quantity schedule, however, technical difficulties can arise that make their usefulness
questionable.
Such applications are characteristically designed to scan and update the data sets on the computer on
which the user enters data and performs queries. Although these functions are divided between two
different programs, it is essential that both programs be running on the same computer. For this reason, the
organization of such applications can be described as monolithic , i.e. blocklike (as opposed to distributed
or client/server applications).
Limitations of Monolithic Applications
The file or files in which the data sets are stored can be located on the hard disk(s) of the same computer
or of another computer in the network (e.g. on a file server ).
9
The Upsizing Wizard
Adabas D: Access Plus
Files can be accessed, i.e. data can be transferred from the hard disk to the main memory, much more
rapidly if a powerful file server is used, thus temporarily lightening the load on a monolithically structured
application.
However, this type of application continues to have a structural disadvantage: The search and update
procedures, which make up the database functionality and place the highest demands on the
operating-system and hardware environment, must continue to be executed on the workstation on which
the user is working in order to achieve the fastest possible response times. This means that these
procedures can never match the throughput possible when the application program and database function
are divided among several workstations and a dedicated server.
Naturally, some of the problems that arise in monolithic applications are the result of inferior
programming, i.e. they cannot be corrected even by upsizing. However, when these applications have
reached their upper limits with regard to the number of users simultaneously accessing the database and
the size of the database, their usefulness has by no means come to an end. By changing the database
technology on which they are based, you can maintain such applications without modification or with only
minor modifications.
Load Distribution: Client and Server
The Microsoft Access architecture is well equipped for a change in database technology thanks to "Open
Database Connectivity" (ODBC) , an established interface that is used and supported by all leading
database providers. With this interface, the database engine no longer has to be running on the same
computer as the application, thus allowing it to be swapped out to a computer specially provided for this
purpose.
Consequently, users can conveniently access an Adabas server database from any Windows application
that supports ODBC (e.g. Microsoft Access).
10
Adabas D: Access Plus
The Upsizing Wizard
Using development tools such as Visual Basic or Powerbuilder, programmers can generate Windows
applications that use the ODBC functions for accessing the database.
For a description of known restrictions and special characteristics of Windows applications or
development tools that have been tested in conjunction with the Adabas ODBC driver , see the "User
Manual ODBC". Keep these restrictions in mind in order to achieve the best possible link between the
relevant ODBC application and Adabas.
Dividing the application between two different computers is called upsizing , since it increases the power
of the database by a quantum leap. One of the parts created – the existing Microsoft Access application –
is referred to in this context as the client; the other part, which manages the data in the tables, performs
queries and updates data hidden from the user, is the server. When the two parts run on different
computers, this is called a client/server application or a client/server architecture .
An ODBC interface is not required for dividing an application between a client and server; other variants
are also conceivable and can be quite useful. However, no other distribution permits migration without
modifying the application or modifying it only slightly. This is an important advantage when migrating
applications that are overly complex or whose source text is unavailable or cannot be edited.
Advantages of a Client/Server Architecture
The following are most important advantages of a client/server architecture:
It reduces the load on the workstation on which the application is running because queries are
processed, tables are scanned, etc., on another computer that is specially equipped for these
operations.
11
The Upsizing Wizard
Adabas D: Access Plus
It reduces the load on the network because only the result of a search is transported to the workstation
and not all the data that was scanned.
As the scope of the database grows, the workstations do not have to be equipped with additional
disks or memory. These changes affect only the computer on which the server database runs.
Migration to a client/server architecture can significantly improve the performance of an application
without requiring that workstations on which the client is executed be modified. Certain requirements for
multi-user operation, such as the selective locking of individual data rows , only become possible when
Adabas is used as a server database.
Using Adabas as a server database opens up the application to multiple users and large amounts of data.
You are provided with all the options available with a professional database management system with
regard to availability, scalability, permissions, support for data backup and many other features.
Preparations
Before you can migrate a Microsoft Access database, you need an Adabas database user and an ODBC
data source. You should make a backup copy of the Microsoft Access database.
Creating a User
To migrate tables from a Microsoft Access database, the corresponding Adabas database must contain a
user to whom the tables exported from Microsoft Access can be assigned. In other words, this user will be
the owner of the migrated tables.
The user must have the access rights of a DBA and must also be authorized to open several database
sessions simultaneously (NOT EXCLUSIVE option) . The maximum disk space available to this user,
which is defined by means of the PERMLIMIT value, must be enough to accommodate the data.
The user should be given the name of the Microsoft Access database or of the application, because this
will facilitate the common backup, export and authorization of all tables belonging to an application.
If there is as yet no such database user, you can create one using Adabas Domain. However, you can also
issue a CREATE USER statement from Microsoft Access by means of an SQL Pass-Through Query .
Example:
CREATE USER nwind PASSWORD secret DBA NOT EXCLUSIVE
For more information, see Section "Authorization, <create user statement>" in the "Reference" manual.
Creating an ODBC Data Source
Make sure that you have created an ODBC data source for the Adabas database to which you wish to
migrate Microsoft Access tables.
To create an ODBC data source, use the ODBC Administrator. You will find more information on
creating a data source for an Adabas database in the "User Manual ODBC".
12
Adabas D: Access Plus
The Upsizing Wizard
Backing up the Microsoft Access Database
Before migrating your Microsoft Access database, you should make a backup copy. The Upsizing Wizard
does not delete any data or objects from your Microsoft Access database, but tables are renamed if
necessary.
Selecting a Microsoft Access Database
Only Microsoft Access tables can be migrated using the Upsizing Wizard. Some Microsoft Access
applications use two databases; the tables are contained in one database (the back end) and queries, forms,
reports, macros and modules in the other (the front end). The tables from the back end are attached to the
front end. Since the Upsizing Wizard ignores attached tables, you must use the back-end database.
The selection of the Microsoft Access tables that you wish to export to Adabas has a decisive influence on
the performance of your application.
A good Microsoft Access client/server application consists of a mixture of local tables and tables in the
server database. As a general rule, tables that are seldom modified should be kept in the local database and
tables that are often modified or are accessed by a large number of users should be exported.
Exporting Microsoft Access Tables to Adabas
This section provides a general description of the procedure for exporting Microsoft Access Tables to
Adabas. Subsequent sections will take you through the individual screens involved in the migration
process. When you click on the Upsizing Wizard button, the following occurs:
1. The Upsizing Wizard analyzes the local database, its tables, the attributes and indexes of these tables,
and the relationships between tables (if any were previously assigned by means of Edit /
Relationships).
2. You are informed of any changes that need to be made to critical identifiers (identifiers containing
characters that are not allowed in the Adabas database) and must decide whether to initiate the export
operation.
3. The Upsizing Wizard automatically creates a mirror image of each individual table and its indexes in
the server database. If any default values, validation rules or required options were defined for table
fields, they are also transferred to the target table. When this procedure has been completed and if the
Structure only, no data field was not selected beforehand, the table in the server database is filled
with the contents of the local table. In the final step, the local table is assigned its original name with
the extension "_local", e.g. a table with the name Customers would be named Customers_local after
upsizing. The local tables are then no longer needed and can be manually deleted from the database
once you have determined that the migration process was successful.
4. If the Attach exported tables field was selected when the migration process was initiated, the tables in
the server database are automatically attached to the Microsoft Access database. In order to permit
the attached tables to be addressed under their original names, the Upsizing Wizard generates a query
with the original table name for each table attached. Since queries and tables for Microsoft Access
applications are normally indistinguishable, this makes the swapping out of tables to the server
database transparent for the application.
13
The Upsizing Wizard
Adabas D: Access Plus
For a description of the individual steps in detail and in conjunction with the screens, see below.
Setting the Options
Export Options
If you wish to export only the table structure without the data, select Structure only, no data .
To export table attributes, select one or more of the following options:
- Primary Keys
- Default Values
- Indexes
- Validation Rules
- Relationships
- Required Options
After the Microsoft Access tables have been exported, an attempt is made to reproduce these attributes in
the newly created Adabas tables.
If you wish to export the relationships (table relationships) , you must export all the tables involved in a
relationship.
Some of the Microsoft Access attributes have different names in Adabas. The following list indicates the
names of the Microsoft Access attributes in Adabas:
14
Adabas D: Access Plus
The Upsizing Wizard
Microsoft Access
Adabas
Primary Key
Primary Key
Index
Index
Relationship
Referential Constraint or Foreign Key
Default Value
Default Value
Validation Rule
Constraint
Required Option
NOT NULL Attribute
The Adabas attributes are created using the Adabas Data Definition Language (DDL) .
The following list shows the SQL statements that are used to create the corresponding attributes:
Primary Key
ALTER TABLE <table name>
ADD PRIMARY KEY (<column name>, ...)
Index
CREATE [UNIQUE] INDEX <index name>
ON <table name> (<column name>, ... )
Relation
ALTER TABLE <table name>
FOREIGN KEY (<referencing column>, ...)
REFERENCE <referenced table>
[ON DELETE CASCADE]
Default Value
ALTER TABLE <table name>
ADD DEFAULT <default value>
Validation Rule
ALTER TABLE <table name>
ADD CONSTRAINT <constraint name>
CHECK <search condition>
Required Option
ALTER TABLE <table name>
COLUMN <column name>
NOT NULL
See also Section "Data Definition", in the "Reference" manual.
If an SQL statement cannot be executed during the migration process because, for example, it could not be
created correctly, it is displayed in a dialog box.
15
The Upsizing Wizard
Adabas D: Access Plus
You can then either correct the SQL statement and execute it again (using the Execute button), or you can
skip it (using the Skip button).
Attach Options
If you do not wish to attach the exported tables to your Microsoft Access database, clear the Attach
exported tables check box.
If the tables are already available in the server database, for example, because an attempt to export them
was not satisfactory, you can select Overwrite existing server tables to obtain that these tables are replaced
without a comment. You can also select this option later during the migration process.
Select Save login ID and password locally if you wish to save your user name and password for the
attached tables.
More Options
Uppercase Identifiers
Set this option if all identifiers (table names, field names) of the exported tables are to appear in
uppercases in the Adabas database.
Maximal Identifier Length
You can use this option to specify the maximum length of the identifiers for the exported tables in the
Adabas database.
Set Adabas D Comments
When this option is set, the descriptions of tables or fields are stored as comments in the Adabas system
tables.
Skip All Errors
Set this option if you do not wish that Adabas AccessPlus stops at each error to ask you to correct the SQL
statement.
Selecting the Tables
The selection of the Microsoft Access tables that you wish to export to Adabas has a decisive influence on
the performance of your application.
A good Microsoft Access client/server application consists of a mixture of local tables and tables in the
server database. As a general rule, tables that are seldom modified should be kept in the local database and
tables that are often modified or are accessed by a large number of users should be exported.
Note : Tables previously migrated and renamed by the Upsizing Wizard do not appear in the list of
available tables. You can alter this by giving the tables the suffix "_local" before starting the Upsizing
Wizard .
16
Adabas D: Access Plus
The Upsizing Wizard
On the left-hand side of the dialog box you will find the list of Microsoft Access tables. Enter all the
Microsoft Access tables that you wish to export to Adabas on the right-hand side. You must select at least
one Microsoft Access table to start the migration process.
Use the arrow buttons to move individual tables or all tables from the list on the left to the list on the right
or vice versa. Use the Add button to move the currently selected entry from the list on the left to the list on
the right.
The Info button lets you view the definition of a selected Microsoft Access table. This is useful if you
wish to know what fields are contained in a table before you export it to Adabas.
Once you have finished your selection, click on the Next button.
Converting Identifiers
In the Microsoft Access tables that you have selected, the system checks the identifiers (table names, field
names) to determine whether they are valid ODBC identifiers. If not, they are automatically converted to
valid ODBC identifiers.
A valid ODBC identifier can contain uppercase and lowercase letters (A-Z, a-z), digits (0-9) and the
underline character. All identifiers must begin with a letter.
In addition, the identifiers are adapted to whatever options are set, e.g. they might be converted to
uppercase letters and shortened to the maximum length selected.
If the conversion procedure results in duplicate identifiers, these identifiers are numbered consecutively.
Example:
17
The Upsizing Wizard
Adabas D: Access Plus
Selected Options
Uppercase Identifiers:
Yes
Max Identifier Length:
18
Original identifier
Converted identifier
’Employee Telephone Office’
’EMPLOYEE_TELEPHON1’
’Employee Telephone Private’
’EMPLOYEE_TELEPHON2’
The next screen lists all the identifiers and conversions that apply to the selected Microsoft Access tables.
If desired, this screen allows you to modify the converted identifiers to meet your own requirements.
The first column indicates whether the name is a table name or a field name. The second column specifies
the original name. The third column (check box) indicates whether or not the identifier has been modified
by Adabas AccessPlus; the fourth column displays any newly converted names.
The table is structured so that one table name is displayed at the top and below it are the names of the
fields belonging to the table.
The selection box at the top allows you to move directly to a specific table.
When you have finished editing the identifiers, you can begin migrating the Microsoft Access tables by
clicking on the Upsize button.
Migrating the Tables
Once you have selected the Microsoft Access tables and adapted the identifiers, if necessary, click on the
Upsize button to start the migration process.
The following steps are then executed:
18
Adabas D: Access Plus
The Upsizing Wizard
The selected Microsoft Access tables are exported to Adabas one after the other. If a table already
exists in Adabas, you are asked whether or not you wish to overwrite this table.
The selected attributes of the Microsoft Access tables are created in Adabas by means of DDL. If an
error occurs, you can correct the corresponding SQL statement (see Section, "Setting the Options").
If the Attach exported tables option was not selected, the upsizing process terminates here.
Otherwise, the tables migrated to Adabas are attached to the Microsoft Access database. All the
exported Microsoft Access tables are first renamed. The new name of a table is obtained by adding
the suffix "_local" to the previous name. The names of exported tables or individual field names may
have to be modified when they are created in Adabas because, for example, they contain invalid
special characters. In this case, the name of the attached table receives the suffix "_remote" and an
"aliasing query" is automatically generated with the original table name and field name.
The Adabas tables are attached to the Microsoft Access database one after the other. A status window
indicates which Adabas table is currently being attached.
On completion of the migration process, the number of exported Adabas tables and the number of
exported tables that were attached to the Microsoft Access database are displayed.
The result of the migration process is given in an upsizing report. Click on the Report button to view or
print out the upsizing report, or the Close button to exit Adabas AccessPlus.
The Upsizing Report
The upsizing report contains information on the migration process. It indicates the Adabas database to
which the tables were transferred, the options that were set and the Microsoft Access tables that were
exported.
For each table, it indicates the table’s new name (if any), whether an "aliasing query" was generated, and
which attributes were exported.
In addition, the converted column names and data types and the attributes that could be created for
individual columns are also displayed for each table.
Verifying the Success of Migration
In most cases, you can start up your usual Microsoft Access application immediately after the migration
process is completed. Run through the usual tables and queries in order to determine whether the existing
functionality was retained beyond the migration process.
Updating ported tables
You should now check whether Microsoft Access allows the ported tables to be updated. If not, you
should make sure that a unique index to the tables to be updated has been defined.
A table attached to Microsoft Access can be updated only if it has a unique index. Although the Upsizing
Wizard ports an existing unique index, it does not generate an index to the exported table if none was
defined.
19
The Upsizing Wizard
Adabas D: Access Plus
You can define a unique index using, for example, Adabas Domain. However, you can also issue a
CREATE UNIQUE INDEX statement from Microsoft Access by means of an SQL Pass-Through Query .
For more information, see Section, "Data Definition, <create index statement>" in the "Reference"
manual.
Case-sensitive Text Comparison
If you perform queries whose WHERE condition is based on text comparison or issue
"Recordset.Findfirst" statements that access a text field and you discover that data rows are no longer
found that were found before migration, the problem may be related to the handling of uppercase and
lowercase letters when the Adabas database performs a text comparison. The default method used for text
comparison in a Microsoft Access database is not case-sensitive in contrast to an Adabas database.
To prevent Adabas from distinguishing between uppercase and lowercase letters, in the SQL statement,
simply base the comparison on the result of the UCASE function, applied to the column contents, instead
of basing it on the column contents.
Example:
SELECT Cust_no FROM Customer
WHERE Cust_name="MILLER"
and it also returned the customer number of the customer "Miller", the following minor modification must
be made following migration:
SELECT Cust_no FROM Customer
WHERE UCASE(Cust_name)="MILLER"
The UCASE function instructs the Adabas server database to convert the contents of each cell in this
column to uppercase letters before performing the comparison; i.e., it now follows the same procedure that
was formerly executed automatically by Microsoft Access without your intervention. Naturally, the other
value in the comparison must also be written in uppercase letters. This can be achieved, for example, by
again inserting the UCASE function before the other column name as well (if the application is comparing
two columns) or by changing this value to uppercase letters beforehand (if a fixed value is being
compared).
20
Adabas D: Access Plus
The Upsizing Wizard
If you originally generated the query in the Microsoft Access Select Query window as in this example,
you can also easily modify it there:
21
The Upsizing Wizard
Adabas D: Access Plus
As in this example, you can prevent the column contents from being output in uppercase letters and
reinsert the column in the query as it was originally written.
You also have the option of using an Adabas extension of the LIKE predicate , which is somewhat more
efficient than the method described above. Using a "match element" , you can specify a number of
characters that are to be handled identically for each character position, e.g.:
SELECT * FROM Customer
WHERE Cust_name LIKE ’(mM)(üÜ)(lL)(lL)(eE)(rR)’
For more information on this variant, see the description of the LIKE predicate in Section "Basic
Elements, <like predicate>" in the "Reference" manual.
Unsatisfactory Performance
If you find that upsizing was successful but performance did not improve as you had expected, look for
the possible causes as follows:
Are the application and server database running on the same computer? If yes, you have shown that
upsizing was successful but are not yet enjoying the advantages of a client/server architecture
because loads are not being distributed to more than one computer. If this is the case, you can expect
even worse performance since the computer is additionally loaded down by another program – the
server database – running simultaneously. Take the next step as soon as possible and move the server
database to another dedicated computer in the network.
If, despite a distribution in the network, performance is worse (or did not improve as expected), the
problem is often related to the way in which the application accesses the database. You can
sometimes eliminate this problem by making minor changes to the application. Refer to Section
"Which Applications Benefit from Upsizing" in order to find out why your application is not
benefiting.
Problems That Can Occur During the Migration Process
Between all databases, "cultural" differences exist; this means that you must adapt the application or
database when you change databases.
The Upsizing Wizard itself takes care of these differences and performs conversions where necessary.
However, sometimes the Upsizing Wizard may not be able to proceed without your assistance. Depending
on the syntactical differences between Microsoft Access and Adabas, it may be necessary for you to
participate in the conversion of default values and validation rules.
Certain initialization constants and functions are not automatically transferred. In this case, the Upsizing
Wizard opens a window containing the DDL expression that was used when attempting to transfer this
initialization to the table in the server database.
For the complete list of functions provided in Adabas, their syntax and notation, refer to the "Reference"
manuals.
Microsoft Access and the Adabas server database do not use the same syntax for time/date constants .
22
Adabas D: Access Plus
The Upsizing Wizard
If you would like to initialize a column with a date constant and meet up with a specific error message
during the migration process, change the time constant at the end of the ALTER TABLE statement from
the Microsoft Access format to the Adabas format "YYYY-MM-DD-HH:mm:ss". For example:
’1990-01-01-00:00:00’
Unlike Microsoft Access, Adabas always requires that you specify the date and time in a TIMESTAMP
column (date/time column). If the date alone is always specified in a column, the "Date" data type must be
used for this column. If necessary, the user must make this change after the migration process is
completed.
23
What You Can Expect from Upsizing
Adabas D: Access Plus
What You Can Expect from Upsizing
If you are dissatisfied with the performance of a Microsoft Access database application that originally
functioned well with a smaller volume of data, you can expect an improvement when you distribute it to a
client and server. In such cases, a bottleneck is most likely due to the throughput of the local Microsoft Jet
Engine. A database server that runs on a dedicated server computer and fully exploits its resources and
operating system distributes the load more efficiently and is better equipped to handle a high volume of
data and a large number of users.
If, however, you are dissatisfied with the waiting time during which Access is being started, upsizing is
not the solution to your current problem. In order to continue using the application, you must also
continue to use Microsoft Access since the Access database contains not only the module code but also all
screens and any existing macros. You can attempt to speed up Microsoft Access 2.0 by preventing the
Wizards from being loaded when the program is started and increasing the memory cache from the preset
value of 512 KB to around 2 MB. This is done by changing the relevant settings in the MSACC20.INI
initialization file located in the directory in which Microsoft Access 2.0 is installed. You also have the
option of further upgrading the workstations or rewriting the application, e.g. in Microsoft Visual Basic,
thus slightly reducing the resource requirements placed on an Access application.
The minimum requirements for computers on which the client can execute a database application are
constantly growing. As of mid-1996, if you plan to run a number of Microsoft Office products
simultaneously under Windows or if you or the programs used utilize "Object Linking and Embedding"
(OLE), your workstation should be a PC with a Pentium processor, a minimum clock rate of 100 MHz and
at least 32 MB of main memory. For example, if the server database is to be accessed on the workstation
only by a Visual Basic 3.0 program and if no other Microsoft Office applications or similar
resource-intensive applications from other manufacturers are to be run simultaneously, you can use a
workstation with half the memory specified above, or even a quarter.
This chapter covers the following topics:
Which Applications Benefit from Upsizing
What Difficulties You Can Expect
Which Applications Benefit from Upsizing
Some jobs performed by the client can easily be taken over by the server; other jobs can result in
additional outlay if they have to be performed by the server. If you have an adequate server configuration,
you can be confident that the server database will have no trouble adding and updating data rows, partially
updating indexes, scanning indexes and, within certain limits, scanning entire table columns.
Bottlenecks can also occur in a server database when large tables are copied, when queries are issued that
duplicate one or more tables or attach a large number of joins to a large number of hits, and when a large
number of users simultaneously access and lock data rows in a small number of tables.
Consequently, upsizing is beneficial to applications that edit a specific segment of the database that is
relatively static or applications that mainly add new data rows to the database. Applications that search a
large data set and expect to obtain manageable result sets for local editing take particular advantage of the
strengths of client/server computing.
24
Adabas D: Access Plus
What You Can Expect from Upsizing
The database can perform many functions without having to transfer the relevant data rows to the
application program. Here are two examples:
If the application needs to know the number of data rows to which a specific criterion applies, it can use
an appropriate SELECT statement and then query the number of data rows in the result set. However, it is
more economical to use the COUNT aggregate function directly in order to obtain the number. If it is
more important to know whether a result set is empty or not than to know the exact number of rows
contained in the result set, it is better to use the original SELECT statement. This statement only checks
whether the number of result rows is greater or less than 0 (The number can be -1 if the size of the result
set has not yet been determine; the value 0 indicates an empty result set). Subsequent calls of MoveLast(),
GetFirst(), or GetNext() actually build larger result sets, causing wainting times.
A wide range of processing times can also be achieved when updating data rows. Using a SELECT
statement , the application can create a dynaset containing the data rows to be updated and update them
one after the other using the edit method. However, it is better to formulate an appropriate UPDATE
statement immediately in order to allow the server database to perform the updates. This is a clever way to
prevent the data rows that are to be updated from being transferred to the database.
What Difficulties You Can Expect
Some table attributes cannot simply be transferred from a Microsoft Access table to an Adabas table.
Case-Sensitive Treatment of Identifiers : Unlike Microsoft Access and the Jet Engine, Adabas always
distinguishes between uppercase and lowercase letters in the identifiers for database names, table names,
column names, index names, etc. This poses no problem as long as you access Adabas tables by means of
the "aliasing queries" created by the Upsizing Wizard. And this is definitely an option, since it has no
adverse effect on performance. However, if you wish to access the attached tables directly you must, for
example, use the column names just as they are displayed in the Access Table Window- Design View or
in the relevant window of the Adabas tool Domain.
Permissions
Permissions that you have assigned within Microsoft Access must first be transferred to the Adabas
database so that accesses there will be subjected to these rules. Following migration, the Adabas user that
you specified when connecting to the database is the owner of the exported tables and, thus, has all
permissions.
If you are connected as this user, you can grant privileges to other users for specific tables. For more
information, see Section, "Authorization, <grant statement>" in the "Reference" manual.
Counter Fields
After the table has been transferred to an Adabas server database, counter or autovalue columns are not
automatically initialized with a unique value when a new data row is created. The columns in a newly
created data row are initialized in Adabas by means of an insert trigger .
An insert trigger is a short program that the server database executes when creating a data row. The trigger
procedure is created by the user after upsizing since it cannot be inserted automatically. For more
information on this procedure, see the "SQL-PL" manual.
25
What You Can Expect from Upsizing
Adabas D: Access Plus
Validation Text
After the table has been transferred to a server database, if you have entered validation texts , these texts
will not be output automatically when the validation rules are violated. Instead, less specific messages are
output.
Cascade Update-Related Fields
If you selected Cascade Update-Related Fields under the Relationships option as a rule for referential
integrity in Microsoft Access, this attribute is not automatically emulated in the server database. The
cascading of update-related fields is implemented in Adabas by means of an update trigger (see above:
Counter Fields).
Default Values
Default values and values that are inserted in a new data row by means of an insert trigger are not visible
when first created and do not become visible until the data row is reselected. The data row is
automatically reselected if it was created with a column containing a value that is unique within the table
and that was used for recreating the application program.
Performance
If performance does not meet your expectations after upsizing; i.e., the application functions but requires
too much time for simple procedures, check the application for the following characteristics and modify
the application if any of these characteristics apply:
The application updates or examines the data rows in a loop in that it issues an SQL statement at each
loop pass. Remedy : If the application uses a "Recordset.Findfirst" statement in the loop, find out
how you can select the record set in one step in the correct order, for example by inserting an
appropriate ORDER BY clause in the SELECT statement . If this is not possible or also takes too
much time, set an index to the necessary sequence criterion.
In an SQL statement, the application applies a function to a large number of data rows that Adabas
cannot execute because Adabas either does not know this function or does not know it under this
name. Although this does not result in an error, each field to be updated is transferred to the client via
the network, manipulated locally by the function and returned to the database. Remedy : Refer to the
"Reference" manual to find the name of the function in Adabas.
The application accesses both local tables and tables located in the server database within the same
SQL statement. Such a statement cannot be processed by the server database alone nor can it be
processed exclusively on the local level. The Jet Engine suitably resolves this statement and, after
transferring the necessary data from the server database to the workstation, executes it. If the result of
the statement is an updating of the data in the server database, following the update procedure the
data is transferred from the workstation back to the server database. Depending on the size of the
transfer, this procedure may take the form of noticeably poor performance. Remedy : Reduce the
frequency of comparisons between local tables and tables in the server database. As far as possible,
formulate SQL comparison statements so that the resulting data flow between the workstation and
server database clearly emerges from it. Do not blindly trust the Jet Engine to discover the best
strategy for you. Transfer only what is necessary, i.e. the updated data between the local tables and
the tables in the server database.
26
Adabas D: Access Plus
SQL and Jet Engine
SQL and Jet Engine
Introduction
When you formulate a query on a database, it can be handled either locally on the workstation or remotely
on another computer or partially on both locations. On the local workstation, the Jet Engine handles the
query. On the remote computer, the server database handles the processing .
The differences between local query processing and remote processing on the server must be taken into
account when designing the application to ensure that a client/server database application works
satisfactorily. This section explains the main differences, lists functions that are unknown to the server
database and shows how operations at the ODBC interface can be traced.
Functionality Distribution Between Client and Server
When the application sends a query to the database, the Jet Engine checks whether the query can be
processed by the server database alone. If this is possible, the query is passed to the server database which
returns a result set and/or a message whether the query was successful or not. This is the simplest and less
costly way to obtain the result.
The Jet Engine finds out on its own whether the query contains traps that make it impossible to the server
database to execute the query without outside help. This happens always when the query contains
references to functions or objects that cannot be resolved by the server database. As such a query requires
the cooperation of several instances, it will always need more time than a query that can be executed by
the server database alone. Therefore it is useful to familiarize oneself with the capabilities of the server
database before designing an application and to formulate a query that uses these capabilities. Do not rely
upon the Jet Engine that it transforms a query appropriately before executing it - it does it, but the loss in
performance can be significant.
When formulating a query, your goal should be to simplify it to such an extent that the Jet Engine can pass
it to the server database as it is. Section "Tracing the ODBC Interface" below describes how you can find
out that your efforts have been successful.
If you keep the following rules in mind, you can already relieve the Jet Engine:
If your query accesses tables or views from multiple data sources (e.g. local tables and joined tables),
the Jet Engine must perform at least a part of the query locally on the workstation.
If a query contains a function that is unknown or whose name is unknown to the server database, the
Jet Engine must perform the parameter of the function locally.
Unsupported Operators and Functions
When the Jet Engine receives a query for execution, it uses the "SQLGetInfo"" function of the ODBC
driver to ask for the facilities available on the server database. When the Jet Engine encounters a function
or an operator that is unknown to the server database, then it performs this part of the statement locally.
When you use the following functions and operators in queries, you should ensure that they are supported
by the server database or find out how they are called there.
27
SQL and Jet Engine
Adabas D: Access Plus
General Operators
=
-
IS NULL
<>
*
IS NOT NULL
<
/
LIKE
>
&
MOD
<=
AND
NOT
>=
DIV
OR
+
IN
Mathematical Functions
ABS
FIX
SGN
ATN
INT
SIN
COS
LOG
SQR
EXP
RND
TAN
ASC
LEFT
SPACE
CHR
LEN
STR
INSTR
MID
STRING
LCASE
RTRIM
TRIM
LTRIM
RIGHT
UCASE
String Processing
Aggregate Functions
AVG
MIN
COUNT
MAX
SUM
Type Conversion
CCur
CLng
CDbl
CSng
CInt
CStr
28
CVDate
Adabas D: Access Plus
SQL and Jet Engine
Date and Time Functions
DATE
DATEPART(’ww’)
MONTH
DATEPART(’ddd’)
DATEPART(’www’)
NOW
DATEPART(’hhh’)
DATEPART(’yyy’)
SECOND
DATEPART(’mmm’)
DATEPART(’yyyy’)
TIME
DATEPART(’nnn’)
DAY
WEEKDAY
DATEPART(’qqq’)
HOUR
YEAR
DATEPART(’sss’)
MINUTE
The following features are not supported by the server database:
Outer Joins , if more than two tables are involved.
The LIKE operator, applied to LONG or MEMO columns.
Reports using several levels of grouping and totals.
SQL extensions realized in the Jet Engine, such as "SELECT TOP n" or "TRANSFORM".
Tracing the ODBC Interface
Problems with the performance and functioning of the ODBC layer can make it necessary for you to trace
the ODBC interface. However, this measure is to be used only as a last resort for getting to the bottom of
things, since tracing all activities will slow down the execution of the application considerably and reading
the trace file produced by this procedure requires special knowledge of the ODBC interface.
The versions 2.0, 2.5 and 3.0 are the best known versions of the Microsoft ODBC Manager. The following
description refers to the ODBC Manager 2.0; later versions can slightly deviate from this description.
Usually, the ODBC Manager is installed together with other Microsoft Software. It can only be obtained
from Microsoft.
Interface between ODBC Driver and Microsoft Jet Engine
Click on the Options button to display the dialog box below, which allows you to activate and stop tracing
(also automatically) and to define where the trace file will be stored.
29
SQL and Jet Engine
Adabas D: Access Plus
The main difficulty in interpreting the trace file is in separating the essential from the irrelevant. Many
functions are "negotiated" between the Jet Engine and ODBC driver; i.e., the Jet Engine calls a function
and receives an error code on the basis of which it decides to follow a different strategy. This dialog is
also part of the trace but not important for execution. The list of functions that can be called by the ODBC
driver and their meanings are provided below and should help you to interpret the trace file .
SQLExecDirect:
The application performs a non-parameterized query.
<SQL statement>
SQLPrepare:
The application prepares a parameterized query.
<SQL statement>
SQLExecute:
The application performs a prepared, parameterized query.
(PARAMETERIZED
QUERY)
SQLExecute:
The application passes a data row that is clearly identified by
the bookmark to the workstation.
(GOTO
BOOKMARK)
SQLExecute:
The application passes several data rows to the workstation;
each data row is defined by a bookmark.
(MULTI-RECORD
FETCH)
SQLExecute:
(MEMO FETCH)
SQLExecute:
(GRAPHIC
FETCH)
SQLExecute:
(RECORD-FIXUP
SEEK)
SQLExecute:
The application passes the content of a LONG or MEMO
column field to the workstation; each field is clearly
identified by a bookmark.
The application passes the content of a LONG or MEMO
column field to the workstation; each field is clearly
identified by a bookmark.
The application passes the content of a data row identified by
any key to the workstation (This key need not be the
bookmark key).
The aplication deletes a single data row identified by a
bookmark.
(UPDATE)
SQLExecute:
(DELETE)
30
The application deletes a single data row identified by a
bookmark.
Adabas D: Access Plus
SQLExecute:
SQL and Jet Engine
The application inserts a new single data row in a dynaset.
(INSERT)
(SELECT INTO
insert)
The application inserts a new single data row in export mode.
This chapter covers the following topics:
Applications Using the Jet Engine
Applications without Jet Engine
Applications Using the Jet Engine
Some users find that the intelligence added to their database application by the Jet Engine is more of a
hindrance than a help. This criticism is justified since the Jet Engine’s optimizations take a certain amount
of time for each statement. However, the user benefits in a variety of concrete ways:
1. The Jet Engine optimizes SQL statements that were not well formulated by the application
programmer and usually returns its results more rapidly than if there were no optimization.
2. The Jet Engine reformulates SQL statements that are not adapted to the capabilities of the specific
server database so that they can be executed by this server database and takes over jobs that the
server database cannot perform.
3. In the case of SQL statements that use tables from several data sources within one join, the Jet
Engine resolves these statements, optimizes them and transfers the parts to the relevant server
databases for execution or executes the necessary jobs itself on a local Microsoft Access database
(MDB). Following execution, the Jet Engine puts the pieces of the puzzle back together and returns
the result to the application.
Consequently, users will often want to work with the Jet Engine because to do without it would require
making major changes to an existing application or would increase programming effort to a degree that
would be disproportionate to the value of the solution. Sometimes, however, small changes can already
result in a significant improvement in performance.
Before reading a few tips, you should be aware of certain limits: Initially, the response time increases
linearly and later on increases exponentially with the size of the result set. Bound controls respond
differently to large data sets, with responses ranging all the way to aborting the program and issuing an
error message. The mathematical limit for using bound controls is formed by the range of values for the
list index (up to 32767); depending on the concrete volume of data, however, memory management
already sets a lower limit.
What Is a Large Result Set?
When you issue an SQL query, a larger or smaller set of data is returned in the result for each individual
data row (see below under "Snapshot or Dynaset?") before the query has been fully processed and the time
is up.
31
SQL and Jet Engine
Adabas D: Access Plus
Therefore, it would be a good idea to calculate the size of the result sets beforehand. A result set of 1500
data rows is already considered to be "large". Try to obtain small data sets. As far as possible, avoid
accessing whole tables by using a SELECT statement without a WHERE clause , a "Database.OpenTable"
(in VB 3.0) or a "Database.CreateRecordset" with the "dbOpenTable" parameter (in VB 4.0 or, similarly,
in VBA in Microsoft Access). In an SQL statement, address only those columns that you will later need.
From your experience with other applications, you already know that response times basically depend on
the RAM (or main memory). The more free RAM you have available, the more fluently the system can
toggle between programs and the better the applications can respond. In the same way, a database
application can perform better when it attempts to maintain simultaneous access to as few data rows as
possible. In each individual data row, only the fields that are required should appear in a SELECT
statement. Although "SELECT *" is quickly written, the subsequent results for the application are much
slower and can have a significant impact on performance. Consider making MEMO (or LONG) fields
visible only on request.
When is it Advisable to Fill in Lists and Combo Boxes?
On a form, avoid using several combo boxes with a large number of entries that are filled from a data
control or from the program with an SQL query. If you are using Visual Basic or VBA for Microsoft
Access, if at all possible do not insert lists or combo boxes until they are to be used.
If this is not possible, try not to insert these activities in the Form_Load subroutine but wait at least until
the Form_Activate subroutine . If you decide in favor of the latter option, use a flag variable to prevent
initialization from being performed more than once (Form_Activate is called almost every time if the
focus is set to the form). Afterwards, call Do_Events one or more times to ensure that before the
initializations take place, the form is drawn, initially with empty controls.
Specific ways of programming are a question of taste. The example below is only a suggestion for
structuring Form_Activate:
Private Sub Form_Activate()
Static active As Integer
If active Then Exit Sub
active = True
DoEvents: DoEvents: DoEvents:
’ ...
’ Initializations ...
’ ...
’ In the end set focus to first entry
MyControl.SetFocus
End Sub
Snapshot or Dynaset?
You were already warned above about accessing entire tables. It is more difficult to know when to use
dynasets and when it is more convenient to use snapshots . Although snapshots are generally faster (and
therefore have the restriction that they cannot be updated), which is also the case for moderately sized
result sets (up to one- or two-thousand data rows), dynasets have a more intelligent strategy for highly
extensive result sets because they contain only a keyset (i.e., the unique index for each data row that is
used to retrieve the entire data row only as necessary) of the result and not the complete data rows.
Nevertheless, do not expect miracles with regard to performance when using dynasets. Time
measurements have shown that the strategic advantage of dynasets is not apparent unless they are used for
volumes of data for which users have already found the response time to be an inconvenience (more than
32
Adabas D: Access Plus
SQL and Jet Engine
thirty seconds).
Therefore, base your choice of using a dynaset or a snapshot only on whether you wish to allow updates.
If you must work with volumes of data for which the response time using a snapshot is unacceptable, read
the sections below.
Applications without Jet Engine
Many application programmers wish to have more direct control over the SQL database than they do
when they have to pass via the Jet Engine.
Programming the ODBC API
In order to obtain more direct control over the SQL database, you can call the functions of the ODBC
driver directly in the relevant DLL just as the Jet Engine does normally. As for the programming language
used, the only restrictions that apply are those that generally apply for DLLs. When you bypass the Jet
Engine, naturally you lose all the functions implemented by the Jet Engine
Program Structure
The structure of a VB program that accesses the database by calling the ODBC API directly is not
necessarily made more complex by this fact. However, there is no longer any use of data controls and
bound controls, nor can a number of data sources be accessed within one SQL statement. None of the
conveniences associated with the use of record-set and database objects are available in such applications.
At the same time, however, their main memory requirements are lower, dialog with the server database
can be controlled much more closely, and it is possible to execute SQL statements without becoming
blocked.
The figure below provides a general overview of the flow of an ODBC API application:
The figure shows that, following a preparatory phase during which the connection to the database is set
up, SQL statements are placed in a buffer as strings. The statement is then examined and prepared by the
ODBC driver, parameters – if any – are identified and buffers are prepared for parameter passing. Once
the parameter buffers have been filled with values, the server database can execute the prepared statement.
Execution usually results in one or more result sets being returned that contain data rows or an error. In
33
SQL and Jet Engine
Adabas D: Access Plus
contrast to access via the Jet Engine, execution of the statement does not yet transfer any data rows to the
application program, since this is the responsibility of the application (as is any optimization associated
with it).
Column Binding, Extended Fetch
The application asks the server database for data rows from the result set either one at a time or in blocks
(extended fetch) . Afterwards, the columns of the data rows can be accessed. The API also provides the
option of binding local variables to the individual columns before the result set is accessed so that these
columns will be filled with values directly when the ODBC driver fetches the data row(s) (column
binding) . It is recommended that you take advantage of this option since it not only saves time but also
avoids superfluous program lines.
System Tables
Naturally, this type of application can also suffer from poor performance as a result of unwise
programming. It is still generally advisable to address only the columns and data rows in an SQL
statement that are actually needed. If the application accesses system tables (lists of all tables, indexes,
views, users, etc.), keep in mind that in a larger database, these lists can take on considerable dimensions.
Therefore, once you have requested the necessary information, you should maintain it locally.
Required Declarations
If you decide to use the ODBC API, you will need several files containing the declarations of the
functions that form the API. Although it is possible to implement these definitions yourself using the right
documentation, it is also cumbersome. Fortunately, however, these files already exist as a component of
the ODBC SDK (Software Development Kit) Version 2.0, which must be purchased separately from
Microsoft Support:
odbcor_g.bi
Constants used by the core functions
odbcor_m.bi Declarations of core functions
odbext_g.bi
Constants required for the "extended ODBC functions"
odbext_m.bi Function declarations required for the "extended ODBC functions"
Aids to Decision-making
Use of the ODBC API does not automatically determine that you will use a specific database; instead, it
leaves this decision open until you need to utilize specific characteristics of the server database. There is a
number of good reasons for deciding in favor of the ODBC API when designing an application and for
rejecting the more convenient alternative, Jet Engine and DAO.
The application must make do with a small amount of main memory .
Performance is unsatisfactory, even though the server database quickly executes the application’s
SQL statements.
The application must utilize characteristics of the server database that are not accepted by the Jet
Engine even with SQL_Passthrough (e.g. passing of parameters to stored procedures, multiple result
sets).
34
Adabas D: Access Plus
SQL and Jet Engine
Knowledge of embedded SQL and ODBC can make it easier to decide in favor of the
higher-performance interface.
Programming with the RDO Data Model
The Enterprise version of the Visual Basic 4.0 programming environment provides a valuable alternative
to using the ODBC API, i.e. the remote data object (RDO) . This programming model, which is in
addition to the existing DAO interface, was created in order to provide application programmers with a
lightweight alternative to the ODBC API that allow you to access characteristics and options of server
databases that are not compatible with the Jet Engine and DAO programming model.
RDO is available only in the 32-bit variant of the Enterprise version of VB 4.0. Therefore, no programs
that use RDO can be generated for the 16-bit Microsoft Windows 3.1 and Microsoft Windows for
Workgroups platforms.
RDO has important benefits:
RDO is an extremely thin layer ("object wrapper") over the ODBC interface that has practically no
adverse effects on performance. RDO does require some memory, but significantly less than the Jet
Engine.
There is one data control that uses RDO, which serves to supplement the advantages of this
lightweight interface with all the bound controls.
Any existing ODBC driver can be used with RDO because RDO does not extend the defined ODBC
interface (Level II).
As with the ODBC API, all server database options can be fully exploited.
SQL statements that return a number of result sets can be executed more easily than via the ODBC
API.
Unlike other libraries, RDO follows the object-oriented approach of Visual Basic. Thus, the "For
Each" statement can be applied to the columns of a result set.
Many useful characteristics that are familiar from the DAO programming model (record sets, databases,
etc.) can be found in a similar form in RDO, making the "culture shock" with regard to well-known
techniques less apparent. Thus, it offers corresponding collections of tables, columns and result sets that
can be accessed as usual.
Aids to Decision-making
RDO is a useful programming model for applications that do not necessarily require the Jet Engine and
justify the greater effort required because of their degree of usefulness. SQL programming in SQL-PL,
which serves to generate DB procedures, DB functions and triggers, is also based on the RDO interface.
The higher programming effort as compared to the DAO interface is rewarded by improved performance.
Professional applications that exploit the possibilities of the server database beyond what is permitted by
the Jet Engine also rely on ODBC API programming or on the RDO programming model. Another benefit
in addition to performance is the option of storing procedures on the server database that encapsulate
multiple processing steps for the purpose of permanently modeling business transactions and taking
security aspects into account. The necessity of leaving the convenient abstraction level of the DAO model
is made more acceptable by RDO.
35
Only a Preliminary Step
Adabas D: Access Plus
Only a Preliminary Step
Automatic upsizing is only a preliminary step that creates a foundation on which to build. Although
upsizing quickly and easily provides you with the advantages of a client/server architecture, optimal
utilization of a server database such as Adabas requires that existing Access applications be revised.
36