Features Added in FOCUS 7.7.06

The AUTOSYN facility creates synonyms (Master and Access Files) for existing tables using RDBMS catalog definitions and user selections. All data types supported by the adapter are available for the generated Master Files.

Only single-segment Master and Access Files can be created with this facility. Each synonym created describes a single table or a view of a single table.

Master Files are generated in the first writable data set allocated to DDNAME MASTER. Access Files are generated in the first writable data set allocated to DDNAME ACCESS, which is a required DDNAME for using this facility to generate synonyms. For running requests and for viewing or editing synonyms, both the ACCESS and FOCSQL DDNAMEs are supported.

A synonym for a table in a relational data source can contain declarations in the Access File specifying which columns should have indexes.

When you issue a CREATE FILE command for a table, an index is created in the relational DBMS for the primary key and for each index specified in the Access File.

If you want to create a table without creating all of the indexes at that time, you can omit the index declarations from the Access File and issue the CREATE FILE command to create only the table and the index on the primary key. Then, at a later time, you can add the index declarations to the Access File and issue the CREATE FILE command with the INDEXESONLY phrase. The option of adding the indexes later makes loading data into a relational table much faster.

The FOCUS join command and conditional join command have a FULL OUTER join option.

A full outer join returns all rows from the source data source and all rows from the target data source. Where values do not exist for the rows in either data source, null values are returned. FOCUS substitutes default values on the report output (blanks for alphanumeric columns, the NODATA symbol for numeric columns).

The full outer join is only supported for use with those relational data sources that support this type of join, in which case the FOCUS join syntax is optimized (translated to the full outer join SQL syntax supported by the RDBMS). Use of this syntax for any data source that does not support a full outer join produces an error message.

In prior releases, fields participating in the key to a table had to be described first in the Master File. The number of key fields was identified by the KEYS=n attribute in the Access File, where n is the number of key fields.

While this method is still supported by the relational adapters, it has been deprecated. In Version 7 Release 7.06, new syntax is available that does not require any reordering of the fields in the Master File. If an Access File with the KEYS=n syntax is opened in the Synonym Editor, the Synonym Editor will convert it to the new syntax.

Simplified character functions have streamlined parameter lists, similar to those used by SQL functions. They are optimized against a wide variety of Relational data sources. The simplified character functions introduced in this release are:

• SUBSTRING - Extracts a substring from a source character string.
• TOKEN - Extracts a token from a string.
• LTRIM - Removes all blanks from the left end of a character string.
• RTRIM - Removes all blanks from the right end of a character string.
• TRIM_ - Removes leading characters, trailing characters, or both from a character string.
• CHAR_LENGTH - Returns the character length of a source string.
• LOWER - Returns the character string with all letters lowercase.
• UPPER - Returns the character string with all letters uppercase.
• POSITION - Returns the position of the first occurrence of a specified sample string in a source string.

The SQL Optimization Report provides optimization information for each function by adapter.

Simplified date and date-time functions have streamlined parameter lists, similar to those used by SQL functions. They are optimized against a wide variety of Relational data sources. The simplified date and date-time functions introduced in this release are:

• DTRUNC - given a date or timestamp and a component, DTRUNC returns the first date within the period specified by that component.
• DTADD - given a date in standard date or date-time format, DTADD returns a new date after adding the specified number of a supported component. The returned date format is the same as the input date format.
• DTPART - given a date in standard date or date-time format and a component, DTPART returns the component value in integer format.
• DTDIFF - given two dates in standard date or date-time formats, DTDIFF returns the number of given component boundaries between the two dates. The returned value has integer format for calendar components or double precision floating point format for time components.

The SQL Optimization Report provides optimization information for each function by adapter.

The following functions have been optimized for most SQL adapters, as reflected in the SQL Optimization Report.

• TRIMV with the options L or B (left or both).
• TRIM.
• POWER (fld ** exponent).
• ARGLEN.

The following functions are optimized as reflected in the SQL Optimization Report.

