SCHEMA
SET CURRENT SCHEMA='XXX'
UNIQUE Index
Different between UNIQUE index and UNIQUE constraint: unique index allow NULL. Unique constraint does not allow NULL. UNIQUE constraint can be used by reference in a FK.
CREATE TABLE t(c1 int,c2 int NOT NULL UNIQUE);
create unique index idx1 on t(c1);
insert into t values(1,1);
insert into t values(NULL,2);
insert into t values(1,NULL);
select * from t;
Table
- Base tables
- Result tables
- Materialized query table
- Declared temporary table: use by the application and life time only last till application terminate.
DECLARE GLOBAL TEMPORARY TABLE - Typed table: Base on attributes of user defined function.
List tables
db2 list tables
db2 list tables show detail
select tabschema,tabname ,type from syscat.tables where tabschema=current schema
db2 describe table is same as desc table in oracle. In Sybase and MSSQL, we can use sp_help
db2 describe table <tablename>
View
- WITH LOCAL CHECK OPTOIN : this has same effect as WITH CHECK OPTION in TSQL within Microsoft SQL Server.
- WITH CASCADE CHECK OPTION: When view is created base on view, if the child view is created with cascade check option, the insert would fail when insert is violate the parent condition.
CREATE TABLE t(c1 int);
CREATE VIEW v1 AS SELECT * FROM t WHERE c1 > 10;
CREATE VIEW v2 as select * from v1 with cascaded check option;
Insert into v2 values(2);

Alias
Alias is the alternation name for table or view.
create alias newv2 for v2;
Package
Package is an object that contains the information needed to process SQL. The process to creating and storing the package is called binding. Binding by default is happen at precompile process. However by specify the precompile options, we can defer binding to the later stage.
USER DEFINE DATATYPE
create distinct type euro as DECIMAL(9,3);
0 comments:
Post a Comment