Topic
  • 5 replies
  • Latest Post - ‏2013-01-07T10:20:32Z by SystemAdmin
SystemAdmin
SystemAdmin
141 Posts

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

    Re: WSRR database usage and suggested number of databases

    ‏2013-01-04T10:02:19Z  
    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

    Re: WSRR database usage and suggested number of databases

    ‏2013-01-04T12:30:37Z  
    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
    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

    Re: WSRR database usage and suggested number of databases

    ‏2013-01-04T15:07:37Z  
    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
    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

    Re: WSRR database usage and suggested number of databases

    ‏2013-01-06T17:24:37Z  
    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
    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

    Re: WSRR database usage and suggested number of databases

    ‏2013-01-07T10:20:32Z  
    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
    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