Since SAS 7, the DB2 engine supports more than one connection
to DB2 per SAS session. This is an improvement over SAS 6 in a number
of ways, especially in a server environment. One advantage is being
able to separate tasks that fetch rows from a cursor from tasks that
must issue commits. This separation eliminates having to resynchronize
the cursor, prepare the statement, and fetch rows until you are positioned
back on the row that you were on. It also enables tasks that must
issue commits to eliminate locking contention to do this sooner because
they are not delayed until after cursors are closed to prevent having
to resynchronize. In general, tables that are opened for input fetch
from cursors do not issue commits, although update openings might,
and output openings do, issue commits.
You can control how
the DB2 engine uses connections by using the
CONNECTION= option in 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. Update and output openings
obtain their own connections. CONNECTION=SHAREDREAD allows for 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 usually optimal, at times another
value might be better. If you must use multiple librefs, you might
want to set them each as GLOBALREAD. In this case, you have one connection
for all of your input openings, regardless of which libref you use,
as opposed to one connection per libref for input openings. In a single-user
environment (as opposed to a server session), you might know that
you do not have multiple openings occurring at the same time. In this
case, you might want to use SHARED—or GLOBAL for multiple librefs.
By using such a setting, you eliminate the overhead of creating separate
connections for input, update, and output transactions. If you have
only one opening at a time, you eliminate the problem of resynchronizing
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
DB2 under z/OS 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. Because this is true, you can expect better performance
(less CPU time) if you use the first connection for these operations
when you read or write large numbers of rows. If you read-only 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 opening use the first connection.
UNIQUE causes each opening to have its own connection. If you have
only one opening 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 opening.
The utility connection
is another type of connection that the DB2 engine uses, which the
use does not control. This connection is a separate connection that
can access the system catalog and issue commits to release locks.
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 that must use the main SAS
task connection for performance reasons, refrain from using the DEFER=YES
option in the LIBNAME statement. It is possible that the utility connection
can be established from that task, causing the connection that you
use for your opening to be from a slower subtask.
In summary, no one value
works best for the CONNECTION= option 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.