Moving Db2 databases to a different Db2 server or Db2 instance

Edit online

After you install the Cloud APM server, you can move your Cloud APM server Db2 databases from the local to a remote Db2 server or if you are already connected to a remote Db2 server, you can move the databases to a different remote Db2 server or a different Db2 instance user.

About this task

Use this procedure to back up and move the Db2 databases from one Db2 server either local or remote to a different remote Db2 server or to a different Db2 instance user. You can use the same database names or change the database names during the move. The following procedure uses the Db2 RESTORE REDIRECT command to move the databases to the new remote Db2 server. Complete the following main tasks to move the databases:

Stop the Cloud APM server and the connections to the current Db2 databases. See Step 1 - Stop the Cloud APM server and Db2 database connections.
Back up the databases. See Step 2 - Back up the databases.
Move (restore) the Db2 to the new Db2 server. See Step 3 - Move (restore) the databases.
Check the grant requests. See Step 4 - Check the grant requests.
Set up TBSMDB for SCR UDF functions. See Step 5 - Set up TBSMDB for SCR UDF functions and Step 6 - Set up the SCR UDF functions.
Verify the databases. See step Step 7 - Verify the databases.

When you are completing the steps in the following procedure, keep the following items in mind:

It is an example procedure that describes how to move your existing Db2 databases to a new Db2 server. If your organizations Db2 administrator completes their own processes and procedures for moving databases, use your organizations Db2 procedures instead of completing the steps in this procedure. Each step in this procedure is either marked Cloud APM or Db2 so that you are aware of the Cloud APM specific steps and the Db2 steps.
It includes the steps to change the Db2 database names, port number, instance name, password, and database file system location. If you want to change a straight forward item such as the Db2 port number only, you might want to refer to the standard Db2 procedures and run the db2 update dbm cfg using svcename command and update /etc/services instead of completing the following specific port number steps.
The following steps are using an offline backup to create the backup files. After the files are created, they are copied to the new Db2 server. On the new Db2 server, a RESTORE REDIRECT is used to create a specific restore script. This script can then be used to move and restore the databases to the new Db2 server. Before the script is run, it is edited to allow the user to specify items such as the directory where the new databases are being stored. The script runs the restore process twice, the first time to complete the initial restore, and the second time with the CONTINUE option to process storage group storage paths and DMS table space containers.
The Cloud APM server does not support moving from the db2apm instance user for a local Db2 server to a different Db2 instance user for the local Db2 server. When the Db2 server is installed, db2apm must be the instance user name.

Procedure

Complete the following steps to move your Cloud APM Db2 databases to a new Db2 server and optionally change the default names of the databases, change the port number, and change the instance ID.

Stop the Cloud APM server and the connections to the current Db2 databases by completing the following steps:
1. As user root, stop the Cloud APM server by issuing the following command
```
apm stop_all
```
2. On the current Db2 server, issue su to the Db2 instance user ID. For example, for the db2apm instance user ID, issue the following command:
```
su - db2apm
```
3. Start the current Db2 server by issuing the following command:
```
db2start
```
4. Verify that no connections to the Cloud APM Db2 database that you are moving exist. Issue the following command to check the connections:
```
db2 list applications
```
  If you have more than one Cloud APM server that use this Db2 instance, you can specify the database name. For example:
```
db2 list applications for db WAREHOUS
```
5. If connections still exist, investigate them and kill them if necessary by issuing the following command:
```
db2 "force application (31441, 54612)"
```
  where 31441 and 54612 are handles to the applications. You can use the db2 force applications all command if no other applications are using the Db2 instance.
6. Verify again that no connections to each of the databases exist. For example for the WAREHOUS database, issue the following command:
```
db2 list applications for db WAREHOUS
```
  where WARESHOUS is the database that you are checking for connections. Output similar to the following output is displayed if no connections to this database exist:
```
SQL1611W  No data was returned by Database System Monitor
```
7. Repeat steps 2.d, 2.e, and 2.f for each of the databases.
At the end of this step, the Db2 server is started with no connections to the three Cloud APM server Db2 databases that are being moved. The offline backups require a writable directory that your Db2 instance user ID (which is either the default user ID db2apm or another user ID) either owns or has write access to the directory.
Back up the databases by completing the following steps.
1. As user root on the current Db2 server, create a directory for Db2 backups. For example:
```
mkdir /db2; mkdir /db2/backups/; chown db2apm:db2iadm1 -R  /db2
```
2. As the Db2 instance user ID on the current DB2 server, complete an offline backup for all three Cloud APM server Db2 databases. Use the default names WAREHOUS, DATAMART, and SCR32 unless your databases already have different names. Issue the following commands:
```
db2 backup db WAREHOUS to /db2/backups/
db2 backup db DATAMART  to /db2/backups/
db2 backup db SCR32 to /db2/backups/  
```
  After each backup command is run, a backup file is created with a name similar to this name: /db2/backups/WAREHOUS.0.db2apmc.DBPART000.20180427141206.001 and a message similar to the following message is displayed:
