ArcGIS Server Banner

Using multiversioned views

Using multiversioned views

Release 9.3 E-mail This TopicPrintable VersionGive Us feedback

This topic contains the following information:

What are multiversioned views?

Multiversioned views incorporate database views, stored procedures, triggers, and functions to access a specified version of data in a geodatabase table using Structured Query Language (SQL).

Multiversioned views access a specified version of data in the business table of a dataset. The dataset must be registered as versioned. Each versioned dataset has associated delta tables that record edits made to the dataset. When a versioned dataset is accessed by a multiversioned view, all the records in the business table are selected and merged with records from the delta tables to construct a view that includes all the changes made to the business table in the context of the specified version. Multiversioned views appear to have the same columns and rows as the business table they represent. Unlike database or spatial views, multiversioned views are used not to change the table's schema or limit access to it; rather, they are used to facilitate access to a certain version of the table.

Applications that do not support ArcSDE geodatabase versioning can only directly query the business table of a versioned dataset and have no knowledge of the delta tables. Using multiversioned views with these applications provides them with access to the data in the delta tables.

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 straightforward if you are using a spatial type, such as ST_Geometry or SDO_Geometry, to store your geometries; it is more involved if you are using a binary geometry storage type, such as the ArcSDE compressed binary or the Open Geospatial Consortium, Inc. (OGC), well-known binary types.

It is possible to edit a versioned table using multiversioned views. This is an advanced use of views, is database management system (DBMS) specific, and has the potential to corrupt your geodatabase if not done properly.

The general steps you take to edit with multiversioned views are as follows:

  1. Create a version in which to do your edits.
  2. Set the multiversioned view to use the new version.
  3. Start an edit session.
  4. Perform your edits through the multiversioned view.
  5. Stop the edit session.
  6. Commit your edits to the database or roll them back.
  7. If you reconcile and post your edits through ArcGIS, you can delete the version you created for your edits.

For instructions on how to set up this type of editing, see the "Editing with multiversioned views" sections for each DBMS.

Creating and deleting multiversioned views

Use the administration command sdetable –o create_mv_view to create a multiversioned view on a single business table that has already been registered as versioned through ArcGIS.

This example creates a multiversioned view of the parcels feature class.

sdetable –o create_mv_view –T mv_parcels –t parcels -i 5100 –D landbase –u bjorn –p a.secret

Notice that 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 standard or spatial views using sdetable –o create_view or SQL.

Also, be aware that only one multiversioned view can exist per feature class, and you cannot create a multiversioned view on a view.

To remove a multiversioned view, use the command sdetable –o delete_mv_view. For example, to delete the multiversioned view created in the last example, type the following:

sdetable –o delete_mv_view –t parcels -i 5100 –D landbase –u bjorn –p a.secret

For complete syntax, explanation, and examples of the sdetable –o create_mv_view and sdetable –o delete_mv_view commands, see the Administration Command Reference provided with the ArcSDE component of ArcGIS Server Enterprise.

Using multiversioned views in DB2

Multiversioned views can be used to read the data in a versioned dataset and also to edit the dataset.

Reading data with multiversioned views

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 other than the default, execute the ArcSDE setcurrentversion stored procedure. This procedure validates the supplied version name and sets the corresponding database state internally. It can be executed directly from a SQL client. The syntax for the stored procedure to set the current version is as follows:

CALL sde.setcurrentversion('<version_name>',?,?)

For example:

CALL sde.setcurrentversion('edits',?,?)

This procedure may be called again to change to 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.

NOTE: 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 view and modify these types of features.

Editing with multiversioned views

When you edit versioned tables through multiversioned views, changes are made to the delta tables, and row ID values for new records are 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.

NOTE: If you need to perform version management operations, such as reconciling, resolving conflicts, and posting, you need to use the ArcGIS software. Improper version management can lead to geodatabase corruption when working with views. Also note that you should never use DBMS tools to update any row ID (ObjectID) field maintained by ArcSDE in the database. These row ID fields are allocated and managed by the geodatabase and should not be altered.

