Data Set Options for Relational Databases

TRAP151= Data Set Option

Enables removal of columns that cannot be updated from a FOR UPDATE OF clause so that update of columns can proceed as normal.

Default value:	NO
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	DB2 under z/OS

Syntax
Syntax Description
Examples

Syntax

TRAP151=YES | NO

Syntax Description

YES: removes the non-updatable column that is designated in the error-151 and reprepares the statement for processing. This process is repeated until all columns that cannot be updated are removed, and all remaining columns can be updated.
NO: disables TRAP151=. TRAP151= is disabled by default. It is not necessary to specify NO.

Examples

In this example, DB2DBUG is turned on so that you can see what occurs when TRAP151=YES:

SAS Log for TRAP151=YES

proc fsedit data=x.v4(trap151=yes);
run;
SELECT * FROM V4 FOR FETCH ONLY
SELECT * FROM V4 FOR FETCH ONLY
SELECT "A","X","Y","B","Z","C" FROM V4 FOR UPDATE OF "A","X","Y","B","Z","C"
DB2 SQL Error, sqlca->sqlcode=-151
WARNING: SQLCODE -151: repreparing SELECT as:
  SELECT "A","X","Y","B","Z","C" FROM V4 FOR UPDATE OF "A","Y","B","Z","C"
DB2 SQL Error, sqlca->sqlcode=-151
WARNING: SQLCODE -151: repreparing SELECT as:
  SELECT "A","X","Y","B","Z","C" FROM V4 FOR UPDATE OF "A","B","Z","C"
DB2 SQL Error, sqlca->sqlcode=-151
WARNING: SQLCODE -151: repreparing SELECT as:
  SELECT "A","X","Y","B","Z","C" FROM V4 FOR UPDATE OF "A","B","C"
COMMIT WORK
NOTE:  The PROCEDURE FSEDIT used 0.13 CPU seconds and 14367K.

The next example features the same code with TRAP151 turned off:

SAS Log for TRAP151=NO

proc fsedit data=x.v4(trap151=no);
run;
SELECT * FROM V4 FOR FETCH ONLY
SELECT * FROM V4 FOR FETCH ONLY
SELECT "A","X","Y","B","Z","C" FROM V4 FOR UPDATE OF "A","X","Y","B","Z","C"
DB2 SQL Error, sqlca->sqlcode=-151
ERROR: DB2 prepare error;  DSNT4081 SQLCODE= --151, ERROR;  
   THE UPDATE STATEMENT IS INVALID BECAUSE THE CATALOG DESCRIPTION OF COLUMN C 
   INDICATES THAT IT CANNOT BE UPDATED.  
COMMIT WORK
NOTE:  The SAS System stopped processing this step because of errors.
NOTE:  The PROCEDURE FSEDIT used 0.08 CPU seconds and 14367K.

Top of Page