Technical tip: High availability on DB2 for z/OS on single LPAR systems

Maintain availability during the test, development, and production cycle

This article introduces a high data availability solution on IBM® DB2® for z/OS® using DB2 BINDs for shops having their test, development and production environments on a single LPAR. Using this method, it is possible to map the test program onto production qualifiers and retrieve data directly from production without the need of data refresh.

Sreeharsha Naik (harsha_pace@yahoo.com), DB2 application DBA, MphasiS

Sreeharsha Naik photoSreeharsha Naik started his DB2 for z/OS career at MphasiS, an HP company. He is currently working as a DB2 application DBA for a manufacturing and telecommunications firm. He has extensive knowledge and experience in DB2 application design, development, and performance tuning. He constantly seeks new challenges and opportunities on DB2 for System z and is very keen on sharing his DB2 knowledge with the technical community. He is an IBM certified database associate (DB2 9 Fundamentals) and the POC for the Chennai DB2 Regional Users Group of IDUG. You can check out his blog at www.db2champ.com.



23 May 2013

Introduction

Data refresh from a production environment to test and development regions can often be time consuming and expensive if there is a frequent need for it. High requirements for data refresh can increase the cost of storage, hardware server, and data server software. Therefore, reducing or eliminating the need to refresh data can bring substantial cost savings for the entire database solution.


How it works

For a Single LPAR environment you will find the architecture similar to what is shown below:

Figure 1. Single LPAR environment
Mainframe LPAR includes test subsystem and production sybsystems

The DBRM libraries are accessible and shared on the mainframe LPAR. This architecture potentially enables a cross link to be established between DB2 objects used by the programs in test and production.

Figure 2. Mainframe LPAR environment with EBPG001 sample program
The figure shows the Mainframe LAPR environment

Let's look at the design and BIND card of a sample program, EBPG001 from the test subsystem.

Listing 1. Sample BIND card program
BIND PACKAGE(AA)                                                 -      
    MEMBER(EBPG001)                                              -      
    LIBRARY('TEST.DBRMLIB')                                      -      
    OWNER(TST0001)                                               -      
    QUALIFIER(TST0001)                                           -      
    CURRENTDATA(NO)                                              -      
    VALIDATE(BIND)      EXPLAIN(YES)        ACTION(REPLACE)      -      
    SQLERROR(NOPACKAGE) ISOLATION(CS)       RELEASE(COMMIT)      -      
    DEGREE(1)                                                    -      
    REOPT(NONE)                                                  -      
    KEEPDYNAMIC(NO)                                              -      
    DBPROTOCOL(DRDA)                                             -      
    ENCODING(EBCDIC)                                             -      
    IMMEDWRITE(NO)                                               -      
    FLAG(I);

Since the QUALIFIER keyword is used in the BIND card, all the unqualified SQL statements in the COBOL program will be qualified with TST0001 as the schema name during the BIND process. If we change the OWNER and QUALIFIER names to the production specifications and BIND the program EBPG001 in production, we achieve high data availability.


Enabling high data availability

By changing the OWNER and the QUALIFIER name to production specifications and performing the BIND operation in the production subsystem, you will now be able to map the test program residing in TEST.DBRMLIB onto production qualifiers.

Listing 2. Mapping the test program onto production qualifiers
BIND PACKAGE(AA)                                                  -      
    MEMBER(EBPG001)                                               -      
    LIBRARY('TEST.DBRMLIB')                                       -      
    OWNER(PRD0001)                                                -      
    QUALIFIER(PRD0001)                                            -      
    CURRENTDATA(NO )                                              -      
    VALIDATE(BIND)      EXPLAIN(YES)        ACTION(REPLACE)       -      
    SQLERROR(NOPACKAGE) ISOLATION(CS)       RELEASE(COMMIT)       -      
    DEGREE(1)                                                     -      
    REOPT(NONE)                                                   -      
    KEEPDYNAMIC(NO)                                               -      
    DBPROTOCOL(DRDA)                                              -      
    ENCODING(EBCDIC)                                              -      
    IMMEDWRITE(NO)                                                -      
    FLAG(I);

Now, if you run the program in production, it will access the production data. Notice how the PROD.DBRMLIB is untouched and unaffected.

Figure 3. Mainframe LPAR environment with PROD.DBRMLIB untouched
The figure shows the mainframe LAPR environment with PROD.DBRMLIB untouched

Prerequisites and precautions

It's the DBA's responsibility to ensure that no DML statements are present before performing the high data availability (HDA) operation. Here are a few more things to be aware of:

  • This HDA method is possible in accounts with TEST and PRODUCTION subsystems residing on a single LPAR.
  • Only unqualified SQL statements should be used in the COBOL source code if you plan to implement this method. Otherwise, BIND may result in a -204 SQL error code.
  • While performing the BIND, ensure that the same program version and name is not found in production. Otherwise, there can be a potential -805 error when the production version is executed.
  • You should not use this method if any INSERT, UPDATE, or DELETE statements are found within the test program, or data can be affected in production.
  • The queries in the program that will undergo this method should be reviewed for an access path check to ensure program stability.
  • DBAs should validate the isolation level in the BIND card. The recommended isolation level for this operation is UR or CS. Ascertaining that one of these isolation levels is being used will help ensure application stability.

Advantages

Here are the advantages of this technique:

  • It eliminates the need for a repeated data refresh.
  • It potentially can save considerable system resources when compared to data refresh.
  • Quick reports can be generated using this HDA method.
  • It enables application developers and testers to perform parallel tests and reporting.

Conclusion

This is a useful technique for shops on a single LPAR. More tests can be performed by application development teams at no extra cost and without the need for a data refresh. The BIND feature is harnessed to its maximum limits using this high data availability concept.

Resources

Learn

Get products and technologies

  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=930975
ArticleTitle=Technical tip: High availability on DB2 for z/OS on single LPAR systems
publish-date=05232013