Editing versioned data in DB2 using multiversioned views and SQL

You can edit versioned attribute data in a geodatabase in IBM DB2 on Linux, UNIX, or Windows operating systems by editing a multiversioned view created on the data.

Note:

Multiversioned views are not supported in DB2 databases on z operating systems.

There are several stored procedures installed with ArcSDE that help you work with multiversioned views. These components and their descriptions are as follows:

Stored procedure	Purpose	Syntax and description
sde.create_version	Create a new geodatabase version. Always edit your own, separate version; multiple editors cannot edit the same version using multiversioned views.	CALL sde.create_version ('<parent_version>', '<child_version>', <name_rule>, <access>, '<description>', <message_code_output>, <message_output>) <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.
sde.setcurrentversion	Set which geodatabase version you will access.	CALL sde.setcurrentversion('<version_name>', <message_code_output>, <message_output>) The <version_name> is the name of the geodatabase version to which you want to connect.
sde.edit_version	Start and end an edit session.	CALL sde.edit_version('<version_name>', <1 or 2>, <message_code_output>, <message_output>) Specify 1 to start the edit session. Specify 2 to end it.
sde.delete_version	Delete a geodatabase version.	CALL sde.delete_version('<version_name>', <message_code_output>, <message_output>)

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

Additionally, an exclusive lock is placed on the state that the version references when you start an edit session on a multiversioned view. 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 and locks will block other users. Instead, create your own version specifically for your multiversioned view edit session.

Caution:

• Version management operations, such as reconciling, resolving conflicts, and posting, must be done using the ArcGIS software. Improper version management can lead to geodatabase corruption when working with views.
• Never use database management system (DBMS) tools to update any row ID (object ID) field maintained by ArcSDE in the database. These object ID fields are allocated and managed by the geodatabase and, therefore, should not be altered using SQL.
• Never edit the DEFAULT version of the geodatabase using SQL. Starting an edit session on a version obtains an exclusive lock on the state that the version references. If you lock the DEFAULT version, you prevent ArcGIS users from connecting to the geodatabase.

The following steps guide 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 code_mv, and the version created and used for editing is mvedits.

Tip:

Remember that the dataset you edit through a multiversioned view must already have been registered as versioned.

Steps:

1. At an MS-DOS (Windows) or shell (UNIX or Linux) command prompt, execute the sdetable utility to create a multiversioned view.

   sdetable –o create_mv_view –T code_mv 
   –t code_enf –i sde:db2 –D code –u sarja –p not4U

2. Create a new version in which to perform your edits.

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

Text enclosed in single quotes is read literally. Therefore, the text you type inside the quote marks must match the case of the text (all upper case, all lower case, or mixed case) as it is stored in the database.

3. Set the version for the edit session to the child version you just created.

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

4. Start an edit session by calling the edit_version stored procedure and specifying 1. The 1 indicates an edit session should be started.

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

5. Perform the first edit to the multiversioned view using SQL.

   In this example, an existing record is updated.

   UPDATE code_mv 
   SET propowner = 'C. Industry' 
   WHERE vid = 2233

6. Perform the next edit to the multiversioned view using SQL.

   In this example, a new record is inserted.

   INSERT INTO code_mv 
   (codenum, propowner) 
   VALUES (456, 'Anjo Badsu')

   Tip:

   In the INSERT statement, no object ID value is specified; the multiversioned view automatically gets the next available object ID and inserts it for the row.

   Although it is possible to version a table that has only been registered with ArcSDE and has a user-maintained object ID column, this is not recommended. If you do this anyway, though, you must provide an object ID with the INSERT statements; the multiversioned view cannot generate a value for you.

7. Execute a COMMIT or ROLLBACK statement.
8. Stop the edit session by calling the edit_version stored procedure, but this time, specify 2. The 2 indicates the edit session should be ended.

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

9. When all editing is done, reconcile and post edits through ArcGIS Desktop, then delete the geodatabase version you created in step 2. Or if you decide you do not want the changes, you can delete the version you created in step 2 without reconciling and posting the edits.

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