SQL representation of Model 204 data

From m204wiki
Jump to navigation Jump to search

Successful SQL processing of the data in Model 204 files requires an accurate SQL catalog definition of the files. Essential to the catalog definition is knowing how to take advantage of Model 204 database features in SQL. This chapter discusses how to work with Model 204 files in SQL: from preliminary file preparation to SQL accommodation of Model 204 file features, data formats, and data handling.

Representing Model 204 data in SQL

This section describes what you need to do to your Model 204 files to prepare for SQL processing and how certain Model 204 file features are handled in SQL processing.

Changing existing Model 204 files

Model 204 SQL requires little, if any, manipulation of the Model 204 file data before you catalog the data. This section describes the Model 204 SQL processing requirements that may necessitate changes to your Model 204 files.

You might have to change file parameter settings, add or redefine fields or field attributes, or update field values as follows:

  • Files used in SQL processing must be transaction backout (TBO) files.

    A file manager can change non-TBO files to TBO files by issuing the Model 204 RESET command. In addition, logging for TBO files makes demands on CCATEMP file space. For more information about transaction backout files, see File integrity and recovery.

  • SQL multicolumn unique constraints require addition of a new field.

    To enforce an SQL multicolumn unique constraint, you must first define and populate a special Model 204 UNIQUE field, which provides the constraint index. See Specifying a multicolumn UNIQUE key.

  • The following operations or SQL column definitions might require you to add or redefine field attributes:
    • INVISIBLE field values cannot be updated by SQL DML. To use SQL DML to update columns mapped to Model 204 INVISIBLE fields, you must redefine the fields to VISIBLE.
    • Model 204 fields mapped to columns designated as primary keys must have the UNIQUE and ORDERED field attributes.
    • Multiply occurring groups of fields mapped to nested tables must have the same number of occurrences.
    • Model 204 field attributes must map to compatible SQL column data types. Your choice of SQL data type determines the type of Model 204 storage index that is used to retrieve data. You might need to change a Model 204 field definition to better fit the data type you select for the corresponding SQL column.
  • The following operations or SQL column definitions might require you to check or update Model 204 data values:
    • SQL operations against columns mapped to STRING fields containing hexadecimal zeros are not reliable. Avoid storing hexadecimal zeros in STRING fields.
    • Empty character strings mapped to SQL character columns are findable in SQL with the WHERE clause:

      WHERE columnname="

      In SQL numeric operations, an empty string is interpreted as zero.

    • SQL operations against CHARACTER columns mapped to fields containing characters other than EBCDIC X '40' through X 'FE' are not reliable. Model 204 SQL processing supports only printable characters that sort higher than the blank character, that is, those equivalent to EBCDIC X '40' through X 'FE'.

      Characters outside this defined range cannot be entered using SQL. If other than these values exist in a Model 204 file, SQL operations involving character comparisons, sorting, or pattern matching might have unexpected results.

    • For joint SOUL and SQL access to a file: store data fields with no nonblank characters as one blank; do not define the Model 204 PAD character as a blank; and note that SQL removes trailing blanks for fixed-length CHARACTER data while SOUL does not.

SQL pattern search guidelines

For single character SQL pattern searches to return correct results, the ORDERED CHARACTER attribute is recommended.

Do not end an SQL search pattern with an underscore, the single character substitution symbol. Instead, use %, for multiple character substitution.

Using Model 204 file data features

This section describes what you have to do to use the following Model 204 file features in SQL processing.

File security

Model 204 files accessed for SQL processing are also available for SOUL and Host Language Interface (HLI) use. These files are subject to both Model 204 security and SQL security.

The usual Model 204 security features are in effect for SOUL and HLI access. SQL access to these files is protected by Model 204 login security and SQL security from GRANT, REVOKE, and CREATE VIEW statements.

Model 204 field and record security are not directly applicable in the SQL environment.

File groups

Model 204 file groups cannot be represented directly in SQL. However, you can simulate file groups with a view, and you can retrieve (but not update) data through the view. See Simulating file groups.

If there is a group file with filename1 and a file with filename1, Model 204 will open the group file and try to process the SQL request against the group file.

Model 204 precedence algorithm tries to open a Group-file filename1 first. If Group-file filename1 does not exist, Model 204 tries to open File filename1. Take care when using the same name for a group-file and a file; the file cannot be accessed directly through SQL because of the precedence algorithm.

See the OPEN FILE command for a more detailed explanation.

Sorted or hash key files

If a Model 204 file is a sorted or hash key file, specify the sort or hash key as a column in the table for the file. If the sort or hash key is required in the file (Model 204 FILEORG parameter X'02' option is set), define the column NOT NULL. For more information about mapping SQL columns to sort or hash keys, see Defining columns.

Files with multiply occurring fields

