RTG SQL access

FairCom RTG offers more than just a high-performance file system. The FairCom RTG 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 gives you the most direct access available.

COBOL and 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 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 COBOL 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 an XFD: If your compiler can create an XDD, you can generate the XDD from it.
• Creating an XDD from the COBOL Source: If your COBOL compiler does not provide an XFD, you can generate the XDD from COBOL source.
• Creating an XDD Manually: If you do not have an XFD or source, 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

Next, you will store the XDD in the file and link it to the FairCom DB SQL dictionaries using ctutil -sqlize.

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

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 COBOL 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 RTG/ISAM provides concurrent access to data using SQL in addition to COBOL 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 RTG/ISAM products allow your COBOL 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 RTG 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 FairCom RTG/ISAM ISAM drivers for COBOL.
• 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 RTG/ISAM program cannot invoke a SQL trigger.
• If transactions are disabled on COBOL 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.

Step-by-Step Sqlizing Instructions

This section provides step-by-step instructions for sqlizing your data based on the Sqlize Tutorial (Driver\ctree.cobol\tutorials\sqlize\). It assumes you have already set up the environment where you will run the COBOL application using FairCom RTG.

You can obtain the current XDD structure from an existing table using ctutil -sqlinfo.

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

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

Bring RTG SQL to your Existing COBOL Data

The process of sqlizing consists of three major steps for the end user (the tutorial below includes extra steps for clarity):

1. Extract the XDD from COBOL (step 2 in the tutorial below).
2. Sqlize it using ctutil (step 4 in the tutorial below).
3. Use SQL to access the database (step 6 in the tutorial below).

The SQLIZEEXAMPLE program prints out information about membership cards satisfying a partial card number request. The program itself can create the required file if the file does not exist, however it does not populate the file.

Compile the SQLIZEEXAMPLE.CBL program:
iscc SQLIZEEXAMPLE.CBL

Extract the XDD from SQLIZEEXAMPLE.CBL by running xddgen:
C:\FairCom\vx.x.x.RTG\winX64\Tools\cmdline\XDDGEN\xddgen.exe SQLIZEEXAMPLE.CBL
 

Create empty FairCom RTG files using either the COBOL program or the XDD.

You can execute the COBOL program one time to create the COBOL files (.DAT and .IDX):
iscc -ze SQLIZEEXAMPLE.CBL

Or you can use the XDD to create the file, using ctutil -make:
ctutil.exe -make CARDFILE CARDFILE.xdd

Sqlize it, using ctutil –sqlize and the XDD:
ctutil.exe -sqlize CARDFILE CARDFILE.xdd ADMIN ctreeSQL -rule=rules.xml

Populate the table using the SQL script Tutorial.sql:
isql.exe -s Tutorial.sql -u ADMIN -a ADMIN ctreeSQL

Run the COBOL program again and count the records. You can now use SQL to query the multi-record data.

A few tips:

• Use the ACUCOBOL compiler option ‑f4 to automatically generate an XFD file. In most cases, FairCom RTG can readily use this information to automatically sqlize the table upon creation.
• The FairCom RTG ctree.conf file allows automatic sqlizing with the <sqlize> configuration option as long as a valid COBOL XFD or FairCom RTG XDD file is available. Uncomment this in the provided ctree.conf file for Tutorial1.
• You’ll find compiler specific directives (*>>XDD WHEN) in the CARDFILE.FD source module. Review this module which provides a modestly complex usage of COBOL redefines, resulting in multiple SQL “tables” from a single COBOL table. Be sure to run xddgen over this module to create a valid XDD file.

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.

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.

COBOL to SQL Type Mapping

FairCom RTG COBOL maps COBOL data types into SQL types. The mapping depends on the settings in the XDD file and compilation settings at runtime.

The table below shows the default mapping. It is also possible to force mapping to a particular data type by using the dbtype attribute in the XDD.

Number Mapping

