Options dialog box: Table tab

You can use the options in the Table tab to specify how ACL processes tables.

Automatically Profile on Open

When the Automatically Profile on Open option is turned on, ACL automatically executes the Profile command on all numeric fields when you open a project, change tables, or change global filters.

ACL retains the information and uses it to provide minimum and maximum values for histograms and stratifications, as well as absolute values for monetary unit sampling.

Note

Tables with numeric fields will open more slowly with this option on.

Delete Data File with Table

If you turn this option on, ACL automatically deletes the associated data file when you delete a table. You can use this option to quickly remove unwanted files from your hard disk, but it will prevent you from accessing the data in the future.

Note

Use this option with caution. It may be an original data file that is deleted along with the table.

Don’t Share Table Layouts
Note

To prevent accidental deletion of field definitions, the Don't Share Table Layouts checkbox is selected by default.

If you deselect this option, a single table layout can be shared by multiple data files or data sources with an identical record structure. The feature works with only those ACL operations that can output results to an ACL table with an identical record structure – extracting, sorting, sampling, and merging – and with copying table layouts.

When sharing of table layouts is permitted, multiple source data files (e.g., ACL data files (.fil)) or data sources that have the same record structure share a single set of field definitions. When you add a physical or computed field to a shared table layout, add a column to an associated view, or add a view, the added field, column, or view is automatically added to all the ACL tables that use the shared table layout. When you delete a field, column, or view, it is no longer available to any of the ACL tables that use the shared table layout.

Generally, you should maintain a separate table layout for each data file. However, sharing a single table layout can save labor if multiple data files with the same record structure require an identical set of field definitions, and any updates to the table layout will apply to all the data files. For example, extracting records from an annual transactions table into twelve separate monthly tables produces a total of thirteen tables with the same record structure. If the Don’t Share Table Layouts checkbox is selected, each table has its own layout. If the Don’t Share Table Layouts checkbox is deselected, all the tables share the original table’s layout and the layout can be managed centrally.

Deleting a shared table layout from one of the tables that uses it does not perform a global deletion. The shared table layout is still available to the other tables that use it.

Sharing does not extend beyond individual ACL projects. If you copy a table to another project, a new table layout is created, regardless of how Don’t Share Table Layouts is set.

Exact Character Comparisons

When this option is off, ACL compares character fields, expressions, or literal values based on the shorter of the two comparison strings. For example, “AB” is equal to “AB”, and it is also considered equal to “ABC” and “ABLMNOP”. The comparison starts with the leftmost character and moves to the right.

If you turn this option on, ACL compares character fields, expressions, or literal values based on the longer of the two comparison strings. For example, “AB” is equal to “AB”, but it is not considered equal to “ABC”. When this option is on, comparison strings must be exactly identical to constitute a match.

Leading or trailing blanks are treated like characters whether Exact Character Comparisons is off or on:

  • When Exact Character Comparisons is off, trailing blanks can affect the result of comparisons. For example, “AB” in a field with a length of 2 bytes (2 character positions) is equal to “ABC”, but “AB” in a field with a length of 3 bytes (3 character positions) is not equal to “ABC” because the blank in the third position is not equal to “C”.

  • When Exact Character Comparisons is on, ACL pads the shorter string with blanks so that the two comparison strings are an identical length, which has the effect of factoring out any trailing blanks in the longer string. From a practical standpoint, you can ignore trailing blanks when Exact Character Comparisons is on.

If required, you can use the TRIM( ) or ALLTRIM( ) functions to remove trailing blanks, and the LTRIM( ) or ALLTRIM( ) functions to remove leading blanks, and ensure that only text characters are compared.

The Exact Character Comparisons option affects how filters work. For example, if the option is off, Address = "PO Box" would return all addresses that start with “PO Box”. If the option is on, Address = "PO Box" would return only those records that have the exact string “PO Box” and nothing else in the Address field.

Some ACL operations and functions are affected by the Exact Character Comparisons option and some are not. The Locate If operation, and the MATCH( ) and BETWEEN( ) functions, are affected by the option. The join and relate operations, and the FIND( ) function, are not.

Changes to this setting are recorded in the log using the following syntax:

SET EXACT {ON|OFF}

Display Format on Open

If you turn this option on, ACL automatically displays the current table layout and computed field definitions when you open a new table. The results appear in the command log.

Changes to this setting are recorded in the log using the following syntax:

SET FORMAT {ON|OFF}

Define Flat Files Manually

With this option selected, certain screens of the Data Definition Wizard are skipped when you create a table from a flat file and you complete the data definition in the Table Layout dialog box.

Buffer Size

This option specifies the size of the data block read. The default is 33K (kilobytes), which is the recommended buffer size for most applications.

Acceptable values range from 5 to 255. Changing the buffer size may provide small performance improvements in certain environments. You should only change this setting if you are advised to do so by ACL Support Services.

Changes to this setting are recorded in the log using the following syntax:

SET READAHEAD value

Sort Memory

This option specifies the maximum amount of system resources to be allocated for sorting and indexing processes. The sort memory can be any value from 0 to 2000MB (megabytes), in 20MB increments. To optimize Sort performance, set the sort memory according to the available physical memory in the system. This enables ACL to use the necessary amount of memory to sort a table up to this maximum, if required.

If the sort memory is left as 0, ACL uses the system resources currently available.

Sort Order

This option sets the sort sequence for character fields.

Choose the locale from the drop-down list. The default is “System Default” for the non-Unicode edition of ACL and “Mix Languages (UCA)” for the Unicode edition. By default, ACL sorts data in ascending order based on the byte order of each character in its character set. The Sort Order option affects sort order when sorting or indexing, or performing a quick sort, and when testing sequential order.

Changes to this setting are recorded in the log using the following syntax:

SET ORDER values

Related tasks
Configuring ACL options


(C) 2015 ACL Services Ltd. All Rights Reserved.