• DATEADD. With YEAR, MONTH, and DAY parameters.
• DATECVT. Optimized for DB2 with 6-digit legacy date formats I6YMD, I6MDY, I6DMY, P6YMD, P6MDY, P6DMY, A6YMD, A6MDY, and A6DMY.
• DATEDIFF. With YEAR, MONTH, and DAY parameters.
• DATEMOV. With BOW, EOW, BOM, EOM, BOQ, EOQ, BOY, and EOY parameters.
• DPART. With YEAR, MONTH, DAY, QUARTER, and WEEKDAY parameters.
• HADD. With YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND parameters.
• HDATE.
• HDIFF. With YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND parameters.
• HDTTM. With the following parameter combinations: "8,'HYYMDS'", "8,'HYYMDs'", and "10,'HYYMDm'".
• HGETC.
• HPART. With YEAR, MONTH, DAY, QUARTER, WEEKDAY, DAY-OF-YEAR, WEEK, HOUR, MINUTE, SECOND, MILLISECOND, and MICROSECOND parameters. The optimized HPART function with the WEEK parameter is compatible with FOCUS only in the presence of the WEEKFIRST=ISO2 setting.
• TODAY.

This feature implements a SET command that generates HOLD files as SQL_SCRIPT files when no format is specified on the ON TABLE HOLD command.

Note: If the adapter cannot generate SQL requests for a complex TABLE request (due to expression complexity or other reasons), the system automatically downgrades the HOLDFORMAT setting to BINARY for that request only. The procedure will run, but more slowly (with extract to disk). This can be used for application conversions to SQL_SCRIPT, such as BCEE-generated or MFACT-generated procedures.

The Adapter for Teradata now supports read access to the PERIOD and INTERVAL data types.

The PERIOD data type is mapped to Alphanumeric (An), where n = 24 to 60, depending on the native PERIOD format.

The INTERVAL data type (all formats) is mapped as An, where n = 2 to 21, depending on the INTERVAL format.

Teradata introduced Extended Object Name (EON) in v.14.1. The Adapter for Teradata now supports table and view names (containing multi-byte characters) with length up to 128-bytes.

The Adapter for Teradata now supports the NUMBER(p,s) data type that was introduced in Teradata Version 14. Numeric values with up to 31 digits are supported.

The optimization of some FOCUS features and functions can now be controlled using the FEATOPT SET command. This enables users to switch off SQL optimization for a particular function or feature when the optimization might produce unsatisfactory results.

The syntax is:

ENGINE SQL<engine> SET FEATOPT <focus_feature> OFF

The default value is ON, which means that the given function will be passed to SQL or the feature will be optimized. The setting can be applied to the following FOCUS features and functions:

An SQL SELECT statement with all the sources in the same relational database can now be used with an ON TABLE HOLD AS name FORMAT SAME_DB command. This uses the underlying database capabilities, such as INSERT INTO ... SELECT, that provide much faster throughput than alternative methods.

Synonyms created for SQL strings without parameters can now be used in the following commands/situations:

• JOIN
• WHERE clause (passed to SQL)
• Aggregation (passed to SQL)
• As a result of the ON TABLE HOLD FORMAT SAME_DB (passed to SQL).

The SQL strings will be processed as subqueries embedded in a FROM clause of the generated SELECT query. The new behavior is triggered by the presence of a SUBQUERY=Y keyword in the Access File describing the location of the SQL string file. SUBQUERY=Y is not permitted for Access Files describing SQL strings that use SQL parameter markers, for example '?' or ':001'.

Surrogate Keys Support has been enhanced and now includes fields defined with the PACK data type. Therefore, auto-incrementing can now be assigned to PACK data type fields.

The AUTOINCREMENT field uses the numeric data type with the widest value range allowed by a particular DBMS for the autonumbering: DECIMAL, BIGINT, SERIAL, SERIAL8, or BIGSERIAL.

AUTOINCREMENT is supported by the following adapters:

• DB2 (LUW, z/OS, and i5)
• Informix
• JDBC (Derby)
• Microsoft SQL Server
• MySQL
• Oracle
• PostgreSQL
• Red Brick
• Sybase (ASE and IQ)
• Teradata

