4GL-IGNORED-SUFFIX-LIST
This variable is a space-delimited list of case-independent suffixes to ignore when opening Microsoft SQL Server tables.
The standard syntax for Microsoft SQL Server tables is server.database.owner.table, with a dot (.) as the separator. This syntax causes the filename "idx1.dat" to be interpreted as "the table dat owned by the user idx1." By setting this variable (which must be set in the configuration file, and is not settable via SET ENVIRONMENT), the Acu4GL for Microsoft SQL Server interface will ignore the listed suffixes. There can be up to 10 ignored suffixes (more than that are not kept internally, so will be ignored.) The default value for this variable is "dat".
Example
4GL-IGNORED-SUFFIX-LIST dat idx txt
A-MSSQL-ADD-IDENTITY
Unless it is set at zero (0), A-MSSQL-ADD-IDENTITY will add an extra column to any table created by the Acu4GL for Microsoft SQL Server interface. The extra column will have the identity property, and will be included on all indexes that are not unique.
Example
A-MSSQL-ADD-IDENTITY non-zero number or zero
On keys that allow duplicates, this variable has been found to vastly improve performance.
A-MSSQL-CURSOR-OPTION-1
A-MSSQL-CURSOR-OPTION-2
A-MSSQL-CURSOR-OPTION-3
These configurations allow you to fine-tune the declaration of cursors in the Acu4GL for MSSQL interface. In general, cursors are declared with the following syntax:
DECLARE cursor_name option_1 CURSOR option_2 FOR (select....) option_3
In other words, different phrases can go in each of the option_X places. Also, different versions of SQL Server allow different options in each of those places. Because of this, the Acu4GL interface allows customization of the cursor declaration via these three variables. The values of these variables is placed verbatim into the declare phrase when building a cursor. Note that any errors in the values of these variables may cause your interface to be inoperable. Be sure to read the SQL Server documentation to determine what phrases are allowed in each case.
The default values of these variables are blank for OPTION_1 and OPTION_2, and if "for read only" for OPTION_3. For backwards compatibility, set A_MSSQL_CURSOR_OPTION_1 to "SCROLL". This was added because of an issue in SQL Server 6.5 which would return incorrect results if this phrase was not used. It is possible that SQL Server 7.0 suffers from the same issue and would need the same corrective action.
A-MSSQL-DATABASE
A-MSSQL-DATABASE specifies the name
of the specific database to be accessed. You cannot open any database files
until you have set this variable.
Example
A-MSSQL-DATABASE stores
indicates the stores database is to
be accessed.
A-MSSQL-DEFAULT-CONNECTION
A-MSSQL-DEFAULT-CONNECTION is the name
of the server to which the runtime will connect. This variable is checked
only if the DSQUERY environment variable has not been set. If neither DSQUERY
nor A-MSSQL-DEFAULT-CONNECTION is set, the server is named DSQUERY, just
as for isql. To reference tables in another server, open the file
servername.database.owner.table.
Example
Suppose you have two servers, one named
TOM and one named HARRY. If most of the tables you want to access are on
the server HARRY, then you should set:
A-MSSQL-DEFAULT-CONNECTION HARRY
For those occasions when you want to
access the TOM server, you could open the file this way:
TOM. stores.johndoe.purch1
See also
The Microsoft SQL Server documentation
on isql.
A-MSSQL-EXTRA-PROC
A-MSSQL-EXTRA-PROC can be used to keep modifications to the AcuLocks table out of transactions. This variable works by creating a separate connection for these modifications.
When this variable is set to a non-zero value, then an extra connection is used for the following three procedures:
See also
The "Table Locking" discussion in this appendix.
A-MSSQL-FAST-ACCESS
A-MSSQL-FAST-ACCESS is a configuration
variable that is set from your COBOL program. Files opened while this variable
is set to a non-zero value will be optimized for sequential access. Acucorp
implemented this option to substantially improve the READ NEXT performance
in some cases. For example, testing the Acucorp benchmark program iobench.cbl
in 3 different ways, yielded the following results for the READ AND SKIP
operation:
For certain reporting programs, this
option can dramatically improve performance. However, please note the following
restrictions.
First, files opened with FAST
ACCESS use a dedicated connection for reading from the file. Since connections
are memory-intensive (both on the client, and on the server), the number
of files opened with FAST ACCESS should be kept to a minimum. In the event
that opening a connection fails, the file open will still continue, but
FAST ACCESS mode will be disabled. (In the file trace, a message is printed:
FAST_ACCESS mode not available.) Files open with FAST ACCESS will not participate
in transactions, and may even cause the runtime to hang if transactions
are used, especially if the FAST ACCESS file is updated within the transaction.
We suggest that if you use transactions, you don't use FAST ACCESS. At
the minimum, if you use transactions, we suggest that you use FAST ACCESS
only for files open INPUT.
Files open with FAST ACCESS can
not be read backwards. In other words, READ PREVIOUS will not work with
FAST ACCESS files. In fact, if you try to READ PREVIOUS on a file opened
with FAST ACCESS, you will get an error 9D,20. The ANSI standard states
that READ NEXT after a READ will return the next record. Some applications
depend on this, and some applications just want to read dynamically from
a file, and don't use the positioning facility. Because of this ambiguity,
files that are open with FAST ACCESS can not be READ dynamically. If you
try to READ on a file open with FAST ACCESS, you will get an error 9D,20.
There are no restrictions on
WRITE, REWRITE, and DELETE. However, these operations use the cursor-based
connection, not the dedicated connection. This is the reason transactions
may hang.
A-MSSQL-FORCED-INDEX
A-MSSQL-FORCED-INDEX causes the interface
to attempt to use an actual index that matches the key used with the table.
In order for this variable to have any effect, the index matching the key
must be a unique index.
The default value of this variable
is 0 (off). If you don't
use the forced-index feature, the interface adds an ORDER BY clause to
the SELECT, which can add to the processing time.
Acucorp has recently discovered that
SQL Server does not guarantee that an index will be used if this variable
is set to 1, and so the order of records returned to the COBOL program
is not guaranteed to be correct in that case. Because of this, we highly
discourage the use of this variable.
A-MSSQL-LOCK-DB
A-MSSQL-LOCK-DB
specifies the name of the database that holds the lock table (see Microsoft
SQL Server:Table Locking ).
A-MSSQL-LOGIN
A-MSSQL-LOGIN indicates the user name
under which you want to connect to the database system.
Example
To connect to the database with the
user name MYNAME, you would specify:
A-MSSQL-LOGIN MYNAME
in the runtime configuration file.
If A-MSSQL-LOGIN is not set, the runtime
will use the value of your USER environment variable as your Microsoft
SQL Server login name. For this automatic login to succeed, you must have
set up a user with the same name as your computer login name.
See also
A-MSSQL-PASSWD runtime configuration
file variable.
Step Three: Microsoft
SQL Server: Setting Up a User Account
.
A-MSSQL-MAX-CHARACTERS
A-MSSQL-MAX-CHARACTERS indicates the
maximum number of bytes the interface will allow in a table row. The default
value is 1962.
Microsoft SQL Server places a limit
on the number of bytes per table row. The Acu4GL interface will adhere
to this limit, but sometimes it cannot accurately count how many bytes
a particular row contains (because of overhead bytes that SQL Server adds).
This variable enables the developer to set the interface's
upper bound. You might want to try reducing it if you discover that a row
cannot be added to a table. By reducing the upper bound, you may be able
to prevent the problem.
If SQL Server increases the maximum
number of bytes allowed in a row (in a future release of the product),
you could increase the value of this variable to take advantage of the
new limit.
A-MSSQL-MAX-
COLUMNS
A-MSSQL-MAX-COLUMNS indicates the maximum
number of columns the interface will allow in a table. The default value
is 250.
Microsoft SQL Server places a limit
on the number of columns per table. The Acu4GL interface will adhere to
this limit, but sometimes it cannot accurately count how many columns a
table contains (because a column has been added to a table without the
interface's
knowledge). This variable enables the developer to set the interface's
upper bound. You might want to try reducing the upper bound if you discover
that a table cannot be created for some reason. By reducing the upper bound,
you allow for uncountable columns and thus may be able to prevent the problem.
If SQL Server increases the maximum
number of columns allowed per table (in a future release of the product),
you could increase the value of this variable to take advantage of the
new limit.
A-MSSQL-NEW-PROC-FOR-IMAGE
A-MSSQL-NEW-PROC-FOR-IMAGE assists
with the processing of columns with type image.
When a column with type image is updated,
some versions of Microsoft SQL Server return this error: Attempt to initiate
a new SQL Server operation with results pending.
Setting this variable to a non-zero
value causes the Acu4GL interface to use a new dbprocess structure for
updating the image types. This prevents the error.
A-MSSQL-NO-23-ON-START
When A-MSSQL-NO-23-ON-START is set
to a non-zero value, START does not detect whether records actually exist.
Because it does not detect the existence of records, it is possible, when
using this variable, to do a START without error, and for the next READ
NEXT to return END_OF_FILE.
Example
A-MSSQL-NO-23-ON-START zero or a non-zero
number
A-MSSQL-NATIVE-LOCK-TIMEOUT
This is one of the two locking methods
available with Acu4GL. The methods are accessed via two configuration variables
(A_MSSQL_VISION_LOCKS_FILE and A_MSSQL_NATIVE_LOCK_TIMEOUT). The lock method
used is determined as follows: If A_MSSQL_VISION_LOCKS_FILE is set to the
name of a Vision file that can be opened I/O, and has the correct structure,
then this method is used. Otherwise, if A_MSSQL_NATIVE_LOCK_TIMEOUT is
set to a positive value, then this method is used. Otherwise, the AcuLocks
table is used to hold locks.
This locking method enables you to
use Microsoft SQL Server native locks if you explicitly code transactions
in your COBOL program. You can access this method by setting the configuration
variable A_MSSQL_NATIVE_LOCK_TIMEOUT to a positive value. This value will
be the number of seconds that a connection will wait for a timeout to occur.
When such a timeout occurs (for any reason), the interface assumes that
the timeout was due to a locked record, and will return error 99 (record
locked). If you set this variable but do not explicitly code transactions
in your COBOL program, record locking will not occur. Note that the interface
will wait the number of seconds specified, and your application may seem
to hang if the timeout is too long. On the other hand, if the timeout is
too short, you may get record locked errors when the network is slow.
Microsoft SQL Server uses a page locking
mechanism, and so this method of locking records may cause your application
to return spurious record locked errors due to a record being locked on
the same page as the record you are trying to access. If a future version
of Microsoft SQL Server implements row-level locking, then this locking
method may be the preferred method.
See also
A_MSSQL_VISION_LOCKS_FILE runtime configuration
variable and Microsoft SQL Server: Table Locking.
A-MSSQL-PACKETSIZE
This configuration variable, A-MSSQL-PACKETSIZE, sets the size of network packets. Setting this variable can affect performance, since fewer and larger network calls can improve performance.
This variable must be set in the configuration file, and has no affect if set in a COBOL program via SET CONFIGURATION or SET ENVIRONMENT. The value of this variable is the largest size that the transport layer uses for network packets (although the underlying library may reduce the size specified; this is out of the control of the interface.) The largest value that can be specified is 32767. The default depends on which version of the client libraries are linked into the runtime, although 512 is the most common default.
Use this configuration variable to tune your database performance. To set the packet size to 8192 use:
A-MSSQL-PACKETSIZE 8192
No FAST_ACCESS: 72.76 seconds
FAST ACCESS, ROWCOUNT 0: 148.88 seconds
FAST ACCESS, ROWCOUNT 10: 8.09 seconds.
(10 is the perfect value for ROWCOUNT
in this benchmark, since the program does a START, 10 READ NEXT operations,
and does it again.)
Setting this variable to 0 or to a negative value will cause the interface to use the default value.
A-MSSQL-PASSWD
The variable A-MSSQL-PASSWD should be set to the password assigned to the database account associated with the user name specified by A-MSSQL-LOGIN.
Examples
If the account with the user name in A-MSSQL-LOGIN has the associated password CW021535, you would specify:
A-MSSQL-PASSWD CW021535
in the runtime configuration file.
For better security, you can accept a password from the user during program execution; set the A-MSSQL-PASSWD variable based on the response:
ACCEPT RESPONSE NO-ECHO.
NOTE
If the user has been set up without a password, this variable need not be set.
See also
A-MSSQL-LOGIN runtime configuration file variable.
A-MSSQL-ROWCOUNT
This variable only has an effect if you are reading on a key that does not allow duplicates, or if you have added an Identity column to the table. If you set A-MSSQL-FORCED-INDEX to a non-zero value, then A-MSSQL-ROWCOUNT determines how many rows are returned by a SELECT statement sent to the server.
This variable can be used to speed up the interface. For example, if you know you will be reading only one record at a time, and reading from a unique key, you can set A-MSSQL-FORCED-INDEX to "1" and A-MSSQL-ROWCOUNT to "1", thus speeding up the processing.
If you know you are going to be reading records ten rows at a time, set A-MSSQL-ROWCOUNT to "10". If you don't have any information about how many rows are going to be requested, set this variable to "0", which is the default.
See also
A-MSSQL-ADD-IDENTITY runtime configuration file variable.
A-MSSQL-SKIP-ALTERNATE-KEYS
A-MSSQL-SKIP-ALTERNATE-KEYS determines whether alternate keys are used to form indexes during table creation. The default value of this variable is 0, which means it's okay to use alternate keys.
If you set the variable to a non-zero value (such as 1), alternate keys are not used to form indexes, which speeds up processing if many writes or rewrites are being done. (Note that a value of 1 may slow processing if the application is reading sequentially using an alternate key).
You can load a great deal of data into a table rapidly with this variable set to 1, and then create the missing indexes using isql.
See also
The Microsoft SQL Server documentation on isql.
A-MSSQL-USE-DROPDOWN-QUERIES
Setting A-MSSQL-USE-DROPDOWN-QUERIES to a non-zero value causes selects sent to the database to be of the drop-down variety, instead of a single large query. For example, if you have a file with 3 fields in the primary key, (keyseg1, keyseg2, keyseg3), and your COBOL program does a START, then the following query is sent to the database:
select (columns) from (table) where ((keyseg1 = value1 and keyseg2 =value2 and keyseg3 > value3) or (keyseg1 = value1 and keyseg2 >value2) or (keyseg1 > value1)) order by keyseg1, keyseg2, keyseg3
If you use drop-down queries, then the following collection of queries will be sent instead:
select (columns) from (table) where (keyseg1 = value1 and keyseg2 =value2 and keyseg3 > value3) order by keyseg1, keyseg2, keyseg3
select (columns) from (table) where (keyseg1 = value1 and keyseg2 >value2) order by keyseg1, keyseg2, keyseg3
select (columns) from (table) where (keyseg1 > value1) order by keyseg1, keyseg2, keyseg3
There are advantages and disadvantage to each method. If you use the A4GL-WHERE-CONSTRAINT variable, you should probably set A-MSSQL-USE-DROPDOWN-QUERIES to 0, since the where-constraint will limit the result set sufficiently that the larger query will be more efficient.
If you usually START files, and read to the end, then you should set A-MSSQL-USE-DROPDOWN-QUERIES to 0, since a fewer number of queries need to be sent to the database. On the other hand, if you START files, and stop reading after some condition, but haven't used the where-constraint, then you may get more efficient access by setting this variable to 1, and using the drop-down style of query. In either case, it is recommended that you run some tests to see which value of this variable makes the most sense for your application.
This variable is accessed only during a positioning operation, so you can set it at different times for different tables.
Example
A-MSSQL-USE-DROPDOWN-QUERIES zero or a non-zero number
A-MSSQL-VISION-LOCKS-FILE
This locking method causes the lock table (AcuLocks) to be a Vision file instead of an SQL table. This can be accessed via the configuration variable A_MSSQL_VISION_LOCKS_FILE, which must be set to the name of the Vision file that will hold the lock information. Note that you must also set a configuration variable that specifies this file as a Vision file (using a -host variable). This file must be accessible to all users accessing the MS SQL Server, either through a common directory, or through AcuServer.
Also included with the Acu4GL for Microsoft SQL Server interface is a small COBOL program that will manage this Vision file ("lockmgr"). This program should be run with the same runtime that you normally use for access MS SQL Server tables, and with the same configuration variables set. The program detects whether Acu4GL for Microsoft SQL Server is available, and detects the A_MSSQL_VISION_LOCKS_FILE variable to determine which file to manage. This program displays all the records in the lockfile, and gives options for removing single records (by highlighting the desired record to remove), or removing all shown records, and also for restricting the shown records by PID, Table, and Database. You can refresh the display by selecting the Restrict button, and then pressing OK without restricting the display further. If everything is working correctly, there should be no records in this table when there are no users accessing MS SQL Server through Acu4GL. The source for this program is in the sample/acu4gl directory.
See also
A_MSSQL_NATIVE_LOCK_TIMEOUT configuration file variable and Microsoft SQL Server: Table Locking.