• Home Page
• Support
• Product Knowledgebase
• DBPronto
• DBPronto Hints and Tips
• DBPronto Hints and Tips

DBPronto - Hints and Tips

SQL Server JDBC Connection Tuning

Connectivity between the DBPronto Server and the RDBMS is handled by a JDBC driver.

If the RDBMS in use is Microsoft SQL Server or Sybase ADS, the database will sometimes be unable to determine the optimum index to use for a prepared SQL statement. This can cause a drop in performance between DBPronto and the RDBMS.

The JDBC driver provided with DBPronto can be configured so that the "raw" SQL statement is passed to SQL Server, which can greatly improve performance. This configuration is achieved within the "connection_url.conf" file, by adding "prepareSQL=" to the connection string. An example is provided below:

connection_url=//MACHINE/DBNAME;domain=DOMAINNAME;
                                user=USERNAME;password=PASSWORD;prepareSQL=0

The table below provides a list of the values that can be placed after "prepareSQL=", and what each option provides (The default value is 3 for SQL Server, 1 for Sybase ADS):

As indicated in the table above, setting "prepareSQL=0" can actually be slower than the default option, as the entire SQL statement is being passed to the database server each time. If setting "prepareSQL=0" does not improve performance, revert to the default value.

Performance Tuning

There are a number of ways in which the performance of DBPronto can be optimised, and the extent to which any item impacts performance will vary from one user to another.

Some of the common performance tuning options available include: 

• DBPronto Server Location
• General RDBMS options
• DBpronto Server configuration
• DBPronto Client configuration

DBPronto Server Location

One of the first decisions that a DBPronto user should make, is how they are going to deploy the product. The  typical architecture of the product is multi-tiered, and there are a number of ways in which the product can be physically deployed. Refer to the FAQ article on product architecture for more information.

A key consideration to make when deciding on a physical deployment, is the dependency on network links between tiers (Client, Server and RDBMS). Where high performance is a requirement, latency between machines must be considered, particularly where larger batch processes are to be run.

It is therefore recommended that where possible, the DBPronto Server and Client reside on the same physical machine. There are also cases where using the same physical machine for the DBPronto server and RDBMS may improve performance, however in many cases a dedicated database server will provide greater performance.

General RDBMS Options

There are a multitude of ways in which specific RDBMS types can be optimised for use with DBPronto, and other articles within My Transoft may offer advice for this purpose.

A general consideration to make with regards to your RDBMS setup, is the way in which your application(s) uses indexing. Because DBPronto does not specify a "Primary" or "Clustered" index, performance gains could potentially be made by determining which index your application(s) use more often, and specify this as the primary index in the database.

In order to determine which index is being utilised more often by an application, database analysis tools, such as the "SQL Server Profiler" utility, will be able to identify such opportunities for tuning the RDBMS.

DBPronto Server Configuration

BLOCK_READ_COUNT

The DBPronto server setting "BLOCK_READ_COUNT" can improve the performance of batch processes, where there is significant use of "START -> READ NEXT" processing.

This setting controls the number of records that are read by the DBPronto server whenever a "START" call is received from the client application. As an example, if BLOCK_READ_COUNT is set to equal 10, the server will read the first 10 records from the database when a "START" call is received, meaning that subsequent "READ NEXT" calls avoid the need to re-access the database (until the 11^th record is required to be read).

The following should be considered when setting the BLOCK_READ_COUNT value:

• If set too high, the DBPronto server is likely to read records from the database that it does not need, and will then discard them without use when a call other than "READ NEXT" is made;
• If set too low, the DBPronto server will need to access the RDBMS more often than required.

The best way to find the optimum setting for BLOCK_READ_COUNT is to start with a value of 10, test performance for a particular process, and then set the count higher or lower as required, and repeat the test.

HandlerCount

A second server configuration that can have a large impact on performance, is the number of "Handler Threads" that the DBPronto server will use when processing client requests, and communicating with the RDBMS.

By default, the HandlerCount value is set to 3. This can be increased, particularly if there are a high number of client users and performance is slower than required.

As with BLOCK_READ_COUNT, there are some additional considerations for this setting:

• If set too high, performance may decrease due to the level of data synchronisation required;
• If set too low, a "bottleneck" effect may manifest itself;
• Some RDBMS types are licensed on a per-user basis. As such, optimising the number of handler threads can be a trade-off between performance and cost;
• In some instances, RDBMS concurrent connectivity will not be unlimited, so setting handlerCount too high may cause connection issues.

DBPronto Client Configuration

Where Block Read is already in use on the DBPronto Server, the client-side setting "ClientBlockReadMode=Y" can be used to send multiple records to the client application at once.

The multiple records are cached by the DBPronto server’s "Block Read Mode", and can improve performance by supplying the client application with multiple records to process at once, without having to repeatedly call on the server for the next record.

Additional information on performance tuning can be found in the DBPronto user documentation, available from the DBPronto Downloads page. For further information, please contact Transoft Customer Services.

Configuring DBPronto for use with RM/Cobol

Where DBPronto is being used with an RM/Cobol source, error messages will persist where the directive "WORD_STORAGE_MODE" is set to "ON" within the server configuration file.

WORD_STORAGE_MODE is used to control a DBPronto algorithm which determines the size of COMP-4 and COMP-5 items based on the COBOL PICTURE. When this setting is set to "OFF", binary data items will occupy the minimum number of bytes required.

Date Support

Server Configuration

The DBPronto server configuration allows users to specify default data formats and values for date fields. The following config.conf directives apply to date mapping:

• DEFAULT_DATE_FORMAT – this controls how dates will be formatted. The default value is "YYYYMMDD";
• DEFAULT_DATE – this setting will apply a date value to a data item of type "date" where no date (or an invalid date) has been specified from the COBOL program. By default, this is set to "19000101";
• DEFAULT_AMEND_DATES – this controls the cleansing of invalid dates, when received from the COBOL program. This substitutes the invalid date with the specified amended date, and commits this value to the database.
  
  For example: DEFAULT_AMEND_DATES = 99999999:19000101
  
  In the above example, if the invalid date "99999999" is received from the client, it is converted into "19000101", and inserted into the database.

Metadata Configuration

The DBPronto Metadata Manager can be used to configure date formats and types, by setting the attributes of the <field…> tag.

To identify a field to be mapped to a data format, the "field_type" attribute can be set to "DATE". Setting this attribute to "DATE" informs DBPronto to create RDBMS columns of type "date" or "datetime", and convert values to and from dates as required. For example:

<field name="FLD" sqlname="FLD" picture="X(8)" field_type="DATE" />

The date_format attribute can be specified to override the DEFAULT_DATE_FORMAT server setting for a particular field. Similarly, default_date and amend_date attributes can also be set to override the server’s "DEFAULT_DATE" and "DEFAULT_AMEND_DATES" setting’s respectively. A full example is show below:

<field name="DATEFLD" sqlname="DATEFLD" picture="X(8)"
               field_type="date" date_format="YYYYMMDD" default_date="19991231">
        <amend_date>99999999=29991231</amend_date>
</field>