Progress
Version 9
Product Update Bulletin


Progress Version 9.1D DataServer For Microsoft SQL Server

The Progress Version 9.1D DataServer for Microsoft SQL Server has the following enhancements:

Collation Table Support For Microsoft SQL Server

Version 9.1D of the DataServer for Microsoft SQL Server allows the user to specify the collation table that should be used through the Create/Modify Database Record for MSS Schema dialog box. You can access the Collation option through the Data Administration tool, and select DataServer MSS Utilities Create DataServer Schema.

The MSS Schema dialog box shown in Figure A–4 opens.

Figure A–4: Create/Modify Database Record For MSS Schema Dialog Box

The collation table that the user specifies must be defined in the convmap.dat file or the user will receive an error message stating that the combination between the code page and the table does not exist.

There is also a new fill-in, Case Insensitive. The case-insensitive selection is to determine if the code page used in the MS SQL Server database engine, the code page selected during installation of the SQL Server product, is case insensitive. MS SQL Server is case insensitive by default, so there is no need to add shadow columns, but if the code page is sensitive, then the user has the option to create shadow columns.

For more information on collation tables, see the Progress Internationalization Guide .

The user can also specify the Collation table in the PROGRESS DB to MS SQL Server Conversion. To access the utility from the Data Administration tool, choose DataServer MS SQL Server Utilities Schema Migration Tools PROGRESS DB to MS SQL Server. The dialog box shown in Figure A–5 opens.

Figure A–5: PROGRESS DB To MS SQL Server Conversion Dialog Box

Alternative No NameServer Support For Microsoft SQL Server

In prior versions of the DataServer for Microsoft SQL Server, you can connect through the Progress Explorer without using the NameServer by providing a special value of “none” to the -DataService connection parameter. Now, in Progress Version 9.1D, to indicate that the NameServer should not be used, the DataServer for Microsoft SQL Server can use the -DirectConnect connection parameter. If the -DirectConnect connection parameter is specified, then the -H and -S parameters are interpreted as the host and port of the broker. However, unlike connecting directly to the broker with the -DataService connection parameter, using the -DirectConnect parameter makes the -H and -S parameters optional. If -H is not provided, it defaults to “localhost.” If -S is not specified, it defaults to 4446. If -DirectConnect is specified, the -DataService is ignored.

Insert Trigger Support For Microsoft SQL Server

Progress Version 9.1D has modified the insert trigger that it places on a table in the foreign database when the Progress RECID support is selected. The modification reduces the level of contention between multiple users performing inserts simultaneously. The reduction in contention can produce performance improvement.

