x

The Data Management Console facility for Sample Data now displays the columns described with a USAGE value of TXnn. Users can test CLOB/TX fields with sample data.

x

A synonym for an RDBMS table indicates the column(s) with a Primary Key or INDEX. This information is added by the Create Synonym process. When the CREATE FILE command is used for the synonym, the table is created with the INDEX(ES) or with the Primary Key.

x

The relational adapters optimize FOCUS Boolean expressions as SQL CASE expressions, for example:

CASE 
   WHEN <boolean expression> THEN 1 
   ELSE 0 
END

x

You can capture the SQL Data Definition Language (DDL) generated during a CREATE FILE command in an external file called filename.sql. If the DDLONLY keyword is specified in the CREATE FILE command, the DDL will be generated and captured into filename.sql, but it will not be executed, and the table will not be created (or recreated).

The syntax for the CREATE FILE command is:

CREATE FILE XXX [DROP] [NOMSG] [DDLONLY filename] 

x

The DEFINE FUNCTION syntax can be sent directly to an SQL engine as long as all expressions used in the function can be optimized.

Note that in order for DEFINE FUNCTION syntax to be optimized, the types and lengths of the arguments used to call the DEFINE FUNCTION must exactly match the types and lengths of the DEFINE FUNCTION parameters.

For example, the Oracle data source EMPINFO has columns LAST_NAME (alias LN) and FIRST_NAME (alias FN). The following DEFINE FUNCTION takes two arguments, N1 and N2, and sets a flag, which it returns as EMPNAME. EMPNAME has the value 1 if N1 is Smith and N2 is Richard:

DEFINE FUNCTION EMPNAME (N1/A15, N2/A10)
DEF1/I1 = IF N1 EQ 'SMITH' THEN 1 ELSE IF N1 EQ 'JONES' THEN 2
             ELSE IF N1 EQ 'CARTER' THEN 3 ELSE 0;
DEF2/I1 = IF N2 EQ 'RICHARD' THEN 1 ELSE IF N2 EQ 'BARBARA' THEN 2
          ELSE 0;
EMPNAME/I1 = IF DEF1 EQ 1 AND DEF2 EQ 1 THEN 1 ELSE 0;
END

The following request uses the result of the DEFINE FUNCTION in an aggregation command. Note that the format of LAST_NAME exactly matches the format defined for N1, and the format of FIRST_NAME exactly matches the format defined for N2:

SET TRACEUSER = ON
SET TRACESTAMP = OFF
SET TRACEON = STMTRACE//CLIENT
ENGINE SQLORA SET OPTIFTHENELSE ON
DEFINE FILE EMPINFO
DEF3/I1 = EMPNAME(LAST_NAME, FIRST_NAME);
END
TABLE FILE EMPINFO
SUM MAX.LAST_NAME IF DEF3  EQ 1

The trace output shows that the IF-THEN-ELSE expressions from the DEFINE FUNCTION are translated to an SQL expression in the WHERE predicate of the SELECT statement passed to the RDBMS:

AGGREGATION DONE ...
SELECT  
MAX(T1."LN")
FROM
EMPINFO T1
WHERE   
((CASE WHEN (((CASE (T1."LN")  WHEN 'SMITH' THEN 1 WHEN 'JONES' 
THEN 2 WHEN 'CARTER' THEN 3 ELSE 0 END) = 1) AND ((CASE   
(T1."FN")  WHEN 'RICHARD' THEN 1 WHEN 'BARBARA' THEN 2 ELSE 0    
END) = 1)) THEN 1 ELSE 0 END) = 1); 

x

The SQL adapters support SQL scalar functions in a request. The function must be row based and have a parameter list that consists of a comma-delimited list of columns, constants, or expressions. In order to reference the function in a request, prefix the function name with SQL.

The following example creates the table GGORA, which has columns Biscotti, Capuccino, Croissant, Espresso, and Latte, some of which are null. It then issues a TABLE request that calls the SQL function COALESCE. COALESCE returns the first column in the parameter list that does not contain a null value, if one exists.

The Master File for GGORA is:

