ISAM SQL access

FairCom RTG/ISAM offers more than just a high-performance file system. The FairCom RTG/ISAM SQL database engine opens a new and efficient method to access your data from many other applications via SQL. By avoiding the overhead imposed by many hybrid SQL implementations, FairCom RTG/ISAM gives you the most direct access available.

Btrieve data files do not explicitly define a record schema, which is mandatory for SQL. If you want to provide SQL access to this data, you will need to define the record schema through an XDD (eXternal Data Definition) file. The XDD is an external XML file that is stored as a special resource within the data file created through the processes described in this chapter.

FYI: If your data file contains more than one record schema (REDEFINES), FairCom RTG/ISAM provides an ability to define multiple record schemas, each of which will appear to SQL as a virtual table. See the FairCom DB API Virtual Tables technical white paper on the FairCom website.

The procedures in this chapter are optional: they are needed only if you want SQL access to your Btrieve data. There are two main parts to this process:

1) Create an XDD

First, you will define the record schema of your data with an XDD file. The method you will use depends on the compiler you are using:

• Creating an XDD from a DDF: If you have a Data Dictionary File (DDF), you can generate the XDD from it using a ctutil option.
• Creating an XDD Manually: If you do not have a DDF, you can manually create an XDD. Users of FairCom ISAM who desire SQL access to their data will need to use these procedures.

2) Store the XDD in the data file and link it to the SQL dictionaries

• Storing the XDD in the Data File: Next, you will store the XDD in the file and link it to the FairCom DB SQL dictionaries using a ctutil option.
   

Note: In some cases, these procedures will not produce the desired results because your data may be structured in a way that cannot be interpreted correctly. In those situations, you will need to add directives to your copybook to handle the data correctly or you can use the procedures in Creating an XDD Manually.

The FairCom RTG/ISAM SQL Challenge

Many COBOL ecosystems support a separate relational database for SQL requiring batch copy data into the relational database, resulting in out-of-sync data - a D+1 or D+7 constraint. The nature of data and record definitions (which may have redefines) used in these languages does not fit into a traditional relational (SQL-based) model of table and record definitions, which poses a unique challenge.

The FairCom approach is different. Our engineering team implemented the required interfaces through our ISAM technology. These interfaces parallel the Btrieve approach to indexed files without the complications of SQL table remapping. From the application's point of view, FairCom RTG/ISAM provides a more advanced “native” file system with no limitations for the application programmer. In addition, the interface between the FairCom DB SQL engine and the native data allows concurrent read/write access through standard SQL.

FairCom ISAM provides concurrent access to data using SQL in addition to Btrieve direct record file I/O routines. This provides a truly flexible environment allowing you to precisely choose data access interfaces balanced for performance and information demands. While some data conversion is required for SQL access, the FairCom SQL approach is far simpler and more efficient than the remapping required by other implementations. The advantage is your original application maintains direct access to the data with no added complexity.

Supported SQL Features

The FairCom ISAM product allowS your Btrieve data to be exposed through SQL, giving you full access to read, write, insert, and delete records. This support is targeted at the most important SQL functionality and includes:

• Reading and writing and updating records, concurrent with direct ISAM access
• Advanced joins across tables
• Views
• Standard table, column and view access permissions.
• User-defined scalar functions
• Stored procedures
• Triggers

Notable Limitations

There are multiple considerations when combining SQL.with RTG.

• A table created through SQL cannot be accessed using ISAM drivers for Btrieve.
• Referential integrity is applicable at the SQL level only; no check is performed to ensure referential integrity.
• SQL triggers are applicable at the SQL level only; your FairCom ISAM program cannot invoke a SQL trigger.
• If transactions are disabled on Btrieve files, this creates additional considerations when accessing these files via SQL. For example, rollbacks do not affect these files; transaction isolation is not provided; etc.

Developing Stored Procedures, Triggers and User Defined Functions

Stored Procedures, triggers and user defined functions are supported with Java across all platforms giving you nearly complete access to any Java class framework you can include. FairCom recommends developing within our NetBeans plug-in (found in the sql.stored.procs folder), which eases Java stored procedure development, debugging, and deployment.

For Microsoft Windows environments, the .NET framework can be used with our Microsoft Visual Studio plugin.

Creating an XDD from a DDF