For full use of Model 204 multiply occurring fields, you must translate them into SQL nested tables. The nested table feature is a Model 204 SQL extension described in Mapping multiply occurring fields to nested tables.

Files with INVISIBLE fields

In Model 204, INVISIBLE fields are stored in the index portion of the file and not in the data portion (Table B). They are typically used to assist in Table B data retrieval operations but are not themselves printed or sorted. They generally require special treatment for Model 204 file and field update operations.

In Model 204 SQL, columns mapped to INVISIBLE fields are available to qualify searches but are not themselves retrievable. They also require special treatment for the files they map to and have restrictions on their use in SQL DML specifications.

In general, Model 204 files that contain INVISIBLE fields should be maintained by SOUL or Host Language Interface applications and not by SQL applications. This recommendation applies regardless of whether the fields are mapped to SQL columns. It does not apply to files that only have INVISIBLE fields that are mapped to multi-column unique constraints.

For example, you can remove an SQL row containing a column mapped to an INVISIBLE field with SQL DELETE, but there is no way with SQL DML to remove the INVISIBLE field from the Model 204 file index. Similarly, SQL UPDATE can modify a visible SQL column but cannot affect any INVISIBLE field values.

You can use an INVISIBLE field as a concatenation of the individual fields that map to the SQL columns that comprise a multicolumn unique key. However, the individual fields that map to these SQL columns might not be INVISIBLE.

Files with multiple record types

You must map all the data in each Model 204 file to a single non-nested SQL table (plus optional nested tables). Consequently, SQL column attributes you assign must apply to every record in a file. This might present a problem for files that have multiple record types. A record type is a set of records having the same collection of field names or formats and connected by the same value of a record type field.

For example:

A single file might have a set of driver records (with field RECTYPE equal to DRIVERS) and a set of policyholder records (with field RECTYPE equal to POLICYHOLDER). Many of the fields on the driver records are not common to the policyholder records, and vice versa.

To avoid retrieving unwanted driver information when you need only policyholder information, your SQL queries against the file must list each of the policyholder column names. Your queries grow lengthy and you have to keep track of the fields associated with each record type.

In addition, files with multiple record types limit the use of the SQL NOT NULL attribute. SQL column attributes must apply to all the records in the file, regardless of record type. Only fields common to both driver and policyholder records (with no empty-string values) can validly be mapped to a column defined with NOT NULL.

The most efficient way to map multiple record types within the same file is to use SQL views. After defining all the fields in each record type to the SQL base table you are creating, you define a view for each record type. The view definition contains a listing of the columns associated with the record type.

You can then execute simpler queries against the view, taking advantage of the preselection of required columns in the view definition. For an example of views defined for mixed record type files, see Mapping files with mixed record types.

File data indexes

Model 204 data retrieval indexes (for example, NUMERIC RANGE and ORDERED) might be ignored for SQL queries against the file, if you are not careful about your SQL catalog mapping of SQL and Model 204 data types.

Model 204 data values inconsistent with the SQL attributes for a column might not be usable by the SQL application or might be truncated upon conversion to the specified SQL format. Compatibility between SQL and Model 204 data formats is discussed in Matching Model 204 and SQL data formats.

Using PRIMARY KEY table columns

Successful SQL processing of the data in Model 204 files requires an accurate SQL catalog definition of the files. For example, mapping a Model 204 field to a column designated as a PRIMARY KEY requires the use of UNIQUE and ORDERED field attributes.

Some third-party, SQL compliant packages require a UNIQUE key in any table or view that is to be updated.

To ensure that such products work with Model 204 data, the following restrictions apply:

  • The base table in question, either the table to be queried or the table underlying the view to be queried, must be defined with a PRIMARY KEY. This PRIMARY KEY can be either a system-generated key (that is, the Model 204 record number) or a unique ORDERED INDEX. It must consist of a single column.
  • When you define a view that you want to update, it must be defined on one of the following:
    • Table with a PRIMARY KEY, with the PRIMARY KEY visible in the view definition
    • View conforming to a table with a PRIMARY KEY visible in the definition of the new view as well

Because a Model 204 nested table cannot have a PRIMARY KEY, any third-party product that requires a PRIMARY KEY for updating cannot update nested tables.

Model 204 and SQL data extraction mismatches

When you extract values in a Model 204 field that do not conform to a defined SQL data type, you can get unexpected results. For example, if a Model 204 stored value 0.05 (which is not an integer) is mapped to an SQL integer data type and you try to fetch that row with:

WHERE colname=0

there are no returned values. However, if you rewrite the request to:

WHERE colname>0 AND colname<1

SQL fetches the row and shows the column value as 0.

Model 204 extracts from the file all records qualifying for the selection criteria. From that found set, the SQL engine flags as Dirty Data only those rows where the data cannot be converted to the SQL data type.

You might see the following messages in the audit trail, which may affect the outcome of your query:

M204.0554: DIVIDE BY ZERO
M204.0563: ARITHMETIC OVERFLOW

