x

The SUBSTR and SUBSTV FOCUS functions have been optimized to their native SQL counterparts.

x

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.

Note:* For DB2, any calculations using the last day in February (28/29 for leap year) generate incorrect predicates with ADD_MONTHS and inaccurate calculations. The fix is available in DB2 9.7 FixPack 4.

x

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.

Valid OPTIFTHENELSE settings are:

• ON (default). Generates SQL CASE expressions for FOCUS IF-THEN-ELSE expressions.
• OFF. Suppresses generation of SQL CASE or DECODE expressions for IF-THEN-ELSE and/or DECODE expressions.
• CASE/ON CASE. Equivalent to ON.
• NOCASE/OFF NOCASE. Equivalent to OFF.

Starting in Version 7 Release 7, relational adapters optimize IF-THEN-ELSE DEFINE fields only as SQL CASE statements. The WHERE predicate without CASE is no longer generated.

x

Bulk loading from a disk file is supported for IBM iSeries.

Two loading method are available:

• INSERT. Insert the rows only. This is the default.
• REPLACE. Drops the existing destination table, and creates a new table.

x

A redistributable run-time client is available on Windows using a separate configuration option for the Adapter for DB2.

x

In i5/OS, the metadata of a stored procedure that has an answer set will include column descriptions. These will appear as title fields in the synonym, as shown in the following example.

FILENAME=TEST_DESCRIPTIONS, SUFFIX=DB2 , $   
  SEGMENT=INPUT, SEGTYPE=S0, $   
    FIELDNAME=, ALIAS=DUMMY, USAGE=A1, ACTUAL=A1, MISSING=ON, $   
  SEGMENT=ANSWERSET1, SEGTYPE=S0, PARENT=INPUT, $   
    FIELDNAME=STORECODE, ALIAS=STORECODE, USAGE=A6, ACTUAL=A6,   
      TITLE='Store,Code', $   
    FIELDNAME=STORENAME, ALIAS=STORENAME, USAGE=A30, ACTUAL=A30,   
      MISSING=ON, TITLE='Store,Name', $   
    FIELDNAME=COUNTRY, ALIAS=COUNTRY, USAGE=A15, ACTUAL=A15,   
      MISSING=ON, TITLE='Country', $   
    FIELDNAME=REGION, ALIAS=REGION, USAGE=A25, ACTUAL=A25,   
      MISSING=ON, TITLE='Region', $

x

The Adapter for DB2 supports primary and secondary authorization security checking for DSN3SATH. Three modifications are required, as shown in the following example.

Top of page

Example: Changing DSN3SATH for RACF and eTrust CA-Top Secret Sites

1. Search for the SATH001 label - add two lines (FOCDSN3):

SATH001  DS    0H  
         USING WORKAREA,R11        ESTABLISH DATA AREA ADDRESSABILITY 
         ST    R2,FREMFLAG                SAVE FREEMAIN INDICATOR
         XC    SAVEAREA(72),SAVEAREA CLEAR REGISTER SAVE AREA
         . 
         . 
         .  
*********SECTION 1:  DETERMINE THE PRIMARY AUTHORIZATION ID  ************
*                                                                       *
*  IF THE INPUT AUTHID IS NULL OR BLANKS, CHANGE IT TO THE AUTHID       *
*  IN EITHER THE JCT OR THE FIELD POINTED TO BY ASCBJBNS.               *
*  THE CODE IN THIS SECTION IS AN ASSEMBLER LANGUAGE VERSION OF         *
*  THE DEFAULT IDENTIFY AUTHORIZATION EXIT.  IT IS EXECUTED ONLY        *
*  IF THE FIELD ASXBUSER IS NULL UPON RETURN FROM THE RACROUTE          *
*  SERVICE.  FOR EXAMPLE, IT DETERMINES THE PRIMARY AUTH ID FOR         *
*  ENVIRONMENTS WITH NO SECURITY SYSTEM INSTALLED AND ACTIVE.           *
*                                                                       *
*************************************************************************        
SPACE 
    LA    R1,AIDLPRIM         LOAD PARM REG1             <--ADD 
    CALL  FOCDSN3             GO GET THE IBI EXIT        <--ADD 
    CLI   AIDLPRIM,BLANK      IS THE INPUT PRIMARY AUTHID NULL
    BH    SATH020             SKIP IF A PRIMARY AUTH ID EXISTS

2. Search for the SATH020 label - add a comment box, add one line, and comment out four lines:

SATH020  DS    0H                  BRANCH TO HERE IF PRIMARY EXISTS
*****OPTIONAL CHANGE @CHAR7:  FALLBACK TO SEVEN CHAR PRIMARY AUTHID***
*                                                                    *
*  IF YOUR INSTALLATION REQUIRES ONLY SEVEN CHARACTER PRIMARY        *
*  AUTHORIZATION IDS (POSSIBLY TRUNCATED) DUE TO DB2 PRIVILEGES      *
*  GRANTED TO TRUNCATED AUTHORIZATION IDS, THEN YOU MUST BLANK OUT   *
*  COLUMN 1 OF THE ASSEMBLER STATEMENT IMMEDIATELY FOLLOWING THIS    *
*  BLOCK COMMENT. THEN ASSEMBLE THIS PROGRAM AND LINK-EDIT IT INTO   *
*  THE APPROPRIATE DB2 LOAD LIBRARY AS EXPLAINED IN AN APPENDIX      *
*  OF "THE DB2 ADMINISTRATION GUIDE".                                *
*                                                                    *
*  OTHERWISE, YOU NEED DO NOTHING.                                   *
*                                                            @KYD0271*
**********************************************************************
*      MVI   AIDLPRIM+7,BLANK    BLANK OUT EIGHTH CHARACTER 
       SPACE 
       . 
       . 
       .