The table below describes the mapping of numbers. The mapping used will be the first case that applies (e.g., if the PIC contains a V, that rule will be used regardless of the size in bytes). For the size in bytes or the number of digits of COBOL types, please check the compiler/runtime documentation.

Variable Length Fields

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.

COBOL Date Baseline Can Be Set to Julian Starting Date of Dec 31, 1600

COBOL has no "date" type, however it has a standard function called integer_of_date, which takes a COBOL number of the form YYYYMMDD and generates an integer that is the number of days since Dec. 31 1600 (therefore a Julian date). When linking such a date into SQL, the julianBase would be "16001231". However, such a julianBase cannot be specified because the value needs to be in c-tree valid date range, which is not earlier than March 3 1700.

It is now possible to specify in the XDD julianBase='integer-of-date' ('integer-of-date' must be lowercase!) to indicate values stored in the record result from an integer-of-date function.

Create an XDD Manually

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

Users of FairCom DB 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.

Create an XDD from an XFD

If your COBOL compiler can provide an XFD, it can be used to create the XDD required for sqlizing your data.

If your COBOL compiler cannot provide an XFD, see the section Creating an XDD from COBOL Source.

If your compiler can create an XFD, you can generate the XDD from it using ctutil -xfd2xdd or ctutil -sqlize.

Use the ctutil utility with the ‑xfd2xdd parameter to create a valid XDD file starting from an XFD file, which can be generated by COBOL compilers such as ACUCOBOL.

You can specify a rules file to fine-tune the creation of the XDD, as described in Defining External Rules.
If you need to further fine-tune the XFD, it can be edited with an XML editor before it is stored in the data file.

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

Create an XDD from Your COBOL Source

If you do not have an XFD, FairCom RTG allows you to create the XDD from your COBOL source code. The XDD is generated from COBOL source code using xddgen. This command-line utility analyzes the COBOL program to determine the schema. You will specify the COBOL program file as input. A variety of other parameters allow you to specify the dialect of COBOL (ACUCOBOL, Micro Focus, IBM, etc.), source format (free or fixed), directories, and other options.

After creating the XDD, use the procedures in the section titled Storing the XDD in the Data File to prepare your data file for SQL access.

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.

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.

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.

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

Data Conversion Between COBOL and SQL

FairCom RTG data is persisted in its “native” (COBOL) format. To share data between COBOL and SQL, the SQL engine performs necessary data conversion between COBOL and SQL data types on-the-fly as fields are accessed. As SQL access is fully read and write, this process must go in both directions. That is, on SQL SELECT native COBOL data must be converted to standard SQL data types for presentation and on SQL INSERT, SQL data types must be converted to persisted data accessible by an existing COBOL application. The XDD schema definition is the source of information about operations performed when bridging SQL and COBOL data.

There are two sides of conversion:

• From COBOL to SQL when reading (for example, SELECT).
• From SQL to COBOL when writing (for example, INSERT INTO, UPDATE statements)

Each is described below.

COBOL to SQL

When converting data from COBOL to SQL there may be situations where the content of the record cannot be interpreted and/or correctly mapped into a SQL data type.

Consider the situation of a PIC 9(3) (a COBOL numeric value) containing three spaces. RTG conversion does not know what these spaces represent or how to interpret it. It cannot tell if the content is garbage or intentionally defined.

Another very common situation is date formats, where it happens that although the field content is clear it does not map into a valid SQL value DATE type. For example, a PIC 9(10) mapped into a date value may have its value set to 0, which is a good numeric value, but not a valid FairCom DB date!

A third common scenario that there are different COBOL conventions for how to represent persisted numeric data in the file. In particular, COBOL dialects have differing default conventions for how signed numeric sign values are encoded. The SQL engine must know which convention is in use and the data on disk must adhere to that convention, otherwise a conversion error occurs.

The XDD schema mapping contains information on how to handle conversion errors at the “schema” level (for an entire table) or at “field” level (for a single field) by setting the “onConvertError” attribute.

onConvertError

