The following features were added as of FOCUS 7.7.03
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);
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:
How to: Reference: |
A left outer join selects all records from the host table and matches them with records from the cross-referenced table. When no matching records exist, the host record is still retained, and default values (blank or zero) are assigned to the cross-referenced fields. The relational adapters may optimize any WHERE-based left outer join command in which the conditional expressions are supported by the RDBMS.
JOIN LEFT_OUTER FILE hostfile AT hfld1 [TAG tag1] [WITH hfld2] TO {UNIQUE|MULTIPLE} FILE crfile AT crfld [TAG tag2] [AS joinname] [WHERE expression1; [WHERE expression2; ...] END
where:
Specifies a left outer join. If you do not specify the type of join in the JOIN command, the ALL parameter setting determines the type of join to perform.
Is the host Master File.
Links the correct parent segment or host to the correct child or cross-referenced segment. The field values used as the AT parameter are not used to cause the link. They are used as segment references.
Is the field name in the host Master File whose segment will be joined to the cross-referenced data source. The field name must be at the lowest level segment in the data source that is referenced.
Is the optional tag name that is used as a unique qualifier for fields and aliases in the host data source.
Is a data source field with which to associate a DEFINE-based conditional join. For a DEFINE-based conditional join, the KEEPDEFINES setting must be ON, and you must create the virtual fields before issuing the JOIN command.
Specifies a one-to-many relationship between hostfile and crfile. Note that ALL is a synonym for MULTIPLE.
Specifies a one-to-one relationship between hostfile and crfile. Note that ONE is a synonym for UNIQUE.
Note: UNIQUE returns only one instance and, if there is no matching instance in the cross-referenced file, it supplies default values (blank for alphanumeric fields and zero for numeric fields).
The unique join is a FOCUS concept. The RDBMS makes no distinction between unique and non-unique situations; it always retrieves all matching rows from the cross-referenced file.
If the RDBMS processes a join that the request specifies as unique, and if there are, in fact, multiple corresponding rows in the cross-referenced file, the RDBMS returns all matching rows. If, instead, optimization is disabled so that FOCUS processes the join, a different report results because FOCUS, respecting the unique join concept, returns only one cross-referenced row for each host row.
Is the cross-referenced Master File.
Is the join field name in the cross-referenced Master File. It can be any field in the segment.
Is the optional tag name that is used as a unique qualifier for fields and aliases in the cross-referenced data source.
Is the name associated with the joined structure.
Are any expressions that are acceptable in a DEFINE FILE command. All fields used in the expressions must lie on a single path.
or
or
The SUBSTR and SUBSTV FOCUS functions have been optimized to their native SQL counterparts.
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.
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:
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.
How to: Reference: |
Optimization is the process in which the adapter translates the projection, selection, join, sort, and aggregation operations of a report request into their SQL equivalents and passes them to the RDBMS for processing.
In some cases the relational adapters can optimize IF-THEN-ELSE DEFINE expressions as part of aggregation or record selection.
In prior releases, two types of optimization were available for IF-THEN-ELSE DEFINE fields:
ENGINE SQL SET OPTIFTHENELSE {ON|OFF}
where:
Generates a CASE statement for IF-THEN-ELSE expressions and DECODE expressions whenever possible. ON is the default value. Note that CASE and ON CASE are synonyms for ON.
Does not optimize IF-THEN-ELSE or DECODE expressions. Note that NOCASE and OFF NOCASE are synonyms for OFF.
Consider the following request that has a WHERE condition on an IF-THEN-ELSE DEFINE field named DEF1:
ENGINE DB2 SET OPTIFTHENELSE ON DEFINE FILE DB2EMP DEF1 = IF (LAST_NAME EQ ' ') AND (FIRST_NAME EQ ' ') AND (DEPARTMENT EQ 'MIS') THEN 1 ELSE 0; END TABLE FILE DB2EMP PRINT DEPARTMENT LAST_NAME FIRST_NAME WHERE DEF1 EQ 1 END
The adapter generates an SQL request that incorporates the IF-THEN-ELSE condition corresponding to the IF-THEN-ELSE DEFINE field and the WHERE DEF1 EQ 1 test as a CASE statement:
SELECT T1."LN", T1."FN", T1."DPT" FROM DB2EMP T1 WHERE ((CASE WHEN (((T1."LN" = ' ') AND (T1."FN" = ' ')) AND (T1."DPT" = 'MIS')) THEN 1 ELSE 0 END) = 1) FOR FETCH ONLY;
The following request displays the maximum salary when an IF-THEN-ELSE DEFINE field named DEF2 equals 1:
ENGINE DB2 SET OPTIFTHENELSE ON DEFINE FILE DB2EMP DEF2 = IF LAST_NAME EQ 'SMITH' THEN 1 ELSE IF LAST_NAME EQ 'JONES' THEN 2 ELSE IF LAST_NAME EQ 'CARTER' THEN 3 ELSE 0; END TABLE FILE DB2EMP SUM MAX.CURR_SAL IF DEF2 EQ 1 END
The adapter generates an SQL request that incorporates the aggregation, the IF-THEN-ELSE DEFINE field, and the condition corresponding to the WHERE DEF2 EQ 2 test as a CASE statement:
AGGREGATION DONE ... SELECT MAX(T1."CSAL") FROM DB2EMP T1 WHERE ((CASE (T1."LN") WHEN 'SMITH' THEN 1 WHEN 'JONES' THEN 2 WHEN 'CARTER' THEN 3 ELSE 0 END) = 1) FOR FETCH ONLY;
The following request has a condition that is always false because the IF-THEN-ELSE DEFINE field named DEF3 is defined to be either 1 or 0, never 2:
ENGINE DB2 SET OPTIFTHENELSE ON DEFINE FILE DB2EMP DEF3 = IF FIRST_NAME EQ 'RITA' THEN 1 ELSE 0; END TABLE FILE DB2EMP PRINT FIRST_NAME IF DEF3 EQ 2 END
Because DEF3 EQ 2 will never be true, the adapter passes the CASE statement ((CASE (T1."FN") WHEN 'RITA' THEN 1 ELSE 0 END) = 2), which is always false to the RDBMS, returning zero records from the RDBMS:
SELECT T1."FN" FROM DB2EMP T1 WHERE ((CASE (T1."FN") WHEN 'RITA' THEN 1 ELSE 0 END) = 2) FOR FETCH ONLY;
All relational adapters have been enhanced to pass character constants from the initial TABLE request to the database unchanged, without truncating trailing spaces. It provides complete control over the number of specified trailing spaces. This is important for applications that communicate with RDBMS sensitive to trailing blanks.
The &ROWSAFFECTED variable is populated with the number of rows affected by the following DPT DML commands: INSERT, UPDATE, and DELETE.
&ROWSAFFECTED is initialized to -1 and is set to -1 by any Direct SQL Passthru command that does not return the number of rows affected, such as a SELECT statement. &ROWSAFFECTED is undefined for other types of requests, and remains unchanged for those requests.
Note: The value of &ROWSAFFECTED is overwritten each time a Direct SQL Passthru INSERT, UPDATE, or DELETE command is executed. If you want to retain the value, you must copy it to another Dialogue Manager variable or store it in a calculated field.
The &ROWSAFFECTED variable is independent of the adapter PASSRECS setting and is always populated for a Direct SQL Passthru INSERT, UPDATE, or DELETE command, regardless of the value of the PASSRECS parameter.
The following request creates an Oracle table:
TABLE FILE EMPLOYEE SUM LAST_NAME FIRST_NAME DEPARTMENT HIRE_DATE CURR_SAL CURR_JOBCODE BY EMP_ID ON TABLE HOLD AS EMPINFO FORMAT SQLORA END
The EMPINFO Master File generated by the request follows:
FILENAME=EMPINFO, SUFFIX=SQLORA SEGNAME=EMPINFO ,SEGTYPE=S0,$ FIELD=EMP_ID ,ALIAS=EID ,USAGE=A9 ,ACTUAL=A9,$ FIELD=LAST_NAME ,ALIAS=LN ,USAGE=A15 ,ACTUAL=A15,$ FIELD=FIRST_NAME ,ALIAS=FN ,USAGE=A10 ,ACTUAL=A10,$ FIELD=HIRE_DATE ,ALIAS=HDT ,USAGE=YMD ,ACTUAL=DATE,$ FIELD=DEPARTMENT ,ALIAS=DPT ,USAGE=A10 ,ACTUAL=A10, MISSING=ON,$ FIELD=CURR_SAL ,ALIAS=CSAL ,USAGE=P9.2 ,ACTUAL=P4,$ FIELD=CURR_JOBCODE ,ALIAS=CJC ,USAGE=A3 ,ACTUAL=A3,$
The EMPINFO Access File generated by the request follows:
SEGNAME=EMPINFO, TABLENAME=EMPINFO, WRITE=YES, KEYS=1, $
The following procedure sets the adapter PASSRECS setting to OFF, then uses the Direct SQL Passthru DELETE command to delete all rows from the Oracle table where the last name is SMITH. It saves the value returned to &ROWSAFFECTED in another variable named &DELETE1. Next, it deletes all rows where the first name is JOHN. It then displays the number of rows affected by each delete command. &DELETE1 contains the value saved from the first delete command and &ROWSAFFECTED contains the value from the second DELETE command:
SQL SQLORA SET PASSRECS OFF SQL SQLORA INSERT INTO EMPINFO (EID, LN, FN, DPT, HDT, CSAL, CJC) VALUES('111111111', 'ABEL', 'AARON', 'CORP', '990101', 25000, 'A05'); END -RUN -SET &IAFFECTED = &ROWSAFFECTED; SQL SQLORA DELETE FROM EMPINFO WHERE EID='111111111'; END -RUN -TYPE Rows affected on INSERT and DELETE: &IAFFECTED and &ROWSAFFECTED SQL SQLORA SET PASSRECS OFF SQL SQLORA DELETE FROM EMPINFO WHERE LN='SMITH'; END -RUN -SET &DELETE1 = &ROWSAFFECTED; SQL SQLORA DELETE FROM EMPINFO WHERE FN='JOHN'; END -RUN -TYPE Rows affected on both deletes: &DELETE1 and &ROWSAFFECTED
Since two employees had the last name SMITH and two employees had the first name JOHN, two rows were deleted by each DELETE command:
Rows affected on both deletes: 2 and 2
If you set the value of PASSRECS to ON, in addition to populating the &ROWSAFFECTED variable you generate the FOC1364 message for each Direct SQL Passthru INSERT, UPDATE, or DELETE command:
SQL SQLORA SET PASSRECS ON SQL SQLORA DELETE FROM EMPINFO WHERE LN='SMITH'; END -RUN -SET &DELETE1 = &ROWSAFFECTED; SQL SQLORA DELETE FROM EMPINFO WHERE FN='JOHN'; END -RUN -TYPE Rows affected on both deletes: &DELETE1 and &ROWSAFFECTED
The PASSRECS ON setting generates the FOC1364 messages:
(FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND : 2/DELETE (FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND : 2/DELETE Rows affected on both deletes: 2 and 2
The relational adapters have been enhanced to optimize aggregation in the presence of the FOCUS PCT function (which has no direct SQL counterparts) to SQL SUM(). Note that:
The 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.
The syntax is:
DPART (value, 'component', outfield)
where:
Is an input parameter (date) which can be a constant or a variable in one of the following formats:
Is one from the subset of components that can be specified as:
Formats are case-sensitive.
Is an integer variable or an I format enclosed in single quotes.
If any input errors are made, a value of zero will be returned.
For more information, see Optimization of the HPART, DPART, HDIFF, HDATE, and DATEDIF Functions.
DEFINE FILE CAR DATE/YYMD = IF COUNTRY IS 'ENGLAND' THEN '1941 DEC 7' ELSE DATE + 30; YEAR/I11 = DPART(DATE, 'YEAR', 'I11'); MONTH/I11 = DPART(DATE, 'MM', 'I11'); DAY/I11 = DPART(DATE, 'DAY', 'I11'); END
TABLE FILE CAR PRINT COUNTRY DATE YEAR MONTH DAY END
COUNTRY DATE YEAR MONTH DAY ------- ---- ---- ----- --- ENGLAND 1941/12/07 1941 12 7 JAPAN 1942/01/06 1942 1 6 ITALY 1942/02/05 1942 2 5 W GERMANY 1942/03/07 1942 3 7 FRANCE 1942/04/06 1942 4 6 >>
In this section: |
The relational adapters can translate certain calls to the HPART, DPART, HDIFF, HDATE, and DATEDIF functions to SQL date and time functions, allowing the optimization of some TABLE requests with DEFINE fields that call these functions.
The DATEDIF function returns the difference between two dates in units, and the HDIFF function returns the difference between two date-time values in units. Calls to these functions can be translated to SQL when the unit is the number of days between two values.
The syntax for the DATEDIF function with the day unit is
DATEDIF(from_date, to_date, 'D')
The syntax for the HDIFF function with the day unit is
HDIFF(to_date_time, from_date_time, 'DAY', outfield)
where:
Is the starting date or date-time value from which to calculate the difference.
Is the ending date or date-time value from which to calculate the difference.
Numeric
Is the name of the field that contains the result, or the format of the output value enclosed in single quotation marks.
For Microsoft SQL Server, Oracle, SYBASE ASE and IQ, calls to DATEDIF and HDIFF translate calls to the native DATE-TIME functions with Datepart Parameter DAY, Startdate, and Enddate.
For example, for DB2, calls to DATEDIF and HDIFF translate as calls to the DAYS function:
SELECT DAYS(d2) - DAYS(d1) FROM tablename
For Teradata, calls to DATEDIF and HDIFF translate as a simple difference between two date values for the DATE datatype and as calls to the CAST function for the TIMESTAMP datatype.
The following Master File represents a DB2 table named DATETIME with two DATE fields named DATE1 and DATE2 and two TIMESTAMP fields named TIMEST1 and TIMEST2:
FILENAME=DATETIME, SUFFIX=DB2 , $ SEGMENT=DATETIME, SEGTYPE=S0, $ FIELDNAME=DATE1, ALIAS=DATE1, USAGE=YYMD, ACTUAL=DATE, MISSING=ON, $ FIELDNAME=DATE2, ALIAS=DATE2, USAGE=YYMD, ACTUAL=DATE, MISSING=ON, $ FIELDNAME=TIMEST1, ALIAS=TIMEST1, USAGE=HYYMDm, ACTUAL=HYYMDm, MISSING=ON, $ FIELDNAME=TIMEST2, ALIAS=TIMEST2, USAGE=HYYMDm, ACTUAL=HYYMDm, MISSING=ON, $
The following request calculates the number of days difference between DATE1 and DATE2 using the DATEDIF function and the number of days difference between TIMEST1 and TIMEST2 using the HDIFF function:
SET TRACEUSER = ON SET TRACEOFF = ALL SET TRACEON = STMTRACE//CLIENT TABLE FILE DATETIME PRINT DATE1 DATE2 TIMEST1 TIMEST2 WHERE DATEDIF(DATE2, DATE1, 'D') LT 5 AND HDIFF(TIMEST1, TIMEST1, 'DAY', 'I11') EQ 1 END
The SQL generated for this request replaces the calls to DATEDIF and HDIFF with calls to the SQL DAYS function in the SQL WHERE predicate:
SELECT T1."DATE1", T1."DATE2", T1."TIMEST1", T1."TIMEST2" FROM OWNER1.DATETIME T1 WHERE ((DAYS(T1."TIMEST1") - DAYS(T1."TIMEST1")) = 1) AND ((DAYS(T1."DATE1") - DAYS(T1."DATE2")) < 5) FOR FETCH ONLY;
The following Master File represents an Oracle table named DATETIME with two DATE fields named DATE1 and DATE2 and two TIMESTAMP fields named TIMEST1 and TIMEST2:
FILENAME=DATETIME, SUFFIX=SQLORA , $ SEGMENT=DATETIME, SEGTYPE=S0, $ FIELDNAME=DATE1, ALIAS=DATE1, USAGE=YYMD, ACTUAL=DATE, MISSING=ON, $ FIELDNAME=DATE2, ALIAS=DATE2, USAGE=YYMD, ACTUAL=DATE, MISSING=ON, $ FIELDNAME=TIMEST1, ALIAS=TIMEST1, USAGE=HYYMDm, ACTUAL=HYYMDm, MISSING=ON, $ FIELDNAME=TIMEST2, ALIAS=TIMEST2, USAGE=HYYMDm, ACTUAL=HYYMDm, MISSING=ON, $
The following request calculates the number of days difference between DATE1 and DATE2 using the DATEDIF function and the number of days difference between TIMEST1 and TIMEST2 using the HDIFF function:
SET TRACEUSER = ON SET TRACEOFF = ALL SET TRACEON = STMTRACE//CLIENT TABLE FILE DATETIME PRINT DATE1 DATE2 TIMEST1 TIMEST2 WHERE DATEDIF(DATE2, DATE1, 'D') LT 5 AND HDIFF(TIMEST1, TIMEST1, 'DAY', 'I11') EQ 1 END
The SQL generated for this request replaces the calls to DATEDIF and HDIFF with calls to the SQL EXTRACT and TRUNC functions in the SQL WHERE predicate:
SELECT T1."DATE1", T1."DATE2", T1."TIMEST1", T1."TIMEST2" FROM OWNER1.DATETIME T1 WHERE (EXTRACT(DAY FROM (TRUNC(T1."TIMEST1") - TRUNC(T1."TIMEST1")) DAY(9) TO SECOND) = 1) AND (EXTRACT(DAY FROM (TRUNC(T1."DATE1") - TRUNC(T1."DATE2")) DAY(9) TO SECOND) < 5);
The HPART function extracts a specified component from a date-time value and returns it in numeric format. The DPART function extracts a specified component from a date value and returns it in numeric format. Calls to HPART and DPART 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.
HPART(dtvalue, 'component', outfield)
where:
Date-time
Is a date-time value, the name of a date-time field that contains the value, or an expression that returns the value.
Alphanumeric
Is the name of the component to be retrieved enclosed in single quotation marks.
Integer
Is the field that contains the result, or the integer format of the output value enclosed in single quotation marks.
DPART (datevalue, 'component', outfield)
where:
Date
Is a full component date.
Alphanumeric
Is the name of the component to be retrieved enclosed in single quotation marks. Valid values are:
For year: YEAR, YY
For month: MONTH, MM
For day: DAY, For day of month: DAY-OF-MONTH.
For quarter: QUARTER, QQ
Integer
Is the field that contains the result, or the integer format of the output value enclosed in single quotation marks.
For Microsoft SQL Server, SYBASE ASE, and IQ, calls to HPART and DPART translate as calls to the DATEPART function.
For DB2 and MySQL, calls to HPART translate as calls to the YEAR, MONTH, or DAY function.
For Oracle and TERADATA (Both DATE and TIMESTAMP datatypes), calls to HPART and DPART translate as calls to the EXTRACT function.
The following request uses the HPART function to retrieve the year component from the DB2 TIMEST1 column:
SET TRACEUSER = ON SET TRACEOFF = ALL SET TRACEON = STMTRACE//CLIENT TABLE FILE DATETIME PRINT TIMEST1 WHERE HPART(TIMEST1, 'YEAR', 'I4') EQ 2008 END
The generated SQL replaces the call to the HPART function with a call to the SQL YEAR function in the SQL WHERE predicate:
SELECT T1."TIMEST1" FROM OWNER1.DATETIME T1 WHERE (YEAR(T1."TIMEST1") = 2008) FOR FETCH ONLY;
The following request uses the HPART function to retrieve the year component from the Oracle TIMEST1 column:
SET TRACEUSER = ON SET TRACEOFF = ALL SET TRACEON = STMTRACE//CLIENT TABLE FILE DATETIME PRINT TIMEST1 WHERE HPART(TIMEST1, 'YEAR', 'I4') EQ 2008 END
The generated SQL replaces the call to the HPART function with a call to the SQL EXTRACT function in the SQL WHERE predicate:
SELECT T1."TIMEST1" FROM OWNER1.DATETIME T1 WHERE (EXTRACT(YEAR FROM T1."TIMEST1") = 2008);
The HDATE function converts the date portion of a date-time value to the date format YYMD.
HDATE(dtvalue, {'YYMD'|outfield})
where:
Date-time
Is the date-time value to be converted, the name of a date-time field that contains the value, or an expression that returns the value.
Date
Is the output format. The value must be YYMD.
YYMD
Is the field that contains the result.
For Microsoft SQL Server (2008 and up), SYBASE ASE and IQ, DB2, TERADATA, and MySQL, calls to DATE translate as calls to the CAST function.
For Oracle, calls to HDATE translate as calls to the TRUNC function.
The following request uses the HDATE function to extract the date portion of the TIMEST1 column:
SET TRACEUSER = ON SET TRACEOFF = ALL SET TRACEON = STMTRACE//CLIENT TABLE FILE DATETIME PRINT DATE1 TIMEST1 WHERE HDATE(TIMEST1, 'YYMD') GT DATE1 END
In the generated SQL, the call to HDATE is replaced by a call to the SQL CAST function in the SQL WHERE predicate:
SELECT T1."DATE1", T1."TIMEST1" FROM OWNER1.DATETIME T1 WHERE (CAST(T1."TIMEST1" AS DATE) > T1."DATE1") FOR FETCH ONLY;
The following request uses the HDATE function to extract the date portion of the TIMEST1 column:
SET TRACEUSER = ON SET TRACEOFF = ALL SET TRACEON = STMTRACE//CLIENT TABLE FILE DATETIME PRINT DATE1 TIMEST1 WHERE HDATE(TIMEST1, 'YYMD') GT DATE1 END
In the generated SQL, the call to HDATE is replaced by a call to the SQL TRUNC function in the SQL WHERE predicate:
SELECT T1."DATE1", T1."TIMEST1" FROM OWNER1.DATETIME T1 WHERE (TRUNC(T1."TIMEST1") > T1."DATE1");
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. This reduces the volume of RDBMS-to-FOCUS communication, which improves response time.
Diagnostic messages and tracing for aggregation functions, DB-lookup of SQL Tables, and Cluster join structures that explain SQL statement generation have been improved.
The Adapter for DB2 supports primary and secondary authorization security checking for DSN3SATH. Three modifications are required, as shown in the following example.
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 . . .
The adapter supports the increased length of SQL statements from 32K to 2MB. This feature is supported on z/OS for DB2 Version 8 and higher and AS400 Version 5.4 and higher, with the maximum length 2MB. On AS400 Version 5.3, the maximum length is set to 64K.
Change Data Capture allows loading of data targets using only the rows that have changed since the last load. This approach is useful when dealing with large databases that would take too much time and too many resources to reload completely. This capability is supported for DataMigrator and for DB2 data sources on the z/OS platform.
The SUBSTR character function can be passed to SQL for optimized processing that takes advantage of RDBMS substring functions, thus improving performance and response time.
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 multi row 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.
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.
The Adapter for Oracle now supports Change Data Capture (CDC). It provides the ability to capture incremental changes in the database, making them available to FOCUS and utilizing them for different purposes, including data replication and forensics.
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.
Information Builders |