DB2 - SPLessons
SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

DB2 View

DB2 View

shape Description

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

shape 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.

shape 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.

shape 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

shape 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.

shape 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.

shape 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

shape 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.

shape Syntax

Drop table <table_name> table_name => The accurate table stored in the database.

shape 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

shape 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.
  • Drop view - drop view will drop a table.