A filter is a user-written function that indicates whether or not a particular record should be loaded. It can also contain other desired logic such as accumulating statistics or other information about the records being loaded. A filter cannot alter the contents of the records read from the data file. You can write filters in COBOL85 or ALGOL.

In this subsection, an example is used to illustrate how to write and use filters. For the filter example, a database that includes the following table is assumed:

create table orders
(
 order_id      numeric (6) not null,
 customer_id   numeric (6),
 order_date    datetime,
 ship_date     datetime,
 order_status  numeric (2),
 primary key (order_id)
)

One of the filters in the filter library is designed for use when populating the preceding table. The filter for the orders table causes only orders with an order date in 2010 or later to be included in the load. Note that this filter could also be accomplished with an INCLUDERULE parameter but is provided here for purposes of illustration.

A filter initialize function is incorporated in the library so the metadata is available to the filters.

The following assumptions are made whether the library is written in COBOL85 or ALGOL:

This information is expressed in the configuration file as

FILTERLIBRARY = OBJECT/SYMBOL/ORDERSDB/FILTERLIBRARY
FILTERINITIALIZE = ORDERSDBINITLIB
FILTERFUNCTION = ORDERSFILTER

There are a number of ways that the date comparison in the orders filter can be done. The examples assume that a library of procedures has been constructed to provide conversions from string input of numbers, dates, and times to numeric values that are readily compared. It is assumed that this convert library has been compiled as OBJECT/SYMBOL/CONVERT.

This particular example does not use any of the information from the initialize function and that function can be omitted from the configuration file and the filter library. However, it is included in the example to illustrate the correct logic for such a function.

For a sample implementation of the conversion library procedures, refer to the MCPSQL product page on the Unisys Product Support website at

http://www.support.unisys.com/common/ShowWebPage.aspx?id=6391=AS=MCPSQL2

Sample Cobol Filter

IDENTIFICATION DIVISION.

PROGRAM-ID. FILTERLIBRARY IS LIBRARY PROGRAM.

ENVIRONMENT DIVISION.

DATA DIVISION.

WORKING-STORAGE SECTION.

* ------------------------------------------------------------
* VARIABLES TO CAPTURE GLOBAL FILTER INFORMATION PASSED TO
* THE LIBRARY VIA THE ORDERSDBINITLIB FUNCTION
* ------------------------------------------------------------
77 LIB-NUM-PARAMS                   REAL       IS GLOBAL.
01 LIB-NULL-STRING                  PIC X(16)  IS GLOBAL.
01 LIB-FILE-STRING                  PIC X(16)  IS GLOBAL.
01 LIB-PARAM-NAME-ARRAY IS GLOBAL.
   05 LIB-PARAM-NAME                PIC X(30)  OCCURS 5 TIMES.
01 LIB-PARAM-METADATA-ARRAY                    IS GLOBAL.
   05 LIB-PARAM-METADATA	 OCCURS 5 TIMES.
      10 LIB-PARAM-SQLTYPE          REAL.
      10 LIB-PARAM-PRECISION        REAL.
      10 LIB-PARAM-SCALE            REAL.
      10 LIB-PARAM-EMPTYISNULL      REAL.
      10 LIB-PARAM-ZEROISNULL       REAL.
      10 LIB-PARAM-SOURCEFIELD      REAL.

* ------------------------------------------------------------
* THE FOLLOWING ARE VARIOUS GLOBAL VALUES USED IN THE LIBRARY
* ------------------------------------------------------------

77 FIRST-TIME                       PIC 9      IS GLOBAL.
77 LIB-IS-LINKED                    PIC 9      IS GLOBAL.
77 COMPARE-DATETIME                 REAL       IS GLOBAL.
PROGRAM-LIBRARY SECTION.
LB FILTERLIBRARY EXPORT
ATTRIBUTE SHARING IS PRIVATE.
ENTRY PROCEDURE ORDERSDBINITLIB.
ENTRY PROCEDURE ORDERSFILTER.

