IGNORE_READ_ONLY_COLUMNS= LIBNAME Statement Option

Specifies whether to ignore or include columns whose data types are read-only when generating an SQL statement for inserts or updates.

Valid in:	LIBNAME statement
Default:	NO
Supports:	DB2 under UNIX and PC, Greenplum, MySQL, ODBC, Oracle, Teradata

Syntax

Syntax Description

Details

Comparisons

Syntax

IGNORE_READ_ONLY_COLUMNS= YES | NO

Syntax Description

YES

specifies to ignore columns whose data types are Read-Only when generating INSERT and UPDATE statements.

NO

specifies to include columns whose data types are Read-Only when generating INSERT and UPDATE statements

Details

Several databases include data types that can be Read-Only, such as the DB2 TIMESTAMP data type. Also, some databases have properties that allow certain data types to be Read-Only, such as the Microsoft SQL Server identity property.

When the IGNORE_READ_ONLY_COLUMNS= option is set to NO (the default), and a table contains a column that is Read-Only, an error is returned indicating that the data could not be modified for that column. For data sources that do not have Read-Only columns, such as SAS data sets, the option has no effect.

Comparisons

For the following example, a database that contains the table Products is created with two columns: Id and Product_Name. The Id column is defined by a Read-Only data type and Product_Name is a character column.

create table products (id int identity primary key, product_name varchar(40))

If you have a SAS data set that contains the name of your products, you can insert the data from the SAS data set into the Products table:

data work.products;
   id=1;
   product_name='screwdriver';
   output;
   id=2;
   product_name='hammer';
   output;
   id=3;
   product_name='saw';
   output;
   id=4;
   product_name='shovel';
   output;
run;

With IGNORE_READ_ONLY_COLUMNS=NO (the default), an error is returned by the DBMS because, in this example, the Id column cannot be updated. However, if you set the option to YES and execute a PROC APPEND, the append succeeds, and the SQL statement that is generated does not contain the Id column.

libname x fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=db2dsn ignore_read_only_columns=yes;
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
proc append base=x.products data=work.products;
run;

IGNORE_READ_ONLY_COLUMNS= LIBNAME Statement Option

Syntax

Syntax Description

YES

NO

Details

Comparisons

See Also