DB2 Views are the database objects that can be created on a table to improve the performance of DB2 Server. A DB2 view has unique look on data from one or more tables. It can organize data in unique order, focus or hide some data.
DB2 Views comprises of a stored query accessible as a fundamental table composed of the outcome set. Beside standard tables a perspective does not shaped a part of the physical schema. It is a virtual table, dynamic in the database. DB2 View is a stored query, and it can be attributed like a table.
DB2 Views object is mainly classified into
Create view
Alter view
Drop view
Create view
Description
Once a view is created from base table, all DML operations can be performed on that view which effects its base table. This kind of view is called simple view. Create any number of columns in a table. The system privileges are required to create own schema, and can execute any object privilege on object type.
Syntax
db2 create view <view_name>(<column_name1>,<column_name2> as select from <table_name>
View_name => Any accurate table.
column_name => The columns that inserted in the table.
Examples
By viewing the below example, the concepts of simple view can be understood easily.
[sql]
db2 => select * from vehicles.cars;
car_id car_name cost
-------- ---------- --------
1 audi 52642
2 skoda 526400
3 volva 52640
4 volva 52000
5 hummer 41400
5 record(s) selected.
db2 => create view Allot.cheapcars as select name from cars where cost>52640;
ERROR 1054 (42S22): Unknown column 'name' in 'field list'
db2 => CREATE VIEW Allot.Cheapcars AS SELECT car_name from vehicles.cars where cost>52640;
db2 => select * from Allot.cheapcars;
car_name
----------
volva
hummer
2 record(s) selected
[/sql]
In the above example, a simple table cars has been created and by applying view operation to that table, like inserting some condition on column name.(i.e.,cost of car>52640 is the condition, and it show the cost of cars less than that value).
Alter view
Description
Once a view is created from base table, all alter operations can be performed on that view which effects its base table.
The alter view proclamation is like the create view proclamation aside from create is replaced with alter keyword.
Syntax
db2 alter view <view_name> alter <column_name> add scope <view_name>;
View_name => Any accurate table.
column_name => The columns that inserted in the table.
Examples
By viewing the below example, the concepts of alter view can be understood easily.
[sql]
db2 => select * from vehicle.cars;
car_id car_name cost
-------- ---------- --------
1 audi 52642
2 skoda 526400
3 volva 52640
4 volva 52000
5 hummer 41400
5 record(S) selected
db2 => alter view Allot.cheapcars as select car_name from vehicles. Cars where cost<52640;
db2 => select * from Allot.cheapcars;
car_name
----------
volva
hummer
2 rows in set (0.00 sec)
[/sql]
In the above example, a simple table cars has been created and then alter the table by applying view operations to that table, like adding some condition on column values using where clause.(The column name cost have been alter by using where clause) i.e, cost>52640 will be show in the result set.
Drop view
Description
Drop table will drop the table. In the same way drop view will drop the permission of view table, and there is no such table exist in the databases.
Syntax
Drop table <table_name>
table_name => The accurate table stored in the database.
Examples
By viewing the below example, the concepts of drop view can be understood easily.
[sql]
db2 => drop table vehicle.cars;
db2 => select * from Allot.cheapcars;
ERROR 1356 (HY000): View 'employee.cheapcars' references invalid table(s) or column(s) or function(s) ordefiner/invoker of view lack rights to use them[/sql]
In the above example, the table name vehicle.cars have been dropped from the database, and cannot perform any operations on cars related to Allot cheap cars.
Summary
Key Points
DB2 View -View is a database object.
Create view - DML operations can be performed in create view.
Alter view - DML operation can be performed in alter view.