Version 7.7.06

• Creating Synonyms for DB2 Data Sources. 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.
• Creating Indexes for an Existing Table. 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.
• Optimization of Full Outer Joins. The FOCUS join command and conditional join command now have a FULL OUTER join option.
• Support for Discontiguous Key Definition in the Access File. Key fields in the Master File can be listed in the order established by the relational DBMS or in any order that is convenient. New syntax is available that does not require reordering of the fields in the Master File.
• Optimization of Simplified Character Functions. The SUBSTRING, LTRIM, RTRIM, TRIM_, CHAR_LENGTH, LOWER, POSITION, and UPPER simplified functions are optimized to SQL.
• Optimization of Simplified Date and Date-Time Functions. The DTPART, DTRUNC, DTADD, and DTDIFF functions have been optimized for SQL adapters, as reflected in the SQL Optimization Report.
• Optimization of Legacy Character and Numeric Functions. The TRIMV function with the L/B (left/both) option, TRIM, ARGLEN, and POWER functions have been optimized.
• Selectively Controlling FOCUS Function Optimization Through FEATOPT. 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.
• Support for SELECT ON TABLE HOLD FORMAT SAME_DB. 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.
• Full Synonym Support Added for SQL Strings. Synonyms created for SQL strings without parameters can now be used in JOIN, WHERE clause (passed to SQL), Aggregation (passed to SQL), and as a result of the ON TABLE HOLD FORMAT SAME_DB (passed to SQL).
• AUTOINCREMENT for PACK Fields Added. 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.
• Support for FOCUS Amper Variables as SQL String Parameters. 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.
• Truncate Table Added to Existing Table. A new Prior to Load option has been added for existing tables in DB2 on Windows, UNIX, and zSeries platforms. The Truncate Table target property removes all rows from the target table without logging.
• Optimize the DEDIT and DSUBSTR Functions. The FOCUS functions, DEDIT and DSUBSTR with character arguments, are optimized to various native SQL character functions. This has been implemented for most SQL Adapters. These functions are designed to be used in multi-byte environments instead of the EDIT and SUBSTRING functions.
• Optimize the EDIT Function of Integer Arguments. The FOCUS function EDIT with an INTEGER argument has been optimized to various SQL native numeric functions. This is implemented for most SQL Adapters.
• Optimization of POWER Scalar Function. The Auto-Passthru mode takes advantage of the ability to translate the POWER function for most engines, such as DB2, Sybase, Oracle, Teradata, Microsoft SQL Server, MySQL, Sand Analytical Server (formerly Nucleus), Informix, Redbrick, Microsoft Access, Microsoft Excel, Ingres, IDMS, Postgres, Progress, Cache, Netezza, and SQL1010.
• Optimizing the UPCASE and LOCASE Functions. Most SQL Adapters will optimize the UPPER and LOWER case function to their RDBMS SQL counterparts.
• [RANKED] BY HIGHEST n [TOTAL] Optimization. Some SQL Adapters, such as Teradata, Oracle, Microsoft SQL Server, and Sybase, can optimize BY HIGHEST n/BY HIGHEST n TOTAL to SQL analytical functions RANK() or DENSE_RANK() with subsequent filtering by means of a QUALIFY clause (if supported), or an outer SELECT with a WHERE clause on the RANK value.
• SUBSTR and SUBSTV Optimization. The SUBSTR and SUBSTV FOCUS functions have been optimized to their native SQL counterparts for most SQL Adapters.
• Subquery Supported in SELECT, WHERE, and HAVING Clauses. A SQL subquery (query within a query) can be used in several places in the SQL Select statement, including a from list, as well as in a WHERE clause or a HAVING clause.
• Synonym Column Name Limit Increased. Synonym processing has been enhanced to support column names of up to 128 characters.
• Adapter for Adabas Support for Multiple SVCs and Logical Names. The Adapter for Adabas supports the Logical name to help identify the particular set of connection attributes defining connections to the same or different databases. This feature significantly enhances adapter flexibility by allowing access to databases with different SVCs and releases through the same adapter.
• SQL.Function Syntax for Direct DBMS Function Calls. 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.