Message 0554 indicates that an arithmetic expression attempted to divide by zero.

The SQL error message will be like the following:

SQL Error -802 DIVIDE BY ZERO exception has occurred during INTEGER DIVISION processing.

or

SQL Error -802 DECIMAL OVERFLOW exception has occurred during DECIMAL MULTIPLICATION processing.

Mapping multiply occurring fields to nested tables

A nested table is a Model 204 SQL extension that makes Model 204 multiply occurring fields available to SQL access. This section introduces the basic design of the feature and includes explanation of a simple example.

For information about using Model 204 SQL DDL to define nested tables to the SQL catalog, see Rules for nested table columns. For information about creating nested tables with the Table Specification facility, see Defining nested tables. For information about using SQL DML to access nested tables, see Retrieving a particular occurrence of a multiply occurring group.

Understanding nested tables

In the relational model, SQL tables must be normalized: at each row-column position in a table, there can be only one value (or a null). Repeating, or multiple, values are not allowed. This is not a requirement in Model 204 databases. In a single Model 204 record an individual field can have a set of, or multiple, values. Such a field is called a multiply occurring field. A group of multiply occurring fields is also allowed.

The Model 204 SQL nested table extension allows you to take advantage of Model 204 multiply occurring fields. This extension permits the mapping of a file with multiply occurring fields to a main (parent) SQL table plus one or more subsidiary (nested) tables related to the parent. The nested tables contain only columns that map to multiply occurring fields and a single key column that joins the nested table to its parent.

The values of the nested table joining key column (nesting key, or foreign key) are the same as those of the primary key in the parent table. These values must be unique. If the nesting key is a composite of two or more columns, you can have the Model 204 SQL Server generate and maintain a SYSTEM primary key.

A nested table can have only one parent table and must belong to the same SQL schema as the parent. A parent table, which must not be nested, can have multiple nested tables.

The Model 204 SQL nested table extension is a DDL extension only. Once you have defined repeating fields as nested tables in the SQL catalog, you can issue standard SQL DML against these tables.

Translating multiply occurring fields

A simple example follows of a Model 204 SQL translation of Model 204 multiply occurring fields. A Model 204 file has the following fields:

Field Frequency of occurrence
NAME Once per record
HIRE_DATE Once per record
REV_DATE Multiply occurring group member
SALARY Multiply occurring group member
TITLE Multiply occurring group member
TASK Multiple times per record

REV_DATE, SALARY, and TITLE are repeating groups that occur once each salary review.

You can translate this Model 204 situation into one SQL parent table (PEOPLE) with two columns (NAME and HIRE_DATE) and two nested tables (REV_HIST and TASKS):

<MAP NAME="Chap3-2"> </MAP> <IMG SRC="Chap3-2.gif" USEMAP="#Chap3-2">

The parent table is linked to the nested tables by the common values of the primary key NAME in the parent and the foreign key FNAME in the nested tables. The values of NAME must be unique (NAME must have the Model 204 UNIQUE attribute). Each value of NAME or FNAME is the unique identifier the SQL Server uses to locate the Model 204 record that contains the multiply occurring values.

SQL updates to the primary key are propagated to the foreign key. You cannot directly update the foreign key.

Notice that nested tables may include only a foreign key and the columns that map to Model 204 multiply occurring fields or multiply occurring groups.

Simulating normalization of Model 204 record data

The file in the preceding example contains the following two records:

Record 1

NAME HIRE_DATE REV_DATE SALARY TITLE TASK
n1 n1HD n1RD1 n1S1 n1TI1 n1T1
n1RD2 n1S2 n1TI2 n1T2
  n1T3

Record 2

NAME HIRE_DATE REV_DATE SALARY TITLE TASK
n2 n2HD n2RD1 n2S1 n2TI1 n2T1
  n2RD2 n2S2 n2TI2 n2T2
n2RD3 n2S3 n2TI3  

Using the Model 204 SQL DDL for nested tables discussed in Creating nested tables, you map this file to the SQL parent table PEOPLE and the nested tables REV_HIST and TASKS. Logically the data appears to the SQL Server as follows:

Table Columns
PEOPLE NAME HIRE_DATE
n1 n1HD
n2 n2HD
Table Columns
REV_HIST FNAME REV_DATE SALARY TITLE
n1 n1RD1 n1S1 n1TI1
n1 n1RD2 n1S2 n1TI2
n2 n2RD1 n2S1 n2TI1
n2 n2RD2 n2S2 n2TI2
n2 n2RD3 n2S3 n2TI3
Table Columns
TASKS FNAME TASK
  n1 n1T1
n1 n1T2
n1 n1T3
n2 n2T1
n2 n2T2