FILENAME=GGORA  , SUFFIX=SQLORA     , IOTYPE=STREAM, $
  SEGMENT=GGORA, SEGTYPE=S0, $
    FIELDNAME=CATEGORY, ALIAS=CAT, USAGE=A11, ACTUAL=A11,
      MISSING=ON, $
    FIELDNAME=Biscotti, ALIAS=BIS, USAGE=I08, ACTUAL=A08,
      MISSING=ON, $
    FIELDNAME=Capuccino, ALIAS=CAP, USAGE=I08, ACTUAL=A08,
      MISSING=ON, $
    FIELDNAME=Croissant, ALIAS=CROI, USAGE=I08, ACTUAL=A08,
      MISSING=ON, $
    FIELDNAME=Espresso, ALIAS=ESP, USAGE=I08, ACTUAL=A08,
      MISSING=ON, $
    FIELDNAME=Latte, ALIAS=LAT, USAGE=I08, ACTUAL=A08,
      MISSING=ON, $
    FIELDNAME=Mug, ALIAS=MUG, USAGE=I08, ACTUAL=A08,
      MISSING=ON, $
    FIELDNAME=Scone, ALIAS=SCO, USAGE=I08, ACTUAL=A08,
      MISSING=ON, $
    FIELDNAME=Thermos, ALIAS=THER, USAGE=I08, ACTUAL=A08,
      MISSING=ON, $

The following procedure creates the table GGORA based on the FOCUS data source GGSALES (with MISSING=ON attributes added to the DOLLARS field in the Master File):

APP HOLDMETA baseapp
APP HOLDDATA baseapp
SET HOLDMISS = ON
SET HOLDMISS = ON
SET ASNAMES = ON
TABLE FILE GGSALES
SUM DOLLARS AS '' 
BY CATEGORY
ACROSS PRODUCT
WHERE PRODUCT NE 'Coffee Grinder' OR 'Coffee Pot'
ON TABLE HOLD AS GGFLAT FORMAT ALPHA
END
 
FILEDEF GGFLAT DISK baseapp/ggflat.ftm 
CREATE FILE GGORA
MODIFY FILE GGORA
FIXFORM FROM GGFLAT
DATA ON GGFLAT
END

The following TABLE request calls the SQL function COALESCE:

SET TRACEUSER = ON
SET TRACESTAMP = OFF
SET TRACEON = STMTRACE//CLIENT
 
DEFINE FILE GGORA
VALUE/I8 MISSING ON = SQL.COALESCE(Biscotti, Capuccino, Croissant, Espresso, Latte);
END
TABLE FILE GGORA
PRINT Biscotti Capuccino Croissant Espresso Latte VALUE
BY CATEGORY
ON TABLE SET PAGE NOPAGE
END

The trace output shows that the SQL function call was passed to the RDBMS:

SELECT       
COALESCE(T1."BIS",T1."CAP",T1."CROI",T1."ESP",T1."LAT"),    
T1."CAT",   
T1."BIS",   
T1."CAP", 
T1."CROI",   
T1."ESP",  
T1."LAT" 
FROM    
GGORA T1    
ORDER BY   
T1."CAT";

The output is:

x

FOCUS DEFINE and IFTHENELSE expressions are now optimized as SQL CASE logic and SQL COUNT expressions for Informix, MySQL, IDMS, MetaMatrix, NonStop, Pervasive, and Nucleus data sources.

x

Most SQL adapters can now translate FOCUS sorting calls into the SQL ORDER BY expression. This provides better optimization.

x

The Adapter for Teradata includes an Owner/Schema selector for choosing a data source filter on the Create Synonym page of the Web Console.

x

The Teradata Array Insert and Array Fetch mechanisms are supported by the ODBC Adapter for Teradata when the driver is from the TTU 13.1 utility pack.

Array Insert provides significantly improved performance. It allows the use of insert sizes other than 1, for example:

SQL SQLDBC SET INSERTSIZE 100

The typical range is from 1 to 5000, and the best value depends on the particular DBMS and hardware configuration.

Note: Array Insert can be used in MODIFY with the LOAD ONLY option. Array Fetch provides only limited performance improvement.

x

Block Size Insert (also known as Array Insert) is supported by the CLI Adapter for Teradata.

Block Size Insert provides significantly improved performance. It allows the use of insert sizes other than 1, for example:

SQL SQLDBC SET INSERTSIZE 909

The typical range is from 1 to 909, and the best value depends on the particular DBMS and hardware configuration.

Note: Block Size Insert can be used in MODIFY with the LOAD ONLY option. Other options will perform the SELECT first with FETCH, causing performance degradation. Due to internal Teradata CLIv2 limits, an insert size greater than 909 currently provides no performance improvement.

x

The Adapter for Informix supports the Informix SDK 3.7.

x

The Adapter for Informix supports Native CLOB and BLOB data types for the Informix IDS server.