Topic
4 replies Latest Post - ‏2013-01-14T18:27:49Z by jcdelmo
jocaseyibm
jocaseyibm
48 Posts
ACCEPTED ANSWER

Pinned topic What db2 security settings are required for running dmhClearDB?

‏2013-01-08T20:48:06Z |
Customer is unable to successfully run dmhClearDB.rexx on their RAA Windows server (6.0.0.11). The console output says that all tables are processed, but the log file says that only rows from 4 BRM tables have been deleted. We have not found any other DB2 messages.

I think it is an authorization issue, since I can successfully run dmhcleardb on my personal install of RAA, where my userid has all priviledges.

On this customer's RAA installation, users are assigned to a DB2 group which has these priviledges:
  • CONNECT to database;
  • SELECT, INSERT, UPDATE, DELETE against all tables;
  • and EXECUTE on the utility plans.

With these settings, users of this group can use RAA's web interface and they can run the dmh6000 utility. I think dmhClearDB.rexx is different in that it uses dynamic SQL, which may require other DB2 authorization settings.

What additional db2 security settings are required for running dmhClearDB.rexx?
Updated on 2013-01-14T18:27:49Z at 2013-01-14T18:27:49Z by jcdelmo
  • SystemAdmin
    SystemAdmin
    849 Posts
    ACCEPTED ANSWER

    Re: What db2 security settings are required for running dmhClearDB?

    ‏2013-01-08T22:43:36Z  in response to jocaseyibm
    Can the users execute the dmh runstats and reorg ? They should have the same requirements.

    FYI, in my experience, I have found that if there is a reason to reset the database, it is much faster to drop it and rebuild it. Obviously you need to know that you may loose some work. You can export CQs, but that is about it.
    • jocaseyibm
      jocaseyibm
      48 Posts
      ACCEPTED ANSWER

      Re: What db2 security settings are required for running dmhClearDB?

      ‏2013-01-14T17:45:23Z  in response to SystemAdmin
      In some situations it may be faster to reset the database by rebuilding it, but in that case you lose any work you have done to setup the database, such as DB2 grants, concat sets.

      After a few tests, having SELECT/DELETE on the RAA tables for the user (or group that user is in) is sufficient to successfully run dmhcleardb.

      Another user had a similiar experience with dmhcleardb not clearing containers - the most obvious example of a table whose contents were not deleted. In each case, it seems that after running dmhcleardb mulitple times, move talbes were cleared in the successive runs.

      Does this make sense? Is there something special about how containers are setup (referential relationships) in RAA's database that would prevent them being deleted before some other row had been deleted first?
      • SystemAdmin
        SystemAdmin
        849 Posts
        ACCEPTED ANSWER

        Re: What db2 security settings are required for running dmhClearDB?

        ‏2013-01-14T18:08:49Z  in response to jocaseyibm
        You may be running into DB2 log size problems. I am not sure what sequence the code uses to delete the tables, but triggers are going to be tripping all over the place.
      • jcdelmo
        jcdelmo
        291 Posts
        ACCEPTED ANSWER

        Re: What db2 security settings are required for running dmhClearDB?

        ‏2013-01-14T18:27:49Z  in response to jocaseyibm
        dmhClearDB processes the tables in alphabetical order, relying on the triggers to eliminate related rows in other tables. This is not the most efficient way to clear an entire RAA database (while preserving what is needed to run RAA itself) - but it is consistent.

        The main method in DbCleanup.java would be more efficient (because it uses DELETE, TRUNCATE or ALTER depending on platform/db2 version being used) but currently is not packaged for client use.

        If there are issues, I agree with Tony that triggers and log space issues may be involved.