The nested table rows are ordered and retrieved by matching occurrence: the first row is mapped to the first occurrences of the repeating field values, the second row is mapped to the second occurrences of the repeating field values, and so on. The rank of occurrence (first, second, and so on) is determined by physical storage order in the file.

When you issue a query against a nested table, the query must specify either the primary key of the parent table or the foreign key of the nested table. The Model 204 SQL Server uses the unique value of the primary or foreign key in the nested table row to locate the physical record with the repeating field values.

Handling foreign keys

If a foreign key is defined twice, first with a REFERENCES clause and then with a FOREIGN KEY clause, the statement is accepted only if the two clauses are identical and reference the same column.

SQL error message -4703 is generated if a FOREIGN key is defined twice for a nested table and the two keys do not reference the same column. For example:

Acceptable:

CREATE TABLE NESTEDINVENTOR2 NESTED USING PART_NO (PART_NO DECIMAL(8) NOT NULL REFERENCES INVENTORY, ON_HAND SYSNAME 'ON HAND' FLOAT(4) NOT NULL, LOCATION CHAR(255) NOT NULL, FOREIGN KEY (PART_NO) REFERENCES INVENTORY)

Returns SQL error -4703:

CREATE TABLE NESTEDINVENTOR2 NESTED USING PART_NO (PART_NO DECIMAL(8) NOT NULL REFERENCES INVENTORY, ON_HAND SYSNAME 'ON HAND' FLOAT(4) NOT NULL, LOCATION CHAR(255) NOT NULL, FOREIGN KEY (ON_HAND) REFERENCES INVENTORY)

Handling primary keys

SQL error message -6315 is now displayed if a column in a table is defined with PRIMARY KEY SYSTEM and the SYSNAME clause.

The following example returns SQL error message -6315:

CREATE TABLE XYZ (COL1 INT, COL2 CHAR (25), F_KEY INT PRIMARY KEY SYSTEM SYSNAME 'INDEX')

Matching Model 204 and SQL data formats

The DDL you create to map SQL tables to Model 204 files specifies the data types of SQL columns. Model 204 field attributes and SQL column attributes place different restrictions on the type of data values that can be stored in a field or column. This section discusses aspects of Model 204 data mapping you need to consider when you are preparing SQL DDL to map the Model 204 file data.

Compatibility of Model 204 and SQL data formats

Model 204 data typing is more informal than SQL data typing. Therefore, Model 204 files can include data that meets the Model 204 data typing definitions, but which might cause unexpected results when a Model 204 SQL program is run that uses Model 204 data.

In general, Model 204 SQL returns data to an SQL application in the SQL format requested, and stores data in the Model 204 format after conversion from the defined SQL format. It is your responsibility to ensure that the SQL format you define for a column is compatible with the format of the data stored in the Model 204 file.

Data format compatibility between Model 204 and SQL affects the accuracy and efficiency of data retrievals and data conversions. The broad levels of compatibility between Model 204 and SQL data formats are:

Data retrieval performance This first level of compatibility is more general and requires that you pair SQL numeric column data types with numeric Model 204 data and nonnumeric types with nonnumeric data. Failure to do so means that you cannot use Model 204 indexes for that field to retrieve data, or certain data is not retrievable, or both.
Data conversion This second level of compatibility extends the first and requires Model 204 format and SQL data type combinations that result in data conversions without loss of accuracy or precision.

Optimizing Model 204 data retrieval

Data retrieval is more efficient in Model 204 when you can use a variety of numeric and nonnumeric indexes. Model 204 data indexes are defined with Model 204 field attributes such as KEY, NUMERIC RANGE, and ORDERED. SQL application programmers are responsible for understanding the effects of these attributes and for tailoring their applications accordingly. Model 204 field attributes are described in the Rocket Model 204 documentation wiki:

http://m204wiki.rocketsoftware.com/index.php/Field_design

Optimizing retrieval for SQL selections

When using a SELECT DISTINCT statement on an SQL column, define the underlying Model 204 field as ORDERED or FRV to eliminate NULL column values from the result.

The Model 204 SQL engine is specifically designed to handle Model 204 field types. Rocket Software recommends that you define all fields that are used in SQL DISTINCT or WHERE processing with the ORDERED attribute. Performance improvements are noticeable.

The following figure compares the paths taken when fields are not ORDERED or not FRV with the path taken when fields are defined ORDERED or FRV.

SQL SELECT DISTINCT processing paths

You can preserve these Model 204 retrieval efficiencies in SQL by making sure that you assign compatible SQL data types to the Model 204 fields you are using. Usually you define an SQL numeric data type for numeric Model 204 data, and you define the SQL nonnumeric data type for nonnumeric data, although not all cases are this straightforward.

Model 204 indexes and data format mappings and Model 204/SQL data format incompatibilities list compatible mappings. Model 204 indexes and data format mappings shows permissible mappings of Model 204 data format attributes and the Model 204 indexes that are preserved or lost by such mappings. Model 204/SQL data format incompatibilities shows mappings that are not permitted because they present serious data conversion problems.