There are several stored procedures installed with the ArcSDE component that help you work with multiversioned views. The stored procedure used to set which version the multiversioned view will work with was discussed in the previous section. There are also stored procedures to create a version in which you can do your editing, start and stop an edit session, and delete your edit version.

The following set of steps takes you through creating a multiversioned view, creating a version in which to perform edits, setting the version to edit, starting an edit session, performing some edits through the multiversioned view, stopping the edit session, committing your edits to the database, and deleting the version created for the edits. In the examples, the multiversioned view created to perform the edits on the table is permits_mv, and the version created and used for editing is mvedits.

Remember that the dataset you edit through a multiversioned view must already have been registered as versioned through ArcGIS. For this example, the permits feature class would need to have already been registered as versioned in ArcCatalog to be able to edit it through a multiversioned view.

  1. At a command prompt, execute the sdetable function to create a multiversioned view.
  2. sdetable –o create_mv_view –T permits_mv –t permits –i 4400 –D code –u sarja –p not4u

  3. Create a new version in which to perform your edits.
  4. CALL sde.create_version
    ('<parent_version>', '<child_version>', <name_rule>, <access>, '<description>',
    <message_code_output>, <message_output>)

    where

    <parent_version> is the version from which your version is created.

    <child_version> is the name for the version you are creating.

    <name_rule> indicates whether the name specified for the version should be taken as given (2) or a unique name should be created when a duplicate name is specified (1).

    <access> is the permission level for the version, either 0 for Private, 1 for Public, or 2 for Protected.

    <description> is a text description of the child version.

    <message_code_output> indicates you want the SQL code returned to you.

    <message_output> indicates you want the SQL messages returned to you.

    For example:

    CALL sde.create_version
    ('sde.DEFAULT', 'mvedits', 1, 1, 'multiversioned view edit version', ?, ?)

    NOTE: The message_code_output and message_output are the SQL codes and messages returned after you execute the function. You pass in question marks, and the code and message are returned to you.

  5. Set the version for the edit session to the child version you just created.
  6. CALL sde.setcurrentversion('<version_name>', <message_code_output>, <message_output>)

    For example:

    CALL sde.setcurrentversion('mvedits', ?, ?)

  7. Start an edit session by calling the edit_version stored procedure and specifying "1". The 1 indicates an edit session should be started.
  8. CALL sde.edit_version('<version_name>', 1, <message_code_output>, <message_output>)

    For example:

    CALL sde.edit_version('mvedits', 1, ?, ?)

  9. Perform the necessary edits to the multiversioned view using SQL.
  10. In this example, an existing record is updated and a new record is inserted.

    UPDATE permits_mv
    SET permittee_name = 'Cpt. Industry' 
    WHERE business_name = 'Body Electric'

    INSERT INTO permits_mv
    (bid, business_name, business_type, permittee_name) 
    VALUES (117, 
    'Bionix', 
    'engineering', 
    'Anjo Badsu')

  11. Stop the edit session by calling the edit_version stored procedure, but this time, specify "2". The 2 indicates the edit session should be closed.
  12. CALL sde.edit_version('<version_name>', 2, <message_code_output>, <message_output>)

    For example:

    CALL sde.edit_version('mvedits', 2, ?, ?)

  13. Execute a COMMIT or ROLLBACK statement.
  14. In this example, the edits are committed to the database.

    COMMIT

After the changes have been reconciled and posted through ArcGIS Desktop, or if you decide you don't want the changes, you can delete the version you created in step 1 by calling the delete_version stored procedure.

CALL sde.delete_version('<version_name>', <message_code_output>, <message_output>)

To delete the version created in step 1, execute the following statement:

CALL sde.delete_version('mvedits', ?,?)

Using multiversioned views in Informix

Multiversioned views can be used to read the data in a versioned dataset and also to edit the dataset.

