Tuesday, 28 January 2014

Migration of Database from derby to db2 in IBM Websphere Portal Server


For RELEASE DB
----------------
CREATE DB release using codeset UTF-8 territory us PAGESIZE 8192
UPDATE DB CFG FOR release USING applheapsz 4096
UPDATE DB CFG FOR release USING app_ctl_heap_sz 1024
UPDATE DB CFG FOR release USING stmtheap 32768
UPDATE DB CFG FOR release USING dbheap 2400
UPDATE DB CFG FOR release USING locklist 1000
UPDATE DB CFG FOR release USING logfilsiz 4000
UPDATE DB CFG FOR release USING logprimary 12
UPDATE DB CFG FOR release USING logsecond 20
UPDATE DB CFG FOR release USING logbufsz 32
UPDATE DB CFG FOR release USING avg_appls 5
UPDATE DB CFG FOR release USING locktimeout 30
UPDATE DB CFG FOR release using AUTO_MAINT off

For COMMUNITY DB
----------------
CREATE DB commun using codeset UTF-8 territory us PAGESIZE 8192
UPDATE DB CFG FOR commun USING applheapsz 4096
UPDATE DB CFG FOR commun USING app_ctl_heap_sz 1024
UPDATE DB CFG FOR commun USING stmtheap 32768
UPDATE DB CFG FOR commun USING dbheap 2400
UPDATE DB CFG FOR commun USING locklist 1000
UPDATE DB CFG FOR commun USING logfilsiz 4000
UPDATE DB CFG FOR commun USING logprimary 12
UPDATE DB CFG FOR commun USING logsecond 20
UPDATE DB CFG FOR commun USING logbufsz 32
UPDATE DB CFG FOR commun USING avg_appls 5
UPDATE DB CFG FOR commun USING locktimeout 30
UPDATE DB CFG FOR commun using AUTO_MAINT off

For CUSTOMIZATION DB
---------------------

CREATE DB custom using codeset UTF-8 territory us PAGESIZE 8192
UPDATE DB CFG FOR custom USING applheapsz 4096
UPDATE DB CFG FOR custom USING app_ctl_heap_sz 1024
UPDATE DB CFG FOR custom USING stmtheap 32768
UPDATE DB CFG FOR custom USING dbheap 2400
UPDATE DB CFG FOR custom USING locklist 1000
UPDATE DB CFG FOR custom USING logfilsiz 4000
UPDATE DB CFG FOR custom USING logprimary 12
UPDATE DB CFG FOR custom USING logsecond 20
UPDATE DB CFG FOR custom USING logbufsz 32
UPDATE DB CFG FOR custom USING avg_appls 5
UPDATE DB CFG FOR custom USING locktimeout 30
UPDATE DB CFG FOR custom using AUTO_MAINT off


Creating JCR DB
---------------
CREATE DB jcrdb using codeset UTF-8 territory us PAGESIZE 8192;
UPDATE DB CFG FOR jcrdb USING applheapsz 4096;
UPDATE DB CFG FOR jcrdb USING app_ctl_heap_sz 1024;
UPDATE DB CFG FOR jcrdb USING stmtheap 32768;
UPDATE DB CFG FOR jcrdb USING dbheap 2400;
UPDATE DB CFG FOR jcrdb USING locklist 1000;
UPDATE DB CFG FOR jcrdb USING logfilsiz 4000;
UPDATE DB CFG FOR jcrdb USING logprimary 12;
UPDATE DB CFG FOR jcrdb USING logsecond 20;
UPDATE DB CFG FOR jcrdb USING logbufsz 32;
UPDATE DB CFG FOR jcrdb USING avg_appls 5;
UPDATE DB CFG FOR jcrdb USING locktimeout 30;
UPDATE DB CFG FOR jcrdb using AUTO_MAINT off;

Creating FDBKDB DB
------------------
CREATE DB fdbkdb using codeset UTF-8 territory us PAGESIZE 8192; 
UPDATE DB CFG FOR fdbkdb USING applheapsz 4096;
UPDATE DB CFG FOR fdbkdb USING app_ctl_heap_sz 1024;
UPDATE DB CFG FOR fdbkdb USING stmtheap 32768;
UPDATE DB CFG FOR fdbkdb USING dbheap 2400;
UPDATE DB CFG FOR fdbkdb USING locklist 1000;
UPDATE DB CFG FOR fdbkdb USING logfilsiz 4000;
UPDATE DB CFG FOR fdbkdb USING logprimary 12;
UPDATE DB CFG FOR fdbkdb USING logsecond 20;
UPDATE DB CFG FOR fdbkdb USING logbufsz 32;
UPDATE DB CFG FOR fdbkdb USING avg_appls 5;
UPDATE DB CFG FOR fdbkdb USING locktimeout 30;
UPDATE DB CFG FOR fdbkdb using AUTO_MAINT off;


