SQL/MX 3.1 Database and Application Migration Guide (H06.23+, J06.12+)

HP NonStop SQL/MX Release 3.1 Database and Application Migration Guide—663853-001
C-1
C Identifying tables where the
ownership has changed
If the ownership of database objects is changed after upgrading to SQL/MX Release
3.1, 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:
SCHEMA_OWNER OBJECT_OWNER OBJECT_NAME TYPE
------------ ------------ ----------- ----
65535 44801 SCH.V1 VI
44801 44802 USER1.T1 BT
--- 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