Using multiversioned views

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 delta table. 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

Top of page

Creating multiversioned views

The following provides some information on view behavior specific to particular DBMSs used to store ArcSDE geodatabases.

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

  1. Set the version.
  2. exec sde.set_current_version('MAP.SQL_EDITS') 

  3. Start the edit session.
  4. exec sde.edit_version('MAP.SQL_EDITS',1)

  5. Perform the desired edit. The following example sets the location of a polygon feature that has a name attribute of Summerhill Elementary School:
  6. 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;

  7. Stop the edit session.
  8. exec sde.edit_version('MAP.SQL_EDITS',2)

  9. Commit the edit.
  10. COMMIT

Example 2

  1. Set the version.
  2. exec dbo.set_current_version [dbo.Editor]

  3. Start the edit session.
  4. exec dbo.edit_version [dbo.Editor], 1

  5. Perform edits. Here, the owner name for parcel 554679 is being set to Clarke.
  6. update mv_parcels set owner_lname = 'Clarke' where parcel_id = 554679

  7. Stop the edit session.
  8. exec dbo.edit_version [dbo.Editor], 2

  9. Commit changes.
  10. COMMIT

Top of page