Reading data with multiversioned views

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 other than the default, execute the ArcSDE set_current_version stored procedure. This procedure validates the supplied version name and sets the corresponding database state internally. It can be executed directly from a SQL client. The syntax for the stored procedure to set the current version is as follows:

EXECUTE FUNCTION sde.set_current_version('<version_name>')

For example:

EXECUTE FUNCTION sde.set_current_version('edits')

This procedure may be called again to change to 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.

NOTE: 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 view and modify these types of features.

Editing with multiversioned views

When you edit versioned tables through multiversioned views, changes are made to the delta tables, and row ID values for new records are 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.

NOTE: If you need to perform version management operations, such as reconciling, resolving conflicts, and posting, you need to use the ArcGIS software. Improper version management can lead to geodatabase corruption when working with views. Also note that you should never use DBMS tools to update any row ID (ObjectID) field maintained by ArcSDE in the database. These row ID fields are allocated and managed by the geodatabase and should not be altered.

There are several stored procedures installed with the ArcSDE component that help you work with multiversioned views. The stored procedure used to set which version the multiversioned view will work with was discussed in the previous section. There are also stored procedures to create a version in which you can do your editing, start and stop an edit session, and delete your edit version.

The following set of steps takes you through creating a multiversioned view, creating a version in which to perform edits, setting the version to edit, starting an edit session, performing some edits through the multiversioned view, stopping the edit session, committing your edits to the database, and deleting the version created for the edits. In the examples, the multiversioned view created to perform the edits on the business table is permits_mv; the version created and used for editing is mvedits; and the coordinate system is a geographic coordinate system, datum NAD27, using the Clarke 1866 spheroid and decimal degree units.

