Sybase Concepts Overview

Servers

A Sybase server is one copy of the database engine executing on a computer. A server has a name, and when a program wants to access the database controlled by a server, the program asks for a connection to that server by name. Multiple servers can be executing on a single machine, controlling different databases. The default name that Sybase gives to a server is SYBASE. Naming of servers is discussed in the Sybase: Configuration File Variables section under the configuration variable A-SYB-DEFAULT-CONNECTION.

Table ownership

Table names in Sybase have the form database.owner.table_name. Within Sybase, if you are the owner of a given table, you can refer to it as just table_name. If you are not the owner, you must refer to it with the owner of the table as a prefix. Different owners can thus have tables of the same name. However, this is not true when you use Acu4GL.

Acu4GL for Sybase works a little differently. It automatically determines the owner name it will use to reference a table. It is therefore essential that there not be multiple tables with the same name in a single database, even though the tables have different owners. If there are, the Acu4GL for Sybase interface will not necessarily find the correct one, and no diagnostic will be issued.

Note that table names include dots (.) as separators. Because of this, you must make sure there are no extensions on COBOL file names that will be converted to table names. If you were to have a COBOL file named IDX1.DAT, Acu4GL for Sybase would attempt to open a table DAT with owner IDX1. You can avoid this problem either by renaming your COBOL file in your source program, or by using an ACUCOBOL-GT runtime configuration file entry to map the file name to an allowable file name, such as:

IDX1.DAT IDX1

If you map your file name to a new name, we recommend that you simply drop the extension to form the new name. Here's why. The compiler uses the base file name-without the extension-to create the XFD file name (IDX1.XFD). The runtime needs to be able to locate this file. But if you've mapped the file name to something completely different (such as MYFILE), the runtime will look for an XFD file named MYFILE.XFD. So you'd have to remember to change the name of IDX1.XFD to MYFILE.XFD in the XFD directory. Save yourself this extra step by simply dropping the extension when you map the name.

Security

Security is implemented in the Sybase RDBMS. A user is required to log in to the RDBMS before any file processing can occur. Acu4GL for Sybase provides both a default and a user configurable method for implementing this.

Generally, it is best for someone with Database Administrator (DBA) privileges to create and drop the tables, allowing others only the permissions to add, change or delete information contained in them.

See the Sybase documentation for more details on DBA privileges.

Stored Procedures

The Acu4GL for Microsoft SQL Server interface checks for certain stored procedures when opening a file, and will use them in certain circumstances. These stored procedures are named:

xxx_insert
xxx_delete
xxx_read
xxx_update
xxx_startnnn (where xxx is the name of the table being
accessed, and nnn is the key number to start on)

The stored procedures must be in the same database as the table they reference.

NOTE

The Acu4GL for Microsoft SQL Server interface does not create these stored procedures. In fact, the Microsoft SQL Server interface does not check the accuracy of these stored procedures. It is possible to create stored procedures in such a way to make the Acu4GL for Microsoft SQL Server interface completely inoperable. The interface uses these stored procedures for performance reasons.

xxx_insert is used to WRITE a record to the file. The parameters passed to the stored procedure are the values of all the columns in the row, in the order of the columns in the database. The timestamp column and identity column (if present in the table) are not passed to the stored procedure.

xxx_delete is used to DELETE a record from the file. The parameters passed to the stored procedure are the values of the primary key, in the order they are listed in the XFD.

xxx_read is used to read a random record (READ, not READ NEXT or READ PREVIOUS). The parameters passed to the stored procedure are the values of the primary key, in the order they are listed in the XFD. The expected rowset is the columns in the first table (if secondary tables are used), or the columns of the table (if secondary tables are not necessary).

xxx_update is used to REWRITE a record from the file. The parameters passed to the stored procedure are the values of all the columns in the row, in the order of the columns in the database. The timestamp column and identity column (if present in the table) are not passed to the stored procedure.

xxx_startnnn is used to START a file. The nnn value is the key number to start on, and will be 0 filled. For example, the start procedure for the primary key for table mytab will be mytab_start000.)

Since there can be up to 119 alternate keys, the interface does not search for a start procedure unless, or until, it is used. The parameters passed to the stored procedure are a 2-char mode (it is a varchar(2) field), with one of the following values: >,>=, =, <=, <. The rest of the parameters are the columns of the key used to start. Because the ANSI specification for START includes information about the size of the key being used (and in particular allows partial keys), the start procedure is only used if an entire key is given to the start verb. This procedure is also special in that it does not return data, but needs to raise an error condition if the start fails. The way to raise the error condition from within the stored procedure is to include something like the following:

