FASTLOAD ... INTO TABLE Syntax errors with WHEN and NULLIF clauses

Products

• Fast Load for DB2 for z/OS

Issue/Introduction

FASTLOAD using the INTO TABLE statement with WHEN and NULLIF clauses, receives syntax error messages because parameters in these clauses are not specified correctly. 
 
PUT0092E - Syntax Error on or near location 001

Environment

Release : 20.0

Component : Fast Load for DB2 for z/OS

Resolution

For FASTLOAD .. INTO TABLE ... statement with WHEN, DEFAULTIF and NULLIF clauses the following has to be considered: 
  
. When ESQL is used, the position for NULLIF, DEFAULTIF, or WHEN clauses needs to be specified as POS<start:end> instead of the ordinary position specification (start:end). 
  
Example:

FASTLOAD ... INTO TABLE ...
WHEN ESQL POS<1:4> >< '1234' ;

. An additional issue are the decimal literals used in some WHEN clauses. Supported decimal literals are hexadecimal strings X'hex-string',character strings 'string', and graphic strings G'graphic-string'. This is similar to the IBM Db2 Load.
  
Example:

FASTLOAD ... INTO TABLE ...
WHEN ESQL POS<1:4> >< '1234' AND POS<5:6> = X'002D' AND
    (COL1_NAME = X'800C' OR 
     COL1_NAME = X'810C') ;
(...
 COL1_NAME  POSITION( 7: 8) DECIMAL 
...)

. The NULLIF, DEFAULTIF, or WHEN clauses can be also written without ESQL, as it contains only '=' operands. In that case, there is no need for POS and a semicolon. The literals still need to be specified as hex string.

Example:
 
FASTLOAD ... INTO TABLE ...
WHEN <1:4> = '1234' AND <5:6> = X'002D' AND
    (COL1_NAME = X'800C' OR 
     COL1_NAME = X'810C') 
(...
 COL1_NAME  POSITION( 7: 8) DECIMAL 
...)