In a data warehouse environment, we force the lockholder off to do the
Alter Table Activate....This is a standard part of our daily and weekly
data maintenance.

I would defer to Serge on this, but I THINK you could also do an online
(allow read access) load of an empty cursor for a slightly more elegant
solution.

Pete H

Good question. I guessed the answer, but still felt compelled to test.
It's just a regular DDL statement.
ALTER TABLE will have to wait until that someone else is done.

Session 1:
db2 => connect to test;

   Database Connection Information

 Database server        = DB2/NT Viper
 SQL authorization ID   = SRIELAU
 Local database alias   = TEST

db2 => update command options using c off;
DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.
db2 => declare cur1 cursor for select * from a3;
DB20000I  The SQL command completed successfully.
db2 => open cur1;
DB20000I  The SQL command completed successfully.
--- Run session 2 here
db2 => close cur1;
DB20000I  The SQL command completed successfully.
db2 => open cur1;
-- Wait for session 2 to commit
DB20000I  The SQL command completed successfully.

session 2:
db2 => connect to test;

   Database Connection Information

 Database server        = DB2/NT Viper
 SQL authorization ID   = SRIELAU
 Local database alias   = TEST

db2 => alter table a3 activate not logged initially with empty table;
-- Waits for session 1
-- returns when cursor is closed
DB20000I  The SQL command completed successfully.
db2 => commit;

Good question. I guessed the answer, but still felt compelled to test.
It's just a regular DDL statement.
ALTER TABLE will have to wait until that someone else is done.

Session 1:
db2 => connect to test;

   Database Connection Information

 Database server        = DB2/NT Viper
 SQL authorization ID   = SRIELAU
 Local database alias   = TEST

-- turn of auto commit
db2 => update command options using c off;
DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.
db2 => declare cur1 cursor for select * from a3;
DB20000I  The SQL command completed successfully.
db2 => open cur1;
DB20000I  The SQL command completed successfully.
--- Run session 2 here
db2 => close cur1;
DB20000I  The SQL command completed successfully.
db2 => open cur1;
-- Wait for session 2 to commit
DB20000I  The SQL command completed successfully.

session 2:
db2 => connect to test;

   Database Connection Information

 Database server        = DB2/NT Viper
 SQL authorization ID   = SRIELAU
 Local database alias   = TEST

-- turn of auto commit
db2 => update command options using c off;
DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.
db2 => alter table a3 activate not logged initially with empty table;
-- Waits for session 1
-- returns when cursor is closed
DB20000I  The SQL command completed successfully.
db2 => commit;

Eventually. I'm not sure whether a hypothetical TRUNCATE will only
require DELETE privileges.
It's a pretty big gun to unrecoverably wipe a table, and ignore triggers..
After all I assume you do not want to simple have TRUNCATE be a
"DELETE FROM T", right?

Interstingly I tried to see if you can work around it, but DB2 is
quite paranoid at this particluar point:

db2 => --#SET TERMINATOR $
db2 => DROP SPECIFIC PROCEDURE TRUNCATE
db2 (cont.) => $
DB20000I  The SQL command completed successfully.
db2 => CALL SYSPROC.SET_ROUTINE_OPTS('DYNAMICRULES BIND')
db2 (cont.) => $

  Return Status = 0

db2 => CREATE PROCEDURE TRUNCATE(IN tabschema VARCHAR(128),
db2 (cont.) =>                           IN tabname   VARCHAR(128))
db2 (cont.) => SPECIFIC TRUNCATE
db2 (cont.) => BEGIN
db2 (cont.) =>   DECLARE txt VARCHAR(1000);
db2 (cont.) =>   SET txt = 'ALTER TABLE "' || tabschema || '"."'
db2 (cont.) =>          || tabname || '" ACTIVATE NOT LOGGED'
db2 (cont.) =>          || ' INITIALLY WITH EMPTY TABLE';
db2 (cont.) =>   EXECUTE IMMEDIATE txt;
db2 (cont.) =>   COMMIT;
db2 (cont.) => END
db2 (cont.) => $
DB20000I  The SQL command completed successfully.
db2 => CALL SYSPROC.SET_ROUTINE_OPTS(CAST(NULL AS VARCHAR(1)))
db2 (cont.) => $

  Return Status = 0

db2 => GRANT EXECUTE ON PROCEDURE TRUNCATE TO JOE
db2 (cont.) => $
DB20000I  The SQL command completed successfully.
db2 => --#SET TERMINATOR ;
db2 => call truncate('SRIELAU', 'A3');
SQL0549N  The "ALTER" statement is not allowed for "package" "P7300390"
because the bind option DYNAMICRULES RUN is not in effect for the "package".
SQLSTATE=42509

Cheers
Serge