Creating LMDB DB
-----------------
CREATE DB lmdb using codeset UTF-8 territory us PAGESIZE 8192;
UPDATE DB CFG FOR lmdb USING applheapsz 4096;
UPDATE DB CFG FOR lmdb USING app_ctl_heap_sz 1024;
UPDATE DB CFG FOR lmdb USING stmtheap 32768;
UPDATE DB CFG FOR lmdb USING dbheap 2400;
UPDATE DB CFG FOR lmdb USING locklist 1000;
UPDATE DB CFG FOR lmdb USING logfilsiz 4000;
UPDATE DB CFG FOR lmdb USING logprimary 12;
UPDATE DB CFG FOR lmdb USING logsecond 20;
UPDATE DB CFG FOR lmdb USING logbufsz 32;
UPDATE DB CFG FOR lmdb USING avg_appls 5;
UPDATE DB CFG FOR lmdb USING locktimeout 30;
UPDATE DB CFG FOR lmdb using AUTO_MAINT off;

Run the following command. This is required only for jcrdb (Java Content Repository Database)
CONNECT TO jcrdb USER db2admin USING passw0rd;
db2 "CREATE BUFFERPOOL ICMLSFREQBP4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSVOLATILEBP4 SIZE 16000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSMAINBP32 SIZE 16000 PAGESIZE 32 K"
db2 "CREATE BUFFERPOOL CMBMAIN4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE REGULAR TABLESPACE ICMLFQ32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLFQ32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMLNF32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLNF32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMVFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMVFQ04') BUFFERPOOL ICMLSVOLATILEBP4"
db2 "CREATE REGULAR TABLESPACE ICMSFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMSFQ04') BUFFERPOOL ICMLSFREQBP4"
db2 "CREATE REGULAR TABLESPACE CMBINV04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('CMBINV04') BUFFERPOOL CMBMAIN4"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('icmlssystspace32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE4 PAGESIZE 4 K MANAGED BY SYSTEM USING ('icmlssystspace4') BUFFERPOOL ICMLSVOLATILEBP4"
db2 "CREATE USER TEMPORARY TABLESPACE ICMLSUSRTSPACE4 PAGESIZE 4 K MANAGED BY SYSTEM USING ('icmlsusrtspace4') BUFFERPOOL ICMLSVOLATILEBP4"
db2 "UPDATE DB CFG FOR jcrdb USING DFT_QUERYOPT 2"
db2 "UPDATE DB CFG FOR jcrdb USING PCKCACHESZ 16384"
db2 "DISCONNECT jcrdb"
db2 "TERMINATE"


From the /ConfigEngine/properties directory, make a backup of the following files:
        - wkplc.properties
        - wkplc_dbtype.properties
        - wkplc_dbdomin.properties

Edit the wkplc_dbtype.properties file and make the following changes:
         db2.DbDriver=com.ibm.db2.jcc.DB2Driver
         db2.DbLibrary=E:/IBM/SQLIB/java/db2jcc.jar;E:/IBM/SQLIB/java/db2jcc_license_cu.jar
         Note: [need to change the semi colon to colon for Unix servers]

- Edit the wkplc_dbdomain.properties file and make the following changes:
        - feedback.DbType=db2
        - feedback.DbName=fdbkdb   
        - feedback.DbSchema=FEEDBACK
        - feedback.DataSourceName=wpdbDS_fdbk
        - feedback.DbUrl=jdbc:db2://localhost:50000/fdbkdb:returnAlias=0;
        - feedback.DbUser=db2admin
        - feedback.DbPassword=db2admin
        - feedback.DBA.DbUser=dasusr1
        - feedback.DBA.DbPassword=passw0rd

        - likeminds.DbType=db2
        - likeminds.DbName=lmdb
        - likeminds.DbSchema=likeminds
        - likeminds.DataSourceName=wpdbDS_lmdb
        - likeminds.DbUrl=jdbc:db2://localhost:50000/lmdb:returnAlias=0;  
        - likeminds.DbUser=db2admin
        - likeminds.DbPassword=db2admin
        - likeminds.DBA.DbUser=dasusr1
        - likeminds.DBA.DbPassword=passw0rd

        - release.DbType=db2
        - release.DbName= release
        - release.DbSchema=release
        - release.DataSourceName=wpdbDS_release
        - release.DbUrl=jdbc:db2://localhost:50000/release:returnAlias=0;
        - release.DbUser=db2admin
        - release.DbPassword=db2admin
        - release.DBA.DbUser=dasusr1
        - release.DBA.DbPassword=passw0rd

        - community.DbType=db2
        - community.DbName=commun
        - community.DbSchema=community
        - community.DataSourceName=wpdbDS_commun
        - community.DbUrl=jdbc:db2://localhost:50000/commun:returnAlias=0;
        - community.DbUser=db2admin
        - community.DbPassword=db2admin
        - community.DBA.DbUser=dasusr1
        - community.DBA.DbPassword=passw0rd


        - customization.DbType=db2
        - customization.DbName=custom
        - customization.DbSchema=customization
        - customization.DataSourceName=wpdbDS_custom
        - customization.DbUrl=jdbc:db2://localhost:50000/custom:returnAlias=0;
        - customization.DbUser=db2admin
        - customization.DbPassword=db2admin
        - customization.DBA.DbUser=dasusr1
        - customization.DBA.DbPassword=passw0rd

        - jcr.DbType=db2
        - jcr.DbName=jcrdb
        - jcr.DbSchema=jcr
        - jcr.DataSourceName=wpdbDS_jcrdb
        - jcr.DbUrl=jdbc:db2://localhost:50000/jcrdb:returnAlias=0;
        - jcr.DbUser=db2admin
        - jcr.DbPassword=db2admin
        - jcr.DBA.DbUser=dasusr1
        - jcr.DBA.DbPassword=passw0rd
       

Update the following property in the file wkplc.properties.
        - For WasPassword, type the password for the WebSphere Application Server security authentication used in your environment.
        - PortalAdminPwd=passw0rd
        - WasPassword=passw0rd
    - Copy the following files from the WebSphere Portal server to a temporary directory on the DB2 server
        - <PortalServer>/jcr/wp.content.repository.install/lib/wp.content.repository.install.jar
        - <wp_profile root>/PortalServer/jcr/config/registerCollationUDFTemplate.sql
    - Open a terminal window and change directories to
        - <db2 instance home>/sqllib/function
        - <db2 instance home>/sqllib/java/jdk/bin/jar -xvf <temporary location>/wp.content.repository.install.jar
    - Edit the <temporary location>/registerCollationUDFTemplate.sql file in a text editor
        - 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'.
        - Save the registerCollationUDFTemplate.sql file
    - Connect to the JCR database by executing the following command in a terminal window
        - db2 connect to jcrdb user db2admin using password
    - From the same terminal window, execute the SQL script by running the following command
        - db2 -tvf <temporary location>/registerCollationUDFTemplate.sql
        - Disconnect from the JCRDB and restart the DB2 instance
    - Execute the following ConfigEngine scripts to validate the database properties:
        - ./ConfigEngine.sh validate-database -DWasPassword=passw0rd
    - Execute the following ConfigEngine script to transfer the database from Derby to DB2
        - ./ConfigEngine.sh database-transfer -DWasPassword=passw0rd

       
From the DB2 Server
        - connect to the release database and execute the following command
            - db2 reorgchk update statistics on table all > reorgchk.txt
        - Review the reorgchk.txt file and note any table names that have an * set in the REORG column
        - Execute the following command for each table name
            - db2 reorg table <tablename>
        - After you have completed running reorg against all the marked tables for this database, execute the following DB2 commands to rebind the database
            - db2 terminate
            - db2rbind <database name> -l db2rbind.out -u <db2admin ID> -p <db2admin password>
        - Repeat the above 4 steps for community, customization, jcr, likeminds and feedback.
Posted by Dillibabu at 04:57 0 comments

Tuesday, 21 January 2014

Storing PortletPreference in serveresource



·         By default, portletpreference can be stored using process action /serveResource method in the portlet class.

·        For storing portlet preference using serve resource method, the following setting needs be done in Portal server in additional:     

  •  Assign all authenticated  portal user to  privilege user  for Portlet which uses serveResource for storing preferences 
    • Click"Resource Permissions" and "Portlets"
  •     Search "Test" in search box



  • Click"Privileged user" and Select "All Authenticated Portal users"
  •     Click "Add". Add " All Authenticated Portal users"
  •   
  • Assign all authenticated  portal user to  privilege user  for Web module which uses serveResource for storing preferences  
  •  
  •     Click "Resource Permissions" and "Web Modules"
  •  
  •     Search "Test" in  Search Box.Click" Access" Icon
  •     Click" Priveileged user" Icon
    •   Click "Add". Add " All Authenticated Portal users"
  • Assign all authenticated portal user to privilege user for page which contains portlet (which uses serveResource for storing preferences).  

  •   Click "Resource Permissions" and "Pages"
    • Search "Test" in Searchbox. Click "Access" Icon

     
        Click "Privileged user" . 
        Click "Add". Add " All Authenticated Portal users"
Posted by Dillibabu at 04:35 0 comments
Subscribe to: Posts (Atom)