The following examples show how to create a table and set the storage access to TD_OFSSTORAGE, so the table is created and stored on the Object File System.
Example: Create an Object File System row table
CREATE MULTISET TABLE Orders,
STORAGE=TD_OFSSTORAGE
( StoreNo SMALLINT
, OrderId INTEGER
, OrderDate DATE FORMAT 'YYYY-MM-DD’
, OrderTotal INTEGER
)
ORDER BY (OrderDate, OrderTotal)
NO PRIMARY INDEX
;
Example: Create an Object File System columnar table
CREATE MULTISET TABLE Orders,
STORAGE=TD_OFSSTORAGE
( StoreNo SMALLINT
, OrderId INTEGER
, OrderDate DATE FORMAT 'YYYY-MM-DD’
, OrderTotal INTEGER
)
ORDER BY (OrderDate, OrderTotal)
NO PRIMARY INDEX
PARTITION BY COLUMN;
Example: Create a Database File System table from an existing Object File System table
CREATE TABLE nds_tab, STORAGE=TD_NDSSTORAGE AS Orders WITH DATA;
Example: Provide default storage at database level
If storage is defined at database level with override option to ignore unsupported syntax using this statement:
CREATE DATABASE db1 as DEFAULT STORAGE = TD_OFSSTORAGE OVERRIDE ON ERROR, PERM = 1e8;
Then the following two "create table" statements are equivalent:CREATE TABLE db1.tb1 (a int, b int) no primary index;
Table is created as Object File System table even though an explicit storage clause is not specified.CREATE TABLE db1.tb2 (a int, b int) primary index(a);
PI is ignored and table is created as NOPI Object File System table.
If modify storage at database level with override option to not ignore unsupported syntax using this statement:
MODIFY DATABASE db1 as DEFAULT STORAGE = TD_OFSSTORAGE OVERRIDE NOT ON ERROR;
Then the preceding CREATE TABLE statement 1 succeeds, but statement 2 fails with an error saying PI is not supported for Object File System tables.Example: Provide default storage at user (profile) level
CREATE PROFILE Test_P1 as DEFAULT STORAGE = TD_NDSSTORAGE ;