File Format Conversions

Converting Micro Focus ISAM Database to Btrieve

If you are using a Micro Focus ISAM Database, the first thing you need to do is to convert your ISAM files to Btrieve format since Elliott V7 does not support a Micro Focus ISAM database.  Before you run the utility to convert your ISAM database to Btrieve, please make sure Pervasive SQL (2000 or higher) is already installed.  In the directory where you installed Elliott V7 (which will be the same directory that your V6.6/V6.7 data resides), you can use the following command to convert ISAM files to Btrieve format:

            CONVERT DATA

CONVERT is the batch file utility and DATA is the sub-directory where your ISAM database resides.

Convert Btrieve Database to be PSQL Compliant

If you were using a V6.6/V6.7 Btrieve database before or if you were previously using a Micro Focus ISAM database and just converted to Btrieve format, your Btrieve database may not be PSQL compliant.  Report writer or ODBC drivers may not always able to read your data and erroneous results may happen.  This condition is caused by an issue called “Segmented Keys.”  To illustrate this issue, let’s take a look at the COP Order Line Item file.  The primary key for this file consists of two segments:

            Order-No           6 Bytes

            Line-No             3 Bytes

If your Btrieve database shows this file’s primary key as one segment of 9 bytes, then it is “non-segmented.”  It will work fine with the Elliott Business Software, however it may not always work when other applications try to access this database through the PSQL relational database layer.  To resolve this problem, we provide a utility (SQL2BTR.EXE) located in the Elliott root directory (i.e. F:\ELLIOTT7).  To convert the Btrieve files that are in a non-segmented key format to one that is segmented, start a command prompt and from the root directory of Elliott type the following command:

            SQL2BTR DATA

SQL2BTR is the EXE utility and DATA is the sub-directory that contains the non-compliant Btrieve data files.  Please make sure the DDF files (you can find them in \ELLIOTT7\DDF40 directory) are in your data directory before you start the conversion.  Also, PSQL (Version 2000 or higher) client or Workgroup engine needs to be installed before you convert.

Elliott Transaction Control

Application Overview

This section explains how Elliott has implemented Transaction Control (TRX Control) into Elliott Software. TRX Control prevents corruption of major Elliott data files if the system fails during a posting, purging or clearing accumulator operation. When enabled, TRX Control views the operation as a single transaction. If the operation is completed successfully then TRX Control ends the transaction and the Elliott data files are updated. If an error is encountered or the system fails during the operation, TRX Control will perform an “automatic rollback” of the data files updated during the operation. Once the system is functioning again, TRX Control will undo all data file changes made by the operation and return the data files to their original state. When the problem has been corrected then the unsuccessful operation may be run again.

Elliott Software uses explicit transaction tracking. This means that Elliott automatically controls when each transaction is started and ended. Elliott Software will use TRX Control during most posting, purging and clearing of accumulator operations. However, because of design, some purging operations will not use TRX Control.

Trx Considerations

There are basically two considerations that need to be taken into account as to whether TRX Control should be used with Elliott Software. The first consideration is disk space available. When a transaction is started, TRX Control will build a transaction rollback work file that is used until the transaction is aborted or ended. For example, a 2-megabyte transactional data file being updated would require an additional 2 megabytes for the TRX Control work file. TRX Control transaction work files are reused. However, there is a chance that the volume on which the transaction work files are kept will run out of space. The second consideration is performance on transactional data files. There is additional overhead associated with transaction control; therefore, if TRX Control is enabled, the performance of some Elliott Software applications will be affected.

Potential Problems With Trx

The following are potential problems or limitations that may occur if Trx Control is in use with Elliott Software:

•           Transaction control cannot guarantee data file integrity if there are disk failures.

•           Transaction control cannot span across multiple file servers.

•           Performance may be hindered if transaction control is used.

Furthermore, large transactions, like G/L year end closing, may cause the server to crash.  Since G/L year end closing will purge out all prior year data and create a balance forward transaction for each balance sheet account, the process either has to completely finish or back out entirely.  There is no middle point.  If your GLTRXFIL is relatively small (a few meg), you might be OK with trx control on.  However, if your GLTRXFIL is a 50 Meg file, the process to create the rollback file may become very inefficient toward the end and your server may simply running out of resources and crash.  Therefore, you should turn off Trx Control before you start G/L year end closing procedure.  The following are areas that you should turn off Trx control:

General Ledger Year End Closing

Inventory Physical Count Posting

Posting a single trx with more than 500 serial numbers.

Transaction Backout

An active transaction is backed out by the system if the file server fails, the workstation fails or the Elliott application encounters a data file error while the transaction is in progress.  The following can cause a transaction backout:

