Websphere portal by default uses derby database as embedded database. It stories all persistence information related to pages preferences in these databases. In this post we will explore these databases to understand internal architecture of portal.
List of databases used by portal :-
Customization database :- This database stores information related to user customizations like portletdta
Community,Jcr database :- This schema stores information about stored documents. This data will be modified during runtime.
Process to view database , tables and data in it :-
Use case scenario 1:-
A customer had deleted the custom unique name for the portal content root node. This broke everything. When attempting to access the portal, only white screens loaded in the browser. We had to use the following insert statement to manually put the record back in place and recover portal access:
Solution :-
INSERT INTO <RELEASE>.UNIQUE_NAME (UNIQUE_NAME, RES_TYPE, OID, VP_DESC_OID)
VALUES ('wps.content.root',6,X'00000000000000000000000000000000000a' ,X'00000000000000000000d93b44a505010000');
List of databases used by portal :-
- Community database
- Jcr database
- Release database
- Likeminds database
- Feedback database
- Customization database
Customization database :- This database stores information related to user customizations like portletdta
Community,Jcr database :- This schema stores information about stored documents. This data will be modified during runtime.
Process to view database , tables and data in it :-
- Navigate to path WebSphere\AppServer\derby\bin\embedded and Run ij.bat cmd window
- connect to the WPSDB database by enterring connect 'jdbc:derby:C:\IBM\WebSphere\wp_profile\PortalServer\derby\wpsdb';
- Total available tables in wpsdb derby database 395 tables under above listed databases
- To test this connection execute the following query, It returns all the Custom Unique Name records for the entities in the portal:
- select * from release.unique_name; {Refer to Derby documentation to explore more.}
Schemas in portal db :-
Tables under RELEASE db {Similarly explore remaining dbs}:-
ij> show tables in RELEASE
> ;
TABLE_SCHEM |TABLE_NAME |REMARKS
------------------------------------------------------------------------
RELEASE |ACL |
RELEASE |ACL_ENTRY |
RELEASE |ACTION_DESC |
RELEASE |ACTION_DESC_LOD |
RELEASE |ACTION_SET |
RELEASE |ACTION_SET_LOD |
RELEASE |APPLICATION_ROLE |
RELEASE |APPROLE_LOD |
RELEASE |APP_DESC |
RELEASE |APP_DESC_DD |
RELEASE |APP_DESC_LOD |
RELEASE |AUTH_LEVEL |
RELEASE |CLIENT_DESC |
RELEASE |CLIENT_DESC_CAPS |
RELEASE |COMP_INST |
RELEASE |COMP_INST_DD |
RELEASE |COMP_INST_DRAFT |
RELEASE |COMP_INST_DRAFT_DD |
RELEASE |COMP_INST_DRAFT_MAD |
RELEASE |COMP_INST_MAD |
RELEASE |CONTENT_MAPPING |
RELEASE |CONTENT_MAPPING_DRAFT |
RELEASE |CP_FILTER_DATA |
RELEASE |CP_FILTER_INST |
RELEASE |CRED_SEGMENT |
RELEASE |CRED_SLOT |
RELEASE |CRED_SLOT_LOD |
RELEASE |DEVICE_CLASS_DESC |
RELEASE |DEVICE_CLASS_DESC_LOD |
RELEASE |EVT_HANDLER |
RELEASE |EVT_INTERFACE |
RELEASE |LANG_DESC |
RELEASE |LANG_DESC_LOD |
RELEASE |LNK_APPROLE_ROLE |
RELEASE |LNK_APPROL_APPROL |
RELEASE |LNK_PAGE_DC |
RELEASE |LNK_PAGE_DC_DRAFT |
RELEASE |LNK_PAGE_PORT |
RELEASE |LNK_PAGE_PORT_DRAFT |
RELEASE |LNK_RESTYPE |
RELEASE |LNK_RES_TYPE_AS |
RELEASE |LNK_RES_TYPE_RES |
RELEASE |LNK_THEME_SKIN |
RELEASE |LNK_USER_APPROLE |
RELEASE |LNK_USER_ROLE |
RELEASE |LNK_USER_ROLE_DRAFT |
RELEASE |MARKUP_DESC |
RELEASE |MARKUP_DESC_LOD |
RELEASE |PAGE_ACT_ASC |
RELEASE |PAGE_ACT_ASC_DRAFT |
RELEASE |PAGE_ACT_ASC_DRAFT_LOD |
RELEASE |PAGE_ACT_ASC_LOD |
RELEASE |PAGE_INST |
RELEASE |PAGE_INST_DD |
RELEASE |PAGE_INST_DRAFT |
RELEASE |PAGE_INST_DRAFT_DD |
RELEASE |PAGE_INST_DRAFT_LOD |
RELEASE |PAGE_INST_DRAFT_MAD |
RELEASE |PAGE_INST_LOD |
RELEASE |PAGE_INST_MAD |
RELEASE |PAGE_LAYOUT_DESC |
RELEASE |PAGE_LAYOUT_DESC_DD |
RELEASE |PAGE_LAYOUT_DESC_LOD |
RELEASE |PAGE_LAYOUT_DESC_MAD |
RELEASE |PORTAL_URL |
RELEASE |PORTAL_URL_DD |
RELEASE |PORT_ACT |
RELEASE |PORT_ACT_DD |
RELEASE |PORT_ACT_LOD |
RELEASE |PORT_ACT_MD |
RELEASE |PORT_DESC |
RELEASE |PORT_DESC_DD |
RELEASE |PORT_DESC_LOD |
RELEASE |PORT_DESC_PREF |
RELEASE |PORT_INST |
RELEASE |PORT_INST_DRAFT |
RELEASE |PORT_INST_DRAFT_PREF |
RELEASE |PORT_INST_PREF |
RELEASE |PORT_PARM |
RELEASE |PORT_PARM_LOD |
RELEASE |PORT_PROP |
RELEASE |PORT_PROP_ALIAS |
RELEASE |PORT_PROP_LOD |
RELEASE |PORT_WIRE |
RELEASE |PORT_WIRE_DD |
RELEASE |PORT_WIRE_DRAFT |
RELEASE |PORT_WIRE_DRAFT_DD |
RELEASE |PORT_WIRE_DRAFT_LOD |
RELEASE |PORT_WIRE_DRAFT_MD |
RELEASE |PORT_WIRE_LOD |
RELEASE |PORT_WIRE_MD |
RELEASE |PROT_RES |
RELEASE |PROT_RES_DEP |
RELEASE |PROT_RES_DRAFT |
RELEASE |PSE_CATEGORY |
RELEASE |PSE_CATEGORY_DD |
RELEASE |PSE_CATEGORY_LOD |
RELEASE |PSE_DESC |
RELEASE |PSE_DESC_DD |
RELEASE |PSE_SOURCE |
RELEASE |PSE_SOURCE_DD |
RELEASE |PSE_SOURCE_LOD |
RELEASE |PUB_REND_PARM |
RELEASE |PUB_REND_PARM_LOD |
RELEASE |PUB_REND_PARM_QN |
RELEASE |RES_TYPE |
RELEASE |RES_TYPE_LOD |
RELEASE |ROLE_INST |
RELEASE |ROLE_INST_DRAFT |
RELEASE |SEARCH_REG |
RELEASE |SEARCH_REG_DD |
RELEASE |SERVER_INFO |
RELEASE |SERVER_INFO_DD |
RELEASE |SERVER_INFO_LOD |
RELEASE |SERV_DESC |
RELEASE |SERV_DESC_DD |
RELEASE |SERV_DESC_LOD |
RELEASE |SERV_DESC_MAD |
RELEASE |SERV_DESC_PREF |
RELEASE |SKIN_DESC |
RELEASE |SKIN_DESC_DD |
RELEASE |SKIN_DESC_LOD |
RELEASE |SKIN_DESC_MAD |
RELEASE |THEME_DESC |
RELEASE |THEME_DESC_DD |
RELEASE |THEME_DESC_LOD |
RELEASE |THEME_DESC_MAD |
RELEASE |UNIQUE_NAME |
RELEASE |UNIQUE_NAME_DRAFT |
RELEASE |URL_MAP_CTX |
RELEASE |URL_MAP_CTX_LBLS |
RELEASE |USER_DESC |
RELEASE |USER_DESC_STATE |
RELEASE |VAULT_DATA |
RELEASE |VAULT_RESOURCES |
RELEASE |VP_DESC |
RELEASE |VP_DESC_LOD |
RELEASE |WEB_MOD |
RELEASE |WEB_MOD_UATTR |
RELEASE |WF_DATA |
RELEASE |WF_DEF |
RELEASE |WF_DEF_LOD |
RELEASE |WF_TP_MAPPING |
RELEASE |WF_TP_MAPPING_DD |
RELEASE |WPS_SCDLMGR |
RELEASE |WPS_SCDLMPR |
RELEASE |WPS_SCDTASK |
RELEASE |WPS_SCDTREG |
RELEASE |WP_SEARCH_REG |
RELEASE |WSRP_PROD |
RELEASE |WSRP_PROD_DD |
RELEASE |WSRP_PROD_LOD |
RELEASE |WSRP_PROD_PORT |
RELEASE |WSRP_PROD_UATTR |
To view structure of any table in RELEASE db {Similarly explore remaining dbs}:-
Local database
For proof-of-concept, demonstrations, and development environments, you can use a local database. You can install the database management software on the same server as WebSphere Portal Express. When the database is on the same server as the portal, it is referred to as a local database. Using a local database can make administering your environment easier. However, this setup is best used for proof-of-concept deployments only. A local database competes for server resources with your portal.
In the topology diagram, all of the portal database domains are stored in one database, wpsdb.
Remote database
For normal load balancing, you can use one or more remote databases. You can install the database management software on a different server fromWebSphere Portal Express. When the database is on a different physical server than the portal, it is referred to as a remote database. Using a remote database can provide performance benefits, depending on the speed of the network.
When multiple lines of production are involved and each line of production is implemented as a cluster of servers, share portal database domains. Each database domain can be placed on a separate database for efficient maintenance. The release and JCR portal database domains cannot be shared.
The topology diagram is similar to the local database topology. The main difference is that the database is on a different server than the portal.
High capacity and availability
For high capacity load balancing, use one or multiple remote databases. When you deploy the portal in a large-scale, high-demand environment, you can dedicate a server specifically for database transactions. As more users access the portal, the portal application becomes database intensive. Database activity can take up processor resources and disk I/O time. Separating the database from the server that the portal is running on increases its capacity.
In the topology diagram, there is a remote database server with four databases. The JCR and release portal database domains have unique databases. They cannot be shared. Also, the release portal database domain cannot be taken offline. Another database contains the customization and community portal database domains. Finally, there is another database to contain the feedback and likeminds portal database domains.
Database schema names :-
The table includes the default names that are used in the Configuration Wizard. Replace these values with the values in your environment; schema names must be different when the database is shared.
All table spaces will be approximately 2.8 GB by default. The size will increase with the use of the Java Content Repository function.
For some database software, such as DB2, the database name cannot exceed 8 characters and can contain letters and numbers only.
| Application | Database name | Space required |
|---|---|---|
| WebSphere® Portal Express®
Used for the portal (at a minimum) or to hold all data. Stores information about user customization, such as pages, and user profile and login information.
|
| Depends on the number of users and portal objects, such as pages and portlets. |
| Personalization,Web Content Manager
Contains documents, personalization rules, personalization campaigns, and document library configuration information.
|
| Depends on the number and size of Personalization rules and campaigns, and the number and size of items and elements that are created in.Web Content Manager |
| Feedback
Contains the information that is logged by your website for analysis of site activity and generating reports.
|
| Depends on the amount of traffic to the site. The amount of data that is logged per login-enabled page can vary. |
| LikeMinds
Contains the recommendations that are displayed to users. The LikeMinds application analyzes the visitor's interactions with your website are and generates predictions.
|
| Depends on the amount of traffic to the site. |
Database users :-
The table indicates types of objects that are owned by each user. The architecture allows each of the following users to exist in the same database. All table spaces are approximately 2.8 GB by default. The size increases with the use of Java Content Repository.
| Application | Database user | Function |
|---|---|---|
| WebSphere Portal Express |
| Core user who owns approximately 230 tables, used for WebSphere Portal Express core objects, which include tables that store the user customizations that are made to pages. |
| Java Content Repository |
| Java Content Repository user who owns approximately 100 tables. The number might be higher depending on usage. |
| Feedback |
| Feedback user who owns approximately 50 tables used for logging site and personalization usage. |
| LikeMinds |
| LikeMinds user who owns approximately 15 tables used to hold the website usage analysis routines and recommendation text. |
Use case scenario 1:-
A customer had deleted the custom unique name for the portal content root node. This broke everything. When attempting to access the portal, only white screens loaded in the browser. We had to use the following insert statement to manually put the record back in place and recover portal access:
Solution :-
INSERT INTO <RELEASE>.UNIQUE_NAME (UNIQUE_NAME, RES_TYPE, OID, VP_DESC_OID)
VALUES ('wps.content.root',6,X'00000000000000000000000000000000000a' ,X'00000000000000000000d93b44a505010000');
No comments:
Post a Comment