Model 204 indexes and data format mappings

Model 204 field attribute SQL data type Indexes preserved Indexes not usable
STRING CHARACTER Ordered character

Key**

Hash key		 Numeric Range
Ordered numeric
STRING numeric* Numeric Range
Ordered numeric		 Ordered character

Key

Hash key
BINARY
(except with OCCURS NON-CODED)		 CHARACTER Ordered character
Key**		 Numeric Range Ordered numeric
BINARY numeric* Numeric Range Ordered numeric Ordered character

Key

FLOAT numeric* Ordered numeric

Key**

none
CLOB CLOB N/A N/A
BLOB BLOB N/A N/A

* Numeric types supported are:

INTEGER
SMALLINT
DECIMAL (precision, scale) with decimal precision and scale
NUMERIC (precision, scale) with decimal precision and scale
FLOAT (precision) with binary precision
REAL
DOUBLE PRECISION

** Key Index is used only for direct searches in SQL as in:

WHERE fieldname=value

Avoiding Table B searches

Not using Model 204 data indexes results in data retrievals that are much less efficient and may require searches of the entire Table B of the file. For example, you assign an SQL numeric column attribute, say INTEGER, to a Model 204 STRING field. If the field is also defined with the Model 204 KEY attribute, which indexes the field's values, your pairing of SQL and Model 204 attributes prevents use of the KEY index. Retrievals involving this field search the file's Table B instead of using the KEY index.

The KEY index is used only for equality data retrievals. Range retrievals against a field having only a KEY index results in a Table B search of the entire data file.

A more ambiguous example involves the ANNIV_DATE column, mapped to a Model 204 field that has the STRING and ORDERED NUMERIC field attributes. A sample field value is 0917. If you map the column to SQL DECIMAL(4,0), you can use the Model 204 numeric ordered index in SQL queries (as Model 204 indexes and data format mappings shows), but the essential leading zeros in the data are not preserved. If you use SQL CHAR(4) to preserve the leading zeros, you lose the benefit of the Model 204 numeric ordered index.

If the data type you assign to an SQL column would prevent the use of a Model 204 index, the SQL Server issues a warning message when you issue DML against that data. Successful DDL statement execution does not imply a compatible mapping of Model 204 and SQL data types.

If a Model 204 field is defined with more than one index, the data type you assign to your SQL column might prevent the use of one index but preserve another.

Optimizing Model 204 data conversion

The previous section defined the combinations of Model 204 and SQL data formats that allow use of the Model 204 indexes. Compatible combinations of Model 204 and SQL data formats are also necessary to maximize the accuracy of the conversions of the data from Model 204 format to SQL format.

Data is converted when you use SQL to insert values into the Model 204 fields and when you use SQL to retrieve values from Model 204 fields. The SQL Server ensures that data you insert is never truncated to fit Model 204 field format requirements; you cannot execute requests with columns whose SQL data types the Model 204 fields cannot fully accommodate. These incompatible combinations of column-to-field mappings are listed in the following table.

Model 204/SQL data format incompatibilities

Model 204 attribute Incompatible SQL data types Notes

STRING (non-preallocated)

None
STRING (preallocated with LENGTH n) CHARACTER(L)

DEC(p, s)
  or
NUMERIC(p, s)
INTEGER
SMALLINT
FLOAT
REAL

DOUBLE PRECISION		 (If L>n, the preallocated length)

(If s=0 and p>n, or
   s=p and p> n-2, or
   0<s<p and p> n-1)
(If n<11)

(If n<11)
BINARY (preallocated with NON-CODED) CHARACTER
BINARY (non-preallocated) DEC(p, s)

 
NUMERIC(p, s)
 
FLOAT
REAL

DOUBLE PRECISION		 (If s>0, or

   p>9 and s=0)
(If s>0, or

   p>9 and s=0)
BLOB all but CLOB  
CLOB all but BLOB  
FLOAT 4 CHARACTER

DEC(p, s)
NUMERIC(p, s)
INTEGER
SMALLINT
FLOAT(p)

DOUBLE PRECISION		  

(If p>6)
(If p>6)
 
 

(If p>21)
FLOAT 8
FLOAT 16		 CHARACTER

Note: The following mappings are not prohibited by the rules shown in Model 204/SQL data format incompatibilities, but they result in a Model 204 error (soft restart) if you attempt to insert negative-valued data with p significant digits into these field and column combinations.

For example, you cannot insert the value -0.1234 (4 significant digits) into a DEC(4,0) column (s=0, p=4) mapped to a preallocated STRING LEN 4 field (n=4). This is the first case shown below: s=0 and p=n.

Model 204 attribute SQL data type
STRING (preallocated with LENGTH n) DEC(p, s)

 