If you have a Data Dictionary File (DDF), it can be used to create the XDD required for sqlizing your data using ctutil -ddf2xdd.
 

The DDF is a detailed file definition including field type information and is contained in a Btrieve data file. There are usually multiple DDF files associated with a data file, and multiple file descriptions are included in a DDF definition file. To use these files you must first unload them. You will need your existing butil file maintenance utility to unload and transform this data before FairCom RTG/ISAM can use it.

You need to generate file.sav, field.sav, and index.sav files before you begin creating an XDD. These three files should be generated starting from file.ddf, field.ddf, index.dff using the butil command as follows:

butil -save FILE.DDF file.sav

butil -save FIELD.DDF field.sav

butil -save INDEX.DDF index.sav

Once created, you can then proceed with the ctutil utility.

You can specify a rules file to fine-tune the creation of the XDD, as described in Defining External Rules.

After you use ctutil -ddf2xdd to create the XDD, use the procedures in the section titled Storing the XDD in the Data File to prepare your data file for SQL access.

Create an XDD Manually

If you do not have access to an external file definition such as an XFD (COBOL), a DDF (Btrieve), your Btrieve program source code, or create an XDD manually with an editor.

Users of FairCom ISAM who do not have a DDF can use these procedures if they desire SQL access to their data.

The XDD file can be created using any XML editor.

To manually create an XDD, you need to know the structure of your data. You then need to translate that structure into an XDD. The XDD is explained in the section titled XDD Schema Structure.

After creating the XDD, use the procedures in the section titled SQLIZE: Persist XDD to Data File and Link to SQL Dictionary to prepare data files for SQL access.

Storing the XDD in the Data File and Linking to the SQL Dictionary

After creating an XDD, the information in that file will need to be stored in the data file and it must be linked to the FairCom DB SQL dictionary.

Use the ctutil -sqlize command to make an existing data file accessible from SQL by storing the XDD in the file and linking the file name to the FairCom DB SQL dictionaries.

After You Have Sqlized

After you have stored the XDD in the data file, you have finished sqlizing and you are ready to take it for a test spin. Experiment with some sample data to see if the process yielded the expected results. Be aware of two factors that may affect the results:

• Some Btrieve fields do not translate well to SQL. For example a text field may be used to store a date in a human-readable format that has no meaning to SQL.

FairCom RTG/ISAM provides tools to help you fine-tune your installation to overcome these problems. In particular, review Troubleshooting Data Conversion Errors.

You will find an abundance of useful information in this guide, such as FairCom RTG/ISAM Errors, XDD File Schema, and Background Information about Sqlizing. Advanced users may want to consult API for SQL Conversion Error Checking.

Type Mapping Table

To define an XDD file requires defining the data type of each column of the original data file. The following table describes the accepted XDD data types:

See also <schema> table element.

Variable-length fields mapped into LONGVAR* SQL field

Variable length fields map into SQL LONGVAR* fields.

The XDD structure allows the following elements and attributes for variable length field support.

1. dbtype values:
   • BLOB: Indicates a variable-length binary object with length depending on a field value.
   • CLOB: Indicates a variable-length text object with length depending on a field value.
2. <field> attribute sizefield used in conjunction with dbtype BLOB or CLOB and having size = "0"

For an XDD field mapped into a LONG VARCHAR or LONG VARBINARY, the following conditions must be met:

1. The field definition must have:

size="0"

sizefield="X" where "X" is a valid field containing the number of bytes (we suggest this field to be hidden, but this is not mandatory)

dbtype="clob" or dbtype="blob"

1. At maximum, one and only one field mapped to a BLOB or CLOB type.
2. It must be the last field in the record buffer.

If one (or more) of the above condition is not met, error CTDBRET_CALLBACK_11 ("Unsupported clob/blob definition") is returned.

Troubleshooting Data Conversion Errors

The fact that the XDD initially does not contain any error handling information immediately exposes data conversion errors to a SQL user. This provides the way to begin troubleshooting data conversion errors and to identify the proper settings to specify in your XDD file.

Visually Check Data with FairCom DB SQL Explorer

FairCom DB SQL Explorer and FairCom DB Explorer include a button to simplify identifying "bad" records.

To check for bad records in FairCom DB SQL Explorer or FairCom DB Explorer:

