Switching database connections for a single SAP application server

Prerequisites for planned Db2 failover of application server ihlscoh1

A connection profile that contains failover connections must be defined and activated as a prerequisite before step 3. Activate Db2 connection failover from topic Updating Db2 or z/OS.

Defining connection profiles

SAP Systems that are installed with SWPM 1.0 SP 24 or newer versions have a connection profile for the ABAP stack, called db2dsdriver.cfg. For the Java™ stack, the connection profile is called config.xml. Each connection profile contains the possible connections between the SAP application servers and the Db2 members. The SAP application server builds up its DB connection in the same sequence as is listed in the connection profile.

For more information and references about how to configure the CLI failover, see Db2 connection failover for ABAP instances.

In this example, the ABAP connection profile db2dsdriver.cfg displays three possible connections to the database for each SAP application server. If the primary Db2 member is not reachable, the application server attempts to establish the connection with the second member. If this second member also cannot be reached, it attempts to establish the connection with the third Db2 member.

<configuration>
    <dsncollection>
       <dsn alias="HPA" name="COHHPA0" host="coh1vipa.boeblingen.de.ibm.com" port="12366"/>
    </dsncollection>
    <databases>
        <database name="COHHPA0" host="coh1vipa.boeblingen.de.ibm.com" port="12366">
          <parameter name="ConnectionTimeout" value="16"/>
          <parameter name="tcpipConnectTimeout" value="5"/>
          <parameter name="SecurityTransportMode" value="SSL"/>
          <parameter name="Authentication" value="CERTIFICATE" />
          <parameter name="SSLClientLabel" value="r3okkoli" />
          <parameter name="SSLClientKeystoredb" value="/sapmnt/HPA/global/certs/brokkoli.kdb" />
          <parameter name="SSLClientKeystash" value="/sapmnt/HPA/global/certs/brokkoli.sth" />
            <acr>
              <parameter name="acrRetryInterval" value="0"/>
              <parameter name="affinityFailbackInterval" value="300"/>
              <parameter name="enableAcr" value="true"/>
              <parameter name="enableSeamlessAcr" value="true"/>
              <parameter name="maxAcrRetries" value="3"/>
              <alternateserverlist>
                <server name="HPA1" hostname="coh1vipa.boeblingen.de.ibm.com" port="12366"/>
                <server name="HPA2" hostname="coh2vipa.boeblingen.de.ibm.com" port="12366"/>
                <server name="HPA3" hostname="coh3vipa.boeblingen.de.ibm.com" port="12366"/>
              </alternateserverlist>
              <affinitylist>
                <list name="list1" serverorder="HPA1,HPA3,HPA2"/>
                <list name="list2" serverorder="HPA2,HPA3,HPA1"/>
              </affinitylist>
              <clientaffinitydefined>
                <client name="D62" hostname="ihlscoh1" listname="list1"/>
                <client name="D63" hostname="ihlscoh2" listname="list2"/>
              </clientaffinitydefined>
            </acr>
        </database>
    </databases>
</configuration>

The db2dsdriver.cfg file contains sections where you define your data source, the database connection, and the client information. From the CLI point of view, the SAP application server hosts are the clients.

Within the <dsncollection> section, you define the data source name (<dsn>) with an arbitrary alias. The <database> section contains definitions of the database location. In the <acr> (automatic client reroute) subsection, you define the parameters, which influence the CLI failover behavior.

In the example, the SAP system identifier HPA was chosen as an alias for the data source name.

In the <database> section, the database location with the Db2 static location name COHHPA0 on host coh1vipa, is available via port 12366. The DDF port must be identical for all members of the data sharing group.

In the <paramter> section, the affinityFailbackInterval parameter is set to 300 seconds. This can be a valid setting if you trigger the planned Db2 failover from the Db2 server side by stopping the DDF or dynamic location alias (see Method 1 from Step 5: Initiate Db2 connection failover). The setting has the effect that once the Db2 server has been restarted, the SAP work processes automatically fails back to their primary member within this interval. To control the Db2 planned failovers via SAP DBACOCKPIT or ABAP report RSDB2SWITCH, the parameter affinityFailbackInterval needs to be set to 0.

