Usually, an application containing embedded SQL needs to be pre-compiled to a source file of the host language with the DB2 APIs, and then bound to the corresponding database. You can use the db2 CLP command PREP to pre-compile embedded SQL applications. By default, the package will be created automatically at pre-compile time. Optionally, you can specify BINDFILE option in PREP command, so that a bind file(.bnd) will be generated, and after pre-compilation, the BIND utility can be used to create a package in the database for this application with the bind file. When the structure or statistics of the tables that an embedded SQL application accesses are changed, the application needs to be rebound explicitly or implicitly.
Perform the following steps for pre-compile and bind.
- Run the PREP command with BINDFILE option to generate a bind file (sample.bnd), as shown in Listing 16.
Listing 16. PREP command with BINDFILE option
=> db2 prep sample.sqc bindfile LINE MESSAGES FOR sample.sqc ------ -------------------------------------------------------------------- SQL0060W The "C" precompiler is in progress. SQL0091W Precompilation or binding was ended with "0" errors and "0" warnings. - Use the BIND utility to create the package in database, as shown in Listing 17.
Listing 17. Using the BIND utility
=> db2 bind sample.bnd LINE MESSAGES FOR sample.bnd ------ -------------------------------------------------------------------- SQL0061W The binder is in progress. SQL0091N Binding was ended with "0" errors and "0" warnings.
Note, both pre-compile and bind require you to be connected to the database.
As previously discussed, no writes are allowed on the HADR standby database. Therefore, bind and rebind are not allowed on standby database either since they will write to the database. When you are trying to bind a package to the standby database, error SQL1773N reason code 5 will be reported as shown in Listing 18.
Listing 18. Errors when bind on standby database
=> db2 bind sample.bnd
LINE MESSAGES FOR sample.bnd
------ --------------------------------------------------------------------
SQL0061W The binder is in progress.
SQL1773N The statement or command requires functionality
that is not supported on a read-enabled HADR standby
database. Reason code = "5".
SQL0082C An error has occurred which has terminated
processing.
SQL0092N No package was created because of previous errors.
SQL0091N Binding was ended with "3" errors and "0" warnings.
When you need to bind or re-bind the package on the standby database, you must bind/re-bind on the primary database. The bind/re-bind operation is shipped and replayed on the standby server since there will be log records written for them.
After bind/re-bind, you can copy the executable binary of the application to the server where the standby database is located, and then run the application. But, pay attention to the bind and re-bind commands. They will trigger the Replay Only window on standby.
Isolation level on standby
Readers on standby database can only run in the Uncommitted Reads(UR) isolation level. The reason for this limitation is that DB2 HADR is based on log shipping. As you know, transactions on the primary server will produce log records, and the log records will be shipped to the standby database. The standby database re-does the log records, the same as rolling forward after a restore database, to ensure data consistency with the primary database. But the locks required for higher isolation levels than UR are not shipped to the standby database, and the readers will not acquire any locks while replaying the log records. In a word, data on the standby database is not protected by any locks, so applications on standby can only read in UR isolation level.
Listing 19. Error when reads in higher isolation than UR
=> db2 "select * from t1 with RR" C1 ----------- SQL1773N The statement or command requires functionality that is not supported on a read-enabled HADR standby database. Reason code = "1".
If some existing read-only applications are implemented in higher isolations than UR, and you don't want them to be broken due to this isolation level error, you can just coerce them running in the UR isolation level quietly by setting the DB2_STANDBY_ISO registry variable, as shown in Listing 20.
Listing 20. Error when reads in higher isolation than UR
=> db2set DB2_STANDBY_ISO=UR
# We need to restart DB2 instance here so that this registry variable take effect
=> db2 connect to testdb
Database Connection Information
Database server = DB2/LINUXX8664 9.7.5
SQL authorization ID = XJCD
Local database alias = TESTDB
=> db2 "select * from t1 with RR"
C1
-----------
1
2
3
3 record(s) selected.
For the read-only applications that need more than UR isolation level, you can only run them on the primary database.
Statistics on the standby database
Statistics of database objects are required by the SQL optimizer when generating access plans. If you don't have accurate statistics, the optimizer may choose an access plan that does not have the lowest cost, and query performance will be affected.
The RUNSTATS utility is used to collect statistics in DB2. It will sample on the specified object (tables or indexes), and calculate the statistics, then write the results into the system catalog tables whose schema is SYSSTAT. Because write operations are not allowed on HADR standby database, the RUNSTATS utility is not available on standby either.
In case you want to update the statistics on standby to improve query performance for the applications on standby database, you have the following alternatives.
- Execute RUNSTATS on the primary database. Yes, the most essential and easiest way is to run RUNSTATS on primary. Since RUNSTATS will write the results into the system catalog tables, corresponding log records will be
- generated, and they will be shipped to standby, and will be replayed. Therefore, the statistics on standby database will be updated.
- Manually update the statistics catalog table directly. However, it is obvious
- that RUNSTATS in solution 1 will have some performance impact on primary database. Some fields in the statistics catalog tables can be updated, so you can update the statistics manually as you need. But, there will be some risk to
- update the catalog tables, so take more care if this solution is adopted.
- Use the optimizer profile to indicate a plan to the optimizer. At the same time, it is possible that some conditions on primary and standby are not identical, even though this is not recommended. For example, one tablespace on the primary database has a container with better performance than standby, the
- statistics on the primary are not applicable on standby. In this circumstance you have to make a different access plan on primary than on standby for the same query.
You can specify optimization guidelines for queries, either in-line guidelines or optimization profiles. Usually, you insert the optimization profiles (XML files) into SYSTOOLS.OPT_PROFILE table, and then enable the profiles before the queries. The DB2 optimizer will generate and choose an access plan according to the optimization profiles. For more details on using optimization profiles, refer to the "Influence query optimization with optimization profiles and statistical views in DB2 9" article that is in the Resources section for more information.
Because write operations are blocked on the standby databases, you can not insert the optimization profiles into SYSTOOLS.OPT_PROFILE on standby. The work around would be insert into the profiles on primary, and then the corresponding log records will be shipped to standby. After the log records are replayed, the optimization profiles will be in standby database, and ready to use.
Handling LOBs on standby servers
Before V9.7 Fix Pack 5, no large objects (LOBs) such as BLOB, CLOB, or DBCLOB were allowed on reads on standby database. All LOBs were stored in a separated tablespace from other non-LOBs columns before DB2 V9.7. When you are trying to read a LOB, you will get a return code of SQL1773N, reason code 1, as shown in Listing 21.
Listing 21. Non-inline LOBs are blocked on standby
=> db2 "select C2 from T" C2 ----------------------- SQL1773N The statement or command requires functionality that is not supported on a read-enabled HADR standby database. Reason code = "1".
In DB2 V9.7, inline LOBs were introduced. These LOBs are stored in the same tablespaces as non-LOB columns. In V9.7 Fix Pack 5 and later, inline LOBs are supported on standby. So, for small LOBs, you should make them inline if possible. To check if LOB column in a row is inline or not, ADMIN_IS_INLINED table function can be used, as shown in Listing 22.
Listing 22. Check if a LOB column is inline or not
=> db2 "select ADMIN_IS_INLINED(c1) from t2"
1
------
1
1 record(s) selected.
A query result of 1 means this column of this row is inline, and 0 means it is not inline.
Conclusion
Beginning with DB2 V9.7 Fix Pack 1, DB2 provides reads on standby capability with the HADR feature. You can put the read only applications on the standby database. This capability can help you to balance the workload on the primary server. Due to the log shipping and replay mechanisms, there will be some limitations on standby for read only applications. This article described these limitations and showed how to make applications on the standby database run effectively, including the following.
- Set DB2_HADR_ROS=on registry variable to enable the RoS feature.
- Make transaction containing DDL statements as short as possible on the primary server to reduce the impact of the replay only window on the standby
- server.
- Bind packages on the primary server, and run corresponding applications on
- the standby server.
- Use Uncommitted Read (UR) isolation level for applications on the standby
- server.
- Make short LOBs inline if possible so that they can be accessed on the standby database.