When a conversion error occurs the engine determines what to do based on the onConvertError setting for that specific field. If the onConvertError for the field missing, it uses the one set on the schema or, when that is missing, it uses the default. The possible behaviors are:

• "error" - This is the default behavior. The error is propagated to SQL and the query fails unless the field content matches bindefault or cbdefault (see <field> schema element) in which case the value will be exposed in SQL as NULL. This is the default behavior when onConvertError is not specified. It is very convenient as is it a good compromise between the “strict” and the “null” approaches. It is similar to “strict” with only one precise exception for values that SQL would store in the record when setting the field to NULL.
• "strict" - No action is taken, the error is propagated to SQL and the query immediately fails at that record location. This behavior is very strict but very safe. It is good for fields where the value is expected to always be a valid value, such as the fields composing the main index of the table.
• "null" - The values that cannot be converted are exposed in SQL as NULL. This is very convenient in those cases where the field contains garbage for whatever reason. It is also convenient when there is no way to handle the conversion error differently without touching the data. However this setting makes it impossible for SQL to use any index on the fields to which it applies. Note - this is NOT the exact same concept as a standard SQL NULL flag value.
• "value:?" (where ? is the wanted value in SQL) - The values that cannot be converted are exposed in SQL as the specified value. This is similar to the “null” approach but it exposes the content as the specified value instead of as NULL. It has the same pros and cons as the “null” approach.

Automatically generated XDD schema mappings do not contain any onConversionError settings and do not contain any default values and application administrator intervention is required, at least initially, to properly configure. Once properly configured and verified that conversion errors are properly handled as desired, it is possible to automate the XDD modification using XDD rules. See Define External Rules.

SQL to COBOL

When converting from SQL to COBOL there are situations where determining what the COBOL record buffer should contain is difficult if not impossible. Typical situations:

Null Values

In SQL it is possible to set a field flag to “NULL” indicating “missing information or inapplicable information” or “lack of a value.” The same concept does not exist outside SQL (for example, in COBOL ISAM files) where any field has a value. Usually applications have a specific sentinel value for "missing" field data. When a field is set to NULL by SQL, RTG conversion logic needs to know what value to store in the field. In COBOL there is no standard discipline for “uninitialized fields” (for example, in a table of “people,” the date of death is unknown while the person is alive). The field may contain a high-value, a low-value, spaces, zeros or even garbage; every application has its own discipline, which may be different on different fields.

To address this issue, the XDD file allows you to specify a “bindefault” or a “cbdefault” attribute for the field, which will be written to disk in place of a SQL NULL. Essentially, “bindefault” is a binary value to replace a SQL NULL and “cbdefault” is a COBOL value to replace a SQL NULL (see <field> schema element).

• If the XDD file DOES NOT specify a “bindefault” or a “cbdefault” attribute for the field, it is linked in SQL as NOT NULL, meaning that SQL will refuse any statement setting the field to NULL.
• If the XDD file DOES specify a “bindefault” or a “cbdefault” attribute for the field, the field is linked as “nullable”, SQL will allow setting it to NULL and conversion logic stores the specified default value in the COBOL record.

In this second case, it is worth noticing that if the default value set in the XDD is a valid value for the field type (e.g., 0 for numeric types) when reading the record, it will be converted into that value in SQL and not NULL as it was set. If the value is not a valid value (e.g., spaces for numeric types), a conversion error will occur when reading the record but the engine automatically handles it (unless the XDD specifies otherwise, as explained later) and exposes the value as NULL in SQL, as expected.

Hidden Fields

An XDD mapping may specify some fields are hidden to SQL, which is to say SQL is not aware of them. This occurs for “filler” fields or for redefines where a record portion is not redefined or simply because the person who generated the XDD decided not to expose some field (and the information it contains) to SQL.

When updating an existing record, hidden fields are not a problem, because when performing an update, the engine will convert and store only fields that are set by the update; the portion of the record belonging to hidden fields is left untouched.

