IBM Connections 4.0. How to change ownership for wiki owned by an inactive user?

Few days ago I received a question like that: person was fired, his user in LDAP was deleted and his profile in Connections was deactivated. How to change his wiki's ownership to continue editing it?

The answer: there is no means to do it from administrative interface. But as long as we store all the data in DB2 database - we can do whatever we want. Here's a small procedure that will help us:

CREATE OR REPLACE PROCEDURE WIKI_CHANGE_OWNER ( IN WIKILABEL VARCHAR(256), IN OWNER_NAME VARCHAR(256), IN NEW_OWNER_NAME VARCHAR(256) )
LANGUAGE SQL
BEGIN
 DECLARE CURRENT_OWNER_ID CHAR(16) FOR BIT DATA;
 DECLARE NEW_OWNER_ID CHAR(16) FOR BIT DATA;
 DECLARE WIKI_ID CHAR(16) FOR BIT DATA;

 SET WIKI_ID = (SELECT "ID" from "WIKIS"."LIBRARY" where "LABEL" like WIKILABEL and "OWNER_USER_ID" = CURRENT_OWNER_ID);
 IF WIKI_ID IS NULL THEN
  CALL DBMS_OUTPUT.PUT_LINE('WIKI_ID not found. Probably the title was misspelled.');
  RETURN -1;
 END IF;


 SET CURRENT_OWNER_ID = (SELECT "ID" from "WIKIS"."USER" where "NAME" like OWNER_NAME);
 
 IF CURRENT_OWNER_ID IS NULL THEN
  CALL DBMS_OUTPUT.PUT_LINE('CURRENT_OWNER_ID not found. Probably the name was misspelled.');
  RETURN -1;
 END IF;
 
 SET NEW_OWNER_ID = (SELECT "ID" from "WIKIS"."USER" where "NAME" like NEW_OWNER_NAME);
 IF NEW_OWNER_ID IS NULL THEN
  CALL DBMS_OUTPUT.PUT_LINE('NEW_OWNER_ID not found. Probably the name was misspelled.');
 END IF;
 
 UPDATE "WIKIS"."LIBRARY" SET "OWNER_USER_ID"=NEW_OWNER_ID WHERE "ID" = WIKI_ID;
 UPDATE "WIKIS"."USER_TO_LIBRARY_ROLE" SET "USER_ID"=NEW_OWNER_ID WHERE "LIBRARY_ID"=WIKI_ID AND "USER_ID"=CURRENT_OWNER_ID;
 CALL DBMS_OUTPUT.PUT_LINE('OWNERSHIP SUCCESSFULLY CHANGED');
END!

How to use it:

1. Save it to file proc.sql
2. In application server console stop the Wikis application.
3. Log in to database with dbadm authorities:
   > db2 connect to wikis user db2admin
4. Backup the database. Like this (example for Windows):
   > db2 force application all
   > db2 backup db wikis to d:\
5. After db backup deploy the procedure:
   > db2 -td! -vf proc.sql
6. Now you can use this procedure as many times as you want. 'Full user name' means full name that you can copy from Connections web-interface. Like this:
   > db2 set serveroutput on
   > db2 call wiki_change_owner('wiki name', 'Full User Name', 'New Owner Name')
7. If everything went well you'll get  Return Status = 0
8. If something went wrong you will see the explanation.