0 DB2 SQL: Object 101

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);