NUMERIC(p, s)		 (If s=0 and p= n, or

     s=p and p= n-2, or

     0<s<p and p= n-1)

Data mapping incompatibilities are validated for an SQL table at runtime when a DML statement involves a reference to the catalog. Such incompatibilities result in an SQL statement validation error, and the statement is not processed.

Even with compatible mappings of data formats, the SQL Server does not guarantee that the actual Model 204 data you retrieve with SQL is convertible to the defined SQL format without some modification or truncation. When necessary, the SQL Server observes the following rules for data conversion:

  • Whenever possible, Model 204 data is converted (with or without modification) to your SQL specification. Data that the Model 204 SQL Server cannot convert to the format you specify is called dirty data. See How Model 204 SQL processes dirty data.
  • Model 204 fields with character strings longer than the defined length for SQL CHARACTER columns are accepted, but the values are truncated. Trailing blanks are removed and leading blanks are preserved if such fields are updated by SQL.
  • Numeric data not matching the definition of the SQL data type is truncated, rounded, or converted to match the SQL data type.

How Model 204 SQL processes dirty data

Model 204 data that cannot be converted to fit the defined SQL data type is dirty data. What data is dirty data is influenced by the SQL data type:

  • SQL CHARACTER specifications encounter no dirty data. All string and non-preallocated binary data is convertible to this format. Empty strings, for example, are converted to blanks. Empty strings in preallocated fields are converted to blanks.
  • For SQL numeric data types, any nonnumeric data is not convertible (dirty data). Empty fields are interpreted as (numeric) zero.

For both SQL character and numeric data types, including nulls, a missing field is interpreted as an SQL null and is not dirty data.

Handling dirty data at runtime

At runtime, each time the Model 204 SQL Server gets data from a Model 204 record, the data type characteristics of each field being returned are validated: if invalid (dirty) data is found in any field referenced in the query, the SQL Server takes one of the following actions:

  • Processing of the SQL statement stops, and a negative SQL code is returned.
  • The record is bypassed, and an SQL warning message is issued. If many records are bypassed, you might still receive only one warning message. Also, whether the record is included in a COUNT of selected data is unpredictable. Processing of the SQL statement continues.

    You might receive a dirty data warning even when your query results are correct, that is, not affected by dirty data. Such a warning indicates that the SQL Engine encountered dirty data while deriving the resultant set of records.

  • You will not receive a dirty data warning if the dirty data was detected in the last record processed prior to EOF.

The system manager determines which of these actions the SQL Server takes by the setting of the Model 204 SQLCNVER parameter.

SQLCNVER is described in the Rocket Model 204 SQL Connectivity Guide and in greater detail in the Rocket Model 204 documentation wiki:

http://m204wiki.rocketsoftware.com/index.php/SQLCNVER_parameter

PC clients can override SCLCNVER by selecting alternative action for the Dirty Data Treatment option.

In addition, whenever a conversion error due to dirty data occurs in the SQL Engine, Model 204 error message 1296 is logged to the audit trail. This message identifies the file, field, and record number of the field that experienced the conversion error.

Handling NOT NULL, UNIQUE, and multiply occurring data

If an SQL column is defined as NOT NULL, the Model 204 SQL Server does not allow SQL updates that result in a null value in the corresponding Model 204 field.

Note: SOUL operations can introduce a null into a Model 204 field that is mapped to a NON NULL column. These operations circumvent Model 204 SQL NON NULL checking, which is enforced only for SQL operations. An SQL SELECT against such a column would return the null value.

Except for nested table columns, the SQL Server does not inform you when null values are found for a NOT NULL column. If a row of null values is found for a nested table, the SQL Server issues a warning message, bypasses the record, and continues processing. If a row with some, but not all, null values is found for a nested table, the SQL Server ends the processing of the statement with a negative SQL code.

No retrievals or updates are allowed against an SQL UNIQUE column not mapped to a Model 204 ORDERED UNIQUE field. The uniqueness of the data in such mappings is guaranteed by Model 204 UNIQUE attribute checking. If you redefine the Model 204 field to be not-UNIQUE, the SQL Server prevents retrievals or updates against the corresponding UNIQUE column.

If the Model 204 multiply occurring fields mapped to the columns of a nested table do not have the same number of occurrences, attempts to query, fetch, or update rows with mismatched occurrences have unexpected retrieval results and produce no error return code. Inserts of data into such fields are permitted; they do not result in detection of the mismatches.

Handling mixed numeric and nonnumeric data

SQL data typing is not well suited for handling mixed numeric and nonnumeric data, for example a Model 204 CODED BIN field. If this were the Model 204 field format, the best choice for SQL column type would be CHAR. Although specifying CHAR allows only character operations with the data, at least the data is convertible.

Rocket Software recommends that you examine the actual data stored in a field before assigning an SQL data type. Match the physical data characteristics and not simply the designated Model 204 data attribute.

