ms sql server database driver internalsbaansupport.com/docs/baan/ms sql server database...

Infor ERP Baan IV

MS SQL Server Database Driver Internals

Copyright © 2010 Infor

All rights reserved. The word and design marks set forth herein are trademarks and/or registered trademarks of Infor and/or related affiliates and subsidiaries. All rights reserved. All other trademarks listed herein are the property of their respective owners.

Important Notices

The material contained in this publication (including any supplementary information) constitutes and contains confidential and proprietary information of Infor.

By gaining access to the attached, you acknowledge and agree that the material (including any modification, translation or adaptation of the material) and all copyright, trade secrets and all other right, title and interest therein, are the sole property of Infor and that you shall not gain right, title or interest in the material (including any modification, translation or adaptation of the material) by virtue of your review thereof other than the non-exclusive right to use the material solely in connection with and the furtherance of your license and use of software made available to your company from Infor pursuant to a separate agreement (“Purpose”).

In addition, by accessing the enclosed material, you acknowledge and agree that you are required to maintain such material in strict confidence and that your use of such material is limited to the Purpose described above.

Although Infor has taken due care to ensure that the material included in this publication is accurate and complete, Infor cannot warrant that the information contained in this publication is complete, does not contain typographical or other errors, or will meet your specific requirements. As such, Infor does not assume and hereby disclaims all liability, consequential or otherwise, for any loss or damage to any person or entity which is caused by or relates to errors or omissions in this publication (including any supplementary information), whether such errors or omissions result from negligence, accident or any other cause.

Publication Information

Document code: U7042B US

Release: Infor ERP Baan IV

Publication date: October 10

Table of Contents

Chapter 1 Baan IV database driver architecture.................................................................... 1-1

Introduction ................................................................................................................................ 1-1

Database driver.......................................................................................................................... 1-2

The Baan IV database driver ............................................................................................... 1-2

SQL processing ................................................................................................................... 1-3

Database-driver features ..................................................................................................... 1-3

Resource file .............................................................................................................................. 1-4

Log files...................................................................................................................................... 1-5

Database driver trace-information........................................................................................ 1-5

Error logging ........................................................................................................................ 1-5

Tuning aspects: profiling and statistics....................................................................................... 1-6

Profiling................................................................................................................................ 1-7

Statistics .............................................................................................................................. 1-7

Chapter 2 Microsoft SQL Server database driver externals and internals .......................... 2-1

MSQL driver externals ............................................................................................................... 2-1

Data-dictionary mapping ...................................................................................................... 2-1

MSQL table and index location ............................................................................................ 2-4

Security................................................................................................................................ 2-5

MSQL error reporting ........................................................................................................... 2-8

MSQL driver profiling and statistics...................................................................................... 2-8

MSQL driver internals ................................................................................................................ 2-8

The ODBC interface............................................................................................................. 2-9

MSQL Driver SQL processing.............................................................................................. 2-9

MSQL driver locking-strategy............................................................................................. 2-10

ii | Table of Contents

Chapter 3 Microsoft SQL Server database driver administration tools............................... 3-1

Introduction ................................................................................................................................ 3-1

The Database Administrator module (DBA) ............................................................................... 3-2

The Maintain Links between Baan Users and Database Users (ttdba0110m000) session................................................................................................................................. 3-2

The Maintain Database Users (ttdba0115m000) session .................................................... 3-3

The Maintain Database Groups (ttdba0120m000) session.................................................. 3-3

The Maintain Table and Index Storage (ttdba0140m000) session....................................... 3-4

The MSQL_MAINT utility ........................................................................................................... 3-4

Appendix A Configuration and tuning ...................................................................................... A-1

Microsoft SQL Server configuration and tuning.............................................................................A-1

Microsoft SQL Server configuration .....................................................................................A-1

Microsoft SQL Server tuning ................................................................................................A-3

Microsoft SQL Server locking...............................................................................................A-5

Baan Microsoft SQL database driver configuration and tuning ..................................................A-7

Cursor management ............................................................................................................A-8

Array interface......................................................................................................................A-8

Locking behavior..................................................................................................................A-9

High-level lock retries...........................................................................................................A-9

Index optimization in Level 1 drivers ....................................................................................A-9

Fetch optimization and caching..........................................................................................A-13

Optimistic and pessimistic reference checks......................................................................A-15

Tuning................................................................................................................................A-15

ODBC tracing in the SQL Server 7.0 driver .......................................................................A-18

Windows NT tuning ..................................................................................................................A-21

Appendix B Setting database driver behavior ......................................................................... B-1

Driver resources...................................................................................................................B-1

Environment variables .........................................................................................................B-2

Storage file...........................................................................................................................B-4

Appendix C Environment variables and resources ................................................................. C-1

Summary of MSQL resources and environment variables ........................................................ C-1

Table of Contents | iii

Detailed description of MSQL resources and environment variables........................................ C-4

Generic driver resources..................................................................................................... C-5

MSQL driver specific resources ........................................................................................ C-11

Appendix D Storage file format and configuration options .................................................... D-1

Storage file format..................................................................................................................... D-1

MSQL storage parameters........................................................................................................ D-3

About this Guide

This document supplies background information about the Infor ERP Baan IV database driver for Microsoft SQL Server. This database driver is referred to as the Infor ERP Baan IV MSQL database driver. This document can be used by all persons who want to configure or customize the Infor ERP Baan IV MSQL database driver. However, an elementary knowledge of Windows NT and Microsoft SQL Server is required.

Infor ERP Baan IV is from this point onwards simply referred to as Baan IV.

Prior to porting set 6.1c.03, the Baan IV MSQL database driver could run only with Microsoft SQL Server Version 6.5. With porting set 6.1c.03 and later, two MSQL drivers are shipped. The Baan MSQL database driver named msql_srv.exe can run with both Microsoft SQL Server versions 6.5 and 7.0. The Baan MSQL database driver named msql7_srv.exe can only run with Microsoft SQL Server Version 7.0. Both Baan IV MSQL database drivers are substantially the same and both are documented here. However, the 7.0 driver exploits some SQL Server 7.0-specific features that are not compatible with version 6.5. If a feature is available for only one MSQL database driver, this document explicitly notes the MSQL database driver for which the feature applies. If no version is explicitly noted, the feature applies to both MSQL database drivers.

The procedure for installing the Microsoft SQL Server and Baan IV software is described in the Infor ERP Baan IV Installation Guide for Microsoft Windows NT (U7000 US). This document includes a description of how to choose the proper Baan MSQL database driver. The Conversion Guide Microsoft SQL Server 6.5 to 7.0 (U7156 US) describes the process of migrating Baan data from Microsoft SQL Server 6.5 to 7.0.

The guide is divided into the following chapters and appendices:

Chapter 1 supplies a general overview of the Baan IV database driver architecture.

vi | Table of Contents

Chapter 2 supplies additional information about the Microsoft SQL Server database driver.

Chapter 3 describes the Baan IV administration tools for the Microsoft SQL Server database driver.

Appendix A supplies information about general configuration and tuning aspects.

Appendix B describes how to modify database driver behavior.

Appendix C supplies information about the environment variables and resources that can be used on both the client and server.

Appendix D contains sections discussing the file format of the storage file and the driver configuration options specific to the Microsoft SQL Server database driver.

Send us your comments

We continually review and improve our documentation. Any remarks/requests for information concerning this document or topic are appreciated. Please e-mail your comments to [email protected].

In your e-mail, refer to the document code and title. More specific information will enable us to process feedback efficiently.

mailto:[email protected]?subject=Documentation�

1 Chapter 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 application server.

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 Microsoft SQL Server (MSQL) relational database management system (RDBMS). The interface to the Microsoft SQL Server RDBMS is the Baan IV MSQL database driver.

The distribution of Baan IV application tables in different databases is transparent to the user of an application. Accessing the data from these tables is done by using Baan IV SQL embedded in Baan 4GL programs. The Baan IV SQL query is converted to DBMS-dependent SQL at the driver level and is passed to the DBMS server. Finally, the database driver retrieves the rows returned by the RDBMS server as a result of carrying out the DBMS SQL query.

A driver that generates only single-table SQL queries to the RDBMS is called a Level 1 driver. In this architecture, any join on rows is performed by the driver before returning the result row to the Baan IV client.

A driver that can generate multiple table-joins to the RDBMS is called a Level 2 driver. In this architecture, the driver relies on the RDBMS to perform table joins. The resulting rows returned by the RDBMS are returned to the Baan IV client.

1-2 | Baan IV database driver architecture

This document addresses the Baan IV Level 1 driver for Microsoft SQL Server driver, which allows Baan IV tables to be created and manipulated in the Microsoft SQL Server database. As all the database drivers have some common functionality, this document discusses the common part first and then describes the Microsoft SQL Server database driver-specific details.

Database driver

This section supplies information about the Baan IV database driver, and discusses the following topics:

The Baan IV database driver in general

SQL processing

Database driver features

These topics are discussed in the sections below.

The Baan IV database driver

The database driver acts as an interface between Baan IV Tools and the RDBMS, and is part of the Baan IV Tools client/server architecture. Any 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 carried out by the application server) 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 via the driver.

An overview of the architecture is illustrated below (in this diagram the Microsoft SQL Server RDBMS is used as an example).

Bshell

Client

BAAN/MSQLDriver

Server

MicrosoftSQL Server

RDBMS

BAAN Query MSQL Query

Figure 1, Baan IV database driver architecture

Baan IV database driver architecture | 1-3

SQL processing

When the application server encounters a database query in the application that is being carried out, it parses the query and sends an internal representation to the database driver. The Level 1 driver translates this into one or more single table queries in an RDBMS-specific SQL dialect (for example, Transact-SQL for Microsoft SQL Server). The MSQL driver connects to the RDBMS by using the appropriate ODBC (Open Database Connectivity) calls and the queries are then passed 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 or returned to the client.

Processing of Baan IV SQL queries is as follows. The driver receives database-independent representations of queries that 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 is possible 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 that can be supported by the RDBMS are put together and a database-expression is generated. This is done by the database-dependent layer in the driver.

Database-driver features

The Baan IV database-driver architecture contains the following features.

Data dictionary (DD) The Baan DD client can differ from the actual table definition in the DBMS server. There is one DD table in the driver for each DD table on the client. A common DD pool in the driver allows the DD’s to be shared among several cursors in the driver, so it need not be sent again by the client. The Baan DD (column names, data types, column size, and number of columns) is mapped to a table definition for storage in the RDBMS.

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 application server for each database.

Security The authorized Baan IV users are registered in the administration files kept in the %BSE%\LIB\MSQL directory. The MSQL_USERS file contains the user name and password in encrypted form, as well as a


group (database) mapping. The driver establishes a connection to the database for the user using this password (if the unified logon feature is not used). The administration of these files is carried out by a Baan IV administrator.

Referential integrity The Baan IV database driver maintains referential integrity internally 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, you can delete all referencing rows at once. This is much more efficient than deletion on a row-by-row basis.

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 that also use normal locking, are concurrent. Implicit locking is applied whenever possible. In other cases the driver will perform explicit locking.

Statement buffering Updates (inserts, updates, deletes) can be 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.

Data buffering, reduction, and compression When multiple rows are returned from a query, the rows are buffered and then returned to the client as one block. Data reduction and compression is also applied to minimize the amount of data transferred between the client and the driver.

Delayed locking and updates A delayed locking mechanism is implemented in the driver. This means that before an update is carried out, 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. This reduces both the workload on the RDBMS server and the IPC/network traffic between the database driver and the RDBMS.

Resource file

The database drivers developed for Baan IV refer to a common administration file in the Baan Software Environment (BSE). The resource variables defined here set the behavior of the driver at run time.

