%SqlHint meta-SQL element

Syntax

%SqlHint(SQL_cmd, index, hint_text, DB_platform [,
{ENABLE | DISABLE}])

Description

Use the %SqlHint function to insert a database platform-specific SQL hint into the specified SQL statement. The hint is inserted immediately after the SQL command specified by the SQL_cmd parameter.

This meta-SQL function is ignored in any of the following circumstances:

  • The current database connection does not match the DB_platform parameter.

  • The DB_platform parameter is not specified as ORACLE. (This is a limitation of the current release.)

  • The nth occurrence of the SQL command specified by SQL_cmd and index does not exist in the current SQL statement.

Parameters

Parameter Description

SQL_cmd

Specifies the SQL command that will use the hint as one of the following literal constants:

  • SELECT

  • INSERT

  • UPDATE

  • DELETE

  • MERGE

index

Specifies which occurrence of the SQL command will use the hint as an Integer value from 1 to 99.

hint_text

Specifies the SQL hint as a String value enclosed in single quotes. The hint can include other meta-SQL, such as %Table.

DB_platform

Specifies the database platform for which the hint is valid as one of the following literal constants:

  • ORACLE

  • DB2

  • DB2UNIX

  • SQLSERVER

Note: Currently, ORACLE is the only supported platform. This meta-SQL function is ignored for all other platforms.

ENABLE | DISABLE

Specifies whether to enable or disable the hint as a literal constant.

Note: ENABLE is the default value for this optional parameter.

Example 1

Before: The following example includes an Oracle-specific SQL hint to be inserted after the first SELECT of the SQL statement: 

%SqlHint(SELECT, 1, '/*+ FIRST_ROWS(10) */', ORACLE) 
 SELECT EMPLID 
  FROM PS_JOB

After: For an Oracle connection, this meta-SQL would expand to: 

 SELECT '/*+ FIRST_ROWS(10) */' EMPLID 
  FROM PS_JOB

After: On all other connections, this meta-SQL would expand to: 

 SELECT EMPLID 
  FROM PS_JOB

Example 2

Before: In the following example, %SqlHint functions will be expanded and applied after all other meta-SQL expansion has occurred. In this example, the APPEND hint will be applied to the first INSERT found in this SQL statement. The LEADING hint will be applied to the first SELECT found in this SQL statement. 

%SqlHint(INSERT, 1, '/*+ APPEND*/', ORACLE, ENABLE ),%SqlHint(SELECT, 1, '/*+ LEADING(H) INDEX(L, PSFJRNL_LN) */', ORACLE, ENABLE) %InsertSelect(JRNL_LIU_TAO, JRNL_LN L, BUSINESS_UNIT_IU=H.BUSINESS_UNIT_IU, LEDGER_GROUP=H.LEDGER_GROUP, IU_SYS_TRAN_CD=H.IU_SYS_TRAN_CD, IU_TRAN_CD=H.IU_TRAN_CD, PROCESS_INSTANCE=%Bind(PROCESS_INSTANCE)) 
  FROM %Table(JRNL_HIU_TAO) H, PS_%Bind(GL_JEDIT_WK_AET.RECNAME_JRNL_LN,NOQUOTES) L 
 WHERE H.PROCESS_INSTANCE=%Bind(PROCESS_INSTANCE) 
   AND H.BUSINESS_UNIT=L.BUSINESS_UNIT 
   AND H.JOURNAL_ID=L.JOURNAL_ID 
   AND H.JOURNAL_DATE=L.JOURNAL_DATE 
   AND H.UNPOST_SEQ=L.UNPOST_SEQ;

After: The SQL statement after all meta-SQL expansion and hint insertion: 

 INSERT /*+ APPEND */ INTO PS_JRNL_LIU_TAO5 (BUSINESS_UNIT , JOURNAL_ID , JOURNAL_DATE 

/* For the purposes of clarity, many columns in this column list have been omitted from this example. */

 , DEPTID , SCENARIO , BUSINESS_UNIT_IU) 
 SELECT /*+ LEADING(H) INDEX(L, PSFJRNL_LN) */ L.BUSINESS_UNIT 
 , L.JOURNAL_ID 
 , L.JOURNAL_DATE 

/* For the purposes of clarity, many columns in this column list have been omitted from this example. */ 

 ,L.DEPTID 
 ,L.SCENARIO 
 , H.BUSINESS_UNIT_IU 
  FROM PS_JRNL_HIU_TAO5 H 
  , PS_%Bind(GL_JEDIT_WK_AET.RECNAME_JRNL_LN,NOQUOTES) L 
 WHERE H.PROCESS_INSTANCE=%Bind(PROCESS_INSTANCE) 
   AND H.BUSINESS_UNIT=L.BUSINESS_UNIT 
   AND H.JOURNAL_ID=L.JOURNAL_ID 
   AND H.JOURNAL_DATE=L.JOURNAL_DATE 
   AND H.UNPOST_SEQ=L.UNPOST_SEQ ;