Observing data precision limits

This section discusses discrepancies between the precision of the data stored in Model 204 fields and the precision available to the data types of the SQL columns mapped to those fields. Data precision considerations are discussed for decimal integer data and then for floating point format.

Decimal integer

The Model 204 SQL Server treats data mapped to SQL DECIMAL and NUMERIC the same. The SQL Server supports user-defined scale and precision for DECIMAL and NUMERIC (where 0 ≤ scale ≤ precision) and maintains both formats with a maximum precision of 15 decimal digits.

The SQL Server treats data mapped to SQL INTEGER and SMALLINT the same. The SQL Server maintains both SQL INTEGER and SMALLINT column formats with a maximum precision of four bytes (31 bits plus one bit for the sign: the range from decimal -2147483648 to 2147483647).

The 4-byte integer precision limit is in effect for all operations involving this data, and any operations that exceed this limit result in an error message.

Note: Remember that the Model 204 BINARY field attribute has at most 30 bits of precision available. Mapping Model 204 fields to SQL DECIMAL, NUMERIC, INTEGER, and SMALLINT columns is subject to the restrictions listed in Model 204/SQL data format incompatibilities.

Floating point

Model 204 FLOAT (floating point) fields can be one of the following lengths, reflecting the possible precision:

This precision... Is equivalent to...
4-byte floating point (FLOAT LEN 4) 6 decimal digits of precision
8-byte floating point (FLOAT LEN 8) 15 digits of precision
16-byte floating point (FLOAT LEN 16) 31 digits of precision

Model 204 SQL also defines SQL column precision available to the SQL floating point data types. Data you attempt to store or extract that is longer than the precision limit is truncated either before storage or before extraction:

This precision... Is limited to...
REAL and FLOAT (binary precision ≤ 21) 6 decimal digits of precision
DOUBLE PRECISION and FLOAT (binary precision > 21) 15 decimal digits of precision

As in Model 204 SOUL, 15 significant digits is the Model 204 SQL maximum. You can still retrieve data stored in a FLOAT LEN 16 field, for example, but the precision of the retrieved value will be no more than 15.

SQL processing floating point numbers

SQL processing of floating point values greater than the largest valid value in:

  • An INSERT statement results in:

    SQL error -103. '7.237E75 is an invalid numeric literal.'

  • A SELECT statement returns an SQL warning with:

    'Invalid data was skipped by the SQL Engine.'

  • A SELECT DISTINCT statement retrieves no data for such a value.

Mapping recommendations

The precision limits for Model 204 fields and SQL columns dictate the recommended mappings shown in Mapping floating point fields. These mappings are most efficient in terms of space usage and precision preservation.

For example, if you map a FLOAT LEN 8 field to an SQL REAL column, you will lose some of the precision of your stored data, or you will waste space by storing 6-significant digit data in 15-significant digit fields, or both.

Mapping floating point fields

Model 204 field attribute Most compatible SQL data type Maximum precision
(decimal digits)
FLOAT LEN 4 REAL
FLOAT ≤ 21		 6
FLOAT LEN 8 DOUBLE PRECISION
FLOAT > 21		 15

Remember, the actual precision of the data returned to an SQL application is never greater than the precision of the stored data, which is always the precision of the defined format of the Model 204 fields. To return the data according to your SQL data type specification, the SQL Server converts (rounds, truncates, expands) the field values.

You can't specify a precision on the column that's greater than that of the Model 204 field, or at least it won't work. If you have a Model 204 field defined as FLOAT LEN 4, and you define the precision of the mapped SQL column as FLOAT(53), then you will get an SQL error code (5518), or the row will be skipped, depending upon the Dirty Data options.

If the SQL data type precision you specify does not match the precision of the field data, the actual precision of the data returned to you has the lower precision of the two. If you specify a lower precision than the stored data, the data is truncated to give the lower precision you specify; if you specify a higher precision than the stored data, the data is expanded to meet your specification, but it retains the lower precision of its storage format.

To achieve the precision you specify for an SQL column, make sure the column is mapped to the Model 204 FLOAT LEN that matches your specification. Also, remember that your mappings are subject to the incompatibility restrictions listed in Model 204/SQL data format incompatibilities.

You can also map a Model 204 FLOAT field to an SQL DECIMAL or NUMERIC column. The precision considerations are the same as above. For example, mapping a DECIMAL 4 to FLOAT LEN 8 loses precision, wastes space, or both.

In general, Model 204 SQL precision and conversion rules match those for Model 204 SOUL.

For more information about Model 204 floating point fields, see the Field design topic.

Converting SQL data types for display

The preceding discussion of precision limits does not address the conversion of retrieved data to the final display or print format for your application. These conversions are from one SQL data type to another.

Model 204 SQL processing of client result packets converts data in SQL columns to CHARACTER data.