In the <alternateserverlist> subsection, the Db2 members HPA1, HPA2, and HPA3 are defined with their virtual hostnames.

In the <affinitylist> section, two connection lists are defined. Each list defines a parameter serverorder is an ordered list of Db2 members. In case of a connection failure, the Db2 CLI driver reroutes the client database connections according to this order.

In the <clientaffinitydefined> section, you assign a list with its serverorder to an application server host.

For example, the Db2 CLI driver on host ihlscoh1 uses the routing that is defined by list1 , which means that it primarily connects to the Db2 member HPA1. If HPA1 is not accessible, it attempts to establish the connection with HPA3. If HPA3 also is not accessible, it connects to HPA2.

The same principle applies for the SAP application server instance D63, with the only difference, that it has another routing list, with another Db2 member sequence.

Example: DDF definition on host coh1vipa for Db2 member HPA1

-HPA1 DSNLTDDF DISPLAY DDF REPORT FOLLOWS:                
STATUS=STARTD                                              
LOCATION           LUNAME            GENERICLU         WLB 
COHHPA0            DEIBMIPS.IPXAOCY1 -NONE             DFLT
TCPPORT=12060 SECPORT=12366 RESPORT=12066 IPNAME=-NONE     
IPADDR=::10.101.4.210                                      
SQL    DOMAIN=coh1vipa.boeblingen.de.ibm.com               
RESYNC DOMAIN=coh1vipa.boeblingen.de.ibm.com               
MEMBER IPADDR=::10.101.4.210                               
CURRENT DDF OPTIONS ARE:                                   
PKGREL = COMMIT                                            
DSNLTDDF DISPLAY DDF REPORT COMPLETE

Theoretically, you can define one db2dsdriver.cfg file, or multiple files with different names, for example, one per SAP application server. It is recommended that you use only one db2dsdriver.cfg file and place it into the SAP directory /sapmnt/<sid>/global. This has the advantage that you must maintain only one connection configuration file. In addition, the SAP global file system can always be accessed because it is exported by the highly available NFS Server on z/OS.

For the Java stack, the connection profile config.xml looks similar to the db2dsdriver.cfg file and has the same effect. The config.xml file is generated by the SAP installation and written into the directory /sapmnt/<sid>/global.

The SSL parameters are set for an encrypted certificate-based authentication/data traffic to the database. For information how to setup this in Transport Layer Security (TLS), see TLS with Certificate Authentication for SAP Application Server Connections to Db2 on IBM Z.

For information about how to modify the db2dsdriver.cfg or the config.xml file, see the Database Administration Guide for SAP on IBM® Db2 for z/OS .

Verification activities before the switch

Before you remove the Db2 connections from Db2 member HPA1 as described in step 3. Activate Db2 connection failover from topic Updating Db2 or z/OS, you need to verify the current SAP application server distribution.

You can do this with the MVS command DISPLAY THREAD for each Db2 member. Here is an example for Db2 member HPA1:

DSNV401I  -HPA1 DISPLAY THREAD REPORT FOLLOWS -              
DSNV402I  -HPA1 ACTIVE THREADS - 305                         
NAME     ST A   REQ ID           AUTHID   PLAN     ASID TOKEN
SERVER   RD *  6112 HPADIA005    R3USER   DISTSERV 0081  5594
V485-TRUSTED CONTEXT=SAP_R3USER_H1,                        
     SYSTEM AUTHID=R3USER,                                 
     ROLE=  *                                              
V437-WORKSTATION=DB2                                       
     USERID=SAP*                                           
     APPLICATION NAME=SAPLSDB2CCMS_ANALYSIS                
V442-CRTKN=10.101.4.214.58022.241202180606                 
V445-GA6504D6.E2A6.241202180606=5594 ACCESSING DATA FOR    
 ::FFFF:10.101.4.214                                       
SERVER   RD *   240 HPAUP2012    R3USER   DISTSERV 0081  5604
V485-TRUSTED CONTEXT=SAP_R3USER_H1,                        
     SYSTEM AUTHID=R3USER,                                 
     ROLE=  *                                              
V437-WORKSTATION=ihlscoh1                                  
     USERID=R3USER                                         
     APPLICATION NAME=HPAUP2012                            
V442-CRTKN=10.101.4.214.57922.241202180616                 
 V445-GA6504D6.E242.241202180616=5604 ACCESSING DATA FOR
 ::FFFF:10.101.4.214  
. . .

The command output displays useful information about each thread, for ABAP threads as well as for Java threads.

For example:

• The thread ID=HPADIA005 shows some characteristics of the ABAP work process:
  • HPA is the SAP system identifier
  • DIA indicates that the type of the SAP work process is an SAP dialog work process
  • 005 indicates the SAP work process number (see SAP transaction SM50). It also indicates the number of the SAP developer trace file (dev_w05)
• The AUTHID=R3USER (Db2 schema for ABAP stack) states that this is a thread connection from ABAP stack. In case the thread comes from a Java stack, the AUTHID would be the Db2 schema SAPJAVA.

The DISPLAY THREAD command output for Db2 members HPA2 and HPA3 shows similar connections from the application server hosts ihlscoh2 and ihlscoh2.

You can also check the current database connections of the SAP ABAP application server instances in your SAPGUI using SAP transaction DBACOCKPIT. Navigate from Performance to Thread Activity, and from the following panel, you can

• Toggle with the button on the top left (or Shift+F1) between the display of active threads or all threads (inclusive idle threads)
• Choose in field “Mem” either to display the DB connections to one specific Db2 member only or to all Db2 members

For example, the Db2 thread activity (see Figure 1) shows all Db2 connections to Db2 member HPA1, like it is defined in the db2dsdriver.cfg file.

The listed DB connections to Db2 member HPA1 shows that only the ABAP work processes from the SAP application server host ihlscoh1 are connected to HPA1. Before the Db2 member HPA1 can be stopped for maintenance, those DB connections must be moved from HPA1 to another DB member.

To see Db2 thread information for a remote database of a Java system for example, you need to add the Java system for remote monitoring to SAPCL. Please see SAP on IBM Z Database Administration Guide under Creating and Configuring Stored Procedure SAPCL. Then you can select the remote / Java system in the System list.

Switch using SAP transaction DBACOCKPIT

The subsequent information describes how to initiate a database connection switch using the failover configuration tool from SAP transaction DBACOCKPIT. You can use the configuration tool to change the failover configuration, and to verify and activate the new configuration on fly.

To perform the connection move, you need to log on in SAPGUI to any SAP application server. From SAP transaction DBACOCKPIT, select Configuration, then launch Failover Configuration and follow these five instructions:

1. Load the current failover configuration if not done automatically when entering the failover configuration tool.
2. To perform the failover configuration changes, go to the rider with the Affinity Lists. On each list, move the HPA1 from Current Affinity List to the field Alternative Db2 Members. It is important to remove HPA1 from each list to ensure that the HPA1 can no longer be connected to from any list. Db2 members parked in Alternative Db2 Members are removed from the serverorder lists only. The Db2 member definition as an alternate database connection will stay.
3. Perform a configuration check.
4. If the check passed successfully, save the new failover configuration. This writes the new failover configuration file (db2dsdriver.cfg) and a backup file of the original version into the SAP global directory.
5. To activate the changed db2dsdriver.cfg, all work processes need to pick it up at start time. Therefore, either restart the application server or for more seamless activation, use the following recommended procedure:
   • Activate profile parameter (with RZ11) rdisp/wp_auto_restart and rdisp/noptime. Set both to 60 (seconds) for example.
   • Wait until all work processes have been restarted.
   • Deactivate both parameter again (set both to 0) to avoid interference with the default hourly Db2 thread recycle and planned failover that is initiated from DBA Cockpit or program interface.

The saved configuration with the new server order (as specified with attribute serverorder in db2dsdriver.cfg) shows that HPA1 is still known as a possible database connection. HPA1 has only been removed from all server orders:

