HP 3000 Manuals

HP ALLBASE/BRW Reference Manual

Data Access Methods 

You define the data access when you define the table.  In most cases, you
use both the Define Relation screen, the Parameter screen, and the Tune
Access screen to define the data access.

This section discusses how you can define:

   *   the sequence in which the source tables are to be accessed

   *   which keys (IMAGE search items or KSAM keys) are to be used for
       accessing which source tables

   *   which source tables are to be read serially, HP ALLBASE/BRW
       default method

   *   which non-SQL source tables are to be read directly via record
       number

   *   which HP ALLBASE/SQL source tables are to be combined to one query

   *   which source tables are to be locked during access

   *   the database open mode for IMAGE data sets


NOTE  For better performance: 

      Check the report's compile listing for the transformation of tables
      into access blocks and the optimization of conditions and
      calculated items.

      Then, use the access block statistics printed with the report to
      determine which data access methods are most efficient for the
      report.

      Finally, if necessary, change the data access sequence on the Tune
      Access screen.  See Chapter 19  for details about data access
      tuning.


Tuning Data Access 

You can tune the data access for better efficiency after you have
selected the source tables for the report.

Choosing the right data access method can significantly improve the
performance level for a report.  HP ALLBASE/BRW lets you try different
data access methods.  Using different access methods does not change the
definition of the data in the report.

There are several things you can do to tune your data access for better
efficiency:

   *   Define the sequence in which the source tables are accessed.

   *   Specify keys (IMAGE search items or KSAM keys) to access certain
       source tables.  (The default is a serial read.)

   *   Read non-SQL source tables by record number using TABLE-REC-NUMBER
       as a key.

   *   Combine SQL tables belonging to the same DBEnvironment as one
       query using the same sequence number.

   *   Secure locks on IMAGE datasets or files.

   *   Define Open Mode capabilities such as "Read, allow concurrent
       modify".

To tune data access 

   1.  Define the source tables, relations, relation conditions, and
       parameters for your report, as described in previous chapters.

   2.  From the Define Table screen, choose Tune Access.

       The Tune Access screen lists the source tables you have specified
       and also shows the table type.


            

   3.  Type the access sequence for each source table.  See the
       discussion below for more information about access sequence.

   4.  Type any keys you want to use for non-SQL tables.

   5.  Type a Lock Mode, Open Mode, or Item Security code.  See the IMAGE 
       Data Base Management Reference manual for a full description of
       the DBOPEN modes and capabilities.  See also Chapter 17,
       "Configuration and Security."

   6.  Press Enter.

Defining Access Sequence 

Defining access sequence affects the order in which the tables are
accessed.  If one of the data sources listed is an MPE file, it is
usually most efficient to list it as the first data source.  Other
considerations when deciding on access sequence include:

   *   A source table on which an open join is made must be accessed
       last.

   *   HP ALLBASE/SQL tables belonging to the same DBEnvironment can be
       combined into one query by specifying the same access sequence
       number for each table.  (If the tables are listed consecutively
       and no open join is indicated, the same access sequence number is
       assigned by default.)

   *   If different access sequence numbers are entered for HP
       ALLBASE/SQL tables, separate queries are generated.

   *   If keyed access is used for the first source table in the access
       sequence, a relation must be defined and a single-value or
       list-of-values parameter must provide the value for that key.
       Using keyed access to the first source table can improve the
       performance of the report if you want to select only records for
       some distinct key values.

Example:  Keyed access to first source table 

The following is an example of keyed access to the ORDERS and
ORDER-DETAILS data set, in which the parameter ?CUSTOMER-NO is used to
provide the key values for accessing ORDERS.

The Parameter screen below shows the definition for the parameter
?CUSTOMER-NO:


            

The value for ?CUSTOMER-NO is provided when the report is executed.

Two relations are defined:  CUSTOMER-NO to ?CUSTOMER-NO and ORDER_NO
(from the ORDERS table) to ORDER-NO (from the ORDER_DETAILS table, as
shown on these two Define Relation screens.


            


            

The Tune Access screen shows the access sequence and keys used for these
source tables:


            

Access by Record Number.   

You can access a record in an IMAGE data set, KSAM file or MPE file
directly by using its logical record number (TABLE-REC-NUMBER) in a
relation.

In the following example, the file PRODX (format PRODUCT-INDEX) contains
an item (PRODUCT-REC#) that relates to the logical record numbers
(TABLE-REC-NUMBER) for the records in the data set PRODUCTS.

The common items PRODUCT-REC# and TABLE-REC-NUMBER are specified on the
Define Relation screen:


            

The access sequence is defined on the Tune Access screen:


            

Once the report is defined, you can experiment separately with ways of
accessing the data.  You can use serial read or chained/calculated
(keyed) reads to see which takes the less time.  The access method will
not change the result of the report.

HP ALLBASE/BRW verifies that the access methods are compatible with the
relational access definition.

MPE/iX 5.0 Documentation