SQLUNDOPOLICY= System Option

Specifies how PROC SQL handles updated data if errors occur while you are updating data. You can use UNDO_POLICY= to control whether your changes become permanent.

Valid in:	configuration file, SAS invocation, OPTIONS statement
Categories:	Files: SAS Files
Categories:	System Administration: SQL
PROC OPTIONS GROUP=	SASFILES
PROC OPTIONS GROUP=	SQL
Default:	REQUIRED
Supports:	SQL Server
Note:	This option can be restricted by a site administrator. For more information, see “Restricted Options” in SAS System Options: Reference.

Syntax

Syntax Description

Details

Syntax

SQLUNDOPOLICY=NONE | OPTIONAL | REQUIRED

Syntax Description

NONE

keeps any updates or inserts.

OPTIONAL

reverses any updates or inserts that it can reverse reliably.

REQUIRED

reverses all inserts or updates that have been done to the point of the error. This is the default.

CAUTION:

Some UNDO operations cannot be done reliably.

When a change cannot be reversed, PROC SQL issues an error message and does not execute the statement.

Details

Regardless of the SAS session’s AUTOCOMMIT= setting, SAS Federation Server sets AUTOCOMMIT=YES only when it is connecting to a data source that processes one statement per connection. For data sources that do not support transactions, the FEDSVR engine sets the SQL_UNDO policy to false to prevent rollbacks. The combined setting of these internal options causes PROC SQL to read tables one row at a time. When you are updating a database that processes one statement per connection, you can significantly improve processing performance by setting SQLUNDOPOLICY=NONE. However, ensure that NONE is an appropriate setting for your application.

SQL Server is a one-data source that would benefit by setting SQLUNDOPOLICY=NONE.

The value that is specified in the SQLUNDOPOLICY= system option is in effect for all SQL procedure statements, unless the PROC SQL UNDO_POLICY= option is set. The value of the UNDO_POLICY= option takes precedence over the SQLUNDOPOLICY= system option. The RESET statement can also be used to set or reset the UNDO_POLICY= option. However, changing the value of the UNDO_POLICY= option does not change the value of the SQLUNDOPOLICY= system option. After the procedure is complete, it reverts to the value of the SQLUNDOPOLICY= system option.