Transfer SQL Server manually

 

+
Search Tips   |   Advanced Search

 

This section provides information on how to manually transfer data to another database domain.

 

Before you begin

Before transferring IBM WebSphere Portal data to another domain, ensure that the following prerequisites are met:

• Supported database software is installed.

• Databases and users are correctly set up for use by WebSphere Portal.

• If you are using DB2 Fix Pack 10, DB2 Fix Pack 11, or DB2 Fix Pack 12, complete the following steps prior to database transfer. This fix should be applied to both the client side and server side of the DB2 installation. Failure to follow these steps will cause the database transfer to hang at the task action-process-constraints.

  If you are running a remote z/OS database, the fix should be applied to the client side only.

  1. Locate the following file:

     • UNIX:

       /home/db2inst1/sqllib/cfg/db2cli.ini

     • Windows:

       db2home/sqllib/db2cli.ini

  2. Edit the file by adding the following to the end of the file:
     • For Fix Pack 10 or Fix Pack 11:

       [COMMON]
       DYNAMIC=1
       
       

       An empty line is required after the dynamic=1 at the end of the file.

     • For Fix Pack 12:

       [COMMON]
       ReturnAliases=0
       
       

       An empty line is required after the ReturnAliases=0 at the end of the file.

• If you are transferring from Oracle Enterprise Edition, the open_cursors setting should be set to 1500 by default. If you are using DB2Content Manager Runtime Edition, this value may need to be increased based on the table count in the DB2 Content Manager Runtime Edition schema.

Password considerations: For security reasons, you should not store passwords in the wpconfig.properties, wpconfig_dbdomain.properties, and wpconfig_dbtype.properties files. It is recommended that you edit each of the properties files prior to running a configuration task, inserting the passwords needed for that task. Then, after the task has run, you should delete all passwords from each file. For information, see Delete passwords.

Alternatively, we can specify the password on the command line using the following syntax:

   WPSconfig.{sh|bat} task_name -Dpassword_property_key=password_value 

As with other properties, each password property must have the -D prefix and be set equal to (=) a value. If you have multiple properties in a single command, use a space character between each -Dproperty=value setting.

Tip:

• The section entitled Steps for transferring data to another supported database provides instructions for manually transferring data. Instead of performing the following steps, we can use the configuration wizard, a graphical user interface, to transfer data to another supported database. For more information, refer to Configuring WebSphere Portal with the configuration wizard.

 

Steps for transferring data to another supported database

To transfer data between domains.

1. Locate the following files and create a backup copy of each before changing any values:
   • portal_server_root/config/wpconfig.properties

   • portal_server_root/config/wpconfig_dbdomain.properties

   • portal_server_root/config/wpconfig_dbtype.properties

