CREATE TRANSIENT TABLE Examples
1. Create the dept transient table where the name column must be unique:
CREATE TRANSIENT TABLE dept (
name CHAR(10) NOT NULL,
location CHAR(20),
CONSTRAINT dept_unique UNIQUE(name));
Create the emp_const transient table with a referential constraint to the name column in table dept:
CREATE TRANSIENT TABLE emp_const (
name CHAR(10) NOT NULL,
salary DECIMAL(10,2),
dept CHAR(10)
CONSTRAINT empref REFERENCES dept(name));
2. Create a transient table in the default transient location (transient_default):
CREATE TRANSIENT TABLE region_orders (
partno VARCHAR(10),
cust_no INTEGER NOT NULL,
qty INTEGER,
unit_price FLOAT,
orderno INTEGER NOT NULL);
INSERT INTO region_orders VALUES('123-45',101,10,10.0,1001),('123-45', 202, 100, 10.00, 1002),('543-21',987,2,99.99,1007),('543-21',654,33,99.99,1004),('987-65', 321, 20, 29.99, 1003);
3. Create a customer transient table. Explicitly declare the structure to be X100:
CREATE TRANSIENT TABLE customer (
customer_no INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(40) NOT NULL,
customer_status_code CHAR(10) NOT NULL)
WITH STRUCTURE = X100;
4. Create a structure of related transient tables:
CREATE TRANSIENT TABLE tcons1 (
rnum INTEGER NOT NULL,
col1 INTEGER NOT NULL,
CONSTRAINT tcons1pk PRIMARY KEY (col1));
GRANT SELECT ON TABLE tcons1 TO PUBLIC;
CREATE TRANSIENT TABLE tcons2 (
rnum INTEGER NOT NULL,
col2 INTEGER NOT NULL, col3 INTEGER NOT NULL,
CONSTRAINT tcons2pk PRIMARY KEY (col2,col3));
GRANT SELECT ON TABLE tcons2 TO PUBLIC;
CREATE TRANSIENT TABLE tcons3 (rnum INTEGER NOT NULL,
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
col3 INTEGER NOT NULL,
col4 INTEGER,
CONSTRAINT tcons3pk PRIMARY KEY (col1,col2),
CONSTRAINT tconsfk1 FOREIGN KEY (col1) REFERENCES tcons1,
CONSTRAINT tcons3fk2 FOREIGN KEY (col1,col2) REFERENCES tcons2 (col2,col3),
CONSTRAINT tconsuk UNIQUE (col3),
CONSTRAINT tcons2fk3 FOREIGN KEY (col4,col3)
REFERENCES tcons2 (col3,col2));
GRANT SELECT ON TABLE tcons3 TO PUBLIC;
5. Define a transient table with a BY DEFAULT identity column that maps to a negative incrementing sequence. INSERT statements can override the sequence by explicitly defining values for column d1, but in the absence of an explicit value, the INSERT will generate the next value from the underlying sequence.
CREATE TRANSIENT TABLE t2 (
d1 DECIMAL(15) GENERATED BY DEFAULT AS IDENTITY (START WITH -1 INCREMENT BY -10),
d2 FLOAT, . . .);
6. Create a transient table with the address and salary columns masked:
CREATE TRANSIENT TABLE employee(
name VARCHAR(20),
address VARCHAR(20) MASKED,
salary FLOAT MASKED AS 0);
7. Create a transient table, add a table comment, and provide access to it using GRANT:
CREATE TRANSIENT TABLE tbint (rnum INTEGER NOT NULL, cbint BIGINT);
COMMENT ON TABLE tbint IS 'This describes transient table TBINT.';
GRANT SELECT ON TABLE tbint TO PUBLIC;
8. Create two partitions in db_loc1:
CREATE TRANSIENT TABLE X
(a i4 NOT NULL,
b i4 NOT NULL)
WITH PARTITION=(HASH on a 2 PARTITIONS);
9. Create a transient partitioned table with rows distributed automatically (that is, randomly):
CREATE TRANSIENT TABLE employee (
emp_no INTEGER NOT NULL NOT DEFAULT,
emp_name CHAR(32) NOT NULL NOT DEFAULT,
dept_no INTEGER,
emp_rating INTEGER)
WITH JOURNALING,
PARTITION = (AUTOMATIC 8 PARTITIONS);