Synergy file types

There are four types of Synergy database files: Synergy ISAM files (or RMS on OpenVMS), Synergy relative files, Synergy sequential files, and Synergy stream files.

Synergy ISAM files

This section includes the following subtopics:

We use RMS ISAM on OpenVMS for native compatibility. All other systems use Synergy ISAM. See OpenVMS Development in the Professional Series Portability Guide for information about Synergy ISAM features that are not compatible with RMS ISAM. See the OpenVMS Record Management Services Reference Manual for information on how to use RMS ISAM.

A Synergy ISAM file is used for high-speed, keyed access and ordered sequential access. As your file grows, high-speed, keyed access is maintained. Your ISAM file’s size can grow to fit the need of your application, given the physical limitations of your disk. For example, we can have an ISAM file that contains a record for each of our customers. A record in an ISAM file consists of a set of fields. Each field stores a specific item of data, such as a customer number, a first and last name, a company name, a street address, a city, a ZIP Code, or a telephone number.

If we wanted to find a particular customer in a non-ISAM database file, such as customer number 125 or customer B. Jones, our program might have to search the entire file. Synergy ISAM, however, provides an access method that uses an index. An index enables you to quickly find specific records in a database file without having to search the entire file and without your program having to look at extra records. This is called keyed access. It also enables you to define an order for the sequential processing of a database file. This is called sequential access.

Each index in an ISAM file is defined by a key. A key is one or more fields or portions of fields from a record that are used to locate that record. Keys are defined when the ISAM file is created. For example, we can define a key for our customer number field that places customer numbers in ascending or descending order within the corresponding index.

An ISAM file’s index contains leaf entries, which are sequentially ordered key values that point to corresponding data records. For example, the index defined by our customer number key, as illustrated in figure 1, contains the customer numbers (key values) in descending order for all customers in an ISAM file. These customer number entries point to data records. The data records are in no particular order. The index, however, is always in a specific order as defined by the key. In this case, the index is in descending order by customer number.

ISAM indexes are also structured hierarchically so that access to any particular record occurs with a minimum of index reading. Access to any data record by a particular key requires the same amount of index reads, provided the index doesn’t change. This number is determined by the index depth. Keyed access requires one index READ for each level of the index. The number of levels required for an index is universally proportional to key length. Large ISAM files (one million or more records) with long keys (45 or more characters) have five or more levels of index depth.

1. Diagram of an ISAM file’s index.

As shown in figure 1, an ISAM file’s index is composed of blocks. A block is the smallest unit of an index that can be read or written at one time, and block size is set when you create an ISAM file. (Note that to simplify the diagram, the block size in the diagram is artificially small.) The index in the diagram is three levels deep. In this case, separator blocks make up the first two levels of the index. (Any ISAM file with more than one index block has separator blocks.) The third or last level is made up of leaf blocks, which contain sorted key values (in this case, customer numbers) with a one-to-one correspondence between each leaf entry and the data record to which it points. The separator blocks exist as pathways to the leaf entries and are composed of pointers to lower-level index blocks and separator values that narrow the range of key values. The root block is a special separator block that is read first on any keyed access.

For example, let’s assume we want to access the data record for customer number 40002 by our customer number key using the following statement:

read(ch, rec, "40002")

First the root block is read. Synergy ISAM will determine that the number of our customer number (4) is greater than the value 3. We therefore read the separator block indicated by the first bold pointer in the diagram. Synergy ISAM will then determine that the first two numbers of our customer number (40) are less than the value 52 in the separator block. We therefore read the next block indicated by the second bold pointer in the diagram. Synergy ISAM will then find the entry 40002 within this block, which reads the data record for customer number 40002. By structuring the index hierarchically, Synergy ISAM enables us to access data records without having to read through the entire index. Also notice that reading any record by key value requires the same number of index reads.

An ISAM file can have more than one index. For example, we can define an index for the customer number field of our ISAM file and another index for the customer name field. These indexes enable us to quickly access the record for customer number 125, or the record for customer B. Jones.

The keys of an ISAM file also define the sequential order in which the file may be processed. For example, we can access our ISAM file sequentially by the customer number key, alphabetically by the customer name key, geographically by the customer city key, or by any other key that we define.

You can create an ISAM file using the ISAMC subroutine, the OPEN statement, or the bldism utility. See ISAMC, OPEN, or bldism for details.

Revision levels and file compatibility

ISAM Revision 6 is the default revision level for all ISAM files being created, unless 6 is overridden by the ISAMC_REV environment variable. (ISAMC_REV enables you to create ISAM files compatible with previous versions of Synergy or to convert ISAM files to a higher revision level.)

Starting with Revision 6, ISAM files also have a compat level, which enables them to change their file structure without requiring an entire ISAM revision change. The compat level begins with the revision number, followed by a period and another digit that is incremented by 1 each time new ISAM features are added.

Current compat levels and their features are as follows:

Compat level

Release version

ISAM features

6.0

10.1

6.1

Non-Synergy release

6.2

10.3

Support of records greater than 64K

CTIMESTAMP key

SIZE_LIMIT option

RECORD_LIMIT option

All REV 6 files created without any of the above listed features have the initial 6.0 compat level. Using one or more of the features above gives a file the specified compat level.

As an example, let’s say you install Synergy/DE version 10.1. If you create files with any ISAM options available through version 10.1, the compat level for these files is 6.0. You can access these files with version 10, but because the revision level is REV 6, you can’t access them with earlier Synergy versions (9.5.3, 8.3.1, etc.), which had a lower revision level.

If you upgrade one of your systems to 10.3, both versions 10.1 and 10.3 can access the files you created with 10.1, because version 10.3 can access prior revisions (4 and 6) and compat levels. If you create new files with 10.3 and continue to use the 10.1 compatible options, both 10.1 and 10.3 can still access the files, and the compat level is 6.0. However, if you create more new files with 10.3 and this time use new 10.3 options such as the CTIMESTAMP autokey, the compat level for these files will be 6.2. If version 10.1 attempts to open the file now, it can’t, because 10.1 can only access a compat level of 6.0.

To view the compat level of a file, use the ipar utility. Below is some sample output of a file created with 10.3 with new options. Note that it says “revision 6” and then below that, “Creation version 10.2.5c {Compat Level 6.2}.”

