Example: Creating a database view in DB2 using SQL and registering it with ArcSDE
You can use SQL to create a view in an ArcSDE geodatabase. To query the view with ArcGIS, you must register it with ArcSDE.
To register the view with ArcSDE, the underlying table from which the view is created must contain a qualifying row ID column. Row IDs 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 in ArcGIS because a registered row ID column is required for selections and queries in ArcGIS.
The example in this topic shows how a view created in IBM DB2 using SQL can be registered with ArcSDE. The example is based on a table with the following definition:
CREATE TABLE employees(emp_id integer not null, name varchar(32), department smallint not null, hire_date date not null);
Notice that the table contains a not-null integer column that can be used as an ArcSDE row ID (emp_id). This column must be included in the view definition to allow you to register it with ArcSDE.
Grant privileges on the table
If the user creating the view is not the owner of the table or tables on which the view is based, the table owner must grant the view creator at least the privilege to select from the table. If the view owner needs to be able to grant privileges on the view to other users, the table owner must grant the view owner the ability to grant privileges to other users.
In this example, the table on which the view is based (employees) is owned by the user gdb. The user creating the view is user rocket. Additionally, the rocket user must grant privileges on the view to other users. Therefore, the gdb user must grant the rocket user the privilege to select from the employees table and include the WITH GRANT OPTION so rocket can grant other users select privilege on the view.
db2 => connect to testdb user gdb using gdb.dbg Database Connection Information Database server = DB2 9.5.5 SQL authorization ID = GDB Local database alias = TESTDB db2 => GRANT SELECT ON employees TO USER rocket WITH GRANT OPTION; DB20000I The SQL command completed successfully.
Create a view
In this example, user rocket creates a view on the employees table to restrict access to only those records where the department is 201.
db2 => connect to testdb user rocket using nopeeking Database Connection Information Database server = DB2 9.5.5 SQL authorization ID = ROCKET Local database alias = TESTDB db2 => CREATE VIEW view_dept_201 AS SELECT emp_id, name, department, hire_date FROM gdb.employees WHERE department = 201; DB20000I The SQL command completed successfully.
Grant privileges on the view
You can grant privileges on the view to specific users without having to grant those users access to the base table (employees). In this example, the user mgr200 is granted select privileges on the view, view_dept_201:
db2 => connect to testdb user rocket using nopeeking Database Connection Information Database server = DB2 9.5.5 SQL authorization ID = ROCKET Local database alias = TESTDB db2 => GRANT SELECT ON view_dept_201 TO USER mgr200; DB20000I The SQL command completed successfully.
Log in as mgr200 and select records from view_dept_201.
db2 => connect to testdb user mgr200 using mgr200 Database Connection Information Database server = DB2 9.5.5 SQL authorization ID = MGR200 Local database alias = TESTDB db2 => SELECT * FROM rocket.view_dept_201; emp_id name dept hire_date 112 LOLLI POP 201 06/30/2007 134 VAN CHIN 201 10/15/2007 150 DON GUN 201 03/01/2009
As expected, only records for employees in department 201 are returned.
Register the view with ArcSDE
To query or select records in a view from an ArcGIS client application, you must register the view with ArcSDE. Registering the view with ArcSDE adds a record to the ArcSDE TABLE_REGISTRY system table.
Use the sdetable command with the register operation to register the view. Only the owner of view can register it with ArcSDE. Therefore, the rocket user name and password must be provided with the sdetable command when registering view_dept_201.
sdetable -o register -t rocket.view_dept_201 -c emp_id -C SDE -i sde:dbe -s mydb2server -D testdb -u rocket -p nopeeking
In this example, the row ID (emp_id) is registered as maintained by ArcSDE. If your view is read-only, you can register the row ID as user maintained.
For more information on the sdetable command, see the Administration Command Reference.