Connectivity and administration routines
This chapters describes enhancements in the connectivity and administration routines of DB2 12:
8.1 Maintaining session data on the target server
Maintaining session data on the target DB2 12 for z/OS server is also called session token support. When this feature is enabled, a single small session token (a few bytes) generated by DB2 represents a client session. A transport is then associated with the session by flowing the appropriate session token on it.
|
Tip: You need to configure clientApplcompat at the Connection/DataSource level with a value 'V12R1' to enable session token support to control behavior of applications migrating to DB2 12 for z/OS. Also, a new client driver level is required for your CLI and Java driver.
|
If this feature is not enabled, as in DB2 11 for z/OS, the client drivers using sysplex workload balancing (WLB) must remember and track session information (such as global variables, client information, and special registers) to support transaction pooling or connection reroute as shown in Figure 8-1.
Figure 8-1 Remembering and tracking session information
Thus the WLB or client reroute initiated transport switch requires the client drivers to replay the session data on the new transport. This results in growing size of replay data, security exposure, and performance degradation.
In DB2 12 for z/OS, with the session token support, the session data will not be returned to client from DB2 for z/OS server. Here the client using sysplex WLB will pass a session token to DB2 server and DB2 will maintain session data for the data sharing group. DB2 will use the following catalog tables to manage session data:
•SYSIBM.SYSSESSION
•SYSIBM.SYSSESSION_EX
•SYSIBM.SYSSESSION_STATUS
In the sample scenario illustrated in Figure 8-2, if client connection is rerouted to DB2B, the client will pass a session token on establishing transport (global variables, special registers will be kept intact).
Figure 8-2 Sample scenario
Thus, with the session token support, the performance is better because you are now dealing with much smaller replay data (only the session token), far fewer session commands, and also it provides increased security for sensitive information in variables and registers.
When the application closes a logical connection, the driver flows a terminate connection to DB2, resulting in the following sequence of events:
1. The distributed thread (DBAT) is pooled.
2. Connection is made inactive.
3. The session information is released.
|
Tip: Applications should explicitly close result sets, statements, and connections thereby releasing database resources in a timely manner.
|
You may use the MODIFY DDF command to set the session data timeout value. The session data timeout value determines how long the session data will stay active before it can be removed from the SYSIBM.SYSSESSION table. For example, -MODIFY DDF SESSIDLE(100) sets the SESSIDLE value to 100 minutes.
|
Note: If session data was purged, you receive a DRDA reply with the error code -30062 and the invalid session token message. The application that receives -30062 should explicitly close the logical connection.
|
8.2 Preserving prepared dynamic statements after a ROLLBACK
Up to DB2 11, when the application package is bound with KEEPDYNAMIC(YES), this option is applicable to the COMMIT request only. Normally statements are cleaned up on COMMIT. With the KEEPDYNAMIC(YES) option, DB2 keeps the runtime structures of cached dynamic statements on COMMIT. Example 8-1 shows COMMIT and dynamic SQL in DB2 11, assuming the package is bound with the KEEPDYNAMIC(NO) option.
Example 8-1 COMMIT and dynamic SQL with KEEPDYNAMIC(NO)
PREPARE STMTID FOR INSERT INTO T1 SELECT * FROM T2 WHERE T2.C1 = ?
EXECUTE STMTID USING :H1
EXECUTE STMTID USING :H2
COMMIT the dynamic INSERT is unprepared
EXECUTE STMTID USING :H3 fails because statement is not prepared
Example 8-2 Package bound with KEEPDYNAMIC(YES)
PREPARE STMTID FOR INSERT INTO T1 SELECT * FROM T2 WHERE T2.C1 = ?
EXECUTE STMTID USING :H1
EXECUTE STMTID USING :H2
COMMIT the dynamic INSERT is not unprepared
EXECUTE STMTID USING :H3 successful because statement is still prepared
The KEEPDYNAMIC(YES) functionality is to avoid a trip from the application to re-prepare dynamic SQL statements after COMMIT, which improves performance. However, this functionality does not apply to ROLLBACK on DB2 11. Example 8-3 shows ROLLBACK and dynamic SQL in DB2 11, assuming the package is bound with the KEEDYNAMIC(YES) option.
Example 8-3 ROLLBACK and dynamic SQL
PREPARE STMTID FOR INSERT INTO T1 SELECT * FROM T2 WHERE T2.C1 = ?
EXECUTE STMTID USING :H1
EXECUTE STMTID USING :H2
ROLLBACK the dynamic INSERT is unprepared
EXECUTE STMTID USING :H3 fails because statement is not prepared
DB2 12 extends the KEEPDYNAMIC(YES) functionality to ROLLBACK also, where prepared dynamic statements are retained at then end of a unit of work. With DB2 12, and the package bound with APPLCOMPAT(V12R1M500) and KEEPDYNAMIC(YES); Example 8-4 shows the result.
Example 8-4 Package bound with APPLCOMPAT(V12R1M500) and KEEPDYNAMIC(YES)
PREPARE STMTID FOR INSERT INTO T1 SELECT * FROM T2 WHERE T2.C1 = ?
EXECUTE STMTID USING :H1
EXECUTE STMTID USING :H2
ROLLBACK the dynamic INSERT is not unprepared
EXECUTE STMTID USING :H3 successful because statement is still prepared
The DBAT is also kept active so the next re-execution of the statement can resume immediately without the need for re-prepare.
|
Note: This function is available for local application as well.
|
8.3 DRDA fast load
DB2 supplies the stored procedure DSNUTILU, which can be invoked by a DB2 application program to run DB2 online utilities. The LOAD online utility is one such utility that DSNUTILU supports. As such, DSNUTILU can be used to load data from distributed clients to DB2 for z/OS servers by invoking the LOAD online utility.
Starting in DB2 12, the client drivers are enhanced to support remote loads to DB2 for z/OS servers. The DB2 Call Level Interface (CLI) APIs and the Command Line Processor (CLP) are modified to stream data to the load process in continuous blocks. By using the enhanced client drivers, the loading of data from distributed clients can significantly reduce elapsed time because the task that extracts data blocks and passes them to the LOAD utility is 100% zIIP offloadable using the goal of the distributed address space. The function is available before activating new function. For information about new function activation, see Chapter 2, “Continuous delivery” on page 7.
8.4 Profile monitoring for remote threads and connections
DB2 provides monitoring capabilities by the use of profiles tables. Profile tables enable you to monitor the use of system resources by remote applications, including remote threads and connections, and to control performance-related subsystem parameters in particular contexts on your DB2 subsystem. Each monitored situation is defined by a set of criteria called a profile.
A profile is a set of criteria that identifies a particular situation on a DB2 subsystem. A profile is defined by a record in the SYSIBM.DSN_PROFILE_TABLE table. The profile tables and related indexes are created by the DSNTIJSG job during DB2 installation or migration. After profiling is correctly defined, use the START PROFILE command to start profile monitoring. Issue a STOP PROFILE command to disable all profile functions.
For further information about profiles, see the topic about using profiles to monitor and optimize performance in DB2 12 for z/OS Managing Performance, SC27-8857.
DB2 12 offers several enhancements to system profiles:
8.4.1 Automatic start of profiles during subsystem start
A new subsystem parameter, PROFILE_AUTOSTART, is introduced to allow the START PROFILE command processing to be automatically initiated as part of DB2 startup processing. If you set this subsystem parameter to YES, DB2 will automatically start the START PROFILE command processing.
Consider the following values:
•If you specify YES and your DB2 system was started with the ACCESS(MAINT) option or LIGHT(YES) option, DB2 will ignore the setting of the PROFILE_AUTOSTART subsystem parameter and not initiate START PROFILE.
•A value of NO, which is the default value, tells DB2 to not initiate START PROFILE during startup processing.
8.4.2 Support for global variables
You can create profiles to define a set of criteria to monitor processes within DB2. For remote applications, client information like application name, user ID name, and workstation name are particularly helpful.
Consider these two tables:
•SYSIBM.DSN_PROFILE_TABLE. Contains a row for each profile.
•SYSIBM.DSN_PROFILE_ATTRIBUTES. Allows you to specify actions for DB2 to take when a process, such as a SQL statement, thread, or connection meets the criteria of the profile. The KEYWORDS column in the SYSIBM.DSN_PROFILE_ATTRIBUTES table indicates which action DB2 should perform. Each action can have up to three attributes that control how the specified action is applied by DB2. The attributes are defined by the ATTRIBUTE1, ATTRIBUTE2, and ATTRIBUTE3 columns.
Starting in DB2 12, you can specify GLOBAL_VARIABLE as a KEYWORDS column value in the SYSIBM.DSN_PROFILE_ATTRIBUTES table. The following built-in global variables are supported:
•GET_ARCHIVE
•MOVE_TO_ARCHIVE
•TEMPORAL_LOGICAL_TRANSACTION_TIME
•TEMPORAL_LOGICAL_TRANSACTIONS
The ATTRIBUTE1 column must specify a valid SET assignment-statement statement for the global variable. For example, if you want DB2 to allow temporal logical transactions to occur, set the ATTRIBUTE1 column value as follows:
SET SYSIBM.TEMPORAL_LOGICAL_TRANSACTIONS = 1
If a profile filter matches a connection, DB2 will automatically apply the built-in global variable value to the DB2 process of that connection when the connection is initially established, and when a connection is reused.
Setting a built-in global variable in the profile table applies only to remote applications. This function is available after new function is activated. For detailed information about new function activation, see Chapter 2, “Continuous delivery” on page 7. For additional information about temporal logical transactions, see Chapter 7, “Application enablement ” on page 99.
8.4.3 Support for wildcarding
As indicated previously, SYSIBM.DSN_PROFILE_TABLE contains a row for each profile. Each column in the table tells DB2 which connection to monitor. Prior to DB2 12, to handle various connections, multiple rows had to be defined in the table. Starting in DB2 12, you may choose to have a single row that represents more than one connection.
The following columns are enhanced in DB2 12 for remote connections:
•AUTHID
•LOCATION
•PRDID
The support described next is available before new function activation. For information about new function activation, see Chapter 2, “Continuous delivery” on page 7.
AUTHID column enhancement
The AUTHID column contains the authorization ID of a monitored user. The AUTHID column can contain a VARCHAR(128) value. Starting in DB2 12, you can monitor all authorization IDs that start with the same prefix by specifying an asterisk (*) at the end of the prefix. For example, if you want to set your profile criteria to all authorization IDs that start with 'USER', specify 'USER*' for the AUTHID column.
LOCATION column enhancement
The LOCATION column contains the IP address of a monitored connection. The LOCATION column can contain a VARCHAR(254) value. Starting in DB2 12, you can specify a LOCATION with the following values:
•IPv4 subnet address subnet prefix in the form of IPv4address/mm where mm is 8, 16, or 24 and represents the number of initial bits of an IPv4 address that belongs in the subnet.
For example, if you specify a value of 9.30.222.0/24, any IP address in the range 9.30.222.1 to 9.30.222.254 will be monitored.
•IPv6 subnet address subnet prefix in the form of IPv6address/mmm where mmm is 16, 32, 48, 64, 80, 96, or 112 and represents the number of initial bits of an IPv6 address that belongs in the subnet.
•The form of either 0.0.0.0 or ::0, which represents any IP address.
PRDID column enhancement
The PRDID column contains the product-specific identifier of a monitored remote requester. The PRDID column is a CHAR(8) value. Similar to the AUTHID column enhancement, starting in DB2 12, you can monitor all product-specific identifiers that start with the same prefix by specifying an asterisk (*) at the end of the prefix. For example, if you want to monitor all versions of DB2 for z/OS, use 'DSN*' for the PRDID column.
8.4.4 Idle thread enhancement
As discussed previously, the SYSIBM.DSN_PROFILE_TABLE contains a row for each profile, the SYSIBM.DSN_PROFILE_ATTRIBUTES table allows you to specify actions for DB2 to take when a process meets the criteria of the profile. The MONITOR IDLE THREADS column in the SYSIBM.DSN_PROFILE_ATTRIBUTES table tells DB2 to monitor, for an approximate amount of time, an active server thread’s idle time. The ATTRIBUTE1 column is used to specify the type of messages and level of detail of messages issued for monitored threads. The ATTRIBUTE2 column specifies the threshold the thread is allowed to be idle.
The ATTRIBUTE1 column is enhanced to allow the following values:
•EXCEPTION_ROLLBACK
•EXCEPTION_ROLLBACK_DIAGLEVEL1
•EXCEPTION_ROLLBACK_DIAGLEVEL2
If the threshold is exceeded according to the ATTRIBUTE2 value, DB2 issues the appropriate type and level of messages, and if database changes occurred, but were not committed, the following actions are performed:
•The thread is aborted.
•The database access thread (DBAT) is pooled.
•The database changes are rolled back.
•The connection becomes inactive as it is placed in a must-abort states.
|
Note: DB2 hides the EXCEPTION_ROLLBACK event from the remote application environment in either of the following scenarios:
•The aborted thread performed only read-only operations.
•The thread committed or aborted but the associated DBAT remained active before it became idle.
It might appear to the remote application that database resources were lost, such as held cursors, held LOB locators, declared global temporary tables, and KEEPDYNAMIC sections.
|
This enhancement to idle threads for EXCEPTION_ROLLBACK became available in DB2 11 for z/OS after DB2 11 was made generally available. On a DB2 11 system, the functionality is available after migration to DB2 11 new function mode. On a DB2 12 system, the function becomes available after new function activation. For information about new function activation, see Chapter 2, “Continuous delivery” on page 7.
8.5 Stored procedures supplied by DB2
The following administration routines are changed in DB2 12:
•ADMIN_COMMAND_DB2
This routine supports the DISPLAY GROUP command changes for continuous delivery. For more information, see Chapter 2, “Continuous delivery” on page 7.
•ADMIN_COMMAND_DSN
This routine supports the FREE STABILITY DYNAMIC QUERY subcommand. For more information, see Chapter 9, “Administrator function” on page 131.
•ADMIN_EXPLAIN_MAINT
This routine upgrades the EXPLAIN table definitions for the new release.
The suggested approach is to invoke this routine in the migration process with the STANDARDIZE_AND_CREATE action to alter the existing EXPLAIN tables to conform to DB2 12 format.
The suggested approach is to invoke this routine in the migration process with the STANDARDIZE_AND_CREATE action to alter the existing EXPLAIN tables to conform to DB2 12 format.
You can set the stored procedure input parameters to upgrade each existing EXPLAIN table to the current format for DB2 12 and create any new tables needed for EXPLAIN:
– MODE to 'RUN'
– ACTION to 'STANDARDIZE_AND_CREATE'
– SCHEMA-NAME to the creator identified by the query
|
Note: You can set MODE to 'PREVIEW' to obtain a report of any changes without processing them.
|
•ADMIN_INFO_SQL
This routine provides a serviceability mechanism to collect information needed for service issues. This stored procedure is enhanced in DB2 12 as follows:
– Collect the EXPLAIN table, DSN_STATEMENT_TABLE, in the EXPL file. This is needed for service cases where the function level information is useful.
– Issue the ALTER BUFFERPOOL commands earlier in the process.
•GET_CONFIG
This routine supports the DISPLAY GROUP command changes for continuous delivery. For more information, see Chapter 2, “Continuous delivery” on page 7.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.