Parameters

While Database Builder can be used to maintain tables, we recommend that you use the Studio to maintain tables during development.

The Parameters tab page of the table definition dialog displays various parameters that can be used to fine-tune the performance and behavior of a table.

You must open a table in exclusive mode before you can make any changes to the parameters. See "Opening a Table" and "Switching Open Modes" for more information.

The following information is maintained in the Parameters tab page.

Table Statistics

At Least N Records

This setting only applies to embedded database tables.

The value you enter here is the minimum number of rows (the guaranteed maximum number of rows) to be stored in the database for this table. A default of 10,000 is assigned for new tables. The maximum number of rows (and thus the highest value for this parameter) that can be in an embedded database table is 16.7 million, disk space permitting.

DataFlex uses this parameter for organizing its indexes efficiently. A lower value will mean faster index performance, and vice versa.

You should set a value for each table that is sufficiently large to accommodate the expected growth of rows in the table. You should never allow the number of rows in a table to exceed this parameter; this can result in corruption of one or more indexes.

Each time you save a change to this parameter, Database Builder must rebuild the table’s indexes and will do so automatically.

When you set this parameter to 1, DataFlex will treat the table as a system table and automatically place a check in the System File checkbox.

Record Length

Record Length is the number of bytes between the beginning of one row and the beginning of the next row. If you specify a row length too short to accommodate the total length of the columns in the table definition, your choice will be overridden automatically in favor of the smallest row length sufficient to accommodate all the columns.

If you want a particular row length, you may enter it. The default length for embedded database tables is 8 bytes, so this is most often used for shorter row lengths. If you do not know the row length, the row length will automatically be increased for you and you will see a message indicating that this has happened. Once a row length has been increased automatically, it will not be decreased automatically if you delete a column or reduce its size, but you can do this manually if you wish.

The Embedded database driver can set row lengths to any of: 8–26 bytes, 28, 30, 32, 34, 36, 39, 42, 46, 51, 56, 64, 73, 85, 102, 128, 170, 256, and upward in multiples of 128 bytes, to a maximum of 16 kilobytes. These values may change for other database drivers.

Capacity

This setting only applies to embedded database tables.

This read-only statistic reports the actual maximum number of rows this table can hold, based on the value entered for the At Least N Records parameter. You can read a detailed explanation of how this value is calculated here.

Records Used

This read-only statistic reports the actual number of rows in the table. With SQL tables, you will have to request the value from the server by clicking the "Click to Get"/Refresh button.

RecLen Used

This setting only applies to embedded database tables.

This read-only statistic reports the total length, in bytes, of the columns in this table. This can be used as a comparison with the actual row length to gauge the amount of unused space in each row.

Table Revision

This read-only value is the DataFlex revision number of the database system under which the table was created. This parameter is not available for all database systems, but the Embedded database driver supports it.

DataFlex supports three Embedded database revisions: 2.3b, 3.0 and 4.0 format. The 3.0 format was introduced with DataFlex 3.0 in the early 1990s and added capabilities such as more indexes, and text and binary column support.

Once a table has been converted from 2.3 format to 3.0 format it cannot be used from a 2.3 runtime anymore. The 4.0 format was introduced with DataFlex 7 and DataFlex 3.2 (character mode) for tables with relationships to other tables with a filelist number above 250.

At the same time the 4.0 format was introduced, the DataFlex filelist was expanded to support 4095 tables, as opposed to the 255 tables of the older revisions.

When a new table is created in DataFlex, it is created as 3.0 format unless it contains relationships to tables above 250, in which case it will be created as 4.0 format.

Table Settings

This section only applies to embedded database tables.

Multi-User Reread

If rows of the table may be edited by more than one user concurrently, make sure there is a check in the Multi-User Reread checkbox. In multi-tasking environments, this box should always be checked, as DataFlex regards concurrent tasks as concurrent users.

This entry affects the Lock Type column of definition tables. When Multi-User Reread is checked, the Lock Type will become set to FILE, while an empty checkbox makes the Lock Type value NONE. Additional Lock Type values are supported for SQL database tables.

Reuse Deleted Space

For most applications, you will want DataFlex to reassign the disk space released when a row is deleted from a table. However, in applications where you want to use the row number as an audit trail or as a transaction number, this would not be desirable. To allow the reuse of deleted space, check this checkbox; otherwise, clear the checkbox.

Header-Integrity Checking

Embedded database tables store structural information about their columns, indexes, and other metadata in a special portion of the table called the header. It is possible for a header to become corrupted, either by an operating system crash or by a program that illegally writes to the header.

If a table's header-integrity checking is turned on, the DataFlex runtime and tools will prohibit access to the table whenever its header is corrupted. Each time the table is saved, information is written to an .HDR file (in the same directory as the .DAT file) which enables header inspection and repair if necessary. If header-integrity checking is turned off, the table is not checked and no .HDR file is written.

For new tables, the default for header-integrity checking is on (checked). For existing tables, header-integrity checking is off until you turn it on.

Turning on header-integrity checking causes only a very slight performance impact when the table is first opened. If header-integrity checking is on, then repair of corrupted tables can be done in Database Builder.

System File

A system table is a table that contains only one row and has its At Least N Records parameter set to one. DataFlex will automatically find and load this row upon opening the table.