PROCEDURE DIVISION.
MAIN.
   MOVE 1 TO FIRST-TIME.
   MOVE 0 TO LIB-IS-LINKED.
   CALL SYSTEM FREEZE TEMPORARY.
   STOP RUN.

*
* ======================== ORDERSDBINITLIB =======================
*

IDENTIFICATION DIVISION.
PROGRAM-ID. ORDERSDBINITLIB.

ENVIRONMENT DIVISION.

DATA DIVISION.
WORKING-STORAGE SECTION.
77 I REAL.

LINKAGE SECTION.
77 NUM-PARAMS REAL.
01 PARAM-NAMES                      PIC X(150).
01 PARAM-METADATA-ARRAY             REAL.
   05 PARAM-METADATA OCCURS 5 TIMES.
01 NULL-STRING                      PIC X(16).
01 FILE-STRING                      PIC X(16).

PROCEDURE DIVISION USING NUM-PARAMS,
                         PARAM-NAMES, PARAM-METADATA-ARRAY,
                         NULL-STRING, FILE-STRING.
PARA.
* ------------------------------------------------------------
* CAPTURE THE NUMBER OF PARAMETERS
* ------------------------------------------------------------
  MOVE NUM-PARAMS TO LIB-NUM-PARAMS.

* ------------------------------------------------------------
* CAPTURE THE PARAMETER METADATA AND NAMES
* ------------------------------------------------------------
  PERFORM GET-METADATA VARYING I FROM 1 BY 1 UNTIL I GREATER 5.
  MOVE PARAM-NAMES TO LIB-PARAM-NAME-ARRAY.

* ------------------------------------------------------------
* CAPTURE THE NULL AND FILE INDICATOR STRINGS
* ------------------------------------------------------------

  MOVE NULL-STRING TO LIB-NULL-STRING.
  MOVE FILE-STRING TO LIB-FILE-STRING.

  EXIT PROGRAM.

GET-METADATA.
  MOVE PARAM-METADATA(I) TO LIB-PARAM-SQLTYPE(I)[47:11:12].
  MOVE PARAM-METADATA(I) TO LIB-PARAM-PRECISION(I)[39:5:6].
  MOVE PARAM-METADATA(I) TO LIB-PARAM-SCALE(I)[33:5:6].
  MOVE PARAM-METADATA(I) TO LIB-PARAM-EMPTYISNULL(I)[27:0:1].
  MOVE PARAM-METADATA(I) TO LIB-PARAM-ZEROISNULL(I)[26:0:1].
  MOVE PARAM-METADATA(I) TO LIB-PARAM-SOURCEFIELD(I)[25:9:10].
END PROGRAM ORDERSDBINITLIB.

*
* ========================= ORDERSFILTER =========================
*

IDENTIFICATION DIVISION.
PROGRAM-ID. ORDERSFILTER.

ENVIRONMENT DIVISION.

DATA DIVISION.
WORKING-STORAGE SECTION.
77 ORDERDATETIME-FIELD-OFFSET       REAL.
77 ORDERDATETIME-FIELD-LENGTH       REAL.
77 ORDER-DATETIME                   REAL.
01 DUMMYBUFFER                      PIC X(256).

77 DATETIMETONUMERIC-RSLT           PIC 9.

LINKAGE SECTION.
01 INPT                             PIC X(8096).
01 PARAM-INFO-ARRAY                 REAL.
   05 PARAM-INFO OCCURS 256 TIMES.
77 RSLT                             REAL.
PROCEDURE DIVISION USING INPT,
                         PARAM-INFO-ARRAY
                   GIVING RSLT.
PARA.

MOVE 0 TO DATETIMETONUMERIC-RSLT.
IF LIB-IS-LINKED EQUAL 0
  PERFORM INIT-CONVERT-LIB.
MOVE 0 TO CONVERT-RSLT.
IF FIRST-TIME EQUAL 1
  PERFORM INIT-COMPARE-DATE.

MOVE 0 TO RSLT.

