Configuring the Datafile System

Adding the DFSQL User

In operation the Datafile SQL Driver accesses the Datafile System with a DFSQL user-id and this user-id needs to be added to the list of user-ids within Datafile as entry 90.

Set the User-Id, Full-Name and Password as DFSQL – leave all other options blank / unset. For multi-connection purposes you may also want to define entries 91-99 as DFSQL0 through to DFSQL8 – setting the password equal to the user name. If the system reports that DFSQL is in use then will move on to DFSQL0 and so on.

Setting a Database to be mirrored in SQL

When restructuring a Database press <F4-Select> and choose ‘Advanced Features’

Mirror this db in SQL – set to Y for the database to be mirrored.

Refresh Structure / Content – selected processes will perform a Database Refresh rather than output the individual record add/update/delete entries to the SQLDET database to improve processing speed. This option asks whether these refreshes dropt the table and re-create or empty the table and re-output, the latter may be desirable if you modify the SQL table to include additional items. Select as required – note though that Database Restructure will always drop the table and recreate.

Once set all data items will be mirrored by default – if you want to restrict the items mirrored then, from the data items list, you press <F4-Select> again and choose ‘Database INI Options’

If want to mirror most items set the tick box on the ‘ALL Select’ line and then switch off the items you do not require, otherwise tick the individual items required. We suggest that you do not take pointer items across – FIRST-TX, LAST-TX etc – particularly when mirroring transaction / detail files. The SQL option button is not relevant for SQL Output.

When restructuring a database press <F4-Select> and choose Database INI Settings before selecting the SQL button.

Here you may the Datafile Database item to the SQL table item by recording the SQL table name followed by the item name.

Certain items may require extra detail, for example dates, so that correct formatting for Datafile databases is maintained.

SQL Maintenance Options

Within the Datafile Database Utilities menu you may have a menu option SQL Maintenance Options which provides tools for viewing and refreshing the Datafile Databases mirrored to SQL.

List SQLDET Entries –performs a Datafiler List Records on the SQLDET.DFD. When selected you will be prompted if you wish to omit updated (ERR-STAT equals D-Done) and error (ERR-STAT equals E or EO) entries.

Refresh All SQL Databases – triggers refresh of all mirrored Datafile databases. When selected will prompt if wish to cleardown and compact the SQL database (this clears entries for the mirrored database – whether marked as done or not – before writing the refresh entry)

Refresh A SQL DB (Delete / Recreate) – allows manual refresh of a Datafile Database to SQL. This option drops the SQL table and then recreates the table.

Refresh A SQL DB (Erase / ReOutput) – this refreshes the content of the SQL table from the Datafile Database but not the structure.

Cleardown SQLDET Entries – this option deletes any SQLDET entries marked as D-Done and optionally clears any entries with an error status also.