2. Edit the following properties files, and enter the values that are appropriate for our environment.
   • portal_server_root/config/wpconfig.properties

   • portal_server_root/config/wpconfig_dbdomain.properties

   • portal_server_root/config/wpconfig_dbtype.properties

   Note the following information:

   • The WebSphere Portal database can be used to hold information for applications such as Document Manager, Portal Personalization, Feedback and LikeMinds. In this case, the user should use similar naming conventions for property values such as release.DbName, community.DbName, customization.DbName, Jcr.DbName, Feedback.DbName, and Likeminds.DbName. For example:
     • release.DbName=releasedb

     • community.DbName=communitydb

     • customization.DbName=customizationdb

     • JcrDbName=jcrdb

     • FeedbackDbName=fdbkdb

     • LikemindsDbName=lkmddb

   • Do not change any settings other than those that are specified in these steps. For instructions on working with these files, see Configuration properties reference for a complete properties reference, including default values.

   • If you are using a remote database, be sure to enter the values for the remote server.

   • Use / instead of \ for all platforms.

   • Some values, shown here in italics, might need to be modified to the specific environment.

   • There might be additional database properties other than those listed here. Only change the properties within this table; skip all other properties.

   • If you are following the recommended architecture in Planning for databases for the specific database, the Value listed for each property in the following table represents the DB2 -specific information that is required to configure WebSphere Portal to DB2.

   • Depending on which database domain has to be configured, dbdomain may need to be replaced by:
     • release

     • customization

     • community

     • jcr

     • feedback

     • likeminds

     • wmm

   Update the following properties in the file wpconfig_dbdomain.properties.

   Database domain properties
   Property	Value
   dbdomain.DbType	The type of database used to store information for WebSphere Portal. Value type: Alphanumeric text string Recommended value for all domains: Cloudscape : cloudscape DB2 : db2 Oracle: oracle MS SQL Server: sqlserver DB2 for i5/OS: db2_iseries DB2 for z/OS and OS/390: db2_zos
   dbdomain.DbName	The name of the WebSphere Portal domain database and schema. Notes: This value is also the database element in the dbdomain.DbUrl property. Non-Windows platforms when using DB2 only. This value is the TCPIP Alias for the database. For Oracle, this value should match the 'ORACLE_SID'. Value type: Alphanumeric text string Notes: For DB2 and DB2 for z/OS, this value cannot exceed 8 characters and can only contain letters and numbers. Refer to your database documentation for more information. For DB2, this value must be different from the value of dbdomain.XDbName. If you change the name of the WebSphere Portal data source due to a database migration, manually update this property in the portal_server_root/config/wpconfig_dbdomain.properties file to maintain the proper resource reference mapping. Default value for all domains: wpsdb Recommended value for local DB2 for i5/OS: For non-Member Manager databases: Release: *LOCAL/REL60 Customization: *LOCAL/CUST60 Community: *LOCAL/COMM60 JCR: *LOCAL/JCR60 Feedback: *LOCAL/FDB60 LikeMinds: *LOCAL/LKM60 For Member Manager databases: *LOCAL/wmm.DbUser, where *LOCAL is the database and wmm.DbUser is the Member Manager database administrator user ID (the schema you will use to connect to the database). Recommended value for remote DB2 for i5/OS: For non-Member Manager databases: Release: hostName/REL60 Customization: hostName/CUST60 Community:hostNameCOMM60 JCR: hostName/JCR60 Feedback: hostName/FDB60 LikeMinds: hostName/LKM60 For Member Manager databases: hostName/wmm.DbUser, where wmm.DbUser is the Member Manager database administrator user ID (the schema you will use to connect to the database).
   dbdomain.DbUrl	The database URL used to access the WebSphere Portal database with JDBC. The value must conform to standard JDBC URL syntax. For Oracle, Oracle RAC, and SQL Server, hostname is the name of the remote server and port is the port where the appropriate database instance is listening. The database element of this value should match the value of dbdomain.DbName. Value type: Alphanumeric text string, conforming to standard JDBC URL syntax Recommended values (for all domains): Cloudscape : jdbc:db2j:wpsdb;create=true DB2 : jdbc:db2:wpsdb DB2 for i5/OS (local): For non-Member Manager databases: jdbc:db2:*LOCAL/dbdomain.DbName For Member Manager databases: jdbc:db2:*LOCAL/wmm.DbUser, where *LOCAL is the database and wmm.DbUser is the Member Manager database administrator user ID (the schema you will use to connect to the database). DB2 for i5/OS (remote): For non-Member Manager databases: jdbc:as400:hostName/dbdomain.DbName For Member Manager databases: jdbc:as400:hostname/wmm.DbUser, where wmm.DbUser is the Member Manager database administrator user ID (the schema you will use to connect to the database). DB2 for z/OS (local): jdbc:db2:location DB2 for z/OS (remote): jdbc:db2:wpsdb Oracle: jdbc:oracle:thin:@YourDatabaseServer:1521:wpsdb SQL Server: jdbc:microsoft:sqlserver://YourDatabaseServer:1433;DatabaseName=wpsdb
   dbdomain.DbUser	The user ID for the database administrator. Notes: For SQL Server and non-wmm databases only, unless you are the system administrator, the values for dbdomain.DbUser and dbdomain.DbSchema must be the same. For Oracle and SQL Server servers, this value must be set to FEEDBACK, which corresponds to the user FEEDBACK in the database. If the user you are using is an administrative user that has authority over the FEEDBACK schema, the administrative user should be entered for the dbdomain.DbUser property. Value type: Alphanumeric text string Default value for all domains: db2admin Recommended value: wpsdbusr (for databases other than DB2 )
   dbdomain.DbPassword	The password for the database administrator. A value must be set for this property; it cannot be empty. Value type: Alphanumeric text string Default value for all domains: ReplaceWithYourDbAdminPwd
   dbdomain.XDbName	The database loop back alias that needs to be set if you plan to use the create-local-database-db2 task. Notes: For DB2, this value must be different from the value of dbdomain.DbName. Value type: Alphanumeric text string Default: Release: wps6TCP Community: wps6TCP Customization: wps6TCP WMM: wps6TCP JCR: wps6TCP Feedback: fdbk6TCP LikeMinds: lmdb6TCP
   dbdomain.DbNode	The value for the node database. Required for non-Windows platforms when using DB2 only. Value type: Alphanumeric text string Default values: Release: wpsNode Community: wpsNode Customization: wpsNode JCR: wpsNode WMM: wpsNode Feedback value: pznNode LikeMinds value: pznNode
   dbdomain.DbSchema	The schema name of the database domain. Notes: For i5/OS: dbdomain.DbSchema must match the postfix of dbdomain.DbName after /. For example, if release.DbName=*LOCAL/REL60, it must also have release.DbSchema=REL60. Follow the documentation of the target database management system in order to define a valid schema name as restrictions apply for some database management systems, for example on i5/OS. Notes: This property is not used on Member Manager, which only uses the dbdomain.DbUser name as the schema. Follow the documentation of the target database management system in order to define a valid schema name as restrictions apply for some database management systems. For SQL Server only, unless we are the system administrator, the values for dbdomain.DbUser and dbdomain.DbSchema must be the same. For Oracle and SQL Server servers, the value must be set to FEEDBACK. Value type: Alphanumeric text string Default: dbdomain
   dbdomain.DataSourceName	The name of the data store that WebSphere Portal uses to communicate with its databases. Notes: If the DbUser, DbUrl, and DbPassword are not the same across domains, this value must differ from the DataSourceName of the other domains. The value for dbdomain.DataSourceName should not be set to the same value as dbdomain.DbName. Value type: Alphanumeric text string Default values: Release: wpsdbDS Community: wpsdbDS Customization: wpsdbDS JCR: JCRDS WMM: wpsdbDS Feedback: feedback5 LikeMinds: lmdbDS

   Update the following properties in the file wpconfig_dbtype.properties.

   Database type properties
   Property	Value
   dbtype.DbSafeMode	This property only applies to database-specific tasks. If this property is set to true, database-specific tasks such as create/modify/drop database will not be performed. The property should be used if a pre-configured database, such as from a previous installation, already exists. If the property is set to false, the database will be updated and the pre-existing database configuration will be overwritten. Value type: true, false Default: false
   dbtype.DbDriver	The Java class name for the JDBC provider that WebSphere Portal uses to communicate with its databases. Value type: Alphanumeric text string, conforming to the Java namespace syntax for specifying classes. For example: COM.ibm.db2.jdbc.app.DB2Driver. The following values can be specified: Cloudscape : com.ibm.db2j.jdbc.DB2jDriver DB2 : com.ibm.db2.jdbc.app.DB2Driver Oracle : oracle.jdbc.driver.OracleDriver SQL Server : com.microsoft.jdbc.sqlserver.SQLServerDriver DB2 for i5/OS (local):com.ibm.db2.jdbc.app.DB2Driver DB2 for i5/OS (remote): com.ibm.as400.access.AS400JDBCDriver DB2 for i5/OS Recommended: For local databases: com.ibm.db2.jdbc.app.DB2Driver For remote databases: com.ibm.as400.access.AS400JDBCDriver
   dbtype.DbLibrary	The directory and name of the.zip or.jar file that contains the JDBC driver class. Value type: Directory path with elements delimited by forward slashes (/). In UNIX environments, use a colon (:) instead of a semi-colon (;) to separate multiple paths. Example: The following values can be specified: Cloudscape: PortalServershared/app/cloudscape/db2j.jar DB2 : SQLLIBjava/db2java.zip Oracle: Oracle/jdbc/lib/ojdbc14.jar SQL Server: SQLServerJDBC/lib/mssqlserver.jar; SQLServerJDBC/lib/msbase.jar;SQLServerJDBC/lib/msutil.jar DB2 for i5/OS: /QIBM /ProdData/Java400/ext/db2_classes.jar DB2 for i5/OS (remote): For the UserData path of the installed edition of WebSphere Application Server :portal_server_root/IBM Trans/lib/jt400.jar i5/OS Recommended value: DB2 for i5/OS: Local: /QIBM /ProdData/Java400/ext/db2_classes.jar Remote: For the UserData path of the installed edition of WebSphere Application Server :portal_server_root/IBM Trans/lib/jt400.jar For remote DB2 configuration, for the UserData path of the installed edition of WebSphere Application Server this value should be: portal_server_root/IBM Trans/lib/jt400.jar i5/OS Default value: /QIBM /ProdData/Java400/ext/db2_classes.jar

   If you have come to this step while creating databases and users, please return to that section of the InfoCenter now. Otherwise, continue with the next step.