* ------------------------------------------------------------
* EXTRACT THE ORDER DATE (FIELD 3) AND CONVERT IT TO A FORM
* FOR COMPARISON
* ------------------------------------------------------------

  MOVE PARAM-INFO(3) TO ORDERDATETIME-FIELD-OFFSET [19:19:20].
  MOVE PARAM-INFO(3) TO ORDERDATETIME-FIELD-LENGTH [35:15:16].
  IF CONVERT-RSLT EQUAL 0
    CALL "DATETIMETONUMERIC OF CONVERTLIB"
          USING INPT,
          ORDERDATETIME-FIELD-OFFSET, ORDERDATETIME-FIELD-LENGTH,
          ORDERDATETIME
          GIVING CONVERT-RSLT.
  IF CONVERT-RSLT EQUAL 0
    IF DATETIMETONUMERIC-RSLT EQUAL 0
      IF ORDER-DATETIME GREATER OR EQUAL COMPARE-DATETIME
        MOVE 1 TO RSLT.

EXIT PROGRAM.

* ------------------------------------------------------------
* IF THE CONVERT LIB NAME HAS NOT BEEN ESTABLISHED DO THAT
* ------------------------------------------------------------
INIT-CONVERT-LIB.
  MOVE 1 TO LIB-IS-LINKED.
  CHANGE ATTRIBUTE TITLE OF "CONVERTLIB" TO
                            "OBJECT/SYMBOL/CONVERTLIB".

* ------------------------------------------------------------
* IF THIS IS THE FIRST TIME WE ARE BEING CALLED, SET UP THE
* DATETIME COMPARISON VALUE THAT WILL BE USED RATHER THAN
* RECOMPUTING IT EACH TIME THE FILTER IS CALLED
* ------------------------------------------------------------

INIT-COMPARE-DATE.
  MOVE 0 TO FIRST-TIME.
  MOVE SPACES TO DUMMYBUFFER.
  MOVE "2010-01-01" TO DUMMYBUFFER.
  MOVE 0 TO ORDERDATETIME-FIELD-OFFSET.
  MOVE 10 TO ORDERDATETIME-FIELD-LENGTH.
  CALL "DATETIMETONUMERIC OF CONVERTLIB"
        USING DUMMYBUFFER,
        ORDERDATETIME-FIELD-OFFSET, ORDERDATETIME-FIELD-LENGTH,
        COMPARE-DATETIME
        GIVING CONVERT-RSLT.

END PROGRAM ORDERSFILTER.

Sample Algol Filter

begin

% ------------------------------------------------------------
% The layout of the parameter metadata passed to the filter
% initialize function is descried in an array of words. Each
% word describes a parameter as follows
% ------------------------------------------------------------
define
   paramSqlType        = [47:12] #
  ,paramPrecision      = [39:06] #
  ,paramScale          = [33:06] #
  ,paramEmptyIsNull    = [27:01] #
  ,paramEmptyIsZero    = [26:01] #
  ,paramSourceField    = [25:10] #
  ;

% ------------------------------------------------------------
% The layout of the data record passed to the filter function
% is described in an array of words.
% ------------------------------------------------------------
define
   parmFieldLen      = [35:16] # % the length of the field
  ,parmFieldInx      = [19:20] # % the offset of the field start in
                                 % the buffer
  ;

% ------------------------------------------------------------
% Variables to capture global filter information passed to
% the library via the OrdersDBInitLib function
% ------------------------------------------------------------
real         libNumParams;            % number of fields being loaded
ebcdic array libNullFlag[0:15]        % the string that explicitily
                                      % identifies null field cotnet
             ,libFileFlag[0:15]       % the string that indicates that the
             ;                        % rest of the field is the name of a
                                      % file with the LOB conents
ebcdic array libParamNames[0:0,0:29]; % the names of the fields being
                                      % loaded
array libParamMetaData[0:0];          % description of the parameters

% ------------------------------------------------------------
% The following are various global values used in the library
% ------------------------------------------------------------

