SAS/ACCESS Software for Relational Databases: Reference

Maximizing DB2 Performance

Among the factors that affect DB2 performance are the size of the table that is being accessed and the form of the SQL SELECT statement. If the table that is being accessed is larger than 10,000 rows (or 1,000 pages), you should evaluate all SAS programs that access the table directly. When you evaluate the programs, consider the following questions:

Does the program need all of the columns that the SELECT statement retrieves?
Do the WHERE clause criteria retrieve only those rows that are needed for subsequent analysis?
Is the data going to be used by more than one procedure in one SAS session? If so, consider extracting the data into a SAS data file for SAS procedures to use instead of allowing the data to be accessed directly by each procedure.
Do the rows need to be in a particular order? If so, can an indexed column be used to order them? If there is no index column, is DB2 doing the sort?
Do the WHERE clause criteria allow DB2 to use the available indexes efficiently?
What kind of locks does DB2 need to acquire?
Are the joins being passed to DB2?
Can your DB2 system use parallel processing to access the data more quickly?

DB2 has a Resource Limit Facility to limit the execution time of dynamic SQL statements. If the time limit is exceeded, the dynamic statement is terminated and the SQL code -905 is returned. The following list describes several situations in which the RLF could stop a user from consuming large quantities of CPU time:

An extensive join of DB2 tables with the SAS System SQL procedure.
An extensive search by the FSEDIT, FSVIEW, or FSBROWSE procedures or an SCL application.
Any extensive extraction of data from DB2.
An extensive select.
An extensive load into a DB2 table. In this case, you can break up the load by lowering the commit frequency.

There are several things that you can do in your SAS application to make the DB2 engine perform better:

Set the SAS system option DB2DBUG. This option prints the dynamic SQL that is generated by the DB2 engine and all other SQL that is executed by the DB2 engine to the SAS log. You can then verify that all WHERE clauses, PROC SQL joins, and ORDER BY clauses are being passed to DB2. This option is for debugging purposes and should not be set once the SAS application is used in production. The NODB2DBUG option deactivates this behavior.
Verify that all SAS procedures and DATA step code that read DB2 data share connections where possible. You can do this by using one libref to reference all of the SAS applications that read DB2 data and by accepting the default value of SHAREDREAD for the CONNECTION= option.
If your DB2 subsystem supports parallel processing, you can assign a value to the CURRENT DEGREE special register. Setting this register may enable your SQL query to use parallel operations. You can set the special register by using the LIBNAME options DBCONINIT= or DBLIBINIT= with the SET statement as shown in the following example:
```
libname mydb2 db2 dbconinit="SET CURRENT DEGREE='ANY'";
```
Use the view descriptor WHERE clause or the DBCONDITION= option to pass WHERE clauses to DB2. You can also use these methods to pass sort operations to DB2 with the ORDER BY clause instead of performing a sort within SAS.
If you are using a SAS application or an SCL application that reads the DB2 data twice, let the DB2 engine spool the DB2 data. This happens by default because the default value for the SPOOL= option is YES.
The spool file is read both when the application rereads the DB2 data and when the application scrolls forward or backward through the data. If you do not use spooling, and you need to scroll backward through the DB2 table, the DB2 engine must start reading from the beginning of the data and read down to the row that you want to scroll back to.
Use the SQL procedure to pass joins to DB2 instead of using the MATCH MERGE capability (that is, merging with a BY statement) of the DATA step.
Use the DBKEY= option when you are doing SAS processing that involves the KEY= option. When you use the DBKEY= option, the DB2 engine generates a WHERE clause that uses parameter markers. During the execution of the application, the values for the key are substituted into the parameter markers in the WHERE clause.
If you don't use the DBKEY= option, a new WHERE clause is generated for every key value. The SQL query with the new WHERE clause must be optimized in the DB2 PREPARE process every time the key value changes. The SQL query that uses the WHERE clause with parameter markers is optimized, or PREPAREd, only once.
Consider using stored procedures when they can improve performance in client/server applications by reducing network traffic. You can execute a stored procedure by using the DBCONINIT= or DBLIBINIT= LIBNAME options.

