IFFDV (HLI function)

The conventions used on this page are described in Function call notation conventions.

Summary

Description

The IFFDV call (FIND ALL VALUES) finds all values of a specified Model 204 field and creates a value set. The field must be defined having either KEY and FRV or ORDERED attributes.

Thread type

multiple cursor IFSTRT, single cursor IFSTRT

IFCALL function number

74

Syntax

IFFDV(RETCODE,FIELD_NAME,FDV_NAME,%VARBUF,%VARSPEC)

Compile-only form

IFCALL function number: 75

IFFDVC(RETCODE,FIELD_NAME,FDV_NAME)

Execute-only form

IFCALL function number: 76

IFFDVE(RETCODE,FDV_NAME,%VARBUF,%VARSPEC)

Specify the parameters in the syntax order shown above.

Parameter	Description
RETCODE	[O,i,r] The Model 204 return code is the required first parameter. The code is a binary integer value.
FIELD_NAME	[I,c,r] The field name is a required input parameter which specifies the name of the Model 204 field whose values are to be found. A field name variable or a %variable is valid. Specify the field as a character string using the following format: [IN {FILE filename; | GROUP groupname;}] FDV fieldname; [FROM value1] [TO valuen]; [[NOT] LIKE pattern];END; where: the IN clause is available only for a multiple cursor thread and its use is optional for specifying a file or group context other than the default. The file context can change on a multiple cursor thread, and if the file specification is omitted, IFFDV processes values from the file or group that is the default on the thread at the time that the IFFDV call is compiled. filename|groupname specifies the name of a particular file or group context for the value set. FDV is a required keyword that must be specified if the IN clause is used. fieldname is required and specifies the name of a particular Model 204 field or field name variable. FROM and TO clauses are optional and specify a minimum (greater than or equal to) value (FROM), a maximum (less than or equal to) value (TO), or a range of values (FROM and TO) for selection criteria. LIKE|NOT LIKE clause is optional and specifies a string pattern for selection criteria. For information about value specifications, see Value specification syntax.
FDV_NAME	[I,s,r/o] The name of the IFFDV compilation is an input parameter that is required for use with a multiple cursor IFSTRT thread, and is only required for a single cursor IFSTRT thread if using the Compiled IFAM facility (IFFDVC and IFFDVE). Model 204 saves the compilation using this name. Specify the name as unique, and as a short character string (maximum 32 characters). On a single cursor IFSTRT thread, any characters except the following are valid in the name: blank, comma, parenthesis, equal sign, or semicolon. On a multiple cursor IFSTRT thread, the first character in the name must be alphanumeric, and the name must begin with a letter (A-Z or a-z) which may be followed by a letter, a digit (0-9), a period (.), or underscore (_). A null value is equivalent to omitting the name parameter, and is not valid for a multiple cursor thread.
%VARBUF	[I,c,o] The variable buffer is an optional input parameter that addresses a data area which accommodates up to 255 bytes of data per value. The buffer contains values which are defined by the %VARSPEC parameter, below, to be assigned to %variables. Specify a character string. For information about %variables, see Using variables and values in computation.
%VARSPEC	[I,c,o] The variable specification describes the format of the data that is contained in the %VARBUF parameter and lists the %variables to be assigned. %VARSPEC specifies the contents of the variable buffer, described above. Specify a character string which follows a LIST, DATA, or EDIT syntax. %VARSPEC is a required input parameter if %VARBUF is specified.

SQL performance

The SQL driver, if possible, uses the IFFDV call to process the DISTINCT qualifier in SQL, which avoids triggering Model 204 Long Requests and improves performance. This behavior applies to any SQL interface, such as Connect★ or a C program. Model 204 stipulates that an IFFDV call contain only one field with either an FRV attribute or an ORDERED attribute.

IFFDV applies to all SQL aggregate functions, AVG, COUNT, MAX, MIN and SUM. The MIN (DISTINCT column-name) requires only one fetch of the value set in question if the corresponding Model 204 field has an ORDERED attribute.

Limiting considerations

• Because of the discrepancy between Model 204 and SQL, a Model 204 string field must have either the ORDERED or the FRV attribute; a binary or float field must have the ORDERED attribute.
• The WHERE clause in the SELECT statement is limited to the scope of the arguments in an IFFDV call. For example:

  SELECT DISTINCT LAST_NAME FROM filename WHERE LAST_NAME BETWEEN 'A' AND 'C' AND LAST_NAME NOT LIKE 'ABC%'

• If SELECT statements reference only a single column in the query, the IFFDV performance enhancement is used. For example, the following query does not invoke an IFFDV call because the query references two columns, LAST_NAME and FIRST_NAME.

  SELECT DISTINCT LAST_NAME FROM filename WHERE LAST_NAME BETWEEN 'A' AND 'C' AND FIRST_NAME (NOT) LIKE 'ABC%'

• If you have more than one FROM or more than one TO clause, then IFFDV optimization is not performed, as the following query illustrates:

  SELECT DISTINCT LAST_NAME FROM filename WHERE LAST_NAME BETWEEN 'A' AND 'C' AND LAST_NAME <> 'ABC%'

• A query that contains an OR keyword is not optimized by IFFDV, as the following example illustrates:

  SELECT DISTINCT LAST_NAME FROM filename WHERE LAST_NAME BETWEEN 'A' AND 'C' OR LAST_NAME LIKE 'NEL%'

• A query that contains a NOT BETWEEN clause is not optimized by IFFDV, as the following example illustrates:

  SELECT DISTINCT LAST_NAME FROM filename WHERE LAST_NAME NOT BETWEEN 'A' AND 'M'

Usage notes

Use the IFFDV call to retrieve the stored values of a particular field. You can specify only one field per call. You may specify retrieval conditions, a range or a pattern, to limit values in the found set. Note that in group context, the found value set is automatically sorted in ascending order, following the standard EBCDIC collating sequence.

The IFFDV call is permitted on all types of IFSTRT threads.

The IFFDV call is the equivalent of the FIND ALL VALUES statement in SOUL in the host language environment. See Value loops for information about the FIND ALL VALUES statement.

Processing records from a value set

There are differences between single cursor and multiple cursor IFSTRT threads in processing records from a value set.

When a set is found on a single cursor IFSTRT thread, and while it is the current set, you can use IFGETV to retrieve individual values. On a multiple cursor thread, use IFOCUR to open a cursor to a value set any time after it is established using Compiled IFAM and use the IFFTCH call to retrieve individual values.

Coding examples (COBOL)

The examples below find all the values of the field COLOR. In this example, IFFDV is called without the compilation name or a %variable:

WORKING-STORAGE SECTION. 01 CALL-ARGS. 05 RETCODE PIC 9(5) COMP SYNC. 05 FIELD-NAME PIC X(6) VALUE "COLOR;". . . . PROCEDURE DIVISION. . . . CALL "IFFDV" USING RETCODE, FIELD-NAME.

In the example below, IFFDV is called using the compilation name and a %variable:

WORKING-STORAGE SECTION. 01 CALL-ARGS. 05 RETCODE PIC 9(5) COMP SYNC. 05 FIELD-NAME PIC X(8) VALUE "%%FNVAR;". 05 FD-NAME PIC X(23) VALUE "IFFDV COMPILATION NAME;". 05 PCV PIC X(6) VALUE "COLOR;". 05 PCV-SPEC PIC X(20) VALUE "EDIT(%FNVAR)(A(5));". . . . PROCEDURE DIVISION. . . . CALL "IFFDV" USING RETCODE, FIELD-NAME, FD-NAME, PCV, PCV-SPEC.