Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
5 replies Latest Post - ‏2013-01-07T10:20:32Z by SystemAdmin
SystemAdmin
SystemAdmin
141 Posts
ACCEPTED ANSWER

Pinned topic WSRR database usage and suggested number of databases

‏2013-01-03T17:10:23Z |
Hi,

I am unable to find detailed description/usages of different databases used by WSRR - Activity Logging, WSRR, Business Space and System Integration Bus. Also, As per my understanding these can be separate databases or a single databases. Given I will be using oracle for the database what is best possible solution. I thought of having single database and multiple schema (possible 2 - one for core (which has wsrr, act, bs) and other for SIB), is this correct topology?. Can someone please suggest document to get knowledge about these databases and help in decision separation of databases or schema.

Thanks in advance.

  • KS
Updated on 2013-01-07T10:20:32Z at 2013-01-07T10:20:32Z by SystemAdmin
  • johnduffell
    johnduffell
    9 Posts
    ACCEPTED ANSWER

    Re: WSRR database usage and suggested number of databases

    ‏2013-01-04T10:02:19Z  in response to SystemAdmin
    Hi, thanks for your message Kuldeep,

    Whenever this question has come up in the past, the recommendation has been to create multiple schemas in the same database. Separating your databases doesn't give any particular benefits, but if your company has its own requirements to have them separate, WSRR will support this scenario.

    Hope this helps, John

    John Duffell, Test, WebSphere Service Registry and Repository
    • SystemAdmin
      SystemAdmin
      141 Posts
      ACCEPTED ANSWER

      Re: WSRR database usage and suggested number of databases

      ‏2013-01-04T12:30:37Z  in response to johnduffell
      Thanks John,

      Can you share me some link document to study wsrr database/tables and their interaction with WSRR application?

      Thanks for your help.

      • KS
      • johnduffell
        johnduffell
        9 Posts
        ACCEPTED ANSWER

        Re: WSRR database usage and suggested number of databases

        ‏2013-01-04T15:07:37Z  in response to SystemAdmin
        Hi Kuldeep, thanks for your message.

        We don't document the specifics of the tables, but there are several groups of tables. Some for the Business Space UI, some for the SI Bus, some for Activity Logging, and some for the core WSRR object data.

        Unless your DBAs require otherwise, a common database should meet your needs.

        Thanks, John

        John Duffell, Test, WebSphere Service Registry and Repository
        • SystemAdmin
          SystemAdmin
          141 Posts
          ACCEPTED ANSWER

          Re: WSRR database usage and suggested number of databases

          ‏2013-01-06T17:24:37Z  in response to johnduffell
          Thanks John,

          Can you pls answer another question about database scripts being created -
          1) Scripts which are getting created for Oracle by (WSRR 8.0) containing lines
          ALTER USER SYSTEM IDENTIFIED BY XXXXXX;
          ALTER USER SYS IDENTIFIED BY XXXXXX;

          Is it required to change system and sys password?

          2) And this line -
          CREATE USER WSRR IDENTIFIED EXTERNALLY DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS ACCOUNT LOCK;
          where it script already has following line - CREATE USER WSRR IDENTIFIED BY XXXXXX

          Which of the statement need to be executed?

          Thanks
          • KS
          • SystemAdmin
            SystemAdmin
            141 Posts
            ACCEPTED ANSWER

            Re: WSRR database usage and suggested number of databases

            ‏2013-01-07T10:20:32Z  in response to SystemAdmin
            The WSRR database tables do not require db SYS authority.
            However the SIB ones do - they require truncate permission and in order to get that they need full db sys authority.

            The reason there are two create user lines is historical, WSRR used to support the use of a different schema to the user name.
            Which is why there are two create user lines - one to create the user and one to create the schema.

            However in v8 for various reasons WSRR has had to remove support for configurable schema names and the schema must now match the db user name. This does mean that one of those create user lines is now superfluous.
            If you look at the SQL script is apparent which line is for the user and which is for the schema:

            CREATE USER __DBUSER__ IDENTIFIED BY __DBPASSWORD__;
            GRANT CREATE SESSION TO __DBUSER__;
            GRANT UNLIMITED TABLESPACE TO __DBUSER__;

            CREATE USER __DBSCHEMA__ IDENTIFIED EXTERNALLY DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS ACCOUNT LOCK;

            The last one uses __DBSCHEMA__ as the variable name which is a clue that that line is for creating the schema not the user, as such in WSRR v8 that line should not really be needed any longer.

            I will add that the SQL files are designed to be run as is, and you edit them at your own risk. Commenting out lines could cause product functionality to break and will not have been tested (and so may not be supported). e.g. We have never tested the product with the ALTER USER SYSTEM and SYS lines and the second create user (for the schema) commented out.

            Regards,

            Chris