1. Select a sqlized table, such as custmast in the image above.
2. Click the Table Records tab.
3. A button labeled Check Bad Records appears at the right of the row of buttons (the image above shows the Java version of FairCom DB Explorer where the buttons are at the top of the tab; the .NET version, called FairCom DB SQL Explorer, shows this row of buttons at the bottom of the tab).
4. Click the button to execute a SQL query to find records that did not sqlize properly.

Check using a SQL Query

You can use the procedures in this section to identify data-conversion errors and use that information to fine-tune your XDD files.

If a query fails, it is possible the failure is due to a problem with SQL data conversion. Troubleshooting this type of error is quite easy with the following steps:

1. Identify the table (in case of a complex query) on which the conversion error occurs by running the following SQL statement on each table involved in the query:

SELECT * FROM <table>

If none of the queries fail, the original query failure is not due to a conversion problem.

1. Run the following SQL statement to select only the records that do not properly convert:

SELECT * FROM <table> ctoption(badrec)

ctoption(badrec) is a FairCom DB extension to SQL indicating the query should return only records having conversion errors and expose values that do not properly convert as NULL.

1. Look for NULL values returned from the query in step 2. These are the fields that do not properly convert. The remaining record values should be sufficient to identify the record that requires investigation.

CTSQLCBK.FCS Log

The ctoption(badrec) command generates a log file, ctsqlcbk.fcs, in the FairCom DB SQL Server directory that can be used to determine the exact conversion error and the data causing it. This file lists all the fields that caused a conversion error exposed in SQL along with the value of the data that could not be converted. Note that the log contains information for the fields that result in propagating the conversion error to SQL. It does not log conversion errors that result in a SQL value because they were already handled successfully following the settings in the XDD.

Each log entry is made of three lines:

1. The first line is similar to the following:

Convert error XXXX on field[YYYY]

Where XXXX is the error code indicating the cause of the conversion error, YYYY is the field on which the conversion error occurred.

1. The second line contains a message which gives internal information for FairCom technicians to identify where the error occurs in the code, as well as a message explaining the problem.
2. The third line is a hexadecimal dump of the field content.

For example:

Convert error 4028 on field[FIELD1]

{ctdbStringFormatToDateTime} on 0000000000 failed

00000000

Convert error 4028 on field[FIELD2]

{ctdbStringFormatToDate} on 00000000 failed

3030303030303030

Convert error 4118 on field[FIELD3]

[NormalizeCobolStr] ascii value is not a digit

2020202020202020

Convert error 4118 on field[FIELD4]

[NormalizeCobolStr] ascii value is not a digit

2020202020202020

Viewing Sqlized Tables in FairCom DB SQL Explorer

FairCom RTG opens your COBOL data to the world of SQL access. FairCom knows you will want to monitor and manage this data—and that means seeing it the way it appears to SQL applications. FairCom DB SQL Explorer and FairCom DB Explorer are graphical tools that allow you to view and manipulate your data, providing comprehensive management capabilities.

FairCom DB SQL Explorer and FairCom DB Explorer are client tools designed to interact with FairCom RTG/ISAM Servers through the TCP/IP SQL port. Using the SQL language, they give you access to all the items in your FairCom DB database. They also provide the ability to execute single SQL statements and SQL scripts including options for viewing the execution plans.

FairCom DB SQL Explorer and FairCom DB Explorer show linked tables in a different color (a reddish orange). Tables that were also sqlized have a jagged "S" (or lightning bolt) to indicate they were sqlized and linked. The custmast table in the image below is a sqlized and linked table:

When you right-click a table, the context menu will display only the options that are available to that table (all other options are dimmed), as shown in the image above. Some options available to regular tables, such as Alter Table, Clone, and Constraints, are disabled because they are not available for sqlized tables.

Adding SQL Indexes to Sqlized Files

FairCom ISAM allows you to create a SQL index on your COBOL tables. You can execute CREATE INDEX on imported tables or you can use the graphical tools, FairCom DB SQL Explorer and FairCom DB Explorer.

Performance of SQL Queries

A practical use of this feature is in handling COBOL indexes that do not translate well to SQL. In some cases, the index is imported into SQL by sqlize with limited functionality, so it can be used only to perform searches using the "=" and the "<>" (not equal) operators. In a small number of cases, the index cannot be sqlized at all.