BY HIERARCHY reporting now can be applied to relational data sources. The end user is responsible for properly representing the hierarchy in the Master File. The Dimension Builder in the Data Management Console enables you to assign the properties of the hierarchy.

The default tag name used by the FOCUS SQL generator is T1, T2, and so on. A new setting has been introduced that enables you to assign tag names to match the segment names that contain the referenced fields. To switch to using the SEGNAMEs as tag names, use the following setting:

SQL engine SET SQLTAG SEGNAME

To switch back to the T1, T2 tag names in the generated SQL, which is the default, use the following setting:

SQL engine SET SQLTAG SEGNUM

The ability to run a request against a synonym for an SQL string has been enhanced to allow the parameterization of the SQL string using Dialogue Manager global variables. After the synonym has been created against the relational adapter where the SQL will be processed, global variables can be added to the source SQL file. The Synonym Editor is then used to add the Variable Declarations.

The following example illustrates this feature. The .sql file contains the following:

SELECT COLUMNA, COLUMNB, COLUMNC FROM test1
WHERE COLUMNA = 'test'

The synonym created will have three fields. The SQL can now be edited to add a global variable in place of the WHERE value test:

WHERE COLUMNA = &&VARIABLE1

The Synonym Editor is then used to add the variable declaration:

FILENAME=test1, SUFFIX=SQLMSS , $
 VARIABLE NAME=&&VARIABLE1, QUOTED=ON, $

The global variable can be set prior to running a request:

-SET &&VARIABLE1 = 'NEWVALUE'
TABLE FILE TEST1
PRINT *
END

The Adapter for Oracle can now describe a column defined in the DBMS as native XMLType.

Oracle XMLType can be accessed through the getCLOBVal() native method using any of two techniques:

• Oracle view, where getCLOBVal() converts XMLType into CLOB.
• DEFINE field that uses DB_EXPR() applied to XMLType column(s), if the first technique is not desirable.

For example, with an Oracle table with following structure:

ORAXML 
---------------------
INTFLD  INT
XMLFLD XMLType

The first technique requires you to use Create View in Oracle:

CREATE VIEW ORAXML_V (I_FLD,X_FLD)
AS
SELECT a.INTFLD,
       a.XMLFLD.getCLOBVal()
FROM ORAXML a;

You can then use the standard approach which combines Oracle and XML synonyms, as described in the documentation for the Adapter for XML.

The second technique does not require using Create View in Oracle. The virtual field can be used instead.

For example, assume the synonym ORAXML_S is created for the table above and the XMLFLD is mapped as TX50L, TX. Then the following statements:

DEFINE FILE ORAXML_S
XMLFLD/A32767 WITH INTFLD=DB_EXPR(T1.XMLFLD.getCLOB());
END

TABLE FILE ORAXML_S PRINT XMLFLD WHERE INTFLD EQ <value>
END

will be optimized as:

SELECT
  (T1.XMLFLD.getCLOBVal()),
  T1."INTFLD"
  FROM 
  ORAXML T1
  WHERE 
  T1."INTFLD" = <value>

and will bring Oracle XMLType data into the report.

The Adapter for Oracle now can describe report and update Oracle columns that are defined as NCLOB (National Character Large Objects) when FOCUS is configured for Unicode.

The SQL adapters can pass virtual fields that call certain SQL scalar functions to the relational engine for processing. This enables you to use SQL functions in a request even when they have no equivalent in the FOCUS language. The function must be row-based and have a parameter list that consists of a comma-delimited list of column names or constants. In order to reference the function in an expression, prefix the function name with SQL.

If the virtual field is in the Master File, both TABLE requests and those SQL requests that qualify for Automatic Passthru (APT) can access the field. If the virtual field is created by a DEFINE FILE command, TABLE requests can access the field. The function name and parameters are passed without translation to the relational engine. Therefore, the expression that creates the DEFINE field must be optimized, or the request will fail.