&NDBSCAN

Scans a NetMaster database (NDB) for all records matching a search argument.

&NDBSCAN dbname { [ SEQUENCE=result-list-name ]
                  [ KEEP={ YES | NO } ]
                  [ SORT=sort-expression ] ]
                  [ EXEC={ YES | NO } ]
                  [ OPTIMIZE={ NO | YES } ]
                  [ RETDEL={ NO | YES } ]
                  [ RETMSG={ NO | YES } ]
                  [ RETPOS={ NO | YES } ]
                  [ IOLIMIT=n ]
                  [ TIMELIMIT=n ]
                  [ STGLIMIT=n ]
                  [ RECLIMIT=n ]
                  { START | [ DATA ] S-EXP | END | CANCEL } }

S-EXP: [ SELECT * FROM ndbname [ correl-id ] WHERE ] EXP1

EXP1: EXP2 [ OR EXP2 ... ]

EXP2: EXP3 [ AND EXP3 ... ]

EXP3: [ NOT ... ] EXP4

EXP4: ( EXP1 ) | EXP5

EXP5: [ IGNORE { TRUE | FALSE } ]
        TEST1 | TEST2 | TEST3 | TEST4 | TEST5 | TEST6 | TEST7 | TEST8 | TEST9 | TEST10

TEST1: SEQUENCE sequence-name

TEST2: L-LIST PRESENT

TEST3: L-LIST ABSENT

TEST4: [ FIELDS ] fieldname IS [ NOT ] NULL

TEST5: [ FIELDS ] fieldname [ NOT ] BETWEEN value AND value

TEST6: EXISTS ( SUBSEL )

TEST7: [ FIELDS ] fieldname [ NOT ] IN { ( value [ , value ] ) | ( SUBSEL ) }

TEST8: L-LIST [ NOT ] LIKE R-LIST

TEST9: L-LIST  CONTAINS R-LIST

TEST10: L-LIST { = | ¬= | < | > | <= | >= }
               { R-LIST | [ ANY | ALL | SOME  ] ( SUBSEL ) }

SUBSEL: SELECT { fieldname [ : fieldname ] | prefix* }
               [ , ... ] FROM ndbname [ correl-id ] WHERE EXP1

L-LIST: [ ANY | ALL ] [ FIELDS ]
        { fieldname [ : fieldname ] | prefix* } [ ,  ... ]

R-LIST: [ ANY | ALL | SOME ]
        { [ VALUES ] { value { [ : value | GENERIC ] } } [ , ... ]|
            FIELDS [ ( correl-id ) ]
          { fieldname [ : fieldname ] | prefix* } [ , ... ]
          [ { PLUS | MINUS } number ] }

The &NDBSCAN statement is used to find all records in an NDB that pass a set of criteria, called a scan-expression.

These criteria include such things as:
A field or list of fields, that is equal to, not equal to(or other test) a given value, or list of values.
A field or list of fields, that is equal to (or other test) another field, or list of fields, in the same record. For numeric or date format fields, an adjustment amount is specified.
The presence or absence of a given field, or list of fields, in a record.
The results of a previous &NDBSCAN, which is further filtered, combined with other scan results.
Generic and range matches on values.

The resulting list of records can optionally be saved under a user-nominated result-list-name, for processing by &NDBGET.

To prevent a scan from using excessive system resources, you can optionally impose limits on the amount of these resources.

The fields referenced in the scan need not be keyed. The scan processing logic uses keys wherever possible, but will automatically switch to reading records whenever a non-keyed field is referenced. The only penalty is the number of I/Os, and the elapsed time.

Operands:

dbname

Specifies the name of the NDB that is to be scanned, and is mandatory. The NDB must have been previously opened by an &NDBOPEN statement.

SEQUENCE=result-list-name

An optional parameter, which provides a name for the result list. This name can then be used in an &NDBGET SEQUENCE=result-list-name statement to retrieve the records that passed the scan.

If this operand is omitted, no results are saved. The value of &NDBRC indicates the result of the scan, and the scan information variables are set. KEEP= and SORT= cannot be specified if this operand is omitted. The result-list-name must not already exist, either as a scan result list name or as the name of a SEQUENCE specified on an &NDBSEQ DEFINE statement.

If SEQUENCE is specified, the result-list-name is only saved if the scan completes and &NDBRC is set to zero. SEQUENCE is abbreviated to SEQ.