These cases may impact the performance of SQL queries on those fields. This issue is now easily addressed: simply create SQL indexes where needed to speed up queries. It is not necessary to replace every COBOL index, simply replace those that are needed to speed up your SQL queries.

Note: A SQL index cannot be created on COBOL "bit" data types (Boolean).

Date/time types (date, time, datetime / timestamp) can have additional indexes created if their type definitions are in collation order (YYYYMMDD for dates).

The presence of a SQL index will better optimize some queries. As a side effect, the index returns rows sorted in logical order as opposed to binary order as is the case for COBOL indexes.

Creating an Index in FairCom DB SQL Explorer

Several options are available for creating an index, such as creating with a direct CREATE statement in ISQL or using FairCom DB SQL Explorer.

To create a new index for a table using FairCom DB SQL Explorer, click the table name to see the group labeled Indexes, right-click on the group, and select Create from the context menu. The following window will appear for creating the new index definition including defining index columns (segments):

Create Definition Controls

• UNIQUE - Check this option if you don't want your index to contain duplicate values.
• Index Name - Enter the name to be assigned to the new index.
• Table Name - This box will display the table name receiving the new index. If you access this dialog from a specific table, the table name will be displayed read-only. If you access this dialog from the Index group under a user, select the table from the drop-down list.
• Storage_Attributes “Partition” - This check box adds STORAGE_ATTRIBUTES "PARTITION" to the SQL statement. This creates the index as the partition index to the table enabling multiple table partitions.

Column Definitions

Use the controls in the Columns group to define the columns composing your new index. New lines can be added by filling the line marked with the asterisk:

• Column - Click inside the Column drop-down list to select the column name to be included in your new index. If your index will be built over multiple columns, continue this process until all columns are listed.
• Desc - Check this box if you want the column to be sorted in descending order. Otherwise an ascending sort is the default.
• - Use this button to move a column up the index column list. Note, the column in the top of this list will appear first (to the left) within the index.
• - Use this button to move a column down the index column list.

To delete an index column: Select the row header that contains the column to be deleted and then press the Delete key on your keyboard.

Resulting Statement

The Resulting Statement window will show the CREATE statement to be executed for building the new index. Once your index is completely defined, press the Create button to create your index and remember to check Result in the left corner of the status bar at the bottom of the window for either Success or an error message. Once you see Success in the status window, click on exit to return to the main window.

You can save the CREATE statement shown in the Resulting Statement window by clicking Save Statement or using the File menu or pressing CTRL+S.

Finishing Up

Save Statement - Click this button if you want to save the SQL statement so you can execute it at a later time.

Create - Click this button to create the index.

Exit - Click this button to close this dialog.

Opening Your File

Once you update your index definition, the default configuration of your COBOL application probably won't open the file (FS 39), indicating there is a mismatch between the number of indexes indicated in the FD and the real file. COBOL runtimes generally allow this behavior when so configured depending on your COBOL environment.

• EXTFH Interfaces: The FairCom RTG/ISAM <keycheck> option can be used to open the file even if it contains more indexes than the key definitions.
• AcuCOBOL Include the environment variable EXTRA_KEYS_OK in your runtime environment
• isCOBOL Configure you isCOBOL runtime configuration propriteis file with iscobol.file.extra.keys.ok = true
• The new index will be updated regularly with updates from the COBOL program, even without the changes in the FD, so configuring with <keycheck> is recommended.

Preserve Imported Data Files upon SQL DROP

FairCom DB SQL brings many advanced SQL capabilities to application data that was not originally created in SQL. Legacy application tables can be linked to SQL. FairCom DB SQL now defaults to always removing linked physical data and index files when performing a DROP from SQL as expected from SQL standards. However, there are frequently cases where removal of the data file is not appropriate nor expected. Rather, it is best to only remove the SQL system table linkage entry. A new configuration option is available to preserve the physical files.

SQL_OPTION DROP_TABLE_DICTIONARY_ONLY

Background Information about Sqlizing Btrieve Data

This section contains important information that should be kept in mind with adding SQL access to your Btrieve data.

FairCom RTG/ISAM Extended Data Definitions - XDD

To provide simultaneous access to the data through COBOL and SQL, FairCom RTG/ISAM maps COBOL types into SQL types (and vice versa). FairCom RTG/ISAM must know the structure of the records so it can provide the SQL schema required for this mapping.