Place a check in this checkbox to set this table as a system table. If the database driver supports the At Least N Records setting, this value will automatically be forced to 1 when checking this checkbox.

Compression

This setting only applies to embedded database tables.

For the embedded database, data compression can be used to reduce the disk space that is occupied by your tables. You can define a table as having no compression, or as having fast, standard, or custom compression. The default is no compression.

Compression is effective if your tables contain large Text or Binary columns that are usually not filled with data. For example, you may have a 2-Kilobyte Text column that is only half full in most rows.

When a compression method is specified, DataFlex places the compressed data in a table with the same filename as the table but with the extension .VLD (for variable-length data). The .DAT table changes to contain pointers to the data and other information necessary for the compression process.

Type	Description
None	The table has fixed-length rows only.
Fast	Fast compression uses run-length compression: any series of two or more similar characters are stored as the binary representation of the number of occurrences followed by the character (e.g., one hundred spaces -> binary(100) + space).
Standard	Standard compression is a symbolic replacement based on the frequency that characters are used in a typical table.
Custom	Custom compression is a symbolic replacement based on an analysis that Database Builder performs on the data in the table. Files that use custom compression should occasionally be re-compressed as the data in the table changes.

Transaction Processing

This setting only applies to embedded database tables.

Transaction processing is when a group of database operations must all complete or fail together to maintain database integrity. Read here for a full description of transaction processing.

There are three types of transaction processing: client-atomic, server-atomic, and server-logged. Not all database drivers offer all three types. Embedded databases (as opposed to server-based ones) offer only client-atomic transactions. Other database drivers may supply their own transaction behavior.

Type	Description
None	Do not use unless the table will only be used in programs in a very limited way, without using data dictionaries. If the table uses data dictionaries, you must utilize at least client-atomic transactions.
Client-Atomic	Can be aborted under program control and, under some circumstances (e.g., deadlock), will be aborted automatically by DataFlex. Client-atomic transactions do not provide protection from workstation crashes; if the workstation crashes while a client-atomic transaction is outstanding, part of the transaction may have already been written and cannot be automatically backed out.
Server-Atomic	Offers the same benefits as client-atomic transactions and, in addition, offers protection from workstation crashes. If a program or system crash occurs while a server-atomic transaction is outstanding, the full transaction can be automatically backed out.
Server-Logged	Offers the same benefits as server-atomic transaction control and, in addition, offers rollback capability. System rollback can restore the system to its state as of a specified point in time (affecting all transaction processing on that server after that point).

Lock Type

This setting only applies to embedded database tables.

This setting specifies how this table shall be locked when a Lock, Reread, or Begin_Transaction command is executed.

Type	Description
None	The table will not be locked. This improves overall system performance and may be used on tables to which multi-user data entry is otherwise prohibited.
File	The table will be locked. This should be the normal setting for embedded database tables used for data entry in a multi-user setting.
Record	Row locking can greatly improve performance in active, multi-user, multi-table settings. It is supported by some non-Embedded database drivers. The DataFlex client (as distinguished from the Server) does not support this lock type.

Connection Settings

File Name

If you are using the embedded database, the File Name refers to the physical name files use to store the table's data (excluding the file extension). Normally, the table name and this file name will be the same. The File Name must conform to operating-system filename rules.

In certain cases, a file name may contain a drive letter and path (up to a maximum of 40 characters in total). If it does not contain a drive and path, then the workspace Data path will be used to specify the table's location.

If you are not using the embedded database, then the filename refers to the name of the file that describes the table to the database driver. Special modifiers to the file name specify the type of driver. Refer to your database driver documentation for specific information.

SQL Settings

This section only appears if you are editing an SQL database table. Refer to the appropriate driver's documentation for relevant information.

Recnum Table

In a recnum table, individual records are uniquely identified by a record identity: a single numeric column with no decimal places that uniquely identifies a record in a table. The driver is informed about the record identity by specifying the (unique) index made up of the record identity column. Read more here.

Use Dummy Zero Date

Determines whether dummy zero dates will be used for the date columns of the table. Read more here.

Refind After Save

Determines whether the record will be refound directly after a save operation. Read more here.

System file (SQL)

A system table is a table that contains only one row. DataFlex will automatically find and load this row upon opening the table.

Place a check in this checkbox to set this table as a system table. For the embedded database, the At Least N Records setting will automatically be forced to 1 when checking this checkbox.

JIT Binding

Indicates if JIT binding is on or off for the table. Read more here.

Dummy Zero Date Value

Determines what dummy zero date value will be used for the date columns of the table. Read more here.

Table Character Format

When using the DataFlex SQL Drivers (SQL Server, DB2 or ODBC), you can choose to store your data in either OEM or ANSI format. Read more here.

Block Size

The size of the block cursor used for find operations on the table. Read more here.

Max Rows Fetched

The number of rows to limit a result to. Read more here.

Parameters

Table Statistics

At Least N Records

Record Length

Capacity

Records Used

RecLen Used

Table Revision

Table Settings

Multi-User Reread

Reuse Deleted Space

Header-Integrity Checking

System File

Compression

Transaction Processing

Lock Type

Connection Settings

File Name

Login

SQL Settings

Recnum Table

Use Dummy Zero Date

Refind After Save

System file (SQL)

JIT Binding

Dummy Zero Date Value

Table Character Format

Block Size

Max Rows Fetched