This topic contains the following information. Click the links to navigate to each section.
Multiversioned views are designed primarily to access attribute columns of a table rather than spatial columns, though it is possible to access the spatial column. Accessing the spatial column with a multiversioned view is more straight forward if you are using a spatial type to store your geometries; it is more involved if you are using a binary geometry storage type.
Multiversioned views are intended for use with client applications that do not support ArcSDE geodatabase versioning. Applications that don't support ArcSDE geodatabase versioning can only query base tables and have no knowledge of the
. Therefore, to allow these applications to access versioned tables in the geodatabase using SQL, you can create multiversioned views.
Multiversioned views access a specified version of data in a single geodatabase table—the base table—which must be registered as versioned. All the records in the base table are selected and merged with records from the delta tables to construct a view that includes all the changes made to the base table in the context of the specified version.
Multiversioned views appear to have the same columns and rows as the table they represent. They are not used to change the table's schema or limit access to it but to facilitate access to a certain version of the table.
You use the administration command sdetable –o create_mv_view to create a multiversioned view on a base table that has already been registered as versioned through ArcGIS.
For details on the use of sdetable and other administration commands, consult the Administration Command Reference that is provided with the software. You can also download a PDF version of the file from the ESRI support site. (Login required)
http://support.esri.com/index.cfm?fa=knowledgebase.documentation.listDocs&PID=19
NOTE: You cannot create a multiversioned view on a view.
After creation, the multiversioned view can be used by SQL applications like any other view. These client applications can select the version to be accessed using stored procedures provided with ArcSDE.
To remove a multiversioned view, you use sdetable –o delete_mv_view. For complete syntax, explanation, and examples for these commands, see the Administration Command Reference provided with ArcGIS Server Enterprise.
It is possible to edit a versioned table using multiversioned views (except in geodatabases in Informix databases). This is an advanced use of views, is DBMS specific, and has the potential to corrupt your geodatabase if not done properly. For instructions on how to set up this type of editing, see the DBMS-specific view information section below.
Things to keep in mind about multiversioned views
- Unlike a standard or spatial view, you do not choose columns or define a WHERE clause to create a multiversioned view.
- Only one multiversioned view can exist per feature class.
- You should perform all version management operations, such as creating versions, resolving conflicts, reconciling, and posting, using ArcGIS. Improper version management can lead to geodatabase corruption when working with views.
- Multiversioned views should not be used to access or modify complex features—features that participate in geometric networks, topologies, or relationships or have specific geodatabase behavior. You should use ArcGIS to modify these types of features.
- Don't use a multiversioned view to edit the DEFAULT or any other version that will be subject to other edits and reconcile operations while you're editing with the multiversioned view.
- Never use DBMS tools to update any row ID (ObjectID) field maintained by ArcSDE. These row ID fields are allocated and managed by the geodatabase and should not be altered.
Top of page
Creating multiversioned views
The following provides some information on view behavior specific to particular DBMSs used to store ArcSDE geodatabases.
- SQL Server
Multiversioned views are implemented through INSTEAD OF triggers on a view, stored procedures, and user-defined functions.
Use the sdetable –o create_mv_view command to create a multiversioned view. You do not choose columns or define a WHERE clause when using the sdetable –o create_mv_view command as you do when you create ArcSDE views using sdetable –o create_view. The schema of a multiversioned view is identical to that of the business table on which it is based.
Only one multiversioned view can exist per feature class.
This example creates a multiversioned view of the parcels feature class.
sdetable -o create_mv_view -T mv_parcels -t parcels -D landbase
A multiversioned view is a single SQL Server view with three INSTEAD OF triggers for handling versioned edits. Users issue INSERT, UPDATE, and DELETE statements directly against the view. The INSTEAD OF triggers redirect the edits to the appropriate adds (A) and deletes (D) tables of the base table on which the view is built.
Multiversioned views automatically access the default version. Before issuing any queries against the view, you must ensure that they will take place against the correct version. To access a specific version, execute the set_current_version stored procedure. This stored procedure can be executed directly from a SQL client such as the Query Analyzer. It has the following syntax:
sde.set_current_version @version_name
This example accesses the dbo.Assessor version:
exec sde.set_current_version 'dbo.Assessor'
This example selects data from the dbo.Assessor version of the mv_parcels view:
Exec sde.set_current_version 'dbo.Assessor'
Select Parcel_id, address from mv_parcels where owner_lname like 'Gates%'
Once the view has been created, it can be queried the same way as any other SQL Server table or view.
Top of page
Oracle
Multiversioned views incorporate Oracle views, stored procedures, and triggers to access a specified version of data in a geodatabase table. Multiversioned views appear to have the same columns and rows as the table they represent. They are not used to change the table's schema or limit access to it but to facilitate access to a certain version of the table.
To create a multiversioned view from the command line, use the command sdetable –o create_mv_view. Multiversioned applies to a single specified table, which must be registered as versioned. Use ArcGIS to register a table as versioned and create new versions.
NOTE: You cannot create a multiversioned view on a view.
After creation, the multiversioned view can be used by SQL applications like any other view. These client applications can select the version to be accessed using the stored procedures is provided with ArcSDE. When an application opens an Oracle connection to the geodatabase, it needs to execute the stored procedure set_current_version('<desired_version_name>') to validate the version name and set the database state internally. If no version name is provided, the version SDE.DEFAULT is assumed. After selecting a version, the application can use SQL to access the attribute data. If multiple multiversioned views are accessed by the application, all views will be accessed using the specified version.
The procedure set_current_version can be called to change the version as desired. It should be called to refresh the current representation of the selected version when data in the version has been changed.
Top of page
DB2
Multiversioned views incorporate DB2 views, stored procedures, and triggers to access a specified version of data in a geodatabase table. Multiversioned views appear to have the same columns and rows as the table they represent. They are not used to change the table's schema or limit access to it but rather to facilitate access to a certain version of the table.
To create a multiversioned view, use the command sdetable –o create_mv_view. Multiversioned applies to a single specified table, which must be versioned. Use ArcGIS register a table as versioned to create new versions.
NOTE: You cannot create a multiversioned view on a view.
After creation, the multiversioned view can be used by SQL applications like any other view. These client applications can select the version to be accessed using stored procedures installed with ArcSDE. When an application opens a DB2 connection to the geodatabase, it should execute the stored procedure sde.setcurrentversion('<desired_version_name>',?). The procedure validates the version name and sets the database state internally. After selecting a version, the application can use SQL to access the attribute data. If multiple multiversioned views are accessed by the application, all views will be accessed using the specified version.
The procedure sde.setcurrent_version can be called to change the version as desired. It should be called to refresh the current representation of the selected version when data in the version has been changed.
Top of page
Informix
Multiversioned views are a combination of Informix views, stored procedures, and triggers on the underlying versioned table schema (the business and delta tables). These types of views are read only in Informix databases.
A multiversioned view applies to a single multiversioned table or feature class. The schema of a multiversioned view is identical to that of the base table on which it is created.
To create a multiversioned view, use the sdetable –o create_mv_view command. Unlike when you issue the sdetable –o create_view command to create a regular ArcSDE view, you do not choose columns or define a where clause when executing sdetable –o create_mv_view.
Once the multiversioned view has been created, the client application will work with the view as it would work with any other Informix view. When an application connects to the database, access to specific versions is supported by stored procedures in the Informix database, created when ArcSDE is installed. The required version is set by calling the application using the ArcSDE stored procedure, set_current_version ('<required version>').
sde.set_current_version @version_name
This procedure validates the supplied version name and sets the corresponding database state internally; if no version name is supplied, the DEFAULT version is used. This procedure may also be called again to change other versions as required, and it is called each time the workspace is refreshed to return the current state of the versioned table to the calling application.
With the connection to the required version established via the multiversioned view, SQL SELECT queries can be executed against the database. The view guarantees that all the rows from the base and delta tables from a table or feature class in a particular version of the geodatabase will be returned transparently to the end user.
Top of page
Editing with multiversioned views
You can edit versioned tables through multiversioned views in Oracle, SQL Server, and DB2 databases. Changes will be made to the delta tables, and row ID values for new records will be automatically generated. However, unlike editing versioned data in an ArcGIS edit session, no internal version reconciliation is done with these edits. Therefore, it is strongly recommended that multiversioned views not be used to edit the DEFAULT version or any version that may be subject to simultaneous editing or reconciliation by other users because conflicts will not be detected. Instead, create your own version specifically for editing with multiversioned views.
To edit using a multiversioned view, first use a stored procedure to select the version you want to edit.
SQL Server (sde-schema geodatabase):
exec sde.set_current_version ('<version_name>')
SQL Server (dbo-schema geodatabase):
exec dbo.set_current_version ('<version_name>')
DB2:
call setcurrentversion('<version_name>')
Oracle:
exec sde.set_current_version('<version_name>')
Oracle (user-schema geodatabase):
exec <user_schema_name>.set_current_version('<version_name>')
Then, execute the stored procedure edit_version with the version name and an edit value of 1 to begin editing.
SQL Server (sde-schema):
exec sde.edit_version ('<version_name>',1)
SQL Server (dbo-schema):
exec dbo.edit_version ('<version_name>',1)
DB2:
call sde.edit_version('<version_name>',1)
Oracle:
exec sde.edit_version('<version_name>',1)
Oracle (user-schema geodatabase):
exec <user_schema_name>.edit_version('<version_name>',1)
When you have finished editing, use edit_version with the version name and an edit value of 2 to stop editing.
SQL Server (sde-schema):
exec sde.edit_version ('<version_name>',2)
SQL Server (dbo-schema):
exec dbo.edit_version ('<version_name>',2)
DB2:
call sde.edit_version('<version_name>',2)
Oracle:
exec sde.edit_version('<version_name>',2)
Oracle (user-schema geodatabase):
exec <user_schema_name>.edit_version('<version_name>',2)
After executing edit commands, issue a COMMIT or ROLLBACK statement to be sure that changes to the database are applied or rolled back as required.
Following are examples of starting an edit session, performing an edit, and stopping an edit session. The first example edits the MAP.SQL_EDITS version of a view called sensitive_areas in an Oracle geodatabase. The second example updates an attribute of a feature in the dbo.Editor version of a view called mv_parcels in a SQL Server dbo-schema geodatabase.
Example 1
- Set the version.
exec sde.set_current_version('MAP.SQL_EDITS')
- Start the edit session.
exec sde.edit_version('MAP.SQL_EDITS',1)
- Perform the desired edit. The following example sets the location of a polygon feature that has a name attribute of Summerhill Elementary School:
update sensitive_areas set SHAPE =
ST_PolyFromText('polygon ((52 27,59 28,59 23,52 23,52 27))',1))
where name = 'Summerhill Elementary School';
commit;
- Stop the edit session.
exec sde.edit_version('MAP.SQL_EDITS',2)
- Commit the edit.
COMMIT
Example 2
- Set the version.
exec dbo.set_current_version [dbo.Editor]
- Start the edit session.
exec dbo.edit_version [dbo.Editor], 1
- Perform edits. Here, the owner name for parcel 554679 is being set to Clarke.
update mv_parcels set owner_lname = 'Clarke' where parcel_id = 554679
- Stop the edit session.
exec dbo.edit_version [dbo.Editor], 2
- Commit changes.
COMMIT
Top of page