COBOL does not provide the necessary information about record structures required by SQL. These must be presented from an external source. Some COBOL compilers can generate XFD files which define this record structure.

When an XFD is Available

FairCom RTG provides a command-line switch to ctutil utility to convert XFD files into an XDD (eXtended Data Definition) file. This file is in XML format and contains information about the data and index structures including: index definitions, record schemas, default field values, null field handling (an undefined concept in COBOL), behavior of data conversion errors, etc.

During the conversion from XFD to XDD, ctutil also accepts a "rule file" that contains instructions to customize the generated XDD file for specific needs (such as default values for fields).

When an XFD is Not Available: xddgen

For those compilers that do not provide a means to generate an XFD file, we provide the OpenCOBOL-based xddgen, which can create an XDD from the COBOL source code. See the xddgen chapter for more information.

Merging the XDD with the Data File

The information contained in the XDD is stored directly in the data file itself with no effect on COBOL access to the data. The XDD file is embedded into the data file using the ctutil ‑sqlinfo switch. After adding the XDD information to the data file, the ctutil ‑sqllink switch is used to make it immediately accessible through SQL.

Indexes

Any operation performed through SQL or from the application uses and maintains existing indexes for optimal performance. Because of the nature of some COBOL types encoding, a native RTG index may not sort as an SQL expects. FairCom DB SQL can still take advantage of these indexes to retrieve records while not using them for sorting. This architectural limitation does not have significant impact in practice because the SQL engine is able to build temporary index files on the fly when necessary and uses dynamic index techniques. In addition, SQL specific indexes can also be created and are fully compatible with both SQL and original RTG application usage.

Multiple Record Types

An interesting feature available with FairCom RTG/ISAM is the ability to have multiple record schemas in the same data file.

A common programming technique for optimizing memory and storage was to combine multiple schemas into a single data file or table. Depending on some criteria, each record was interpreted using a particular application specific schema. This technique is a standard COBOL feature using REDEFINES. This, of course. contradicts strict relational requirements of SQL schemas. FairCom addresses this challenge by virtually presenting each application specific schema as a separate SQL table. This is accomplished by indicating in your data schema definition your same application specific rule determining which table each record resides. FairCom RTG/ISAM does the rest.

Select statements on one of these virtual tables display only records matching the selected criteria. Inserts into these tables are checked for matching criteria.

See Also

View the FairCom DB API Virtual Tables technical white paper on the FairCom website for the advanced FairCom database technology that makes this seamless support possible.

When a DDF is Available

FairCom RTG/ISAM provides a command-line switch to the ctutil utility (ctutil -ddf2xdd) to convert DDF files into an XDD (eXtended Data Definition) file. This file is in XML format and contains information about the data and index structures including: index definitions, record schemas, default field values, null field handling, behavior of data conversion errors, etc.

During the conversion from DDF to XDD, ctutil also accepts a “rule file” that contains instructions to customize the generated XDD file for specific needs (such as default values for fields).

Merging the XDD with the Data File

The information contained in the XDD is stored directly in the data file itself with no effect on Btrieve access to the data. The XDD file is embedded into the data file using the ctutil ‑sqlinfo switch. After adding the XDD information to the data file, the ctutil ‑sqllink switch is used to make it immediately accessible through SQL.

Index Compatibility

Any operation performed through SQL or from the application uses and maintains existing indexes for optimal performance. Because of the nature of some Btrieve types encoding, a native FairCom RTG/ISAM index may not sort as SQL expects. FairCom DB SQL can still take advantage of these indexes to retrieve records while not using them for sorting. This architectural limitation does not have significant impact in practice because the SQL engine is able to build temporary index files on the fly when necessary and uses dynamic index techniques. In addition, SQL specific indexes can also be created and are fully compatible with both SQL and original RTG/ISAM application usage.

REDEFINES: Multiple Record Types

An interesting feature available with FairCom RTG/ISAM is the ability to have multiple record schemas in the same data file.