The resource variables common to all drivers as well as those specific to each RDBMS can be specified in the


%BSE%\LIB\DEFAULTS\DB_RESOURCE resource configuration file. These variables are read at run time by the driver when the driver is first invoked. Refer to Appendix C for a detailed description of the file format and available resources.

Log files

This section describes the information that can be logged by the Baan IV database driver.

This logging information can be:

Database driver trace information

Error logging

These types of logging are discussed in the sections below.

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 that can help solve problems. Information stored in the log files includes:

Table/index information (data dictionary)

The SQL statements being carried out

Values of the input and output bind variables

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 by using the environment variable DBSLOG. Debugging information is appended to the DBS.LOG file in the current directory. If this file does not exist, it is created. (This can be %SystemRoot%\SYSTEM32 if it is invoked via rexec). For example, tracing can be enabled by entering the following command:

SET DBSLOG=0560

Error logging

The database driver logs its error messages in the Windows NT application event-log, or in the log files in the %BSE%\LOG directory. The following information can be retrieved from the log files:


General information (user name, date, time, source file, and line number, and so on).

The function being called.

The error code returned by the database.

The database error description.

The BDB error code returned to the application.

In some cases the failing SQL statement can also be logged.

When a database error occurs, an attempt is made to map it to some known or anticipated error condition. Generally, these mapped BDB errors have corresponding error numbers falling in the range of 1 to 1000. An example of this is Microsoft SQL Server’s error 208, which is mapped to BDB error 506 (E_BDB_NOTABLE). 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 entries from the display driver, application server, and database driver contain enough information to determine the nature of and solution to the problem. Therefore, whenever an error is encountered with an error code greater than 1000, you are advised to check the log entries from the database driver.

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 can 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.

Profiling

Statistics

These options are discussed in the following sections.


Profiling

The profiling option of the drivers provides the user with a way to gather timings of SQL statements that are being carried out. Logging all statements with their timings, however, results in a log file that is so big that it cannot be properly analyzed. Therefore, you can define a logging threshold. Only statements that take longer than a predefined number of seconds are logged.

Profiling can be enabled by setting an environment variable. The profiling output is logged to a file in the current directory of the database driver.

Suppose the profiling environment variable is set to two seconds, then each statement that takes longer than 2.0 seconds (real time) is logged to the log file. The information that is logged in the file is the RDBMS request, with the elapsed time, user name, date, and time. The maximum precision that 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:

1 The execute event This event represents the amount of time the RDBMS engine took to carry out an SQL statement.

2 The fetch event This event represents the amount of time the driver took to retrieve data from the buffer or the DBMS.

Statistics

The drivers also provides an option to gather driver-wide statistics of actions being performed, such as:

Number of cursors (opened, closed, current open)

Number of parses, binds, executes, fetches

Number of logons (sessions)

Number of inserts, updates, deletes

Number of commits, rollbacks

For each action, the cumulative (real) time spent by the action and the 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). If 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 MSQL Driver Profiling and Statistics in Chapter 2 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 Chapter 2 Microsoft SQL Server database driver externals and internals

This chapter supplies additional information about the Microsoft SQL Server database driver. It describes both the external aspects of its communication with Baan IV and the internal features of the Microsoft SQL Server database driver.

MSQL driver externals

This section describes the characteristics of Baan IV application tables created in the Microsoft SQL Server RDBMS through the Baan IV MSQL Driver in level-1 mode, where Baan IV SQL queries are processed by the database driver on a single-table/single-row basis.

Data-dictionary mapping

Since the Baan IV data dictionary (DD) cannot directly be used by the driver to create MSQL tables (if, for example, there is no MSQL datatype that exactly matches a Baan IV datatype), some mapping or translation is performed on the driver side in order to create valid MSQL tables.

The following conventions play an important part in this process:

General name convention In general, all names generated by the driver are lowercased characters and are not enclosed by double quotation marks. Since the binary sort order is selected during the installation (for more information, refer to Chapter 1 of the Infor ERP Baan IV Installation Guide for Microsoft Windows NT), Microsoft SQL Server treats object names with case sensitivity.

2-2 | Microsoft SQL Server database driver externals and internals

Table-name convention The table name of a Baan IV table in MSQL has the following format:

t<Package><DD table name><Company Number (3 digit)>

For example, the Baan IV table ttadv999 with company number 000 is created in MSQL as tttadv999000.

Column name convention Each column in the Baan IV DD corresponds to one or more columns in the MSQL 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 MSQL as t_cpac. This is done to avoid reserved words. For example, a column name such as desc can not be used in MSQL as the order column name, since it is a reserved Transact-SQL word. Also, when (column) names contain a period [ . ], the period is replaced by the underscore [ _ ] character.

Long string columns Columns of type string in Baan IV can exceed the maximum length of character columns in MSQL. For CHAR data type this limit is 254 in Microsoft SQL Server 6.5 and 8060 in 7.0. For example, when the Baan IV string column exceeds this limit in SQL Server 6.5, the column is split into parts of up to 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, results in two MSQL columns:

t_desc_1: size 254 t_desc_2: size 46

In Microsoft SQL Server 7.0, the maximum character column-length is 8060. It is unlikely that the columns need to be split as described above, but if necessary they are split.

Array columns Array columns can be defined in the Baan IV DD. 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 MSQL 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

Microsoft SQL Server database driver externals and internals | 2-3

Note that when you use Microsoft SQL Server 6.5, if the element is of string type and one element type exceeds the maximum MSQL 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

Because the maximum character length in Microsoft SQL Server 7.0 is 8060, it is unlikely that the array columns will be split.

Array compression If the number of MSQL columns exceeds the maximum number of MSQL columns (255 for Microsoft SQL Server 6.5; 1024 for Microsoft SQL Server 7.0), the driver tries to compress (concatenate) array columns to reduce the number of columns. This means that all array elements of one array column are stored as one raw column in the MSQL database. The raw value contains all the elements concatenated in binary format. The driver starts compressing the array column that yields the highest number of columns. It continues compressing array columns until the number of columns has become lower than the maximum. The name of the column in MSQL is the same as normal columns, as in:

t_array: raw contains all elements.

Note that when using Microsoft SQL Server 6.5, if a compressed array column is longer than 254 characters, it is also split in parts of 254 characters or less. When using Microsoft SQL Server 7.0, the maximum character size is 8060, so it is unlikely that the compressed array column is split.

Index name convention Baan IV indexes are identified by a sequence number, starting from one for each table. Each table has at least one index (primary index). Since in MSQL the index names must 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 000) in ascending order:

I<table_name>_<idx_no><idx_type> Ittadv999000_1a (index 1 of table ttadv999 in company 000 in ascending order)

If a Baan IV index is defined as a unique index, then the MSQL index is created with the UNIQUE clause. Otherwise, (duplicate) indexes are created without the UNIQUE clause.

Data types Since Baan IV SQL expects ANSI-compliant string-comparison semantics, the MSQL CHAR data type is used (instead of VARCHAR). The driver uses the MSQL CHAR data type in cases where ANSI-


compliant standard behavior is expected for character data (for example, the Baan IV string type). This MSQL datatype is used because a Baan IV string data type has characteristics that conform to the ANSI specification for character data. Operations like comparison and concatenation can be done in a predefined, standard fashion with predictable results when the CHAR data type is used.

The following table shows the mapping between Baan IV data types and their MSQL counterparts.

The mapping between Baan IV and MSQL data types

Baan IV data type MSQL data type

Char Binary (1)

Enum Binary (1)

Int Smallint

Long Integer

Mail Integer

Time Integer

Text Integer

Bitset Integer

Float Real

Double Double

String (n) Char(n)/Varchar(n)

Date DateTime

Constraints All the columns created by the MSQL driver have the NOT NULL constraint. Baan IV applications do not support NULLS.

The date range for the Baan IV application server is not the same as MSQL (MSQL is more restrictive), so some Baan IV dates are not valid when stored with the MSQL driver. Baan IV date 0 is mapped to the earliest possible date in MSQL ( 01-Jan-1753 ). If you use Microsoft SQL Server, the earliest storable Baan IV date is then 02-Jan-1753 and the latest is 31-Dec-9999.

MSQL table and index location

The MSQL driver provides a way to specify the distribution of table and index data in different segments. A table’s storage (or creation) parameters can be specified in the file %BSE%\LIB\MSQL\MSQL_STORAGE. This file is


referred to as the storage file. Storage parameters are used by the MSQL driver when DDL statements are carried out (like the create table and create index statements). The appearance of the SEGMENT keyword (followed by a segment name) in the storage file causes the MSQL driver to add the on <segment name> clause to the create statement during index or table creation. Refer to Appendix D for more detail on the storage file.

Examples of SEGMENT entries in the storage files are:

SEGMENT segment1

or

segment segment1

Note

If the segment for a table or index is not specified, then the table and index data are created in the default segment. If index data is to be separated, it is necessary to specify a segment.

Security

This section lists the security aspects. The following aspects can be distinguished:

Authentication Baan IV users mapped to MSQL users are allowed to establish a connection to MSQL RDBMS with their own user name and password. To prevent unauthorized users from accessing the database, nonmapped users are not able to establish a connection to the database. When a database is created, an administrator or MSQL database owner (DBO) creates a logon for the user and associates the user with a group in the database that has object privileges (the group user corresponds to the target database). The members that belong to this group inherit these privileges and are able to establish a connection to the database either through unified logon or by using a valid password stored in encrypted form in the driver administration files. A user can be added to or dropped from the group by using the Baan IV administration session or the MSQL_MAINT utility. The users who are authorized to access the database are registered in the Baan IV driver administration files. The user name and password that each Baan IV user uses to log on to the MSQL RDBMS is maintained in the %BSE%\LIB\MSQL_USERS file. This will be explained later in this section.

Object security In MSQL, 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 MSQL database, a mechanism has been developed to allow users to share these tables.

The group concept In order to allow different Baan IV users to share the same MSQL table, a group concept has been introduced. A Baan IV group is an abstraction that maps users to a particular database in MSQL and ensures that members of the group have sufficient privileges to access data in the group’s tables.

A Baan IV group is made up of three components in the MSQL Server, which are a database, a logon and a Microsoft SQL Server group. The MSQL database has the same name as the Baan IV group. The logon (the same name as the Baan group ) is assigned DBO (database owner) privileges in the database. Finally, a MSQL group is created, which becomes the target for privileges granted to objects in the database. Users are associated with the MSQL group and, as a result, inherit the privileges granted to the group. The advantage of having a group table is that the members of the group can share and operate on the same data in a single table.

For example, users peter and john can both be assigned to group baandb. Group baandb owns the tables and grants SELECT, INSERT, DELETE, UPDATE privileges to the group. As a result, users peter and john inherit the SELECT, INSERT, DELETE, UPDATE privileges granted to the group allowing them to access and manipulate group table-data.

A user can also define whether a table must be created as a group table or as a private table. 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 by the group user and the privileges are granted to the group, allowing all users in the group to access it. A table can be configured as a private or group table with the %BSE%\LIB\MSQL\MSQL_STORAGE file.

The DDL statements generated by the driver do not specify to which user an object belongs (that is, no create <user>.<object> statement is generated>). Ownership is determined based on which session (group or user) the create table is executed in. When creating objects identified as belonging to the group, the user creating the object actually logs into the DBMS server as the group user. In this case, the table is owned by the group (and permissions are granted on it to allow all group users access). When creating objects identified as private, the user is connected to the DBMS server under his/her own logon. In this case the table is owned by the user and no other permissions are granted.

Authentication externals All the Baan IV users and their corresponding MSQL logon names and passwords and the name of the group they are assigned to are defined in


