Reading versioned data in DB2 using multiversioned views

You can execute SQL SELECT statements against multiversioned views to access versioned data.

Note:

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

Multiversioned views automatically access the DEFAULT version. Before issuing any queries against the view, you must ensure that you will be querying the required 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>',?,?)

This procedure may be called again to change to other versions as required and can be called each time the workspace is refreshed to return the current state of the versioned table to the calling application.

Caution:

Multiversioned views should not be used to access or modify complex features such as features that participate in geometric networks, topologies, terrains, cadastral fabrics, network datasets, or relationships or that have specific geodatabase behavior. You should use ArcGIS to view and modify these types of features.

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. Open a SQL client and call the setcurrentversion stored procedure to set the version you want to edit.

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

   The question marks indicate message_code_output and message_output. 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.

3. Issue a SELECT statement against the multiversioned view to read versioned data from the geodatabase.

   SELECT owner, site_address, region
   FROM code_mv
   WHERE region = 'b'