*   RACF IS ACTIVE ON THIS MVS 
****************************************************************** <--ADD 
*                                                                * <--ADD 
* The logic was modified because in DB2 V8 AIDLACEE is always not* <--ADD 
* NULL. We used to honor AIDLACEE first, FOCDSN4 second and then * <--ADD 
* AS ACEE. Now we honor FOCDSN4 first, AIDLACEE second and then  * <--ADD 
* AS ACEE.                                                       * <--ADD 
*                                                                * <--ADD 
* 03/11/05   ASK0                                                * <--ADD 
****************************************************************** <--ADD 
  USING ACEE,R6             ESTABLISH BASE FOR ACEE        @KYL0108
  L     R6,AIDLACEE         Get => caller ACEE if any             <--ADD 
* ICM   R6,B'1111',AIDLACEE CALLER PASSED ACEE ADDRESS? @KYL0108 <-COMMENT 
* BZ    SATH024              NO, USE ADDRESS SPACE ACEE  @KYL0108 <-COMMENT 
* CLC   ACEEACEE,EYEACEE    IS IT REALLY AN ACEE?       @KYL0108 <-COMMENT 
* BE    SATH027             YES, PROCEED NORMALLY       @KYL0108 <-COMMENT 
      SPACE 1   
SATH024  DS    0H                  USE ADDRESS SPACE ACEE      @KYL0108
    .
    .
    .

3. Search for the SATH025 label - replace sath025 and add sath026 (FOCDSN4):

SATH025  DS    0H
                                            
    CALL  FOCDSN4              GO GET THE IBI EXIT (4=GROUP AUTH) <--ADD 
    LTR   R6,R6                DOES AN ACEE EXIST?  IF NOT,       <--ADD 
    BZ    SATH026              CHECK ACEE IN ADDRESS SPACE        <--ADD 
    CLC   ACEEACEE,EYEACEE     DOES IT LOOK LIKE AN ACEE?         <--ADD 
    BE    SATH027              YES, GO DO GROUPS                  <--ADD 
SATH026  DS    0H                                                  <--ADD 
   L      R6,ASCBASXB          GET ADDRESS SPACE EXTENSION BLOCK  <--ADD 
   L      R6,ASXBSENV-ASXB(,R6) GET ACEE ADDRESS                  <--ADD 
   CLC    ACEEACEE,EYEACEE     DOES IT LOOK LIKE AN ACEE?         <--ADD 
   BNE    SATH049              NO, THEN CAN'T DO GROUPS           <--ADD 
   DROP   R8                   DROP ASCB BASE REG                 <--ADD 
   SPACE 1                                                        <--ADD

SATH027  DS    0H              CHECK LIST OF GROUPS OPTION
   TM     RCVTOPTX,RCVTLGRP   IS LIST OF GROUPS CHECKING ACTIVE
   BZ     SATH040             SKIP TO SINGLE GROUP COPY IF NOT 
   DROP   R7                  DROP RCVT BASE REG  
   SPACE 1  
* RACF LIST OF GROUPS OPTION IS ACTIVE
   EJECT 
    .
    .
    .

x

The Oracle InstantClient is supported using a separate configuration option for the Adapter for Oracle. InstantClient software is prepackaged as part of the Reporting Server installation.

x

The Adapter for Oracle supports transparent data conversion for NCHAR/NVARCHAR fields when the Reporting Server is configured for Unicode, even if the Oracle instance is non-Unicode.

x

The Adapter for parAccel can translate certain calls to the DPART and HPART functions to SQL date and time functions, allowing the optimization of some TABLE requests with DEFINE fields that call these functions.

The DPART function extracts a specified component from a date value and returns it in numeric format. The HPART function extracts a specified component from a date-time value and returns it in numeric format. Calls to DPART and HPART are optimized when the second parameter is a case-insensitive YEAR, MONTH, QUARTER, DAY-OF-MONTH, or DAY. Calls to HPART are also optimized when the second argument is a case-insensitive HOUR, MINUTE, SECOND, or MICROSECOND.

x

The BIGINT data type is supported. It can be a signed, binary integer value from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

The data type should be mapped as USAGE=P20, ACTUAL=P10.

x

The Decimal/Numeric(n,m) data type is supported in a wider range. The supported precision length has been increased from 18 up to 31 digits.

x

The Adapter for 1010data supports creating and using synonyms based on 1010data stored procedures.

x

The following scalar functions have been optimized to their native SQL counterparts:

• ABS
• ACOS
• CONCAT or simply ||
• CURRENT_DATE or CURDATE
• CURRENT_TIME or CURTIME
• CURRENT_TIMESTAMP or NOW
• DAYNAME
• DAYOFMONTH
• DAYOFYEAR
• DIV (integer division)
• EXP
• IF (the functional form of CASE WHEN X1 THEN Y1 ... ELSE Z)
• IFNULL or NVL
• ROUND
• USER
• WEEK
• YEAR

The following aggregate functions have been optimized to their native SQL counterparts:

• AVG
• CORRELATION
• COUNT, including COUNT(*) and COUNT(DISTINCT ...)
• COVARIANCE
• MAX
• MIN
• STDEV
• SUM
• VARIANCE
• WAVG
• WSTDEV