boolean      firstTime;          % flag to indicate whether this is
                                 % is the first call on the filter
integer      compareDateTimeVal; % the reference datetime value for
                                 % the orders filter
ebcdic array workBuff[0:72];     % general purpose work buffer
integer      workBuffLength;     % amount of workBuff in use

% ------------------------------------------------------------
% The functions available in the convert library
% ------------------------------------------------------------
library convertLib(TITLE="OBJECT/SYMBOL/CONVERTLIB.");

integer procedure ToDouble (buff, off, len, dVal);
  reference off, len, dVal;
  ebcdic array buff[0];
  integer off,len;
  double dVal;
  library convertLib;

integer procedure DateTimeToNumeric (buff, off, len, tVal);
  reference buff, off, len;
  ebcdic array buff[0];
  integer off, len, tVal;
  library convertLib;

integer procedure DateToNumeric (buff, off, len, tVal);
  reference buff, off, len;
  ebcdic array buff[0];
  integer off, len, tVal;
  library convertLib;

integer procedure TimeToNumeric (buff, off, len, tVal);
  reference buff, off, len;
  ebcdic array buff[0];
  integer off, len;
  real tVal;
  library convertLib;

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%                       OrdersDBInitLib                                %
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
procedure OrdersDBInitLib(numParams, paramNames, paramMetaData,
                          nullFlag, fileFlag);
value        numParams;
real         numParams;
ebcdic array paramNames[0,0];
array        paramMetaData[0];
ebcdic array nullFlag[0], fileFlag[0];
%----------------------------------------------------------

begin
  integer i;
  % ------------------------------------------------------------
  % capture the number of parameters
  % ------------------------------------------------------------

  libNumParams := numParams;

  % ------------------------------------------------------------
  % capture the parameter metadata and names
  % ------------------------------------------------------------
  resize(libParamMetaData,numParams + 1,retain);
  resize(libParamNames,numParams + 1,retain);
  for i := 1 step 1 until numParams do
  begin
    libParamMetaData[i] := paramMetaData[i];
    replace libParamNames[i,*] by " " for 30;
    replace libParamNames[i,*] by paramNames[i,*] until leq 48"00";
  end;

  % ------------------------------------------------------------
  % capture the null and file indicator strings
  % ------------------------------------------------------------

  replace libNullFlag by nullFlag until leq 48"00";
  replace libFileFlag by fileFlag until leq 48"00";

end;

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%                        OrdersFilter                                  %
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
real procedure OrdersFilter(inpt, paramInfo);
  ebcdic array		inpt[0];
  array				paramInfo[0];
%----------------------------------------------------------
begin
  integer orderDateTimeVal, rslt;
  % ------------------------------------------------------------
  % If this is the first time we are being called set up the
  % datetime comparison value that will be used rather than
  % recomputing it each time the filter is called
  % ------------------------------------------------------------
  if firstTime then
  begin
    replace workBuff by "2010-01-01";
    workBuffLength := 10;
    rslt := DateTimeToNumeric(workBuff, workBuffFLength,
                              compareDateTimeVal);
    firstTime := false;
  end;

OrdersFilter := 0;

% ------------------------------------------------------------
% extract the order date (field 3) and convert it to a form
% for comparison
% ------------------------------------------------------------

if rslt eql 0 then
  rslt := DateTimeToNumeric(inpt[paramInfo[3].parmFieldInx],
                            paramInfo[3].parmFieldLen,
                            orderDateTimeVal);

% ------------------------------------------------------------
% if the conversion was successful and the order date is at
% least the compare orders date, return 1
% ------------------------------------------------------------
if rslt eql 0 then
  if orderDateTimeVal geq compareDateTimeVal then
    OrdersFilter := 1;
end;

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%               OrdersDB FilterLibrary Mainline                        %
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

%	 ------------------------------------------------------------
%	 Export the library functions
% ------------------------------------------------------------
export
   OrdersDBInitLib
  ,OrdersFilter
  ;

  firstTime := true;
  freeze(temporary);

end.