DBCREATE_TABLE_OPTS= Table Option

Specifies DBMS-specific syntax to be added to the CREATE TABLE statement.

Category:	Table Control
Data source:	DB2 under UNIX and PC, Greenplum, HAWQ, Hive, MySQL, Netezza, Oracle, SAP HANA, Teradata

Syntax

DBCREATE_TABLE_OPTS= 'DBMS-SQL-clauses'

Arguments

DBMS-SQL-clauses

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

Details

This option enables you to add DBMS-specific clauses to the end of the CREATE TABLE statement. The interface passes the CREATE TABLE statement and its clauses to the DBMS, which executes the statement and creates the DBMS table.

The availability and behavior of FedSQL statement options are data-source specific. If DBCREATE_TABLE_OPTS= options are used in a statement other than CREATE TABLE, those options might be ignored.

Examples

Example 1

In the following example, the Greenplum table TEMP is created with the value of the DBCREATE_TABLE_OPTS='DISTRIBUTED BY ("B")' option appended to the CREATE TABLE statement:

create table temp {options dbcreate_table_opts='distributed by ("b")'}
(a int, b int);

The following CREATE TABLE statement is passed to the DBMS in order to create the table:

create table temp (a int, b int) distributed by ("b")

Example 2

In the following example, the HAWQ table TEMP is created with the value of the DBCREATE_TABLE_OPTS='DISTRIBUTED BY ("A")' option appended to the CREATE TABLE statement:

create table temp {options DBCREATE_TABLE_OPTS='DISTRIBUTED by ("A")'}(a int, b int);

The following CREATE TABLE statement is passed to the DBMS in order to create the table:

create table temp {options DBCREATE_TABLE_OPTS='DISTRIBUTED by ("A")'}(a int, b int);

Example 3

In the following example, the Hive table TEMP is created with the value of the DBCREATE_TABLE_OPTS='PARTITIONED BY ("col1")' option appended to the CREATE TABLE statement:

create table temp {options DBCREATE_TABLE_OPTS='PARTITIONED BY (col1
int)'} (col1 int, col2 int);

The following CREATE TABLE statement is passed to the DBMS in order to create the table:

create table `temp` (`COL2` int) PARTITIONED BY (col1 int)

Example 4

In the following example, the MySQL table TEMP is created with the value of the DBCREATE_TABLE_OPTS='PARTITIONING KEY (x) USING HASHING' option appended to the CREATE TABLE statement:

create table temp {option dbcreate_table_opts='partitioning key (x) using hashing'}
   (x double);

The following CREATE TABLE statement is passed to the DBMS in order to create the table:

create table temp (x double) partitioning key (x) using hashing;

Example 5

In the following example, the Netezza table TEMP is created with the value of the DBCREATE_TABLE_OPTS='DISTRIBUTE ON (b)' option appended to the CREATE TABLE statement:

create table temp {options dbcreate_table_opts='distribute on (b)'}
(a int, b int);

The following CREATE TABLE statement is passed to the DBMS in order to create the table:

create table temp (A int, B int) distribute on (b)

Example 6

In the following example, the Oracle table TEMP is created with the value of the DBCREATE_TABLE_OPTS='NOLOGGING’ option appended to the CREATE TABLE statement:

create table temp{options dbcreate_table_opts='nologging'}
(a int, b int)

The following CREATE TABLE statement is passed to the DBMS in order to create the table:

create table temp ("a" number (10) , "b" number (10) ) nologging

Example 7

In the following example, the SAP HANA table TEMP is created with the value of the DBCREATE_TABLE_OPTS='PARTITION BY RANGE’ option appended to the CREATE TABLE statement:

create table temp {options table_type=column dbcreate_table_opts="partition by range (x) 
(partition 1 <= values < 10, partition others)"}
(x int, y int);

The following CREATE TABLE statement is passed to the DBMS in order to create the table:

create column table temp (x int, y int) partition by range (x) 
(partition 1 <= values < 10, partition others)

Example 8

In the following example, the Teradata table TEMP is created with the value of the DBCREATE_TABLE_OPTS='PRIMARY INDEX (B)' option appended to the CREATE TABLE statement:

create table temp {options dbcreate_table_opts='primary index(b)'}
(a int, b int);

The following CREATE TABLE statement is passed to the DBMS in order to create the table:

create table temp (a int, b int) primary index(b)

See Also

Table Options:

Copyright © SAS Institute Inc. All Rights Reserved.

Last updated: February 23, 2017