3. Save the file.

4. Perform this step only if you are installing multiple instances of WebSphere Portal with DB2 on AIX, change the maximum numdb MAX_NETBIOS_CONNECTIONS to increase the default configured number of databases. For example, enter the following commands at the database prompt: set client MAX_NETBIOS_CONNECTIONS 254. A message indicates success if the number was increased.

5. Open a command prompt, and change to the Windows directory portal_server_root/config UNIX directory portal_server_root/config.

6. Enter one of the first two commands to validate the database connection, and then enter the last command to validate the database driver, where domain is replaced with the domain you are transferring.
   • UNIX:
     • For a single domain:./WPSconfig.sh validate-database-connection-wps -DDbDomainList=domain

     • For multiple domains:./WPSconfig.sh validate-database-connection-wps -DDbDomainList=domain1,domain2,domain3

     • ./WPSconfig.sh validate-database-driver

   • Windows :
     • For a single domain: WPSconfig.bat validate-database-connection-wps -DDbDomainList=domain

     • For multiple domains: WPSconfig.bat validate-database-connection-wps -DDbDomainList=domain1,domain2,domain3

     • WPSconfig.bat validate-database-driver

7. From the same command prompt as the previous steps, be sure you are in the Windows directory portal_server_root/config UNIX directory portal_server_root/config.

