Progress
DataServer
for ODBC Guide
Using ODBC and DataServer Options
This section provides information on using various ODBC and DataServer options.
DataServer Connection Options
Some data sources (for example, Sybase) support transactions with a degree of granularity that might cause error conditions when executing complex transactions. For example, a transaction might be rolled back unsuccessfully.
Avoid using the data source’s transaction manager in such cases. Instead, specify -Dsrv AUTOCOMMIT,1 to use the Progress client’s local before-image mechanism to connect to the data source. This startup parameter creates a transaction from each SQL statement that the DataServer issues. Progress emulates the logical transaction on behalf of the application using the local before-image mechanism. This connection technique is useful in multi-database applications.
NOTE: Progress does not guarantee crash recovery when it uses the local before-image mechanism to emulate transactions.Query Result Order: The PRGRS_ALWAYS_INDEX Option
Some queries can gain a performance advantage when the result set from the query does not use an index to order the results. In older versions of the ODBC DataServer, results sets were always indexed because queries and their results were gathered in segments with new cursors being generated for each query segment. However, most advanced drivers and data sources now allow a single cursor to be retained for the life of a given query, even across a transaction boundary. Drivers that can preserve cursors are allowed to execute unindexed queries if the PRGRS_ALWAYS_INDEX option is set to 0 (off). When this option is off and the user does not specify an index or BY clause for the results of their 4GL statement and the particular 4GL being executed does not require scrolling capability in the Progress client, the query result will remain unindexed by the DataServer.
NOTE: In an unindexed query, the DataServer will not send an ORDER BY clause to the data source. However, the DataServer has no control over whether or not the actual data source utilizes an index in order to generate a result set.Concurrent Query Execution: The PRGRS_CACHE_CONN Option
It is possible to run read-only queries in separate connections through the driver to the data source. Opening a separate connection to run a query or stored procedure might provide better performance; although, having to many open connections might also degrade performance. The PRGRS_CACHE_CONN option allows you to set a limit for the maximum number of server connections available in the Progress DataServer session. If the session attempts to exceed the maximum threshold, a session will need to wait for an existing connection to complete first before an additional connection can be made.
Connection Problems: The PRGRS_CONNECT Option
The PRGRS_CONNECT option allows you to pass ODBC-specific information to the ODBC driver. It has the following syntax:
The connection string is separated from the option by a comma (,) and ends with a semicolon (;).
Use the PRGRS_CONNECT option in the following cases:
- To connect to an ODBC data source whose name is not allowed by Progress; for example, a name that includes blank spaces, ampersands (&), commas (,), and/or carets (^). In the connection string, pass the following characters rather than the unallowed characters. The driver resolves the passed characters to the unallowed character:
– Pass the ampersand (&) character as two ampersand (&&) characters.
– Pass the caret (^) character as two caret (^^) characters.
– Pass the blank space character as an ampersand and a caret (&^).
– Pass the comma (,) character as a caret and ampersand (^&).
- To establish complex connections that require more than the Physical Database Name (-db), User ID (-U), and Password (-P) parameters. In all cases, the values must not be space delimited and must be passed in a single connection string. For example, the following connection string sets the user ID and password for the server and user ID and password for the data source:
For more information and syntax examples, see the "Special Connection Issues" section in Connecting the DataServer."
Key-Buffer Size: The PRGRS_IDBUF Option
The PRGRS_IDBUF option sets the size of the key-buffer. Generally, a default of 25 keys is sufficient. If the driver does not have preserved cursors across a transaction boundary, the key-buffer is used with all non-lookahead cursors. If the driver does have preserved cursors across a transaction boundary, use the PRGRS_IDBUF option.
Locking Error Messages: The PRGRS_LOCK_ERROR Option
Some data sources do not distinguish between lock problems and errors. The PRGRS_LOCK_ERROR option lets you control how your application reacts if it encounters an error that is actually a lock problem when accessing a data source. Use this option to pass the native error number to the DataServer so that it handles this error as it would a Progress lock problem; that is, the DataServer waits and retries, rather than halting the application:
Large Rows: The PRGRS_MINBUF Option
Some data rows can be very large; for example, in a Sybase data source, rows often have large fields such as IMAGE and MEMO. The ODBC protocol specifies a dynamic buffer allocation process for handling large rows that do not initially fit into clients’ buffers; however, some drivers do not yet follow the correct ODBC protocol and do not handle these large rows correctly. Use the -Dsrv PRGRS_MINBUF,size option to force a minimum buffer size. For example, -Dsrv PRGRS_MINBUF,15000 enables the DataServer to handle 15K rows even with drivers that fail to follow the ODBC protocol.
NOTE: It is often difficult to determine when there is a buffer size problem and how to choose the correct value for PRGRS_MINBUF. Be careful when using this option.Schema Import: The PRGRS_NO_INDEX Option
For ODBC drivers that are not fully ODBC Level 1 compliant, one potential trouble area is data-source index information. The Progress DataServer requires a unique index for each table and view in order to scroll, update, delete, and create rows. If the process of fetching schema information fails, you can reconnect to the data source using the -Dsrv PRGRS_NO_INDEX,1 startup parameter to specify that index information is not automatically imported. Instead, you create a unique index definition manually by using the Progress Data Dictionary. This guarantees that you can create a unique index for a combination of fields that truly have a unique index in the data source. Note that creating the index does not create an index in the ODBC data source; it marks the DataServer for the unique key for that table.
SQL Statements Cache: The PRGRS_PREPCACHE Option
The DataServer keeps a cache of prepared SQL statements that it reuses with new parameters. This mechanism improves the DataServer performance. You can use the PRGRS_PREPCACHE option to manage this cache in two ways:
- Some drivers might fail in reusing a prepared statement or in getting the second record for a particular query. Using -Dsrv PRGRS_PREPCACHE,0 instructs the DataServer to re-prepare each SQL statement.
- Use the PRGRS_PREPCACHE option to control the size of the cache. The default cache size is 20 statements. You can increase the size for large applications that reuse many queries. The maximum size depends on the amount of resources you have available.
Concurrent Procedure Results: The PRGRS_PROC_TRAN Option
The DataServer allows only one active request for running a sorted procedure before you run a second one. However, you can process results from several stored procedures concurrently if you set the PRGRS_PROC_TRAN switch to 1 (on). When switched on, this option will cause a separate connection to be used for each stored procedure request (assuming the PRGRS_CACHE_CONN threshold has not been exceeded).
CAUTION: When procedures run in separate connections of the same DataServer session, the scopes of their respective transactions are isolated from one other. If one running procedure attempts to update the same record used by another running procedure in the same DataServer session, a lock time out or even a deadlock could occur.Cursor Characteristics: The PRGRS_STABLE_CURS Option
The ODBC driver and its data source ultimately determine whether a cursor is stable during the commit or rollback of a transaction and if the cursor can persist beyond a single transaction scope. The DataServer normally resolves cursor characteristics by interrogating the driver and setting the run-time environment accordingly. Progress does not recommend bypassing normal operation under most circumstances. However, if your driver and/or data source do not provide stable cursors, then under very specific circumstances, you might improve performance by setting the PRGRS_STABLE_CURS option to 1 (on). If all the 4GL you run in your Progress DataServer session is without transactions or if all of its queries and resultant data sets are fully processed on one or the other side of any existing transaction boundaries, setting this option is possible.
NOTE: This is a session-level switch which means all 4GL run in that session must comply with the above requirements.Wait Time for Asynchronous Connections: The PRGRS_WAIT_DELAY Option
The PRGRS_WAIT_DELAY option is ignored unless you are running in ODBC asynchronous mode. Check your driver and Progress connection requirements to determine if you are allowed to run in asynchronous mode. This option allows you to determine the number of seconds you would want the DataServer to delay further execution while waiting for the driver to respond to a request that might initiate a lock on database resources. The delay is initiated when the NO–WAIT option is used in your 4GL and the DataServer is awaiting a response to a database request made through the ODBC driver.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |