Each db2 database has to be configured to enable online backup and you would need to add the 'online' keyword to the db2 backup command. There is overhead associated with enabling the online backup as each database must receive an update to LOGARCHMETH1 configuration parameter (at a minimum ) and have an immediate offline backup taken before the database can be connected again and future online backups can be run.
© 2003 International Business Machines Corporation. All rights reserved.
Important: Read the disclaimer before reading this article.
In addition to providing support for the Structure Query Language (SQL), IBM® DB2® Universal DatabaseTM provides a rich and extensive set of administrative application programming interfaces (APIs). These APIs provide a programming interface into the administrative aspects of DB2 UDB as all of the day-to-day tasks required to properly maintain a DB2 UDB system rely on the DB2 administrative APIs.
The DB2 Control Center, for example, is a Java-based graphical user interface (GUI) tool which calls the DB2 administrative APIs to perform DB2 system, instance, and database administration tasks. DB2 UDB also provides command line tools such as the DB2 CLP to perform similar tasks, which also makes calls to the DB2 administrative APIs.
In addition to the DB2 UDB command line tools, DB2 Universal Database also provides a rich set of system utilities called DB2 system commands. These commands are typically used to perform tasks that have not yet been integrated into the GUI tools. The DB2 UDB system commands are typically invoked from the operating system's shell such as the Windows#xae shell (Wshell), also known as the command prompt.
You can combine the use of SQL, DB2 commands, DB2 UDB system commands, and operating system commands in almost any scripting language from simple Windows shell scripts to more complex scripts written in JScript, VBScript, Object REXX or PerlScript to automate or schedule simple repetitive tasks to more involved complex tasks, to just about every DB2 administrative task imaginable.
This article does not cover the DB2 UDB SQL Procedural Language (SQL/PL). If you are looking for information on SQL/PL, check out the DB2 Developer Domain articles Advanced SQL Procedural Scripting in DB2 v7.2 and Yet Another Article on Advanced Scripting by Paul Yip, as well as his book DB2 SQL Procedural Language for Linux, UNIX and Windows available from IBM Press.
In general, scripting refers to a language that is interpreted. Scripts are created in a text file and are not compiled into assembler or byte code before execution. Windows provides several environments that can be used to host scripting languages. Of these the most commonly ones used to perform system administration tasks are the Windows shell (Wshell) and Windows Scripting Host (WSH).
This article describes the different scripting interfaces available with DB2 UDB on the Windows platforms beginning with an overview of the DB2 administrative APIs, the DB2 command line tools, and the DB2 system commands. I'll show you the best techniques to script DB2 UDB with the Windows shell and provide some working examples you can download from this Website.
DB2 administrative APIs
The DB2 administrative APIs have always been included, documented, and supported as part of DB2 UDB for Linux, UNIX®, and Windows. As DB2 UDB continues to provide additional functionality, such as Split Mirror Images, the API set continues to expand to support these features. These APIs are documented in detail in the DB2 Administrative API Reference. Sample source code for programming with these APIs is also provided with the developer editions of DB2 UDB.
The DB2 administrative APIs are officially supported for the following programming languages:
- C/C++
- COBOL
- Fortran
- REXX
At first glance this might seem a rather short list, given the proliferation of programming languages such as Java, Perl and Visual Basic to name a few. However, most programming languages can call APIs written in other languages, with the C API being the most commonly supported. In addition to having direct access to the DB2 administrative APIs from C, COBOL, FORTRAN, and REXX, these APIs can be accessed indirectly through other methods, such as the Java Native Interface (JNI). In fact, almost all of the DB2 administration tools that ship with DB2 UDB today are written in Java, and Java is not on the short list of programming languages officially supported by the DB2 administrative APIs. The DB2 Control Center accesses these APIs using a Java Native Interface (JNI) developed by IBM.
For those programming and scripting languages, such as VBScript, Jscript, and Perl, that do not have direct access to the DB2 administrative APIs there is yet another alternative. The DB2 CLP, which is a command line interface, provides indirect access to almost all of the DB2 administrative API functionality via DB2 Commands. The DB2 Commands are documented in detail in the DB2 UDB Command Reference.
DB2 UDB command line tools
The DB2 UDB command line tools are components of DB2 UDB that provide support for processing DB2 commands as well as SQL statements. On Windows there are three command line tools available:
You can find these by clicking on Start -> Programs -> IBM DB2 -> Command Line Tools.
DB2 Command Center
The first command line tool is the DB2 Command Center (Figure 1), which provides a GUI for processing commands and SQL statements. You can work in either interactive mode or script (batch) mode or both. Start the DB2 Command Center by clicking on Start -> Programs -> IBM DB2 -> Command Line Tools -> Command Center.
Figure 1. DB2 Command Center
Figure 1 is a screen capture of the DB2 Command Center. In this example we have imported the sample DB2 backup script shown in Listing 1. You can import scripts by selecting Import from the Script menu.
DB2 command line processor (CLP)
In general terms the DB2 Command Line Processor (CLP) can be thought of as a DB2 prompt
DB2 =>
for invoking DB2 commands, much like the Windows command prompt C:
is used for invoking operating system commands. DB2 system commands, discussed later, and operating system commands can also be invoked from any of the DB2 Command Line Tools as long as they are preceded with a bang !
.Figure 2 shows the DB2 CLP along with some basic information on how to get help for the DB2 commands. Start the DB2 CLP by clicking on Start -> Programs -> IBM DB2 -> Command Line Tools -> Command Line Processor.
Figure 2. DB2 command line processor
To find out more about what's new with the DB2 CLP in DB2 UDB v8.1, read the DB2 Developer Domain article Customizing the DB2 Universal Database Command Line Processor by Paul C. Zikopoulos.
DB2 command window
You can think of the DB2 command window as a DB2 shell (
db2cmd.exe
) that extends the Windows shell (cmd.exe
) to provide support for DB2 commands and SQL statements. Commands entered in the DB2 command window must be preceded by DB2
in upper, lower, or mixed case. Operating system commands, such as dir
, are simply processed to the Windows shell (cmd.exe) and do not require a bang (!
).The major difference between the DB2 CLP and the DB2 command window is that the CLP provides a DB2 Command prompt
DB2 =>
, whereas the DB2 command window provides a Windows command prompt where you can enter both DB2 commands and operating system commands. It is the DB2 command window that can be used most effectively to perform DB2 UDB scripting on Windows because it readily supports invoking SQL statements, DB2 commands, DB2 system commands, and operating system commands. Hint: If you attempt to run a DB2 command directly from a Windows shell, you will receive the following error message:
This indicates that the shell cannot process your DB2 command. You simply need to start a DB2 command window by entering
db2cmd.exe
to initialize the command environment. There are several options that you can set to change the default behavior of the DB2 CLP. You can get a list of these options by entering the
list command options
command, as shown in Figure 3. The most common options used to invoke DB2 scripts from the DB2 command windows will be demonstrated in examples that follow. Figure 3. DB2 command window and options
For more information on using the DB2 CLP to script SQL and DDL statements, read the DB2 Developer Domain article DB2's Command Line Processor and Scripting by Blair Adamache. The DB2 command options are also documented in detail in the DB2 UDB Command Reference.
A DB2 script is simply a text file with one or more DB2 commands, although it can contain SQL statements, DB2 system commands, and operating system commands. Although it is not required, it is generally a good idea to develop a standard naming convention for your DB2 scripts by using an appropriate file extension. Some of the most common file extensions are shown in Table 1.
Table 1. Common file extensions for scripts
.db2 | DB2 script containing DB2 commands |
.ddl | DB2 script containing data definition language (DDL) statements |
.sql | DB2 script containing SQL statements |
For illustration purposes, Listing 1 contains a very simple DB2 script to back up the sample database using several DB2 commands. The script can be executed from any of the DB2 command line tools. All of the scripts used in this article are also available for download. These scripts use the default DB2 instance (DB2) and the sample database (SAMPLE). Some of these scripts will require that you install the Windows Resource Kit.
Listing 1. Sample DB2 script to back up sample database (db2backup.db2)
You can execute the above DB2 script from a DB2 command window using the following syntax:
In the above example the DB2 script file (
db2backup.db2
) is executed using the following DB2 command options: - -t
- indicates that each command is terminated, by default with a semicolon (;)
- -v
- indicates that each command should be echoed to standard out
- -f
- indicates that commands are read from an input file
- -l
- indicates that commands are logged to an output file
- -r
- indicates that results are saved to a report file
It is always a good idea to log all DB2 commands to a log file (
db2backup.log
using the -l
option as well as saving the output of the commands to a report file (db2backup.rpt
) using the -r
option. The difference between these options is: - The
-l
option logs the start and completion of each command along with a date and time. - The
-r
option saves the output of each command to a file.
Using both of these options lets you use the report file to view a summary of the script's execution and the log file to view the details of each command.
The DB2 command window itself can also be invoked from a Windows shell by simply typing
db2cmd
from a Windows shell (command prompt). It has a number of useful switches that can be passed to it when it is invoked. Table 2 describes these options:Table 2. Options for the DB2 command window
-c | Execute the DB2 command window and terminate. |
-w | Wait until the DB2 command window terminates. |
-i | Inherit the environment from the invoking shell. |
-t | Inherit the title from the invoking shell. |
You can execute the script in Listing 1 from any Windows shell by first invoking the DB2 command window as in the following example:
The DB2 command window provides return codes for each command that is executed. The return codes are listed in Table 3.
Table 3. DB2 CLP return codes
0 | DB2 command or SQL statement executed successfully |
1 | SELECT or FETCH statement returned no rows |
2 | DB2 command or SQL statement warning |
4 | DB2 command or SQL statement error |
8 | Command line processor system error |
Note: If you are executing statements in interactive mode, the DB2 CLP does not provide a return code for each command.
DB2 system commands
DB2 system commands are a set of command line utilities you can use to perform tasks that for one reason or another have not yet been integrated into the DB2 Control Center or other GUI tools. DB2 system commands are typically invoked from the operating system shell such as the Windows command prompt, but can be invoked from the DB2 Command Center, DB2 Task Center, DB2 CLP, DB2 command window, and of course, your favorite scripting language.
There are close to a hundred DB2 system commands available. See the DB2 Command Reference for a complete list of these commands, along with detailed documentation. It is always a good idea to read through the DB2 UDB Fix Pack release notes for new DB2 system commands because they are frequently addedor enhanced via this method.
Table 4 is a very short list of some of the more popular DB2 system commands that can be very useful when scripting. At first glance some of these might seem unlikely candidates for scripting so a description is provided to give you some idea on how you might use them in a script.
Table 4. Examples of DB2 system commands
db2audit | DB2 provides an audit facility to assist in the detection of unknown or unanticipated access to data. This utility can be used to automate security auditing as described in Scripting scenarios. |
db2batch | Reads SQL statements from either a flat file or standard input, dynamically prepares and describes the statements, and returns an answer set. This DB2 command can be used within a script designed for benchmarking different instance and database configuration parameters. You will want to restart the instance and capture system information such as DB2 registry, instance, and database configurations before invoking this utility. |
db2exfmt | Formats the contents of the explain tables. This DB2 command can be used within a script designed for SQL tuning because you can use it to automate the explaining, extraction, and formatting of SQL statement. Scripting this DB2 command allows a large number of SQL statements to be explained, extracted, and formatted for review at a later time. |
db2chkbk | This utility can be used to test the integrity of a backup image and to determine whether or not the image can be restored. This utility can be very useful at the end of a database backup script to generate a backup completion report. |
db2flsn | Returns the name of the database transaction log file that contains the log record identified by a specified log sequence number (LSN). This utility can be very useful as part of a database transaction log pruning script when log retain is enabled for recovery and you are running data replication and need to determine where to start pruning transaction logs. |
Some of the DB2 system commands can be scripted to simply automate the functionality they already provide. The DB2 Audit Facility (db2audit.exe) is a good example of a utility that can be automated by scripting. Other DB2 system commands provide little value on their own, but can be very useful when combined with other DB2 commands or operating system commands. I'll show you some examples in the Scripting scenarios section of this article.
Windows shell scripts
The Windows shell scripting environment has come a long way from the simple batch files of early Windows operating systems. Windows shell scripts are ideal for scheduling and automating tasks that require the use of either Window shell commands or other programs that have a command line interface.
The Windows shell is an attractive scripting environment for automating DB2 UDB administration tasks for several reasons. First, the Windows shell is integrated into the Windows operating system making it available on all versions of Windows on both clients and servers. Second, Windows shell scripting is relatively simple, making it easy to implement. This allows you to focus on getting the task at hand accomplished. Finally, there are a large number of books, manuals, and reference material available for Windows shell scripting.
The Windows shell scripting environment provides:
- Conditional command execution
- Standard input, output, and error
- Command line arguments
- Environment and system variables
- Local and global variable names
- String and numeric variables
- Arithmetic operators (+, -, *, /, and %)
- Strings, substrings, and substitution
- Conditional statements (if, if else, if not, if defined)
- Iterative processing (range, elements, files, directories)
- Subroutine chaining and nesting (GOTO and CALL)
- Drive and folder localization (pushd and popd)
In addition to these features, Windows shell scripts can be further enhanced by using system utilities that are available with the Windows Resource Kit. Some of these utilities are listed in Table 5.
Table 5. Windows Resource Kit Utilities
choice | Prompts the user to make a choice in a batch program by displaying a prompt and pausing for the user to choose from among a set of keys. You can use this command only in batch programs. |
timethis | Times how long it takes the system to run a given command. |
logtime | Logs the start or finish of command-line programs from a batch file. This is useful for timing and tracking batch jobs such as mail-address imports. |
Uptime | Analyzes a single server by processing the event log to determine reliability, availability, and current uptime. The target system can either be the local system or a remote system. |
logevent | This tool enables you to make entries to the Event Log on either a local or remote computer from the command prompt or a batch file. |
netsvc | You can use this tool to remotely start, stop, pause, continue, and query the status of services from the command line. |
A Windows shell script in the simplest form is a text file that contains one or more commands. The default file extension for Windows shell scripts is
.bat
and .cmd
. Listing 2 is an example of a simple Windows shell script. Listing 2. Windows shell script 'Hello World' (hworld.bat)
There are two basic methods for invoking DB2 commands from a Windows shell script:
- Calling DB2 command window from a Wshell script
- Running a Wshell script from a DB2 command window
Calling a DB2 command window from a Wshell script
Listing 3 is a sample Windows shell script to back up the sample database using the DB2 CLP. This file does not need to execute inside a DB2 command window because it invokes one and then passes it the DB2 backup database command. The value for the environment variable
DB2INSTANCE
sets the default instance to 'DB2', it can also be substituted with the DB2 ATTACH
command. I'll show you this later. Hint: Because the Windows shell does not support named command line parameters, which can be passed to a script in any order, it is a common practice to explicitly define these values as global or local variables within the script.
Listing 3. DB2 backup script invoked from a Windows shell (db2backup2.bat)
The advantage of executing a DB2 command window from within the shell script is that you do not have to ensure that the shell script is executed from within a DB2 command window. The disadvantage of this method is that you can only run a single DB2 command for each invocation of the DB2 command window. You can get around this limitation by placing several DB2 commands in a separate DB2 script and execute the DB2 script using the
-tf
switch as shown in Listing 4. Listing 4 is also a Windows shell script to back up the sample database using the DB2 CLP. This script does not need to execute inside a DB2 command window because it invokes a DB2 command window and then passes it the DB2 script (
db2backup.db2
) that contains multiple DB2 commands, including the backup database command (shown in Listing 5). Listing 4. DB2 backup script invoked from a Windows shell (db2backup3.bat)
Listing 5 shows the DB2 script that is executed by the Windows shell script in Listing 4.
Listing 5. DB2 backup script (db2backup.db2)
Running a Wshell script from a DB2 command window
The advantage of executing a Windows shell script from within a DB2 command window is that you can invoke several DB2 commands within the shell script and do not have to place them in a separate script. This gives you better execution control over each individual DB2 command. The disadvantage of this method is that the execution of the shell script must be performed within a DB2 command window. You can get around this limitation by executing the DB2 command window from any scheduler, including the DB2 Task Center, and passing it the Windows shell script as follows:
In this example, the scheduler will receive the final return code when the DB2 command window exits.
Listing 6 is a sample Windows shell script to back up the sample database using the DB2 backup database command. The script must be invoked from within a DB2 command window.
Listing 6. DB2 backup script invoked from within DB2 Command Window (db2backup4.bat)
If you have installed the Windows Resource Kit, you can change the script as shown in Listing 7. This version of the script uses the logevent utility to write information and error messages to the Windows Event Log instead of standard out.
Listing 7. Sample Script from DB2 Command Window (db2backup5.bat)
Scheduling tasks
The DB2 Task Center provides a graphical user interface for scheduling jobs. This facility has been significantly enhanced in DB2 UDB v8.1. It provides a wizard for creating new tasks that can be DB2 scripts, operating system scripts, MVS shell scripts, and JCL scripts.
As with the DB2 Command Center scripts can be imported from a file. The DB2 Task Center allows you to schedule a task to run locally or remotely on another DB2 UDB system. You can define custom return codes for task completion as well as stop the task in the event of a failure. You can define a list of contacts to be notified upon the success or failure of a task. The DB2 Task Center also allows you to group tasks in categories as shown in Figure 4, which shows we have created a DB2 Maintenance category for the sample DB2 backup script (Listing 1), which we have imported.
Figure 4. DB2 Task Center
On Windows the DB2 Task Center can be started by any of the following means:
- Start -> Program -> IBM DB2 -> General Administration Tools -> Task Center
- Entering the command
db2tc
from a Windows shell - Directly from the DB2 Control Center
You can get more information about the DB2 Task Center from Administration Made Easier: Scheduling and Automation in DB2 Universal Database.
Scripting scenarios
Now that you've learned how DB2 UDB provides the interfaces for scripting, how to access these interfaces from the Windows shell and how you can schedule scripts with the DB2 Task Center, let's take a look at some scenarios where you might use a script to automate a task. These include tasks that are not currently automated, tasks that require the combination of DB2 commands, DB2 system commands, and operating system specific commands, and/or features only available through the DB2 system commands. Let's take a look at the following examples:
Archiving the diagnostic log
One of the easiest tasks that can be automated with a simple Windows Shell script is the periodic archiving of the DB2 diagnostic log. Every DB2 instance uses the DB2 diagnostic log file to write diagnostic information related to the instance and all databases within the instance. The amount of information written to the log is controlled by the instance configuration parameter DIAGLEVEL. Setting the DIAGLEVEL to the highest level of four (4) can provide an invaluable amount of information during problem determination. However, at this level the DB2 diagnostic log can quickly grow to over a gigabyte in size.
Currently DB2 does not provide any facility to automatically archive or prune the DB2 diagnostic log. One strategy for managing the DB2 diagnostic log file is to periodically archive the DB2 diagnostic log and keep these archives readily available for a number of days. After a given number of days you can simply delete the archive, because it will have already been backed up to tape during periodic system backups. This task can easily be accomplished using a Windows shell script as shown in Listing 8.
Listing 8. DB2 Archive Diagnostic Log script (db2arcdiag.bat)
The above Windows shell script assumes the
db2diag.log
file is located in the default DB2 DIAGPATH and archives (renames) it to db2diag.YYYYMMDD.log
based on the date the script is executed. It then deletes any db2diag.YYYYMMDD.log
files that are seven days or older. Collecting DB2 system information
Another task that can be quickly scripted using the Windows shell and DB2 commands is the collection of relevant system, instance, and database information. We can collect a snapshot of system resources, DB2 registry variables, instance and database configuration parameters, as well as database resource utilization. Having such a script always comes in handy before you start making changes to your database system.
Listing 9. DB2 Get Configuration script (db2getconf.bat)
You might even store this information in tables within your database so that this information is saved along with the database backup image. Maintaining a periodic history of this system information provides an invaluable asset that can be used for future capacity planning.
DB2 security auditing
Now let's take a look at a good example of a DB2 system command that can be very useful when automated with a scripting language. The DB2 Audit Facility is a utility that can be used to substantially enhance DB2 instance and database security auditing. It is completely managed and controlled by a DB2 system command called
db2audit.exe
. This command enables you to completely implement very strong DB2 UDB security auditing practices. You can configure, start, and stop security auditing as well as flush and extract audit data from the facility. This process would be a very time consuming day-to-day task, but it can easily be automated with a scripting language. For example, after you have configured and started the DB2 Audit Facility, you might want to create and schedule a script to periodically flush the audit log and extract the audit data into a report file. Listing 10. DB2 Get Auditing Report script (db2getaudit.bat)
This data can then be imported or loaded into a security audit database that can automatically forward alert notifications via triggers and/or stored procedures to the appropriate administrators.
Conclusion
In this article we have seen how all the administrative tasks required to properly maintain a DB2 UDB system rely on the DB2 administrative APIs and how the DB2 Command Line Tools and DB2 system commands can be used to perform database administrative tasks from a command line interface. We demonstrated how you can combine DB2 commands, DB2 system commands, and operating system command in a Windows shell scripts to automate many of these tasks.
In the second article in this series we will explore yet another scripting environment available on the Windows platform, the Windows Scripting Host. How you can combine the use of DB2 commands and DB2 system commands to develop more complex scripts with native Windows scripting languages such as Jscript and VBScript. We will also explore the DB2 Windows Management Instrumentation (WMI) provider that is new with DB2 UDB v8.1.
Disclaimer
This article contains sample code. IBM grants you ('Licensee') a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
Downloadable resources
- Code sample (listings.zip | 6KB)
Comments
Sign in or register to add and subscribe to comments.
Question & Answer
Question
You plan to setup High Availability Data Replication between two DB2 databases and need step by step instructions
Answer
These instructions allow you to setup HADR for DB2 on a Windows environment. However the same commands can be used for Linux and UNIX by simply changing the 'D:' to a valid path.
ON THE PRIMARY:
1. Create sample db using the db2sampl command.
db2sampl
2. Enable it for log archiving.
db2 update db cfg for <DBNAME> using LOGARCHMETH1 LOGRETAIN
Above command will enable to database for log archiving and keep the logs in the same active log directory. This will also place the db in backup pending state.
3. Take an offline backup.
db2 'backup database <DBNAME>'
NOTE: An offline backup is NOT mandatory. An online backup also can be used. But online backups can take longer to bring the HADR pair in to peer state. For sake of simplicity, we are using offline backup here.
4. Setting up HADR cfg parameters on Primary database.
db2 update db cfg for <DBNAME> using HADR_LOCAL_HOST <IP ADDRESS OF PRIM>
db2 update db cfg for <DBNAME> using HADR_LOCAL_SVC <PORT # on PRIM>
db2 update db cfg for <DBNAME> using HADR_REMOTE_HOST <IP ADDRESS OF STNDBY>
db2 update db cfg for <DBNAME> using HADR_REMOTE_SVC <PORT # on STNDBY>
db2 update db cfg for <DBNAME> using HADR_REMOTE_INST <INSTNAME OF STNDBY>
db2 update db cfg for <DBNAME> using LOGINDEXBUILD ON
5. Take an offline backup to be used for setting HADR.
db2 'backup database <DBNAME>'
ON THE STANDBY MACHINE:
Ensure both the servers are on the same db2level so that a mismatch situation does not occur. Run 'db2level' command on both the servers to check whether they are on the same DB2 Version and Fix Pack.
6. FTP the backup image (from the primary machine) to the STANDBY MACHINE
7. db2 'restore database DBNAME'
8. Setting up HADR cfg parameters on standby database.
db2 update db cfg for <DBNAME> using HADR_LOCAL_HOST <IP ADDRESS ON STANDBY>
db2 update db cfg for <DBNAME> using HADR_LOCAL_SVC <PORT # ON STANDBY>
db2 update db cfg for <DBNAME> using HADR_REMOTE_HOST <IP ADDRESS ON PRIM>
db2 update db cfg for <DBNAME> using HADR_REMOTE_SVC <PORT # ON PRIM>
db2 update db cfg for <DBNAME> using HADR_REMOTE_INST <INSTNAME ON PRIM>
9. db2 start hadr on database <DBNAME> as standby
ON THE PRIMARY MACHINE:
10. Starting up HADR on the primary server
db2 start hadr on database <DBNAME> as primary
--Verifing HADR is up and running
db2pd -db <DBNAME> -hadr
Here's the steps on switching roles (PRIMARY/STANDBY) between the two
machine (CM01 and CM11).
1. ON PRIMARY (CM01): db2 connect to <dbname>
2. ON PRIMARY (CM01): db2 'create table tab1 (col1 int)'
3. ON PRIMARY (CM01): db2 'insert into tab1 values (1)' -insert 20 rows
4. ON PRIMARY (CM01): power down the Primary --> db2stop force
5. ON STANDBY (CM11): db2 takeover hadr on database <dbname> by force
6. The STANDBY instance on CM11 (DB2) is now the primary
7. ON CM11: db2pd -db <dbname> -hadr (the ROLE should state: PRIMARY)
8. ON CM11: db2 connect to <dbname>
9. ON CM11: db2 'select * from tab1' -You should see the 20 rows inserted
10. ON CM11: db2 'create table tab2 (col1 int)'
11. ON CM11 db2 'insert into tab2 values (1)' -insert about 20 rows
12. ON CM01: db2 start hadr on database <dbname> as standby
13. ON CM01: db2pd -db <dbname> -hadr (the ROLE should state: STANDBY)
14. on CM01: db2 takeover hadr on database <dbname>
15. on CM01: db2pd -db <dbname> -hadr (the ROLE should state: PRIMARY)
16. ON CM01: db2 'select * from tab2' -you should be able to see the 20 rows inserted
17. on CM11: db2pd -db <dbname> -hadr (the ROLE should state; STANDBY)
Note:
1. Hostname of the HADR pair cannot be the same on both the servers.
2. The instance name and the underlying userid on UNIX systems can be different. Make sure to update the correct name of the instance for the db cfg parameter HADR_REMOTE_INST to the correct value.
ON THE PRIMARY:
1. Create sample db using the db2sampl command.
db2sampl
2. Enable it for log archiving.
db2 update db cfg for <DBNAME> using LOGARCHMETH1 LOGRETAIN
Above command will enable to database for log archiving and keep the logs in the same active log directory. This will also place the db in backup pending state.
3. Take an offline backup.
db2 'backup database <DBNAME>'
NOTE: An offline backup is NOT mandatory. An online backup also can be used. But online backups can take longer to bring the HADR pair in to peer state. For sake of simplicity, we are using offline backup here.
4. Setting up HADR cfg parameters on Primary database.
db2 update db cfg for <DBNAME> using HADR_LOCAL_HOST <IP ADDRESS OF PRIM>
db2 update db cfg for <DBNAME> using HADR_LOCAL_SVC <PORT # on PRIM>
db2 update db cfg for <DBNAME> using HADR_REMOTE_HOST <IP ADDRESS OF STNDBY>
db2 update db cfg for <DBNAME> using HADR_REMOTE_SVC <PORT # on STNDBY>
db2 update db cfg for <DBNAME> using HADR_REMOTE_INST <INSTNAME OF STNDBY>
db2 update db cfg for <DBNAME> using LOGINDEXBUILD ON
5. Take an offline backup to be used for setting HADR.
db2 'backup database <DBNAME>'
ON THE STANDBY MACHINE:
Ensure both the servers are on the same db2level so that a mismatch situation does not occur. Run 'db2level' command on both the servers to check whether they are on the same DB2 Version and Fix Pack.
6. FTP the backup image (from the primary machine) to the STANDBY MACHINE
7. db2 'restore database DBNAME'
8. Setting up HADR cfg parameters on standby database.
db2 update db cfg for <DBNAME> using HADR_LOCAL_HOST <IP ADDRESS ON STANDBY>
db2 update db cfg for <DBNAME> using HADR_LOCAL_SVC <PORT # ON STANDBY>
db2 update db cfg for <DBNAME> using HADR_REMOTE_HOST <IP ADDRESS ON PRIM>
db2 update db cfg for <DBNAME> using HADR_REMOTE_SVC <PORT # ON PRIM>
db2 update db cfg for <DBNAME> using HADR_REMOTE_INST <INSTNAME ON PRIM>
9. db2 start hadr on database <DBNAME> as standby
ON THE PRIMARY MACHINE:
10. Starting up HADR on the primary server
db2 start hadr on database <DBNAME> as primary
--Verifing HADR is up and running
db2pd -db <DBNAME> -hadr
Here's the steps on switching roles (PRIMARY/STANDBY) between the two
machine (CM01 and CM11).
1. ON PRIMARY (CM01): db2 connect to <dbname>
2. ON PRIMARY (CM01): db2 'create table tab1 (col1 int)'
3. ON PRIMARY (CM01): db2 'insert into tab1 values (1)' -insert 20 rows
4. ON PRIMARY (CM01): power down the Primary --> db2stop force
5. ON STANDBY (CM11): db2 takeover hadr on database <dbname> by force
6. The STANDBY instance on CM11 (DB2) is now the primary
7. ON CM11: db2pd -db <dbname> -hadr (the ROLE should state: PRIMARY)
8. ON CM11: db2 connect to <dbname>
9. ON CM11: db2 'select * from tab1' -You should see the 20 rows inserted
10. ON CM11: db2 'create table tab2 (col1 int)'
11. ON CM11 db2 'insert into tab2 values (1)' -insert about 20 rows
12. ON CM01: db2 start hadr on database <dbname> as standby
13. ON CM01: db2pd -db <dbname> -hadr (the ROLE should state: STANDBY)
14. on CM01: db2 takeover hadr on database <dbname>
15. on CM01: db2pd -db <dbname> -hadr (the ROLE should state: PRIMARY)
16. ON CM01: db2 'select * from tab2' -you should be able to see the 20 rows inserted
17. on CM11: db2pd -db <dbname> -hadr (the ROLE should state; STANDBY)
Note:
1. Hostname of the HADR pair cannot be the same on both the servers.
2. The instance name and the underlying userid on UNIX systems can be different. Make sure to update the correct name of the instance for the db cfg parameter HADR_REMOTE_INST to the correct value.
Related URL
[{'Product':{'code':'SSEPGG','label':'DB2 for Linux- UNIX and Windows'},'Business Unit':{'code':'BU001','label':'Analytics Private Cloud'},'Component':'HADR - Other','Platform':[{'code':'PF002','label':'AIX'},{'code':'PF010','label':'HP-UX'},{'code':'PF016','label':'Linux'},{'code':'PF027','label':'Solaris'},{'code':'PF033','label':'Windows'}],'Version':'9.7;10.1;10.5','Edition':'}]
Document Information
Modified date:
23 June 2018
23 June 2018