These conversions primarily affect numeric data and might result in unexpected truncation or rounding of data or loss of least significant digits. You need to be aware of the following:

  • In Model 204 SQL conversions to CHARACTER:
    • If from FLOAT, results might be in scientific notation, and the number of significant digits displayed is not under user control.
    • Truncation and/or rounding rules might vary with the client platform. For example, where 2 and 5 are the sixth and seventh digits, respectively, of a retrieved value, Model 204 SQL might round up and display 3 for a Connect★ client.
    • Your data might be truncated (with a warning message) if the display format is not long enough.
  • In Model 204 SQL conversions from FLOAT:
    • Conversions to any data type risk truncation and/or rounding.
    • Truncation or rounding affects the least significant digits or characters on the right. The leading digit and magnitude are always preserved.
    • Truncation or rounding occurs without a warning message or notice.
    • Conversions of 8-byte float to 4-byte float risk loss of least significant digits, because 8-byte float holds more significant digits than 4-byte float.
  • Model 204 4-byte (and 8-byte) floating point numbers use IBM mainframe floating point representation and can accommodate exponents as large as 75. IBM PC clients use IEEE floating point representation and are limited to exponents as large as 38 for 4-byte floating point numbers. If you are an IBM PC client and want to retrieve the full Model 204 4-byte floating point range, you must request data type conversion to 8-byte float (DOUBLE or FLOAT 22 or greater) or CHARACTER.

LOB fields in SQL statements

The SQL Server supports the definition, update and retrieval of columns containing CLOB (Character Large Object) and BLOB (Binary Large Object) data.

CLOB/BLOB sizes

The maximum size LOB (Large Object) that may be transferred is dependent upon the amount of storage available to allocate the SQLBUF and the Universal Buffer. The SQLBUF and the Universal Buffer must be large enough to hold the entire LOB. Model 204 v7.4 SQL supports a maximum size CLOB/BLOB of up to 1 GB for UPDATE or INSERT and up to 2 GB for SELECT depending on the amount of server memory available.

The CHAR_MAX_LENGTH in the SQL catalog is set to a non-zero value for CLOB and BLOB fields. This value is for internal use only.

SQL statements supporting CLOB/BLOB data types

The CREATE TABLE, SELECT, INSERT, UPDATE, and DELETE SQL statements have been enhanced to support the CLOB and BLOB data types. See Using CLOB or BLOB data for details on using large object data in CREATE TABLE.

SELECT

To download a CLOB or BLOB column, specify the column name in the SELECT statement as you would for any other column data type. However, you cannot perform a search on a CLOB/BLOB column. In other words a CLOB or BLOB column name cannot be specified in the WHERE clause.

For example, you can use the following SELECT statement to download the User Language Manual from the MANUALS table:

SELECT MANUAL FROM MANUALS
WHERE NAME = 'User Language Manual'

(For the complete example defining MANUALS, see Using CLOB or BLOB data.)

INSERT

To insert a CLOB or BLOB column into a row, the INSERT statement specifies the CLOB or BLOB column name as it would any other type of column. The value of the CLOB or BLOB data must be specified as a parameter. INSERT and UPDATE of LOB data through SQL requests can be CPU intensive.

Therefore, Rocket Software recommends:

  • When inserting or updating very large LOBs (over 100MB) or many small/medium size LOBs (0-100MB), set Model 204 USER priority to low for users doing such SQL requests, to allow other users in the Online to process normally.
  • If possible, such updating should be done during non-peak hours.
  • Determine the CPU usage effects of SQL LOB processing applications for each instance of the application before distributing such applications to users.

Normal single (or small number) INSERT/UPDATE of small LOBs should not noticeably affect other users.

UPDATE

To update a CLOB/BLOB field in a row, the UPDATE statement specifies the CLOB or BLOB field name as it would any other type of field. The value of the CLOB or BLOB data must be specified as a parameter.

DELETE

To delete a row containing a CLOB or BLOB column, use the DELETE statement with its usual syntax.

Limitations

Following are the known limitations when using CLOB or BLOB fields in SQL statements.

LOB values cannot:

  • be key values
  • be compared in predicates
  • appear in any clause which will involve a data comparison, including but not limited to:
    • GROUP BY
    • HAVING
    • ORDER BY
    • SELECT DISTINCT
    • WHERE
    • ON
    • IN
    • LIKE

Multiple CLOB/BLOB fields cannot be specified in an INSERT or UPDATE statement. To store multiple CLOB/BLOB columns per row, you must execute a separate UPDATE statement for each CLOB/BLOB. This limitation does not apply to SELECT; you can specify multiple CLOB/BLOB columns in the SELECT statement.

ONLINE Parameter Considerations

The SQLBUFSZ parameter defines the length of the maximum incoming SQL message.

Retrieved from "https://m204wiki.rocketsoftware.com/index.php?title=SQL_representation_of_Model_204_data&oldid=76316"