When inserting a new record, hidden fields are a problem: SQL does not pass any value to store in these fields, because the SQL engine does not know they exist. However, the portion of the record belonging to these fields must be set. The engine sets it to the “default” value specified in the XDD for the specific hidden field which is consistent with the approach taken for NULL values.

However, if the default value is not specified you may encounter error -21126 (SQL) or possibly 4126, CTDBRET_CALLBACK_18 (Missing default for null field).

FairCom DB SQL Explorer will display the above SQL error if you attempt to insert a record into a table that has hidden fields without default values set in the rules file. To prevent this, add the following to your rules file:

<rule sequence="1002">

<when>

<field hidden="true"/>

</when>

<do>

<add>

<field cbdefault="0" onConvertError="null"/>

</add>

</do>

</rule>

Unsigned Fields

Unsigned COBOL types are mapped into SQL types, which are signed. SQL itself does not check for negative values, which are not acceptable values for the COBOL data types. If the XDD has defined a field as unsigned, attempting to set it to a negative value causes a CTDBRET_UNDERFLOW error, which is a common situation out-of-the-box.

Inline Redefines

It is possible for the XDD file to specify that the redefines are “expanded” as further fields in the same table instead of creating a separate SQL table. In this case, attempting to add a new record (i.e., INSERT INTO) fails with error CTDBRET_NOTYET (Not yet implemented).

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

Rules for SQL Conversion Exception Handling

When generating a XDD from an XFD file, it is useful to have an automated method for altering the resulting XDD to better fit user requirements (for example, to add defaults, hide fields, etc.). The "rules file" provides an automated method to control the process using rules saved in XML format.

When rules are specified using the ctutil -sqlize option or the ctutil -xfd2xdd option they are applied to the generated XDD.

XML Rule Format

Rules files have the following XML structure:

<XFDrules>

<rule>

<when> OPTIONAL

<"condition"> </"condition">

...

More <"condition"> elements

...

</when>

<do>

<"action">

<"target"> </"target">

...

More <"target"> elements

...

</"action">

...

More <"action"> elements

...

</do>

</rule>

...

More <rule> elements

...

</XFDrules>

Each of the XML elements are defined in the sections following this topic.

<XFDrules> root element

This is the root element used to contain all the rules to apply to the XDD file.

<XFDrules>

</XFDrules>

Elements

<rule> XFDRules element

The rule tag defines a rule to be applied to the XDD file.

There must be at least 1 rule element. In case of multiple rule elements, they define multiple rules. All of the rules defined will be checked, and, if the <when> condition is satisfied, applied in defined ascending numeric sequence.

<XFDrules>

<rule>

</rule>

<XFDrules>

Elements

Mandatory Attributes

Optional Attributes

See Also

• <XFDrules>

<when> rule element

The optional <when> element is used to specify the criteria identifying if the rule should be applied or not. If there is no when tag then the rule always applies.

<XFDrules>

<rule>

<when>

</when>

</rule>

</XFDrules>

Elements

See Also

• <XFDrules>
• <rule>

<[Condition]> when elements

The condition elements are used by the <when> element to identify when the current rule will be applied. A condition rule is satisfied if all the specified attributes match the matching element of the XDD file:

• <table> condition is satisfied if all attributes specified in the rule condition matches the <table> tag of the XDD file.
• <schema> condition is satisfied if all attributes specified in the rule condition matches the <schema> tag of the XDD file.
• <field> condition is satisfied if all attributes specified in the rule condition matches the <field> tag of the XDD file.

The condition elements are mutually exclusive and contain the attributes of the element with the same name as the XDD file.

If more condition elements are specified the current rule will be applied if all the conditions are satisfied:

• <table> used to describe a condition to verify on the <table> element of the XDD file.
• <schema> used to describe a condition to verify on all the <schema> elements of the XDD file.
• <field> used to describe a condition to verify on all the <field> elements (children of the <schema> element element) of the XDD file.

<XFDrules>

