c-treeRTG COBOL Edition User's Guide

Common SQL Limitations with c-treeRTG 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 c-treeRTG applications that use both ISAM and SQL to access the same data files created through the ISAM interface.

	Limitation	Table definitions are done through ISAM; any changes to the table definitions must be done through ISAM. This implies: You cannot execute Alter Table over an ISAM data file. Referential Integrity cannot be defined across ISAM tables.

	Consideration	Not all ISAM indexes are compatible with SQL. In particular, some indexes do not collate as SQL mandates, for example, certain COMP types, and therefore cannot be used by SQL for sorting or to perform searches depending on the collation. This does not limit functionality, however can impact performance. In such a case it is possible to create new indexes directly from SQL that satisfy SQL needs and apply to both SQL and COBOL access.

	Has Workaround	ISAM files are not portable among different endianess, even if you are using portable data types. You can use the FairCom ctunf1 utility to convert endianess before moving between a big-endian and a little-endian system given that all data is ASCII string format and no native binary fields are included in tables.

Triggers

In addition, keep in mind that SQL triggers are applicable at the SQL level only. NoSQL APIs (e.g., ISAM, c-treeDB, COBOL, c-treeDB Java, etc.) cannot invoke a SQL trigger. This is not a limitation specific to FairCom DB and c-treeRTG: 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, you can use the ISAM Server-Based Queue and File Notification logic discussed in the FairCom DB Programmer's Reference Guide:

Server-Based Queues
File Notification

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.

In This Section

REDEFINES: Multiple Record Types

Index Compatibility

REDEFINES: Multiple Record Types

An interesting feature available with c-treeRTG 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. c-treeRTG 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.

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 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.