Monday, 8 July 2013

IBM WebSpher Portal 7.0 Database Migration



IBM WebSpher Portal 7.0 Database Migration


This document includes my Experience how to transfer Portal Derby Data base to Db2 9.7.2 in Windows Environment .
This document not includes :
  1. Installation of the Portal server 7.0
  2. Installation of the Db2 9.7.2


Migration of Derby 9.7.2

Default status of the portal database status is

It have one database by default its name is wpsdb and it have 6 schemas .
• Release, Likeminds, Feedback: These schemas store data of Pages, Portlets, Portlet instances, Themes, Templates, Personalization rules and Policies. This data is not
modified during the portal runtime.
• Customization: This schema stores information specific to users (for example
PortletData).
• Community, Jcr: These schemas store data of shared documents and resources. This
data will be modified during runtime.



Note point for DB2
Before installing DB2 for IBM i, log in with a user ID that has administrative authority.
This user should have the following specifications:
Belong to the local Administrator group
Act as part of the operating system
Have permissions to create a token object
Have permissions to replace a process level token
To edit user rights:
For the first two specifications: Click Start -> Programs -> Administrative Tools -> Computer Management -> Local Users and Groups.

For the last four specifications: Click Start -> Programs -> Administrative Tools -> Local Security Policy. Then, click Local Policies -> User Rights Assignment.

I am mentioning this because this was the most critical task for me to handling with DB2 user and its permissi


  1. You can transfer these schema to all new database or just create one data base and create 6 schema and transfer the data in that .
  2. Portal Ends configuration Ends
    1. Edit the WebSphere\wp_profile\ConfigEngine\properties\wkplc_dbtype.properties file and make the following changes:
      1. db2.DbLibrary=C:/IBM/DB2/java/db2jcc4.jar;C:/IBM/DB2/java/db2jcc_license_cu.jar
      2. db2.JdbcProviderName=wpdbJDBC_db2
      3. here location of these jar file may differ according to your DB2 installation file . These file must be present in you local system if your Db2 at remote location .
    2. Edit the WebSphere\wp_profile\ConfigEngine\properties\wkplc_dbdomain.properties file and make the following changes:
      1. feedback schema
        1. feedback.DbType=db2
        2. feedback.DbName=wps7db
        3. feedback.DbSchema=FEEDBACK
        4. feedback.DataSourceName=wpdbDS_db2
        5. feedback.DbUrl=jdbc:db2://mydbserver.ibm.com:50000/wps7db:returnAlias=0;
        6. feedback.DbUser=db2admin
        7. feedback.DbPassword=Passw0rd
        8. feedback.DbRuntimeUser=db2admin
        9. feedback.DbRuntimePassword=Passw0rd
      2. likeminds schema
        1. likeminds.DbType=db2
        2. likeminds.DbName=wps7db
        3. likeminds.DbSchema=likeminds
        4. likeminds.DataSourceName=wpdbDS_db2
        5. likeminds.DbUrl=jdbc:db2://mydbserver.ibm.com:50000/wps7db:returnAlias=0;
        6. likeminds.DbUser=db2admin
        7. likeminds.DbPassword=Passw0rd
        8. likeminds.DbRuntimeUser=db2admin
        9. likeminds.DbRuntimePassword=Passw0rd
      3. release schema
        1. release.DbType=db2
        2. release.DbName=wps7db
        3. release.DbSchema=release
        4. release.DataSourceName=wpdbDS_db2
        5. release.DbUrl=jdbc:db2://mydbserver.ibm.com:50000/wps7db:returnAlias=0;
        6. release.DbUser=db2admin
        7. release.DbPassword=Passw0rd
        8. release.DbRuntimeUser=db2admin
        9. release.DbRuntimePassword=Passw0rd
      4. community schema
        1. community.DbType=db2
        2. community.DbName=wps7db
        3. community.DbSchema=community
        4. community.DataSourceName=wpdbDS_db2
        5. community.DbUrl=jdbc:db2://mydbserver.ibm.com:50000/wps7db:returnAlias=0;
        6. community.DbUser=db2admin
        7. community.DbPassword=Passw0rd
        8. community.DbRuntimeUser=db2admin
        9. community.DbRuntimePassword=Passw0rd
      5. customization schema
        1. customization.DbType=db2
        2. customization.DbName=wps7db
        3. customization.DbSchema=customization
        4. customization.DataSourceName=wpdbDS_db2
        5. customization.DbUrl=jdbc:db2://mydbserver.ibm.com:50000/wps7db:returnAlias=0;
        6. customization.DbUser=db2admin
        7. customization.DbPassword=Passw0rd
        8. customization.DbRuntimeUser=db2admin
        9. customization.DbRuntimePassword=Passw0rd
      6. jcr schema
        1. jcr.DbType=db2
        2. jcr.DbName=wps7db
        3. jcr.DbSchema=jcr
        4. jcr.DataSourceName=wpdbDS_db2
        5. jcr.DbUrl=jdbc:db2://mydbserver.ibm.com:50000/wps7db:returnAlias=0;
        6. jcr.DbUser=db2admin
        7. jcr.DbPassword=Passw0rd
        8. jcr.DbRuntimeUser=db2admin
        9. jcr.DbRuntimePassword=Passw0rd
  3. Some time also require to change the following properties
    1. customization.DBA.DbUser=db2inst1
    2. customization.DBA.DbPassword=password

  1. (Optional) Edit the following properties in /ConfigEngine/properties/wkplc_comp.properties if you intend to transfer the Portal databases from any database other than Derby. NOTE: These values by default are set for the out-of-the-box derby configuration. If you are configuring Portal for an external database for the first time, you should NOT modify these properties. However if you have already configured Portal for another database, such as Oracle, and you want to transfer to DB2, then update the source properties to reflect your current database settings.

