Microsoft Azure SQL Data Warehouse Optimization Settings

SQL SQLADW SET OPT {AGGR|NOAGGR}

where:

AGGR

Directs the adapter to off-load aggregated DEFINE fields to the DBMS. This is the default setting.

NOAGGR

Directs the adapter to generate SQL without passing aggregation to the DBMS. Aggregation is, instead, performed internally by the server, while JOIN and SORT operations are handled by the RDBMS. This setting can also be used to provide backwards compatibility for applications that were written based on the functionality of the previous release, when less SQL was off-loaded to the RDBMS. For example, when a calculation on aggregated fields may have contained NULL data that was not processed by the RDBMS NVL( ) function.

SQL SQLADW SET OPTIFTHENELSE ON
DEFINE FILE EMPINFO
DEF3 = IF FIRST_NAME EQ 'RITA' THEN 1 ELSE 0;
END
 
TABLE FILE EMPINFO
PRINT FIRST_NAME
IF DEF3 EQ 2
END 

SELECT T1."FN" FROM USER1."EMPINFO" T1 WHERE (1 = 0) FOR FETCH ONLY;

Since the FOCUS reporting language is more extensive than native SQL, the data adapter cannot pass certain DEFINE expressions to the RDBMS for processing. The data adapter does not offload DEFINE-based aggregation and record selection if the DEFINE includes:

• User-written subroutines.
• Self-referential expressions, such as:

  X=X+1;

• EDIT functions for numeric-to-alpha or alpha-to-numeric field conversions.
• DECODE functions for field value conversions.
• Relational operators INCLUDES and EXCLUDES.
• FOCUS subroutines ABS, INT, MAX, MIN, LOG, and SQRT.

  Note: Do not confuse the FOCUS user-written subroutines MAX and MIN with the MAX. and MIN. prefix operators. DEFINE fields cannot include prefix operators.

• Expressions involving fields with ACTUAL=DATE, except for the subtraction of one DATE field from another and all logical expressions on DATE fields.
• Date-time manipulation handled by the FOCUS date-time functions is not converted to SQL.
• Financial Modeling Language (FML) cell calculations. FML is also referred to as Extended Matrix Reporting (EMR).

  Note: FML report requests are extended TABLE requests. The Financial Modeling Language provides special functions for detailed reporting. Consult your FOCUS documentation for more information.

In addition, IF-THEN-ELSE optimization does not support the following features:

• Any type of DECODE expression.
• STATIC SQL.
• IF/WHERE DDNAME.
• Partial date selection.

The block size for a SELECT request applies to TABLE FILE requests, MODIFY requests, MATCH requests, and DIRECT SQL SELECT statements.

ENGINE SQLADW SET FETCHSIZE n

where:

SQLADW

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

n

Is the number of rows to be retrieved at once using array retrieval techniques. Accepted values are 1 to 5000. The default varies by adapter. 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.

x

In combination with LOADONLY, the block size for an INSERT applies to MODIFY INCLUDE requests. INSERTSIZE is also supported for parameterized DIRECT SQL INSERT statements.

ENGINE SQLADW SET INSERTSIZE n

where:

SQLADW

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

n

Is the number of rows to be inserted using array insert techniques. Accepted values are 1 to 5000. 1 is the default value. If the result set contains a column that has to be processed as a BLOB, the INSERTSIZE value used for that result set is 1.

x

ENGINE SQLADW SET BULKLOAD [ON|OFF]

where:

SQLADW

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

ON

Uses the Bulk Insert API. ON is the default.

OFF

Suppresses the use of the Bulk Insert API.

You can use DataMigrator with the Bulk Insert API for Microsoft Azure SQL Data Warehouse.

For the Adapter for Microsoft Azure SQL Data Warehouse, the Bulk API is used automatically in LOADONLY mode. Measurements show that intermediate flushes do not affect performance; therefore, the behavior does not depend on the INSERTSIZE.

Errors that occur during the load (such as duplication) can cause the batch of rows to be rejected as a whole.

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:

LEFT_OUTER

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.

hostfile

Is the host Master File.

AT

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.

hfld1

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 its data source that is referenced.

tag1

Is the optional tag name that is used as a unique qualifier for fields and aliases in the host data source.

WITH hfld2

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.

MULTIPLE

Specifies a one-to-many relationship between from_file and to_file. Note that ALL is a synonym for MULTIPLE.

UNIQUE

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 WebFOCUS 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 WebFOCUS processes the join, a different report results because WebFOCUS, respecting the unique join concept, returns only one cross-referenced row for each host row.

crfile

Is the cross-referenced Master File.

crfld

Is the join field name in the cross-referenced Master File. It can be any field in the segment.

tag2

Is the optional tag name that is used as a unique qualifier for fields and aliases in the cross-referenced data source.

joinname

Is the name associated with the joined structure.

expression1, expression2

Are any expressions that are acceptable in a DEFINE FILE command. All fields used in the expressions must lie on a single path.

• In order for a WHERE-based left outer join to be optimized, the expressions must be optimizable for the RDBMS involved and at least one of the following conditions must be true:
  • The JOIN WHERE command contains at least one field1 EQ field2 predicate in which field1 is in table1 and field2 is in table2.

    or

  • The right table has a key or a unique index that does not contain NULL data.

    or

  • The right table contains at least one "NOT NULL" column that does not have a long data type (such as TEXT or IMAGE).
• The adapter SQLJOIN OUTER setting must be ON (the default).

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
JOIN LEFT_OUTER FILE employee AT EMPLOYEEID 
TO ALL FILE employeepayhistory AT EMPLOYEEID 
     WHERE RATECHANGEDATE GT HIREDATE;
END     
TABLE FILE employee
PRINT RATE
BY EMPLOYEEID
END

SELECT T1."EmployeeID", T1."HireDate", T2."EmployeeID", 
T2."RateChangeDate", T2."Rate" FROM 
AdventureWorks.HumanResources.Employee T1 LEFT OUTER JOIN 
AdventureWorks.HumanResources.EmployeePayHistory T2 ON 
(T2."RateChangeDate" > T1."HireDate") ) ORDER BY T1."EmployeeID";