;       Synergy ISAM PAR File created Fri Oct 17 15:14:10 2014
test
50      ;Record size
1       ;Number of keys
        ;5ca5 magic, revision 6, 1 byte record overhead
        ;Static vectoring disabled
        ;Default R6 file attributes:
        ;  Static RFAs enabled (Required)
        ;  Page size 4K
        ;Creation version 10.2.5c {Compat Level 6.2}
        ;0 of 32 byte file text allocation in use, @0x1694
        ;Update revision count 0
        ;File created on Fri Oct 17 15:14:05 2014
        ;8 byte longest key
        ;0 free index blocks, 0x0 free list head
        ;0 records, 0 free
/type
        ;Primary key
C       ;Create timestamp autokey type
8       ;Key size
1       ;Start position
N       ;Duplicates allowed
A       ;Ascending/descending
        ;Root 0x2000, index depth 1
        ;Minimum keys per block 170

Large sector drives

Revision 6 ISAM files target the performance of Advanced Format large sector drives by reading and writing index blocks on 4K boundaries. The page size defaults to 4K, or 4096 bytes. (See Page size for more information.) If you want a smaller page size, you must explicitly specify it when the file is created.

Note

You must use Revision 6 ISAM with large sector drives if you want them to operate efficiently.

ISAM file structure

The physical representation of a Synergy ISAM file reflects two files: one contains the data records and the other contains the indexes that point to the data. The index file has the default extension .ism (for example customer.ism). The data file has the extension .is1 (for example, customer.is1). The two files are always referenced together as one ISAM file with the extension .ism.

If you specify an ISAM filename with an extension other than .ism (for example, customer.dat), the last character of the data file’s extension is replaced with a 1 (for example, customer.da1). However, if the last character of the specified filename’s extension is already numeric (for example, cust.ab1), the last character of the data file’s root filename is replaced with an underscore (for example, cus_.ab1). In both cases, the index file (customer.dat or cust.ab1) and the data file (customer.da1 or cus_.ab1) are always referenced by the name of the index file (customer.dat or cust.ab1).

ISAM file types

When creating an ISAM file, you can specify one of three file types:

If your ISAM file is used for only one data structure, you can use the fixed-length format. With this file type, all data in the ISAM data partition is stored in the same length record, regardless of the actual size of the data within the record.

If your ISAM file is used for a predefined group of different sized data structures, you can use the multiple fixed-length record format. The size of the stored record is determined by the data passed to the STORE statement. With this file type, you can’t change the record lengths, using the WRITE statement, after the data has been stored. Multiple fixed-length files can have up to 32 different record lengths. Using this file type enables you to reduce disk storage requirements and the number of open files. This file type is more efficient in disk space usage than variable-length records for cases where there are a limited number of different record sizes. Change tracking is not allowed with multiple fixed-length files.

If your ISAM file is used to store different types of records and it has no set pattern to the record size, or if the data length might change after the initial data is stored, you can use variable-length records. Like multiple fixed-length records, the initial size of the stored data is determined by the size of the data passed to the STORE statement. With variable-length records, however, you can change the size of the record using the WRITE statement.

The isload and fconvert ISAM utilities recognize one additional file type called a counted file. The isutl utility may also create a counted file in the form of an exception file, due to specific failures encountered during the recovery process. Counted files are not supported by the OPEN statement. Each record in a counted file starts with a two-byte length, which is the length of the record written as a portable integer, followed by the record itself, padded out to an even number of bytes if the length is odd. The final two bytes of the file are 0xFFFF, or integer -1.

ISAM index density

Three forms of index density occur during file updates:

Natural fill is enabled by default (in other words, if you don’t specify the file or key density options). Balanced and context fill are enabled for all keys when the file density option is specified, or for the specified keys when the key density option is specified. Routine file updates (STORE, WRITE, and DELETE) use the defined density form and pack indexes accordingly. In addition, the isutl utility (-rp option) can be used to pack indexes to a desired density without changing the defined density option.

Keys stored randomly using natural fill will typically fill to about 68 percent, while keys stored sequentially will fill to 50 percent. Keys stored randomly using balanced fill will typically fill to about 73 percent (when defined density is 80 or higher), while keys stored sequentially will fill to the defined density.

We believe natural fill is best for files that regularly have large volumes of file updates, and it should be followed up by running isutl at regular intervals to optimize the indexes. For files that normally have few updates, like window libraries, archives, and other read-only files, use the natural fill and then use the isutl -rp option to pack the indices to 100 percent. This will reduce the size of the file by compacting the indexes and improving read performance.

Keys that represent a sequence number or any continually increasing value that grows sequentially (such as a key based on time) where insertions to the middle of the sequence are rare may be candidates for context fill at 100 percent density. If these keys allow duplicates, make sure the duplicates are inserted at the end. The STORE operations will maintain the index density at 100 percent. Keys of this nature take up half the space normally taken by keys with no specified density.

File creation density options

Data compression

When creating an ISAM file, you can specify that you want your file to compress data. A repeated string of characters can be compressed to a few bytes. Compression can save from 10 percent to 80 percent of your disk space requirements for the data portion of an ISAM file (.is1), with no program changes. Records containing text fields are ideal candidates for compression. You can compress fixed-length or variable-length files but not multiple fixed-length files.

Important

If you require that your RFAs remain the same on WRITE operations, you must use static RFAs. (Static RFAs are required with Revision 6 ISAM files.) With data compression, your compressed data record size may change, causing the RFA to change. Do not use data compression if you expect a record’s RFA to remain the same after a WRITE, unless you build the file with static RFAs as well. (In other words, your response to the bldism prompt “Enter name of the ISAM file to create:” would be filename,compress,static_rfa.) This is especially true if you use manual locking.

If you use xfODBC and queries that have joins that do not use a default index (i.e., joins for which xfODBC must create temporary indexes), you must use static RFAs on your files.

Page size