<rule>

<when>

<"Condition">

</"Condition">

</when>

</rule>

</XFDrules>

Attibutes

• For <table> condition element attributes refer to the attributes list of the <table> element of the XDD file.
• For <schema> condition element attributes refer to the attributes list of the <schema> element of the XDD file.
• For <field> condition element attributes refer to the attributes list of the <field> element (children of the <schema> element element) of the XDD file.

See Also

• <XFDrules>
• <rule>
• <when>

<do> rule element

The <do> element describes the action to apply by this rule. If a <when> element is also specified the action will be applied only if all the Condition elements are verified.

<XFDrules>

<rule>

<do>

</do>

</rule>

</XFDrules>

Elements

See Also

• <XFDrules>
• <rule>

<[Action]> do elements

The action elements define which action to execute for the current rule. If a <when> element is also defined, the actions will be executed only if all conditions are verified.

There are 4 different types of action elements:

• <add> This action adds new attributes to the matching element in the XDD file. The attributes will be added only if they do not exist.
• <set> This action changes the value of the attributes to the matching element in the XDD file. If the specified attributes do not exist they will be added.
• <modify> This action changes the value of the attributes to the matching element in the XDD file. The attributes will be changed only if they exist.
• <delete> This action removes an existing attribute to the matching element in the XDD file.

<XFDrules>

<rule>

<do>

<"Action">

</"Action">

</do>

</rule>

</XFDrules>

Elements

See Also

• <XFDrules>
• <rule>
• <do>

<[Target]> action element

Target elements are used by the <do> elements to identify which element the rule touches.

The target elements are mutually exclusive and contain the attributes of the element with the same name of the XDD file.

More target elements can be specified:

• <table> used to describe a change to make on the <table> element of the XDD file.
• <schema> used to describe a change to make on one of the <schema> element of the XDD file.
• <field> used to describe a change to make on one of the <field> element (children of the <schema> element element) of the XDD file.

<XFDrules>

<rule>

<do>

<"Action">

<"Target">

</"Target">

</"Action">

</do>

</rule>

</XFDrules>

Attibutes

• For <table> target element attributes please refer to the attributes list of the <table> element of the XDD file.
• For <schema> target element attributes please refer to the attributes list of the <schema> element of the XDD file.
• For <field> target element attributes please refer to the attributes list of the <field> element (children of the <schema> element element) of the XDD file.

See Also

• <XFDrules>
• <rule>
• <do>
• <"Action">

Rule Examples

Hidden Field Example

Here is an example demonstrating all hidden fields, as it deletes all the attributes hidden="true".

<rule sequence="1">

<when>

<field hidden="true"/>

</when>

<do>

<delete>

<field hidden="true"/>

</delete>

</do>

</rule>

DATE onConvertError Example

COBOL types mapped to date, datetime, or time may cause conversion issues if the COBOL data cannot be properly represented by a type of date, datetime, or time. Hence it may be useful to have a rule to set fields to NULL when a conversion error occurs on the schemas having a field mapped to a date, datetime, or time field. Here is a rule to do that for date fields:

<rule sequence="2">

<when>

<field dbtype="date"/>

</when>

<do>

<set>

<schema OnConvertError="null"/>

</set>

</do>

</rule>

The examples below show the following rules, which will be applied in the sequence order (based on "sequence="):

bindefault Example

When the field type="NumUnsigned", add bindefault="all-spaces".

<rule sequence="100">

<when>

<field type="NumUnsigned"/>

</when>

<do>

<add>

<field bindefault="all-spaces"/>

</add>

</do>

</rule>

cbdefault Example

When the field dbtype="date", add field cbdefault="0".

<rule sequence="110">

<when>

<field dbtype="date"/>

</when>

<do>

<add>

<field cbdefault="0"/>

</add>

</do>

</rule>

onConvertError Example

When the field name="CUSTOMER_ID", set field onConvertError="null".

<rule sequence="900">

<when>