```
Backup successful. The timestamp for this backup image is : 20180727141206
```
  The backup file name and output message has a different time stamp to the one in this example.
Move (restore) the databases to the new Db2 server by completing the following steps:
1. On the new Db2 server, you should have previously installed a supported version of Db2 and followed step 1 and steps 2.a to 2k in the Connecting to a remote Db2 server topic to copy the Cloud APM server files to the Db2 server and create the Db2 instance user ID.
2. If you want to run with Db2 authentication set to server_encrypt, complete the step 1 and step 2 in Changing Db2 authentication from server to server_encrypt.
3. Complete the following steps for the three databases: Copy the Db2 backup to the new Db2 server, grant Db2 requests, create the .clp files, and restore the Db2 databases to the new Db2 server:
  1. Use a command such as scp to copy the Db2 backups that are taken in step 2.b. to the new Db2 server.
  2. As the Db2 instance user ID on the new remote Db2 server, issue the following command:
```
db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON
```
    Note: If your Db2 server is running Db2 Advanced Enterprise Server Edition version 10.5, you must also restart the Db2 server by running the db2stop command followed by the db2start to activate the db2set update.
    As another option, you can log on as the previous Db2 instance user ID and grant access to the new instance user ID after each database is restored.
  3. As the Db2 instance user ID on the new Db2 server, run the following commands to create the .clp files, which are used later in this procedure.
  4. As the Db2 instance user ID on the new Db2 server, issue the db2 restore command for all three databases.
  For example:
```
db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON
db2 restore db WAREHOUS   from /db2/backups taken at 20180427141206 into WHOUSG  redirect generate script WHOUSG_db.clp
db2 restore db DATAMART   from /db2/backups taken at 20180427141101 into DMARTG  redirect generate script DMARTG_db.clp
db2 restore db SCR32  from /db2/backups taken at 20180427141501 into SCRG  redirect generate script SCRG_db.clp 
```
  - WAREHOUS, DATAMART, and SCR32 are the default original Db2 database names.
  - /db2/backups is the directory where the backups were copied to on the new Db2 server.
  - 20180727141206 is the time stamp.
  - WHOUSG, DMARTG, and SCRG are the new database names.
  - WHOUSG_db.clp, DMARTG_db.clp, and SCRG_db.clp are the output .clp file names.
  The contents of the .clp files are similar to the following.
```
-- *****************************************************************************
-- ** automatically created redirect restore script
-- *****************************************************************************
UPDATE COMMAND OPTIONS USING S ON Z ON DATAMART_NODE0000.out V ON;
SET CLIENT ATTACH_MEMBER  0;
SET CLIENT CONNECT_MEMBER 0;
-- *****************************************************************************
-- ** automatically created redirect restore script
-- *****************************************************************************
RESTORE DATABASE DATAMART
-- USER  <username>
-- USING '<password>'
FROM '/db2/backups'
TAKEN AT 20180427141101
-- ON '/db2/DATAMART_data'
-- DBPATH ON '<target-directory>'
INTO DMARTG
-- NEWLOGPATH '/db2/DATAMART_data/db2apmc/NODE0000/SQL00001/LOGSTREAM0000/'
-- WITH <num-buff> BUFFERS
-- BUFFER <buffer-size>
-- REPLACE HISTORY FILE
-- REPLACE EXISTING
REDIRECT
-- PARALLELISM <n>
WITHOUT ROLLING FORWARD
-- WITHOUT PROMPTING
```
4. Edit the.clp files for the three databases. The following information includes guidance to help you update these files.
  - The lines beginning with -- are comments.
  - Uncomment the line —ON and specify the location where you want the database to be stored on the new Db2 server. In the previous example, the ON line was the only line updated like this:
    ON '/db2/db2apmc/WHOUSG_data'
  - You can also uncomment the line beginning with —NEWLOGPATH to specify the location where you want the logs to be stored.
  - The —USER line does not need to be updated if you are running the restore as the db2apm user instance ID or as your own custom Db2 user instance ID.
  - You can leave the lines for buffers commented out. When you complete a restore operation, the Db2 database products automatically choose an optimal value for the number of BUFFER, buffer size, and the PARALLELISM settings.
