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 UNIX/PC, Greenplum, MySQL, ODBC, Oracle, Teradata

Syntax

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

Details

Comparisons

See Also