Data Set Options for Relational Databases

DBCREATE_TABLE_OPTS= Data Set Option

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

Default value:	the current LIBNAME setting
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	Aster nCluster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HP Neoview, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase, Sybase IQ, Teradata

Syntax
Syntax Description
Details
Example
See Also

Syntax

DBCREATE_TABLE_OPTS='DBMS-SQL-clauses'

Syntax Description

DBMS-SQL-clauses: are 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