•     Elliott Software encounters a data file error or the Elliott application is terminated abnormally.  This can happen if the user enters CTRL-Break.

•           The workstation fails or is re-booted.

•           There is no activity between the workstation and the file server for 15 minutes.

•           A CLEAR STATION command issued at the file server console.

•           A DOWN command issued at the file server console.

•           The file server fails or is re-booted.

Failure Recovery

When a crash occurs in the middle of a transaction, the user may not know exactly where the crash occurred and which transaction was backed out.  It is the responsibility of the user to verify the last operation attempted and take any steps necessary to successfully complete it.

Before any workstation can re-enter the Elliott Software system, the transactional flag of the data files must be reset.  Elliott has provided the user with a batch file that will reset all the data file flags.  Follow the steps below to reset all data file flags.

1.                   If the file server did not fail, log out all Elliott users.

2.                   One user should then log into the network as an Elliott user and change directories to the directory where the Elliott system data files are installed.

3.                   At the system prompt, type RSETFLAG<data directory>.  For example, to reset the ELLIOTT\DATA directory, you would type RSETFLAG DATA and press RETURN.  This step is not necessary if you are using the Btrieve file handler.

4.         All Elliott users can now log back into the network.

Installing Trx

The following procedures must be followed in order for the Elliott Software to use transaction tracking:

The Trx Control Flag in Company Setup must be set to Y.

If transaction control is being used, posting applications will be able to access files used by other applications.  Instead of informing the user that the file is currently in use, posting will proceed until a locked record is encountered.  At that point the application will wait until the record is unlocked and then continue.  A possibility of deadlock may happen.  When that happens, we suggest you abort the less critical process (non-posting process) and log back in.  If that does not resolve the record lock situation, you might need to use the Pervasive Control Center utility to clear out the aborted user connection.  If that still does not resolve the record lock problem, then you will have to abort the posting process as well.  Since trx control is on, you should not loose any data when you log back in again.  However, if your post routine still leaves the files open, you will need to use the Pervasive Control Center utility to clear out the connection.

Record Locking

Record locking means temporarily reserving an individual record for access by only one user.  In normal use, this lock is in place for only a few seconds.  An example is the updating of inventory during order entry.  Only one person may allocate any individual inventory item at a time.  Individual inventory items are temporarily locked until they are allocated to line items on an order.  If two data entry persons try to order the same inventory item at the same time, one of the individuals will receive a record locked message.

Record lock messages Vary in both appearance and user response. Examples are:

              Ship Via Code Locked At Another Station – Press RETURN

  A Locked Record Is In The Way Of The Next Operation – Press RETURN

  Record Is Locked At Another Station – Press RETURN

  Customer No XXXXXX Locked At Another Station

  Customer No XXXXXX locked by XXXXXXXXXX

Some programs will allow you to make another selection after pressing the RETURN key in response to the above messages.  Other programs will redisplay the message until the record is cleared.  Users should never leave their station when data is being entered because they may leave a record locked.  If a station is to be left unattended, the user should make sure only a menu is being displayed.  Other examples of locked record messages are:

A Locked Record Is In The Way Of The Next Operation…Please Wait

This message will display until changes to the record are completed at another workstation.

Account Locked At Another Station – Will You Wait ? N

If you answer Y to this question, you will get the same question again until the record clears.  If you answer N you will have an opportunity to view another record.

Customer No XXXXXX Locked At Another Station

Some procedures that process a range of records or an entire data file, like Set Customer Balances, will notify you which record is locked.  Generally, these procedures must continue to process and will check every second to see if the locked record has been released.  By supplying the record information, you can determine who has that particular record locked and ask them to release the record.  Once the record is released, processing will continue.

Customer No XXXXXX locked by XXXXXXXXXX

This is similar to the above lock message, but has been updated to include the user ID.  Once the record is released, processing will continue.

File Locking

File Locking means temporarily reserving an entire file or files for access by only one user.  Setup files and the Company file are always locked when they are updated.  An example of a file lock message is:

Company File Presently In Use

This will be displayed in a window when you try to access an application that needs this file while it is being exclusively used by another application.

The most frequent display of file lock messages is in the use of posting applications.  These programs need exclusive use of certain data files because of the extensive amount of updating that is being done.  We recommend you Defer your posting until after your nightly backup has completed.  By utilizing the Deferred Processing application, the files will be posted at a time when it is convenient to your company.  See the Deferred Processing section of this manual.

If the Btrieve file handler is being used and the Trx Control Flag in Company Setup is set to Y, posting applications will be able to access files that are being used by other applications.  Normally, the File In Use message would be displayed.