DB_INFILE Function: Testing Values Against a File or an SQL Subquery

In this section:

How to:

Reference:

The DB_INFILE function compares one or more field values in a source file to values in a target file. It returns the value 1 (TRUE) if the set of source fields matches a set of values from the target file. Otherwise, the function returns zero (FALSE). DB_INFILE can be used anywhere a function can be used in a WebFOCUS request, such as in a DEFINE or a WHERE phrase.

The target file can be any data source that WebFOCUS can read. Depending on the data sources accessed and the components in the request, either WebFOCUS or an RDBMS will process the comparison of values.

If WebFOCUS processes the comparison, it dynamically reads the target data source and creates a sequential file containing the target data values along with a synonym describing the data file. It then builds IF or WHERE structures in memory with all combinations of source and target values. If the target data contains characters that WebFOCUS considers wildcard characters, it will treat them as wildcard characters unless the command SET EQTEST = EXACT is in effect.

The following situations exist when a relational data source is the source file:


Top of page

x
Syntax: How to Compare Source and Target Field Values With DB_INFILE
DB_INFILE(target_file, s1, t1, ... sn, tn)

where:

target_file

Is the synonym for the target file.

s1, ..., sn

Are fields from the source file.

t1, ..., tn

Are fields from the target file.

The function returns the value 1 if a set of target values matches the set of source values. Otherwise, the function returns zero (0).

The mapping is done using pairs, where the s1 field is matched to t1 field, and so on.


Top of page

x
Reference: Usage Notes for DB_INFILE


Example: Comparing Source and Target Values Using an SQL Subquery File

This example uses the WF_RETAIL DB2 data source.

WF_RETAIL is a sample data source you can create by right-clicking an application on the Reporting Server Web Console and selecting New and then Samples from the context menu.

The SQL file named retail_subquery.sql contains the following subquery that retrieves specified state codes in the Central and NorthEast regions:

SELECT  MAX(T11.REGION), MAX(T11.STATECODE)  FROM wrd_dim_geography T11 WHERE (T11.STATECODE IN('AR', 'IA', 'KS', 'KY', 'WY', 'CT', 'MA', 'NJ', 'NY', 'RI')) AND (T11.REGION IN('Central', 'NorthEast'))  GROUP BY T11.REGION, T11.STATECODE

The retail_subquery.mas Master File follows:

FILENAME=RETAIL_SUBQUERY, SUFFIX=DB2     , $
  SEGMENT=RETAIL_SUBQUERY, SEGTYPE=S0, $
    FIELDNAME=REGION, ALIAS=E01, USAGE=A15V, ACTUAL=A15V,
      MISSING=ON, $
    FIELDNAME=STATECODE, ALIAS=E02, USAGE=A2, ACTUAL=A2,
      MISSING=ON, $

The retail_subquery.acx Access File follows:

SEGNAME=RETAIL_SUBQUERY,CONNECTION=CON1,DATASET=RETAIL_SUBQUERY.SQL, $

Note: You can create an SQL subquery file along with a corresponding synonym using the HOLD FORMAT SQL_SCRIPT command.

The following request uses the DB_INFILE function to compare region names and state codes against the names retrieved by the subquery:

TABLE FILE WF_RETAIL
SUM REVENUE
BY REGION
BY STATECODE
WHERE DB_INFILE(RETAIL_SUBQUERY, REGION, REGION, STATECODE, STATECODE)
ON TABLE SET PAGE NOPAGE
END

