Procedure to do a mass delete in the SQL-tables (outside the Controller interface).

It is very much work to execute the UNSUPPORTED solution described hereby. We require this because we perform regularly a datarefresh to another pillar. In the target pillar, other users need access to Controller, and it should taken many hours to delete the users (around 800!!) one by one in the interface.

Second reason: if some users remain the same (with the same access), the CAMid's of them can't even be reused because the are different from the one in the source pillar. So, a user that logs on gets another CAMid so that the SAME user appears twice in the database. It is difficult for administration tasks to figure out the correct userid to provide the necessary authorisation group.

We think that a lot of companies suffer with the same issue. At IBM, Nathalie.St.denis-peers helped us to complete this *unsupported* procedure which we had already created ourselves partly. 

Current unsupported procedure:
1) Create backup of Cognos controller DB in production
2) Create export of XCAMUSER/XUSER/XKONFIG/XMEDLEM/XNYST table in Cognos controller DB in acceptance
3) Export User rights in acceptance via export structures
4) Restore production db in acceptance
5) Truncate XCAMUSER table
6) Refill XCAMUSER table with previous taken export
7) Import user rights in acceptance via import structures (single mode)
8) Delete statements:
DELETE * FROM cognos.xkonfig WHERE NOT EXISTS ( SELECT 1 FROM cognos.xcamuser c
                                                                                               WHERE c.CONTROLLERUSER = cognos.xkonfig.anvid
                                                                                              )
                                                                       AND cognos.xkonfig.typ1 = 'U'
DELETE * FROM cognos.xnyst WHERE NOT EXISTS ( SELECT 1 FROM cognos.xcamuser c
                                                                                              WHERE c.CONTROLLERUSER = cognos.xnyst.frmedlem
                                                                                           )
                                                                    AND cognos.xnyst.frgrptyp like 'U%%'
DELETE * FROM cognos.xuser x WHERE NOT EXISTS ( SELECT 1 FROM cognos.xcamuser c
                                                                                               WHERE c.CONTROLLERUSER = x.userid
                                                                                             )
DELETE * FROM cognos.xmedlem WHERE NOT EXISTS ( SELECT 1 FROM cognos.xcamuser c
                                                                                                    WHERE c.CONTROLLERUSER = cognos.xmedlem.medlem
                                                                                                 )
                                                                           AND cognos.xmedlem.grptyp = 'U' or cognos.xmedlem.grptyp = 'Ux'7

9) Launch IP (to publish Controller via FAP to TM1)