1. BAAN IV database driver architecture
|
This chapter describes the architecture of the BAAN IV database driver in general. It supplies background information about the BAAN IV database interface to the supported relational database systems. This chapter also describes where you can find the database driver resource and log files on the disk of the BAAN IV server.
1.1 Introduction
BAAN IV supports a wide range of databases by having implemented an interface to each of these databases: the database driver. One of the supported databases is the DB2 relational database management system (RDBMS). The interface to the DB2 RDBMS is the BAAN IV DB2 database driver.
The distribution of BAAN IV tables in different databases is transparent to the user. First accessing the data from these tables is done by using BAAN IV SQL. The BAAN IV SQL query is then converted to DBMS SQL at the driver level and is passed to the DBMS server. Finally, the database driver performs the join of rows returned by the RDBMS server as a result of the execution of the DBMS SQL query.
The driver with single table SQL queries passed to the RDBMS is called the Level 1 driver. In this architecture, the join on rows is performed by the driver before returning the result row to the BAAN IV client.
The Driver with multiple table joins passed to the RDBMS is called the Level 2 driver. In this architecture, the driver relies on the RDBMS to perform table joins. The resulting rows returned by the RDBMS are just returned to the BAAN IV client.
This document addresses the DB2 driver in Level 1 mode, which allows BAAN IV tables to be created and manipulated in the DB2 database. As all the database drivers have some common functionality, this document discusses the common part first and then describes the DB2 database driver specific details.
1.2 Database driver
This section supplies information about the BAAN IV database driver, and discusses the following topics:
n The BAAN IV database driver in general
n SQL processing
n database driver features
These topics are discussed in the sections below.
1.2.1 The BAAN IV database driver in general
The database driver acts as an interface between BAAN IV Tools and the running RDBMS, and is part of the BAAN IV Tools client/server architecture. The interaction with the RDBMS Server is performed by the BAAN database driver. The driver acts as a server to the application (BAAN IV client) and communicates with the RDBMS. The client (that is, the application being executed by bshell6.1) sends database requests to the server (the BAAN IV database driver), which in turn transfers the request to the RDBMS Server. Data returned by the RDBMS server is sent back to the client by the driver.
An overview of the architecture is shown in the picture below (in this picture the DB2 RDBMS is used).
Figure 1-1 BAAN IV database driver architecture
1.2.2 SQL processing
When the bshell encounters a database query in the application that is being executed, it parses the query and sends an internal representation to the database driver. The driver translates this into one or more single table queries in the SQL dialect that is relevant to the underlying RDBMS. The driver connects to the RDBMS by using the available call level interface (CLI) and the queries are then handed over to the RDBMS. The rows returned by the RDBMS as a result of the query are collected by the driver and sent back to the client. The error conditions are caught and handled by the database driver.
The processing of BAAN IV SQL queries is as follows. The driver receives database independent representations of queries which are processed further to generate RDBMS compatible queries. The BAAN IV database driver consists of an RDBMS independent section and an RDBMS dependent section. When it comes to evaluating the SQL expressions, it can occur that some expression cannot be handled by the underlying RDBMS. For example, some operators such as the LIKE operator may not be supported by the RDBMS because the BAAN IV definition of the operator is different from that of the RDBMS.
Such expressions are translated into an equivalent statement that is supported by the underlying RDBMS. All expressions which can be supported by the RDBMS are put together and a database expression is generated. This is done by the database dependent layer within the driver.
1.2.3 Database driver features
The BAAN IV database driver architecture contains the following features.
n Data dictionary (DD)
The BAAN IV client DD is different from the DD at the server side. A common pool of DDs at the server side makes the DD sharable. The characteristics of the client DD (column names, data types, column size, and number of columns) differ from the server DD. The server DD contains the column-specific information in terms of the RDBMS format. There is one table DD at the server side for every table DD at the client side. DDs of different company numbers can be shared within the server, so they need not be sent again by the client.
n Distributed joins
The BAAN IV SQL query can contain multiple tables (joins). When tables are physically located in different databases (distributed), the query is split into parts for each database. A driver is activated by the bshell for each database.
n Security
The authorized BAAN IV users are registered in the administration files kept in $BSE/lib/rdbms directory. The $BSE/lib/rdbms/rdbms_users file contains the user name and password in encrypted form. The driver establishes a connection to the database for the user using this password. The administration of these files is done by user root or by the DBA (database administrator) account. For every BAAN IV session there is a corresponding connection established by the driver.
n Referential integrity
The BAAN IV database driver internally maintains referential integrity for update and delete actions, provided that referenced tables are in the same database. Therefore no extra communication between client and server is involved. For cascade updates and deletes, the driver can generate a single statement to replace several row-level queries. For example, it is possible to delete all referencing rows at once. This is much more efficient than deletion on a row-by-row basis.
n Data integrity
Data integrity is enforced by using row level locking mechanisms. As a result different BAAN IV sessions, and even packages or tools other than BAAN IV which also use normal locking, are concurrent. Implicit locking is applied whenever possible. In other cases the driver will perform explicit locking.
n Statement buffering
Updates (inserts, updates, deletes) are buffered at the client side and flushed at the time of transaction commit, or earlier when necessary. This reduces the number of network round-trips and data volumes.
n Data buffering, reduction, and compression
When multiple rows are returned from a query, the rows are buffered and then sent back to the client as one block. Also, data reduction and compression is applied to minimize the amount of data transferred between the bshell and the driver.
n Delayed locking and updates
The delayed locking mechanism is performed by the driver. This means that before an update is done, the driver checks on a column-by-column basis if the related columns are changed. If the related columns are not changed, the update is NOT carried out. No extra network communication needs to be done for this.
1.3 Resource file
The database drivers developed for BAAN IV refer to a common administration file in Baan Software Environment (BSE). The environment variables defined here set the behavior of the driver at runtime.
The variables common to all drivers as well as those specific to each RDBMS can be specified in the file $BSE/lib/defaults/db_resource resource file. These variables are read at runtime by the driver.
The $BSE/lib/defaults/db_resource file contains the resource-variable names and values in the syntax shown below.
<resource_name>:<value>
An alternative resource file can be specified by defining the USR_DBS_RES environment variable, for example:
USR_DBS_RES=$BSE/lib/defaults/my_resource
This command results in the database driver reading the specified resource file, which will overrule the default resource file.
1.4 Log files
This section describes the logging information that can be logged by the BAAN IV database driver. This logging information can be:
n database driver trace information
n Error logging
These types of logging are discussed in the sections below.
1.4.1 Database driver trace information
The database driver provides an option to log online information about the actions that are being performed by the driver. The resulting log file contains debugging information which can help solve problems. Information stored in the log files includes:
n Table/index information (data dictionary)
n The SQL statements being executed
n Values of the input and output bind variables
n Other function-level debug statements
Several tracing categories are defined, so that tracing can be enabled for only those categories that are of interest. Tracing can be enabled using the environment variable DBSLOG. Debugging information will be appended to the dbs.log file in the current directory. If this file does not exist, it will be created. For example, tracing can be enabled by entering the following command:
DBSLOG=0560
The information in the log file can be really technical and may be subject to change. To see what categories are available, refer Appendix A.
1.4.2 Error logging
The database driver logs its error messages in the file log.rdbms, which resides in the $BSE/log directory. The following information can be retrieved from this log file.
n General information (user name, date, time, source and line, and so on)
n The error code returned by the database
n Database error description
n The BDB error code returned to the application
n In many cases a reference to the failing SQL statement is also logged
If a database specific error occurs, it is mapped to the BDB error code by adding 1000 to it (that is, abs(error_code) + 1000). So, when an error -1652 occurs, BDB error 2652 is returned to the application.
In most cases, the various log files in the $BSE/log directory, for example, log.bshell6.1 and log.rdbms contain enough information to determine the nature and cause of a problem. Therefore, whenever a 1000+ error is encountered, you are advised to check the log file of the driver ($BSE/log/log.rdbms).
1.5 Tuning aspects: profiling and statistics
The database drivers also enable users to log timing aspects and statistics. This is useful for tuning purposes, because the information may help to identify performance bottlenecks and can give input to the process of tuning. The following options are provided by the BAAN IV database drivers.
n Profiling
n Statistics
These options are discussed in the sections below.
1.5.1 Profiling
The profiling option of the drivers provides the user a way to gather timings of SQL statements that are being executed. Logging all statements with their timings, however, will result in a log file which is so big that it cannot be properly analyzed. Therefore, it is possible to define a logging threshold. Only statements which takes longer than a predifined number of seconds will be logged.
Profiling can be enabled by means of an environment variable. The profiling output will be logged to a file in the current directory.
Suppose the profiling environment variable is set to 5 sec, then each statement which takes longer than 5.0 seconds (real time) will be logged to the log file. The information that is logged in the file is the SQL statement, with the elapsed time and user, date/time. The maximum precision which can be specified with the environment variable is 0.01 seconds.
Note that the following two statement types are timed and can appear in the log file :
n The execute event
This event represents the amount of time the RDBMS engine took to execute an SQL statement.
n The fetch event
This event represents the amount of time data is retrieved from the buffer.
1.5.2 Statistics
The drivers also provides an option to gather driver-wide statistics of actions being performed, such as:
n Number of cursors (opened, closed, current open)
n Number of parses, binds, executes, fetches
n Number of logons (sessions)
n Number of inserts, updates, deletes
n Number of commits, rollbacks
For each action, the cumulative (real) time spent by the action and average time is also logged. The statistics can be enabled with an environment variable. When the variable is set to 0, a statistics report is generated when the driver terminates (exit from BAAN IV Tools or session). When a value greater than 0 is specified, the driver logs an incremental report each [n] seconds (driver must be active). The statistics report is written to a file in the current directory.
The section “DB2 Driver Profiling and Statistics” in Chapter 4 describes how the environment variable must be set to enable profiling and statistics.
Since the report is generic for all databases, some information, such as the specific ROW actions, may not be appropriate for a particular database driver. The report format is also subject to change.
2. Installing DB2 for BAAN IV
|
This chapter describes:
n the hardware and software requirements
n how to install DB2 Version 2.1
2.1 Hardware and software requirements
To install DB2 on your BAAN IV server, you need the following hardware and software:
n A RS6000 processor
n AIX Version 4.1.4 must be installed and running
n DB2/6000 version 2.1.2 or later
n The TCP/IP software must be installed, configured, and running
n A CD-ROM drive
n A network adapter
By default, the DB2 software will be installed in the /usr filesystem. Be sure to have sufficient diskspace in this file system.
The distribution medium of the DB2 software is either an 8millimeter tape or a CD-ROM.
2.2 Installing DB2
To install the DB2 software, take the following steps.
1 Log in as user root and start the smit utility.
2 Select the Software Installation and Maintenance option and press Enter to continue.
3 Select the Install and Update Software option in the next screen and press Enter to continue.
4 Select the Install/Update Selectable Software (Custom Install) option in the next screen and press Enter to continue.
5 Select the Install/Update From All Available Software option in the next screen and press Enter to continue.
6 Use F4 to select the input device/directory of the DB2 distribution and press Enter to continue.
7 Use F4 to select the software to install. You must select the following software packages (note that you can select them by using ESC+7 or F7, depending on your terminal; follow the menu at the bottom of the screen).
– DB2 Client Application Enabler (if implementing client/server architecture using DB2 native networking)
– DB2 Command Line Processor
– DB2 Communication Support – base with TCPIP
– DB2 Database Director
– DB2 Executables
– DB2 Utilities and Samples
– DB2 Visual Explain
You must also select the appropriate language version of the following packages:
– DB2 Product Library
– DB2 Postscript Library
– DB2 Product Messages
– DB2 Messages and Resources
These are the minimum requirements. You may choose to do a full installation.
8 After you have selected the packages shown in step 7, you can press Enter to continue. The software will be copied to disk.
This step completes the DB2 installation process.
This chapter describes:
n How to prepare the BAAN IV installation
n How to install BAAN IV
n How to install a client/server environment using IBM Network Software (CAE)
In the section about the installation of BAAN IV, no step-by-step description of the installation procedure is supplied. For an extensive description of the installation and configuration procedure of the BAAN IV software, refer to Chapter 2 of the BAAN IV Installation Guide (U7026A US).
3.1 Preparing the BAAN IV Installation
After the DB2 RDBMS has been installed as described in Chapter 2, the following steps must be taken before you can start installing BAAN IV. Note that you must be logged in as user root to take the steps listed below.
n Adding some UNIX user and group accounts
n Modifying the UNIX user account root
n Creating a DB2 instance for BAAN IV
These steps are described in the sections below.
3.1.1 Adding UNIX group and user accounts
The following UNIX group accounts must be created before you can continue the installation.
n a UNIX group db2 which will serve as the database system administrator
n a UNIX group whose name must correspond to the DB2 database which will be created in the section “Installing BAAN IV”, later in this chapter. In this example installation this group is called baan.
n a UNIX group bsp which will serve as the BAAN Software Environment administrator
You can add the groups listed above using the smit utility and by selecting the Security & Users option, followed by the Groups option.
The following UNIX user accounts must be created before you can continue the installation:
n A UNIX user whose name must correspond to the DB2 instance which will be created in the section “Creating a DB2 instance for BAAN IV”, later in this chapter. In this example installation this is user db221. You must make user db221 a member of the group db2 you created before.
n A UNIX user whose name must correspond to the DB2 database which will be created in the section “Installing BAAN IV”, later in this chapter. In this example installation this is user baan. You must make user baan member of the group baan you created before.
n A UNIX user bsp. The primary group of user bsp must be the group bsp. You must make user bsp also member of the group baan you created before.
You can add the users listed above using the smit utility and by selecting the Security & Users option, followed by the Users option.
3.1.2 Modifying the UNIX user account root
You must modify the UNIX user account root by making him member of the group baan you created in the previous section, “Adding UNIX group and user accounts”.
All BAAN IV users requiring access to the database must be added to the group baan. All users requiring database administration access must belong to the group db2.
3.1.3 Creating a DB2 instance for BAAN IV
Before you can install BAAN IV, you must create the DB2 instance for BAAN IV. As mentioned before in this chapter the instance is called db221 in this example installation. To create the instance, change directory to /usr/lpp/db2_02_01/instance and enter the following command.
./db2icrt db221
The instance is created in the home directory of the user db221.
You are now ready to install the BAAN IV software.
3.2 Installing BAAN IV
This section describes the DB2 specific steps which are involved in the installation of the BAAN IV software. This section does not supply a step-by-step description of the installation procedure for BAAN IV itself. For an extensive description of the installation procedure, refer to Chapter 2 of the BAAN IV Installation Guide (U7026A US).
After having read the BAAN IV software from the distribution medium, the BAAN IV installation script install6.1 must be executed. This script prompts the installer, among other things, for the database type. At this point the DB2 database must be specified.
The install6.1 installation script will at a certain point during the installation ask you if you want to run the DB2 installation script. You must answer Yes, after which you will get a UNIX prompt. Enter the following command to start the DB2 driver installation script.
db2_install6.1
The db2_install6.1 install script prompts you to enter the following information:
During the execution of the db2_install6.1 install script you are asked if you want to create the tablespaces for the system catalogs, BAAN IV tables (user tablespace), temporary data (temporary tablespace), and the index data. In general, you are recommended to have the BAAN IV tables, index data, and the DB2 system catalogs on separate tablespaces on different disks to improve performance. It is also recommended to create the tablespaces as database managed space using raw devices. For the best performance, tables for each package can be stored in a different tablespace and/or the tables with the highest growth rate can be separated from other tables and placed in a tablespace with a larger extent size to avoid fragmentation and to allow tablespace level backup and recovery.
n DB2INSTANCE
Enter the name of the DB2 instance you created, in our example installation db221.
n DB2INSTANCEPATH
Enter the pathname to the DB2 instance. This must be the home directory of the user db221.
n Creation of a database for BAAN IV
The db2_install6.1 install script asks if you want to create the database for BAAN IV. If you type Y, you are asked to supply the following information:
– The database name
this name must correspond with the UNIX user you created earlier in this chapter, in our example installation baan.
– The path where the database should reside
You can accept the default path (in our example installation the home directory of the user db221), or enter an alternative path.
n Code set
Accept the default code set.
n Territory
Accept the default displayed language version. This is the version you selected during the installation of DB2.
n Extent size
The extent size parameter defines the number of pages that are written before writing continues in another extent or segment. You can accept the default value. If this value is not an efficient trade-off between the potential for wasting space and the cost of extending a database object, you can retune the extent size at a later stage.
n Catalog tablespace
A set of system catalog tables is created and maintained for each database. At this point of he installation you must create the tablespace for the system catalogs. Type Y. You are asked to supply the following information:
– Managed by system or database
In a system managed space (SMS) tablespace, the operating system’s file system manager allocates and manages the space where the table is to be stored. In a database managed space (DMS) table space, the database manager controls the storage file. You are advised to select Database Managed Space (DMS) table space. So, select D.
– Container type
A container is the allocation of space to a table space, and can be either a file or device. You are advised to select D (device) as the container type for better performance. You are then prompted for the device path. Note that the device must already exist. (the raw partition must be owned by the database baan and belong to the primary group baan).
– Container size
Enter the size of the container (in 4K pages).
– Add another container path?
You can increase the size of a tablespace by adding one or more containers to it. If you decide to add one or more containers, you must supply the same information as you did for the first one. It is advised that the containers for a tablespace be allocated on different disks to stripping of data.
– Extent size
You can accept the default value.
– Prefetch size
The prefetch size specifies the number of pages that will be read from the tablespace when data prefetching is being performed. Prefetching reads in data needed by a query prior to it being referenced by the query, so the query need not wait for I/O to be performed. You can accept the default value.
– Overhead number of milliseconds
This specifies the I/O controller overhead and disk seek and latency, in milliseconds. The number should, preferably, be the same for all containers belonging to a tablespace.
– Transferrate number of milliseconds
This specifies the time to read one 4Kbytes page into memory, in milliseconds. The number should, preferably, be the same for all containers belonging to a tablespace.
n User tablespace
After creating the tablespace for the system catalogs you are asked to create the tablespace for the user data. You can do so at this point of the installation, but in general the database administrator will create this tablespace manually, after having finished the installation of BAAN IV. If you decide to create this tablespace at this point, you must follow the same procedure as for the tablespace for the system catalogs (see previous section).
n Temporary tablespace
The next tablespace to be created is the tablespace for the temporary data. You are advised to create this tablespace at this point of the installation. If you decide to create this tablespace at this point, you must follow the same procedure as for the tablespace for the system catalogs (see previous section).
The BAAN application will rarely – if at all – use the temporary tablespace since it is primarily used for sorting intermediate data. Take this into consideration when allocating space.
After you have entered the necessary information you are asked if the database for BAAN IV must be built. Type ‘Y’.
n After the DB2 database is successfully created, you are asked if you want to create the tablespace for the index data. You can follow the same procedure as for the creation of the tablespace for the system catalogs. You must supply the following additional information:
– Tablespace name
You can accept the default name IDXSPACE.
– Regular/temporary
Accept the default value (Regular), which means that all information except for temporary data is stored.
n The last step in the execution sequence of the db2_install6.1 script is the creation of the administrative files required by the DB2 driver in the directory $BSE/lib/db2. You are asked if you want to create the following files:
– db2_groups
This file contains the group logon name and password, which must be supplied by you.
– db2_users
This file contains the DB2 user name and password. This file contains the initial BAAN IV users that are authorized to access the database, namely root and bsp
– db2_storage
This file sets up the default entries to be used while configuring tables. You are asked if you want to distribute the table and index data. It is recommended to distribute this data for performance reasons. If you select distributed data, you are asked to supply the tablespaces. You can accept the displayed default tablespaces. If you do not want to distribute the table and index data, the data will be written by default to the USERSPACE1 tablespace.
For more information about the administration files, refer to the sections “DB2 Table and Index Location” and “Security” in Chapter 4.
n After the administration files are created by the db2_install6.1 script, the UNIX prompt is displayed. You can type exit to return to the install6.1 installation script and continue the installation of BAAN IV.
3.3 Client/server installation using IBM network software (CAE)
The following two client/server models are available to implement a BAAN IV client/server installation.
n The BAAN IV client/server model
This model places the database driver (db2_srv6.1) on the database server system. For details on how to implement this scenario, refer to Chapter 14 of the ‘Baan Triton Tools Technical Manual’.
n The IBM CAE model
This model places the database driver on the client application box.
The setup of the second scenario is discussed in this section.
The general steps described below must be followed to establish CAE as the communication layer in a client/server environment:
1 On both client and server systems IBM CAE software must be installed.
2 On the server system you must follow the steps described in the sections ‘Adding UNIX group and user accounts’, ‘Modifying the UNIX user account ‘root” and ‘Creating a DB2 instance for BAAN IV’, earlier in this chapter. You must then run the db2_install6.1 script. Note that the database must be created on the server system. This implies that a partial installation of BAAN IV must exist on the server. The directory structure, files, and directories listed below must be present on the server:
– $BSE/bin/db2_install6.1
– $BSE/bin/db2_maint6.1
– $BSE/bin/bput6.1
– $BSE/bin/binput6.1
– $BSE/lib/db2
The db2_install6.1 script will create the database on the server system and put the following files in the directory $BSE/lib/db2:
– db2_storage
– db2_users
– db2_groups
These three files must be copied to the $BSE/lib/db2 directory on the client. Once installation is completed, the partial $BSE environment on the server can be discarded.
3 Create a UNIX user on the client system, whose login name corresponds to the name of the instance to be created in step 4
4 Create a DB2 instance on the client system.
The name of this instance will be the value of the environment variable DB2INSTANCE stated in the client’s tabledef6.1 file (not the name of the instance on the server machine).
5 On both the client and the server system, modify the db2profile file in the sqllib subdirectory of the instance’s home directory to add or change the following environment variables:
DB2COMM=TCPIP
DB2DBDFT=BAAN (database name that is to be used at the database server)
DB2INSTANCE=(the name of the instance on the respective machine)
DB2_RR_TO_RS=ON
Make sure that this file is executed in the startup shell for user root and all other users who need to access or maintain the database outside of
BAAN IV.
6 Look for two unused TCP/IP port numbers higher than ‘1024’ to be used for DB2 client/server communication. Register these ports in the /etc/services files of both the client and server systems. The names for the port numbers are to be referred to as the service name.
7 Register the database created on the server to the client by entering the following DB2 commands on the client system:
CATALOG DATABASE database name AT NODE server node AUTHENTICATION SERVER
CATALOG TCPIP NODE nodename REMOTE hostname SERVER service name
8 Test the installation by attempting to connect to the server machine by entering the following command:
db2 connect to database name user user name using password
3.3.1 Testing the client/server installation
To test the client/server installation, just create a table, insert data, and delete the table by using BAAN IV. The purpose of the test is to demonstrate that the BAAN IV DB2 driver is capable of communicating with the remote database server. The following procedures are used to execute this test:
1 Create a company to be associated with DB2 by using BAAN IV Tools. Run the session Maintain Companies (ttaad1100m000).
2 Define a database to be used with DB2 by running the session Maintain Database Definitions (ttaad4110m000). Specify DB2 as input for the field Database Type and DB2INSTANCE as input for the field Parameter, for example, DB2INSTANCE=db221. This should be assigned the value of the instance created on the client side.
3 Assign tables to the database defined in step 2 by running the session Assign Tables To Databases (ttaad4111m000).
4 Create a table in the company created in step 1 by using the Create Tables (ttaad4230m000) session. You can find this session in the Miscellaneous submenu of the Database Management menu.
5 Insert data into the newly created company by using the General Table Maintenance (ttaad4100) session.
6 Delete the table by using the Delete Tables (ttaad4231m000) session. You can find this session in the Miscellaneous submenu of the Database Management menu.
If no error is reported then a successful installation had been carried out.
4. DB2 database driver externals and internals
|
This chapter supplies additional information about the DB2 database driver. It describes both the external aspects of its communication with BAAN IV and the internal features of the DB2 database driver.
4.1 DB2 driver externals
This section describes the characteristics of BAAN IV tables created in the DB2 RDBMS through the DB2 Driver in level-1 mode, where BAAN IV SQL queries are processed by the DB2 driver on a single-table/single-row basis.
4.1.1 Data dictionary mapping
Since the data dictionary (DD) of BAAN IV cannot directly be used by the driver to create DB2 tables, some mapping or translation is performed on the driver side in order to create valid DB2 tables.
The following conventions play an important part in this process:
n General name convention
In general, all names generated by the driver are put in lowercase characters, not enclosed within double quotation marks. Hence DB2 converts these names to uppercase when storing in the system catalogs.
n Table name convention
The table name of a BAAN IV table in DB2 has the following format.
t<Package><DD table name><Company Number (3 digit)>
For example, the BAAN IV table ttadv999 with company number 505 is created in DB2 as TTTADV999505.
n Column name convention
Each column in the BAAN IV DD corresponds to one or more columns in the DB2 table.
The rules are listed below.
– General
In general, column names are preceded by the t_ string. For example, a BAAN IV column named cpac is created in DB2 as t_cpac. This is done in order to avoid reserved words. For example, a column name like order should not be used in DB2 as the order column name, since it is a reserved SQL word. Also, when (column) names contain a period [ . ], the period will be replaced by the underscore [ _ ] character.
– Long string columns
Columns of type string in BAAN IV can exceed the maximum length of character columns in DB2. For CHAR data type this limit is 254. When the BAAN IV string column exceeds this limit, the column is split into parts of 254 characters. For the first part of the column, the column name is extended with _1, the second part with _2, and so on. For example, a BAAN IV string column, called desc with a size of 300 characters, will result in two DB2 columns:
t_desc_1: size 254
t_desc_2: size 46
– Array columns
In BAAN IV array columns can be defined. An array column is a column with internally multiple elements. The number of elements is called the depth. For example, a column (‘date’) can be defined as an array of three elements. In DB2 each element refers to one (or more) column(s), that have the element number added as a suffix to the column name ( ‘<col_name>_<array element number>’ ), as in:
t_date_1: element 1
t_date_2: element 2
t_date_3: element 3
Note that if the element is of type string and one element type exceeds the maximum DB2 character size of 254, it is again split, as in:
t_str_1_1: element 1, part 1
t_str_1_2: element 1, part 2
– Array compression
When the number of DB2 columns exceeds the maximum number of DB2 columns (255), the driver will try to compress (join) array columns in order to reduce the number of columns. This means that all array elements of one array column are going to be stored as one RAW column in the DB2 database. The raw value contains all the elements concatenated in binary format. The driver starts compressing the array column which will yield the highest number of columns. It continues compressing array columns until the number of columns has become lower than 255. The name of the column in DB2 will be the same as normal columns, as in:
t_array: raw contains all elements.
Note that when a compressed array column is longer than 254 characters, it will also be split in parts of 254 characters or less.
n Index name convention
BAAN IV indexes are identified by a sequence number, starting from 1 for each table. Each table has at least 1 index (primary index). Since in DB2 the index names have to be unique to the user, the table name, index number, and the index type (the order, that is ascending or descending) are included in the index name.
The example below shows the general format and the format of the index name created for BAAN IV table ttadv999, index number 1 (company number 505) in ascending order:
I<table_name>_<idx_no><idx_type>
Ittadv999505_1a (index 1 of ttadv999 in ascending order)
The first letter of the index type (ascending or descending) is appended to the index name.
n Data types
The DB2 driver uses the CHAR data type which conforms to the ANSI standards. This is used because BAAN IV string data type has characteristics which conform to the ANSI specifications. Operations such as comparison and concatenation can be done in ANSI-compliant way when the CHAR data type is used. The table below shows the mapping between BAAN IV data types and DB2 data types being used.
The mapping between BAAN IV and DB2 data types
|
BAAN IV Data Type
|
DB2 Data Type
|
CHAR
|
CHAR(1)/VARCHAR(1) for bit data
|
ENUM
|
CHAR(1)/VARCHAR(1) for bit data
|
INT
|
SMALLINT
|
LONG
|
INTEGER
|
MAIL
|
INTEGER
|
TIME
|
INTEGER
|
TEXT
|
INTEGER
|
BITSET
|
INTEGER
|
FLOAT
|
DOUBLE
|
DOUBLE
|
DOUBLE
|
STRING
|
CHAR(n)/VARCHAR(n)
|
DATE
|
DATE
|
Since BAAN IV SQL uses ANSI-compliant string comparison semantics, the CHAR data type is used, instead of VARCHAR.
n Constraints
All the columns created by the DB2 driver have the NOT NULL constraint. BAAN IV does not support NULLS.
For indexes, if a BAAN IV index is defined as a unique index, then the DB2 index is also created with the UNIQUE clause. Otherwise (duplicate) indexes are not defined with the UNIQUE clause.
The bshell range of dates are the same as in DB2 with the following exception. The bshell date 0 is mapped to date 1 in DB2. The bshell date 1 is marked as an invalid date.
4.1.2 DB2 table and index location
The DB2 RDBMS provides a way to specify the distribution of table and index data in different tablespaces. The storage (or creation) of parameters can be specified in the $BSE/lib/db2/db2_storage file. The storage parameters are used by the DB2 driver when DDL statements are executed (for example, create table and create index). The format is as follows:
[{<user_list>}]<table/module specification>:<company number>:<object>: <private/group>:<table/index optimization>:<refresh_time>:<storage parameters>
n user_list
This entry is a list of comma-separated users to which this entry applies. When no list is specified, the entry applies to all users. For example:
{harshad,peter,john}
n table/module
This entry is a list of comma-separated table names or module name to which the entry applies. An asterisk [ * ]indicates all tables. For example:
ttadv000,ttadv999 (both tables)
ttadv (all ttadv tables)
* (all tables)
n company number
This entry is a list of company numbers of tables to which the entry applies. An asterisk [ * ] indicates all company numbers. For example:
000,505 (companies 000 and 505)
* (all companies)
n object
This entry is a list of object (table or index) identifications to which the entry applies. The following can be specified: T (table only), I (all indexes), I<index number> (only specified index), and asterisk [ * ] (both table and indexes). For example:
I1,I2 ( only index 1 and 2)
T (only for table)
n private/group
This entry is the identification of the owner of the table. Here one of the given keywords should be specified. This will be explained in the section “Security”, later in this chapter.
n table/index optimization
In this entry some specific flags related to indexes and tables can be specified. When specified on a T object entry, it defines the default for all indexes.
n refresh time
This entry specifies the amount of time for which a retrieved set of rows is valid.
n storage parameters
This entry specifies the location of the table and the index data. For example:
T_SPACE userspace1 I_SPACE idxspace
or
T_SPACE baanspace I_SPACE idxspace
If the tablespace for table and index data is not specified then the table and index data is created in USERSPACE1. DB2 creates this space by default when the databse is set up. If the index data is to be distributed in a different table space it is mandatory to specify the tablespace for tabledata.
The db2_storage file is scanned from the beginning whenever a create table or index is created. The first entry that matches the table or index is taken. So the order in which the entries are specified is important. For example:
{peter,john}ttadv999,ttadv000:000:T:private:010:5:T_SPACE datspace I_SPACE idxspace {peter,john}ttadv999,ttadv000:000:I::010:5: tdsfc:505::group:010:5:T_SPACE tdspace *:*:T:group:010:5:T_SPACE baanspace I_SPACE tri_idxspace *:*:I:group:010::
In this example the users peter and john will create tables ttadv999 and ttadv000 of company 000 in tablespace datspace. The indexes will be created in tablespace idxspace. All other users creating tables for module tdsfc will create tables in tablespace tdspace and the indexes will be created in the default tablespace, which is USERSPACE1.
Other users will create the tables and indexes in tablespace baanspace and tri_idxspace.
4.1.3 Security
This section lists the security aspects. The following aspects can be distinguished:
n Authentication
BAAN IV users mapped to DB2 users are allowed to establish a connection to DB2 RDBMS with their own user name and password. When a database is created, a super user or a DB2 DBA grants connect and createtab privileges to the group user (the group user corresponds to the database). The members belonging to this group inherit these privileges and are able to establish a connection to the database by using the valid UNIX password stored in the encrypted form in the administration files. A user can be added to or dropped from the group by using the BAAN IV administration tools (db2_admin6.1 or db2_install6.1). The users who are authorized to access the database are registered in the BAAN IV administration files. The user name and password each BAAN IV user will use to log on to the DB2 RDBMS is maintained in the $BSE/lib/db2_users file. This will be explained later in this section.
n Object security
In DB2, when a user creates an object, for instance, a table, the user becomes the owner of the object, and only the owner can access the object. Other users can only access the table if they have been granted privileges to do so. In a BAAN IV environment where many users access the same tables in the DB2 database, a mechanism has been developed to allow users to share these tables.
n The group concept
In order to allow different DB2 users share the same table, a group concept has been introduced. DB2 makes use of the group concept provided by UNIX. The members of the group can perform the operations granted to a group user. Users are assigned to a particular group and as a result they inherit the privileges granted to the group. This group has its own logon to the DB2 RDBMS and can be considered as a central owner of the tables in the group. The advantage of having a group table is that the members of the group can share and operate on the table data.
n For example, users peter and john can both be assigned to group baan. As a result users peter and john inherit the privileges on the group tables allowing them to access and manipulate group table data.
n A user can also define whether a table should be created in its group schema or in its private schema. When a table is identified as a private table, the user becomes the owner and no privileges are given to other users. When a table is identified as a group table, the table is created under the group schema and the privileges are granted to the group user. Whether a table is privately owned or belongs to a group is defined in $BSE/lib/db2_storage file.
n The DDL statements generated by the driver do not specify to which schema an object is going to belong (that is, no create <user>.<object> statement is generated>). The schema used is the default schema in which the user logs on. When creating objects that are identified as belonging to a group, the user who is creating those object actually logs on to the RDBMS as the group user. In this case the default schema will be the group’s schema. When creating objects identified as private the user logs on to the RDBMS as himself. In this case the default schema is his own.
n Authentication externals
All the BAAN IV users and their corresponding DB2 logon name and password and the name of the group they are assigned to are defined in the file$BSE/lib/db2/db2_users. The format of each entry in this file is shown below.
<BAAN IV user>:<DB2 user>:<Encrypted UNIX password>:<Group name>
The DB2 driver is started by a BAAN IV user. From the $BSE/lib/db2/db2_users file the driver identifies the DB2 user and the user’s password and logs on (that is, establishes the connection) to the DB2 RDBMS.
The group logon procedure also includes a password, which is defined in the file $BSE/lib/db2/db2_groups. The format is as follows:
<Group name>:<Encrypted Group password>
n Object security externals
The definition of the table to be created as a private or a group table is defined in the file $BSE/lib/db2/db2_storage. The format has already been explained in section “DB2 Table and Index Location”, earlier in this chapter. The keywords goup or private can be specified in the appropriate field. For example:
{peter,john}tdsfc:505:T:group:010::T_SPACE tdspace
{charlie}tdsfc:505:T:private:010::T_SPACE privatespace
This indicates that users peter and john create tables tdsfc* as group tables and that user charlie has its own private tables.
n Security internals
Internally the DB2 driver uses the UNIX group user to implement the group concept. Initially the group/database user is granted “connect” and “createtab” privileges The members of the group automatically inherit these privileges and can individually perform SELECT, INSERT, DELETE, and UPDATE operations on the group tables. This provides flexibility in terms of adding and deleting users from the group.
When a new user is added to the group, the user inherits the group privileges and there is no need to explicitly grant privileges on each and every existing table within that group. Also, when the user is dropped from the group, his privileges are revoked. As a result, he cannot longer access the tables owned by that group. If the privileges to operate on the tables were explicitly granted to the user, then they have to be explicitly revoked when the user is dropped from the group. This overhead is now avoided by granting privileges to the group user providing flexibility and easy maintenance.
4.1.4 DB2 error reporting
When the DB2 driver encounters an error, an error message is logged in two files in $BSE/log directory. The files involved are log.db2 and log.db2.mesg. The error message contains the DB2 error sqlcode, sqlstate, and the event that caused the error. Refer to the section “Error logging” in Chapter 1 for details.
4.1.5 DB2 driver profiling and statistics
This section describes profiling and statistics, as implemented in the DB2 database driver.
Profiling
To activate the profiling of the DB2 driver, the DB2PROF environment variable needs to be defined and exported. For example:
export DB2PROF=5.0
The result of this command is that each statement which takes longer than 5.0 seconds (real time) will be logged to the log file. The profiling output will be logged in the db2prof file in the current directory. Refer to the section “Tuning” in Appendix A for the profiling output.
Statistics
The DB2 driver also provides a way to gather so-called driver-wide statistics of the actions that are being performed by defining and exporting the DB2STAT environment variable, for example:
export DB2STAT=0 (logs final report only)
export DB2STAT=30 (logs a report each 30 seconds)
The statistics report is logged in the file db2stat in the current directory. Refer to the section “Tuning” in Appendix A for the statistics output report.
4.1.6 Update statistics
The DB2 Driver can generate statistics on tables when tables are created by means of bdbpost6.1 with the -f option(fast). Creating tables with the -f option is a more efficient way to create the tables, because they are created with all rows in it:
n Create table
n Grant privileges
n Insert all rows
n Create all indexes
n Update table and index statistics
If the tables are created by means of bdbpost6.1 with the -f option, the statistics being generated are more accurate (when table and index statistics are present, the DB2 optimizer can produce a better execution plan).
4.2 DB2 driver internals
In this section the following DB2 driver internal issues are discussed.
n The ClI interface
n DB2 SQL processing
n The DB2 driver locking strategy
4.2.1 CLI interface
The DB2 driver uses the call level interface (CLI) to communicate with DB2. CLI is basically a function library (or a set of C functions) that can be called from a C program in order to execute SQL statements.
The functions called by the DB2 driver perform the following actions:
n Logon to DB2 (open session)
n Allocate a statement handle
n Parse a SQL statement
n Binding of input variables
n Binding of output variables
n Execute the SQL statement
n Fetch the resulting rows
n Break a query (asynchronous)
n Commit/abort transaction
n Close a cursor
n Logoff from DB2 (close session)
The following features of CLI are also used by the DB2 driver(for reference):
n Array fetches (when enabled)
n Array inserts (when enabled and possible)
4.2.2 DB2 SQL processing
The internal processing of SQL statements is as follows.
SQL statements are dynamically generated by the DB2 driver database dependent layer. As BAAN IV applications are dynamic in nature it is not known in advance which tables will be used at runtime, hence it is not possible to prepare the queries before runtime.
When the DB2 driver receives a query from the bshell, the query is translated into a format suitable for DB2 and then passed to DB2 by way of the CLI function calls. Within DB2, a statement handle/cursor is allocated and the query is executed by assigning to it the statement handle. The SQL statement is parsed, input and output variables are bound and the query is executed using the statement handle. After the query is executed, a fetch operation is done and the resulting column values are placed in the output bound variables.
The rows returned by DB2 are passed to the database independent layer of the driver, which sends the results back to the bshell.
When a statement needs to be re-executed, the cursor from the previous execution is closed and the resulting rows are discarded (whether the re-execution is with the same input parameters or not). If new input values are required, the new values are assigned to the input parameter columns, and the query is re-executed. However, for re-execution, no re-parse of the statement or re-bind of input and output parameters is required, which improves the total performance.
When array fetches are enabled, multiple rows are fetched in one action. Space is allocated within the driver to buffer multiple rows fetched in one operation (the rows fetched in the buffer can be, say, 5, and they are returned to the client when requested ). When no rows are left in the buffer and more rows are requested, another array fetch operation is done.
Inserts can also be buffered. When array inserts are done, the rows to be inserted are placed in a buffer. When the buffer is full, a so-called flush is generated. The rows in the buffer are inserted with a so-called multi-row-insert.
When bdbpost is used with the -f option, the rows are buffered by default and are flushed when the array buffer is full. The array size needs to be specified, otherwise buffering will not be done. The array buffer size can be specified in db2_storage file on a per table level basis or globally, by using an environment variable. Refer to the section “Array interface” in Appendix A to see how the array-interface can be enabled.
4.2.3 DB2 driver locking strategy
The DB2 driver may use both implicit and explicit locking during updates.
Locking
Locking means that a row is first being selected for update before it is actually updated/deleted. The row is locked when the SELECT FOR UPDATE statement is executed, to make sure that the row is not changed by some other user. The SELECT FOR UPDATE statement locks the row acquiring share or exclusive lock depending upon the isolation level used. If the process tries to acquire an exclusive lock on the row that is already locked by another process then the process waits till the locked resources are released. Before executing a SELECT FOR UPDATE statement, the driver sets a signal handler which is raised after some time interval (say 1,2, or 3 seconds) if the process is found to be blocked. The signal handler interrupts the current waiting process and returns the locked error code to the application client. The client then decides to rollback or retry the same operation.
The DB2 driver enforces data integrity by using DB2’s row level locking mechanisms. Implicit locking is applied whenever possible; in other cases the driver will perform explicit locking.
The driver uses SQL_TXN_READ_COMMITTED to acquire shared locks in case of look up references and uses SQL_TXN_REPEATABLE_READ isolation level to acquire exclusive lock for update and delete actions. Exclusive types of locks are required so that the locks are retained till the transaction is committed or aborted, even when the cursor is closed.
In DB2 terminology, SQL_TXN_REPEATABLE_READ is identical to read stability and SQL_TXN_READ_COMMITTED is identical to cursor stability.
Time-outs
A SELECT FOR UPDATE statement waits indefinitely if there is a lock on the resource. The DB2 driver provides a way to time-out a long running statement. This means that a time-out of say, 60 seconds can be specified. If the statement is not completed within 60 seconds, an asynchronous sqleintr command is sent, which interrupts the query. However, it can also happen that, for example, a cascade update takes more than 60 seconds and hence is also timed out though it is not waiting for a lock. When no time-out is defined, the statement blocks on the locked resource. Time-outs are implemented by the UNIX alarm() function. On receipt of the SIGALRM signal, the sqleintr is performed on the current query. The lock wait period for SELECT FOR UPDATE, INSERT, DELETE, and UPDATE can be specified using the DB2_TIMEOUT environment variable. For a description on how to change this behavior, refer to section “Locking behavior” in Appendix A.
Be careful in experimenting with these options.
5. DB2 database driver administration tools
|
This chapter describes the BAAN IV administration tools for the DB2 driver. The following tools are discussed.
n $BSE/bin/db2_admin6.1
n $BSE/bin/db2_maint6.1
5.1 Introduction
The files required by the DB2 database driver reside in the directory $BSE/lib/db2. The administration of these files can be done by using the BAAN IV tooldb2_admin6.1. This tool is a shell script which allows the super user or BAAN administrator (bsp) to maintain authorized users and groups in the administration files.
The db2_admin6.1 tool calls db2_maint6.1. The db2_maint6.1 tool is an executable, which does the actual maintenance of the DB2 administration files.
Both tools are discussed in the sections below.
5.2 The db2_admin6.1 tool
After you start db2_admin6.1 (being logged in as super user or DB2 DBA), the Main Menu is displayed:
M A I N M E N U
1. User Administration.
2. User Information.
3. Escape to shell.
e. Exit.
Thedb2_admin6.1 tool provides user administration and information facilities. These facilities are described in the sections below.
If you select the Escape to Shell option, a UNIX shell is started for you. You can return to the Main Menu by killing the shell (by entering exit or <ctrl><d>.
5.2.1 The User Administration menu
If you select the first option, the User Administration menu is displayed.
USER ADMINISTRATION
1. Add User.
2. Drop User.
3. Change DB2 Users Password.
4. Add Group.
5. Drop Group.
6. Change Groups Password.
e. Exit.
This menu enables you to choose from a number of maintenance options. The options of the User Administration menu are described in the sections below.
The Add User option
By using this option a user, which is already known to the DB2 database, can be added to a particular group. This means that the user is added to the file $BSE/lib/db2/db2_users.
To add a particular user to a group, the following information must be specified:
n The group it should belong to
n The group password (not echoed)
n BAAN IV user name
n DB2 user name
n DB2 user’s password (not echoed)
The new user must not be an existing user or group name. Multiple BAAN IV users can not share the same DB2 user name. The new user must first be added at the UNIX level.
When a user is added to the group by the DB2 DBA or a super user, the user will inherit all objects privileges granted to the group user.
The Drop User option
By using this option a user, belonging to a particular group, is removed from the group. This means that the user is deleted from the file $BSE/lib/db2/db2_users.
To remove a particular user from a group, the following information must be specified:
n The group to which the user belongs
n The group password (not echoed)
n The BAAN IV user name
When a user is removed from the group by the super user or BAAN administrator (bsp), the user will no longer be able to access the database through BAAN. The user will still be able to access the database outside of BAAN. To prevent this the user must be removed from the group file at the UNIX level.
The Change DB2 Users Password option
By using this option a user password can be changed. This will update the $BSE/lib/db2/db2_users file with the new specified password. Note that this password is the UNIX password. So, every time when the DB2 user password is changed, the UNIX password must first be updated.
To change the password of a particular user, the following information must be specified:
n BAAN IV user name
n DB2 user name
n New user password (not echoed)
The Add Group option
By using this option a new group, which is a user already known to the DB2 RDBMS, can be added to the administration file. This means that the granting connect and createtab privileges are given to the group user and that the group is added to the $BSE/lib/db2/db2_groups file.
To add a group, the following information must be specified:
n Group name
n Group password (not echoed)
A new group cannot be an existing user or group name. The group name must both be a UNIX group and a UNIX user.
The Drop Group option
By using this option an existing group will be removed. This means that the revoking connect and createtab privileges from the group user are removed and that the group entry in the file $BSE/lib/db2/db2_groups is removed.
To remove a group, the following information must be specified:
n Group name
n Group password (not echoed)
The group logon is not removed from the UNIX account.
A group can only be removed if no users exist in the group.
The Change Groups Password option
By using this option a group password can be changed. This will update the $BSE/lib/db2/db2_groups file with the new specified password. Note that the password of the group user needs to be changed when the group user password is changed at UNIX level.
To change the password of a group, the following information must be specified:
n Group name
n New group password (not echoed)
5.2.2 The User Information menu
If the User Information option is selected in the Main menu the following menu is displayed:
USER Information
1. List Users.
2. List Groups.
3. List Storage.
4. Edit Storage.
e. Exit from this menu.
The options are described in the sections below.
The List Users option
This option displays all the users in the $BSE/lib/db2/db2_users file, along with the DB2 user name and group name they belong to.
The List Groups option
This option displays all groups, along with all users included in each group.
The List Storage option
This option retrieves the parameters for a table or index from the $BSE/lib/db2/db2a_storage file. The following information must be specified:
n BAAN IV user name
n DD Table name (for example, ttadv000)
n Company number (for example, 000)
n The Object type (table or index, for example, T, I, or I1)
For tables the following information will be displayed:
n Owner (private/group)
n Default index optimization
n Refresh time (has become obsolete)
n DB2 storage parameters
For indexes the following information will be displayed:
n Index optimization field
The Edit Storage option
This options will start the UNIX vi editor for the $BSE/lib/db2/db2_storage file, enabling you to change the parameters in this file. After you exit the editor, the User Information menu will appear again.
Changing the db2_storage file can have fatal consequences. Be sure to keep a copy of the file. If the storage file is lost, tuned parameter settings are gone and, as a result, object security may be inconsistent.
5.3 The db2_maint6.1 tool
The db2_maint6.1tool is invoked by db2_admin6.1 script for performing administration tasks. The options offered by the db2_admin6.1 script can also be maintained manually by invoking db2_maint6.1. The different options of the db2_maint6.1 tool, along with their arguments are explained below:
n Adding a user to a group:
db2_maint6.1 -a<BAAN IV user>-m<DB2 user> -p<DB2 password> -G<Group name> -P<Group password>
n Removing a user from a group:
db2_maint6.1 -d<BAAN IV user> -p<DB2 password> -G<Group name> -P<Group password>
n Adding a group:
db2_maint6.1 -A<Group name> -P<Group password>
n Removing a group:
db2_maint6.1 -D<Group name> -P<Group password>
n Changing a user password:
db2_maint6.1 -r<BAAN IV user> -m<DB2 user> -p<new password>
n Changing a group password:
db2_maint6.1 -R<Group name> -P<new password>
n Displaying users:
db2_maint6.1 -l
n Displaying groups:
db2_maint6.1 -L
n Displaying the storage file:
db2_maint6.1 -S<table name> -C<company number> -u<BAAN IV user> -O<object>
where:
The table name should be in format ppmmmxxx
The object should be T or I or I1, and so on.
6. Configuration and tuning aspects
|
This appendix supplies information about general configuration and tuning aspects. The following topics are covered in this appendix.
n DB2 configuration and tuning aspects
n DB2 database driver configuration and tuning aspects
n AIX tuning aspects
The topics listed above are discussed in the sections below.
6.1 DB2 configuration and tuning aspects
Configuring and tuning the DB2 database is important in order to remove possible performance bottlenecks and to optimize the BAAN IV performance on the DB2 database. In this appendix several topics will be discussed briefly. For further details, refer to the DB2 server documentation.
6.1.1 Configuration aspects
Before starting the DB2 engine by doing a db2start, you must export the DB2_RR_TO_RS=ON environment variable. When this variable is set, the adjacent rows in the table are not locked when the current row is updated or deleted. It is recommended to change the ASLHEAPSZ in the database manager configuration to allocate a minimum shared memory segment per connection.
The topics listed below are configuration aspects related to the DB2 installation. For further details, refer to the DB2 server documentation.
n Open cursors
n DML locks
n Concurrent Connections (MAXAPPLS)
n LOCKTIMEOUT (15 seconds)
n DLCHKTIME (600000 milliseconds)
n DBHEAP (1600 4K pages)
6.1.2 Tuning aspects
Tuning the DB2 environment basically consists of adjusting tuning of the database manager configuration as well as the database configuration parameters.
The way the parameters are tuned depends on whether the application is run in host mode (the BAAN IV DB2 driver and the DB2 server on the same system) or in client/server mode (the BAAN IV DB2 driver on one system and the DB2 server on the other).
The storage of the DB2 BAAN IV data
It is recommended to have BAAN IV tables, index data, and the DB2 system catalog on separate tablespaces on different disks to improve performance. It is also recommended that the tablespaces mentioned above are created as database managed space (DMS) using raw devices.
After creation of the raw devices make sure that the raw devices are owned by the instance and belong to the database administrator group db2.
Database manager configuration tuning
With regard to the database manager configuration values, the important parameter to be configured in host mode is ASLHEAPSZ. In client/server mode, the parameter is RQRIOBLK.
For every connection established with the database server, an ‘AGENT’ is started by the DB2 Engine. MAXAGENTS should be appropriately set to accommodate all active applications. Each session in BAAN IV bshell establishes a new connection, and on average the number of connections established by a user’s bshell is 3. It is recommended to set the MAXAGENTS to three times the total number of users supported on the system.
Database configuration tuning
To estimate the performance of the DB2 environment, it is recommended to take a snapshot to determine the buffer pool activity, lock escalation information, sort activity, the Package cache lookups, and Package cache inserts information.
In BAAN IV, the SQL queries for the Level-1 driver are very simple. Hence the optimization level is set to 0 to use indexes for scanning the rows. There should not be any sort activity for the simple BAAN IV level-1 driver queries.
There should not be any lock escalation. If this occurs then it is advisable to increase the value of MAXLOCKS and LOCKLIST. There is a sizable degree of interdependency between these two parameters.
The ratio of package cache lookups to package cache inserts should be set to 100:1 to ensure a good performing environment.
It is recommended by DB2 DBA’s to set the NUM_IOCLEANERS parameter value to “4 plus the number of containers constituting the tablespace of the database”. For example, if the table data tablespace consists of 6 containers on different disk drives and there are another 6 containers for index data on different disk drives, then NUM_IOCLEANERS should be set to
“12 + 4 = 16”.
Since the page cleaners are activated asynchronously, they will update the dirty pages on different disks, making the pages free and available for the database data.
The DLCHKTIME variable is set to a maximum value. This is to avoid any deadlock checking from DB2 Server side. The driver ensures that there are no deadlocks at any time.
The configuration parameters listed below show engineering benchmark values may be used as startup values. More tuning may be required depending upon the hardware capabilities.
n Database configuration parameters:
DBHEAP 11000
BUFFPAGE 10000
CATALOGCACHE_SZ 128
LOGBUFSZ 100
UTIL_HEAP_SZ 100
LOCKLIST 500
SORTHEAP 32
STMTHEAP 1024
DLCHKTIME 600000
LOCKTIMEOUT 600
APPLHEAPSZ 256
PCKCACHESZ 200
STAT_HEAP_SZ 2048
MAXLOCKS 30
CHNPGS_THRESH 60
NUM_IOCLEANERS 16
MAXAPPLS 2000
MAXFILOP 200
LOGFILSIZ 4000
LOGPRIMARY 4
LOGSECOND 3
MINCOMMIT 25
n Database manager configuration parameters:
RQRIOBLK 8192
BACKBUFSZ 128
RESTBUFSZ 128
ASLHEAPSZ 4
MAXAGENTS 2000
agent_stack_sz 12
For more detailed information of the configuration parameters refer to the DB2 Administration Guide.
6.2 DB2 database driver configuration and tuning aspects
The DB2 Level-1 driver has different tuning options, which are a bit technical, but will be discussed here. Also there are configuration options.
6.2.1 Cursor management
The DB2 driver has a built-in cursor management mechanism managed in least recently used (LRU) fashion. Each cursor represents one SQL statement. The environment variable DB2_MAX_OPEN_HANDLES or resource variable db2_max_open_handles can be used to influence cursor management. This limits the number of open cursors per connection. A maximum of 200 open statement handles are allowed per connection.
6.2.2 Array interface
The DB2 Driver can use the DB2 array interface for array fetches and array inserts. With the array interface communication between the DB2 driver and the DB2 RDBMS is more efficient: multiple rows are fetched or inserted at a time. However, because multiple rows need to be stored in a buffer in the DB2 driver, more memory has to be allocated. To allocate a buffer to hold array rows, the DB2_MAX_ARRAY_SIZE environment variable or resource variable db2_max_array_size needs to be set. The array buffer can be set on per table basis using the ARR_SIZE variable in db2_storage file, for example:
*:*:T:group:01:5: ARR_SIZE 5 T_SPACE USERSPACE1 I_SPACE IDXSPACE
If the environment variable is set and the array size is also specified in the storage file, then the size specified in the storage file overrides the value set by environment variable.
The array fetch interface can be enabled with the environment variable DB2_ARRAY_FETCH. Array inserts are enabled by default when bdbpost with-f option is used (it is necessary to set the array size, otherwise the inserts will not be buffered).
The array insert interface can be enabled with the environment variable DB2_ARRAY_INSERT.
Array interfacing is especially useful when you are accessing a remote database, because the number of network round-trips is reduced by this.
6.2.3 Locking behavior
The DB2 driver uses dirty read isolation level to read rows from the table. The queries returning more than a single row do not acquire any type of lock (shared or exclusive), unless this is explicitly stated within the query. Queries such as SELECT FOR UPDATE, INSERT, DELETE and UPDATE acquire exclusive lock when SQL_TXN_REPEATABLE_READ isolation level is used. Only in case of lookup references shared locks are acquired when SQL_TXN_COMMITED_READ isolation level is used. The locks are retained till the transaction is committed or aborted.
The actions which relate to DML locking can be executed with a certain time-out value, which cancels the statement if the time-out is reached.
The table below gives an overview of the sections related to DML locking.
Actions related to DML locking
|
Action
|
Default time-out
|
Minimum time-out
|
[0] Select for update
|
1
|
1
|
[1] Insert
|
1
|
1
|
[2] Update
|
1
|
1
|
[3] Delete
|
1
|
1
|
[4] Lock table
|
1
|
1
|
The time-outs can be specified in the environment variable DB2_TIMEOUT as follows:
DB2_TIMEOUT=”{[value], [value], [value], [value], [value]}”
or through the resource variable db2_timeout as follows:
db2_timeout:“{[value], [value], [value], [value], [value]}”
Where the first value stands for the time-out for the action SELECT FOR UPDATE, the second value for the time-out for action INSERT, and so on. All parameters are optional and the values can be specified to indicate the time-out for the particular action. If the parameter is not specified, the default value is used. The minimum time-out value is also shown in the table above.
For example, the following setting of the DB2_TIMEOUT:
DB2_TIMEOUT=”{1, 5, 6, 4, 1}”
or
db2_timeout:”{1, 5, 6, 4, 1}”
Leads to the following actions:
n SELECT FOR UPDATE: time-out of 1 second
n INSERT: time-out of 5 seconds
n UPDATE: time-out of 6 seconds
n DELETE: time-out of 4 seconds
n LOCK TABLE: time-out of 1 second
6.2.4 High-level lock retries
When a row lock cannot be acquired, high-level lock retries are initiated. This means that the same action is performed after a sleep period. The retry pattern can be defined by environment variable LOCK_RETRY orresource variable lock_retry. This can contain a comma, separated list of combinations of number of retries and sleep periods in milliseconds (ms), for example:
LOCK_RETRY=”5*100,5*500″
or
lock_retry:”5*100,5*500″ (resource variable)
This is the default retry pattern, which means that the action is retried 5 times with a sleep period of 100 ms and then 5 times with a sleep period of 500 ms. The lock retries can also be disabled by specifying:
LOCK_RETRY=”0″
or
lock_retry:”0″
This is a feature common to all database drivers.
The LOCK_RETRY variable can be used both in level-1 and level-2 drivers
6.2.5 Index optimization
Index optimization is used to improve performance for SELECT statements on tables. The reason for this is that BAAN IV requires queries which are not very common in relational databases and, as a result, can cause optimization problems in RDBMSs.
In BAAN IV indices can consist of multiple columns (so-called concatenated indexes). Now, when you want to retrieve data in the order of the index, then the first column in the index is considered most significant, and the last column is considered least significant. So each column has its own significance within the order. This concept is common to all level-1 database drivers. But BAAN IV also requires significance of the columns in the WHERE clause, which means that we should treat each column differently.
For example, consider Table A with columns col1, col2, and col3, which are all of type integer, and an index on col1, col2, and col3. The table contains 5 rows.
Example table A containing the following five rows
|
Col1
|
Col2
|
Col3
|
0
|
0
|
0
|
0
|
0
|
1
|
0
|
1
|
2
|
1
|
2
|
1
|
1
|
2
|
3
|
Suppose we want the rows which are greater than first row : {0, 0, 0}. The correct query to solve this would be:
SELECT col1, col2, col3 FROM A
WHERE
(col1 > 0 OR col1 = 0 AND
(col2 > 0 OR col2 = 0 AND
(col3 > 0)))
ORDER BY col1, col2, col3
We see that this query contains a nested list of AND/OR conditions, which in general cannot be efficiently optimized by RDBMSs. As a result, when there are multiple columns in an index and there are many rows in the table, the RDBMS servers spend a long time searching all the rows which meet the condition. This causes a considerably slower performance.
The way to solve this problem is called index optimization. This means that in the example the 3 columns col1, col2 and col3 are concatenated and added to the table as a separate column. This additional column is called the hash column, and it contains a sortable value of the three concatenated column values. On the hash column an index is created. For our example table, this results in the following table (note that the hash value is simplified for the example).
Example table A with additional hash column
|
Col1
|
Col2
|
Col3
|
Hash1
|
0
|
0
|
0
|
“000”
|
0
|
0
|
1
|
“001”
|
0
|
1
|
2
|
“012”
|
1
|
2
|
1
|
“121”
|
1
|
2
|
3
|
“123”
|
When searching for rows greater than {0, 0, 0}, we can now specify the following query, including the hash column:
SELECT col1, col2, col3 FROM A
WHERE
hash1 > “000”
ORDER BY hash1
This query is much simpler and can be highly optimized by the optimizers. Note that there is always just one condition in the WHERE clause.
Duplicate indexes
For duplicate indexes, the primary key parts are appended to the hash column to make it unique. In this way, a distinction is made between an ascending and descending sorting order through the duplicate rows. The order as such, however, is not defined, only the distinction is made between ascending and descending.
Hash column naming convention
The name of the hash column in a real BAAN IV table is formed using the hash keyword and the index number for which it is being created. Any descending hash column is created with d character appended. For example:
hash1 : ascending hash column for index 1
Size of hash columns
The size of the hash column is determined by the data types and sizes of all the columns in the index. The table below demonstrates the contribution of each data type to the size of the hash column.
Relation between data types and the size of the hash column
|
Type
|
Size
|
CHAR
|
1
|
STRING(n)
|
n
|
SHORT
|
3
|
DATE
|
4
|
LONG
|
5
|
FLOAT
|
(digv + diga + 2) / 2
|
DOUBLE
|
(digv + diga + 2) / 2
|
The digv is the number of digits before and diga the number of digits after the decimal sign.
Specify index optimization
The index optimization can be specified per table and per index in the $BSE/lib/db2/db2_storage file. Refer to the section “DB2 Table and Index Location” in Chapter 4 for information about the syntax.
To specify if index optimization should be used, the following (octal) values are available:
0000 no optimization
0001 optimization using 1 column ( single hash )
0010 Index order Ascending only
0020 Index order Descending only
0100 Key only optimization
You can define the default value for each table in a table [‘T’] entry. Any value in an index [‘I’] entry overrides the table default. If there is none for a specific table, the default value is 000.
You can combine the key only optimization with hash optimization values. For example:
0101 – Optimization using 1 column (single hash) and Key only optimization enabled.
6.2.6 Fetch optimization and caching
This section discusses fetch optimization and caching, which are both used to optimize performance.
Fetch optimization
In BAAN IV, a set of rows is often retrieved from the table in the database. A typical example is an application doing:
DB.FIRST
DB.NEXT
DB.NEXT
DB.NEXT
etc.
When a query is processed after fetching the first row, the subsequent rows are fetched from the retrieved set of rows and are returned to the user. The rows updated or deleted by other concurrent processes are not reflected in this set of rows. The changes made by other concurrent processes will only be reflected when the query is re-executed.
However, in many situations this is really not required. A user may not want to see all the updates at the same time, and as a result, he could just fetch the next row from the existing set, instead of re-executing the statement. This technique is called fetch optimization.
Incorporated in this optimization technique is the refresh time concept, which allows users to specify a time interval in seconds, for which a determined set of rows is valid. As long as the set is valid, no changes of other users are reflected. If the set expires (that is, the refresh time is exceeded), the statement will automatically be re-executed in the driver.
For example, a set of rows is retrieved when DB.FIRST is done. For a refresh time of, say, 5 seconds, the DB.NEXT call will fetch the next row from the retrieved set of rows. All consecutive DB.NEXT calls within 5 seconds will do a fetch only. After 5 seconds, the set will be declared invalid and a following DB.NEXT will again do a re-execute and fetch. This results in a performance improvement as the time to execute the query is eliminated.
You can set the refresh time for table [‘T’] entries in the dbms_storage file. For index [‘I’] entries it is ignored. The default is 0.
For example the db2_storage file could contain an entry such as:
*:*:T:group:01:5:
*:*:I:group:01:5:
The example above shows that all tables and companies will use single hash columns for index optimization purposes and a refresh time period of 5 seconds.
*:*:T:group:01::
*:*:I:group:01::
The above examples shows that all the tables have a default refresh time of 0 seconds.
The fetch optimization can be done in certain cases, that is if there is a next record in the set and if:
n The entire table is locked or
n The action takes place within the interval
Caching
Caching is based on the fact that the last retrieved result (using fetch) is stored in a single-row cache. When two consecutive database requests are exactly identical, the result can be copied from a single-row cache. A restriction is that the second action takes place within the refresh time. This situation occurs frequently when joins are processed. Every time the outer row contains the same key value, a DB.EQ on the outer table can be cached, so it will not be passed to RDBMS.
Note that caching and fetch optimization is consistent with regards to one BAAN IV user. Changes in the database which the user makes in any of the sessions he runs within a bshell, will always disable current sets related to the update. Consequently, additional fetches will always fetch the new result. The changes made by other users will not be reflected within the refresh time.
6.2.7 Optimistic and pessimistic reference checks
To optimize concurrency the DB2 driver supports optimistic and pessimistic reference checks. In lookup reference mode when inserts are performed in a child table, the driver checks whether the reference exists in the parent table and locks the table in order to be sure that another user cannot delete it within the current transaction. This approach is called the pessimistic approach. This approach will also block an insert of another user referencing the same parent row, thereby affecting the concurrency. To encounter this problem there is also an approach where a row in the parent table is not locked is used depending upon the choice of the user. This approach is called the optimistic approach. As the record is not locked, another user can still perform an insert operation, which improves the concurrency.
6.2.8 Tuning
In order to determine which table actions are most time-consuming you can set the DB2PROF environment variable to a number of seconds. All actions which take longer than defined by DB2PROF are written to a file, stating, among other things, the time required to execute the statement and retrieve the result. This file is called db2prof and is stored in the directory where the driver was started. A sample db2prof file is shown below.
Profiling value = 0.00 sec
Pid Table Owner I Mode Cache Exe Fetch Exe Fetch Tot
<11350> tiitm001812 jim 1 FIRST : – 0.01 0.00 – – 0.04
< 8024> tiitm001812 jim 1 FIRST : – 0.01 0.00 – – 0.05
<11350> tiitm001812 jim 1 FIRST : 0.00 – – – – 0.00
<11350> tiitm001812 jim 1 FIRST : 0.00 – – – – 0.00
<11350> tiitm001812 jim 1 FIRST*: – 0.01 0.00 0.01 0.00 0.06
< 8024> tiitm001812 jim 1 FIRST*: – 0.00 0.05 0.00 – 0.10
<11350> tiitm001812 jim 1 NEXT : – – 0.00 – – 0.00
<11350> tiitm001812 jim 1 NEXT : – – 0.00 – – 0.00
<11350> tiitm001812 jim 1 NEXT : – – 0.00 – – 0.00
<11350> tiitm001812 jim 1 NEXT : – – 0.00 – – 0.00
<11350> tiitm001812 jim 1 NEXT : – – 0.01 – – 0.01
<11350> tiitm001812 jim 1 NEXT* : – – 0.01 0.01 0.00 0.02
<11350> tiitm001812 jim 1 NEXT* : – – 0.00 0.01 0.00 0.01
<11350> tiitm001812 jim 1 PREV : – 0.00 1.02 – – 1.05
<11350> tiitm001812 jim 1 PREV : – – 0.01 – – 0.02
<11350> tiitm001812 jim 1 PREV : – – 0.00 – – 0.01
The data in the above sample file can be explained as follows:
n In this example the number of seconds (profiling value) is 0.00. This means that all actions are written to the file.
n The asterisk [*] after some of the records indicate that the records were first read and then locked.
n Column I lists the number of the index used.
n The “Cache” column lists a value when a result is retrieved from cache memory.
n Each action allows for two ‘executes’ and two ‘fetch’-es. This happens when a record must be read in locked mode or when only key fields are selected first and then the other fields are retrieved.
You can also view the execution times per table by specifying the DB2PROF environment variable in the $BSE/lib/tabledef6.1 file, for example as follows:
tccom010:812:db26000(DB2PROF=0.4)
In this example all the queries which require more than 0.4 seconds are logged in the db2prof file. Note that a separate driver is started for this table, because the table is considered to have a different database definition.
The drivers also provide an option to gather driver-wide statistics of actions being performed, such as:
n Number of cursors (opened, closed, current open)
n Number of parses, binds, executes, fetches
n Number of logons (sessions)
n Number of inserts, updates, deletes
n Number of commits, rollbacks
The driver-wide option is activated through the environment variable DB2STAT. Refer to the section “Statistics” in Chapter 4 for details. Below is a sample output of DB2STAT.
<36566> 96-02-05[11:04:24]: Statistics [interval = 0]
C U R S O R S
Opened Closed Parse Bind Define Execute Fetch
Count 28 28 1 17 2 15 2
Time(s) 0.04 0.00 0.03 0.00 0.00 0.13 0.00
Avg 0.00 0.00 0.03 0.00 0.00 0.01 0.00
D A T A B A S E
First Last Next Prev Curr Great Gteq Equal Less Eqle
Count Read 0 0 0 0 0 0 0 0 0 0
Cached Read 0 0 0 0 0 0 0 0 0 0
Fetched Read 0 0 0 0 0 0 0 0 0 0
Executed Read 0 0 0 0 0 0 0 0 0 0
Time(s) Read 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Avg Read 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Count Lock 0 0 0 0 0 0 0 0 0 0
Time(s) Lock 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Avg Lock 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Insert Delete Update
Count Exe 15 0 0
Time(s) Exe 0.36 0.00 0.00
Avg Exe 0.02 0.00 0.00
CrIdx DrIdx ChOrd CrTbl ClTbl DrTbl LkTbl NrRow
Count Exe 0 0 0 1 2 1 0 2
Time(s) Exe 0.00 0.00 0.00 3.43 0.14 1.82 0.00 0.21
Avg Exe 0.00 0.00 0.00 3.43 0.07 1.82 0.00 0.10
Commt Rolbk RdOnl PrCmt NotAc
Count Exe 2 1 0 0 0
Time(s) Exe 0.04 0.09 0.00 0.00 0.00
Avg Exe 0.02 0.09 0.00 0.00 0.00
S U M M A R Y
Count Time(s) Avg
Total asc read (s) 0 0.04 0.00
Total desc read (s) 0 0.30 0.00
Total exact read (s) 0 0.00 0.00
Total all read (s) 0 0.34 0.00
Total updates (s) 15 0.36 0.02
Count Perc
Total cache hit (%) 0 0.00
Total fetch opt (%) 0 0.00
Count
Forced close 0
Current open cursors 0
Sessions (logon/logoff) 2 / 2
The tuning options are:
n Index optimization, 1 column (that is, single hash)
n Key only optimization
n Extend refresh time
n Separate data from indices in tablespaces
Furthermore, you can use the DB2 db2evmon utility to check I/O times and process information. For more information, refer to the DB2 Performance Monitor Guide.
Below an example is given of the db2_storage file where the specification of index optimization for tuning is clearly shown.
tccom010:*:T:group:01:5:T_SPACE tri_space I_SPACE idxspace
tccom010:*:I::01::
tccom012:*:I1:group:01::T_SPACE tri_space I_SPACE idxspace
tccom013:*:I1::01::
tccom022:*:I1,I5::01::
tccom020:*:I2::01::
tiitm001:*:I1::01::
tiitm050:*:T:group:01:5:T_SPACE tri_space I_SPACE idxspace
tiitm050:*:I::01::
*:*:T:group:01:5:T_SPACE tri_space I_SPACE idxspace
*:*:I::01::
6.3 AIX tuning aspects
The performance of the overall system that is running the DB2 Driver can be improved by tuning AIX as follows.
Use vmtume to tune virtual memory by entering the following command:
vmtune -p 3 -P 5
The -p flag is the minperm parameter and the -P flag is the maxperm parameter. This threshold defines the limits for non computational pages to be kept in memory. These are the lowest values to which the treshold can be set.
The schedtune utility is used to set timeslice ticks. It is advised to use schedtune as follows.
schedtune -d 31 -t 3
Connection time-out occurred during client/server testing using the TCP/IP protocol. (SQL30081N:Protocol specific error code 78 [connection time-out])
All the threads of a given priority that can be dispatched occupy consecutive positions in the run queue. The priority value is recalculated after every system clock tick (each system clock tick is 10ms). The recalculation is delayed by 3 ticks to improve performance. The -d option is the CPU decay rate, which is used here to prioritize the foreground and background processes. These values are suggested by an AIX expert from the AIX performance group.
The vmtune and schedtune utilities can be found in the/usr/samples/kernel directory on AIX. The schedtune utility is part of AIX Version 4.1, and is available in a PTF for AIX Version 3.2.5.
7. Environment variables and resources
|
This appendix supplies information about the environment variables and resources which can be used on both the client and server.
Environment variables and resources have to be defined in different ways on the client and the server.
In this appendix the following categories are distinguished:
n Resources for both client and server
n Client environment variables
n Server environment variables
These categories are discussed in the sections below.
7.1 Resources for both client and server
Resources for both client and server can be defined in the resource file in $BSE/lib/defaults/db_resource. In this case identical resource files must exist on both client and server machines. This resource file will be read by both client and server.
Alternatively, a different resource file can be specified. For the client this is specified with environment variable USR_DBC_RES, and for the server it is USR_DBS_RES. The specified path should be based on the $BSE directory, for example:
USR_DBC_RES=”lib/defaults/my_resource”
This command will make the client read the alternative resource file $BSE/lib/defaults/my_resource. The same applies for the server.
The format of a line in the resource file is as follows:
<resource_name>:<value>
For example:
retained_cursors:50
7.2 Client environment variables
The client environment variables can be defined by setting them in the current shell or before starting the BAAN IV Tools environment. For example:
BDB_DRIVER=”db2″
ba6.1
or:
BDB_DRIVER=”db2″
bdbpre6.1
7.3 Server environment variables
The server environment variables can be set the same way as the client environment variables. On the server the variables can also be set in the$BSE/lib/tabledef6.1 file, which also contains the driver specification. An example of an environment variable set in tabledef6.1 is given below.
*:*:db2(DB2INSTANCE=db221)
When a remote database driver is started, the variables have to be defined in the tabledef6.1 file, because the current shell environment is not exported to the remote driver.
Special attention should be paid to some client process debugging methods, which produce output on standard error (stderr). For client tools, such as bdb tools and refint, the stderr output can be redirected to a file, for example:
bdbpre6.1 <arguments> 2>tmp.err
For the BAAN IV Tools environment, the display server is started from the shell prompt (for example, bx6.1 or ba6.1), and this starts the logic server (bshell6.1). Since the logic server has become a server process itself, stderr will be redirected to a file by the following command:
ba6.1 — -logfile tmp.err -keeplog
This command makes the bshell create a file tmp.err and log its stderr information to the file.
The table below shows the client resources and the corresponding environment variables.
Client specific resources
|
Resource name
|
Environment variable
|
Type
|
ssts_set_rows
|
SSTS_SET_ROWS
|
integer
|
bdb_debug
|
BDB_DEBUG
|
integer (octal)
|
bdb_driver
|
BDB_DRIVER
|
string
|
The client specific resources are explained below (note that the corresponding environment variables have the same function):
n ssts_set_rows
This variable defines the number of rows to be read ahead for single table single row calls, such as db.next(). The default is 3 rows, which means that for one db.next(), 3 rows will be read. For following 2 db.next() calls rows will be taken from the client row buffer or fetched from the database without re-execute.
n bdb_debug
This variable can be used to generate some debug information about basic communication between client and database driver. Several categories can be specified, which make the client process print the debug information to standard error (stderr). The categories are:
00001 : server types
00002 : database actions
00004 : delayed lock actions
00010 : reference info
00020 : two-phase commit info (obsolete)
00040 : TSS info from $BSE/lib/tss_mbstore6.1
00100 : permission info
Multiple categories can be defined by adding the octal values.
n bdb_driver
Use this variable to set a database specification, which is usually put in the file tabledef6.1. When this variable is set, all tables will be accessed using the database driver specified and tabledef6.1 will not be read. The driver specified must be defined in the file $BSE/lib/ipc_info.
The table below shows the general client and server resources and the corresponding environment variables.
General client and server resources
|
Resource name
|
Environment variable
|
Type
|
rds_full
|
RDS_FULL
|
integer
|
tt_sql_trace
|
TT_SQL_TRACE
|
integer (octal)
|
The general client and server resources are explained below (note that the corresponding environment variables have the same function):
n rds_full
This variable defines the maximum number of rows transferred between the bshell and the driver as one block. Multiple blocks (and thus network round trips) are transferred if more rows are requested. This variable should be set to the same value for both client and server. The default is 5 rows.
n tt_sql_trace
This variable is introduced to view the BAAN IV SQL query information being handled in client and server. When this variable is set, the client prints debug information to stderr, the server prints info only if the dbslog variable allows it. The information contains also different categories which can be enabled separately, such as evaluation trees, SQL statements, bind variables, timings, and communication debugging. The possible values of the TT_SQL_TRACE variable and their description are shown below.
000040 (c) Show queries with their QID
000200 (c) Show query execution times
002000 (c) Show calls of internal SQL functions
004000 (c+s) Show query execution tree.
020000 (s) Show FullTableScan
010000 (s) Show query evaluation plan
The table below shows the general driver resources and the corresponding environment variables.
General driver resources
|
Resource name
|
Environment variable
|
Type
|
lock_retry
|
LOCK_RETRY
|
integer
|
bdb_max_session_schedule
|
BDB_MAX_SESSION_SCHEDULE
|
integer
|
dbslog
|
DBSLOG
|
integer (octal)
|
dbsinit
|
–
|
integer (octal)
|
The general driver resources are explained below (note that the corresponding environment variables have the same function):
n lock_retry
Refer to the section “High Level Lock Retries” in Appendix A, in which the high-level lock retries are discussed.
n bdb_max_session_schedule
This variable defines the mechanism of closing idle sessions in the driver. Whenever the client process has no more references (cursors or queries) to the session, it can be closed by the client. Closing of an idle session is done after a number of so-called schedule ticks. A schedule tick is generated whenever a BAAN IV session is ended. At this point, all idle sessions will have a schedule counter incremented. When the value of the schedule counter reaches the value of bdb_max_session_schedule, then the session is closed. For the DB2 Driver this means a logoff from the DB2 RDBMS.
The default bdb_max_session_schedule is 3. Setting bdb_max_session_schedule to 1, would result in less connections from the DB2 Driver to the DB2 RDBMS, since whenever a BAAN IV session is ended, the corresponding DB2 session (logon) is closed (logoff).
n dbslog
This variable provides detailed debugging information about the on-line processing of the driver. See Chapter 1, in which Driver tracing is discussed. The information will be logged in the file dbs.log in the current directory. Following categories can be specified:
0000001 : Data Dictionary info of tables within the driver
0000002 : Query info (SQL-level1)
0000004 : Query plan info (SQL-level2)
0000010 : Row action info
0000020 : Table action info
0000040 : Transaction action info
0000100 : DBMS input/output data (SQL-level2 drivers)
0000200 : Administration file info (SQL drivers)
0000400 : DBMS SQL statements
0001000 : General debug statements
0002000 : Query processing info (for TT_SQL_TRACE info)
0004000 : Data buffering info (communication)
Multiple categories can be defined by adding the octal values.
n dbsinit
This variable is used to have an optimistic approach while checking of references in parent tables. Refer also to Appendix A, in which reference checks are discussed.
For example, if dbsinit is put in $BSE/lib/defaults/db_resource as shown below, the referenced row in the parent table is not locked, improving the overall concurrency.
dbsinit:01
The table below shows the DB2 driver resources and the corresponding environment variables.
DB2 driver resources
|
Resource name
|
Environment variable
|
Type
|
db2_timeout
|
DB2_TIMEOUT
|
string
|
db2_max_open_handles
|
DB2_MAX_OPEN_HANDLES
|
integer
|
db2_max_arrz
|
DB2_MAX_ARRZ
|
integer
|
db2_max_ret_rows
|
DB2_MAX_RET_ROWS
|
integer
|
db2_opt_rows
|
DB2_OPT_ROWS
|
integer
|
db2_max_conn
|
DB2_MAX_CONN
|
integer
|
db2_ddl_timeout
|
DB2_DDL_TIMEOUT
|
integer
|
db2_ref_timeout
|
DB2_REF_TIMEOUT
|
integer
|
db2_opt_level
|
DB2_OPT_LEVEL
|
integer
|
|
DB2_ARRAY_FETCH
|
integer
|
|
DB2_ARRAY_INSERT
|
integer
|
|
DB2_NODESCRIBE
|
integer
|
|
DB2_EXPLAIN
|
integer
|
The DB2 driver resources are explained below (note that the corresponding environment variables have the same function):
n db2_timeout
Refer to the section “Locking behavior” in Appendix A for information about locking behavior and the setting of time-outs.
n db2_max_open_handles
Refer to the section “Cursor management” in Appendix A for more information about cursor management.
n db2_max_arrsz
Refer to the section “Array interface” in Appendix A for information about the array interface. If the array interface is enabled, it defines the maximum number of rows fetched at once from the DB2 RDBMS.
n db2_max_ret_rows
This variable specifies the maximum number of rows to be returned to the driver by the DB2 engine. 0 is the default, implying that all rows are returned.
n db2_opt_rows
It is assumed that the number of rows retrieved will not exceed n rows. It determinses a suitable communication buffer size to improve performance.
If more than n rows are retrieved then it may degrade performance.
n db2_max_conn
Determines the maximum number of connections a single driver can have to the RDBMS. The default is 0, which implies that there is no limit.
n db2_ddl_timeout
Determines the time-out value for DDL (create object) statements. The default is 240 seconds.
n db2_ref_timeout
Determines the time-out value for referential integrity checks. The default is 1 second.
n db2_opt_level
Sets the query optimization level for SQL queries. The default and recommended value is 0, which means use index scans. Refer to DB2 documentation for possible query optimization class values and their respective meaning.
n DB2_ARRAY_INSERT
Refer to the section “Array interface” in Appendix A for information about the array interface. If the array interface is enabled, it defines the maximum number of rows inserted at once in the DB2 RDBMS. Note that this option cannot always be enabled. For example, if references need to be checked or updated or the application requires immediate response from the driver whether the insert is successfully, no array insert can be done.
n DB2_NODESCRIBE
This variable specifies whether DB2 CLI should automatically describe the column attributes of the result set (SQL_NODESCRIBE_ON) or wait to be informed by the application via SQLSetColAttributes() (SQL_NODESCRIBE_OFF, which is the default).
n DB2_EXPLAIN
This variable can be set to the following values:
– 0
No explain output ( Default )
– 1
Set CURRENT EXPLAIN SNAPSHOT=yes will be sent to the server to enable the Explain Snapshot facility. The explained info is inserted in the SNAPSHOT column of the EXPLAIN_STATEMENT table.
The EXPLAIN_STATEMENT must have been created earlier.