Views in DB2
Note:This topic was updated for 9.3.1.
You can use views in a number of ways to improve access to your data. Views can provide subsets of rows or columns in a predefined way, codifying common queries in the database and making them available to multiple users, reducing the need for users to construct their own complex queries. Views can join data from multiple business tables or other views, and can include aggregation functions to summarize data. Views that return only necessary columns or aggregated data can reduce the volume of data transferred from the database to the client, which can improve performance.
You can grant users access rights (such as SELECT or INSERT) to views in the same way as to tables. Therefore, you can use views to control access to rows or columns in business tables.
This topic covers how views are stored in DB2 databases, how to create and query a view, how to register the view with ArcSDE, and word of caution about using SQL to replace views that were originally created using the sdetable command.
Querying SYSCAT.VIEWS for view information
The command sdetable –o create_view creates a view that is defined in the system catalog view of your DB2 database. You can query SYSCAT.VIEWS to see how your views are stored. SYSCAT.VIEWS contains one or more rows for each view created.
SYSCAT.VIEWS catalog view| Field name |
Data type |
Description |
|---|
| VIEWSCHEMA |
VARCHAR(128) |
Along with VIEWNAME, the qualified name of a view or table that is used to define a materialized query table or staging table |
| VIEWNAME |
VARCHAR(128) |
Along with VIEWSCHEMA, the qualified name of a view or table that is used to define a materialized query table or staging table |
| DEFINER |
VARCHAR(128) |
Authorization ID of the creator of the view |
| SEQNO |
SMALLINT |
Always 1 |
| VIEWCHECK |
CHAR(1) |
The type of view checking
- N = No check option
- L = Local check option
- C = Cascaded check option
|
| READONLY |
CHAR(1) |
Y = Read-only view because of its definition
or
N = Not read-only view |
| VALID |
CHAR(1) |
Y = Valid view or materialized query table definition valid
or
X = Inoperative view or materialized query table definition; must be re-created |
| QUALIFIER |
VARCHAR(128) |
Contains value of the default schema at the time of object definition |
| FUNC_PATH |
VARCHAR(254) |
The SQL path of the view creator at the time the view was defined
When the view is used in data manipulation statements, this path must be used to resolve function calls in the view. (Use SYSIBM for views created before version 2.) |
| TEXT |
CLOB(64K) |
Text of the CREATE VIEW statement |
Creating a view: an example
The following is an example of how a view created by sdetable looks in DB2:
Suppose you want the manager of Department 101 to see all the columns in the table EMPLOYEES but only the rows for employees in Department 101. This is how the EMPLOYEES table is created:
db2 => create table employees(objectid integer, name
varchar(32),department smallint not null, hire_date date not null)
DB20000I The SQL command completed successfully.
Use sdetable to create a view with all rows in which the department number is 101. The –w option specifies the WHERE clause of the query stored with the view definition.
sdetable –o create_view –T view_dept_101 –t employees
–c objectid,name,department,hire_date -w "where department = 101"
–u gdb –p gdb –i 5910
ArcSDE DB2 Build. . .
Attribute Administration Utility
__________________________________
Successfully created view view_dept_101.
The sdetable command defines the view in the DB2 system catalog view, just as if you created the view yourself using the DB2 CREATE VIEW command.
db2 => select viewschema, viewname, text from syscat.views where viewname = 'VIEW_DEPT_101';
VIEWSCHEMA
|
VIEWNAME
|
TEXT
|
|---|
WORLD
|
VIEW_DEPT_101
|
CREATE VIEW view_dept_101
AS SELECT = objectid, name, department, hire_date
FROM WORLD.EMPLOYEES
WHERE department = 101
|
ArcSDE adds the owner name to the table name in the query. If you create your own view with SQL, this is not done for you automatically.
Notice that the names of the view and its owner are converted to uppercase before being stored in SYSCAT.VIEWS. This is done by DB2, not ArcSDE.
Querying views
Using this type of view, users can be granted no access to the base table (EMPLOYEES) and SELECT access to the view, VIEW_DEPT_101, that contains only the rows of EMPLOYEES where DEPARTMENT is 101:
db2 => connect to testdb user world using world
Database Connection Information
Database server = DB2/NT 8.2.2
SQL authorization ID = WORLD
Local database alias = TESTDB
db2 => grant select on view_dept_101 to mgr100
DB20000I The SQL command completed successfully.
db2 => connect to testdb user mgr100 using mgr100
Database Connection Information
Database server = DB2/NT 8.2.2
SQL authorization ID = MGR100
Local database alias = TESTDB
db2 => select * from world.view_dept_101
no record(s) selected.
db2 => select * from world.employees
SQL0551N "MGR100" does not have the privilege to perform operation "SELECT"
on object "WORLD.EMPLOYEES". SQLSTATE=42501
The first query found the view, but because there are no rows in EMPLOYEES, no rows have been selected. Views exist as schema objects, independent of the data that populates them. In the second query, the user MGR100 has no access to the table EMPLOYEES, so it appears to MGR100 that the table EMPLOYEES does not exist.
Creating a view with SQL and registering with ArcSDE
You can use the sdetable –o create_view command to create and register a view, but using SQL to create the view and manually registering the view with ArcSDE can achieve the same results.
Below, a view is created for Department 201. The owner name is included in the table definition so it can be found if other users access this view.
db2 => create view view_dept_201 as select objectid, name, department, hire_date
FROM GDB.EMPLOYEES
WHERE department = 201
DB20000I The SQL command completed successfully.
db2 => select viewschema, viewname, text from syscat.views where viewname = 'VIEW_DEPT_201'
VIEWSCHEMA
|
VIEWNAME
|
TEXT
|
|---|
WORLD
|
VIEW_DEPT_201
|
CREATE VIEW view_dept_201
AS SELECT objectid, name, department, hire_date
FROM GDB.EMPLOYEES
WHERE department = 201
|
Now, if a column is added to the base table, the view's schema is not affected, as shown below:
db2 => alter table EMPLOYEES add column address varchar(30)
DB20000I The SQL command completed successfully.
db2 => describe select * from employees
SQLDA Information
sqldaid : SQLDA sqldabc: 896 sqln: 20 sqld: 5
Column Information
sqltype
|
sqllen
|
sqlname.data
|
sqlname.length
|
|---|
497 INTEGER
|
4
|
OBJECTID
|
9
|
449 VARCHAR
|
32
|
NAME
|
4
|
500 SMALLINT
|
2
|
DEPARTMENT
|
11
|
384 DATE
|
10
|
HIRE_DATE
|
9
|
449 VARCHAR
|
30
|
ADDRESS
|
7
|
db2 => select viewschema, viewname, text from syscat.views where viewname = 'VIEW_DEPT_201'
VIEWSCHEMA
|
VIEWNAME
|
TEXT
|
|---|
WORLD
|
VIEW_DEPT_2001
|
CREATE VIEW view_dept_2001 AS SELECT objectid, name, department, hire_date
FROM GDB.EMPLOYEES
WHERE department = 201
|
The view is unchanged by this alteration of its base table.
The base table of a view need not be registered with ArcSDE unless it provides a geometry (vector), raster, or XML column managed by ArcSDE. In those cases, the base table is already registered with ArcSDE, but the view itself must be registered separately.
Registered row ID columns are unique, nonnull integer columns used by ArcSDE to uniquely identify rows in a table or view. Although you can create a view without registering a row ID column, such a view has limited functionality. A registered row ID column is required for queries and other operations that use ArcSDE log files. ArcGIS also uses the registered row ID column for selections, queries, and other operations. If you use a table or view with ArcGIS that has no registered row ID column, ArcGIS may create a temporary row ID column for use with the table or view. This operation can slow down the process of connecting to a table or view, especially if it is large.
You should register row ID columns for your views as user-maintained row ID columns. Generally, ArcSDE does not support editing through views. Therefore, there is no need for ArcSDE to create a DB2 sequence to generate new row IDs for the view, which is what ArcSDE does for row ID columns maintained by ArcSDE.
Replacing ArcSDE views using SQL
Some users create registered views with sdetable –o create_view and alter the view definition using SQL. This is done to avoid complex sdetable –o create_view command line syntax (especially the –c option) or to include a more complex or extended view definition, such as one that uses connections to external databases. Although altering a view can solve some problems, it is also possible to create a view that does not work well with ArcSDE.
When you create a view using ArcSDE tools or by other means, you must consider the appropriateness of the new view. You must determine, for example, if the specified columns actually exist in the specified tables and if there are indexes on the columns to speed query execution. If you choose to replace views created by ArcSDE by altering the view afterward, you are taking responsibility for not only the query's correctness and efficiency but also its relevance and suitability for use with ArcSDE clients.