The trace shows that the subquery was inserted into the WHERE predicate in the generated SQL:

 SELECT   
  T11."REGION",  
  T11."STATECODE",  
   SUM(T1."Revenue")  
   FROM   
  wrd_fact_sales T1,  
  wrd_dim_customer T5,  
  wrd_dim_geography T11  
   WHERE   
  (T5."ID_CUSTOMER" = T1."ID_CUSTOMER") AND   
  (T11."ID_GEOGRAPHY" = T5."ID_GEOGRAPHY") AND  
  ((T11."REGION", T11."STATECODE") IN (SELECT  MAX(T11.REGION),  
  MAX(T11.STATECODE)  FROM wrd_dim_geography T11 WHERE  
  (T11.STATECODE IN('AR', 'IA', 'KS', 'KY', 'WY', 'CT', 'MA',  
  'NJ', 'NY', 'RI')) AND (T11.REGION IN('Central', 'NorthEast'))   
  GROUP BY T11.REGION, T11.STATECODE))  
   GROUP BY   
  T11."REGION",  
  T11."STATECODE  "
   ORDER BY   
  T11."REGION",  
  T11."STATECODE  "
   FOR FETCH ONLY; 
END  

The output is:



Example: Comparing Source and Target Values Using a Sequential File

The empvalues.ftm sequential file contains the last and first names of employees in the MIS department:

SMITH           MARY        JONES           DIANE       MCCOY           JOHN        BLACKWOOD       ROSEMARIE   GREENSPAN       MARY        CROSS           BARBARA     

The empvalues.mas Master File describes the data in the empvalues.ftm file:

FILENAME=EMPVALUES, SUFFIX=FIX     , IOTYPE=BINARY, $
  SEGMENT=EMPVALUE, SEGTYPE=S0, $
    FIELDNAME=LN, ALIAS=E01, USAGE=A15, ACTUAL=A16, $
    FIELDNAME=FN, ALIAS=E02, USAGE=A10, ACTUAL=A12, $

Note: You can create a sequential file along with a corresponding synonym using the HOLD FORMAT SQL_SCRIPT command.

The following request against the FOCUS EMPLOYEE data source uses the DB_INFILE function to compare employee names against the names stored in the empvalues.ftm file:

FILEDEF EMPVALUES DISK baseapp/empvalues.ftm
TABLE FILE EMPLOYEE
SUM CURR_SAL
BY LAST_NAME BY FIRST_NAME
WHERE DB_INFILE(EMPVALUES, LAST_NAME, LN, FIRST_NAME, FN)
ON TABLE SET PAGE NOPAGE
END

The output is:


Top of page

x
Controlling DB_INFILE Optimization Against a Relational DBMS

How to:

When used against a single RDBMS, DB_INFILE performs an analysis to determine whether the resulting expression can be optimized using a subquery. It then creates that subquery and passes it to the relational adapter for inclusion in the optimized SQL to be passed to the RDBMS for processing. Occasionally, because of specific syntax not supported by the RDBMS involved, the RDBMS is not able to process the subquery that was created by DB_INFILE. If this happens, you will see messages to that effect. In this case, you can prevent DB_INFILE from optimizing the expression as a subquery by setting the DB_INFILE parameter to EXPAND_ALWAYS. You should implement this settings only for those specific requests that demonstrate problems with the subquery generated by DB_INFILE.

You can also prevent DB_INFILE from expanding the query into IF and WHERE phrases by setting the DB_INFILE parameter to EXPAND_NEVER. One reason to use this value is if the target file is so large that reading it could freeze processing. In this case, DB_INFILE will attempt to create a subquery. If this is not possible, the following message will be issued and processing will halt:

(FOC32585) ERROR EXPANDING DB_INFILE


x
Syntax: How to Control DB_INFILE Optimization

To control whether to prevent optimization of the DB_INFILE expression, issue the following command:

SET DB_INFILE = {DEFAULT|EXPAND_ALWAYS|EXPAND_NEVER}

In a TABLE request, issue the following command:

ON TABLE SET DB_INFILE  {DEFAULT|EXPAND_ALWAYS|EXPAND_NEVER}

where:

DEFAULT

Enables DB_INFILE to create a subquery if its analysis determines that it is possible. This is the default value.

EXPAND_ALWAYS

Prevents DB_INFILE from creating a subquery and, instead, expands the expression into IF and WHERE clauses in memory.

EXPAND_NEVER

Prevents DB_INFILE from expanding the expression into IF and WHERE clauses in memory and, instead, attempts to create a subquery. If this is not possible, a FOC32585 message is generated and processing halts.


WebFOCUS