source.domain.DbType - The type of database you are currently configured to. This is 'derby' by default.
source.domain.DbName - The name of the database this domain is currently using.
source.domain.DbSchema - The current schema identifier for objects within the database for this domain.
source.domain.DataSourceName - The name of the datasource that is currently in use in the WebSphere Application Server configuration.
source.domain.DbUrl - The url currently used to access your database.
source.domain.DbUser - The name of the user that currently accesses this database.
source.domain.DbPassword - The password of the source DbUser.
in this guide, we will not be editing these properties.

4.Notice mention that use form which you logging to the system and must have sufficient privilege as administrator
  1. Database level activity
    1. before migration you have to setup a database at DB2 end this can be achieved by two ways
      1. Automatic database setup :
        1. make sure you portal server and application server is stop .
        2. Stop both the server1 and WebSphere_Portal servers:
          1. ./stopServer.bat server1 -username admin_userid -password admin_password
          2. ./stopServer.bat WebSphere_Portal -username admin_userid -password admin_password
        3. create database in Db2 say wps7db
        4. Execute step 5 or 6
        5. Automatically Creating Users, Granting Permissions, and Creating Java Content Repository Table Spaces
            This topic provides instructions on automatically setting up your database using the ConfigEngine task to create users, grant permissions, and create Java Content Repository table spaces.
          1. You must create your DB2 for IBM i databases before running the configuration task in this topic.
          2. As an alternative to automatically setting up the database, you can manually set up your database by referring to the link in the related tasks section of this topic.
          3. (In command prompt )Change to the directory wp_profile_root/ConfigEngine
          4. To create the database users, type the following command:
          5. Note:
            1. The task setup-database assigns the minimum database privileges to the database configuration and runtime database users.
            2. ./ConfigEngine.sh setup-database -DwasPassword=password





        1. here I put query for different data base so modify accordingly
          1. Run the following commands on the DB2 server system to configure the DB2 database instance:
          2. here note that query for all different data base so make changes accordingly in your properties file .
            1. DB2 Version 9
            2. db2set DB2_RR_TO_RS=YES
            3. db2set DB2_EVALUNCOMMITTED=YES
            4. db2set DB2_INLIST_TO_NLJN=YES
            5. db2 "UPDATE DBM CFG USING query_heap_sz 32768"
            6. db2 "UPDATE DBM CFG USING maxagents 500"
            7. db2 "UPDATE DBM CFG USING sheapthres 50000"


          1. Run the following commands on the DB2 server system to create the necessary databases:
          2. Replace dbname with the actual name of the database.
          3. I. create release db:
            1. db2 "CREATE DB release using codeset UTF-8 territory us PAGESIZE 8192"
            2. db2 "UPDATE DB CFG FOR release USING applheapsz 4096"
            3. db2 "UPDATE DB CFG FOR release USING app_ctl_heap_sz 1024"
            4. db2 "UPDATE DB CFG FOR release USING stmtheap 32768"
            5. db2 "UPDATE DB CFG FOR release USING dbheap 2400"
            6. db2 "UPDATE DB CFG FOR release USING locklist 1000"
            7. db2 "UPDATE DB CFG FOR release USING logfilsiz 4000"
            8. db2 "UPDATE DB CFG FOR release USING logprimary 12"
            9. db2 "UPDATE DB CFG FOR release USING logsecond 20"
            10. db2 "UPDATE DB CFG FOR release USING logbufsz 32"
            11. db2 "UPDATE DB CFG FOR release USING avg_appls 5"
            12. db2 "UPDATE DB CFG FOR release USING locktimeout 30"
            13. db2 "UPDATE DB CFG FOR release using AUTO_MAINT off"
          4. II. create community db
            1. db2 "CREATE DB community using codeset UTF-8 territory us PAGESIZE 8192"
            2. db2 "UPDATE DB CFG FOR community USING applheapsz 4096"
            3. db2 "UPDATE DB CFG FOR community USING app_ctl_heap_sz 1024"
            4. db2 "UPDATE DB CFG FOR community USING stmtheap 32768"
            5. db2 "UPDATE DB CFG FOR community USING dbheap 2400"
            6. db2 "UPDATE DB CFG FOR community USING locklist 1000"
            7. db2 "UPDATE DB CFG FOR community USING logfilsiz 4000"
            8. db2 "UPDATE DB CFG FOR community USING logprimary 12"
            9. db2 "UPDATE DB CFG FOR community USING logsecond 20"
            10. db2 "UPDATE DB CFG FOR community USING logbufsz 32"
            11. db2 "UPDATE DB CFG FOR community USING avg_appls 5"
            12. db2 "UPDATE DB CFG FOR community USING locktimeout 30"
            13. db2 "UPDATE DB CFG FOR commun using AUTO_MAINT off"
          5. III. creating customization db
            1. db2 "CREATE DB customization using codeset UTF-8 territory us PAGESIZE 8192"
            2. db2 "UPDATE DB CFG FOR customization USING applheapsz 4096"
            3. db2 "UPDATE DB CFG FOR customization USING app_ctl_heap_sz 1024"
            4. db2 "UPDATE DB CFG FOR customization USING stmtheap 32768"
            5. db2 "UPDATE DB CFG FOR customization USING dbheap 2400"
            6. db2 "UPDATE DB CFG FOR customization USING locklist 1000"
            7. db2 "UPDATE DB CFG FOR customization USING logfilsiz 4000"
            8. db2 "UPDATE DB CFG FOR customization USING logprimary 12"
            9. db2 "UPDATE DB CFG FOR customization USING logsecond 20"
            10. db2 "UPDATE DB CFG FOR customization USING logbufsz 32"
            11. db2 "UPDATE DB CFG FOR customization USING avg_appls 5"
            12. db2 "UPDATE DB CFG FOR customization USING locktimeout 30"
            13. db2 "UPDATE DB CFG FOR customization using AUTO_MAINT off"
          6. IV. creating jcr db
            1. db2 "CREATE DB jcrdb using codeset UTF-8 territory us PAGESIZE 8192"
            2. db2 "UPDATE DB CFG FOR jcr USING applheapsz 4096"
            3. db2 "UPDATE DB CFG FOR jcr USING app_ctl_heap_sz 1024"
            4. db2 "UPDATE DB CFG FOR jcr USING stmtheap 32768"
            5. db2 "UPDATE DB CFG FOR jcr USING dbheap 2400"
            6. db2 "UPDATE DB CFG FOR jcr USING locklist 1000"
            7. db2 "UPDATE DB CFG FOR jcr USING logfilsiz 4000"
            8. db2 "UPDATE DB CFG FOR jcr USING logprimary 12"
            9. db2 "UPDATE DB CFG FOR jcr USING logsecond 20"
            10. db2 "UPDATE DB CFG FOR jcr USING logbufsz 32"
            11. db2 "UPDATE DB CFG FOR jcr USING avg_appls 5"
            12. db2 "UPDATE DB CFG FOR jcr USING locktimeout 30"
            13. db2 "UPDATE DB CFG FOR jcr using AUTO_MAINT off"
          7. V. creating feedback db
            1. db2 "CREATE DB fdbkdb using codeset UTF-8 territory us PAGESIZE 8192"
            2. db2 "UPDATE DB CFG FOR feedback USING applheapsz 4096"
            3. db2 "UPDATE DB CFG FOR feedback USING app_ctl_heap_sz 1024"
            4. db2 "UPDATE DB CFG FOR feedback USING stmtheap 32768"
            5. db2 "UPDATE DB CFG FOR feedback USING dbheap 2400"
            6. db2 "UPDATE DB CFG FOR feedback USING locklist 1000"
            7. db2 "UPDATE DB CFG FOR feedback USING logfilsiz 4000"
            8. db2 "UPDATE DB CFG FOR feedback USING logprimary 12"
            9. db2 "UPDATE DB CFG FOR feedback USING logsecond 20"
            10. db2 "UPDATE DB CFG FOR feedback USING logbufsz 32"
            11. db2 "UPDATE DB CFG FOR feedback USING avg_appls 5"
            12. db2 "UPDATE DB CFG FOR feedback USING locktimeout 30"
            13. db2 "UPDATE DB CFG FOR feedback using AUTO_MAINT off"
          8. VI. creating likeminds db
            1. db2 "CREATE DB lmdb using codeset UTF-8 territory us PAGESIZE 8192"
            2. db2 "UPDATE DB CFG FOR likeminds USING applheapsz 4096"
            3. db2 "UPDATE DB CFG FOR likeminds USING app_ctl_heap_sz 1024"
            4. db2 "UPDATE DB CFG FOR likeminds USING stmtheap 32768"
            5. db2 "UPDATE DB CFG FOR likeminds USING dbheap 2400"
            6. db2 "UPDATE DB CFG FOR likeminds USING locklist 1000"
            7. db2 "UPDATE DB CFG FOR likeminds USING logfilsiz 4000"
            8. db2 "UPDATE DB CFG FOR likeminds USING logprimary 12"
            9. db2 "UPDATE DB CFG FOR likeminds USING logsecond 20"
            10. db2 "UPDATE DB CFG FOR likeminds USING logbufsz 32"
            11. db2 "UPDATE DB CFG FOR likeminds USING avg_appls 5"
            12. db2 "UPDATE DB CFG FOR likeminds USING locktimeout 30"
            13. db2 "UPDATE DB CFG FOR likeminds using AUTO_MAINT off"
          9. Create the following tablespaces in your JCR database. If you are using one database for all domains, then create these tablespaces in that database:
            1. is step is only required for the IBM Java Content Repository database (jcrdb).
            2. * jcrdb is the name of the database used to store user data and objects
            3. * jcr is the database user for jcrdb
            4. Note: This value can be replaced with any ID that has administrative authority.
              * dbpassword is the password for jcrdb
            5. Db2 Scripts
              1. db2 "CONNECT TO jcrdb USER db2inst1 USING password"
              2. db2 "CREATE BUFFERPOOL ICMLSFREQBP4 SIZE 1000 PAGESIZE 4 K"
              3. db2 "CREATE BUFFERPOOL ICMLSVOLATILEBP4 SIZE 8000 PAGESIZE 4 K"
              4. db2 "CREATE BUFFERPOOL ICMLSMAINBP32 SIZE 8000 PAGESIZE 32 K"
              5. db2 "CREATE BUFFERPOOL CMBMAIN4 SIZE 1000 PAGESIZE 4 K"
              6. db2 "CREATE REGULAR TABLESPACE ICMLFQ32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLFQ32') BUFFERPOOL ICMLSMAINBP32"
              7. db2 "CREATE REGULAR TABLESPACE ICMLNF32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLNF32') BUFFERPOOL ICMLSMAINBP32"
              8. db2 "CREATE REGULAR TABLESPACE ICMVFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMVFQ04') BUFFERPOOL ICMLSVOLATILEBP4"
              9. db2 "CREATE REGULAR TABLESPACE ICMSFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMSFQ04') BUFFERPOOL ICMLSFREQBP4"
              10. db2 "CREATE REGULAR TABLESPACE CMBINV04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('CMBINV04') BUFFERPOOL CMBMAIN4"
              11. db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('icmlssystspace32') BUFFERPOOL ICMLSMAINBP32"
              12. db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE4 PAGESIZE 4 K MANAGED BY SYSTEM USING ('icmlssystspace4') BUFFERPOOL ICMLSVOLATILEBP4"
              13. db2 "DISCONNECT jcrdb"
              14. db2 "TERMINATE"









        1. Configure JCR Collation(This section is optional.)
          1. This section is only needed if you are using Web Content Management (WCM). These steps enable JCR Collation support, designed to help improve performance with WCM. If you are not using WCM, it will not hurt to complete these steps, but again, it is not required.
          2. Copy the following files from the WebSphere Portal server to a temporary directory on the DB2 server:
          3. <PortalServer>/jcr/prereq.jcr/config/collation.jar
          4. <PortalServer>/jcr/prereq.jcr/config/registerCollationUDFTemplate.sql
          5. From the DB2 server, open a terminal window and change directories to:
          6. <db2_instance_home>/sqllib/function
          7. From the DB2 server, Execute the following command:
          8. jar -xvf <temporary location>/collation.jar
          9. NOTE: In order for the above command to work, you must have java installed on the DB2 server and the JAVA_HOME environment variable set to the java root directory.
          10. From the DB2 server, edit the <temporary location>/registerCollationUDFTemplate.sql file in a text editor.
          11. Change all SCHEMA references in this file to the value you set for jcr.DbSchema in wkplc_dbdomain.properties. In this case, the schema value is 'jcr'.
          12. Change the following line:
            1. "VALUE VARCHAR(32672)," to "VALUE VARCHAR(100),"
            "RETURNS VARCHAR(32672) FOR BIT DATA" to "RETURNS VARCHAR(100) FOR BIT DATA"
          13. Save the registerCollationUDFTemplate.sql file
          14. From the DB2 server, connect to the JCR database by executing the following command in a terminal window:

          1. db2 connect to jcr user db2inst1 using password

          1. From the same terminal window, execute the SQL script by running the following command:
          2. db2 -tvf <temporary location>/registerCollationUDFTemplate.sql
          3. Disconnect from the JCRDB and restart the DB2 instance.



        1. Configure WebSphere tPortal with DB2
          1. you can transfer data base by two ways you can use any one of them .1 by using confiEngine task (scripts ) or 2 by portal wizard follow one of these
            1. Scripts
              1. In this section, you will transfer the WebSphere Portal server data that exists in the current database (Derby by default) to the DB2 server. This is accomplished by a ConfigEngine script called 'database-transfer'. This script does all of the following:
                1. Connects to the target database (DB2 in this case) and drops all objects within them to ensure we have a clean database to transfer to.
                2. Creates the necessary tables, indexes, etc in the target databases for each domain.
                3. Transfers the data from the source database (Derby by default) to the target database (DB2) for each domain.
                4. Creates a JDBC Provider and datasources in the WebSphere Application Server configuration
                5. Maps the data sources to the appropriate applications. For example the release, customization, community and jcr datasources are mapped to wps.ear.
            2. From the Portal server, and from a terminal window (command prompt), change directories to wp_profile/ConfigEngine
            3. Execute the following ConfigEngine scripts to validate the database properties:
              1. Windows:
                1. ConfigEngine.bat validate-database-driver -DWasPassword=password
                2. ConfigEngine.bat validate-database-connection -DWasPassword=password
            4. UNIX:
              1. ./ConfigEngine.sh validate-database-driver -DWasPassword=password
              2. ./ConfigEngine.sh validate-database-connection -DWasPassword=password
            5. Execute the following ConfigEngine script to transfer the database from Derby to DB2:
            6. IMPORTANT: Do not run this script as a background process. The script may hang during the 'action-validate-database' sub-task if executed as a background process.
            7. Windows: ConfigEngine.bat database-transfer -DPortalAdminPwd=password -DWasPassword=password
            8. UNIX: ./ConfigEngine.sh database-transfer -DPortalAdminPwd=password -DwasPassword=password