KEEP={ NO | YES }

This is an optional operand which is only valid if SEQUENCE is also specified. It indicates whether or not the result list is to be retained when an &NDBGET for the sequence returns an end-of-file (&NDBRC=2).

KEEP=NO indicates the result is to be deleted (this is the default). KEEP=YES indicates that the result list is not to be deleted, but is to be retained until explicitly deleted by &NDBSEQ DELETE, or by an &NDBCLOSE for this database.

SORT=sort-expression

This is an optional operand which is only valid if SEQUENCE is specified. It indicates the name of the field on which the final result will be sorted. Any field defined in the database is used, not just keyed fields.

The options for this operand are:

omitted (no sort wanted)
() (no sort wanted)
name (entire field contents, ascending)
name(start,end,dir)
(name1(start,end,dir),name2(start,end,dir),...)

start is the start offset (* means 1). If start is omitted, 1 is assumed. It is *, or 1 for non-character fields.

end is the end position within the field. If end is omitted or specified as *, this means the end of the field. It must be omitted or * for non-character fields.

dir is the sort direction. It must be A (indicating ascending, the default) or D (indicating descending). Up to 7 sort fields is specified. If more than 1 is specified, the entire list must be enclosed in parentheses.

If the last sort field is descending, the RIDs of records with equal sort keys are also sorted descending. This is to ensure complete and correct up/down symmetry.

CAPS=SEARCH fields are uppercased when used as sort keys.

EXEC={ YES | NO }

This operand allows you to syntax check a scan request without executing it. If the scan expression (and the sort expression, if specified) has no errors, then the scan is executed, by default.

Specification of EXEC=NO means that no execution takes place. If there are no errors in the scan or sort expressions, &NDBRC will be set to zero.

OPTIMIZE={ NO | YES }

(Or OPTIMISE) This operand allows you to subset the optimization option in effect for the NDB for this scan.

You cannot turn on optimization if it is off at the NDB level.

RETDEL={ NO | YES }

This option (defaulting to NO for compatibility), if set to YES, will cause &NDBGET statements reading the resultant scan-built sequence to not skip over deleted records. Rather, the get will return an NDBRC of 1 (record not found), and the RID. This option will greatly simplify selection list processing of scan sequences.

RETMSG={ NO | YES }

RETMSG=YES specifies that all messages produced as a result of errors in the scan or sort expression, are to be returned in NCL variables with names &NDBMSGn, where n starts from 1.

This is completely independent of any &NDBCTL MSG= setting.

RETPOS={ NO | YES }

This option (defaulting to NO for compatibility), if set to YES, will cause &NDBGET statements reading the resultant scan-built sequence to set the new &NDBSQPOS system variable to the relative position in the scan sequence of the record just read. This will be a number from 1 to the number of records in the scan sequence.

If the RETDEL=YES option is also in effect, the &NDBSQPOS variable will be set when a delete record indication is returned (&NDBRC set to 1).

IOLIMIT=n

This operand allows the scan to be limited to processing a specified number of logical I/Os. A logical I/O corresponds to a VSAM request, not to physical disk I/O. A scan that exceeds this limit will be terminated with &NDBRC set to 5.

Omission of this operand, or coding it as IOLIMIT=0, or IOLIMIT=, causes the value of the SYSPARMS NDBDIOL setting to be used. If the value of n is greater than the SYSPARMS NDBMIOL setting, the SYSPARMS setting is used.

TIMELIMIT=n

This operand allows the scan to be limited to a specified elapsed time, expressed in seconds. A scan that exceeds this time will be terminated with &NDBRC set to 6.

Omission of this operand, or coding it as TIMELIMIT=0 or TIMELIMIT=, causes the value of the SYSPARMS NDBDTML setting to be used. If the value of n is greater than the SYSPARMS NDBMTML setting, the SYSPARMS setting is used.

STGLIMIT=n

This operand allows the scan to be limited to a specified amount of working storage, expressed in Kilobytes. A scan that exceeds this limit will be terminated with &NDBRC set to 7.

Omission of this operand, or coding it as STGLIMIT=0 or STGLIMIT=, causes the value of the SYSPARMS NDBDSTL setting to be used. If the value of n is greater than the SYSPARMS NDBMSTL setting, the SYSPARMS setting is used.

RECLIMIT=n

