ArcGIS Server Banner

Views in DB2

Views in DB2

Release 9.3 E-mail This TopicPrintable VersionGive Us feedback
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.

See Also

  • Using database views