SQL/MX 3.2 Installation and Upgrade Guide (H06.25+, J06.14+)

C Identifying tables where the ownership has changed
If the ownership of database objects is changed after upgrading to SQL/MX Release 3.2, you
must revert the changes before fallback, because earlier releases do not support this feature. To
revert the ownership, the schema owner must be an existing Guardian user.
To revert the changes prior to fallback, complete the following steps for each user catalog on the
system:
1. To identify the list of objects in a catalog whose owners are different from their parent schema
owners, run the following query:
SELECT
SCHEMA_OWNER,
OBJECT_OWNER,
CAST(TRIM(S.SCHEMA_NAME)||'.'||TRIM(O.OBJECT_NAME) AS
CHAR(45)) AS "OBJECT_NAME",
OBJECT_TYPE as TYPE
FROM
<CATNAME>.DEFINITION_SCHEMA_VERSION_3100.OBJECTS O,
NONSTOP_SQLMX_<NODENAME>.SYSTEM_SCHEMA.SCHEMATA S
WHERE
O.SCHEMA_UID = S.SCHEMA_UID AND
O.OBJECT_OWNER <> S.SCHEMA_OWNER;
The following is a sample output:
TYPEOBJECT_NAMEOBJECT_OWNERSCHEMA_OWNER
VISCH.V14480165535
BTUSER1.T14480244801
--- 2 row(s) selected.
2. Logon as the schema owner or security administrator or super ID (if Super ID is part of the
Security Administrator Group (SAG) or if the SAG does not exist).
3. Based on the object type, run the corresponding GIVE commands on the objects to change
their ownership to the schema owner. For example, if the schema and object owners are as
described in step 1, and the catalog name is 'CAT', complete the following steps:
a. Logon as SUPER.SUPER (whose Guardian ID is 65535), the schema owner for schema
'SCH'.
b. Run the following command for the view SCH.V1:
GIVE VIEW CAT.SCH.V1 to "SUPER.SUPER";
--- SQL operation complete.
c. Logon as SQL.USER1 (whose Guardian ID is 44801), the schema owner for schema
'USER1'.
d. Run the following command for table USER1.T1:
GIVE TABLE CAT.USER1.T1 to "SQL.USER1";
--- SQL operation complete.
59