This operand allows the scan to be limited to a specified number of records that finally pass. If this limit is met or exceeded during the final phase of scan processing, the scan is terminated with &NDBRC set to 8.

Omission of this operand, or coding it as RECLIMIT=0 or RECLIMIT=, causes the value of the SYSPARMS NDBDRCL setting to be used. If the value of n is greater than the SYSPARMS NDBMRCL setting, the SYSPARMS setting is used.

RECLIMIT=1 is used if you only want to know whether any records at all have or have not passed the scan criteria, and you are not concerned with the exact number or specific IDs of any such records. &NBDSCAN RECLIMIT=1 will not set the &NDBRID variable.

By specifying RECLIMIT=1, the scan terminates with &NDBRC set to 8, and &NDBRID set to 0, when one passing record is found.

START

This operand indicates the start of a multi-statement &NDBSCAN request. The scan-expression will be built up by one or more &NDBSCAN DATA statements.

The SEQ, KEEP, SORT, and LIMIT operands must all be specified on the START statement. The START operand must be the last operand specified on the statement.

DATA

This operand indicates that a scan-expression (for a single-statement scan), or part of a scan-expression (for a START/DATA/END multi-statement scan), follows. This operand is omitted, but inclusion will prevent any syntax errors if the scan-expression contains any &NDBSCAN operands (for example, START or END).

END

This operand indicates the end of a multi-statement scan. The entire scan-expression is concatenated together and passed to the database for processing.

CANCEL

This operand indicates that a partially-built multi-statement scan is to be canceled. This operand is valid even if no current &NDBSCAN START/DATA/END set is active for this database.

Examples:

The following example will find all records with the field DOB present and less that JAN 1st,1960. The list of matching records is saved in the sequence called RESULT which can then be read using &NDBGET SEQ=RESULT.

&NDBSCAN MYNDB SEQ=RESULT SORT=SURNAME +
   DATA DOB LT 600101

The next example will find, in the database PROBLEMS, all records with the field DATEOCUR less than today (&DATE7 is the current date in YYMMDD format), that are either not closed (&DATECLOS ABSENT) or were closed later that 5 days after opening.

&NDBSCAN PROBLEMS SEQ=S1 SORT=DATEOCUR START
&NDBSCAN PROBLEMS DATA DATEOCUR LT &DATE7
&NDBSCAN PROBLEMS DATA AND (DATECLOS ABSENT
&NDBSCAN PROBLEMS DATA OR DATECLOS GT FIELD DATEOCUR PLUS 5)
&NDBSCAN PROBLEMS END

The next example will determine if there are any records on MYNDB with the field SURNAME equal to JONES. If SURNAME is keyed, an &NDBGET statement would work, and be more efficient. However, the &NDBSCAN statement works regardless of the keying or otherwise, and allows more complex expressions to be specified.

If there are no records with NAME=JONES, &NDBRC will be set to 4. If there is at least one record, the scan will terminate, and &NDBRC will be set to 8. &NDBRID will contain the RID of the first record that the scan found.

Note: No assumption is made about the number of other records that may have passed the scan.

&NDBSCAN MYNDB RECLIMIT=1 DATA SURNAME = JONES

The next example builds a list of all records in the PERSONEL NDB where all the fields starting with REVIEW are equal to POOR and all fields starting with ATTITUDE are also equal to POOR.

&NDBSCAN PERSONEL SEQ=SACKINGS START
&NDBSCAN PERSONEL DATA ALL REVIEW*= 'POOR' AND
&NDBSCAN PERSONEL DATA ALL ATTITUDE*= 'POOR' 
&NDBSCAN PERSONEL END

Notes:

&NDBSCAN is an extremely powerful verb. The SYSPARMS command NDBxxx operands allow the setting of default and maximum limits for I/O, storage, and matching records, which you can use to control &NDBSCAN.

You need never be concerned whether fields are keyed or non-keyed when coding an &NDBSCAN statement. The only difference will be in the number of I/Os generated and the elapsed time.

&NDBCTL SCANDEBUG=YES displays the generated action table which shows whether record-level scanning is required.

&NDBSCAN requests run asynchronously from other database requests. The SYSPARMS NDBSUBMN and NDBSUBMX operands allow you to specify minimum and maximum subthreads, which handle scan requests, for any one NDB. Too many concurrent scan requests can impact other product region users. Too few concurrently allowed scan requests can cause a backlog of scans.

More information:

&NDBGET

&NDBSEQ