4GL-IGNORED-SUFFIX-LIST
This variable is a space-delimited list of case-independent suffixes to ignore when opening Sybase tables.
The standard syntax for Sybase 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 Sybase 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-SYB-ADD-IDENTITY
Unless it is set at zero (0), A-SYB-ADD-IDENTITY will add an extra column to any table created by the Acu4GL for Sybase interface. The extra column will have the identity property, and will be included on all indexes that are not unique.
Example
A-SYB-ADD-IDENTITY number or zero
On keys that allow duplicates, this variable has been found to vastly improve performance.
A-SYB-ADD-TIMESTAMP
Using a timestamp column is the only way to absolutely ensure modifications made to a row are not overwriting someone else's changes. See the discussion of BROWSE MODE in the Sybase Commands Reference Manual. When it's reading a table that is open for I/O, the Acu4GL for Sybase interface uses BROWSE MODE if a timestamp column exists. When the Acu4GL interface is creating a table, if the value of A-SYB-ADD-TIMESTAMP is 1, then a timestamp column is included in the table. (Note that your COBOL FD should not include the timestamp column.)
Example
A-SYB-ADD-TIMESTAMP 1
See also
BROWSE MODE in the Sybase Commands Reference Manual.
A-SYB-CURSOR-OPTION-1
A-SYB-CURSOR-OPTION-2
A-SYB-CURSOR-OPTION-3
These configurations allow you to fine-tune the declaration of cursors in the Acu4GL for Sybase 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 Sybase allow different options in each of those places. Because of this, the 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. You should refer to the Sybase 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. Users of Sybase version 11 should set the value of OPTION_3 to “at isolation read uncommitted” in order to prevent page locks.
A-SYB-DATABASE
A-SYB-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-SYB-DATABASE stores
indicates the stores database is to be accessed.
A-SYB-DEFAULT-CONNECTION
A-SYB-DEFAULT-CONNECTION is the name of the server to which the runtime will connect. The default value of this variable is the value of the environment variable DSQUERY (this is a Sybase variable, and so should be set in your environment. See the Sybase documentation for isql.) If neither variable is set, the default server is named SYBASE, 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-SYB-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 Sybase documentation on isql.
A-SYB-EXTRA-PROC
A-SYB-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-SYB-FORCED-INDEX
A-SYB-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 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-SYB-LOCK-DB
A-SYB-LOCK-DB specifies the name of the database that holds the lock table (see discussion of locking).
A-SYB-LOGIN
A-SYB-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, specify:
A-SYB-LOGIN MYNAME
in the runtime configuration file.
If A-SYB-LOGIN is not set, on Windows and Windows NT the runtime will use the value of your USER environment variable as your Sybase login name. On UNIX systems, the runtime will use your UNIX login name as your Sybase 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-SYB-PASSWD runtime configuration file variable.
Step Three: Setting Up a User Account.
A-SYB-FAST-ACCESS
A-SYB-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:
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.)
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-SYB-MAX-CHARACTERS
A-SYB-MAX-CHARACTERS indicates the maximum number of bytes the interface will allow in a table row.
Sybase 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 Sybase 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 Sybase increases the maximum number of bytes allowed in a row (in a future release of the Sybase product), you could increase the value of this variable to take advantage of the new limit.
The A-SYB-MAX-CHARACTERS variable has a default value of 1962.
A-SYB-MAX-COLUMNS
A-SYB-MAX-COLUMNS indicates the maximum number of columns the interface will allow in a table.
Sybase 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 Sybase increases the maximum number of columns allowed per table (in a future release of the Sybase product), you could increase the value of this variable to take advantage of the new limit.
The A-SYB-MAX-COLUMNS variable has a default value of 250.
A-SYB-NEW-PROC-FOR-IMAGE
A-SYB-NEW-PROC-FOR-IMAGE assists with the processing of columns with type image.
When a column with type image is updated, some versions of Sybase 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.
Because a non-zero setting can degrade performance, we recommend that you set this variable only if you receive the error message.
A-SYB-NO-23-ON-START
When A-SYB-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-SYB-NO-23-ON-START zero or a non-zero number
A-SYB-NATIVE-LOCK-TIMEOUT
This is one of the two new locking methods available with Acu4GL Version 4.1. The methods are accessed via two configuration variables (A_SYB_VISION_LOCKS_FILE and A_SYB_NATIVE_LOCK_TIMEOUT). The lock method used is determined as follows: If A_SYB_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_SYB_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 Sybase native locks if you explicitly code transactions in your COBOL program. You can access this method by setting the configuration variable A_SYB_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.
Sybase 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 Sybase implements row-level locking, then this locking method may be the preferred method.
See also
A_SYB_VISION_LOCKS_FILE runtime configuration variable and Sybase: Table Locking.
A-SYB-PASSWD
The variable A-SYB-PASSWD should be set to the password assigned to the database account associated with the user name specified by A-SYB-LOGIN.
Examples
If the account with the user name in A-SYB-LOGIN has the associated password CW021535, you would specify:
A-SYB-PASSWD CW021535
in the runtime configuration file.
For better security, you can accept a password from the user during program execution; set the A-SYB-PASSWD variable based on the response:
ACCEPT RESPONSE NO-ECHO.
SET ENVIRONMENT "A-SYB-PASSWD"
TO RESPONSE.
NOTE
If the user has been set up without a password, this variable need not be set.
See also
A-SYB-LOGIN runtime configuration file variable.
A-SYB-PACKETSIZE
This configuration variable, A-SYB-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. It has no affect if it is 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-SYB-PACKETSIZE 8192
Setting this variable to 0 or to a negative value causes the interface to use the default value.
A-SYB-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-SYB-FORCED-INDEX to a non-zero value, then A-SYB-ROWCOUNT determines how many rows are returned by a SELECT statement sent to the server. If you set A-SYB-FORCED-INDEX to a non-zero value, then A-SYB-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-SYB-FORCED-INDEX to "1" and A-SYB-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-SYB-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-SYB-FORCED-INDEX runtime configuration file variable.
A-SYB-SKIP-ALTERNATE-KEYS
A-SYB-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 Sybase documentation on isql.
A-SYB-USE-DROPDOWN-QUERIES
Setting A-SYB-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. This variable is accessed only during a positioning operation, so you can set it at different times for different tables. 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-SYB-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-SYB-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.
Example
A-SYB-USE-DROPDOWN-QUERIES zero or a non-zero number
A-SYB-VISION-LOCKS-FILE
This is one of the two new locking methods available with Acu4GL Version 4.1. The methods are accessed via two configuration variables (A_SYB_VISION_LOCKS_FILE and A_SYB_NATIVE_LOCK_TIMEOUT). The lock method used is determined as follows: If A_SYB_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_SYB_NATIVE_LOCK_TIMEOUT is set to a positive value, then this method is used. Otherwise, the AcuLocks table is used to hold locks.
A_SYB_VISION_LOCKS_FILE causes the lock table (AcuLocks) to be a Vision file instead of an SQL table. This can be accessed via the configuration variable A_SYB_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 SYBASE SQL Server, either through a common directory, or through AcuServer.
Also included with the Acu4GL for Sybase interface is a small COBOL program that will manage this Vision file ("lockmgr"). This program should be run with the same runtime as you normally use for access Sybase tables, and with the same configuration variables set. The program detects whether Acu4GL for Sybase is available, and detects the A_SYB_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 SYBASE SQL Server through Acu4GL. The source for this program is in the sample/acu4gl directory.
See also
A_SYB_NATIVE_LOCK_TIMEOUT runtime configuration variable Sybase: Table Locking.