<field name="CUSTOMER_ID"/>

</when>

<do>

<set>

<field onConvertError="null"/>

</set>

</do>

</rule>

Combined onConvertError and Format Addition Example

When the field dbtype="date", add field cbdefault="0" onConvertError="null" and add field format="YYYYMMDD" cbdefault="0" onConvertError="error".

<rule sequence="1000">

<when>

<field dbtype="date"/>

</when>

<do>

<add>

<field cbdefault="0" onConvertError="null"/>

</add>

<add>

<field format="YYYYMMDD" cbdefault="0" onConvertError="error"/>

</add>

</do>

</rule>

xddgen Techniques

This section demonstrates techniques you may need to use so that xddgen can map your COBOL data to SQL.

In many cases xddgen can sqlize your data without any modifications to your copybook. In some situations, the structure of your data will dictate adding directives to your copybook to tell xddgen how to handle the data. You will not need to restructure your data nor will you need to rewrite your application. All you will need to do is add certain directives to the file, which xddgen will read when it sqlizes your data.

The program used in this tutorial lists and counts all credit card holders within a file whose credit card numbers are similar to a sequence provided by the user. Once it processes the whole file, it displays the total count. You must provide a number between 1 and 10 digits. The program always considers only the initial digits of the number.

The main file of this tutorial is CARDFILE, a file that contains records of a reward membership card for customers. This tutorial will take you through the copybook, CARDFILE.FD, and explain how directives can be added to tell xddgen how to handle certain data structures.

Using Group Names

A directive is used here to force FairCom RTG to use the name of the group CARD-TITLE-NUMBER instead of the subfields when building the SQL table. By default, xddgen always expands the field belonging to groups and shows them in the SQL structure. However, there are cases where this is not the desired behavior.

*>>XDD USE GROUP

10 CARD-TITLE-NUMBER.

15 CARD-TITLE-NUMBER-1 PIC 9(4).

15 CARD-TITLE-NUMBER-2 PIC 9(6).

10 CARD-TITLE-NUMBER-X REDEFINES CARD-TITLE-NUMBER

PIC 9(10).

10 CUSTOMER-NUMBER PIC 9(4).

10 ELITE-MEMBER-TYPE PIC X.

88 CARD-GOLD VALUE "G".

88 CARD-PLATINUM VALUE "P".

88 CARD-SILVER VALUE "S".

Splitting an OCCURR

The first elements of the record are actually a split of the OCCURR GROUP-TITLE-INFO that follows it, but with each field identified separately. This is included in this tutorial to demonstrate a possible solution to use fields within an OCCURR as indexes of a file in SQL. Without this split, FairCom RTG would not be able to properly identify the field CARD-FAMILY-NUMBER-1 in order to build a SQL index using it. This is needed only in cases where you need a sub-group of an OCCURR to be used as a SQL index. Notice that this would not be required if this file would be accessible only by COBOL programs.

*>>XDD USE GROUP

10 CARD-FAMILY-NUMBER-1.

15 CARD-LABEL-NUMBER-1 PIC 9(4).

15 CARD-MAIN-NUMBER-1 PIC 9(8).

15 CARD-NUMBER-CRC-1 PIC 9(2).

*>>XDD DATE = YYMMDD

10 EMISSION-DATE-1 PIC 9(6) COMP-6.

Combining Multiple XDD Directives

The example below shows how to combine multiple XDD directives into a single line. In this example, we combined USE GROUP to force SQL to use the name of the group as the SQL field and a specific format to display this field as a date as two digits for year, two for month, and two for day. Many other formats available; please refer to the documentation for other options.

*>>XDD USE GROUP

*>>XDD DATE=YYMMDD

10 VALID-UNTIL-DATE-1 PIC 9(6) COMP-6.

Name Conflicts

OCCURs are handled automatically by FairCom RTG when converting this table into SQL. FairCom RTG automatically expands every OCCURS into multiple fields, using numbered extensions such as "_1", "_2" and so on. Because we are not explicitly using the groups for field naming in this example, the final fields to be displayed in SQL will be the most internal ones. After sqlizing this table, you can view it in SQL Explorer to see the following fields:

