DBCREATE_TABLE_OPTS= Data Set Option

Specifies DBMS-specific syntax to add to the CREATE TABLE statement.

Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
Default:	the current LIBNAME setting
Data source:	Aster nCluster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, Hadoop, HP Neoview, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase, Sybase IQ, Teradata
See:	DBCREATE_TABLE_EXTERNAL= LIBNAME option, DBCREATE_TABLE_EXTERNAL= data set option, DBCREATE_TABLE_LOCATION= data set option, DBCREATE_TABLE_OPTS= LIBNAME option, DBTYPE= data set option

Syntax

Details

Example

Syntax

DBCREATE_TABLE_OPTS='DBMS-SQL-clauses'

Syntax Description

DBMS-SQL-clauses: specifies one or more DBMS-specific clauses that can be appended at the end of an SQL CREATE TABLE statement.

Details

You can use this option to add DBMS-specific clauses at the end of the SQL CREATE TABLE statement. The SAS/ACCESS engine passes the SQL CREATE TABLE statement and its clauses to the DBMS. The DBMS then executes the statement and creates the DBMS table. This option applies only when you are creating a DBMS table by specifying a libref associated with DBMS data.

If you are already using the DBTYPE= data set option within an SQL CREATE TABLE statement, you can also use it to include column modifiers.

Example

In this example, the DB2 table TEMP is created with the value of the DBCREATE_TABLE_OPTS= option appended to the CREATE TABLE statement.

libname mydblib db2 user=testuser
        pwd=testpass dsn=sample;
data mydblib.temp (DBCREATE_TABLE_OPTS='PARTITIONING
     KEY (X) USING HASHING');
x=1; output;
x=2; output;
run;

When you use this data set option to create the DB2 table, the SAS/ACCESS interface to DB2 passes this DB2 SQL statement:

CREATE TABLE TEMP (X DOUBLE) PARTITIONING
   KEY (X) USING HASHING