<configuration>
  <dsncollection>
    <dsn alias="HPA" name="COHHPA0" host="coh1vipa.boeblingen.de.ibm.com" port="12366"/>
  </dsncollection>
  <databases>
    <database name="COHHPA0" host="coh1vipa.boeblingen.de.ibm.com" port="12366">
      <parameter name="ConnectionTimeout" value="16"/>
      <parameter name="tcpipConnectTimeout" value="5"/>
      <parameter name="SecurityTransportMode" value="SSL"/>
      <parameter name="Authentication" value="CERTIFICATE"/>
      <parameter name="SSLClientLabel" value="r3okkoli"/>
      <parameter name="SSLClientKeystoredb" value="/sapmnt/HPA/global/certs/brokkoli.kdb"/>
      <parameter name="SSLClientKeystash" value="/sapmnt/HPA/global/certs/brokkoli.sth"/>
      <acr>
        <parameter name="acrRetryInterval" value="0"/>
        <parameter name="affinityFailbackInterval" value="300"/>
        <parameter name="enableAcr" value="true"/>
        <parameter name="enableSeamlessAcr" value="true"/>
        <parameter name="maxAcrRetries" value="3"/>
        <alternateserverlist>
          <server name="HPA1" hostname="coh1vipa.boeblingen.de.ibm.com" port="12366"/>
          <server name="HPA2" hostname="coh2vipa.boeblingen.de.ibm.com" port="12366"/>
          <server name="HPA3" hostname="coh3vipa.boeblingen.de.ibm.com" port="12366"/>
        </alternateserverlist>
        <affinitylist>
          <list name="list1" serverorder="HPA3,HPA2"/>
          <list name="list2" serverorder="HPA2,HPA3"/>
        </affinitylist>
        <clientaffinitydefined>
          <client name="D62" hostname="ihlscoh1" listname="list1"/>
          <client name="D63" hostname="ihlscoh2" listname="list2"/>
        </clientaffinitydefined>
      </acr>
    </database>
  </databases>
</configuration>

After the new failover configuration has been activated, the restarted SAP processes request the CLI to connect to the database. And CLI connects to the next available Db2 member according to the new server order. In the example, CLI connects the database request from the SAP application server ihlscoh1 to HPA3. If HPA3 is not available, then CLI connects the database request to HPA2.

This can be verified from SAP transaction DBACOCKPIT in the thread activity. There should be no thread activities any more for Db2 member HPA1, or if you list all thread activities for all members, there should be threads only on HPA2 and HPA3.

Because no Db2 threads are connected to Db2 subsystem HPA1, the subsystem can now be stopped with system automation, see step 4 (stop the Db2 data sharing members in the LPAR).

Switch using SAP report RSDB2SWITCH

To move away the ABAP database connection, you can alternatively use the SAP report RSDB2SWITCH.

Start the report from transaction SE38 and enter the parameters:

NEWDBCON

Enter the logical name of the target database. This entry is case-sensitive and therefore must be entered in the same way as the server name is defined in the db2dsdriver.cfg file.

ALLAPPS

leave this entry field blank if only the DB connection of the current logged on SAP application server should be moved to the new DB connection (NEWDBCON). If you enter X, then the database connections of all SAP application servers move to the new DB connection.

WAIT

When you enter X, then the report provides feedback only when all DB connections have been moved to the new DB connection.

MAX_WAIT

Enter the time (in seconds) after which the report should end and show the status of moved DB connections. If not all connections have been moved restart the report until all DB connections have been moved.

The parameters WAIT or MAX_WAIT make sense especially when the report is scheduled as a batch job.

Example of RSDB2SWITCH and its entry fields:

Using RSDB2SWITCH has the same effect as switching the database connection via SAP transaction DBACOCKPIT. In both cases, the connection moves to member HPA2.

Switch by stopping the DDF or dynamic location alias

Database connections can be rerouted by stopping a Db2 member location. Depending on the Db2 member setup this can be done by the stop-command of DDF or if implemented by stop-command of its dynamic DDF location alias ( MODIFY DDF ALIAS(<alias-name>) STOP). Stopping the dynamic location alias has the advantage, that no DB connection can interfere while Db2 or DDF will be stopped or started during maintenance. For ABAP instances this is an alternative to switching database connections via DBACOCKPIT or RSDB2SWITCH. For Java instances please see Db2 connection failover for Java instances.

