SQL-Ease : SQL statement which extends beyond column 72 returns -104
Article ID: 63113
Updated On:
Products
Issue/Introduction
An SQL statement which extends beyond column 72, like the following statement:
SELECT * FROM AUTHID.COLMASK1
WHERE (TIFIN_CATFIN||TIFIN_TIPFIN||SUBSTR(CHAR(TIFIN_NUMFIN),2,5) = 'K1');
returns SQLCODE -104 even if it is executed in SPUFI.
The reason the SQL is not being processed is that it extends beyond column 72 in the input stream.
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
000001 SELECT * FROM AUTHID.COLMASK1
000002 WHERE (TIFIN_CATFIN||TIFIN_TIPFIN||SUBSTR(CHAR(TIFIN_NUMFIN),2,5) = 'K1');
SYMPTOMS:
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "<END-OF-STATEMENT>".
SOME SYMBOLS THAT MIGHT BE LEGAL ARE: AT MICROSECONDS
MICROSECOND SECONDS SECOND MINUTES
Environment
Release: R20
Component: RBP
Cause
Batch Processor(RBP) is working as designed, the same way as SPUFI. IBM's SPUFI accepts SQL from columns 1-72, ignores whatever is beyond column 72, and begins accepting SQL in the next line at column 1.
Resolution
The fact that RBP accepts SQL from positions 1-72 is controlled by an SQL processing option set by the DB2 precompiler or an SQL statement coprocessor using the option: MARGINS(2,3)(m,n,c]. The default for this is MARGINS(1,71,16). RBP uses the HOST(ASM) defaults. See Create Customized Batch Processor Scripts
"Statements are written within columns 1 through 72 of an input record. If content exceeds column 72, continue the statement on subsequent lines using apostrophes or quotation marks to improve readability."
To fix the SQL statement above just do a ISPF Text Split(TS) before the = sign to split the line to another line:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
SELECT * FROM AUTHID.COLMASK1
WHERE (TIFIN_CATFIN||TIFIN_TIPFIN||SUBSTR(CHAR(TIFIN_NUMFIN),2,5)
= 'K1');
The above SQL statement now does not cross the column 72 point and executes normally.
Additional Information
Create Customized Batch Processor Scripts
The IBM DB2V12 Application Programming and SQL Guide, "Options for SQL statement Processing" provides further information on the MARGINS option.
Feedback
