Tutorials and Best Practice Recommendations

You can control the behavior of CitOESQL by setting precompiler directives. This can be done in a variety of ways, on the command line, in source code and in directive files. The order in which sources of directives are used, and hence the reverse order of precedence if a directive is set more than once is:

1. The command line
2. Automatic directive files
3. Source code

Directive files can be specified explicitly with the USE directive, however some directive files, if they exist, are used automatically by CitOESQL. These are recommended as the most convenient way of managing directive use. The automatic directive files, and the order in which they are applied are:

• %COBOLITDIR%\etc\citoesql.dir (Windows) or $COBOLITDIR/etc/citoesql.dir (Linux and Unix).

  This is useful for setting directives that will be applied to all programs.

• citoesql.dir in the directory containing the COBOL source code.

  This is useful for setting directives that are common to groups of programs.

• A file with the same name and in the same directory as the program being pre-compiled but with a file extension of ‘.dir’.

Performance Tuning

Introduction

Reducing the number of interactions required between components to perform a given task will often improve application performance. In a SQL application, the most significant of these is the number of calls made between the client application and the database management system, often referred to as ‘round trips’. In some cases, the database client library or ODBC driver will perform such optimizations transparently on behalf of the client application. In other cases, a client application can use optimization techniques explicitly.

CitOESQL provides several ways to optimize the performance of SQL applications and some of these are demonstrated in the tune.cbl sample application.

A prerequisite for running this application is an ODBC data source, see the Getting Started section for more details.

• On Windows open a COBOL-IT command line window, on Linux or Unix run the COBOL-IT command line setup script.

• Change directory to the CobolIt (or CobolIT64) samples\sql or samples/sql directory

• Build the application with the following command line:

  cobc -g –x -conf=citoesql.conf tune.cbl timer.cbl

• Run the application with the following command line:

  tune (Windows) or ./tune (Linux and Unix)

• Respond to the prompts for Data Source, User-ID and Password.

You will see elapsed times for;

• Inserting rows via a single row insert statement and an array insert statement.

• Selecting and fetching rows using read only and updatable cursors or SELECT INTO statements using single row or multi-row retrieval.

Transparent Cursor Prefetch

When using single row cursor FETCH statements, CitOESQL can prefetch rows. By default, it will use a prefetch of 8 rows for read only cursors and 4 rows for updatable cursors. If you edit tune.dir you will see the prefetch settings at their default values. You can experiment with them as follows:

• Try setting them to 1 to see how much default prefetching improves performance.

• Try commenting them out with a # at the start of the line to confirm their default values.

• Try using larger values. You may find that very large values perform less well than smaller values in some cases and that for a given application there is often a “sweet spot” that performs best.

Host Variable Arrays

CitOESQL supports the use of host variable arrays to insert and fetch multiple rows at a time. When using array host variables you can use a FOR :count clause if you do not want to use the whole array. Edit tune.cbl and familiarize yourself with the syntax. You can change the 78-level constants demoRows, insertArraySize, readOnlyarraySize and forUpdatearraySize to change the number of rows in the table and the size of the host variable arrays. You can experiment with different values to see how different array sizes impact performance and how the BEHAVIOR directive and its subdirectives perform relative to host variable arrays.

It is often best to use a smaller array size for updatable cursors than for read only cursors. Although a larger array size will generally improve performance, it can also increase contention and lock wait delays. When considering array sizes and prefetch sizes for updatable cursors, you should consider if your databases hold FOR UPDATE locks only when a row is available on the client, for example in Microsoft SQL Server, or if FOR UPDATE locks are held until the current transaction terminates, for example in Oracle and PostgreSQL.

SQL Statement Cache

CitOESQL maintains a cache of prepared statements. The default cache size is 20. You can use the STMTCACHE directive to change this. For a large batch, application values up to the low hundreds may be beneficial.

SQL Syntax Checking Options

CitOESQL syntax checking is designed to be lightweight and tolerant of server-specific SQL extensions. This means that it may not detect all SQL errors. You can enable more rigorous checking with the CHECK directive. This also requires the DB directive and, in most cases, the PASS directive. You can see how this works with the tune.cbl sample:

• Navigate to %COBOLITDIR%\Samples\sql or $COBOLITDIR/Sample/sql and open tune.dir in an editor of your choice.

• Remove the # character from the start of the line that starts #db and replace the string <your ODBC Data Source Name> with the name of your ODBC data source.

• Unless you are using operating system authentication, remove the # character from the line that starts #pass and replace the string <UserID> with your database User-ID, and the string <Password> with your database password.

• Remove the # character from the line that starts #check.

• Save the file, and open tune.cbl.

• Search for the first INSERT INTO statement and change the keyword into to intoo.

• Compile tune.cbl and notice the syntax-based error message.

• Change the intoo back to into, the table name oesqldemo to xyz and re-compile. Note the error message is now for a reference to a non-existent table.

Sometimes you cannot avoid errors because a table does not exist in the database, for example if the table is a temporary table that the database automatically drops at disconnect time. CitOESQL can work around this by executing DDL statements at precompile time.

• Navigate to the start of tune.cbl and search for create table.

• Note that the EXEC SQL statement starts with the prefix ‘[also check]’. This tells the precompiler that this statement should be executed at both precompile and execute time.

• The prefix ‘[only check]’ instructs the precompiler that the statement should only be executed at precompile time.

• Go back to the top of the program and search for ‘drop table’. Note the prefix ‘[also check ignore error]’. This instructs the precompiler to execute the statement at both precompile time and execute time and to ignore any precompile time errors.

There may be other cases where a precompile time server-detected error is unavoidable, in which case the prefix [nocheck] can be used.

• Return to the start of tune.cbl and search for ‘set :dbms’. Note that immediately after opening a database connection, tune.cbl uses a set :<hostVariable = current database CitOESQL statement to determine the type of database it is connected to.

• Search for if dbms = and then scroll down a few lines until you can see two create table statements. tune.cbl includes two create table statements to enable it to use the TYPE VARCHAR with most databases. VARCHAR2 is used with Oracle and exploits the create or replace syntax to avoid separate create and drop statements.

• The create table statements have the prefix [nocheck] [also check ignore error]. This has the following effect:

  • [nocheck] means there will be no syntax check at precompile time.

  • [also check ignore error] means the statement will be executed at compile time and potentially also at runtime, however in tune.cbl COBOL code ensures only one of the two statements will be executed at runtime. Any precompile time execution error will be ignored.

• Note that this is a somewhat contrived example for demonstration purposes.

When migrating an application to a new database, you can use the IGNORESCHEMAERRORS directive in conjunction with the CHECK directive. This limits server syntax checking to syntax alone and does not treat missing tables and columns as errors. This may be useful in obtaining a quick appraisal of SQL syntax differences that need to be remediated before the schema has been migrated.