Note:
To select specific database domains to transfer, modify the -DTransferDomainList specified in the command to include only the domains that you want to transfer. For example, to transfer only the JCR domain you can enter the following command:

./ConfigEngine.sh database-transfer -DTransferDomainList=jcr -DWasPassword=password

If you have been storing data in Apache® Derby for a long time, database transfer could fail with OutOfMemory exceptions. If database transfer fails, add the following property to the command in this step:

./ConfigEngine.sh database-transfer -DDbtJavaMaxMemory=1536M -DWasPassword=password

          1. By using wizard


Open the portal config wizard from C:\IBM\WebSphere\wp_profile\PortalServer\wizard\configwizard.bat, this wizard will walk you through all required configuration to transfer the db (most of the screens are self-explanatory).













































Data base transfer done .



  1. After the database-transfer completes, change directories to wp_profile/bin and execute the following command to start the Portal server:
    1. Windows: startServer.bat WebSphere_Portal
    2. UNIX: ./startServer.sh WebSphere_Portal

  1. Clustered environments only. If you have performed these steps in a clustered environment, you must also complete the following steps:
    1. Copy the wp_profile_root/PortalServer/jcr/lib/com/ibm/icm/icm.properties file from the primary node and replace the icm.properties file on each secondary node with the new file from the primary node.
    2. Restart the secondary cluster members.




  1. Updating the Database Configuration to Support Large Files (Web Content Management Only)If you are using Web Content Management, you must update the database configuration to support large files. Do this by setting thefullyMaterializeLobData property in the WebSphere Application Server administrative console.

    1. Note:
    2. You only need to perform these steps if you are using Web Content Management.
    3. Log in to the WebSphere Application Server administrative console.

    1. Click Resources -> JDBC -> Data sources.

    1. Select all scopes (the default setting) or select a specific cell, node, or node/server.

    1. Select the scope that corresponds to your instance of WebSphere Portal.

    1. The view refreshes.

    1. Select the name of the data source that is defined in wkplc_dbdomain.properties for the JCR database domain.

    1. The default data source is wpdbDS.

    1. Click Custom properties.

    1. Ensure that the fullyMaterializeLobData property is set to false.


  1. Optional: If you specified a runtime database user for the dbdomain.DbRuntimeUser parameter, that user must have sufficient database user privileges. To grant the database user privileges, choose either the manual steps or the command line steps:

    1. Complete these steps to manually grant database user privileges:

    1. Copy the appropriate template files to a work directory. Choose one of the following template files:

    1. createRuntimeRoleForDifferentSchema.sql if the name of the database user and the schema name are not the same.

    1. createRuntimeRoleForSameSchema.sql if the name of the database user and the schema name are the same.

    1. JCR database domain: For the JCR database domain, you must also copy grantExtendedPermissionsToRuntimeRole.sql.

    1. Locate these files in the following directories:

    1. PortalServer_root\base\wp.db.impl\config\templates\setupdb\dbms\domain

    1. PortalServer_root\pzn\prereq.pzn\config\templates\setupdb\dbms\domain

    1. Replace all placeholder values with the values as defined in wkplc_dbdomain.properties. Placeholder values are surrounded by the character @.

    1. Run these statements.

    1. Complete these steps to grant database user privileges with the ConfigEngine task:

    1. Ensure the database administrator user ID is specified for domain.DBA.DbUser inwp_profile_root\ConfigEngine\properties\wkplc_dbdomain.properties. For example, domain.DBA.DbUser=dbadmin.

    1. Run the following task:

    1. ./ConfigEngine.sh grant-runtime-db-user-privileges -DtransferDomainList=comma_separated_list_of_domains



















at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Very Impotent Links for Java and Portal development development

Jar Download for Spring  Maven Or Gradel Dependency for Spring