raiserror 22006 "Record not found"

The code 22006 is very important. It is the code searched for in setting the error condition from within the interface. If you use a different number, your starts may succeed when they should actually fail.

For example, given the following XFD (which is from newftest.cbl):

XFD,03,FTEST2-FILE,FTESTDAT

# ftestdat.xfd - generated by ACUCOBOL-GT v4.3 Alpha 1 (4/30/99)

# Generated Fri Apr  30 07:54:28 1998
00031,00031,003
01,0,004,00000
01
FTEST2-KEY
01,1,004,00004
02
FTEST2-KEY1-SEG1
FTEST2-KEY1-SEG2
01,0,004,00008
01
FTEST2-ALTKEY2
000
0006,00006
00000,00004,16,00004,+00,000,000,FTEST2-KEY
00004,00002,16,00002,+00,000,000,FTEST2-KEY1-SEG1
00006,00002,16,00002,+00,000,000,FTEST2-KEY1-SEG2
00008,00004,16,00004,+00,000,000,FTEST2-ALTKEY2
00012,00009,00,00009,+00,000,000,FTEST2-NUMBER
00021,00010,16,00010,+00,000,000,FTEST2-INFO

create procedure ftestdat_insert
@ft2_key char(4),
@ft2_key1_seg1 char(2),
@ft2_key1_seg2 char(2),
@ft2_altkey2 char(4),
@ft2_number char(9),
@ft2_info char(10)
as
     insert into ftestdat (ftest2_key, ftest2_key1_seg1,
 ftest2_key1_seg2, ftest2_altkey2, ftest2_number,
 ftest2_info) values (@ft2_key, @ft2_key1_seg1,
 @ft2_key1_seg2, @ft2_altkey2, @ft2_number, @ft2_info)

grant execute on ftestdat_insert to public

create procedure ftestdat_delete
@ft2_key char(4)
as
 delete from ftestdat where ftest2_key = @ft2_key

grant execute on ftestdat_delete to public

create procedure ftestdat_update
@ft2_key char(4),
@ft2_key1_seg1 char(2),
@ft2_key1_seg2 char(2),
@ft2_altkey2 char(4),
@ft2_number char(9),
@ft2_info char(10)
as
        update ftestdat set
 ftest2_key = @ft2_key,
 ftest2_key1_seg1 = @ft2_key1_seg1,
 ftest2_key1_seg2 = @ft2_key1_seg2,
 ftest2_altkey2 = @ft2_altkey2,
 ftest2_number = @ft2_number,
 ftest2_info = @ft2_info
 where ftest2_key = @ft2_key

grant execute on ftestdat_update to public

create procedure ftestdat_start001
@mode varchar(2),
@ft2_key1_seg1 char(2),
@ft2_key1_seg2 char(2)
as

if exists (select 1 from ftestdat where
(ftest2_key1_seg1 = @ft2_key1_seg1 and
((@mode = ">=" and ftest2_key1_seg2 >=@ft2_key1_seg2) or
 (@mode = ">" and ftest2_key1_seg2 > @ft2_key1_seg2) or
 (@mode = "=" and ftest2_key1_seg2 = @ft2_key1_seg2) or
  (@mode = "<" and ftest2_key1_seg2 < @ft2_key1_seg2) or
  (@mode = "<=" and ftest2_key1_seg2 <= @ft2_key1_seg2))))
return
if exists (select 1 from ftestdat where
(((@mode = ">=" or @mode = ">") and
ftest2_key1_seg1 > @ft2_key1_seg1) or
 ((@mode = "<=" or @mode = "<") and
 ftest2_key1_seg1 < @ft2_key1_seg1)))
return
raiserror 22006 "Record not found"

grant execute on ftestdat_start001 to public

If the stored procedure sp_AcuInit exists in the master database, then it is executed when the connection is made to the server. This is a procedure you can set up to do customized initialization. For example, you can use this stored procedure to remove stale locks by calling sp_AcuRemoveUnusedLocks_1 (which is installed when all the other Acu4GL stored procedures are installed - see your Sybase manual for information). This stored procedure is not passed by any parameters, and is not expected to return any results. However, if it causes an error to occur, then the login will fail, and the user will be denied access.

Alternate REWRITE method

If the above stored procedure is not available, then the update query passed to the database has been optimized to only update dirty columns. This should improve performance.