A common programming technique for optimizing memory and storage was to combine multiple schemas into a single data file or table. Depending on some criteria, each record was interpreted using a particular application specific schema. This technique is a standard COBOL feature using REDEFINES. This, of course. contradicts strict relational requirements of SQL schemas. FairCom addresses this challenge by virtually presenting each application specific schema as a separate SQL table. This is accomplished by indicating in your data schema definition your same application specific rule determining which table each record resides. FairCom RTG/ISAM does the rest.

Select statements on one of these virtual tables display only records matching the selected criteria. Inserts into these tables are checked for matching criteria.

See Also

View the FairCom DB API Virtual Tables technical white paper on the FairCom website for the advanced FairCom database technology that makes this seamless support possible.

SQL Considerations

FairCom RTG/ISAM allows the same data files to be accessed by both SQL and ISAM interfaces. This provides a truly flexible development environment allowing you to choose exactly the techniques that best suit their needs for performance and data integrity.

The SQL support provided by FairCom RTG/ISAM is targeted at the most important SQL functionality, such as reading and writing records using SQL queries, user-defined functions, stored procedures & triggers, views, and joins (including joining a SQL-created table with an ISAM table). There are multiple considerations on SQL tables that are important for you to keep in mind.

Note that FairCom DB provides full, industry-standard SQL. You can create a database that is accessed exclusively through SQL and it will not experience the limitations listed below. The limitations discussed below arise when accessing data from both SQL and ISAM interfaces on files created through ISAM. Due to the high flexibility of ISAM contrasted with the discipline of SQL, some SQL features cannot be expected to work in this environment. The next sections list important considerations to keep in mind when developing applications using both ISAM and SQL to access the same data.

Note: Additional considerations apply if you use the FairCom DB Professional Developer's Kit to expose your ISAM data through SQL, giving you full read, write, insert, and delete access to the data. Please contact FairCom for a complete list of supported functionality.

Please review the following considerations:

• Common Issues
• FairCom RTG/ISAM SQL Support

Common SQL Limitations with FairCom RTG/ISAM Data

FairCom DB provides full, industry-standard SQL and you can create a database that is accessed exclusively through SQL. The limitations discussed below arise when accessing data from both SQL and ISAM interfaces on files created through direct record-oriented ISAM interfaces. Due to the high flexibility of ISAM contrasted with the discipline of SQL, some SQL features cannot be expected to work in this environment.

The following issues are important to keep in mind when developing FairCom DB or FairCom RTG/ISAM applications that use both ISAM and SQL to access the same data files created through the ISAM interface.
 

Triggers

In addition, keep in mind that SQL triggers are applicable at the SQL level only. NoSQL APIs (e.g., ISAM, FairCom DB API, COBOL, FairCom DB API Java, etc.) cannot invoke a SQL trigger. This is not a limitation specific to FairCom DB and FairCom RTG/ISAM: a trigger is a SQL concept that is not supported by ISAM.

If you desire to have an action executed on record changes below the SQL layer, consider the FairCom DB Record Update Callback feature discussed in the FairCom DB Programmer's Reference Guide:

Record Update Callback

Note: Additional considerations apply if you use the FairCom DB Professional Developer's Kit to expose your ISAM data through SQL, giving you full read, write, insert, and delete access to the data. Please contact FairCom should you have questions about specific use cases.

FairCom RTG/ISAM SQL Support

The FairCom RTG/ISAM products allow your Btrieve data to be exposed through SQL, giving you full access to read, write, insert, and delete records. This support is targeted at the most important SQL functionality, which includes:

• Reading and writing records using SQL queries
• User-defined functions
• Stored procedures
• Triggers
• Views
• Joins (including joining a SQL-created table with an ISAM table).

The topics listed in Common Issues are important to remember when developing FairCom RTG/ISAM applications that use both ISAM and SQL to access the same data files. In addition, keep the following in mind:

• A table created through SQL cannot be accessed using FairCom ISAM drivers for Btrieve.
• SQL triggers are applicable at the SQL level only; your Btrieve program cannot invoke a SQL trigger.
• Referential integrity is applicable at the SQL level only; no check is performed to ensure referential integrity.
• If transactions are disabled on Btrieve files, this creates additional considerations to accessing these files at the SQL level: rollbacks do not affect these files; isolation of transaction is not provided; etc.

Developing Stored Procedures

FairCom highly recommends the use of our NetBeans plug-in (found in the sql.stored.procs folder), which significantly simplifies the development, debugging, and deployment of stored procedures.