8. Stop the WebSphere Application Server (named server1 on Windows UNIX systems) before running the database-transfer task.
   • Enter the following command:
     • UNIX:

       ./stopServer.sh server1 -user admin_userid -password admin_password

     • Windows:

       stopServer.bat server1 -user admin_userid -password admin_password

     • i5/OS:

       stopServer -profileName profile_root -user admin_userid -password admin_password

       ...where profile_root is the name of the WebSphere Application Server profile where WebSphere Portal is installed; for example, wp_profile.

     ...where server1 is the name of the WebSphere Application Server administrative server, and profile_root is the name given to the WebSphere Application Server profile in use.

9. Stop the WebSphere Portal server before running the database-transfer task.
   • Enter the following command:
     • UNIX:

       ./stopServer.sh WebSphere_Portal -user admin_userid -password admin_password

     • Windows:

       stopServer.bat WebSphere_Portal -user admin_userid -password admin_password

     • i5/OS:

       stopServer WebSphere_Portal -profileName profile_root -user admin_userid -password admin_password

       ...where profile_root is the name of the WebSphere Application Server profile where WebSphere Portal is installed; for example, wp_profile.

10. To transfer the database, perform this step:
    1. Enter the following commands to run the configuration task.
       • To run the task on an individual domain, enter the following command:
         • UNIX:

           ./WPSconfig.sh database-transfer-domain -Ddomain.DbPassword=password

         • Windows:

           WPSconfig.bat database-transfer-domain -Ddomain.DbPassword=password

         and where domain is one of the domain database names (community, customization, feedback, jcr, likeminds, release or wmm).

       • To run the task on multiple domains, enter the following command:

         • UNIX:

           ./WPSconfig.sh database-transfer -DTransferDomainList=domain1,domain2,domain3

         • Windows:

           WPSconfig.bat database-transfer -DTransferDomainList=domain1,domain2,domain3

         and where domain is one of the domain database names (community, customization, feedback, jcr, likeminds, release or wmm).

       • To run the task on all domains, enter the following command:

         • UNIX:

           ./WPSconfig.sh database-transfer

         • Windows:

           WPSconfig.bat database-transfer

    2. After running this task, a message indicating success should result. Check the log files to verify that this task was successful. If the configuration fails, verify the values in the wpconfig.properties, wpconfig_dbdomain.properties, and wpconfig_dbtype.properties files, and then repeat this step.

11. For Windows and UNIX only: After transferring the database tables, perform a reorg check to improve performance. (Do the following set of steps for each database alias in the property file: wps.DbName and feedback.DbName.)

    This step may be skipped with an i5/OS system.

    1. Connect to a database with the following command:

       db2 connect to database_alias user db2admin_userid using password 

       Additional option may be required if additional security was installed. Refer to DB2 Universal Database commands by example for links to a downloadable command reference.

    2. Once connected, run the following commands from the DB2 prompt:

       Windows:

       db2 reorgchk update statistics on table all > xyz.out 

       Look in the reorg column for entries marked with a * (star or asterisk) in the file xyz.out. For each line with a *, note the tablename and run the following command for each tablename:

       db2 reorg table tablename db2 terminate c:\> db2rbind database_name -l db2rbind.out -u db2_admin -p password 

       UNIX :

       db2 reorgchk update statistics on table all > xyz.out 

       Look in the reorg column for entries marked with a * (star or asterisk) in the file xyz.out. For each line with a *, note the tablename and run the following command for each tablename:

       db2 reorg table tablename db2 terminate db2rbind database_name -l db2rbind.out -u db2_admin -p password 

    3. The output file db2rbind.out will only be created if there is an error for the above db2rbind command.

12. Change to the Windows directory was_profile_root/bin UNIX directory was_profile_root/bin.

13. Start the WebSphere Portal server.
    • Enter the following command:
      • UNIX:

        ./startServer.sh WebSphere_Portal

      • Windows:

        startServer.bat WebSphere_Portal

      • i5/OS:

        startServer WebSphere_Portal -profileName profile_root

        ...where profile_root is the name of the WebSphere Application Server profile where WebSphere Portal is installed; for example, wp_profile.

 

Parent Topic

Transferring individual domains

 

---