.logon dbc,
-- Create profile p1 and setting default retentiondays as 12
CREATE PROFILE p1 AS
DEFAULT RETENTIONDAYS = 12;
*** Profile has been created.
*** Total elapsed time was 1 second.
-- Default retentiondays column from DBC.PROFILES
SELECT ExtraField5
FROM DBC.PROFILES
WHERE profilename = 'p1';
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
ExtraField5
-----------
12
• Modifiy profile p1 for updating default retentiondays from 12 to 21
MODIFY PROFILE p1 AS
DEFAULT RETENTIONDAYS = 21;
*** Profile has been modified.
*** Total elapsed time was 1 second.
-- Default retentiondays column from DBC.PROFILES
SELECT ExtraField5
FROM DBC.PROFILES
WHERE profilename = 'p1';
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
ExtraField5
-----------
21
-- Create user u1 belong to profile p1 and setting default retentiondays as 1
CREATE USER u1 AS
PERM = 1e7,
PASSWORD = u1,
PROFILE = p1,
DEFAULT RETENTIONDAYS = 1;
*** User has been created.
*** Total elapsed time was 1 second.
-- Default retentiondays column from DBC.DBASE
SELECT ExtraField4
FROM DBC.DBASE
WHERE DataBaseNameI = 'u1';
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
ExtraField4
--------------
1
-- Modify user for setting updating default retentiondays from 1 to 90
MODIFY USER u1 AS
DEFAULT RETENTIONDAYS = 90;
*** Database/User has been modified.
*** Total elapsed time was 1 second.
-- Default retentiondays column from DBC.DBASE
SELECT ExtraField4
FROM DBC.DBASE
WHERE DataBaseNameI = 'u1';
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
ExtraField4
--------------
90
-- Create database d1 and setting default retentiondays as 42
CREATE DATABASE d1 AS
PERM = 1e7,
DEFAULT RETENTIONDAYS = 42;
*** Database has been created.
*** Total elapsed time was 1 second.
-- Default retentiondays column from DBC.DBASE
SELECT ExtraField4
FROM DBC.DBASE
WHERE DataBaseNameI = 'd1';
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
ExtraField4
--------------
42
MODIFY DATABASE d1 AS
DEFAULT RETENTIONDAYS = 2;
*** Database/User has been modified.
*** Total elapsed time was 1 second.
-- Default retentiondays column from DBC.DBASE
SELECT ExtraField4
FROM DBC.DBASE
WHERE DataBaseNameI = 'd1';
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
ExtraField4
--------------
2
.logon u1,
-- Profiles views: New column for default retentiondays
SELECT DefaultRetentiondays
FROM DBC.ProfileInfoV
WHERE PROFILENAME = 'p1';
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
DefaultRetentionDays
--------------------
21
SELECT DefaultRetentiondays
FROM DBC.ProfileInfoVX
WHERE PROFILENAME = 'p1';
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
DefaultRetentionDays
--------------------
21
SELECT DefaultRetentiondays
FROM DBC.ProfileInfoV_SZ
WHERE PROFILENAME = 'p1';
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
DefaultRetentionDays
--------------------
21
-- Database views: New column for default retentiondays
SELECT DefaultRetentiondays
FROM DBC.DatabasesV
WHERE DatabaseName = 'u1';
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
DefaultRetentionDays
--------------------
90
SELECT DefaultRetentiondays
FROM DBC.DatabasesVX
WHERE DatabaseName = 'u1';
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
DefaultRetentionDays
--------------------
90
SELECT DefaultRetentiondays
FROM DBC.DatabasesV_SZ
WHERE DatabaseName = 'u1';
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
DefaultRetentionDays
--------------------
90
-- Case: Table-level retentiondays inherited from profile
CREATE TABLE T1, NO FALLBACK,
STORAGE = TD_OFSSTORAGE
( a integer,
b integer );
*** Table has been created.
*** Total elapsed time was 1 second.
SHOW TABLE T1;
*** Text of DDL statement returned.
*** Total elapsed time was 1 second.
-------------------------------------------
CREATE MULTISET TABLE U1.T1 ,NO FALLBACK ,
MAP = TD_MAP1,
STORAGE = TD_OFSSTORAGE ,
RETENTIONDAYS = 21
(
a INTEGER,
b INTEGER)
NO PRIMARY INDEX
PARTITION BY COLUMN;
-- Case: Table-level retentiondays inherited from user
.logon dbc,
/* In this case first setting default retentiondays from profile p1
* from 21 to NULL for the new table inherited retentiondays from user
* instead profile p1.
*/
MODIFY PROFILE p1 AS
DEFAULT RETENTIONDAYS = NULL;
*** Profile has been modified.
*** Total elapsed time was 1 second.
-- Default retentiondays column from DBC.PROFILE
SELECT ExtraField5
FROM DBC.PROFILES
WHERE profilename = 'p1';
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
ExtraField5
-----------
?
.logon u1,
CREATE TABLE: Table-level retentiondays inherited from user
CREATE TABLE T2, NO FALLBACK,
STORAGE = TD_OFSSTORAGE
( a integer,
b integer );
*** Table has been created.
*** Total elapsed time was 1 second.
SHOW TABLE T2;
*** Text of DDL statement returned.
*** Total elapsed time was 1 second.
-------------------------------------------
CREATE MULTISET TABLE U1.T2 ,NO FALLBACK ,
MAP = TD_MAP1,
STORAGE = TD_OFSSTORAGE ,
RETENTIONDAYS = 90
(
a INTEGER,
b INTEGER)
NO PRIMARY INDEX
PARTITION BY COLUMN;
-- Case: Tables doesn’t have table-level retentiondays.
.logon dbc,
/* In this case first setting default retentiondays
* from user u1 from 90 to NULL for the new table be
* created without table-level retentiondays
*/
MODIFY USER u1 AS
DEFAULT RETENTIONDAYS = NULL;
*** Database/User has been modified.
*** Total elapsed time was 1 second.
SELECT ExtraField4
FROM DBC.DBASE
WHERE DataBaseNameI = 'u1';
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
ExtraField4
--------------
?
.logon u1,
CREATE TABLE T3, NO FALLBACK,
STORAGE = TD_OFSSTORAGE
( a integer,
b integer );
*** Table has been created.
*** Total elapsed time was 1 second.
SHOW TABLE T3;
*** Text of DDL statement returned.
*** Total elapsed time was 1 second.
------------------------------------------
CREATE MULTISET TABLE U1.T3 ,NO FALLBACK ,
MAP = TD_MAP1,
STORAGE = TD_OFSSTORAGE
(
a INTEGER,
b INTEGER)
NO PRIMARY INDEX
PARTITION BY COLUMN;