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
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
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
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.
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.
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.
- Gain skills on DB2 for z/OS at the DB2 for z/OS page on developerWorks.
- Follow developerWorks on Twitter.
- Watch developerWorks on-demand demos ranging from product installation and setup demos for beginners, to advanced functionality for experienced developers.
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.
- Participate in the discussion forum.
- Get involved in the My developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.