Making the Most of Your Connections

Beginning in Version 7, the DB2 engine supports more than one connection to DB2 per SAS session. This is an improvement over Version 6 in a number of ways, especially in a server environment. One advantage is being able to segregate tasks that fetch rows from a cursor from tasks that must issue commits. This eliminates having to resynchronize the cursor, prepare the statement, and fetch rows until you are positioned back on the row you were on. This separation also allows tasks that must issue commits to eliminate locking contention to do so sooner, since they are not delayed until after cursors are closed to prevent having to resynchronize. In general, tables opened for input fetch from cursors and do not issue commits, while update opens may, and output opens do, issue commits.

You can control how the DB2 engine uses connections by using the CONNECTION= option on the LIBNAME statement. At one extreme is CONNECTION=UNIQUE, which causes each table access, whether it is for input, update, or output, to create and use its own connection. Conversely, CONNECTION=SHARED means that only one connection is made, and that input, update, and output accesses all share that connection.

The default value for the CONNECTION= option is CONNECTION=SHAREDREAD, which means that tables opened for input share one connection, while update and output opens get their own connections. CONNECTION=SHAREDREAD allows the best separation between tasks that fetch from cursors and tasks that must issue commits, eliminating the resynchronizing of cursors.

The values GLOBAL and GLOBALREAD perform similarly to SHARED and SHAREDREAD. The difference is that you can share the given connection across any of the librefs that you specify as GLOBAL or GLOBALREAD.

Although the default value of CONNECTION= SHAREDREAD is optimal, there are times when another value might be better. If you must use multiple librefs, you might want to set them each as GLOBALREAD. This way, you will have one connection for all of your input opens, regardless of which libref you use, as opposed to one connection per libref for input opens. In a single-user environment (as opposed to a server session), you might know that you will not have multiple opens occurring at the same time. In this case, you might want to use SHARED (or GLOBAL for multiple librefs). This eliminates the overhead of creating separate connections for input, update, and output transactions, while having only one open at a time eliminates the problem of having to resynchronize input cursors if a commit occurs.

Another reason for using SHARED or GLOBAL is the case of opening a table for output while opening another table within the same database for input. This can result in a -911 deadlock situation unless both opens occur in the same connection.

As explained in Information for the Database Administrator, the first connection to DB2 is made from the main SAS task. Subsequent connections are made from corresponding subtasks, which the DB2 engine attaches; DB2 allows only one connection per task. Due to the system overhead of intertask communication, the connection established from the main SAS task is a faster connection in terms of CPU time. Since this is true, if you are reading or writing large numbers of rows, you will have better performance (less CPU time) if you use the first connection for these operations. If you are only reading rows, SHAREDREAD or GLOBALREAD can share the first connection. However, if you are both reading and writing rows (input and output opens), you can use CONNECTION=UNIQUE to make each open use the first connection. UNIQUE causes each open to have its own connection. If you only have one open at a time, and some are input while others are output (for large amounts of data), the performance benefit of using the main SAS task connection far outweighs the overhead of establishing a new connection for each open.

One other type of connection that the DB2 engine uses, and which is not user controllable, is the utility connection. This connection is used to access the system catalog, issues commits to release locks, and is a separate connection. Utility procedures such as DATASETS and CONTENTS can cause this connection to be created, although other actions necessitate it as well. There is one connection of this type per libref, but it is not created until it is needed. If you have critical steps which must use the main SAS task connection for performance reasons, refrain from using the DEFER=YES option on the LIBNAME statement. It is possible that the utility connection can be established from that task, causing the connection you use for your opens to be from a slower subtask.

In summary, there is not one value for the CONNECTION= option which works best in all possible situations. You might need to try different values and arrange your SAS programs in different ways to obtain the best performance possible.

For additional information about

DB2-specific LIBNAME options, refer to SAS/ACCESS LIBNAME Options.
SAS system options, refer to SAS System Options and Settings for DB2
SAS/ACCESS LIBNAME options, refer to SAS/ACCESS LIBNAME Statement
SAS/ACCESS data set options, refer to SAS/ACCESS Data Set Options

Chapter Contents
Previous
Next
Top of Page