Read versioned data in DB2 using versioned views

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

Note:

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

Versioned views automatically access the current state of the DEFAULT version. If you execute SELECT statements against a versioned view, it will access the current state of DEFAULT. If other users are committing edits to the DEFAULT version (thereby changing the state that the DEFAULT version references), your subsequent queries will see the latest state and their edits.

If you want to query a version other than DEFAULT, or you don't want your queries against the DEFAULT version to change states, execute the setcurrentversion stored procedure. This procedure validates the supplied version name and sets the corresponding database state internally. Queries you make against the version will always point to the state the version referenced when you executed the setcurrentversion stored procedure.

Setcurrentversion can be executed directly from a SQL client. The syntax is as follows:

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

This procedure can 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.

The following steps show you how to run setcurrentversion to query a version:

Steps:

1. Be sure there is a versioned view for the versioned feature class or table you want to access.

   Beginning with ArcGIS 10.1, versioned views are created when you version data. If your data was versioned prior to 10.1, you can create a versioned view by right-clicking the dataset, pointing to Manage, and clicking Enable SQL Access.

2. Open a SQL client and call the setcurrentversion stored procedure to set the version you want to query.

   In this example, version2 is set as the version to be queried.

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

   The question marks indicate message code output and message output. Message code output and message output are the SQL codes and messages returned after you execute the procedure. You pass in question marks, and the code and message are returned to you.

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

   In this example, the versioned view is code_mv.

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

If you need to return to querying the current state of the DEFAULT version, execute the set_default procedure.

CALL sde.set_default;

You can then run SELECT statements on the versioned view and your queries will run on the latest state of the DEFAULT version.