You can control the size of each index page or block for each key in the index file by specifying a file page size of 512, 1024, 2048, 4096, 8192, 16384, or 32768 in the ISAMC routine. The default page size is 4096 for Revision 6 ISAM files. (For Revision 4 files, the default is 1024.) In addition to considering the operating system, the size of the largest key should also determine what page size should be used. This enables more keys to fit in a single block, thus reducing the overall depth of the key’s index. Keyed access is faster with a smaller depth, and the file is smaller due to less index at the top. Also, very large files benefit most from increased page size, though large page sizes may increase disk read time, reading data that is not needed. Increasing the page size above 4096 when the depth is at 2 or 3 will probably harm, not improve, performance. For optimal keyed READ performance, try to keep the index depth around 3 or  4, even though the CPU time required to search the larger index is increased. In most cases, the tradeoff is worth it, because CPU is faster than I/O.

When creating a file with a page size of 512 (PAGE=512), the maximum internal key size allowed is 100 bytes. The internal key size is the specified key size plus 3 if the key allows duplicates (4 for terabyte files). All keys defined for this file are limited to this maximum.

Tip

With page sizes larger than 1024, consider defining a higher index packing density. (See ISAM index density for more information.) Maximum gain from larger page sizes can only be achieved in conjunction with higher blocking factors. Ipar reports the current depth of a file, and isutl -v reports the actual density.

Change tracking

Change tracking is an internal structure maintained by each individual ISAM file. Internal control entries, or change records, are automatically recorded to a file for each file update that occurs. Updates include STORE, DELETE, and WRITE operations, as well as the Select.AlphaEnumerator.Current property set method. Periodic timing controls, called snapshots, can then be applied to manage these change records over time. These controls are transparent to normal file operation, but you can use external routines to access these controls and influence sequential operations on the file. Change records represent all user updates and are accessed as a whole rather than by individual user.

Change tracking enables you to quickly access changes made to a file over a period of time. For example, change records can be used to apply period-end figures, update an SQL database, or do anything that would otherwise require a top-to-bottom sequential scan of an entire file when only changes made during a specific time period need to be queried. Reclamation and rollback operations are allowed at file snapshot points unless the rollback function is turned off. A file snapshot can also be used as a backup restore point.