Remember that the dataset you edit through a multiversioned view must already have been registered as versioned through ArcGIS. For this example, the permits feature class would need to have already been registered as versioned in ArcCatalog to be able to edit it through a multiversioned view.

  1. At a command prompt, execute the sdetable function to create a multiversioned view.
  2. sdetable –o create_mv_view –T permits_mv –t permits –i 4400 –D code –u sarja –p not4u

  3. Create a new version in which to perform your edits.
  4. EXECUTE FUNCTION sde.create_version
    ('<parent_version>', '<child_version>', <name_rule>, <access>, '<description>')

    where

    <parent_version> is the version from which your version is created.

    <child_version> is the name for the version you are creating.

    <name_rule> indicates whether the name specified for the version should be taken as given (2) or a unique name should be created when a duplicate name is specified (1).

    <access> is the permission level for the version, either 0 for Private, 1 for Public, or 2 for Protected.

    <description> is a text description of the child version.

    For example:

    EXECUTE FUNCTION sde.create_version
    ('sde.DEFAULT', 'mvedits', 1, 1, 'multiversioned view edit version')

  5. Set the version for the edit session to the child version you just created.
  6. EXECUTE FUNCTION set_current_version('<version_name>')

    For example:

    EXECUTE FUNCTION set_current_version('mvedits')

  7. Start an edit session by executing the edit_version function and specifying "1". The 1 indicates an edit session should be started.
  8. EXECUTE FUNCTION sde.edit_version('<version_name>',1)

    For example:

    EXECUTE FUNCTION sde.edit_version('mvedits',1)

  9. Perform the necessary edits to the multiversioned view using SQL.
  10. In this example, an existing record is updated and a new record is inserted.

    UPDATE businesses_mvw 
    SET name = 'Cpt. Industry' 
    WHERE business_name = 'Body Electric'

    INSERT INTO businesses_mvw 
    (bid, business_name, business_type, owner_name, location) 
    VALUES (117, 
    'Bionix', 
    'engineering', 
    'Anjo Badsu', 
    ST_PointFromText('point (40 40))', 12)

  11. Stop the edit session by executing the edit_version function, but this time, specify "2". The 2 indicates the edit session should be closed.
  12. EXECUTE FUNCTION sde.edit_version('<version_name>',2)

    For example:

    EXECUTE FUNCTION sde.edit_version('mvedits',2)

  13. Execute a COMMIT or ROLLBACK statement.
  14. In this example, the edits are committed to the database.

    COMMIT WORK

After the changes have been reconciled and posted through ArcGIS Desktop, or if you decide you don't want the changes, you can delete the version you created in step 1 by executing the delete_version function.

EXECUTE FUNCTION sde.delete_version('<version_name>')

To delete the version created in step 1, execute the following statement:

EXECUTE FUNCTION sde.delete_version('mvedits')

Using multiversioned views in Oracle

Multiversioned views can be used to read the data in a versioned dataset and also to edit the dataset.

Reading data with multiversioned views

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 other than the default, execute the ArcSDE set_current_version stored procedure. This procedure validates the supplied version name and sets the corresponding database state internally. It can be executed directly from a SQL client. The syntax for the stored procedure to set the current version is as follows:

EXEC sde.version_util.set_current_version('<version_name>')

For example:

EXEC sde.version_util.set_current_version('edits')

For Oracle user-schema geodatabases

EXEC <user_schema_name>.version_user.set_current_version('<version_name>')

For example:

EXEC usertwo.version_user.set_current_version('edits')

This procedure may be called again to change to 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.

NOTE: 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 view and modify these types of features.

Editing with multiversioned views

When you edit versioned tables through multiversioned views, changes are made to the delta tables, and row ID values for new records are 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.

NOTE: If you need to perform version management operations, such as reconciling, resolving conflicts, and posting, you need to use the ArcGIS software. Improper version management can lead to geodatabase corruption when working with views. Also note that you should never use DBMS tools to update any row ID (ObjectID) field maintained by ArcSDE in the database. These row ID fields are allocated and managed by the geodatabase and should not be altered.

There are several stored procedures installed with the ArcSDE component that help you work with multiversioned views. The stored procedure used to set which version the multiversioned view will work with was discussed in the previous section. There are also stored procedures to create a version in which you can do your editing, start and stop an edit session, and delete your edit version.

The following set of steps takes you through creating a multiversioned view, creating a version in which to perform edits, setting the version to edit, starting an edit session, performing some edits through the multiversioned view, stopping the edit session, committing your edits to the database, and deleting the version created for the edits. In the examples, the multiversioned view created to perform the edits on the business table is permits_mv; the version created and used for editing is mvedits; and the coordinate system is a geographic coordinate system, datum NAD27, using the Clarke 1866 spheroid and decimal degree units.

Remember that the dataset you edit through a multiversioned view must already have been registered as versioned through ArcGIS. For this example, the permits feature class would need to have already been registered as versioned in ArcCatalog to be able to edit it through a multiversioned view.

These edits are performed in the master SDE geodatabase (as opposed to being performed in a geodatabase in a user's schema).

  1. At a command prompt, execute the sdetable function to create a multiversioned view.
  2. sdetable –o create_mv_view –T permits_mv –t permits –i 4400 –u sarja –p not4u

  3. Declare a variable to store the version you will create in the next step.
  4. VARIABLE <variable_name> <string_data_type>;
    EXEC :<variable_name> := '<version_name>';

    For example:

    VARIABLE mv_version NVARCHAR2(10);
    EXEC :mv_version := 'mvedits';

  5. Create a new version in which to perform your edits.
  6. EXEC sde.version_user_ddl.create_version
    ('<parent_version>', :<child_version_variable>, <name_rule>, <access>, '<description>')

    For example:

    EXEC sde.version_user_ddl.create_version
    ('sde.DEFAULT', :mv_version, sde.version_util.C_take_name_as_given, sde.version_util.C_version_private, 'multiversioned view edit version');

  7. Set the version for the edit session to the child version you just created.
  8. EXEC sde.version_util.set_current_version('<version_name>')

    For example:

    EXEC sde.version_util.set_current_version('mvedits');

  9. Start an edit session by executing the version_user_ddl.edit_version stored procedure and specifying "1". The 1 indicates an edit session should be started.
  10. EXEC sde.version_user_ddl.edit_version('<version_name>',1)

    For example:

    EXEC sde.version_user_ddl.edit_version('mvedits',1);

  11. Perform the necessary edits to the multiversioned view using SQL.
  12. In this example, an existing record is updated and a new record is inserted.

    UPDATE businesses_mvw 
    SET owner_name = 'Cpt. Industry' 
    WHERE business_name = 'Body Electric';

    INSERT INTO businesses_mvw
    (bid, business_name, business_type, owner_name, location) 
    VALUES (117, 
    'Bionix', 
    'engineering', 
    'Anjo Badsu', 
    sde.ST_PointFromText('point (40 40))', 12);

  13. Stop the edit session by executing the version_user_ddl.edit_version stored procedure, but this time, specify "2". The 2 indicates the edit session should be closed.
  14. EXEC sde.version_user_ddl.edit_version('<version_name>',2)

    For example:

    EXEC sde.version_user_ddl.edit_version('mvedits',2);

  15. Execute a COMMIT or ROLLBACK statement.
  16. In this example, the edits are committed to the database.

    COMMIT

After the changes have been reconciled and posted through ArcGIS Desktop, or if you decide you don't want the changes, you can delete the version you created in step 1 by executing the delete_version function.

EXEC sde.version_user_ddl.delete_version('<version_name>')

To delete the version created in step 1, execute the following statement:

EXEC sde.version_user_ddl.delete_version('mv_version');

Using multiversioned views in PostgreSQL

Multiversioned views can be used to read the data in a versioned dataset and also to edit the dataset.

Reading data with multiversioned views

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 other than the default, execute the ArcSDE sde_set_current_version function. This procedure validates the supplied version name and sets the corresponding database state internally. It can be executed directly from a SQL client. The syntax for the function to set the current version is as follows:

SELECT sde.sde_set_current_version('<version_name>')

For example:

SELECT sde.sde_set_current_version('edits')

This procedure may be called again to change to 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.

NOTE: 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 view and modify these types of features.

Editing with multiversioned views

When you edit versioned tables through multiversioned views, changes are made to the delta tables, and row ID values for new records are 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.

NOTE: If you need to perform version management operations, such as reconciling, resolving conflicts, and posting, you need to use the ArcGIS software. Improper version management can lead to geodatabase corruption when working with views. Also note that you should never use DBMS tools to update any row ID (ObjectID) field maintained by ArcSDE in the database. These row ID fields are allocated and managed by the geodatabase and should not be altered.

There are several functions installed with the ArcSDE component that help you work with multiversioned views. The function used to set which version the multiversioned view will work with was discussed in the previous section. There are also functions to create a version in which you can do your editing, start and stop an edit session, and delete your edit version.

The following set of steps takes you through creating a multiversioned view, creating a version in which to perform edits, setting the version to edit, starting an edit session, performing some edits through the multiversioned view, stopping the edit session, committing your edits to the database, and deleting the version created for the edits. In the examples, the multiversioned view created to perform the edits on the business table is permits_mv; the version created and used for editing is mvedits; and the coordinate system is a geographic coordinate system, datum NAD27, using the Clarke 1866 spheroid and decimal degree units.

Remember that the dataset you edit through a multiversioned view must already have been registered as versioned through ArcGIS. For this example, the permits feature class would need to have already been registered as versioned in ArcCatalog to be able to edit it through a multiversioned view.

  1. At a command prompt, execute the sdetable function to create a multiversioned view.
  2. sdetable –o create_mv_view –T permits_mv –t permits –i 4400 –D code –u sarja –p not4u

  3. Log in to psql and create a new version in which to perform your edits.
  4. SELECT sde.sde_create_version
    ('<parent_name>',  '<version_name>', <rule_code>, <access_code>, '<description>')

    where

    <parent_name> is the name of the version from which your version is created.

    <version_name> is the name for the version you are creating.

    <rule_code> indicates whether the name specified for the version should be taken as given (2) or a unique name should be created when a duplicate name is specified (1).

    <access_code> is the permission level for the version, either 0 for Private, 1 for Public, or 2 for Protected.

    <description> is a text description of the child version.

    For example:

    SELECT sde.sde_create_version('DEFAULT', 'mvedits', 2, 1, 'version for edits');

  5. Set the version for the edit session to the child version you just created.
  6. SELECT sde.sde_set_current_version('<version_name>');

    For example:

    SELECT sde.sde_set_current_version('mvedits');

  7. Start an edit session by executing the sde_edit_version function and specifying "1". The 1 indicates an edit session should be started.
  8. SELECT sde.sde_edit_version('<version_name>',1);

    For example:

    SELECT sde.sde_edit_version('mvedits',1);

  9. Perform the necessary edits through the multiversioned view using SQL.
  10. In this example, an existing record is updated and a new record is inserted.

    UPDATE businesses_mvw
    SET owner_name = 'Cpt. Industry'
    WHERE business_name = 'Body Electric';

    INSERT INTO businesses_mvw
    (bid, business_name, business_type, owner_name, location) 
    VALUES (117, 
    'Bionix', 
    'engineering', 
    'Anjo Badsu', 
    ST_Point('point (40 40))', 12);

    To be sure you are getting a unique number to populate an SDE-maintained objectid field in a business table, you will need to query the sde_table_registry table to determine the registration_id of the business table on which your multiversioned view is based. Once you have that, you can use the table's corresponding i table to generate the next objectid. The statements to do this are similar to the following:

    SELECT registration_id FROM sde.sde_table_registry 
    WHERE table_name = 'businesses' and owner = 'editor';
    
registration_id
    
  11. ---------------
  12. 49

    Since the registration_id for the editor.businesses table is 49, you will select an ID from the i49 table using the function i49_get_ids(2,1). The 2 in the function is a constant. The 1 indicates you want one objectid returned from the i49 table. Since you are updating just one record, you only need one objectid.

    INSERT INTO businesses_mvw
    (objectid, bid, business_name, business_type, owner_name, location) 
    VALUES ((SELECT o_base_id FROM editor.i49_get_ids(2,1)), 
    119
    'Info4U', 
    'consultant', 
    'Neville Sorth', 
    ST_Point('point (12 36))', 12);

  13. Stop the edit session by executing the sde_edit_version function, but this time, specify "2". The 2 indicates the edit session should be closed.
  14. SELECT sde.sde_edit_version('<version_name>',2);

    For example:

    SELECT sde.sde_edit_version('mvedits',2);

After the changes have been reconciled and posted through ArcGIS Desktop, or if you decide you don't want the changes, you can delete the version you created in step 1 by executing the delete_version function.

SELECT sde.sde_delete_version('<version_name>');

To delete the version created in step 1, execute the following statement:

SELECT sde.sde_delete_version('mvedits');

If you accidentally drop your multiversioned view at the SQL prompt rather than using sdetable -o delete_mv_view, use the sde_clean_rule_functions to unreference the rules functions.

SELECT sde_clean_rule_functions();

Note that you should not use SQL to drop your multiversioned view. The sde_clean_rule_functions is provided in case you inadvertently do so.

Using multiversioned views in SQL Server

Multiversioned views can be used to read the data in a versioned dataset and also to edit the dataset.

Reading data with multiversioned views

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 other than the default, execute the ArcSDE set_current_version stored procedure. This procedure validates the supplied version name and sets the corresponding database state internally. It can be executed directly from a SQL client. The syntax for the stored procedure to set the current version is as follows:

For sde-schema geodatabases

EXEC sde.set_current_version '<version_name>'

For example:

EXEC sde.set_current_version 'edits'

For dbo-schema geodatabases

EXEC dbo.set_current_version '<version_name>'

For example:

EXEC dbo.set_current_version 'edits'

This procedure may be called again to change to 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.

NOTE: 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 view and modify these types of features.

Editing with multiversioned views

When you edit versioned tables through multiversioned views, changes are made to the delta tables, and row ID values for new records are 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.

NOTE: If you need to perform version management operations, such as reconciling, resolving conflicts, and posting, you need to use the ArcGIS software. Improper version management can lead to geodatabase corruption when working with views. Also note that you should never use DBMS tools to update any row ID (ObjectID) field maintained by ArcSDE in the database. These row ID fields are allocated and managed by the geodatabase and should not be altered.

There are several stored procedures installed with the ArcSDE component that help you work with multiversioned views. The stored procedure used to set which version the multiversioned view will work with was discussed in the previous section. There are also stored procedures to create a version in which you can do your editing, start and stop an edit session, and delete your edit version.

The following set of steps takes you through creating a multiversioned view, creating a version in which to perform edits, setting the version to edit, starting an edit session, performing some edits through the multiversioned view, stopping the edit session, committing your edits to the database, and deleting the version created for the edits. In the examples, the multiversioned view created to perform the edits on the business table is permits_mv; the version created and used for editing is mvedits; and the coordinate system is a geographic coordinate system, datum NAD27, using the Clarke 1866 spheroid and decimal degree units.

Remember that the dataset you edit through a multiversioned view must already have been registered as versioned through ArcGIS. For this example, the permits feature class would need to have already been registered as versioned in ArcCatalog to be able to edit it through a multiversioned view.

These edits are performed in a dbo-schema geodatabase.

  1. At a command prompt, execute the sdetable function to create a multiversioned view.
  2. sdetable –o create_mv_view –T permits_mv –t permits –i 4400 –D code –u sarja –p not4u

  3. Create a new version in which to perform your edits.
  4. EXEC dbo.create_version
    '<parent_version>', '<child_version>', <name_rule>, <access>, '<description>'

    where

    <parent_version> is the version from which your version is created.

    <child_version> is the name for the version you are creating.

    <name_rule> indicates whether the name specified for the version should be taken as given (2) or a unique name should be created when a duplicate name is specified (1).

    <access> is the permission level for the version, either 0 for Private, 1 for Public, or 2 for Protected.

    <description> is a text description of the child version.

    For example:

    EXEC dbo.create_version
    'dbo.DEFAULT', 'mvedits', 1, 1, 'multiversioned view edit version'

  5. Set the version for the edit session to the child version you just created.
  6. EXEC dbo.set_current_version '<version_name>'

    For example:

    EXEC dbo.set_current_version 'dbo.mvedits'

  7. Start an edit session by executing the edit_version stored procedure and specifying "1". The 1 indicates an edit session should be started.
  8. EXEC dbo.edit_version '<version_name>', 1

    For example:

    EXEC dbo.edit_version 'dbo.mvedits', 1

  9. Perform the necessary edits to the multiversioned view using SQL.
  10. In this example, an existing record is updated.

    UPDATE businesses_mvw
    SET owner_name = 'Cpt. Industry' 
    WHERE business_name = 'Body Electric'

  11. Stop the edit session by executing the edit_version stored procedure, but this time, specify "2". The 2 indicates the edit session should be closed.
  12. EXEC dbo.edit_version '<version_name>', 2

    For example:

    EXEC dbo.edit_version 'dbo.mvedits', 2

After the changes have been reconciled and posted through ArcGIS Desktop, or if you decide you do not want the changes, you can delete the version you created in step 1 by executing the delete_version stored procedure.

EXEC dbo.delete_version '<version_name>'

To delete the version created in step 1, execute the following statement:

EXEC dbo.delete_version 'dbo.mvedits'