CARD_LABEL_NUMBER_1

CARD_MAIN_NUMBER_1

CARD_NUMBER_CRC_1

CARD_LABEL_NUMBER_2...
 

This might cause some field name conflicts, depending on how your fields are named in COBOL. In this example, if we had named VALID-UNTIL-DATE-1X without the final X, a conflict would happen and you would not be able to sqlize.

10 GROUP-TITLE-INFO OCCURS 2 TIMES.

*>>XDD USE GROUP

*>>XDD NAME=CARD_FAMILY_NUMB

15 CARD-FAMILY-NUMBER.

20 CARD-LABEL-NUMBER PIC 9(4).

20 CARD-MAIN-NUMBER PIC 9(8).

20 CARD-NUMBER-CRC PIC 9(2).

*>>XDD USE GROUP

*>>XDD NAME=EMISSION

*>>XDD DATE = YYMMDD

15 EMISSION-DATE PIC 9(6) COMP-6.

*>>XDD USE GROUP

*>>XDD NAME=VALID_UNTIL

*>>XDD DATE=YYMMDD

15 VALID-UNTIL-DATE PIC 9(6) COMP-6.

HIDDEN Directive

The example below shows the usage of HIDDEN as a directive that will instruct SQL to not display this field, considering that this is just a filler placeholder used to align the fields between two different REDEFINES. With this directive, this field will simply not be displayed. Note: This is provided as an example—FairCom RTG automatically hides fillers without using this command.

*>>XDD HIDDEN

10 RESERVED PIC X(4).

Multi-Record Example

This is an example of using multiple records with SQL. Notice that CARD-TABLE-PLATINUM redefines a previous record structure, but it includes additional fields. This means that this table has two different types of records, with different sizes. FairCom RTG can handle this properly, as long as you indicate the rules that the SQL server needs to use to decide which record belongs to which table. To do this, use the following XDD directive:

*>>XDD WHEN ELITE-MEMBER-TYPE != "P" TABLENAME="REGULARMEMBERS"

05 CARD-TABLE.
 

This will instruct FairCom RTG to create two separate SQL tables representing each separate set of records, depending on the condition that has been established for values within each record.

Notice that physically there is still a single COBOL file which allows you to run your COBOL programs with no modification. FairCom RTG handles this dynamically, building SQL tables as if they were actually views, and updating the indexes in accordance with the file definition.

In this example, we are forcing FairCom RTG to create a separate table to represent Platinum members. This SQL table includes some additional fields that are not displayed for the regular table.

*>>XDD WHEN ELITE-MEMBER-TYPE="P" TABLENAME="PLATINUMMEMBERS"

05 CARD-TABLE-PLATINUM REDEFINES CARD-TABLE.

10 GROUP-TITLE-INFO-PLT OCCURS 3 TIMES.

15 CARD-FAMILY-NUMBER-PLT.

20 CARD-LABEL-NUMBER-PLT PIC 9(4).

20 CARD-MAIN-NUMBER-PLT PIC 9(8).

20 CARD-NUMBER-CRC-PLT PIC 9(2).

*>>XDD DATE=YYMMDD

15 EMISSION-DATE-PLT PIC 9(6) COMP-6.

*>>XDD DATE=YYMMDD

15 VALID-UNTIL-DATE-PLT PIC 9(6) COMP-6.

10 GROUP-REWARDS-POINTS.

15 TOTAL-POINTS PIC 9(8).

*>>XDD DATE=YYMMDD

15 EXPIRATION-DATE PIC 9(6) COMP-6.

*>>XDD DATE=YYMMDD

15 LAST-TRANS-DATE PIC 9(6) COMP-6.

05 CARD-GROUP-NUMBER PIC 9(4).

05 CARD-EMBOSS-FLAG PIC X(1).