When change tracking is first applied to a file, or when the change tracking file has been cleared, an implied snapshot {#0} is applied to represent the beginning point, and the current snapshot number is set to 1. (This beginning point remains throughout the life of the file; however, as old snapshots are freed, the beginning point assumes the identity of the oldest applied snapshot.) All file inserts, updates, and deletes are logged with the current snapshot number until a new snapshot is applied using the ctutl utility (numerically identified by the current snapshot number, which is then incremented). Snapshot numbers continue to grow numerically in value until the file is cleared or re-initialized. Rolling a file back to an earlier snapshot also sets the current snapshot number back. Snapshots are freed (beginning with the oldest) and rolled back (beginning with the newest) using the ctutl utility. A maximum of 255 concurrent snapshots is allowed at one time.

Tip

When using change tracking, we recommend using frequent snapshots (daily, weekly, or monthly, depending on your transaction cycle) and then managing those snapshots by freeing old ones as they become unnecessary. Neglecting to manage snapshots will result in unwanted file growth and/or exceeding the 255 concurrent snapshot maximum.

A file’s change tracking history is accessed using the Select class. The From class Changes and NetChanges methods allow you to programmatically select all changes and net changes, respectively, made between two snapshots. In addition, the From class Snapshot method enables you to make selections against a file entirely as it was when an applied snapshot was made. Using this method, end-of-period processing can occur without suspending current file update activity.

All changes made between one snapshot and the next snapshot get recorded as net changes. In other words, if you insert a record with a STORE and then update the same record with a WRITE before the next snapshot is made, the net result is an insert with the contents as of the last WRITE. Similarly, if a WRITE occurs at the beginning of a snapshot followed by another WRITE before the next snapshot, the result is an update with the contents of the last WRITE. Lastly, if a record is inserted and then deleted during the same snapshot, the record and its change history are removed entirely. The following table summarizes these results:

Changes

Net result

Insert + Update

Insert

Update + Update

Update

Update + Delete

Delete

Insert + Delete

<none>

Note

Snapshots are maintained within the ISAM file itself. If you unload the records or convert them with fconvert, the change history is not preserved in the new file.

ISAM files must be at Revision 6 in order to use change tracking capabilities. Use the ctutl utility to manage change tracking information on the files you’ve chosen. (See ctutl for details.) Change tracking is not allowed with multiple fixed-length files.

Index caching

The Synergy runtime performs three types of caching, depending on how the file was opened.

In all cases, if the cache becomes full before a CLOSE or FLUSH is issued, the oldest blocks will be flushed.

Data file record structure

On operating systems that support large files, Synergy DBL supports individual ISAM files (.ism and .is1) of up to 256 terabytes in size. To create a terabyte file, specify the TBYTE option in the ISAMC subroutine. By default, ISAM files are limited to 2 GB.

On Windows, terabyte files are supported by Synergy and xfServer/xfServerPlus only on systems with the NTFS file system. They are not supported on FAT or FAT32 file systems.

On UNIX, some operating systems require you to set a large-file option on the file system being used. Some (AIX in particular) require this to be done when the file system is first created, and others allow the option to be added later.

When creating an ISAM file, you can declare a minimum record size of 4 bytes (5 for TBYTE files) and a maximum of 65,535 bytes minus the following overhead:

On OpenVMS, the maximum RMS record size is 32,234.

Portable storage format

Synergy ISAM storage format is the same on all Synergy DBL systems (except OpenVMS); therefore, you can copy ISAM files to any Windows or UNIX system and access them without conversion. This portable storage format also enables you to access ISAM files across heterogeneous networks.

Important

Using integer data in your records may affect portability. Integer data is not universally portable unless you define it using the I option in bldism or the ISAMC subroutine. If you use the I option, files can be moved to other machines and accessed across heterogenous networks without having to apply any conversion at the application layer.

Portable integer data can be stored in an ISAM file and retrieved portable across all platforms except OpenVMS.

Keys in ISAM files

When you create an ISAM file, you must define at least one key (the primary key) by which to access that file. You can define up to 255 keys: 1 primary key and 254 alternate keys.

A defined key can have the following attributes:

The maximum overall length of a key may not exceed 254 bytes on Windows and UNIX (251 if the key allows duplicates or 250 if the key allows duplicates and this is a terabyte file), or 255 bytes on OpenVMS.

Named key of reference

When defining a key, you can specify an optional identifying string to be used in key-of-reference specifications for Synergy ISAM file access.

Key type

Each key in an ISAM file may be made up of one or more segments of the following key types:

Alphanumeric key. The standard ASCII character set is valid for each character of the key (although the entire binary 0 to 255 range is allowed).

Case-insensitive alphanumeric key.

Note

Case-sensitive keys cannot be used on OpenVMS nor for optimization with our ODBC drivers.

Using NOCASE keys with 8-bit multinational characters is not recommended and may cause unpredictable results.

Zoned decimal key (Synergy DBL decimal data type). The valid range of values allowed for a decimal key is the maximum negative value to the maximum positive value for the size of the defined key. Implied-decimal values may also be used; however, the number of digits to the right of the decimal point must be maintained by the application.

d1 = -9 to 9
d2 = -99 to 99
etc.

Note

Decimal keys cannot be used on OpenVMS.

Native integer key (i1, i2, i4, or i8). The valid range of values allowed for an integer key is the maximum negative value to the maximum positive value for the size of the defined key.

i1 = -128 to 127
i2 = -32768 to 32767
etc.

Native unsigned integer key (with the same restraints as integer). The valid range of values allowed for an unsigned integer key is 0 to the maximum positive value for the size of the defined key.

%unsigned(i1) = 0 to 255
%unsigned(i2) = 0 to 65535
etc.

Note

Unsigned keys cannot be used for optimization with our ODBC drivers.

An i8 key that creates an automatically incrementing number between 1 and 9,223,372,036,854,775,807 that is guaranteed to generate a unique key within the file. The key’s value is generated on the initial STORE and will remain for the life of the record until deleted.

An i8 key that represents the current UTC time in microseconds. It maintains a timestamp of the last time modified. A timestamp key is set to the current time (or that of the server) on every STORE, WRITE, and optionally DELETE. To programmatically convert the i8 time to the local DATETIME, use the %DATETIME_FROM_I8 routine.

An i8 key that represents the create timestamp time in microseconds. Unlike TIMESTAMP, which changes on every record update, the CTIMESTAMP value is only set on the initial STORE of a record.

Numeric keys or key segments may not overlap or be overlapped by any other key segment (alpha or numeric). However, you may specify the same numeric key segment in more than one key.

Important

On Windows and UNIX, using integer keys (INTEGER, UNSIGNED, SEQUENCE, TIMESTAMP, and CTIMESTAMP key types) in an ISAM file requires special handling when unloading and loading is performed. Due to the chance that one of these keys contains data that could be interpreted as a record terminator, unloading to a sequential (or text) file is not recommended. Instead, we recommend using the fconvert utility to generate a counted file (-oc). (See fconvert for more information and further restrictions on text files.)

Synergy DBMS automatically fills autokeys (SEQUENCE, TIMESTAMP, and CTIMESTAMP) with the appropriate values. A sequence key starts at 1 and is incremented in sequence for every record stored until the file is cleared with ISCLR. A sequence key cannot be descending. A record with autokeys passed to a STORE or WRITE statement will be updated to reflect the autokey values that were stored or written, unless a literal was passed. This allows you to retrieve the autokey values from the record without having to reread it. An autokey must be declared the appropriate size and cannot be modified, does not allow duplicates, cannot be a null key, and cannot be segmented or be a segment in a key with multiple segments. By default, autokey values are preserved (with the exception of a timestamp key where the incoming key data amounts to an empty key; in this case, all zeros, blanks, or nulls will be generated to the current timestamp).

Note

A file that contains a timestamp key cannot be opened in update mode across a network share or NFS drive unless it’s opened exclusively (SHARE:Q_EXCL_RW or SHARE:Q_EXCL_RO). If you try to open a file with a timestamp key in update mode on a mapped drive, you’ll get a “Network share is not allowed with this file” error (NONETSHR). This restriction exists to prevent damaging the integrity of the timestamp key. Use xfServer in this situation. Note that utilities that gain exclusive access (e.g., fconvert and isutl) will allow this kind of access.

If both TIMESTAMP and CTIMESTAMP are in the same record, you can compare their values to see if the record’s original contents have been updated.

Duplicate keys

A duplicate key is a key value found in more than one record of an ISAM file. If you don’t allow duplicate key values in an index, each record in the file is uniquely identified by its key value. With duplicate keys, for example, we can define our zip code field as a duplicate key so that many different records can contain the same value for the zip code. However, if we also define a key for our customer number field, we probably don’t want to allow duplicate keys, so that there will only be one record in the index for each customer number.

When duplicate keys are allowed, you must also specify the order in which a set of duplicate key values are stored within an index and retrieved from a file. Since duplicate keys are internally unique, they are stored sequentially based on the order defined for duplicates. You can either insert duplicate keys at the end of a list of records possessing the same key value or insert duplicates at the front of such records. If you insert duplicate keys at the end, records are retrieved in the same order that they were stored in the file: “first in, first out” (FIFO) order. If you insert duplicates at the front, the first records retrieved are those stored most recently in the file: “last in, first out” (LIFO) order. The default is to insert at the end (FIFO), which is the same as on OpenVMS, where duplicate records are always inserted at the end.

For example, we can define our customer city field as a duplicate key with duplicates inserted at the front of a list of matching records. If our customer ISAM file contains five customers from Baltimore and we accessed that file by the customer city key, we’d retrieve the most recently stored customers first, as shown below:

STORE order

READS order

B. Jones

L. Peterson

C. Smith

R. Carey

A. Johnson

A. Johnson

R. Carey

C. Smith

L. Peterson

B. Jones

On Windows and UNIX, allowing duplicates adds 3 bytes (4 bytes for terabyte files) to the internal size of the key, which cannot exceed a total of 254 bytes.

Modifiable keys

If a key is modifiable, Synergy ISAM allows your application to update an existing record and change the value of the defined key using the WRITE statement.

Note

The primary key cannot be a modifiable key.

For example, if we define our customer telephone field as a modifiable key, we can change the value of this key using the WRITE statement if a customer’s phone number changes. However, we probably don’t want to define our customer number field as a modifiable key, since this value should not change during the life of the file. To change a nonmodifiable key, you must use the DELETE and STORE statements.

Segmented keys

Keys can consist of up to eight segments. The total length of the key (up to 254 characters on Windows and UNIX [251 if the key allows duplicates or 250 if the key allows duplicates and this is a terabyte file], or 255 characters on OpenVMS) is equal to the sum of the lengths of the key segments. Key segments usually correspond to fields in a record, but they do not have to be in any particular order. Segments can be defined as different types and ordered ascending or descending.

On OpenVMS, due to an RMS limitation, multiple segments of a key must all have the same order.

For example, we can define a customer address key with four segments. The first segment can be 25 characters long and correspond to our street address field; the second can be 15 characters long and correspond to our city field; the third can be 2 characters long and correspond to our state field; and the fourth can be 5 characters long and correspond to our zip code field. The total length of this key is 47 characters long.

Different alpha keys and key segments can overlap each other in a record. Numeric keys and key segments cannot overlap any other key segments unless the segment types, starting positions, and lengths are equal.

To access a segmented key, you must first construct that key by concatenating each segment together. You can use the %KEYVAL intrinsic function to return the extracted key value from the specified record.

On Windows and UNIX, partial key specifications on segmented keys are allowed when system option #45 is set.

Null keys

You can specify a null value for any key except the primary key. No entry is made in an index that is defined to have a null value if the inserted record contains the null value for that key. Therefore, when accessing a file by a null key, Synergy ISAM skips over records that contain the specified null value.

Null keys can be useful in a record that contains an optional key field. When the field is blank or contains a value of 0 (depending on the field’s data type), the field doesn’t occupy space in the index. Thus, the use of null keys reduces the size of the index file as well as the overhead time required to insert, delete, or modify a record with a null value. An index allowing null keys can only be used for limited optimization with our ODBC drivers.

You can specify one of three different types of null keys:

A replicating null key’s value must be either a decimal character or its corresponding ASCII character. This type of null key generates a null entry if every byte of that key matches the specified null value.

The following table shows some possible null values in decimal and ASCII form:

 

Null values for alpha keys

Zero

Space

Null

Decimal

48

32

0

ASCII

“0”

“ ”

“\0”

The null value for a numeric key defined as a replicating null key is always binary zero for unsigned and integer keys and decimal zero for decimal keys. A specified null_value for this key type is ignored. When defining a replicating null key on a key that has multiple segments of different types, the null_value only refers to the alpha segment (if any). If there are no alpha segments and null_value is specified, null_value is ignored.

A nonreplicating null key’s value is a string (quotes are optional). This type of null key generates a null entry if the key matches the string for the length of the string starting at the beginning of the key. Nonreplicating null keys can be defined for either alpha or numeric keys; however, the allowable value depends on the type:

A short null key does not have a specified null value. This type of null key generates a null entry if the record doesn’t include the entire key on a STORE or WRITE operation. Short null keys can only be defined for ISAM files that are not fixed-length.

Ascending or descending keys

By default, Synergy ISAM sequentially retrieves keys in ascending order (lowest to highest). When creating an ISAM file, however, you can specify that you want a particular key or segment retrieved in descending order (highest to lowest).

Key density

You can define a specific density for an individual key or keys, while leaving the rest of the keys at the default file density. See ISAM index density for more information about density.

File corruption vs. data corruption

As it applies to ISAM files, file corruption occurs when the control information in an index file doesn’t correspond to the records in the data file. Data corruption occurs when records in the data file have been unexpectedly overwritten or inserted. File corruption can be detected by running the isutl -v utility and corrected by running the isutl -r utility. When data corruption is detected, isutl fails, usually with a BADSEG error, and the file remains undisturbed. You will be prompted to run isutl again with the -a option. Any records that cannot be processed will be written to an exception file with the extension .exc.

Recovery options and strategies

When recovering data from a corrupted ISAM file, we recommend you first copy the ISAM file (both .ism and .is1). Then, if recovery should fail with one method, you can try other methods.

The file with the highest chances for recovery from data corruption is a file that employs data compression. This is because the codes used to compress the data can also be used as a roadmap to distinguish between good data and data corruption. If one or more data records near the beginning of a file are corrupted, isutl can skip them (these bad record segments are automatically sent to the exception file as they are found in the data file) and continue recovering the rest of the file. You may be able to reconstruct the lost records by examining the exception file.

Note

The exception file is a counted file and the data written is a copy of the binary data segment exactly as it was in the data file. You can use fconvert to convert the counted file to something easier to work with.

It is more difficult to attempt recovery from data corruption on files without data compression. Isutl may detect data corruption in files with variable-length records, but the only thing it can do is to write the rest of the data file to the exception file. If the index file is in good shape (with at least one good key), you may recover more data by using fconvert to recover the file.

Note

When running isutl -v on a file with corrupted data, look for keys displaying fewer index related errors or data pointer errors.

Files with fixed-length records and no data compression make detecting data corruption most difficult. The data retrieved from the data file is whatever happens to be at the stored location. If one record gets written with the wrong size, every record after it will be at the wrong file boundary. This phenomenon has been know to happen during system crashes and other abnormal terminations. Previous versions of Synergy ISAM continued storing records and making index links to new records, and the file continued to operate as normal. Ismvfy detected the problem, but irecovr wasn’t able to recover from it. The current version of Synergy ISAM checks record boundaries before it writes data. If an invalid boundary is detected, an error is produced, and you cannot extend the file by adding records to it until you recover the file using isutl.

ISAM limits

The following are the capacities and minimum/maximum limits of Synergy ISAM.

Synergy ISAM capacities and limits

Capacity

Maximum

Minimum

Keys defined per file

255

1

Segments defined per key

8

1

Length of key

254 on Windows and UNIX (or, 251 if the key allows duplicates or 250 if the key allows duplicates and this is a terabyte file)

255 on OpenVMS

1

Length of key segment

Same as defined key length

Number of records per file

Approximately 250,000,000 for nonterabyte files

The actual value varies depending on key size, index density, and available disk space.

0

Record length a

On Windows and UNIX, use this formula:

65,534 – (3 * number of keys allowing duplicates), – 2 if variable or compressed, – 6 if static RFA, – 2 if static RFA and variable or compressed

32,234 on OpenVMS

4
(or 5 for terabyte files)

Size per disk file (.ism and .is1)

231 (or 248 for terabyte files)

Combined null key size per file

1K

0

Keys per duplicate key value

16,777,216 (or 4 billion for terabyte files)

Index depth per key

16

Static RFA reuse

127

a. In Synergy .NET and 64-bit traditional Synergy, a file created with a variable record type and a maximum record size of 0 can contain larger records that are limited only by the amount of memory you have available or 2 GB.

ISAM input and output statements

The following input and output (I/O) statements can be used with ISAM files:

ISAM routines

The following system-supplied ISAM subroutines and functions enable you to manipulate ISAM files from within your applications:

Synergy relative files

Synergy relative files are used to access records by relative record number. The physical file format varies slightly on each operating system.

On Windows and UNIX, Synergy DBMS accesses records in relative files. Binary data can be in the records because all records are assumed to be of the same length followed by the record terminator, so the data is not scanned for the end of the record. If the record terminator is not found in the correct location, an “Invalid relative record” error ($ERR_RELREC) is generated. The record size can be specified either by the destination size on the first I/O statement or by the RECSIZ qualifier on the OPEN statement.

On OpenVMS, the RMS file system is used for native compatibility. Binary data can be in the records because RMS knows the length of each record and does not depend on the record terminator to separate each record. When the file is opened, the record length is automatically retrieved. If the RECSIZ is specified, the value is compared against the actual record size and the error generated is IRCSIZ if they do not match.

To support terabyte relative files, both the operating system and file system must be 64 bit.

Relative file structure

On Windows and UNIX, a relative file consists of a byte stream where the records all contain the same number of bytes followed by the record terminator. On Windows, the record terminator is a CR-LF (carriage return and line feed) byte pair. On UNIX, the record terminator is a single LF (line feed) byte. Random record positioning is accomplished by multiplying the record number minus one by the sum of the record size and the number of bytes in the record terminator to determine the byte offset from the beginning of the file.

On OpenVMS, a relative file is a specific RMS file type. Each record consists of only the data without record terminators. Each record is accessed by a record number index that ranges from 1 through 2147483647.

Relative file types

On Windows and UNIX, there is only one file type where all of the records are the same length.

On OpenVMS, relative files can either contain fixed-length records or variable-length records depending on how the record format for the file was specified when it was created. The maximum size for fixed-length records in a relative file is 32,255 bytes. The maximum size for variable-length records is 32,253 bytes. For VFC (variable-length with fixed-length control field) records, the maximum size is 32,253 bytes minus the size of the fixed-length control field, which may be up to 255 bytes long. The RECTYPE qualifier of the OPEN statement is used to specify the record format.

To create a relative file, specify the mode as O:R or A:R in the OPEN statement. To open an existing relative file, specify the mode as I:R, U:R, or A:R in the OPEN statement. The record size can be specified in the OPEN statement using the RECSIZ qualifier. On OpenVMS, the record size is stored in the file header, so if RECSIZ is specified, the record size is compared against the RECSIZ value. If the RECSIZ qualifier is not specified on Windows or UNIX, the record size is determined by the size of the destination area on the first I/O operation. If the RECSIZ qualifier is specified as -1 on Windows or UNIX, the record size is determined by the size of the first record in the file.

Record access

Records are accessed randomly by specifying the record number as a numeric field in the key field of READ, FIND, and WRITE statements, or sequentially using the READS or WRITES statements.

Relative record input and output statements

This section lists the primary input and output statements and describes how their use affects relative files. System-specific differences are also listed. See Synergy DBL Statements for more information about other statement qualifiers that are not specific to relative file access.

READ statement

read(channel, record, record_number)

You can use the READ statement to retrieve a record from the file by specifying the record number. To specify the first or last record in the file, replace the record number with ^FIRST or ^LAST, respectively. The POSITION qualifier can replace ^FIRST or ^LAST.

On Windows or UNIX, a READ of an unwritten record returns data of indeterminate contents. If the record terminator is not found in the file at the end of the fixed number of bytes, an “Invalid relative record” error ($ERR_RELREC) is generated.

On OpenVMS, a READ of an unwritten record results in a “Record not found” error ($ERR_RNF).

FIND statement

find(channel, record, record_number)

The FIND statement positions to the record specified by record_number. To specify positioning to the first or last record in the file, replace the record number with ^FIRST or ^LAST, respectively. To specify positioning to the beginning of the file (before the first record) or end of the file (after the last record), replace the record number with ^BOF or ^EOF, respectively. The POSITION qualifier can replace ^FIRST, ^LAST, ^BOF, or ^EOF.

On Windows or UNIX, a FIND to an unwritten record proceeds without error, as positioning in the file is all that occurs.

On OpenVMS, a FIND to an unwritten record results in a “Record not found” error ($ERR_RNF).

WRITE statement

write(channel, record, record_number)

The WRITE statement updates a record in the file or adds the specified record to the file. To specify the first or last record in the file, replace the record number with ^FIRST or ^LAST, respectively. The POSITION qualifier can replace ^FIRST or ^LAST.

On Windows or UNIX, if a WRITE statement specifies a record number beyond the last record written to the file when it was opened in append or output mode, the file is extended by the size required to include the unwritten records. The contents of these unwritten records are undefined.

On OpenVMS, if a WRITE statement specifies a record number beyond the last record written to the file when it was opened in append or output mode, the specified record is written to the file, and the unwritten records between the previous last record and the record just written are left as “holes” in the file.

READS statement

reads(channel, record, eof_label [, DIRECTION=Q_REVERSE])

The READS statement retrieves the record that is sequentially next in the file. When DIRECTION=Q_REVERSE is specified, the previous sequential record is retrieved.

On Windows or UNIX, a READS of an unwritten record returns data of indeterminate contents. If the record terminator is not found in the file at the end of the fixed number of bytes, an “Invalid relative record” error ($ERR_RELREC) is generated.

On OpenVMS, a READS statement skips unwritten records and retrieves the next record in the file without error.

WRITES statement

writes(channel, record)

The WRITES statement updates the record that is sequentially next in the file. If the file is opened in append or output mode, the operation adds the next sequential record to the file.

DELETE statement

delete(channel)

The DELETE statement is only available for use on relative files on OpenVMS, because RMS allows “holes” of unwritten records to exist in relative files.

UNLOCK statement

unlock channel[, RFA:match_rfa]

The UNLOCK statement unlocks any records that have automatic locks. If RFA:match_rfa is specified, the specified record with the manual lock is the only record unlocked. The RFA:match_rfa qualifier is ignored on Windows and UNIX.

%RSIZE function

size = %rsize

The %RSIZE function returns the size of the last record read, excluding the record terminator. The returned value is for the last operation, regardless of the channel used.

%RTERM function

value = %rterm

For READ and READS operations, %RTERM returns the record terminator of the last operation, regardless of the channel used.

%RECNUM function

number = %RECNUM(channel)

The %RECNUM function returns the relative record number of the last accessed record.

Record locking

When a relative file is opened in update or output mode, record locking is in effect by default unless the LOCK:Q_NO_LOCK option is specified on the OPEN statement. The FIND, READ, and READS statements unlock any previously locked record. READ and READS also lock the specified record.

Synergy sequential files

Synergy sequential files are used to access records sequentially from the beginning of the file to the end of the file. Records are not accessed randomly. The physical file format varies slightly on each operating system.

On Windows and UNIX, Synergy DBMS accesses records in sequential files. We do not recommend placing binary data in the records, as the size of each record is determined by the placement of the record terminator and the binary data can be mistaken for the record terminator.

On OpenVMS, the RMS file system is used for native compatibility. The records can contain binary data because RMS knows the length of each record and does not depend on the record terminator to separate each record. When the file is opened, the record type is automatically retrieved.

To support terabyte sequential files, both the operating system and file system must be 64 bit.

Sequential file structure

On Windows and UNIX, a sequential file consists of a byte stream where the end of each record is defined by the location of the record terminator. On Windows, the record terminator is normally a CR-LF (carriage return and line feed) byte pair but can also be a single LF byte. On UNIX, the record terminator is a single LF (line feed) byte. On either system, a record terminator can also be a VT (vertical tab) byte or an FF (form feed) byte.

On OpenVMS, a sequential file is a specific RMS file type. Each record consists of only the data without record terminators.

Sequential file types

On Windows and UNIX, there is only one sequential file type: a byte stream where the records are defined by the placement of the record terminator.

On OpenVMS, sequential files can either contain fixed-length records or variable-length records depending on how the record format for the file was specified when it was created. The maximum size of a record in a sequential file is 65,535 bytes. The records in a sequential file are preceded by two bytes that specify the length of the record, and if the record length is odd, a null byte follows the record. RMS masks this physical format of the file so only the data is stored or retrieved.

To create a sequential file, specify the mode as O:S or A:S on the OPEN statement. On OpenVMS, if the mode is specified as O or A without a submode, and the program was either not compiled with the /STREAM switch or the OPTIONS=“/STREAM” qualifier was not specified, the file is created as a sequential file. To open an existing sequential file, specify the mode as I:S, U:S, or A:S on the OPEN statement.

Record access

Records are accessed sequentially from the first record through the end of the file.

Sequential record input and output statements

This section lists the primary input and output statements and describes how their use affects sequential files. System-specific differences are also listed. See Synergy DBL Statements for more information about other statement qualifiers that are not specific to sequential file access.

READS statement

reads(channel, record, eof_label)

The READS statement retrieves the next sequential record in the file.

On Windows or UNIX, a READS statement retrieves data from the file based on the size of the destination field plus the size of a record terminator and then searches the data for the record terminator. The data up to the record terminator is transferred to the destination field and left-justified over blanks.

WRITES statement

writes(channel, record)

The WRITES statement writes the record plus the record terminator to the file at the current location. If the file is opened in U:S mode, the WRITES statement can be used for updating the record that was previously read.

FIND statement

find(channel, , , POSITION:Q_BOF)

The FIND statement repositions to the beginning of the file.

%RSIZE function

size = %rsize

The %RSIZE function returns the size of the last record read but does not include the record terminator. The returned value is for the last operation, regardless of the channel used.

%RTERM function

value = %rterm

For READ and READS operations, %RTERM returns the record terminator of the last operation, regardless of the channel used.

Record locking

Record locking occurs by default if the file is opened in U:S mode unless the LOCK:Q_NO_LOCK option is specified. The READS statement causes the previous record to be unlocked and the record specified by the statement to be locked.

Synergy stream files

Synergy stream files are used to access records sequentially or by relative record number.

Stream file structure

A stream file consists of a byte stream where the end of each record is defined by the location of the record terminator. On UNIX, the default record terminator is a single LF (line feed) byte. On Windows and OpenVMS, the default record terminator is a LF CR (line feed and carriage return) byte pair, but the record terminator can also be a single LF (line feed) byte. On all systems, a record terminator can also be a VT (vertical tab) byte or a FF (form feed) byte.

Random record positioning is accomplished by multiplying the record number minus one by the sum of the record size and the number of bytes in the default record terminator to determine the byte offset from the beginning of the file.

Stream file types

On Windows and UNIX, there is only one file type: a byte stream, where the records are defined by the placement of the record terminator.

To create a stream file, specify the mode as O or A without a submode on the OPEN statement. On OpenVMS, the OPTIONS=“/STREAM” qualifier must also be present or the file must have been compiled with the /STREAM switch to create a stream file. To open an existing stream file, specify the mode as I, U, or A without a submode on the OPEN statement.

Record access

Records are accessed randomly by specifying the record number as a numeric field in the key field of a READ, FIND, or WRITE statement, or sequentially using the READS or WRITES statements.

Stream record input and output statements

This section lists the primary input and output statements and describes how their use affects stream files. System-specific differences are also listed. See the Synergy DBL Statements for more information about other statement qualifiers that are not specific to stream file access.

READ statement

read(channel, record, record_number)

You can use the READ statement to retrieve a record from the file by specifying the relative record number. To specify the first or last record in the file, replace the record number with ^FIRST or ^LAST, respectively. The POSITION qualifier can replace ^FIRST or ^LAST. READ returns the data from the new file position to the next record terminator.

FIND statement

find(channel, record, record_number)

The FIND statement positions to the record specified by record_number. To specify the first or last record in the file, replace the record number with ^FIRST or ^LAST, respectively. To specify the beginning (before the first record) or end of the file (after the last record, replace the record number with ^BOF or ^EOF, respectively. The POSITION qualifier can replace ^FIRST, ^LAST, ^BOF, or ^EOF.

WRITE statement

write(channel, record, record_number)

You can use the WRITE statement to replace a record in the file or add a record at the end of the file. You can extend a stream file opened in update mode by specifying the record at the current end-of-file position. The WRITE statement writes the contents of the record plus the default record terminator.

READS statement

reads(channel, record, eof_label)

The READS statement retrieves the next sequential record in the file. It returns the data from the current file position to the next record terminator.

WRITES statement

writes(channel, record)

The WRITES statement replaces the next sequential record in the file or adds a record at the end of the file. You can extend a stream file opened in update mode by writing the record when positioned at the current end-of-file position. The WRITES statement writes the contents of the record plus the record terminators.

GET statement

get(channel, record, record_number)

The GET statement retrieves a record from the file by specifying the relative record number. It returns the contents of the file from the new file position for the length of the record, regardless of any record terminators.

GETS statement

gets(channel, record, eof_label)

The GETS statement retrieves the next sequential record in the file. It returns the contents of the file from the current file position for the length of the record, regardless of any record terminators.

PUT statement

put(channel, record, record_number)

The PUT statement replaces a record in the file or adds a record at the end of the file. You can extend a stream file opened in update mode by specifying the record at the current end-of-file position. The PUT statement writes only the contents of the record; the default record terminator is not written.

PUTS statement

puts(channel, record, eof_label)

The PUTS statement replaces the next sequential record in the file or adds a record at the end of the file. You can extend a stream file opened in update mode by writing the record at the current end-of-file position. The PUTS statement writes only the contents of the record; the default record terminator is not written.

UNLOCK statement

unlock(channel)

The UNLOCK statement unlocks any records with automatic locks. On OpenVMS, the blocks encompassing the locked record are unlocked.

%RSIZE function

size = %rsize

The %RSIZE function retrieves the size of the last record read but does not include the record terminator. The returned value is for the last operation, regardless of the channel used.

%RTERM function

value = %rterm

For READ and READS operations, %RTERM returns the record terminator of the last operation, regardless of the channel used.

%RECNUM function

number = %recnum(channel)

The %RECNUM function returns the relative record number of the last accessed record.

Record locking

On Windows and UNIX, the bytes encompassing the record plus the record terminator are locked when the READ or READS statement is used. When the GET or GETS statement is used, only the bytes in the file for the length of the specified record are locked.

On OpenVMS, the blocks encompassing the record plus the record terminator are locked when the READ or READS statement is used. When a GET or GETS statement is used, the blocks encompassing the bytes in the file for the length of the specified record are locked. If the BUFSIZ qualifier is specified in the OPEN statement, the number of locked blocks may be greater.

Synergy block file I/O

Synergy block file I/O is used to access files and block devices on a binary basis. All I/O to or from the file must be in multiples of 512 bytes. (For most operating systems, a minimum of 4096 bytes at a time is most efficient.) This type of I/O bypasses the file organization and retrieves or writes the raw data to and from the file or device.

Block file structure

The block submode is a way to bypass the native file organization to manipulate the binary data in a file in multiples of 512-byte blocks.

Block file types

There is no specific block file type on any system. Block file I/O is only a way to access the raw data in a file.

You can create a file in block mode by specifying a mode of O:B or A:B in the OPEN statement. To open an existing relative file, specify the mode as I:B, U:B, or A:B on the OPEN statement.

Data access

Data is accessed randomly if you specify the block number as a numeric field in the key field of READ, FIND, and WRITE statements, or sequentially using the READS or WRITES statements.

Block mode input and output statements

This section lists the primary input and output statements and describes how their use affects block I/O. See Synergy DBL Statements for information about other statement qualifiers that are not specific to block mode access.

READ statement

read(channel, block, block_number)

You can use the READ statement to retrieve records from the file by specifying block_number. To specify the first or last record in the file, replace the block number in the READ statement with ^FIRST or ^LAST, respectively. The POSITION qualifier can replace ^FIRST or ^LAST.

FIND statement

find(channel, block, block_number)

The FIND statement positions to the block specified by block_number. To position to the first or last block in the file, replace the block number in the FIND statement with ^FIRST or ^LAST. To position to the beginning (before the first block) or end (after the last block) of the file, replace the block number in the FIND statement with ^BOF or ^EOF, respectively. The POSITION qualifier can replace ^FIRST, ^LAST, ^BOF, or ^EOF.

WRITE statement

write(channel, block, block_number)

You can use the WRITE statement to update blocks or add blocks to the file by specifying the block number. To specify the first or last block in the file, replace the block number in the WRITE statement with ^FIRST or ^LAST, respectively. The POSITION qualifier can replace ^FIRST or ^LAST. To extend a file opened in update mode, specify the block at the current end-of-file position.

READS statement

reads(channel, block, eof_label)

The READS statement retrieves the next sequential block in the file.

WRITES statement

writes(channel, block)

The WRITES statement updates the next sequential block in the file. If the file is opened in append or output mode, the operation adds the next sequential block to the file.

%RSIZE function

size = %rsize

The %RSIZE function retrieves the size of the last block read. When the end-of-file block is read, size is the actual number of bytes contained in that block. The value returned is for the last operation, regardless of the channel used.

Synergy counted files

Synergy counted files are available on Windows and UNIX. They are currently only used by the isload and fconvert utilities to safely unload and reload ISAM files that contain binary data (including INTEGER, UNSIGNED, SEQUENCE, TIMESTAMP, and CTIMESTAMP keys). You may decide to unload an ISAM file to a counted file instead of a relative file because with a counted file you don’t need to specify the record size when you reload your ISAM file. For variable-length records containing binary data, we highly recommended you use a counted file.

Counted file structure

A counted file consists of record entries in format of a two-byte (portable integer) record length followed by the record itself. When the record length is odd, a null byte is appended to the record and is discarded when read. A record length of 0xFFFF indicates the end of the file.

Record access

The only access to a counted file is sequentially via isload or fconvert. The file type /COUNTED is required when specifying the file in isload, and the -c option is required when specifying the file using fconvert.

Note

Files created with record sizes greater than 64K cannot be counted files.

Input and output statements

There are currently no I/O statements in the language to directly read or write records in a counted file.