Example: Creating a spatial view in DB2 using SQL and registering it
You can use SQL to create a spatial view in an ArcSDE geodatabase by including the spatial column in the view definition. When you use SQL to create the spatial view, you must register it with ArcSDE to query the spatial view with ArcGIS and view it as a feature class.
In this example, a spatial view is created between the employees table and region feature class.
Create a view with a spatial column
Define the view to include both the spatial column and the object ID from the feature class along with the other attribute columns you want in the view.
The owner of the employees table and regions feature class is the gdb user; therefore, the user already has the necessary privileges to create 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 CREATE VIEW emp_region_vw AS SELECT (e.emp_name,e.emp_id,r.rname,r.reg_id,r.region) FROM employees e, region r WHERE e.emp_id = r.emp_id;
The reg_id is the not null, integer column from the regions feature class that will be used as the row ID when you register the view with ArcSDE. Region is the spatial column from the regions feature class.
Grant privileges on the spatial view
Now that the view is created, grant select privileges to the dispatch1 user. Include WITH GRANT OPTION to allow dispatch1 to grant privileges on the view to other users.
GRANT SELECT ON emp_region_vw TO USER dispatch1 WITH GRANT OPTION;
Register the view with ArcSDE as a layer
To query the spatial view with ArcGIS, you must register the view as a layer with ArcSDE. Doing so adds records to the ArcSDE LAYERS and GEOMETRY_COLUMNS system tables.
The layer must be registered by the owner of the view; therefore, the gdb user name and password must be supplied with the –u and –p options.
sdelayer -o register -l emp_region_vw,region -e a -t ST_GEOMETRY -C reg_id,USER -E 2.20,2.20,3.01,3.01 -R 1 -s mydb2server -i sde:db2 -D testdb -u gdb -p gdb.bdg
The values you provide for the options, except –o, will vary depending on your data.
For more information on the sdelayer command, see the Administration Command Reference.