If you perform a migration using the MS SQL Server Progress DB to MS SQL Server utility and select to have Progress RECID support, Progress recommends that you dump your data, re-create your MS SQL Server database by redoing the migration and reload the data. If this is not possible, then you can manually change your existing MS SQL Server database by doing the following on each table that has been defined with Progress RECID support from your SQL Server Migration:

  1. Drop the unique index created over the progress_recid field. There should be one index for each table created during the initial migration. The syntax is:
  2. SYNTAX
    DROP index <database>.<owner>.<table name>#_#progress_recid. 
    

  3. Drop the old insert trigger. The name typically looks like _TI_<tablename>. The syntax is:
  4. SYNTAX
    DROP trigger <database>.<owner._TI_<table name> 
    

  5. Add the new trigger. The syntax is:
  6. CREATE TRIGGER _TI_<table name> ON <table name> FOR INSERT AS 
        IF ( SELECT PROGRESS_RECID FROM INSERTED ) IS NULL 
        BEGIN 
           UPDATE t SET PROGRESS_RECID = i.IDENTITYCOL 
              FROM <table name> t JOIN INSERTED i ON 
              t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_ 
              SELECT CONVERT (int,@@identity) 
        END 
    

    Sample code for db.owner.Customer table is:

    CREATE TRIGGER db.owner._TI_customer ON customer FOR INSERT AS 
       IF ( SELECT PROGRESS_RECID FROM INSERTED ) IS NULL 
       BEGIN 
          UPDATE t SET PROGRESS_RECID = i.IDENTITYCOL 
          FROM customer t JOIN INSERTED i ON 
          t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_ 
          SELECT CONVERT (int, @@identity) 
       END 
    

  7. Create a new nonunique index for each table naming the new index the same name as was dropped earlier in Step 1. Make sure it is not unique. The syntax is:
  8. CREATE INDEX <database>.<owner>.<table>#_#progress_recid ON  
    <table name>(PROGRESS_RECID) 
    

    A second index must be created for the progress_recid_ident field. This index must be created as unique and named <table name>#_#progress_recid_ident. The syntax is:

    CREATE UNIQUE INDEX <table name>#_#progress_recid_ident_ ON  
    <table name>(PROGRESS_RECID_IDENT_ 
    

    An example of the two indices that must be created for the customer table is as follows:

    CREATE INDEX customer#_#progress_recid ON CUSTOMER (PROGRESS_RECID) 
    CREATE UNIQUE INDEX customer#_#progress_recid_ident_ ON 
    CUSTOMER(PROGRESS_RECID_IDENT_) 
    

Dynamic Find Feature For Microsoft SQL Server

In Progress Version 9.1D, the DataServer for Microsoft SQL Server executes Dynamic Find statements like a FOR FIRST or FOR LAST statement. This should provide improved performance over a typical FIND FIRST or FIND LAST statement. In particular, the NO-LOCK Dynamic Find statements will be run using a single SQL SELECT statement instead of the two SQL SELECT statements required by a typical FIND statement. In addition, the result set is limited to a single record, as they are with a FOR FIRST and a FOR LAST.

QUOTER 4GL Function For Microsoft SQL Server

The QUOTER 4GL function can be useful for quoting values or handling embedded quotes when building SQL statements that will be passed into the send-sql-statement stored procedure. For more information on the QUOTER function, see "Progress 4GL Language Reference" in this bulletin and the Progress Programming Handbook .

FOR FIRST and FOR LAST Performance For Microsoft SQL Server

The FOR FIRST and FOR LAST functions in the Progress Version 9.1D DataServer for Microsoft SQL Server has been enhanced by limiting the server-side result set to a single record. This should provide additional improvement in performance for these statements over traditional FIND statements. The new dynamic find statements emulate the new FOR FIRST and FOR LAST behavior and also provide performance improvement over traditional FIND statements.

DataServer (–Dsrv) Startup Parameter Option For Microsoft SQL Server

RECID Buffer: The ZPRGRS_RECID_BUF_SIZE,xxx Option

If you do not select the Progress RECID field in your Progress-to SQL Server Database conversion, then a unique index over each table is selected to support Progress RECID/ROWID functions. RECID functionality enables backward and forward scrolling in the DataServer for Microsoft SQL Server product.

In Progress Version 9.1D, the RECID buffer is used to store key information about the unique index selected to support RECID. The default RECID buffer size is 245 bytes. The space is used dynamically to allocate the RECID buffer for a given record. The RECID buffer size needs to be large enough to contain all the key information regarding the unique index. If the unique index selected is a composite of many columns and/or contains large column names, the RECID key information might exceed the RECID buffer size and issue an error message 2090.

If you exceed the RECID buffer limit, Progress first recommends that you try to find an alternative unique index with a smaller number of key fields to represent the Progress RECID. This should help improve performance during record access as well. You can change the RECID index in the Progress dictionary by selecting the “DataServer” button from the Table Properties of the table in your schema holder.

If an alternative unique index is not available and it is not possible to reduce the key size of your existing RECID unique index to avoid overrunning the buffer, you can expand the area by setting the startup option as follows:

SYNTAX
-Dsrv ZPRGRS_RECID_BUF_SIZE,xxx 

Where xxx is the new size of the area in bytes.

You can expand the RECID buffer to a maximum of 1000 bytes. The minimum size is 44 bytes, which is the size required by the PROGRESS_RECID field when the Progress RECID field is selected during database conversion. The algorithm to determine your adjusted size is as follows:

9 + Length of first Index column name  + Length of first column data  + 17 
  + Length of second Index column name + Length of second column data + 22 
  + Length of third Index column name  + Length of third column data  + 22 
  + ... 

Block Cursor For Microsoft SQL Server

In Progress Version 9.1D, block cursors are available as a performance enhancement. They allow blocks of records to be bound and fetched together from a single database request, thus reducing the network overhead and minimizing record copies for 4GL queries and finds, and minimizing record copies. In Progress Version 9.1D, block cursors become the default behavior replacing look-ahead cursors for NO-LOCK queries. A NO-LOCK query is one that executes with the NO-LOCK lock condition attached to its 4GLstatement. DataServer connections that run at the read uncommitted isolation level will also execute queries that specify the SHARE-LOCK condition as NO-LOCK queries and thus will also utilize block cursors for their result sets. You can shut off block cursor behavior and revert back to look-ahead cursors for NO-LOCK queries by setting the new -Dsrv parameter PRGRS_BLOCK_CURS to zero. If you leave block cursors on but wish to turn off the block and/or look-ahead cursor optimizations for a specific query, you can set the QUERY-TUNING option QUERY-TUNING(NO-LOOKAHEAD) on your 4GL statement.

While block cursors increase efficiency, they might also require more memory than look-ahead cursors. Block cursors will try to reuse memory from previous result sets whenever possible. However, you can adjust the memory available to block cursors according to your specifications. Adjust the cache size of an individual block with the same connection-level parameter you used to set the look-ahead cache size. The QT_CACHE_SIZE -Dsrv option allows you to specify at connect time how large the block cache should be for individual query results. When block cursors are active, the default QT_CACHE_SIZE is set to 10,000 bytes (when look-ahead cursors are active, this size defaults to 30,000 bytes instead).

You can override the connection-level QT_CACHE_SIZE at the statement level by setting the QUERY-TUNING option called CACHE-SIZE on the query itself. Determining the proper block size should be based on the maximum length of your returned records multiplied by the expected number of resultant rows and then compared to your available memory. Whenever an existing block is not reused, the block of memory allocated for the query will be adjusted downward to an exact multiple of the number of rows that can fit into the allocated area.

To prevent large applications from potentially overutilizing memory for block cursors, two other settings can be adjusted to limit the amount of memory available to block cursors. The first is the maximum block memory usage per table. If you have multiple queries open against the same table simultaneously, each query uses its own query block. The PRGRS_TABLE_BLOCKSIZE -Dsrv option puts an upper limit on the total memory available to query blocks of an individual table. This number should normally be set larger than, and perhaps as a multiple of, the QT_CACHE_SIZE value. If the maximum block memory available to the table is, or would be, exceeded by allocating space for the current NO-LOCK query in your 4GL, the query is instead executed with a look-ahead cursor. The default maximum block memory area per table is set at 65,000 bytes.

The second adjustment switch available is the PRGRS_MAX_BLOCKSIZE -Dsrv option. This value sets an upper limit to the overall memory provided to block cursors (irrespective of per table allocations). The default maximum is 1048576 bytes (or 1MB). Your accumulated total memory allocated for block cursors will not exceed this value. If allocating block cursor memory for a NO-LOCK query in your 4GL would cause this limit to be exceeded, the query would instead be executed with a look-ahead cursor. The PRGRS_MAX_BLOCKSIZE value should be set with respect to the amount of memory available on your client machine when running the Personal edition of the Microsoft SQL Server DataServer. If you are running in Enterprise mode using the Enterprise edition, the number should be set with respect to both the available memory on the server machine as well as the number of Progress clients that will be connecting through your Progress database.

The following example uses the state table in the demo database, which has a maximum record size of 84 bytes:

FOR EACH state NO-LOCK QUERY-TUNING(CACHE-SIZE 850): 
      DISPLAY state. 
END. 

In this example, the QUERY-TUNING CACHE-SIZE value overrides the -Dsrv QT_CACHE_SIZE query block size default of 10,000 bytes. Ten records at 84 bytes per record would total 840 bytes in the query block. If a previously established block was located for that table that was greater than or equal to the requested size, it would be reused. However, if a new block were established instead, its memory allocation would be adjusted downward to 840 bytes in order to fit an exact multiple of rows in the block. If the result set contained 30 records, for example, the query block would get refilled three times before the end of the result set if read sequentially


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095