This example shows the connection move away from Db2 member HPA1 by stopping DDF:

-HPA1 STOP DDF                   
DSNL021I  -HPA1 STOP DDF COMMAND ACCEPTED
DSNL005I  -HPA1 DDF IS STOPPING                   
DSNL006I  -HPA1 DDF STOP COMPLETE      

There are no longer any threads that are connected to Db2 member HPA1:

-HPA1 DIS THREAD(*) LIMIT(*) 
DSNV401I  -HPA1 DISPLAY THREAD REPORT FOLLOWS -
DSNV419I  -HPA1 NO CONNECTIONS FOUND
DSN9022I  -HPA1 DSNVDT '-DIS THREAD' NORMAL COMPLETION

Stopping a Db2 member

As soon as there are no more connections the Db2 member can be stopped. If your Db2 subsystem is under SA z/OS control, you need to use SA z/OS commands to stop a member. For the SAP System HPA, the first Db2 member HPA1 is modeled in SA z/OS application group HPA1_X/APG. In order to stop member HPA1, issue a STOP vote for this group. As soon as HPA1_X/APG is unavailable (stopped), consider changing its SA z/OS AUTOMATION FLAG to NO, or alternatively, you can suspend the HPA1_X resource group. Automation then does not take any action if the Db2 member has to be restarted manually in maintenance mode which may be required as part of the Db2 maintenance.

Switching back database connections

First, you must restart the Db2 member HPA1 either by an explicit operator command, or by resetting the AUTOMATION FLAG and canceling the STOP vote for HPA1_X/APG in SA z/OS. Or if you suspended the member group from system automation, then you must cancel the suspend request from the INGVOTE list.

The Db2 member restarts.

If you used STOP DDF to stop Java or ABAP database connections, then a restart of the Db2 member triggers a reconnect of Java or ABAP connections to this member, provided the parameter affinityFailbackInterval was set to a positive value in the config.xml (Java) or db2dsdriver.cfg (ABAP) configuration files. If you stopped the DDF location alias, then ensure that the alias is restarted, in order that the failback mechanism will work.

ABAP connections of ihlscoh1 can be switched back with SAP transaction DBACOCKPIT and failover configuration tool as follows:

The most easy and error-free way is to copy the backup of the original failover configuration file into db2dsdriver.cfg on operating system level. Then from the SAP transaction DBACOCKPIT, open Configuration and launch Failover Configuration. If not done automatically, load the current failover configuration when entering the failover configuration tool. Db2 member HPA1 should be back in all server orders. A configuration check should pass without errors because it is the original failover configuration, which was active before the Db2 maintenance.

Alternatively, when you have no access to the operating system level, then in SAPGUI log-on to any SAP application server. From SAP transaction DBACOCKPIT, select Configuration, then launch Failover Configuration and again follow these instructions (see also Figure 2 ):

1. If not done automatically, load the current failover configuration when entering the failover configuration tool.
2. Perform the failover configuration changes on the section with the Affinity Lists. For each list, move back the HPA1 from the Alternative Db2 Members field into the Current Affinity List and move HPA1 up or down until it is on the correct position.
3. Perform a configuration check.
4. If the check passed successfully, save the failover configuration. This again writes a new failover configuration file (db2dsdriver.cfg) and a backup file of the previous failover configuration into the SAP global directory. As well you can verify the changed content of the configuration file in Failover Configuration Tool of DPA Cockpit under the rider Result.
5. See Switching back database connections on how to reload the db2dsdriver.cfg changes for an application server. You need to do this for all application servers.

Verify that the ABAP threads from the application server host ihlscoh1 are switched back to its primary DB member HPA1 (step 9 from the procedure in topic Updating Db2 or z/OS ). To do this use the DBACOCKPIT or DIS THREAD command as previously described.

Restart the SAP batch processing for SAP application server on host ihlscoh1 SAP transaction RZ03 (step 10 Restart all SAP batch processing from topic Updating Db2 or z/OS).

This is the procedure for Db2 member HPA1. Repeat the procedure (steps 1 - 10) in topic Updating Db2 or z/OS for the maintenance of Db2 members HPA2 and HPA3.