Version 7.7.03

• Aggregation of Boolean Expressions. The relational adapters optimize FOCUS Boolean expressions as SQL CASE expressions.
• Capture of Data Definition Language While Using the CREATE FILE Command. You can capture the SQL Data Definition Language (DDL) generated during a CREATE FILE command in an external file called filename.sql.
• DEFINE FUNCTION Optimization. The DEFINE FUNCTION syntax can be sent directly to an SQL engine as long as all expressions used in the function can be optimized.
• Optimization of DATEADD and HADD Functions for YEAR and MONTH. The relational adapters now translate the DATEADD and HADD FOCUS functions to their SQL counterparts. The year/month calculations are optimized as an ADD_MONTHS() SQL function that will be processed by the DBMS engine. This is implemented for Oracle, DB2, Teradata, Sybase IQ, and MySQL.
• IF-THEN-ELSE Optimization Enhancement. IF-THEN-ELSE optimization has been enhanced.

  The default OPTIFTHENELSE setting is set to ON:

  ENGINE sqleng SET OPTIFTHENELSE ON 

  This setting optimizes all relational adapter IF-THEN-ELSE FOCUS expressions as CASE or DECODE SQL expressions.

• DSN3SATH Support for CA-ACF2. The Adapter for DB2 supports primary and secondary authorization security checking for DSN3SATH.
• Passing the Exact Number of Trailing Blanks to the DBMS. All relational adapters have been enhanced to pass character constants from the initial TABLE request to the database unchanged, without truncating trailing spaces.
• Capturing Rows Affected by DPT SQL Passthru Commands as Dialogue Manager Variables. The &ROWSAFFECTED variable is populated with the number of rows affected by the following DPT DML commands: INSERT, UPDATE, and DELETE.
• Passing the Equivalent of FOCUS PCT to the SQL Engine. The relational adapters optimize aggregation in the presence of the FOCUS PCT function (which has no direct SQL counterparts) to SQL SUM().
• Using the DPART Function in SQL Generation. A new DPART FOCUS function, which extracts a specified component from a date field and returns it in numeric format, can be passed to SQL for improved performance.
• Optimization of the HPART, DPART, HDIFF, HDATE, and DATEDIF Functions. The relational adapters can translate certain calls to the HPART, DPART, HDIFF, HDATE, and DATEDIF functions to SQL date and time functions, allowing optimization of some TABLE requests with DEFINE fields that call these functions.
• Passing Virtual Fields Defined as Constants to an SQL Engine. A virtual field defined as a constant is passed directly to an SQL database engine (RDBMS) for optimized processing that takes advantage of RDBMS join, sort, and aggregation capabilities.
• Improved Error Diagnostics in Expression Generation. Diagnostic messages and tracing for aggregation functions, DB-lookup of SQL tables, and Cluster join structures that explain SQL statement generation have been improved.
• CAF Support for SQL Statements With Lengths Greater Than 32K. Supports IBM’s increased length of SQL statements from 32K to 2MB.
• Surrogate Keys Support. FOCUS supports DB2 IDENTITY columns as surrogate keys using FIELDTYPE=R (read-only) in the Master File and three attributes in the Access File.
• Support of Multirow-Fetch for DB2 (CAF). The FETCHSIZE value is the number of rows to be retrieved at once using array retrieval techniques for the CLI adapter or a cursor with Rowset positioning and multirow Fetch for the CAF adapter. Accepted values are 1 to 32000. The default is 100. If the result set contains a column that has to be processed as a CLOB or a BLOB, the FETCHSIZE value used for that result set is 1.
• Support Added for Release 8 Extended Adabas Control Block (ACBX). The Adapter for Adabas supports the Extended Adabas Control Block (ACBX), introduced by SoftwareAG in Adabas 8.
• Adapter for Adabas: Support for Record Buffers Larger Than 32K. The Adapter for Adabas can read Adabas record buffers that are longer then 32K when processing MU/PE (simple PE only) fields.
• Adapter for Adabas: Support for LA and W Fields. Support has been added for the following Adabas 8 data types: LA fields (native Adabas CLOB data) and Unicode fields in W format.