{"id":16,"date":"2013-10-04T17:36:00","date_gmt":"2013-10-04T17:36:00","guid":{"rendered":""},"modified":"2021-08-31T10:12:25","modified_gmt":"2021-08-31T09:12:25","slug":"foolproof-way-of-transferring-websphere-portal-from-derby-to-db2","status":"publish","type":"post","link":"https:\/\/www.srivittal.com\/wp\/?p=16","title":{"rendered":"Foolproof way of transferring WebSphere Portal From Derby to DB2"},"content":{"rendered":"
\n
Ok. I have seen several pieces of documentation on this. Lots of tutorials out there. However all of the sites and tutorials do not have all the steps mentioned clearly. I am trying to make this as comprehensive as possible. Try this on a Trial or test Portal Server, preferably a VM with a Snapshot taken. This is to ensure that in case something goes pear shaped, you can always go back to the working snapshot.<\/div>\n
<\/div>\n
For this exercise, I have two VM’s.<\/div>\n
<\/div>\n
portale.svil.com contains the WebSphere Portal 8.0.0.7 Server<\/div>\n
connections.svil.com contains the DB2 10.1 Database server<\/div>\n
<\/div>\n
I am not covering the basic installation of DB2 or of Portal itself here. I will try and cross link these articles later. For the moment, I am assuming that you already have Portal and DB2 installed, especially that DB2 is installed on a Remote DB2 server instead of on the same server as Portal Server.<\/div>\n
<\/div>\n
1. Copy the DB2 JDBC driver jar files from the DB2 server to the Portal server. <\/div>\n
<\/div>\n
cd \/opt\/IBM\/WebSphere\/wp_profile\/PortalServer<\/span><\/span><\/div>\n
mkdir dbdrivers<\/span><\/span><\/div>\n
cd dbdrivers<\/span><\/span><\/div>\n
scp db2inst1@connections.svil.com:\/opt\/IBM\/db2\/V10.1\/java\/* .<\/span><\/span><\/div>\n
<\/div>\n
Please Note: <\/b>DB2 usually installed under the lowercase ibm folder like \/opt\/ibm. I have changed this to the Uppercase IBM like \/opt\/IBM for consistency. The above commands are run within the Portal server.<\/div>\n
<\/div>\n
2. Take a backup copy of the properties files.<\/div>\n
<\/div>\n
cd \/opt\/IBM\/WebSphere\/wp_profile\/ConfigEngine\/properties<\/span><\/span><\/div>\n
cp wkplc_dbdomain.properties wkplc_dbdomain.properties.bak<\/span><\/span><\/div>\n
cp wkplc_dbtype.properties wkplc_dbtype.properties.bak<\/span><\/span><\/div>\n
cp wkplc.properties wkplc.properties.bak<\/span><\/span><\/div>\n
<\/div>\n
3. On the DB2 Server issue the following commands under the instance owner id<\/div>\n
<\/div>\n
db2 <\/span><\/span>create database wpsdb alias wpsdb PAGESIZE 32 K<\/span><\/div>\n
db2 <\/span>create role WP_JCR_CONFIG_USERS<\/span><\/span><\/div>\n
db2 <\/span>create role WP_PZN_CONFIG_USERS<\/span><\/span><\/div>\n
db2 <\/span>create role WP_BASE_CONFIG_USERS<\/span><\/span><\/div>\n
<\/div>\n
db2 <\/span>GRANT DBADM ON DATABASE TO ROLE WP_JCR_CONFIG_USERS<\/span>db2 <\/span>GRANT DBADM ON DATABASE TO ROLE WP_PZN_CONFIG_USERS<\/span>db2 <\/span>GRANT DBADM ON DATABASE TO ROLE WP_BASE_CONFIG_USERS
<\/span>db2 <\/span>GRANT ACCESSCTRL ON DATABASE TO ROLE WP_JCR_CONFIG_USERS<\/span>db2 <\/span>GRANT ACCESSCTRL ON DATABASE TO ROLE WP_PZN_CONFIG_USERS<\/span>db2 <\/span>GRANT ACCESSCTRL ON DATABASE TO ROLE WP_BASE_CONFIG_USERS
<\/span>db2 <\/span>GRANT DATAACCESS ON DATABASE TO ROLE WP_JCR_CONFIG_USERS <\/span>db2 <\/span>GRANT DATAACCESS ON DATABASE TO ROLE WP_PZN_CONFIG_USERS<\/span>db2 <\/span>GRANT DATAACCESS ON DATABASE TO ROLE WP_BASE_CONFIG_USERS
<\/span>db2 <\/span>GRANT SECADM ON DATABASE TO ROLE WP_JCR_CONFIG_USERS<\/span>db2 <\/span>GRANT SECADM ON DATABASE TO ROLE WP_PZN_CONFIG_USERS<\/span>db2 <\/span>GRANT SECADM ON DATABASE TO ROLE WP_BASE_CONFIG_USERS <\/span><\/span><\/div>\n

<\/span><\/span>db2 <\/span>GRANT ROLE WP_JCR_CONFIG_USERS TO GROUP DB2IADM1<\/span>db2 <\/span>GRANT ROLE WP_PZN_CONFIG_USERS TO GROUP DB2IADM1<\/span>db2 <\/span>GRANT ROLE WP_BASE_CONFIG_USERS TO GROUP DB2IADM1<\/span><\/span><\/div>\n
<\/div>\n
4. Edit the wkplc_dbdomain.properties file<\/div>\n
<\/div>\n
<\/div>\n
    \n
  • Change all .DbType= to .DbType=db2<\/something><\/li>\n<\/ul>\n
    <\/div>\n
      \n
    • Change all .DbURL= to .DbUrl=jdbc:db2:\/\/connections.svil.com:50000\/wpsdb:returnAlias=0;<\/something><\/li>\n<\/ul>\n
      <\/div>\n
        \n
      • Change all .DbUser= to .DbUser=db2inst1<\/something><\/li>\n<\/ul>\n
        <\/div>\n
          \n
        • Change all .DbPassword=ReplaceWithYourDbAdminPwd to .DbPassword=<\/actual><\/li>\n<\/ul>\n
          <\/div>\n
            \n
          • Change all .DBA.DbUser= to .DBA.DbUser=db2inst1<\/something><\/li>\n<\/ul>\n
            <\/div>\n
              \n
            • Change all .DBA.DbPassword=ReplaceWithYourDBAPwd to .DBA.DbPassword=<\/actual><\/li>\n<\/ul>\n
              <\/div>\n
              <\/div>\n
              5. Save and close the file<\/div>\n
              <\/div>\n
              6. Edit the wkplc_dbtype.properties file<\/div>\n
              <\/div>\n
              <\/div>\n
                \n
              • Change db2.DbLibrary= to db2.DbLibrary=\/opt\/IBM\/WebSphere\/wp_profile\/PortalServer\/dbdrivers\/db2jcc4.jar;\/opt\/IBM\/WebSphere\/wp_profile\/PortalServer\/dbdrivers\/db2jcc_license_cu.jar<\/something><\/li>\n<\/ul>\n
                <\/div>\n
                <\/div>\n
                7. Save and close the file<\/div>\n
                <\/div>\n
                8. Edit wkplc.properties<\/div>\n
                <\/div>\n
                Change WasPassword=ReplaceWithYourPassword to WasPassword=<\/actual><\/password><\/div>\n
                Change LTPAPassword=ReplaceWithYourPassword to LTPAPassword=<\/actual><\/password><\/div>\n
                Change PortalAdminPwd=ReplaceWithYourPwd to PortalAdminPwd=<\/actual><\/password><\/div>\n
                <\/div>\n
                9. Save and close the file<\/div>\n
                <\/div>\n
                10. On the Database server , run the following commands<\/connections><\/div>\n
                <\/div>\n
                db2set DB2COMM=TCPIP<\/span><\/span><\/div>\n
                db2set DB2_EVALUNCOMMITTED=YES<\/span><\/span><\/div>\n
                db2set DB2_INLIST_TO_NLJN=YES<\/span><\/span><\/div>\n
                db2 “UPDATE DBM CFG USING query_heap_sz 32768”<\/span><\/span><\/div>\n
                db2 “UPDATE DBM CFG USING sheapthres 0”<\/span><\/span><\/div>\n
                db2 “CREATE DB wpsdb alias wpsdb using codeset UTF-8 territory us PAGESIZE 32 K”<\/span><\/span><\/div>\n
                db2 “UPDATE DB CFG FOR wpsdb USING applheapsz 4096”<\/span><\/span><\/div>\n
                db2 “UPDATE DB CFG FOR wpsdb USING app_ctl_heap_sz 1024”<\/span><\/span><\/div>\n
                db2 “UPDATE DB CFG FOR wpsdb USING stmtheap 32768”<\/span><\/span><\/div>\n
                db2 “UPDATE DB CFG FOR wpsdb USING dbheap 2400”<\/span><\/span><\/div>\n
                db2 “UPDATE DB CFG FOR wpsdb USING locklist 1000”<\/span><\/span><\/div>\n
                db2 “UPDATE DB CFG FOR wpsdb USING logfilsiz 4000”<\/span><\/span><\/div>\n
                db2 “UPDATE DB CFG FOR wpsdb USING logprimary 12”<\/span><\/span><\/div>\n
                db2 “UPDATE DB CFG FOR wpsdb USING logsecond 20”<\/span><\/span><\/div>\n
                db2 “UPDATE DB CFG FOR wpsdb USING logbufsz 32”<\/span><\/span><\/div>\n
                db2 “UPDATE DB CFG FOR wpsdb USING avg_appls 5”<\/span><\/span><\/div>\n
                db2 “UPDATE DB CFG FOR wpsdb USING locktimeout 30”<\/span><\/span><\/div>\n
                db2 “UPDATE DB CFG FOR wpsdb using AUTO_MAINT off”<\/span><\/span>

                <\/span><\/span>
                <\/span><\/span><\/div>\n
                db2 “CONNECT TO wpsdb USER db2inst1 USING “<\/password><\/span><\/span>

                <\/span><\/span><\/div>\n
                db2 “CREATE BUFFERPOOL ICMLSFREQBP4 SIZE 1000 PAGESIZE 4 K” <\/password><\/span><\/span><\/div>\n
                db2 “CREATE BUFFERPOOL ICMLSVOLATILEBP4 SIZE 16000 PAGESIZE 4 K”<\/password><\/span><\/span><\/div>\n
                db2 “CREATE BUFFERPOOL ICMLSMAINBP32 SIZE 16000 PAGESIZE 32 K”<\/password><\/span><\/span><\/div>\n
                db2 “CREATE BUFFERPOOL CMBMAIN4 SIZE 1000 PAGESIZE 4 K”<\/password><\/span><\/span><\/div>\n
                db2 “CREATE REGULAR TABLESPACE ICMLFQ32 PAGESIZE 32 K MANAGED BY SYSTEM USING (‘ICMLFQ32’) BUFFERPOOL ICMLSMAINBP32”<\/password><\/span><\/span><\/div>\n
                db2 “CREATE REGULAR TABLESPACE ICMLNF32 PAGESIZE 32 K MANAGED BY SYSTEM USING (‘ICMLNF32’) BUFFERPOOL ICMLSMAINBP32”<\/password><\/span><\/span><\/div>\n
                db2 “CREATE REGULAR TABLESPACE ICMVFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING (‘ICMVFQ04’) BUFFERPOOL ICMLSVOLATILEBP4”<\/password><\/span><\/span><\/div>\n
                db2 “CREATE REGULAR TABLESPACE ICMSFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING (‘ICMSFQ04’) BUFFERPOOL ICMLSFREQBP4”<\/password><\/span><\/span><\/div>\n
                db2 “CREATE REGULAR TABLESPACE CMBINV04 PAGESIZE 4 K MANAGED BY SYSTEM USING (‘CMBINV04’) BUFFERPOOL CMBMAIN4”<\/password><\/span><\/span><\/div>\n
                db2 “CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE32 PAGESIZE 32 K MANAGED BY SYSTEM USING (‘icmlssystspace32’) BUFFERPOOL ICMLSMAINBP32”<\/password><\/span><\/span><\/div>\n
                db2 “CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE4 PAGESIZE 4 K MANAGED BY SYSTEM USING (‘icmlssystspace4’) BUFFERPOOL ICMLSVOLATILEBP4”<\/password><\/span><\/span><\/div>\n
                db2 “CREATE USER TEMPORARY TABLESPACE ICMLSUSRTSPACE4 PAGESIZE 4 K MANAGED BY SYSTEM USING (‘icmlsusrtspace4’) BUFFERPOOL ICMLSVOLATILEBP4”<\/password><\/span><\/span><\/div>\n
                db2 “UPDATE DB CFG FOR wpsdb USING DFT_QUERYOPT 2”<\/password><\/span><\/span><\/div>\n
                db2 “UPDATE DB CFG FOR wpsdb USING PCKCACHESZ 16384”<\/password><\/span><\/span><\/div>\n
                db2 “DISCONNECT wpsdb”<\/password><\/span><\/span><\/div>\n
                db2 “TERMINATE” <\/password><\/span><\/span><\/div>\n
                <\/div>\n
                11. On the portale.svil.com server switch to the ConfigEngine directory and run the ConfigEngine.sh task<\/div>\n
                <\/div>\n
                cd \/opt\/IBM\/WebSphere\/wp_profile\/ConfigEngine<\/span><\/span>
                .\/ConfigEngine.sh validate-database-driver<\/span><\/span>
                .\/ConfigEngine.sh validate-database-connection<\/span><\/span>

                <\/span><\/span>Only if the above 2 commands succeed, proceed to the next step<\/span>

                <\/span><\/span><\/div>\n
                .\/ConfigEngine.sh setup-database<\/i><\/tt><\/span><\/span><\/div>\n
                <\/div>\n
                12. From the connections.svil.com server issue the following commands<\/div>\n
                <\/div>\n
                cd ~<\/span><\/span><\/div>\n
                scp wasadmin@portale.svil.com:\/opt\/IBM\/WebSphere\/PortalServer\/jcr\/wp.content.repository.install\/lib\/wp.content.repository.install.jar .<\/span><\/span><\/div>\n
                scp wasadmin@portale.svil.com:\/opt\/IBM\/WebSphere\/wp_profile\/PortalServer\/jcr\/config\/registerCollationUDFTemplate.sql .<\/span><\/span><\/div>\n
                cd ~\/sqllib\/function<\/span><\/span><\/div>\n
                \/opt\/IBM\/db2\/V10.1\/java\/jdk64\/bin\/jar -xvf ~\/wp.content.repository.install.jar icm\/CollationUDF.class<\/span><\/span><\/div>\n
                <\/div>\n
                13. Edit the ~\/registerCollationUDFTemplate.sql and change all occurrences of to jcr.<\/schema><\/div>\n
                <\/div>\n
                14. Issue the following commands<\/div>\n
                <\/div>\n
                db2 connect to wpsdb<\/i> user db2inst1<\/i><\/tt> using <\/password><\/i><\/tt><\/span><\/span><\/div>\n
                db2 -tvf ~<\/i><\/tt>\/registerCollationUDFTemplate.sql<\/tt><\/span><\/span><\/div>\n
                db2 terminate<\/tt><\/span><\/span><\/div>\n
                db2stop<\/tt><\/span><\/span><\/div>\n
                db2start<\/span><\/span><\/tt><\/div>\n
                db2 connect to wpsdb user db2inst1 using <\/password><\/span><\/span><\/tt><\/div>\n
                values <\/tt>schema<\/i><\/tt>.sortkeyj('abc','en')<\/tt><\/span><\/span><\/tt><\/div>\n
                db2 terminate <\/tt> <\/span><\/span> <\/tt><\/div>\n
                <\/div>\n
                15. You should receive a single record for the above values schema.sortkeyj statement.<\/div>\n
                <\/div>\n
                16. On the portale.svil.com server issue the following command<\/div>\n
                <\/div>\n
                cd \/opt\/IBM\/WebSphere\/wp_profile\/PortalServer\/jcr\/lib\/com\/ibm\/icm<\/span><\/span><\/div>\n
                vi icm.properties<\/span><\/span> <\/div>\n
                <\/div>\n
                17. Edit the icm.properties file and add the following section to the end of the file<\/div>\n
                <\/div>\n
                \n
                # Enable\/Disable collation support for all DB2 platforms<\/span><\/span><\/pre>\n
                # Disabled by default<\/span><\/span><\/pre>\n
                jcr.query.collation.db2.enabled = false<\/span><\/span><\/pre>\n
                 <\/span><\/span><\/pre>\n
                # Database specific collation mappings<\/span><\/span><\/pre>\n
                # These mappings apply map a Java locale name into a collation name<\/span><\/span><\/pre>\n
                # supported by the underlying database.<\/span><\/span><\/pre>\n
                # Example mappings for DB2 platform<\/span><\/span><\/pre>\n
                 <\/span><\/span><\/pre>\n
                # English<\/span><\/span><\/pre>\n
                jcr.query.collation.en = en<\/span><\/span><\/pre>\n
                 <\/span><\/span><\/pre>\n
                # Swedish<\/span><\/span><\/pre>\n
                jcr.query.collation.sv = sv<\/span><\/span><\/pre>\n
                 <\/span><\/span><\/pre>\n
                jcr.query.collation.zh = zh<\/span><\/span><\/pre>\n
                jcr.query.collation.de = de<\/span><\/span><\/pre>\n
                jcr.query.collation.da = da<\/span><\/span><\/pre>\n
                jcr.query.collation.hu = hu<\/span><\/span><\/pre>\n
                jcr.query.collation.jp = jp<\/span><\/span><\/pre>\n<\/div>\n
                <\/div>\n
                18. Please NOTE:  <\/b>I have purposefully disabled collation support. Because I want to go through the steps but not really enable collation support at this point in time.<\/p>\n
                 <\/fill><\/p>\n
                19. Take a snapshot at this point in time for both the VM’s. If this go pear shaped we can restore back to this state and continue.<\/span><\/b><\/p>\n
                20. Start the VM’s again and ensure that the DB2 instances are started<\/p>\n
                21. Login to the portale.svil.com server and issue the following commands. Ensure Portal server is stopped.<\/span>

                <\/span>cd \/opt\/IBM\/WebSphere\/wp_profile\/ConfigEngine<\/span><\/span>
                .\/ConfigEngine.sh validate-database -DWasPassword=<\/password><\/i><\/tt><\/span><\/span>
                cp ..\/PortalServer\/jcr\/lib\/com\/ibm\/icm\/icm.properties ~<\/i><\/tt><\/span><\/span>
                .\/ConfigEngine.sh database-transfer<\/tt><\/i><\/tt><\/span><\/p>\n
                22. Verify that everything went well by editing the ConfigTrace.log file like<\/span>

                <\/span>vi .\/log\/ConfigTrace.log <\/span><\/span><\/p>\n
                23.Connect to the connections.svil.com database server and perform a reorg check to improve performance. Issue the following commands<\/p>\n
                \n
                db2 connect to wpsdb user db2inst1 using <\/password><\/i><\/pre>\n
                db2 reorgchk update statistics on table all > xyz.out <\/tt><\/pre>\n
                 <\/tt><\/pre>\n
                24. Edit the file and for each file that is marked with an asterix (*)<\/tt><\/pre>\n
                in the REORG Column run the following command:<\/tt><\/pre>\n

                <\/tt><\/pre>\n
                db2 reorg table <\/tt>tablename<\/i><\/tt><\/pre>\n
                 <\/tt><\/pre>\n
                25. <\/tt>After you have run the reorg<\/tt> command for each tablename<\/i>, <\/pre>\n
                run the following commands: <\/pre>\n
                 <\/pre>\n
                db2 terminate<\/tt>
                db2rbind <\/tt>database_name<\/i><\/tt> -l db2rbind.out -u <\/tt>db2_admin<\/i><\/tt> -p <\/tt>password<\/i><\/tt><\/p>\n
                <\/tt> <\/tt><\/pre>\n
                26. !!!!! BEFORE YOU START THE SERVER PERFORM THE FOLLOWING !!!!<\/tt><\/span><\/pre>\n

                <\/tt><\/pre>\n
                cp \/opt\/IBM\/WebSphere\/wp_profile\/PortalServer\/dbdrivers\/*.jar \/opt\/IBM\/WebSphere\/AppServer\/lib<\/tt><\/pre>\n

                <\/tt><\/pre>\n

                <\/tt><\/pre>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
                Ok. I have seen several pieces of documentation on this. Lots of tutorials out there. However all of the sites and tutorials do not have all the steps mentioned clearly. I am trying to make this as comprehensive as possible. Try this on a Trial or test Portal Server, preferably a VM with a Snapshot …<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,18,15,17,13,19,16],"tags":[],"class_list":["post-16","post","type-post","status-publish","format-standard","hentry","category-centos","category-configengine","category-database","category-installation","category-portal","category-transfer","category-websphere","entry entry-center"],"_links":{"self":[{"href":"https:\/\/www.srivittal.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/16","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.srivittal.com\/wp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.srivittal.com\/wp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.srivittal.com\/wp\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.srivittal.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=16"}],"version-history":[{"count":1,"href":"https:\/\/www.srivittal.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/16\/revisions"}],"predecessor-version":[{"id":76,"href":"https:\/\/www.srivittal.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/16\/revisions\/76"}],"wp:attachment":[{"href":"https:\/\/www.srivittal.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=16"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.srivittal.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=16"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.srivittal.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=16"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}