5. For each .clp file for the three databases, as the Db2 instance user ID on the new Db2 server, create the directory that is specified in the ON parameter in the database .clp file. Go to the directory where the .clp file was saved for the particular database, and run the .clp file. Here is an example for the DMARTG (originally the DATAMART) database.
```
mkdir /db2/db2apmc/DMARTG_data
cd /db2/backups  
db2 -tvf DMARTG_db.clp
```
  The following code is sample output after the DMARTG .clp file is run. It runs a redirect restore followed by a normal RESTORE DATABASE DATAMART CONTINUE. It automatically runs the second restore to handle the SQL1277W warning message that was generated during the redirect restore. If the second restore is successful, the warning message can be ignored.
```
$ db2 -tvf DMARTG_db.clp
UPDATE COMMAND OPTIONS USING S ON Z ON DATAMART_NODE0000.out V ON
DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.

SET CLIENT ATTACH_MEMBER  0
DB20000I  The SET CLIENT command completed successfully.

SET CLIENT CONNECT_MEMBER 0
DB20000I  The SET CLIENT command completed successfully.

RESTORE DATABASE DATAMART FROM '/db2/backups' TAKEN AT 20180427141101 ON '/db2/db2apmc/DMARTG_data' INTO DMARTG NEWLOGPATH '/db2/db2apmc/DMARTG_data/db2apmg/NODE0000/SQL00001/LOGSTREAM0000/' REDIRECT WITHOUT ROLLING FORWARD
SQL1277W  A redirected restore operation is being performed. During a table space restore, only table spaces being restored can have their paths reconfigured. During a database restore, storage group storage paths and DMS table space containers can be reconfigured.
DB20000I  The RESTORE DATABASE command completed successfully.

RESTORE DATABASE DATAMART CONTINUE
DB20000I  The RESTORE DATABASE command completed successfully.
```
6. If you complete an offline backup, you should not need to run a db2 rollforward command. However, if you have log records that are pending then you might need to run the following commands as the Db2 user instance ID on the new Db2 server before you can connect to the databases.
```
db2  rollforward db WAREHOUS complete
db2  rollforward db DATAMART complete
db2  rollforward db SCR32 complete
```
  where WAREHOUSE, DATAMART, and SCR32 are the original database names.
Check the grant requests. If you changed the Db2 instance user ID when you moved the databases, the original Db2 instance user ID can still access the databases. Run the following command to review the existing grants on the databases. If the original Db2 instance user ID has grants on the databases, these grants should be revoked.
```
db2 connect to db_name
db2 "SELECT DISTINCT GRANTEETYPE, GRANTEE, SECURITYADMAUTH from SYSCAT.DBAUTH"
db2 "SELECT DISTINCT GRANTEETYPE, GRANTEE, DBADMAUTH from SYSCAT.DBAUTH"
db2 connect reset
```
where db_name is the name of one of the three databases. The Db2 instance user ID should have dbadmin and secadm authority. The itmuser user ID should have dbadmin authority. Public should have authority=N. If other IDs are displayed and they have authority, you might want to revoke their authority. An example of the command to revoke authority is:
```
db2 connect to WHOUSG   
db2 REVOKE DBADM ON DATABASE FROM USER db2apm BY ALL
db2 REVOKE SECADM ON DATABASE FROM USER db2apm BY ALL
 
```
db2apm in the previous commands is the default Db2 instance user ID. The user ID might be different if you changed it. Complete this step on all three databases for authorities that you want to revoke.
Set up TBSMDB for SCR UDF functions. Complete step 2.n in the Connecting to a remote Db2 server topic. When you are completing step 2.n, for the question: Should the installer create the schema for this database, enter 2 for No instead of 1 for yes as indicated in step 2.n.
Set up SCR UDF functions. Install the jars and create the UDF functions. Complete this step once for each Db2 instance. The *_JAR.jar files are installed in the Db2 instance home directory/sqllib/function/jar/TBSMUDF directory. Issue the following commands as the Db2 instance user ID on the new Db2 server.
```
cd $home		
cd tbsmdb/sql
db2 connect to db_name
db2 -v -f ./tbsmudf_cleanupudf.db2.sql -t
db2 -v -f ./tbsmudf_removeudf.db2.sql -t
db2 -v -f ./tbsmudf_installudf.db2.sql -t
db2 -v -f ./tbsmudf_setupudf.db2.sql -t
db2 connect reset
```
where db_name is the name of one of the three databases. If this is the first database added to the Db2 instance, you might see error messages when running the cleanupudf and removeudf SQL commands. Ignore the messages and run the remaining installudf and setupudf SQL commands.
Verify the databases. As the Db2 instance user ID on the new remote Db2 server connect to your SCR database and run the select statement. For example:
```
db2 connect to scr32;
db2 "select ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE from sysibm.routines " | grep -i TBSMUDF |wc -l  
```
The line count should be about 32 functions.

Results

The three databases are moved to the new Db2 server, the Db2 instance user ID that the databases are using is changed, and the Cloud APM functions are added to the SCR database.

What to do next

Cataloging Db2 databases after changing the Db2 server.