English

Problem: Upgrading a geodatabase in DB2 fails if multiversioned views exist

Description

If there are multiversioned views present in the geodatabase when upgrading, the upgrade fails.

Cause

Certain stored procedures need to be upgraded to a different format when upgrading to ArcSDE 9.3. Multiversioned views use some of these stored procedures. Therefore, if the multiversioned views are still present when the upgrade is run, the upgrade fails.

Solution or Workaround

1. Before uninstalling ArcSDE, check the permissions granted on all multiversioned views in the database.

2. Make a backup of the database and run a script similar to the mv_view_upgrade_db2 script provided below. This script generates one text file, mv_view_owners.txt, and two batch files, mv_view_delete_<schema>.bat and mv_view_create_<schema>.bat.

The mv_view_owners.txt file contains the names of all schemas that contain multiversioned views. Use this list to check the existing permissions on the multiversioned views before running the mv_view_delete_<schema>.bat file.

3. Run the mv_view_delete_<schema>.bat file to delete the existing multiversioned views.

4. Once the multiversioned views have been dropped, uninstall ArcSDE, upgrade the geodatabase, and run the mv_view_create_<schema>.bat file to re-create the multiversioned views.

5. Finally, re-grant all necessary permissions on the multiversioned views.

The following is a sample script, mv_view_upgrade_db2, for Windows operating systems. For UNIX or Linux operating systems, modify the script for the platform used.


Code:
REM -------------------------------------------------------------
REM Windows mv_view_upgrade_db2 script
REM Change variables to match your implementation before running
REM -------------------------------------------------------------------
echo off
prompt [$T] $P$G
REM --------------------------------------------------------------------
REM Set SDE environment variables
REM --------------------------------------------------------------------
set SDEHOME=C:\arcgis93\arcsde\db2exe
set PATH=%SDEHOME%\bin;%PATH%

set SDESERVER=%COMPUTERNAME%
set SDEINSTANCE=sde93_db282
set SDEDATABASE=sde93
set SDEUSER=sde
set SDEPASSWORD=sde

REM --------------------------------------------------------------------
REM Execute commands
REM --------------------------------------------------------------------

echo on

db2 connect to %SDEDATABASE% user %SDEUSER% using %SDEPASSWORD%

db2 EXPORT TO mv_view_owners.txt OF DEL ^
MODIFIED BY chardel0x09^
SELECT DISTINCT owner ^
FROM table_registry ^
WHERE imv_view_name IS NOT NULL

for /F "tokens=1" %%O in (mv_view_owners.txt) do db2 ^
EXPORT TO mv_view_delete_%%O.bat OF DEL ^
MODIFIED BY chardel0x09^
SELECT 'sdetable -o delete_mv_view -t ' ^
CONCAT table_name ^
CONCAT ' -s %SDESERVER% ' ^
CONCAT ' -i %SDEINSTANCE% ' ^
CONCAT ' -D %SDEDATABASE% ' ^
CONCAT ' -N ' ^
FROM table_registry ^
WHERE imv_view_name IS NOT NULL ^
AND OWNER = UPPER('%%O')

for /F "tokens=1" %%O in (mv_view_owners.txt) do db2 ^
EXPORT TO mv_view_create_%%O.bat OF DEL ^
MODIFIED BY chardel0x09^
SELECT 'sdetable -o create_mv_view -T ' ^
CONCAT imv_view_name ^
CONCAT ' -t ' ^
CONCAT table_name ^
CONCAT ' -s %SDESERVER% ' ^
CONCAT ' -i %SDEINSTANCE% ' ^
CONCAT ' -D %SDEDATABASE% ' ^
FROM table_registry ^
WHERE imv_view_name IS NOT NULL ^
AND OWNER = UPPER('%%O')

prompt