the %BSE%\LIB\MSQL\MSQL_USERS file. The format of each entry in this file is shown below.

<Baan IV user>:<MSQL user>:<Encrypted MSQL User password>:<Group name>

The MSQL driver is started by the application server on behalf of the user. From the %BSE%\LIB\MSQL\MSQL_USERS file the driver identifies the MSQL user and the user’s password and logs on (that is, establishes the connection) to the MSQL RDBMS.

The group logon procedure also includes a password, which is defined in the %BSE%\LIB\MSQL\MSQL_GROUPS file. The format is as follows:

<Group name>:<Encrypted Group password>

Object security externals The definition of the table to be created as a private or a group table is defined in the %BSE%\LIB\MSQL\MSQL_STORAGE file. The format has already been explained in the section MSQL Table and Index Location, earlier in this chapter. The keywords group or private can be specified in the appropriate field. For example:

{peter,john}tdsfc:*:T:group:011:: {charlie}tdsfc:*:T:private:011::

This indicates that users peter and john create tables in the Shopfloor Control (tisfc) module as group tables and user charlie has his own private tables in the Shopfloor Control (tisfc) module.

Security internals Internally the MSQL driver uses a Microsoft SQL Server group to implement the Baan IV group concept. Whenever a new table is created by the group user, SELECT, INSERT, DELETE and UPDATE privileges are granted to the MSQL group. Any user associated with the group automatically inherits these privileges and can individually perform these operations on the group table.

Also, when new users are added, they need only be associated with the group, and automatically inherit all privileges currently granted to the group without a need to grant privileges on every group object in the database to the user. When the user is dropped from the group these privileges are revoked. The user no longer has access to tables in that group. If the privileges to operate on the tables were explicitly granted to the user, then they must also be explicitly revoked when the user is dropped from the group. The overhead of adding users is now greatly reduced by granting privileges to the group, thus providing flexibility and ease of maintenance.


MSQL error reporting

When the MSQL driver encounters an error, an error message is logged in the Windows NT Application Event Log (or in some cases in the %BSE%\log directory). The files involved are MSQL_SRV.LOG and LOG.MSQL.MESG. The error message contains the MSQL error, sqlcode, sqlstate, and the event that caused the error. Refer to the section Error logging in Chapter 1 for details.

MSQL driver profiling and statistics

This section describes profiling and statistics, as implemented in the MSQL database driver.

Profiling

To activate the profiling of the MSQL driver, the MSQLPROF environment variable must be set. For example:

SET MSQLPROF=5.0

The result of this command is that each statement that takes longer than 5.0 seconds (real time) is logged to the log file. The profiling output is logged in the MSQLPROF file in the driver’s current directory. Refer to the section Tuning in Appendix A for the profiling output.

Statistics

The MSQL driver also provides a way to gather so-called driver-wide statistics of the actions that are performed by setting the MSQLSTAT environment variable, for example:

SET MSQLSTAT=0 (logs final report only) SET MSQLSTAT=30 (logs a report each 30 seconds)

The statistics report is logged in the MSQLSTAT file in the driver’s current directory. Refer to the section Tuning in Appendix A for the statistics output report.

MSQL driver internals

In this section the following MSQL driver’s internal issues are discussed:

The ODBC interface

MSQL Driver SQL processing


The MSQL driver locking strategy

The ODBC interface

The MSQL driver uses the Open Database Connectivity (ODBC) interface to communicate with Microsoft SQL Server. ODBC is basically a function library that can be called from a program in order to carry out SQL statements and otherwise communicate with an ODBC data source. The initial database driver implementation used ODBC version 2.5. With porting set 6.1c.03 and later, the database drivers are implemented using ODBC version 3.0.

The functions called by the MSQL driver perform the following actions:

Connect to Microsoft SQL Server (open session)

Allocate a statement handle

Parse a SQL statement

Bind input variables

Define output variables

Carry out an SQL statement

Fetch the resulting rows

Commit/abort a transaction

Close, unbind, and drop a cursor

Disconnect from MSQL (close session)

The following features of ODBC are also used by the MSQL driver (for reference):

Array fetches (when enabled)

Array inserts (when enabled and possible)

MSQL Driver SQL processing

The internal processing of SQL statements is as follows:

SQL statements are dynamically generated by the MSQL driver database-dependent layer. As Baan IV applications are dynamic in nature, it is not known in advance which tables will be used at run time. Therefore it is not possible to prepare the queries before run time.

When the MSQL driver receives a query from the application server, the query is translated into a format suitable for MSQL and then passed to MSQL by way of the ODBC function calls. A statement handle is allocated in the


ODBC Driver Manager and the query is carried out by assigning to it the statement handle. The SQL statement is parsed, input and output variables are bound, and the query is carried out by using the statement handle. In some cases, MSQL opens a server cursor internally for query execution. After the query is carried out, a fetch operation is done and the resulting column values are placed in the bound output variables. The rows returned by MSQL are passed to the database-independent layer of the driver, which sends the results back to the application server.

