A few questions on SET INTEGRITY

[Brian Tkatch]

A few questions about SET INTEGRITY.

1) Docs: The SET INTEGRITY statement is under transaction control.

Using two sessions (different user, even) i tried the following.

Session 1: UPDATE COMMAND OPTIONS USING C OFF
Session 1: LOAD a TABLE
Session 2: SysCat.Tables.Status = 'C'
Session 1: SET INTEGRITY FOR table IMMEDIATE CHECKED
Session 2: SysCat.Tables.Status = ''

Shouldn't Session 1 require a COMMIT for SESSION 2 to see it?

2) DECLARE GLOBAL TEMPORARY TABLE A LIKE table.
SET INTEGRITY FOR table FOR EXCEPTION IN table USE SESSION.A

I had some exceptions, and this resulted in an error telling be i
didn't have INSERT permissions on SESSION.A.

I had to ask to DBA to create another (real) TABLE, and GRANT me
INSERT, and then the same statement worked.

Are GLOBAL TEMPORARY TABLEs not supported as EXCEPTION TABLEs?

3) SET INTEGRITY accepts more than one TABLE in its list of TABLEs.

(i) If all of the TABLEs are SysCat.Tables.Status = 'C' , the statement
work regardless of order.
(ii) If any of the TABLEs are SysCat.Tables.Status = '', the entire
statement fails.
(iii) If a TABLE is SysCat.Tables.Status = '' but will become
SysCat.Tables.Status = 'C' as a result of another TABLE in the
statement, the statement worksincluding this TABLE.

Why is (ii) like that? I can understand a warning message, but
complete failure of all TABLEs listed? And especially with (iii) it
doesn't seem correct.

B.

[Brian Tkatch]

One more. The docs state:

Turn on and perform integrity checking.

The privileges held by the authorization ID of the statement must
include at least one of the following:

* CONTROL privilege on the tables that are being checked and, if
exceptions are being posted to one or more exception tables, INSERT
privilege on those tables
* CONTROL privilege on all descendent foreign key tables,
descendent immediate materialized query tables, and descendent
immediate staging tables that will implicitly be placed in check
pending state by the statement
* LOAD authority and, if exceptions are being posted to one or more
tables:
o SELECT and DELETE privilege on each table being checked;
and
o INSERT privilege on the exception tables
* SYSADM or DBADM authority

Apparently, it requires LOCK on the TABLE as well. One user here has
LOAD in the DB, yet cannot SET INTEGRITY to IMMEDIATE CHECKED for a
TABLE :

SQL0551N "<user>" does not have the privilege to perform operation
"LOCK"
on object "<table>". SQLSTATE=42501

B.