When a statement needs to be reexecuted, the cursor from the previous execution is closed and the resulting rows are discarded (whether the reexecution 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 reexecuted. However, for reexecution, no reparse of the statement or rebind of input and output parameters is required, which improves the total performance.

When array fetching is enabled, multiple rows are fetched in one call to the driver. Space is allocated in the driver to buffer multiple rows fetched in one operation (the rows fetched in the buffer can be, say, five, 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-inserting is enabled, the rows to be inserted are placed in a buffer by the driver. When the buffer is full (or some other event necessitates it), a so-called flush is generated, sending the rows to MSQL. The rows in the buffer are inserted with a so-called multirow-insert.

Note

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 the MSQL_STORAGE file on a per table basis or globally, by using an environment variable or resource variable. Refer to the section Array interface in Appendix A to see how the array-interface can be enabled.

MSQL driver locking-strategy

The MSQL driver can use both implicit and explicit locking during updates. In some cases, the driver generates queries that contain optimizer lock hints to direct the SQL Server to choose a particular lock level when carrying out a query. In other cases, the driver does not supply these hints, but uses the default locking determined by the isolation level being used in the connection.


Locking

When a record is locked by the MSQL driver for UPDATE or DELETE, that row can be SELECTED WITH LOCK before it is actually updated/deleted. The row is locked when the SELECT WITH LOCK statement is carried out to make sure that the row is not changed by another user. The SELECT WITH LOCK statement locks the row acquiring a shared or exclusive lock, depending on the isolation level and any lock hints used. If the process tries to acquire an exclusive lock on the row that is already locked by another process, then the process waits until the locked resources are released or a timeout period expires. If timed out, the client then decides to retry the same operation or to rollback the transaction.

The MSQL driver enforces data integrity by using MSQL’s locking mechanisms. Implicit locking is applied whenever possible; in other cases the driver performs explicit locking.

The driver uses the read-committed isolation-level by default to acquire shared locks in case of look up references and acquires exclusive lock for update and delete actions. The driver typically uses a nolock (dirty) read for normal read operations. Exclusive types of locks are required so that the locks are retained until the transaction is committed or aborted, even after the cursor is closed.

Statement and lock time-outs

A SELECT WITH LOCK statement waits for a predetermined time period (timeout duration) if a resource is locked by another session. In the SQL Server 6.5 driver, this timeout is actually a statement timeout. In SQL Server 6.5, you cannot determine if the statement timed out as a result of a resource lock or some other unrelated reason such as slow network throughput or poor response from the database server. This timeout duration is configurable. In cases where the database server resides on a separate machine from the driver (or server performance is not optimal), it can be necessary to increase the wait time (timeout duration) so that the database driver does not give up before the server or network has had an opportunity to deliver the results from a request.

In SQL Server 7.0, a true lock timeout is implemented. The server waits up to a given period of time for a resource when that resource is locked by another process. If the resource is not freed by the end of the lock timeout period, a special error code is returned that identifies this condition. The Baan IV driver for Microsoft SQL Server 7.0 exploits this new feature. Note that statement timeouts can occur on statements that do not take locks (even those that read through locks). The lock wait period for SELECT FOR UPDATE, INSERT, DELETE and UPDATE can be specified using a resource or environment variable. For the MSQL database driver for Microsoft SQL Server 6.5, the resource variable is msql_stmt_timeout and the environment


variable is MSQL_STMT_TIMEOUT. For the MSQL database driver for Microsoft SQL Server 7.0, the resource variable is msql_lock_timeout and the environment variable is MSQL_LOCK_TIMEOUT. For a description of how to configure these values, refer to the section Locking Behavior in Appendix A. Note that care must be taken when experimenting with these options.

3 Chapter 3 Microsoft SQL Server database driver administration tools

This chapter describes the Baan IV administration tools for the MSQL driver. The following tools are discussed:

The Database Administrator module (DBA )

The MSQL_MAINT and MSQL7_MAINT utilities

Introduction

Administration of files used by the MSQL driver (in the %BSE%\LIB\MSQL directory) is achieved by using the Baan IV DBA module. This module allows an administrator to register authorized users in the administration files and perform the necessary actions in the server that give users access to data. A tool is provided along with the MSQL driver to aid in the administration necessary to maintain the server and the files that the driver needs at run time.

The DBA module implements the user and group administration functions for all Baan IV database drivers. The MSQL_MAINT utility is an executable program, called by the DBA module, which implements the functions necessary to affect changes in Microsoft SQL Server. All administration must be done through the DBA module. MSQL_MAINT must only be used by advanced users when necessary to manually perform administration functions.

Both the DBA module and the MSQL_MAINT utility are discussed in the sections below.

3-2 | Microsoft SQL Server database driver administration tools

The Database Administrator module (DBA)

You can enter the DBA module by choosing the Database Management menu from the Tools main menu, and by selecting the RDBMS Administration menu. The DBA module contains the following sessions:

Maintain Links between Baan Users and Database Users Maintain Database Users Maintain Database Groups Maintain Table and Index Storage

The sessions listed above are explained in the sections below.

The Maintain Links between Baan Users and Database Users (ttdba0110m000) session

This session enables you to link an existing Baan IV user to an MS SQL Server user. The information that is added by using this session is written to the %BSE%\LIB\MSQL\MSQL_USERS file.

To link an existing Baan IV user to an MS SQL Server user, select the Maintain User Link form and insert the Baan IV user. Insert the database user to which the Baan IV user must be linked in the Database User field. If this database user does not exist, you can zoom to the Maintain Database Users session. By selecting the Maintain DB User form you can add the MS SQL Server user. Note that you must always select By Baan as value for the Create User field.

Microsoft SQL Server database driver administration tools | 3-3

The Maintain Database Users (ttdba0115m000) session

This session enables you to add new MS SQL Server users. After you started the session, select a database and click OK. By selecting the Maintain DB User form you can add the MS SQL Server user. Note that you must always select By Baan as value for the Create User field. After you have inserted the information, click the Convert Users button.

The Maintain Database Groups (ttdba0120m000) session

By using this session a new group, which is a group already known to MS SQL Server, 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\MSQL\MSQL_GROUPS file.

To add a group, the following information must be specified:

Group name

Group password (not echoed)

Note that you must always select By Baan as value for the Create Group field. After you have inserted the information, select the Database Groups form, and click the Create and Dump button.

Note

A new group cannot be an existing user or group name.

3-4 | Microsoft SQL Server database driver administration tools

The Maintain Table and Index Storage (ttdba0140m000) session

This session enables you to start the text editor for the %BSE%\LIB\MSQL\MSQL_STORAGE file by clicking the Retrieve button. You can then change the parameters in this file. After you exit the editor, the Maintain Table and Index Storage (ttdba0140m000) session is redisplayed.

Note

Changing the MSQL_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.

The MSQL_MAINT utility

The DBA module uses the MSQL_MAINT tool to perform administration tasks. While you can use MSQL_MAINT to manually perform the tasks carried out by the DBA module, Infor recommends that you use the DBA module instead. If you use MSQL_MAINT, the changes you make are not made to the msql_users and msql_groups administration files.

Microsoft SQL Server database driver administration tools | 3-5

MSQL_MAINT performs several administrative functions. Each function requires certain options to be specified. In the function descriptions below, all options must be specified unless they are listed within brackets. For options listed within brackets, exactly one of the two sets of options within brackets must be specified.

Two additional options, –b and –q, are available for each function. These options are defined as follows:

b allows processing in batch mode

q redirects error messages to a specified error file

Note that changes made through MSQL_MAINT will not affect the msql_users and msql_groups files. Any unknown user specified as input to MSQL_MAINT will be created.

The MSQL_MAINT functions and their required options are explained below:

To add a user to a group: MSQL_MAINT –a<Baan IV user> –m<MSQL user> –p<user password> –G<group name> –c<admin name> –i<admin password>

To change a user password: MSQL_MAINT –r<Baan IV user> –m<MSQL user> –p<new password> { –o<old password> | –c<admin name> –i<admin password> }

To remove a user from a group: MSQL_MAINT -d<MSQL user> –G<Group name> –c<admin name> –i<admin password>

To add a group: MSQL_MAINT –A<group name> –P<group password> –c<admin name> –i<admin password>

To change a group password: MSQL_MAINT -R<Group name> –P<new group password> { –o<old group password> | –c<admin name> –i<admin password>

To remove a group: MSQL_MAINT –D<group name> –c<admin name> –i<admin password>

To check the password: MSQL_MAINT –h –m<MSQL user> –p<password>

To print usage information: MSQL_MAINT { –U | –? }

To print version information: MSQL_MAINT { –v | –V }

A Appendix A Configuration and tuning

This appendix supplies information about general configuration and tuning recommendations.

The following topics are covered in this appendix:

Microsoft SQL Server configuration and tuning

Baan Microsoft SQL database driver configuration and tuning

Windows NT tuning

Microsoft SQL Server configuration and tuning

Configuring and tuning Microsoft SQL Server is important in order to avoid possible performance bottlenecks and to optimize the performance of the Baan IV applications with a Microsoft SQL Server database. Several topics are discussed briefly in this chapter. For further details, refer to the Microsoft SQL Server documentation.

Microsoft SQL Server configuration

You can configure SQL Server (via configuration parameters) by using either SQL Enterprise Manager (for version 6.5, from the Server Manager menu, choose Server-> Configure) or the sp_configure procedure stored in the system. In SQL Server 7.0 configure these parameters from the Enterprise Manager, choose Server-> properties.

There are several configuration parameters that must be modified before you start using SQL Server (note that most of these are modified by the Baan installation program when you install Baan IV with SQL Server). If you use

A-2 | Configuration and tuning

SQL Server 7.0, many of these parameters are dynamically configured. The following are the parameters that you may need to modify:

User Connections [dynamic in SQL Server 7.0]

Memory [dynamic in SQL Server 7.0]

Open Objects [dynamic in SQL Server 7.0]

Disable Truncate Log on Checkpoint

The User Connections parameter configures the maximum number of simultaneous user connections to SQL Server. The Baan application can consume an average of five connections per user.

This number can vary widely based on the particular usage pattern of a user and the sessions the user is running. Each configured connection uses 37K of SQL Server memory, so it is advisable to monitor the number of connections (for example, with SQL Performance Monitor) to confirm that the server is not over-configured. Under-configuring this value results in failed connections to SQL Server, which are logged as error 510 in Baan IV. The default for this parameter is 15 connections, which is sufficient for installation, but insufficient for more than a handful of Baan run-time users. This parameter is currently not adjusted by the installation program.

The Memory parameter in SQL Server determines the amount of memory (in 2048-byte pages) that the server will request from the operating system at server startup. From this pool of memory, SQL Server allocates any resources (for example, user connections, locks, and so on) that have been configured, and uses the rest for caching. This remaining memory is divided among the buffer and procedure caches. The default value for this parameter is 8,192 pages. The Baan IV installer will double it to 16,384 pages unless it is already configured to or over this value.

The Open Objects parameter configures the number of object handles that SQL Server allocates. Each takes 70 bytes of SQL Server’s configured memory. The default value is 500 objects. The Baan IV installer increases it to 5,000 if it is not already configured to or over this value.

In SQL Server 6.5 the Truncate Log on Checkpoint option is configured on a per database basis. Log truncation is disabled during the Baan IV installation process for the Baan IV target database. If enabled for a database (the default), the server performs a truncate of the database’s transaction log every time a checkpoint occurs. This can have an adverse impact on performance and also prevents the administrator from being able to restore the database to its most recent state in the event of a media failure, since logged operations are not saved. Disabling the option means that the log file will continue to be consumed and must be manually dumped or truncated by the database administrator in order to avoid filling the log file. If the database is used for purposes that do not require backups and recoverability, this

Configuration and tuning | A-3

option can be enabled to avoid ever having to dump or truncate the log manually.

In SQL Server 7.0 the Truncate Log on Checkpoint option behaves the same way as in version 6.5. However, the log file can be configured with Autogrow, allowing it to expand as needed.

Microsoft SQL Server tuning

Microsoft SQL Server tuning is basically a combination of modifying the server configuration and tuning the raw data (for example, device placement, indexing, locking, and so on). Also, tuning recommendations can depend on whether the application is run in host mode (Baan IV application and Microsoft SQL Server on the same machine) or in a client/server configuration (Baan IV application on one machine and Microsoft SQL Server on another).

Configuration parameters

In addition to the above recommendations, it can be beneficial to adjust the setting of other parameters in SQL Server. The following configuration parameters can be modified from their defaults to improve Baan IV performance with SQL Server:

Procedure cache [dynamic in SQL Server 7.0]

Locks [dynamic in SQL Server 7.0]

tempdb in RAM (if sufficient memory is available) [N/A in 7.0]

Recovery interval [dynamic in SQL Server 7.0]

Set working set size

Priority boost

Processor affinity

Note

In SQL Server 7.0 the procedure cache, locks, and tempdb in RAM are dynamically configured.

The Procedure Cache parameter determines the percentage of remaining memory that the SQL Server allocates to the procedure cache after all configured resources have been allocated. The remainder is allocated to the buffer cache (that is, the buffer cache is total memory minus procedure cache memory). Since the Baan driver for SQL Server exhibits relatively low procedure cache utilization, you can reduce the value of this parameter from its default of 30 to perhaps 10 percent. As with all parameter


recommendations, changes must be monitored with SQL Performance Monitor to confirm their effectiveness. This parameter is dynamically configured in SQL Server 7.0.

The Locks parameter determines the number of locks available to SQL Server at run-time. Each lock consumes 32 bytes of SQL Server memory. A lack of locks can cause SQL Server to perform lock escalation (consolidation) to free up lock resources, which can deteriorate overall application throughput. It is a good idea to monitor lock utilization and to increase the configured value, if necessary, to avoid lock escalation. If this value needs adjustment, it may also be necessary to reevaluate lock escalation thresholds. The default value is 5,000 locks. This parameter is dynamically configured in SQL Server 7.0.

The tempdb in RAM option instructs SQL Server to allocate memory (in addition to configured memory) for tempdb instead of having it reside in a device on disk. If tempdb activity is significant and memory is available, placing tempdb in RAM can have a positive impact on performance. This parameter is not available in SQL Server 7.0.

The Recovery Interval parameter configures the maximum number of minutes per database that the server will need to complete its recovery process. Changing this value can have an impact on the checkpoint interval. Assuming a fixed amount of transaction log activity, increasing the value can reduce the frequency of checkpoint operations. Decreasing this value can increase the frequency. The default is five minutes.

The Set Working Set Size option can direct the operating system to reserve physical memory for SQL Server to accommodate its memory demands (including tempdb if configured in RAM). The effect of this option is to lock physical memory for SQL Server’s use. This can have an impact on memory allocation and swapping activity. It is a good idea to set this option on a machine that is being used strictly as a database server to prevent SQL Server memory from being paged out, assuming there is sufficient memory available. It may be called for in other circumstances as well. If the machine is not a dedicated database server machine and this option is set, other applications can be adversely affected by a lack of memory. In these cases, it is important to analyze both SQL Server and overall system memory utilization in order to maximize its effective use.

The Priority Boost option determines whether SQL Server must run at a higher priority than other processes that are being carried out on the same machine. Again, you are advised to have a dedicated database server machine.

The Processor Affinity parameter can control SQL Server’s processor utilization by forcing its threads to be scheduled on designated CPUs. This can improve overall system performance by reducing context switching in


general. It can also be used to isolate SQL Server threads so they do not consume all available CPU time on the machine. This can be useful if a machine is used as both an application server and as a database server.

Disk I/O configuration and data placement

The goal of successful I/O configuration is to minimize situations where transactions in the server are waiting on disk I/O. To accomplish this, it is useful to understand the server’s I/O activity. In general, the server manages two types of I/O: database activity and transaction log activity.

Database activity is generally routed through a complex and efficient read-ahead and buffering system, so it rarely causes transactions to wait, assuming the server’s read-ahead and buffering subsystems are adequately configured. Also, database waits usually occur on reads, most often when the cache does not contain data needed to satisfy a request. Database writes are normally accomplished in an asynchronous or write-behind fashion and the transaction need not wait for this I/O to complete. However, the transaction log is the repository for changes to the database during the course of a transaction, so it is wise to optimize write throughput for I/O of this type. This is usually one of the first arguments for isolating data and log activity onto separate devices. Because of the dependence of transactions on log writes, it is often better to isolate the log device from other SQL Server devices and even other applications (including the operating system) at the hardware level to reduce resource contention that could adversely affect throughput.

You are advised to allocate the hardware in such a way as to favor log file write throughput. This includes avoiding the use of highly reliable disk configurations such as RAID 5 for the log device, where throughput can suffer in favor of availability. A less intrusive configuration that still offers redundancy, such as disk mirroring, is often preferable for log devices.

For more details on server configuration refer to the Microsoft SQL Server Administrator’s Companion.

Microsoft SQL Server locking

This section discusses how Microsoft SQL Server handles locking. The following topics are covered:

Locking strategies

Insert row lock

Fill factor and indexes


Locking strategies

Understanding and configuring lock behavior is another important aspect of the tuning process. The physical layout of data and the locking that ensues from modifying data in the database can affect concurrency in database applications.

In general terms, there are two approaches with respect to locking of the physical data in commercial database solutions. One is referred to as row-level locking and the other as page-level locking. In a row-level locking database, the lowest level of granularity for a lock is the row.

Rows can have a variable length (even in the same table) and any row that exists in the database can be locked. In general, this is an inefficient way to manage the database server.

The other common approach is page-level locking. The advantage to this solution is that a page is fixed in size and, generally, more than one row exists on a page. A page lock is more efficiently managed by the server because of the fixed page size and the fact that there are generally fewer locks to handle when compared to row-level locking solutions. The disadvantage is that a page lock can affect more than the single row that is being modified and this can have an adverse impact on concurrency.

Microsoft SQL Server version 6.5 attempts to give you the advantages of both. It has traditionally been a page-level locking database but, with the 6.5 release, Microsoft introduces a dynamic locking-strategy for insert operations. This strategy allows the server to use page-level locking when there is no lock contention, but to deescalate the page lock to a row-level lock if there is insert contention for the locked page.

The server can de-escalate if an insert operation is locking a page and another server process needs to insert on the same page. The update, delete, and select-with-lock statements still take page locks and cannot be deescalated. Also, those locks are not compatible with an insert-row-lock.

With Microsoft SQL Server 7.0, full support of row-level locking is available. A dynamic locking strategy is used to determine the most cost-effective locks to use. With Microsoft SQL Server 7.0, the insert row lock option is no longer necessary, and is ignored if set in the storage file.

Insert row lock in SQL Server 6.5

Used judiciously, the insert row lock option in SQL Server 6.5 can be an effective means of improving concurrency in the database. It is not used with SQL Server 7.0. Insert row lock is most effective when applied to tables that are only inserted into or read without lock (dirty read) in the application. In this case, since the pages of the table are only locked by inserts, the server


can de-escalate an insert page lock to the row level. This reduces lock serialization on inserts to the table, thereby improving concurrency. However, if the table also has page locking activity (update, delete, select-with-lock), there can be an increased likelihood of deadlocks in the database. This usually happens when two server processes get insert row locks on the same page and then both need page locks on that same page.

The Insert Row Lock (IRL) option can be configured on a per-table basis in the storage file. The option is set by the driver at table create time. So, if the table already exists, you must use SQL Enterprise Manager or ISQL to set the option for the table.

Otherwise, you must use the bdbpre and bdbpost utilities with the -k option to drop the original table and recreate it with the new storage options.

Below is an example of an msql_storage file with IRL set for table tdsls041.

tdsls041:*:T:group:011:5:IRL *:*:T:group:011:5: *:*:I::011::

Fill factor and indexes

Another useful method to reduce the impact of page locking on concurrency is by using the fill factor setting for indexes in SQL Server. The fill factor setting can influence the number of rows per data page when the index is created. Using a low fill factor setting causes SQL Server to spread the data over a greater number of pages, which gives better concurrency. The fill factor setting is not enforced after the index is created, however. Use of this option gives the most benefit in cases where the number of rows in the table does not change, but there is a significant amount of update activity on existing rows. The First Free Numbers (tcmcs047) table is an example of this.

tcmcs047:*:T:group:011:5:FILLFACTOR 1 *:*:T:group:011:5: *:*:I::011::

A low fill factor setting also helps to reduce page and index splitting by allocating a greater number of pages for the data and indexes with ample extra space to accommodate new rows. It has the side-effect of consuming more space in the database and can also cause more I/O activity to satisfy reads.

Baan Microsoft SQL database driver configuration and tuning

The MSQL Level-1 driver has numerous configuration options that are discussed here. Driver analysis and tuning will also be discussed.


There are several facilities available to configure the driver. The most common is through driver resources. These resource settings are configured in the %BSE%\lib\defaults\db_resource resource file.

The other common configuration method is with storage parameters. These are configured in a driver-specific storage file (%BSE%\lib\msql\msql_storage for the Baan IV Microsoft SQL Server driver). Both parameter types are discussed here.

Appendix B gives a detailed description of how to configure the database driver.

Cursor management

The MSQL driver has a built-in cursor management mechanism using a least recently used (LRU) algorithm. Each cursor represents one type of SQL statement. The msql_max_open_handles resource variable can be used to influence cursor management. This parameter limits the number of open cursors the driver maintains on a per-connection basis. The default is 200 open statement handles per connection.

Array interface

The MSQL driver can use multirow ODBC statements for array fetches and array inserts. With the array interface, communication between the MSQL driver and Microsoft SQL Server is more efficient: multiple rows are fetched or inserted with a single ODBC call. However, because multiple rows need to be stored in a buffer in the MSQL driver, more memory is consumed. To size the buffers to hold array rows, the msql_max_arrsz resource variable must be set. The array buffer size can be set on a per table basis using the ARRAY_SIZE storage parameter in the MSQL_STORAGE file. For example:

*:*:T:group:011:5: ARRAY_SIZE 5

Note

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 MSQL_ARRAY_FETCH environment variable. Array inserts are enabled by default when the bdbpost utility is used with the -f option (it is necessary to set the array size, otherwise the inserts will not be buffered). There is currently no equivalent resource variable to configure this option.


The array insert interface can be enabled with the MSQL_ARRAY_INSERT environment variable. There is currently no equivalent resource variable to configure this option.

Array interfacing is especially useful when you are accessing a remote database, since the number of network round-trips can be reduced.

Locking behavior

The MSQL driver uses the Read Committed isolation level by default, but adds the NOLOCK hint (dirty read) to SELECT statements explicitly on reads without lock to increase concurrency. Multirow read requests do not acquire any type of lock (shared or exclusive) unless explicitly stated in the query syntax. Queries such as INSERT, DELETE, and UPDATE acquire an exclusive lock as the Read Committed isolation level is used. A SELECT WITH LOCK request acquires an update lock. Only in case of lookup references are shared locks acquired using the default locking behavior of the read committed isolation level. The locks are retained until the transaction is committed or aborted.

High-level lock retries

When a row lock cannot be acquired due to locking, high-level lock retries are initiated. This means that the same action is repeated after some delay period. The retry behavior can be configured with the lock_retry resource variable. This can contain a comma-separated list of combinations of a number of retries and sleep periods in milliseconds (ms), for example: lock_retry:5*100,5*500

This is the default retry behavior, which means that the action is retried five times with a sleep period of 100 ms and then five times with a sleep period of 500 ms. The lock retries can also be disabled by specifying: lock_retry:0

This is a feature common to all database drivers.

Note

The lock_retry variable can be used both in Level-1 and Level-2 drivers.

Index optimization in Level 1 drivers

Index optimization is a technique used in Level-1 drivers to obtain better performance for SELECT statements in relational databases. The reasoning behind this is that the Baan IV Level-1 drivers generate queries that are not


very complex, and the driver can take advantage of several attributes of Baan IV application tables to improve access time. The index optimization technique causes a new column (referred to as a hash column) to be created in the table for each index defined in the Baan IV data dictionary. This hash column contains a concatenation of values from each column participating in the key for that index. Also, the first (or primary) index is always unique, so it can be used (again concatenated) in other nonunique indexes to make them unique. Finally, a raw data type can be chosen as the storage type for the hash column.

The combination of key reduction (concatenation into a single column) and index uniqueness has been found to give performance gains in Level-1 drivers implemented on RDBMSs.

In addition, sophisticated optimizers in relational DBMSs tend to overanalyze these Level-1 queries in an attempt to optimize their execution, so the driver exploits any means available in the host DBMS to reduce the effort needed for the optimizer to arrive at an acceptable execution plan. In the MSQL driver, this includes the use of Transact-SQL index hints, so the optimizer does not need to determine if a usable index exists for a query.

In Baan IV applications, an index can consist of multiple columns (concatenated keys). When you want to retrieve data in the order of the index, then the first column in the index is considered most significant, the last part is considered least significant. So each column has its own significance in 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 must treat each column differently.

For example, consider Table A with columns t_col1, t_col2, and t_col3, which are all of integer type, and an index on t_col1, t_col2, and t_col3. The table contains five 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 that are greater than the 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 that 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. An index is created on the hash column. 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}, you 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 quickly evaluated by the optimizer. 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; the only distinction made is between ascending and descending.

Hash column naming convention

The name of the hash column in a real Baan IV application table is formed using the hash keyword and the index number for which it is being created. Any descending hash column is created with a d 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.

Relationship 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

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\msql\msql_storage file. To specify whether index optimization must 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.

Fetch optimization and caching

This section discusses fetch optimization and caching, which are both used to optimize performance.

Fetch optimization

In Baan IV, it often occurs that a set of rows is retrieved from the table in the database. Usually an application is performing the following database actions:

READ FIRST READ NEXT READ NEXT READ 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. This technique, combined with the isolation level or locking strategy employed, determines how current the data is during a read operation. On-demand reads combined with dirty-read locking gives the most current view of the data. However, in many situations this behavior is not required. Read performance can be improved at the expense of a less current view of the data.

Fetch optimization is described as follows. After fetching the first row of a query with a multirow result set, subsequent rows satisfying the query are also fetched and returned to the client. Rows updated or deleted in other concurrent connections between fetches may not be reflected in this set of


rows. The changes made in other concurrent connections are only reflected when the query is reexecuted. A user may not need to see all row changes at the exact moment they occur, and could simply fetch the next row from the existing (buffered) row set, instead of reexecuting the statement to get the most current view. This technique is referred to as fetch optimization.

Critical to the use of this technique is the definition of a refresh time that allows the user to specify a time interval, in seconds, for which a fetch-optimized set of rows is valid. As long as the set is deemed valid, data can be fetched from the buffered row set, and changes that occurred as a result of activity in other connections since the query was carried out, are not reflected in the data. If the rowset expires (that is, refresh time is exceeded), the statement is automatically be reexecuted in the driver before the next row is returned.

For example, a set of rows is retrieved when DB.FIRST is issued. For a refresh time of five seconds, the DB.NEXT call will fetch the next row from the fetch-optimized set of rows. All consecutive DB.NEXT calls within five seconds do not fetch from the database, but are fed from the buffered row set until the buffer is exhausted. After five seconds the set is considered invalid and a subsequent DB.NEXT causes a reexecute of the query and fetch from the database. This results in a performance improvement by reducing the number of query executions, which translates into a reduced DBMS server load and IPC/network traffic.

You can set the refresh time for tables (T entries) in the storage file. For index (I) entries, the Refresh field is ignored. The default is 0 if not specified.

For example, the MSQL_STORAGE file can contain:

*:*:T:group:011:5: *:*:I:group:011::

The above example shows that all tables and companies use single hash columns for index optimization purposes and a refresh time period of five seconds for fetch optimization.

*:*:T:group:011:: *:*:I:group:011::

In this example all the tables have a default refresh time of 0 seconds (fetch optimization disabled).

Fetch optimization can be performed under certain circumstances, that is, if there are several records in the result set and if either of the following is true:

A fetch next action takes place within the refresh interval:

The entire table is locked by the query


Row caching

Row 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 the request is not passed to the RDBMS.

Note that caching and fetch optimization are consistent with regard to one Baan user. Database changes made in any of the sessions that run within a single application server (bshell) always disable current sets related to the update. Consequently, additional fetches always fetch the new result. Changes made by other users are not reflected in the refresh time.

Optimistic and pessimistic reference checks

To optimize concurrency, the MSQL 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 referenced record in order to be sure that another user cannot delete it during the current transaction. This approach is called the pessimistic approach.

This approach also blocks an insert of another user referencing the same parent row, thereby affecting the concurrency. To avoid this problem there is also an approach where a row in the parent table that is not locked is used, depending on 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 concurrency. You can enable this option by configuring through the dbsinit resource variable.

Tuning

In order to determine which table actions are most time-consuming, you can set the MSQLPROF environment variable to a number of seconds. All actions that take longer than defined by MSQLPROF are written to a file, stating, among other things, the time required to carry out the statement and retrieve the result. This file is called MSQLPROF and is stored in the current working directory of the driver.


A sample MSQLPROF 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.016

The data in the above sample file can be explained as follows:

In this example the number of seconds (profiling value) is 0.00. This means that all actions are written to the file.

The asterisk [*] after some of the records indicate that the records were first read and then locked.

The I column lists the number of the index used.

The cache column lists a value when a result is retrieved from cache memory.

For each action two executes and two fetches are recorded. This is useful 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 MSQLPROF environment variable in the %BSE%\LIB\TABLEDEF6.1 file, for example as follows:

tccom010:812:msql(MSQLPROF=0.4)

In this example all the queries on table tccom010812 that require more than 0.4 seconds are logged in the MSQLPROF 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 driverwide statistics of actions being performed, such as:

Number of cursors (opened, closed, currently open)

Number of parses, binds, executes, fetches

Number of connections (sessions)

Number of inserts, updates, deletes


Number of commits, rollbacks

The driverwide option is activated through the environment variable MSQLSTAT. Below is a sample output of MSQLSTAT.

<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:

Index optimization, 1 column (that is, single hash)

Key only optimization

Extend refresh time

For more detail on SQL Server performance you can use the Microsoft Performance Monitor utility, which tracks I/O activity, system utilization, and process information. Refer to the Microsoft documentation for more information.

ODBC tracing in the SQL Server 7.0 driver

In Microsoft SQL Server 7.0, performance can be traced using ODBC tracing tools. ODBC tracing can be set from within Baan IV by using the MSQL_ODBC_PROF_ALL, MSQL_ODBC_PERF_STAT, MSQL_ODBC_LONG_QUERIES, and MSQL_ODBC_QUERY_TIMEOUT environment variables. These environment variables are described in Appendix C.

ODBC tracing can also be set from outside the Baan IV database driver. To set ODBC tracing externally, do the following:

1 Turn on ODBC tracing with the ODBC configuration utility in the control panel on the application server where the Baan database driver is running.

2 Run the specific Baan application that must be monitored.

While running the application, all ODBC transactions are traced and stored in the file specified in the Windows NT Control Panel ODBC application. The trace file generated contains information about all SQL statements processed.

The following table describes the log file that is generated when ODBC tracing is turned on.

Log file field descriptions for Microsoft SQL Server 7.0

Field name Field description

TimerResolution Minimum resolution of the server’s clock time in milliseconds. This is usually reported as zero and must only be considered if the number reported is large. If the minimum resolution of the server clock is larger than the likely interval for some of the timer-based statistics, those statistics could be inflated.


SQLidu Number of INSERT, DELETE, or UPDATE statements since SQL_PERF_START.

SQLiduRows Number of rows inserted, deleted, or updated since SQL_PERF_START.

SQLSelects Number of SELECT statements processed since SQL_PERF_START.

SQLSelectRows Number of rows selected since SQL_PERF_START.

Transactions Number of user transactions since SQL_PERF_START, including rollbacks. When an ODBC application is running with SQL_AUTOCOMMIT_ON, each command is considered a transaction.

SQLPrepares Number of SQLPrepares since SQL_PERF_START.

ExecDirects Number of SQLExecDirects since SQL_PERF_START.

SQLExecutes Number of SQLExecutes since SQL_PERF_START.

CursorOpens Number of times the driver has opened a server cursor since SQL_PERF_START.

CursorSize Number of rows in the result sets opened by cursors since SQL_PERF_START.

CursorUsed Number of rows actually retrieved through the driver from cursors since SQL_PERF_START.



PercentCursorUsed PercentCursorUsed=(CursorUsed/CursorSize)*100. For example, if an application causes the driver to open a server cursor to do “SELECT COUNT(*) FROM authors,” 23 rows will be in the result set for the SELECT statement. If the application then only fetches three of these rows, CursorUsed/CursorSize is 3/23, so PercentCursorUsed is 13.043478.


AvgFetchTime AvgFetchTime = SQLFetchTime/SQLFetchCount.

AvgCursorSize AvgCursorSize = CursorSize/CursorOpens

AvgCursorUsed AvgCursorUsed = CursorUsed/CursorOpens

SQLFetchTime Cumulative amount of time it took fetches against server cursors to complete.

SQLFetchCount Number of fetches done against server cursors since SQL_PERF_START.

CurrentStmtCount Number of statement handles currently open on all connections open in the driver.

MaxOpenStmt Maximum number of concurrently opened statement handles since SQL_PERF_START.

SumOpenStmt Number of statement handles that have been opened since SQL_PERF_START.

CurrentConnectionCount Current number of active connection handles the application has open to the server.

MaxConnectionsOpened Maximum number of concurrent connection handles opened since SQL_PERF_START.

SumConnectionsOpened Sum of the number of connection handles that have been opened since SQL_PERF_START.

SumConnectionTime Sum of the amount of time that all of the connections have been opened since SQL_PERF_START. For example, if an application opened 10 connections and maintained each connection for 5 seconds, then SumConnectionTime would be 50 seconds.

AvgTimeOpened AvgTimeOpened = SumConnectionsOpened/SumConnectionTime.

ServerRndTrips The number of times the driver sent commands to the server and received a reply back.

BuffersSent Number of TDS packets sent to Microsoft SQL Server by the driver since SQL_PERF_START. Large commands can take multiple buffers, so if a large command is sent to the server and it fills six packets, ServerRndTrips is incremented by one and BuffersSent is incremented by six.




BuffersRec Number of TDS packets received by the driver from SQL Server since the application started using the driver.

BytesSent Number of bytes of data sent to SQL Server in TDS packets since the application started using the driver.

BytesRec Number of bytes of data in TDS packets received by the driver from SQL Server since the application started using the driver.

MsExecutionTime Cumulative amount of time the driver spent processing since SQL_PERF_START, including the time it spent waiting for replies from the server.

MsNetworkServerTime Cumulative amount of time the driver spent waiting for replies from the server.

Windows NT tuning

In general, configuration and tuning efforts for Windows NT are best initially focused on disk and file system configuration and the virtual memory subsystem (physical memory and paging). When creating paging space and installing products (including the operating system), an effort must be made to distribute potential disk activity as uniformly as possible over the available hardware.

As a result of the potentially high memory demands of database applications, it is useful to monitor memory utilization in NT to minimize paging activity. When the contents of memory are swapped to disk because of high memory demands and that memory is again needed, the application using that memory space must wait for it to be retrieved from disk before it can execute. This can be an expensive operation.

As the memory demand grows, the system can begin thrashing, because memory used by an application is constantly swapped to disk to free physical memory and then returned in again so that the application can execute. The operating system spends more time managing the physical memory demand and has less opportunity to run applications.


The simplest solution is to add more physical memory to the machine. However, this may not always be necessary. When memory demands reach the limit of physical memory in the system, it becomes more critical to monitor memory utilization to make certain that it is allocated in the best possible way. For example, it may be possible to reallocate SQL Server’s memory to the operating system if it is not being completely utilized by the database server.

For a thorough treatment of Windows NT configuration and tuning issues consult one or more of the numerous resources dedicated to the subject. There are an endless number of articles available on the Web and dozens of books that treat this subject in a much more detailed fashion.

B Appendix B Setting database driver behavior

There are several facilities available to configure the Baan IV MSQL database driver. The most common is through driver resources. Two other facilities for configuring the Baan IV MSQL database driver are environment variables and the storage file. The driver resources and environment variables are described in more detail in Appendix C and the storage file in Appendix D.

Driver resources

The database driver resources are parameters that can be set to modify the behavior of the Baan IV MSQL database driver. These parameters are set in a file called the resource file (db_resource). There is one resource file for all database drivers that run in a Baan IV environment; resources for all the database driver types can be found there. A database driver reads the parameters set in the resource file when it is first invoked.

The resource file can contain many entries, with one entry per line. Each entry is used to set a single resource parameter, with the resource name followed by a colon and then the value to which the resource is to be set. The following is an example of the contents of a resource file that contains two entries:

dbsinit:01 msql_stmt_timeout:90

When modifying the behavior of the database driver, it is often necessary to modify the behavior of the Baan IV application Virtual Machine to take advantage of the characteristics of the database driver. Therefore, there are two types of database driver resources: those that are used to modify the behavior of the database driver and those that are used to modify the behavior of the application Virtual Machine. Driver resources that are used to modify database driver behavior are called resources for the server. Driver

B-2 | Setting database driver behavior

resources that are used to modify behavior in the application Virtual Machine are called resources for the client.

In a Windows NT environment, the resource file, db_resource, is located in the directory %BSE%\lib\defaults, where %BSE% refers to the directory where the Baan IV software environment is installed. If both the database driver and the application Virtual Machine run on the same machine, there will be only one db_resource file containing all the necessary resources parameters. If the database driver and the application Virtual Machine run on different machines, there must be one db_resource file located on the machine running the database driver that contains the server resources, and one db_resource file located on the machine running the application Virtual Machine that contains the client resources.

In addition to the default resource file, db_resource, you can set up an alternative resource file to override resource values for specific users or groups of users. The alternative resource file is specified with the USR_DBS_RES environment variables and USR_DBC_RES. USR_DBS_RES is used to specify the path to a file containing an alternative resource file for the server and must be set on the machine that runs the database driver. USR_DBC_RES is used to specify the path to a file containing an alternative resource file for the client and must be set on the machine that runs the application Virtual Machine. Any driver resource set in the alternative resource file will override the setting of the same driver resource in db_resource. The next section describes how to set the database driver environment variables.

Environment variables

Environment variables can be used to override driver resources. Usually, a default set of resource parameters is configured in the resource file. The administrator can override these default settings with environment variables.

For the most part, there is an environment variable corresponding to each resource parameter. The environment variable name is usually the uppercase equivalent of the resource parameter name. As with the database driver resources, some environment variables are used to modify the behavior of the database driver (server) and some are used to modify the behavior of the application Virtual Machine (client). If a database driver environment variable for the server is to be used, it must be set on the machine running the database driver to override the corresponding driver resource. If a database driver environment variable for the client is to be used, it must be set on the machine running the application Virtual Machine to override the corresponding driver resource.

Setting database driver behavior | B-3

Server environment variables

Environment variables that affect the database driver can be used to override the driver resources for all tables in a database or for specific tables and company numbers within the database. There are three ways to set the database driver server environment variables:

By using the Maintain Database Definitions (ttaad4100m000) session

By using the standard operating system mechanism for setting environment variables

By manually modifying the tabledef6.1 file

Note

If you manually modify the tabledef6.1 file, your changes can be overwritten when a Convert to Run Time is performed in the application.

Infor recommends that you use the Maintain Database Definitions session to modify database driver behavior. This session causes environment variables for a particular database driver to override the defaults defined in the resource file and allows the environment variables to be maintained centrally.

The Maintain Database Definitions session maintains database driver configuration information in a file called tabledef6.1. This file is stored in the %BSE%\lib directory residing on the machine where the database driver runs. While it is recommended that the Maintain Database Definitions session be used to maintain this file, advanced users can modify this file manually. The format of the tabledef6.1 file is as follows:

<table name>:<company number>:<driver type>(<environment variable>=<value>)

If multiple environment variables are to be specified for a single table and company number, they are listed within the parentheses and separated by commas. If all tables or all companies are to be specified, the asterisk (*) is used in place of table name or company number. For example, the following entry can be made in the tabledef6.1 file:

tccom010:812:msql(MSQLPROF=0.4)

In this example all the queries on table tccom010812 that require at least 0.4 seconds are logged in the MSQLPROF file. Note that this table is considered to have a different database definition from other tables. If an MSQL driver is already running, but is accessing a different table, a separate driver is started for this table. Environment variables that appear in the driver specifications of the tabledef6.1 file are put into the driver’s environment before it is invoked, so they are available to the driver at startup.

If the default database driver resources must be modified for specific users, the standard operating system method can be used to set database driver

B-4 | Setting database driver behavior

environment variables for specific users. These environment variables override the settings created in the Maintain Database Definitions session for these users.

Client environment variables

Database driver environment variables that affect the client can be used to override the client resources that affect the application Virtual Machine. These environment variables must be set on the machine running the application Virtual Machine and must be set using the standard operating system methods used for setting environment variables. Any client environment variables that are used override the equivalent resource variables set for the client in the db_resource file.

Storage file

The storage file provides a way to specify the distribution of table and index data in different segments. Storage parameters are used by the database driver whenever a DDL statement such as a create table or create index statement is carried out. The following is an example of an entry in the storage file:

SEGMENT segment1

In this example, the database driver adds the“on <segment name> clause to the create statement during index or table creation.

A storage file is defined for each database driver. The storage file for the Baan IV MSQL database driver is called msql_storage and is located in the %BSE%\lib\msql directory. The format of the storage file is described in detail in Appendix D.

C Appendix C Environment variables and resources

This appendix lists all the database driver resources and environment variables that can be used as configuration parameters to modify the behavior of the MSQL database driver. Some of these resources are used with the client and others with the server. In this context, the client is the Baan IV application Virtual Machine (the bshell) and the server is the Baan IV MSQL database driver. If the Baan IV application Virtual Machine and the database driver running on different machines, client resources must be set on the machine that runs the Baan IV application Virtual Machine, and server resources must be set on the machine that runs the database driver. Resources for both the client and the server must be set on both machines.

A description of how to set the database driver resources and environment variables can be found in Appendix B.

This appendix provides the following information:

Summary of MSQL resources and environment variables

Detailed description of MSQL resources and environment variables

Summary of MSQL resources and environment variables

There are six types of resources and environment variables that can be used with the Baan IV MSQL database driver. Note that some resources and environment variables are available only when you use the Baan IV driver for Microsoft SQL Server 6.5, and others are available only when you use the Baan IV driver for Microsoft SQL Server 7.0.

Client and server resources used by all Baan IV database drivers

Client resources used by all Baan IV database drivers

Server resources used by all Baan IV database drivers

C-2 | Environment variables and resources

Resources used only by the Baan IV MSQL database drivers (MSQL 6.5 and MSQL 7.0)

Resources used only by the Baan IV MSQL database driver for MSQL 6.5


The following six tables provide a summary of each of these types of resources and environment variables. Detailed descriptions of each entry in the tables can be found beginning on Page C-4.

Client and server resources used by all Baan IV database drivers

Resource name Environment variable Description

rds_full RDS_FULL Configures maximum number of rows transferred in one block

tt_sql_trace TT_SQL_TRACE Allows viewing of SQL query information

Client resources used by all Baan IV database drivers


bdb_debug BDB_DEBUG Sets debugging link between client and server

bdb_driver BDB_DRIVER Sets database specifications

ssts_set_rows SSTS_SET_ROWS Configures number of rows read ahead (single table single row)

USR_DBC_RES Specifies alternative resource file for client

Server resources used by all Baan IV database drivers


bdb_max_session_ schedule

BDB_MAX_SESSION_SCHEDULE

Defines mechanism for closing idle driver sessions

dbslog DBSLOG Allows driver profiling

dbsinit Specifies optimistic or pessimistic reference checking

lock_retry LOCK_RETRY Defines the number of lock retries and the sleep period between retries

USR_DBS_RES Specifies alternative resource file for server

Environment variables and resources | C-3

Resources used only by the Baan IV MSQL database drivers (MSQL 6.5 and MSQL 7.0)


MSQL_ARRAY_FETCH Enables array fetching

MSQL_ARRAY_ INSERT

Defines maximum number of rows for array inserts

msql_dsn MSQL_DSN Specifies data source to communicate with MSQL

msql_max_arrsz MSQL_MAX_ARRSZ Adjusts buffer size for array fetches

msql_max_conn MSQL_MAX_CONN Configures maximum number of connections to MSQL

msql_max_open_ handles

MSQL_MAX_OPEN_ HANDLES

Configures number of open cursors per driver connection

msql_max_ret_ rows

MSQL_MAX_RET_ ROWS

Configures maximum number of rows returned from MSQL

MSQLPROF Allows profiling

msql_serverhost MSQL_SERVERHOST Specifies host name for MSQL instance

MSQLSTAT Allows statistics to be gathered

msql_up_on MSQL_UP_ON Configures driver to use procedures for prepare

msql_use_cc MSQL_USE_CC Configures driver to use client cursors

msql_use_sp MSQL_USE_SP Configures driver to use stored procedures



msql_ddl_timeout MSQL_DDL_TIMEOUT Configures query timeout value for DDL statements

msql_ref_timeout MSQL_REF_TIMEOUT Configures query timeout value for referential integrity checks

msql_stmt_timeout MSQL_STMT_ TIMEOUT

Configures query timeout value for row-level queries

msql_tbl_timeout MSQL_TBL_TIMEOUT Configures query timeout value for table-wide queries such as CLEAR TABLE




msql_65_schema MSQL_65_SCHEMA Configures a 7.0 driver to use the 6.5 schema limits (specifically max_char = 254)

msql_lock_timeout MSQL_LOCK_ TIMEOUT

Configures number of seconds MSQL waits for a locked resource before setting lock timeout

MSQL_ODBC_LONG_ QUERIES

Determines whether long ODBC queries are logged

MSQL_ODBC_PERF_ STAT

Determines whether ODBC statistics are logged

MSQL_ODBC_PROF_ ALL

Determines whether ODBC profiling is logged

MSQL_ODBC_QUERY_TIMEOUT

Configures time a query can take for processing before it is considered a long query

msql_query_ timeout

MSQL_QUERY_ TIMEOUT

Configures number of seconds the ODBC Driver manager must wait for a query before generating a query timeout

msql_use_ffo MSQL_USE_FFO Configures driver to use the fast forward only cursor type

Detailed description of MSQL resources and environment variables

This section provides detailed information about the Baan IV MSQL driver resources and environment variables. The driver resources are divided into two sections: those that are generic to all Baan IV database drivers and those that are specific to the Baan IV MSQL driver. Each group of resources is listed in alphabetical order.

The driver resources and environment variables specific to the Baan IV MSQL driver are listed in one group, whether the resources and environment variables are used for the driver for MSQL 6.5 or the driver for MSQL 7.0. Each resource and environment variable includes an indication of which MSQL driver it can be used with.


Generic driver resources

bdb_debug / BDB_DEBUG

Driver resource bdb_debug

Environment variable BDB_DEBUG

Client/server resource Set for client only

Type Integer (octal)

Default 0

Description This variable is used to generate debugging information about the communication between the client and the database driver. When set, the client prints debugging information to standard error (stderr). The following categories of debugging information can be specified:

00001 server types

00002 database actions

00004 delayed lock actions

00010 reference information

00040 TSS info from %BSE%\lib\tss_mbstore

00100 permission information

Multiple categories can be defined by adding the octal values. The value is compared bitwise to determine if a given category must be logged.

bdb_driver / BDB_DRIVER

Driver resource bdb_driver

Environment variable BDB_DRIVER


Type String

Default None

Description This variable is used to set a database specification, usually found in the tabledef6.1 file. When this variable is set, all tables are accessed by using the database driver specified and tabledef6.1 is not read. The driver specified must be defined in the %BSE%\lib\ipc_info file.


bdb_max_session_schedule / BDB_MAX_SESSION_SCHEDULE

Driver resource bdb_max_session_schedule

Environment variable BDB_MAX_SESSION_SCHEDULE

Client/server resource Set for server only

Type Integer

Default 3

Description This variable defines the mechanism for 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 an idle session is done after a number of schedule ticks. A schedule tick is generated whenever a Baan IV session is ended. At this point, all idle sessions have a schedule counter incremented. When the value of the schedule counter reaches the value of bdb_max_session_schedule, the session is closed.

The default for bdb_max_session_schedule is three. Setting bdb_max_session_schedule to one would result in fewer connections from the driver to the RDBMS since whenever a Baan IV session is ended, the corresponding RDBMS session (logon) is closed (logoff).

dbsinit

Driver resource dbsinit

Environment variable —



Default 0

Description This variable allows flags to be set to specify the optimizations to be used. At this time, legal values are 000 (not set) and 001. Other values are reserved and must not be used.

A flag of 00001 specifies that an optimistic approach must be used when checking for references in parent tables. The referenced row in the parent table is not locked, improving the overall concurrency. If this flag is not set, optimistic reference checking is not used.


dbslog / DBSLOG

Driver resource dbslog

Environment variable DBSLOG



Default 0

Description This variable provides detailed debugging information about the online processing of the driver. The information is logged in the dbs.log file in the driver’s current directory. The following debugging categories can be specified:

0000001 Data Dictionary information of tables in the driver

0000002 Query info (SQL Level 1)

0000004 Query plan info (SQL Level 2)

0000010 Row action information

0000020 Table action information

0000040 Transaction action information

0000100 DBMS input/output data (SQL Level 2)

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)

0100000 Lock retries logged (includes session name)

0200000 Logs successful locks and longest lock duration in a transaction

Multiple categories can be defined by adding the octal values. The value is compared bitwise to determine if a given category must be logged.


lock_retry / LOCK_RETRY

Driver resource lock_retry

Environment variable LOCK_RETRY


Type String

Default 5*100,5*500

Description This variable defines the high-level locking behavior that is used when an action is locked. It defines the number of retries and the length of the sleep periods between retries in milliseconds (MS). For example, for the default setting, the action is retried five times with a sleep period after each try of 100 MS. The action is then retried five more times with a sleep period of 500 MS after each retry.

rds_full / RDS_FULL

Driver resource rds_full

Environment variable RDS_FULL

Client/server resource Set for both client and server

Type Integer

Default 5

Description This variable defines the maximum number of rows transferred between the Baan IV application Virtual Machine and the driver as one block. Multiple blocks (and thus network round trips) are transferred if more rows are requested. This variable must be set to the same value for both client and server.


ssts_set_rows / SSTS_SET_ROWS

Driver resource ssts_set_rows

Environment variable SSTS_SET_ROWS


Type Integer

Default 3

Description This variable defines the number of rows to be read ahead for a fetch request from the client. The default is three rows, which means that for one fetch request, three rows are read. For the following two fetch requests, rows are taken from the client row buffer or fetched from the database without reexecuting the query.

tt_sql_trace / TT_SQL_TRACE

Driver resource tt_sql_trace

Environment variable TT_SQL_TRACE

Client/server resource Set for both client and server


Default 0

Description 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 the display; the server prints information only if the dbslog variable allows it. The information contains different categories that 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 descriptions 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

010000 (s) Show query evaluation plan

020000 (s) Show FullTableScan


USR_DBC_RES

Driver resource

Environment variable USR_DBC_RES


Type String

Default None

Description This variable contains the file specification of an alternative resource file for the client. The file specification is based on the BSE directory and is within double quotes. When set, any resources in the alternative resource file override the same client resources set in db_resource.

USR_DBS_RES

Driver resource

Environment variable USR_DBS_RES


Type String

Default None

Description This variable contains the file specification of an alternative resource file for the client. The file specification is based on the BSE directory and is within double quotes. When set, any resources in the alternative resource file override the same server resources set in db_resource.


MSQL driver specific resources

msql_65_schema / MSQL_65_SCHEMA

Driver resource msql_65_schema

Environment variable MSQL_65_SCHEMA

MSQL version 7.0 only


Type Boolean

Default 0 (disabled)

Description Configures the 7.0 driver to use the 6.5 schema limits. When using the SQL Server upgrade wizard to convert a 6.5 database to a 7.0 database, the upgrade wizard maintains the 254 character maximum column size used in 6.5. Enabling this resource configures the Baan IV driver for MSQL 7.0 to use the 6.5 254 character max column-size limit.

This resource is only available with porting set 6.1c.03.01 or later.

MSQL_ARRAY_FETCH

Driver resource —

Environment variable MSQL_ARRAY_FETCH

MSQL version 6.5 and 7.0


Type Boolean


Description This environment variable is used to enable or disable the array fetch interface. The valid values are 0 and 1. When set to 0, the fetch interface is disabled. When set to 1, it is enabled.


MSQL_ARRAY_INSERT

Driver resource —

Environment variable MSQL_ARRAY_INSERT



Type Integer

Default Not set

Description If the array interface is enabled, this environment variable defines the maximum number of rows inserted at once in SQL Server. Note that this option cannot always be enabled. For example, if references must be checked or updated, or the application requires immediate response from the driver as to whether the insert is successful, no array insert can be done.

msql_ddl_timeout / MSQL_DDL_TIMEOUT

Driver resource msql_ddl_timeout

Environment variable MSQL_DDL_TIMEOUT



Type Integer

Default 240

Description Determines the timeout value, in seconds, for DDL (create object) statements.


msql_dsn / MSQL_DSN

Driver resource msql_dsn

Environment variable MSQL_DSN


Client/Server resource Set for server only

Type String

Default None

Description Allows specification of a data source name to be used by the driver for communication with SQL Server.

msql_lock_timeout / MSQL_LOCK_TIMEOUT

Driver resource msql_lock_timeout

Environment variable MSQL_LOCK_TIMEOUT



Type Integer

Default 10

Description Sets the number of seconds Microsoft SQL Server must wait for a locked resource before returning a lock timeout.

msql_max_arrsz / MSQL_MAX_ARRAY_SIZE

Driver resource msql_max_arrsz

Environment variable MSQL_MAX_ARRAY_SIZE



Type Integer

Default 1

Description If the array interface is enabled, this variable defines the maximum number of rows fetched at once from the RDBMS.


msql_max_conn / MSQL_MAX_CONNECTIONS

Driver resource msql_max_conn

Environment variable MSQL_MAX_CONNECTIONS



Type Integer

Default 0

Description 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.

msql_max_open_handles / MSQL_MAX_OPEN_HANDLES

Driver resource msql_max_open_handles

Environment variable MSQL_MAX_OPEN_HANDLES



Type Integer

Default 200

Description Limits the number of open cursors the driver maintains on a per connection basis. Each cursor represents one type of SQL statement.

msql_max_ret_rows / MSQL_MAX_RET_ROWS

Driver resource msql_max_ret_rows

Environment variable MSQL_MAX_RET_ROWS



Type Integer

Default 0

Description This variable specifies the maximum number of rows to be returned to the driver by the MSQL engine. The default is 0, which implies that all rows are returned.


MSQL_ODBC_LONG_QUERIES

Driver resource —

Environment variable MSQL_ODBC_LONG_QUERIES



Type Boolean


Description When set to 1, ODBC profiling is turned on, but only long-running queries are logged. The value of MSQL_ODBC_QUERY_TIMEOUT determines how long a query can run before it is considered a long-running query. The log file is a tab delimited file that can easily be analyzed with a spreadsheet such as Microsoft Excel. The fields of the log file are described in the table on Page A-18.

MSQL_ODBC_PERF_STAT

Driver resource —

Environment variable MSQL_ODBC_PERF_STAT



Type Boolean


Description When set to 1, ODBC profiling is turned on, but only performance statistics are logged. The log file is a tab delimited file that can easily be analyzed with a spreadsheet such as Microsoft Excel. The fields of the log file are described in the table on Page A-18.


MSQL_ODBC_PROF_ALL

Driver resource —

Environment variable MSQL_ODBC_PROF_ALL



Type Boolean


Description When set to 1, ODBC profiling is turned on. When ODBC profiling is on, performance statistics and long-running queries are logged. The log file is a tab delimited file that can easily be analyzed with a spreadsheet such as Microsoft Excel. The fields of the log file are described in the table on Page A-18.

MSQL_ODBC_QUERY_TIMEOUT

Driver resource —

Environment variable MSQL_ODBC_QUERY_TIMEOUT



Type Integer

Default 3000

Description This variable sets the number of milliseconds that are required to process a query before the query is considered a long-running query.


MSQLPROF

Driver resource —

Environment variable MSQLPROF



Type Floating point

Default Not set

Description If a value is specified in this variable, any statement that takes more than the number of seconds specified is logged. The maximum precision that can be specified is 0.01 seconds. This variable is used to determine which table actions are the most time consuming.

msql_query_timeout / MSQL_QUERY_TIMEOUT

Driver resource msql_query_timeout

Environment variable MSQL_QUERY_TIMEOUT



Type Integer

Default 0 [zero] (no query timeout)

Description Configures the number of seconds the ODBC driver manager must wait for a query to complete before returning a query timeout condition. By default there is no query timeout.


msql_ref_timeout / MSQL_REF_TIMEOUT

Driver resource msql_ref_timeout

Environment variable MSQL_REF_TIMEOUT



Type Integer

Default 10

Description Determines the timeout value, in seconds, for referential integrity checks.

msql_serverhost / MSQL_SERVERHOST

Driver resource msql_serverhost

Environment variable MSQL_SERVERHOST



Type String

Default None

Description Allows specification of a host name for the driver to locate the SQL Server instance to be used.

MSQLSTAT

Driver resource —

Environment variable MSQLSTAT



Type Integer

Default Not set

Description This variable allows database driver statistics to be reported. If it is set to a value n greater than 0, statistics are logged every n seconds while the driver is active. If it is set to 0, a statistics report is generated when the driver terminates.


msql_stmt_timeout / MSQL_STMT_TIMEOUT

Driver resource msql_stmt_timeout

Environment variable MSQL_STMT_TIMEOUT



Type Integer

Default 10

Description Determines the timeout value, in seconds, for row-level queries (for example, insert row, update row, delete row, select).

msql_tbl_timeout / MSQL_TBL_TIMEOUT

Driver resource msql_tbl_timeout

Environment variable MSQL_TBL_TIMEOUT



Type Integer

Default 50 (5 * msql_stmt_timeout)

Description Determines the timeout value, in seconds, for table-wide queries (for example, clear table, update table).

msql_up_on / MSQL_UP_ON

Driver resource msql_up_on

Environment variable MSQL_UP_ON



Type Boolean (1 is TRUE, 0 is FALSE)

Default Do not use procedures for prepare

Description Setting this to 1 enables SQL Server’s Use Procedures for Prepare option when carrying out queries against SQL Server.


msql_use_cc / MSQL_USE_CC

Driver resource msql_use_cc

Environment variable MSQL_USE_CC



Type Boolean

Default 0 (server cursors)

Description There are two valid values for this variable: 0 and 1. If it is set to 1 the driver uses client cursors (ODBC Type A) when carrying out queries against SQL Server. If it is set to 0, the driver uses server cursors.

msql_use_ffo / MSQL_USE_FFO

Driver resource msql_use_ffo

Environment variable MSQL_USE_FFO



Type Boolean

Default 1 (enabled)

Description When this variable is set to a nonzero value, Microsoft SQL Server uses the fast forward only cursor instead of the default dynamic server cursor.

Note that when this resource is enabled, the driver also performs auto fetch/auto cose to reduce the number of client/server round trips from driver to server.


msql_use_sp / MSQL_USE_SP

Driver resource msql_use_sp

Environment variable MSQL_USE_SP



Type Boolean

Default 1 (enabled)

Description Sets driver to use stored procedures. If enabled, the driver dynamically creates and executes a stored procedure while carrying out SELECT_EQUAL statements on a given index. One procedure is created for each index for each table.

D Appendix D Storage file format and configuration options

This appendix contains sections that discuss the file format of the %BSE%\LIB\MSQL\MSQL_STORAGE file and the driver configuration options specific to the MSQL driver.

Storage file format

The configuration information that identifies various table and index creation options is contained in the %BSE%\LIB\MSQL\MSQL_STORAGE file. This file is referred to as the storage file. Storage parameters are used by the MSQL driver when DDL statements are carried out (like the create table and create index statements).

The format of the storage file is as follows:

[{<user list>}]<table/module specification>:<company number>:<object type>: <private | group>:<table/index optimization>:<refresh time>:<storage parameters>

The different fields are explained below:

User list This field exists of a list of comma-separated user names to which this entry applies. When no list is specified, the entry applies to all users. For example:

{peter,john}

Table/module This field exists of a list of comma-separated table names or a module name to which the entry applies. A star (*) indicates all tables. For example:

ttadv000,ttadv999 both tables ttadv all ttadv tables * all tables

D-2 | Storage file format and configuration options

Company number This field exists of a list of company numbers to which the entry applies. A star (*) indicates all company numbers. For example:

000,505 companies 000 and 505 * all companies

Object This field exists of a list of object (table or index) identifications to which the entry applies. The following options can be specified:

T table only I all indexes I <index number> only specified index * both table and indexes

For example:

I1,I2 only index 1 and 2 T only for table

Private/group This is the identification of the owner of the table. Here, one of the given keywords must be specified.

Table/index optimization Here, some specific flags related to indexes and tables can be specified. The exact values will be explained later (see section 6.1). When specified on a T object entry, it defines the default for all indexes, as well.

Refresh time This specifies the amount of time that a retrieved dataset is considered valid.

Storage parameters These are defined by the specific database driver implementation and often map to table and index creation options available in the host DBMS.

For example:

CLUSTER 1 SEGMENT segment1

or:

cluster 1 segment segment1

Note

The storage file is scanned from the beginning whenever a create table or create index is performed. The first entry that matches the table or index is taken, so the order in which the entries are specified is important.

Storage file format and configuration options | D-3

For example:

{peter,john}ttadv999,ttadv000:000:T:private:011:5:SEGMENT mydatasegment CLUSTER 1 {peter,john}ttadv999,ttadv000:000:I::011:5: SEGMENT myindexsegment tdsfc:505:::011:5: *:*:T:group:011:5:SEGMENT datasegment CLUSTER 1 *:*:I:group:011::SEGMENT indexsegment

In the example above the users peter and john will create tables ttadv999 and ttadv000 of company 000 in segment mysegment. The associated indexes will be created in segment myindexsegment and index 1 will be a clustered index. All users creating tables in module tdsfc will create tables and indexes in the default segment. Other users create tables and indexes in segments datasegment and indexsegment, respectively, with index 1 being a clustered index.

Note

If the segment for a table or index is not specified, the table and index data are created in the default segment. If you want to separate the index data, you must specify a segment.

MSQL storage parameters

There are several driver configuration options specific to the MSQL driver which allow the user to customize table and index attributes at create time. The following parameters are supported:

Note

Each storage option can be specified either in all uppercase or all lowercase characters.

CLUSTER <n> Creates index n as a clustered index. Only one index on the table can be clustered. If this option is not specified for a table, the primary index (index 1) is clustered (by default). To create all indexes nonclustered, specify CLUSTER 0.

FILLFACTOR <n> Includes the fill factor <n> clause in the create index statement during index creation.

NOINDEX Prevents index creation on the table(s).

D-4 | Storage file format and configuration options

SEGMENT <segment name> Includes the segment <segment name> clause in the create table or create index statement during object creation.

Note

The SEGMENT storage option is used only with the Baan MSQL database driver for Microsoft SQL Server 6.5. It is ignored if set for the driver for Microsoft SQL Server 7.0.

ARRAY_SIZE Determines the array size for the array interface.

IRL Sets the insert row lock table option in the SQL Server when the table is created.

Note

The IRL storage option is used only with the Baan MSQL database driver for Microsoft SQL Server 6.5. It is ignored if set for the driver for Microsoft SQL Server 7.0.

ROWLOCK Causes the optimizer hint ROWLOCK to be used for the specified table.

Note The ROWLOCK storage option is only available for the Baan MSQL database driver

for Microsoft SQL Server 7.0.

READPAST Causes the optimizer hint READPAST to be used for the specified table.

Note

The READPAST storage option is only available for the Baan MSQL database driver for Microsoft SQL Server 7.0.

ms sql server database driver internalsbaansupport.com/docs/baan/ms sql server database...

Documents