This section discusses the following topics:
The database manager can be operated in either multiple user mode or single user mode.
In multiple user mode, more than one user or application can concurrently access the same database. The database manager runs in one VSE system partition while applications run in other partitions. The initialization parameter SYSMODE=M defines this mode.
In single user mode, only one user or application can be run at one time. Both the database manager and the application program run in the same VSE system partition. The initialization parameter SYSMODE=S defines this mode.
Many of the database manager facilities, including support for ISQL, CICS transactions, and VSE/ICCF, are available only in multiple user mode. Support for the DBS utility, preprocessors, and batch applications is available in both multiple and single user modes. Support for special facilities (such as ADD DBEXTENT) is available only in single user mode.
Figure 14 identifies the initialization parameters that apply when the database manager is operating in multiple user mode, and lists their defaults. A discussion of the appropriate settings for these parameters follows.
Figure 14. Multiple User Mode Initialization Parameters
This parameter specifies the application server to be started. Different application servers can be started in different partitions (up to a maximum of 36). With multiple server support, different procedures can be written to link the appropriate disks for each database. When starting the application server, correlate the procedure name with this parameter. If not specified, the application server is started with the default specified in the DBNAME directory. For more details, see Choosing an Application Server Name.
The following examples show the DBNAME parameter specified correctly with two procedures to start two different application servers.
Figure 15. Examples of Job Control to Start a Database in Multiple User Mode
// JOB xxxxx // EXEC PROC=ARIS71SL // EXEC PROC=SQLDB1 // EXEC PGM=ARISQLDS,SIZE=AUTO,PARM='STARTUP=W,DBNAME=SQLDB1_NEWYORK_INV' /* /& // JOB yyyyy // EXEC PROC=ARIS71SL // EXEC PROC=SQLDB36 // EXEC PGM=ARISQLDS,SIZE=AUTO,PARM='STARTUP=W,DBNAME=SQLDB36_TORONTO_INV' /* /& |
This parameter can be specified only if the DRDA code can be installed.
The value specified for RMTUSERS is the maximum number of remote users that can access the application server, and can be set to any number from 1 to 65535 (inclusive). The default is 0. The appropriate value for RMTUSERS depends on the availability of virtual storage.
If RMTUSERS is not specified or the value is 0, remote users will not be able to access the application server.
This parameter is used to specify either single(S) or multiple(M) user mode. Set it to M to initialize the database manager for multiple user mode operation. This is the default mode.
This parameter specifies how the database will be started:
For more information, see Restoring the Database.
This parameter can be used to specify an A-type source member containing the values for the other initialization parameters. Application program parameters (user parameters) cannot be included. Figure 16 shows an example of startup of the application server that uses the PARMID initialization parameter.
When you define the data sets for your database, you can define a password to protect them from unintentional or malicious access. All the data sets for a database must have the same VSAM password, which is defined with the VSE/VSAM DEFINE CLUSTER command. Then, when starting the application server, the operator must specify this password with the DBPSWD parameter, as shown in Figure 16. If the specified password does not match the one defined for the data sets, the operator is prompted to supply the correct one.
Figure 16. Job Control to Start in Multiple User Mode with Password-Protected Data Sets
// JOB MULTI // EXEC PROC=ARIS71SL // EXEC PROC=ARIS71DB // EXEC PGM=ARISQLDS,SIZE=AUTO,PARM='PARMID=WARM1,DBPSWD=password' /* /& |
This section discusses the following:
Use the CHARNAME parameter to specify the CCSIDs to be used as the application server defaults. The default CCSIDs determine the character sets and code pages to be used to interpret statements and return results.
The valid CHARNAME values you can specify are ENGLISH (CCSID=37), INTERNATIONAL (CCSID=500), and all the values that are in the CHARNAME column of the SYSTEM.SYSCCSIDS catalog table.
The database manager obtains the CCSIDs associated with the CHARNAME by looking up the row of the SYSTEM.SYSCCSIDS catalog table where the CHARNAME column matches the CHARNAME parameter. It also obtains the classification and translation tables associated with the CHARNAME by looking up the row of the SYSTEM.SYSCHARSETS catalog table where the NAME column matches the CHARNAME parameter. The classification table is used to identify valid characters in identifiers. The translation table is used to indicate how to fold ordinary lowercase identifiers to uppercase.
For CHARNAMEs ENGLISH and INTERNATIONAL, their CCSID values, the classification table and the translation table are stored internally. The rows in SYSTEM.SYSCCSIDS and SYSTEM.SYSCHARSETS for these CHARNAMEs are for reference purposes only and are not used by the database manager.
During startup, if you do not specify the CHARNAME parameter, the application server uses the same CHARNAME that was used the last time it was started. The values stored in the rows where SQLOPTION equals CHARNAME, CCSIDSBCS, CCSIDMIXED, and CCSIDGRAPHIC are for reference purposes only. They reflect the current values associated with the system. The only way to change the default values is by starting the application server with a different CHARNAME parameter. Any updates to the values in the SYSTEM.SYSOPTIONS table are ignored during startup.
| Note: | The database manager determines the current default CHARNAME from the CCSID attribute of the CNAME character column in the SYSTEM.SYSCOLUMNS catalog table. If this value is null, then 37 is used (a CCSID of 37 corresponds to a CHARNAME of ENGLISH). The database manager uses the CCSID value to locate the corresponding row in the SYSTEM.SYSCCSIDS catalog table to obtain the associated CHARNAME. The value in the CHARNAME column of this row is the current application server default CHARNAME. |
When you specify a value for the CHARNAME parameter that is different from the current application server default CHARNAME, you are prompted to choose whether or not you want to change the application server default CHARNAME. If you specify YES and have supplied a valid CHARNAME value, the database manager updates the application server default values for CHARNAME, CCSIDSBCS, CCSIDMIXED, and CCSIDGRAPHIC.
It also modifies the CCSID attribute of all character columns that are part of the catalog tables to the application server default CCSID. The CCSID attribute of character columns that are not part of the catalog tables are not modified. If the value for CCSIDMIXED is not zero, this value is used as the application server default CCSID. If the value for CCSIDMIXED is zero, then the application server default CCSID is the value of CCSIDSBCS.
Note that the tables which have their CCSID modified when the CHARNAME is changed include:
When a CHARNAME is changed, the following should be considered:
The following query lists all such view packages:
SELECT CREATOR, TNAME, PLABEL
FROM SYSTEM.SYSACCESS
WHERE TABTYPE = 'V'
AND VALID = 'N'
This query is useful in that owners of affected views can be notified to drop and recreate their view before they try and use the view and get an error (SQLCODE=-835, SQLSTATE=56049, with SQLERRD1 set to -833).
The following query lists all such packages:
SELECT CREATOR, TNAME, PLABEL
FROM SYSTEM.SYSACCESS
WHERE TABTYPE = 'X'
AND VALID = 'N'
This query is useful in that owners of affected packages can be notified to rebind the packages instead of having them dynamically repreprocessed at run time. The DBS utility REBIND PACKAGE command can be used to rebind the packages listed.
To check if all the above activities have been done, run the following query:
SELECT CREATOR, TNAME, PLABEL
FROM SYSTEM.SYSACCESS
WHERE VALID = 'N'
If there are no rows found, all packages have been either recreated, reloaded, rebound or dynamically repreprocessed and the VALID column value for the package in SYSTEM.SYSACCESS has been changed to "Y".
Note that CCSID conversion of the data in catalog tables does not occur: only the CCSID attribute of the columns is modified. If you change the application server default CHARNAME, system objects of the character data type (for example, table names and column names) stored in the catalog may be displayed differently. The reason for this is that a code point may represent different characters in different code pages.
If you want to change the application server default CHARNAME, the default will not be changed if:
When the application server is started, it records the application server default values for CHARNAME, CCSIDSBCS, CCSIDMIXED, and CCSIDGRAPHIC in the SYSTEM.SYSOPTIONS catalog table. To obtain these values, you can query the table. For example, to determine the name of the character set that is currently in use, issue:
SELECT VALUE
FROM SYSTEM.SYSOPTIONS
WHERE SQLOPTION = 'CHARNAME'
For more information about character sets, see Chapter 12, Choosing a National Language and Defining Character Sets.
You can use the SET LANGUAGE command from the operator console to choose a national language so that DB2 Server for VSE messages can be received in the selected language. (You cannot choose a double-byte character set (DBCS) language, as the VSE operator console does not support DBCS.) For more information see National Language Support for Messages and HELP Text.
This parameter enables the accounting facility. If ACCOUNT=T or ACCOUNT=D or ACCOUNT=E is specified, accounting records will be generated and directed to either a tape file, a SAM DASD file or a VSAM ESDS file, respectively. (The tape or DASD file must be identified in your job control for starting the application server.) If the default value of ACCOUNT=N is specified, accounting information is not generated.
For a complete description of the accounting facility, see Chapter 10, Using the Accounting Facility.
This parameter specifies whether or not a sync point manager (SPM) will be used to coordinate DRDA2 DUOW two-phase commit and resynchronization activity. It is only meaningful when the RMTUSERS parameter is greater than zero.
If Y is specified, the server will use a sync point manager, if possible, to coordinate two-phase commits and resynchronization activity. If N is specified, the server will not use an SPM to perform two-phase commits. If N is specified, the database manager is limited to multi-read, single-write distributed units of work and it can be the single write site. If Y is specified, but the database manager finds that a sync point manager is not available, then the server will operate as if N was specified.
The default is SYNCPNT=Y, if RMTUSERS is greater than zero.
This two digit parameter specifies what information is displayed and what level of detail is displayed. If 0 is specified, nothing is displayed. If 1 is specified, the minimum information is displayed. If 2 is specified, more detail is displayed. The positional digits correspond to the following informational displays: the first is checkpoint performance information and the second is counter information to be displayed at system shutdown.
If the first option is 1, then format 1 of message ARI2052I is displayed every time a checkpoint occurs. This is useful in determining how often checkpoints occur. If the first option is 2, then format 2 of message ARI2052I is displayed every time a checkpoint occurs. This is useful in determining if checkpoint processing is causing a performance problem.
If the second option is 1, then the "COUNTER *" operator command is issued just before the application server is shutdown. This is useful for performance tuning.
The SET command changes the value of this parameter without having to stop and restart the application server. For more information on the SET operator command, see the DB2 Server for VSE & VM Operation manual.
This parameter determines if the application server will accept users that have already been verified by another system. If SECALVER=Y, verified users will be accepted. The requester only needs to send a user ID to be validated. If SECALVER=N, verified users will not be accepted. The requester must send a user ID and password to be verified.
| Note: | This parameter is only used when validating users are connecting via TCP/IP or when users send the ACCSEC and SECCHK DRDA datastreams in their connect request. |
This parameter determines if the application server will validate a user ID and password for connect authority using an external security manager or by checking the DB2 SYSUSERAUTH catalog table. If SECTYPE=ESM an external security manager will be used to validate the user ID and password. The external security manager must support the RACROUTE application programming interface. If SECTYPE=DB2, the user ID and password are validated by checking the SYSUSERAUTH catalog table.
| Note: | This parameter is only used when validating users are connecting via TCP/IP or when users send the ACCSEC and SECCHK DRDA datastreams in their connect request. |
This parameter specifies the TCP/IP port number that the application server will use to listen for incoming TCP/IP connect requests.
If this parameter is not specified, TCP/IP support will be initialized and the DBNAME Directory on the TCP/IP client disk will be searched to determine the port number that the application server will use.
If this parameter is specified with a non-zero value, TCP/IP support will be initialized and the value specified will be used as the port number that the application server will use.
If this parameter is specified with a value of 0, TCP/IP support will not be initialized.
This parameter defines the maximum number of real agents that the database manager can actively handle at any one time, limiting the number of users that can be supported by the database manager. The value of NCUSERS is usually less than the number of connected users anticipated, because not all users will be accessing data at the same time. This value directly affects the size of the database partition required.
The number of NCUSERS is limited because some static agent storage for each real agent is obtained below 16 megabytes.
Figure 17 provides guidelines for setting the NCUSERS parameter. Because these are only guidelines, you should modify them to concur with the activity on your system. For additional information, see the DB2 Server for VSE & VM Performance Tuning Handbook.
Figure 17. Guidelines for the NCUSERS Parameter
Each ISQL user can generate a high level of system activity. If you set NCUSERS so that all ISQL users can be active at the same time (NCUSERS=number of ISQL users), you minimize the time that any one user must wait for services. However, if this number is large, it may cause the database manager to be overloaded. To prevent this, you should also use the CICS DFHSIT parameter CMXT, which limits the number of users who can be logged on to ISQL. For information on this parameter, see Controlling Access by ISQL Users.
Application developers (VSE/ICCF users) typically do a considerable amount of other activity (such as VSE/ICCF file editing or output scanning). These users require less service from the database manager, so NCUSERS can be lowered accordingly.
If you are using VSE guest sharing, the NCUSERS of the VM database machine should be increased by the number required for the VSE guest. The demand for services from CICS transaction processing can vary widely, depending on the nature of the transactions.
The demand for services from batch application programs can also vary considerably. If you have online or interactive activity on the database manager, consider limiting the amount of concurrent SQL batch processing.
| Note: | When the application server is started, there may be one or more in-doubt logical units of work (LUWs). The value of NCUSERS must be large enough to handle these. When they have been resolved, the DB2 Server for VSE agent structures are used to handle new users. The creation and use of agent structures for resolving in-doubt LUWs takes precedence over all new user logical units of work. For more information about in-doubt LUWs, see Resolving In-Doubt Transactions. |
This parameter defines the maximum number of packages in an LUW, and together with the value specified for NCUSERS, determines the size of the package cache. The size of the package cache limits the number of packages that can be present in storage simultaneously. (Package cache size = NPACKAGE x NCUSERS.) The default value of NPACKAGE is 10, and that for NCUSERS is 5, giving a default package cache of 50, allowing 50 packages to be present in storage simultaneously.
In general, increasing the size of the package cache improves performance of the database manager. However, do not increase it to the point where system paging becomes too great. For more information, see the DB2 Server for VSE & VM Performance Tuning Handbook.
This parameter defines the percentage of the package cache that is used in the calculation of the package cache threshold. The size of the threshold determines the number of loaded packages that are kept in storage at the end of an LUW. (Threshold = NPACKPCT percent of package cache.) If the threshold is exceeded, the loaded packages are freed and returned to the package cache.
The default values for NPACKPCT and the package cache are 30 and 50 respectively, giving a threshold of 15. In general, increasing the size of the threshold improves performance. For more information, see the DB2 Server for VSE & VM Performance Tuning Handbook.
This parameter specifies the number of 4096-byte data pages kept in storage buffers at one time. The number of data buffers you want depends on the number of active users and the nature of their request. The default for NPAGBUF assumes an average of four buffer pages for each potentially active user (NCUSERS x 4), plus ten buffer pages for the buffering of catalog and log information.
In general, increasing NPAGBUF improves the performance of the database manager. However, increasing it also requires an increase in the size of the database partition. Also -- and more importantly -- it can cause an increase in the paging rate of the system. It is more efficient to let the database manager do more I/O operations than it is to let the system do more paging; database I/O operations are overlapped whereas system paging operations are not. Therefore do not increase NPAGBUF to the point where system paging becomes too great.
For more information about NPAGBUF, see the DB2 Server for VSE & VM Diagnosis Guide and Reference manual.
This parameter determines the number of 512-byte directory pages to be kept in storage. Increasing it reduces the number of I/O operations. Again, bigger is better, until you either run out of virtual storage or cause too much system paging. Each directory page addresses 128 data pages.
When you set NPAGBUF and NDIRBUF, you have to choose how to split buffer space between data pages and directory pages. At least initially, you should set them to the same value. Issue the COUNTER commands to see the actual I/O activity; then adjust NPAGBUF and NDIRBUF.
For more information about NDIRBUF, see the DB2 Server for VSE & VM Diagnosis Guide and Reference manual.
NLRBU specifies the maximum number of lock request blocks allowed for one active user, while NLRBS specifies the number allowed for all active users. (Usually, two lock request blocks are used for every lock that a user holds.)
The database manager can perform lock escalations, increasing the granularity of data being locked from either row or page level to dbspace level. In general, you only need to change the default values of NLRBU and NLRBS if contention problems occur. Increasing them reduces the number of lock escalations performed by the database manager.
When either the NLRBU limit for a user is reached or the NLRBS limit is approached, lock escalation occurs. This results in fewer locks being required, and lock request blocks being freed. This in turn reduces the opportunities to share data. For example, when locking is done at a row level, many users may be updating the same dbspace at the same time. When it is escalated to the dbspace level, only one user can update rows in that dbspace. Everyone else must wait until that person's update is committed or rolled back.
Escalation can also cause deadlocks. A deadlock occurs when two or more LUWs are in wait states and dependent on the completion of LUWs that are also in wait states. For example, suppose two users are updating tables in a dbspace. When the lock size is escalated to a dbspace level, both users can be locked out, with each waiting for the other to complete an LUW. The database manager resolves situations like these by rolling back the newest LUW. For more about locking, see the DB2 Server for VSE & VM Application Programming manual.
If the default values for NCUSERS (5) and NLRBU (1000) are used, the database manager defines 2520 lock request blocks, each of which requires 24 bytes; 60480 bytes of virtual storage are required for lock request blocks. With these defaults, one application could use 1000 lock request blocks and four other applications could simultaneously use an average of 370 lock request blocks each, before causing an escalation.
Even though two lock request blocks are needed for each lock, the default values allow a large number of locks for each application. With the defaults, one application could use 500 locks while four other applications use an average of 185 locks each.
You should use the NLRBU and NLRBS default values at first, and increase them if users either are experiencing delays when they access the database manager, or if they are receiving SQLCODEs of -911, -912, or -915 (rollbacks that occur because of deadlock, insufficient lock request blocks for the database manager, or insufficient lock request blocks for a user application, respectively).
| Note: | These SQLCODEs may also be received during preprocessing, as the locks are required then as well. |
To test the frequency of lock escalations and of deadlocks, use the COUNTER operator command. Specify both the ESCALATE and the LOCKLMT counters to get the number of successful escalations and the number of unsuccessful escalation attempts respectively. (An escalation can fail if the LUW that reached the lock limit is rolled back because of a deadlock, or if a sufficient number of lock request blocks cannot be freed.) For example, suppose the operator issues the command COUNTER ESCALATE LOCKLMT a few times a day and normally receives results in the range of 10 to 150 for ESCALATE, and 0 to 5 for LOCKLMT. If, one day, the results are 428 for ESCALATE and 23 for LOCKLMT, a locking problem would be indicated.
In addition, the SHOW LOCK MATRIX command can be used to display information about lock request block usage to determine whether unexpected delays are caused by locking; to monitor how the database manager is using lock request blocks; and to determine the lock request blocks required for a single application or for a run of a preprocessor.
One of the values displayed is called MAX USED BY LUW: the maximum number of lock request blocks used by any one application during an LUW. (When any LUW starts to exceed NLRBU and the escalation process occurs, MAX USED BY LUW is set to zero.) All this information can help you determine the required values for NLRBU and NLRBS.
To establish the lock request block requirements for running a preprocessor, or for an application that is causing contention problems:
If necessary, reset NLRBS. For example, suppose NLRBU is set to 1100, and two users will run their applications -- each requiring 1100 lock request blocks -- at the same time. Also assume that any other application requires about 500 lock request blocks. If NCUSERS is 5, then set NLRBS to at least 3700 (1100 for each of two applications and 500 for each of three additional applications).
If an application requires more lock request blocks than you have virtual storage for, you should consider the following alternatives:
For more information about locking problems and how to solve them, see the DB2 Server for VSE & VM Diagnosis Guide and Reference manual.
This parameter determines how the dispatcher selects the order in which agents get serviced by the database manager.
To set it, you need to understand how the dispatcher works. Only one agent at a time can be serviced; the other agents wait in a queue. Within this queue, agents are prioritized according to their estimated resource consumption: those estimated to consume the least are placed at the top, while those estimated to consume the most are placed at the bottom.
When the active agent returns to the dispatcher, the next agent at the top of the queue is dispatched. Every time an agent is dispatched, the database manager reevaluates the priority of the remaining agents, and requeues them according to their new priorities.
A pure priority dispatcher can present some problems, however. If many short-running LUWs are present, the longer-running ones may never get serviced: they are always at the bottom of the queue. To avoid this problem, fair-share auditing is used, whereby all the agents in the queue are checked periodically to see if they are receiving adequate service. When one is found that is not, its priority is changed and it is moved to the top of the queue.
If fair-share auditing is done frequently, the dispatcher tends to operate more like a round-robin dispatcher: agents get equal service because those at the bottom of the queue get bumped to the top more frequently. If it is done infrequently, the dispatcher tends to operate more like a priority dispatcher: agents get prioritized service because long-running agents are forced to wait at the bottom of the queue longer. (Eventually, fair-share auditing causes these agents to get service.)
The DISPBIAS parameter determines how often fair-share auditing is done. When it is set low (near 1), fair-share auditing is done frequently, and the dispatcher operates more in round-robin mode. When it is set high (near 10), fair-share auditing is done infrequently, and the dispatcher operates more in priority mode.
Initially, you should use the DISPBIAS default of 7. If your long-running LUWs are getting poor service, you may want to use a lower value; if your ISQL users are often waiting for long-running applications to complete, you may want to use a higher value. You can use the SET operator command to change the value of DISPBIAS without having to stop and restart the application server. See the DB2 Server for VSE & VM Operation manual for more information on the SET operator command.
| Note: | Any changes you make using the SET command are only in effect while the application server is running. If you stop and restart the application server, it will use the settings you specified in the startup procedure. |
You may be tempted to set DISPBIAS to 10 to get good response time for ISQL users. Keep in mind, however, that a long-running LUW can hold a large number of locks. If other users are waiting for those locks, they must wait until the application frees them. If the application is waiting at the bottom of the queue, everyone is waiting. In this situation, you would want to have fair-share auditing occur more frequently, so the long-running unit can free the resources it has locked. The default of seven represents a balance between the interests of long-running and short-running LUWs.
This parameter determines the number of internal control scan blocks kept for accessing tables and indexes. These blocks can vary in size and number depending on the type of query being performed. This discussion is concerned with long-running requests that might be queries or database change operations.
Scan control blocks contain positioning information related to a query. The positioning information can result from a user-defined cursor or by an internal cursor created by RDS. If an index is involved in the query, the size of the scan control block depends on the key size for that index. An average scan control block is assumed to be 50 bytes (32 bytes for control information, and an average key length of 18 bytes).
The maximum table size to hold the scan control block entries for each agent is 32 kilobytes (32768 bytes). This can contain 655 entries of 50-byte scan control blocks, which in general, is enough to support 255 user-declared cursors. If, however, the key lengths for indexes are long, the scan table supports fewer user cursors. For example, if the key length for a given index associated with a cursor is 255 bytes, an entry would require 287 (255 + 32) bytes, and the maximum number of cursors possible using that index would be 114 (32 kilobytes divided by 287). That number would be reduced if the DB2 Server for VSE requests caused internal cursors to be created. Internal cursors are always smaller than 50 bytes, and cannot use index keys.
If you have many complex requests, you may have to increase NCSCANS. If it is not set to a high enough value, users will get SQLCODE -522. For information on the virtual storage used by NCSCANS, see Virtual Storage Requirements of Components .
This parameter specifies a general lock wait timeout period for any SQL application, and especially as the way to avoid global deadlocks for DUOW applications.
The range of the LTIMEOUT value is 0 to 99999 seconds. The value of zero indicates that no lock timeout should be enforced for agents connected to this database. This is the default value for a database.
A nonzero lock timeout value will cause any agents waiting for a lock to have their current transaction rolled back when the lock timeout period has expired. The agent will notify the application that a lock timeout has occurred with SQLCODE -911 (SQLSTATE 40001). A reason code will be returned to indicate whether it is a deadlock or lock timeout situation (reason code 2 for a deadlock situation and reason code 68 for a lock timeout situation). The lock timeout period begins at the moment an agent requests a lock on any database resource. The full lock timeout period is allowed for each lock request.
The lock timeout control parameter should be adjusted in those environments where lock contention between applications has started to affect the desired performance and concurrency levels.
If a lock timeout is required for your environment, it is recommended that your starting value be equivalent to the maximum period of time that you want an application to wait for a lock.
| Note: | The LTIMEOUT parameter is changed through the SET operator command. The timeout value will affect any users currently in LOCK WAIT. If a user has been in a LOCK WAIT for 100 seconds and the value of LTIMEOUT is set to a value less than 100, that user will receive a timeout. For more information on the SET operator command, see the DB2 Server for VSE & VM Operation manual. |
If lock timeout control is activated, you should ensure that all applications recognize and can handle the -911 SQLCODE that may be received as the result of a lock timeout initiated rollback.
| Note: | New units of work that are waiting to begin because a log archive is running or is scheduled to run are in a lock wait. The SHOW LOCK WANTLOCK operator command shows these units of work waiting to acquire an IX lock on the database. Because log archives can potentially take a significant amount of time to complete, units of work in this particular type of lock wait are ignored by the lock timeout function. |
This parameter specifies the number of times a stored procedure is allowed to terminate abnormally, after which a STOP PROC ACTION REJECT is performed against the procedure and all subsequent SQL CALL statements for that procedure are rejected. Note that a timeout that occurs while waiting for a stored procedure server to be assigned for an SQL CALL statement is not included in this count.
PROCMXAB must be an integer between 0 and 255. The default, 0, means that the first abend of a stored procedure causes SQL CALLs to that procedure to be rejected. For production systems, you should accept the default.
This parameter specifies:
The default for PTIMEOUT is 180.
This parameter determines whether archives will be taken for the database and the log. Specify LOGMODE=A to maintain an archive of the database, LOGMODE=L to maintain an archive of the log, and LOGMODE=Y if you want logging but do not want the log archived.
LOGMODE=A allows you to restore the database and apply the current log. LOGMODE=L allows you to maintain a database archive as well as log archives. The database archive followed by the log archives are applied during restore, then the current log is applied.
Use LOGMODE=A or L if it is important to protect the database against media (DASD) failures; otherwise use LOGMODE=Y.
| Note: | Each sequence of log archives must be preceded by a database archive, so if you use LOGMODE=L, you must occasionally take a database archive. You do not need to switch to LOGMODE=A to do so. |
For more information on LOGMODE, see Choosing a Log Mode.
This parameter determines how often a checkpoint is taken. A checkpoint is an internal operation in which data and status information is written to permanent (DASD) storage, and a summary status record is written to the log data set. A checkpoint causes two important events:
As updates to data occur, duplicate copies of changed data pages are maintained. These copies (called shadow pages) are kept in the storage pools of the pages that were changed. A checkpoint frees the shadow pages, and thereby frees the storage pool space where they are kept.
If LOGMODE=Y, a checkpoint typically frees log space by moving the logical beginning of the log forward to the beginning of the oldest LUW still active at the time of the checkpoint. If LOGMODE=A or L, log space is only freed when an archive is taken; not on every checkpoint.
Checkpoints are taken periodically: however, by the time one is taken, there may be a large amount of data to be committed. If a failure should occur before it is committed, much processing may need to be redone after the database is restored.
The CHKINTVL parameter lets you take checkpoints at predetermined intervals. Its value is specified in terms of the number of log pages written between checkpoints. You can use the SET operator command to change the value of CHKINTVL without having to stop and restart the application server. See the DB2 Server for VSE & VM Operation manual for more information on the SET operator command.
By setting it low, you minimize the risk of filling the log or storage pools. However, because checkpoints are time-consuming operations that suspend SQL processing until they are completed, they should be taken infrequently. For more information on setting CHKINTVL, see the DB2 Server for VSE & VM Performance Tuning Handbook.
This parameter defines the point at which the log cushion is entered and log-full processing begins. Its value is expressed in terms of the percentage of the log size. The default of 90 means that when the log is 90% full, log-full processing will be initiated.
In log-full processing, the oldest active LUWs are rolled back until enough log space is freed to bring the percentage of the log in use below the SLOGCUSH level. Ideally, checkpoints and archiving would continually free log space so that the log would never reach the SLOGCUSH level.
If the log should become 100% full, the database manager would end abnormally, so you should set SLOGCUSH to a value that allows log-full processing to take effect (free some log space) before this happens. If the database manager is ending with log-full conditions, you may want to lower the SLOGCUSH value or increase the size of your log data sets.
This parameter can be used to define a point at which an archive is automatically initiated.
It is used only when LOGMODE=A or L is specified. Like SLOGCUSH, its value is expressed in terms of a percentage of the log.
Archives free up log space; however, they take some time to complete. If the SLOGCUSH value is reached during an online archive operation, all SQL processing is suspended until the archive is done. For this reason, it is best to ensure that archives are initiated in time to finish before the log fills to the SLOGCUSH percentage. This is done by setting the value of ARCHPCT lower than the value of SLOGCUSH.
When the log becomes full to the ARCHPCT value, a message is issued to the VSE system operator to mount an archive tape and identify the cuu of the tape drive. The database manager then takes a database or log archive depending on whether you have LOGMODE set to A (database) or L (log).
Normally, the operator explicitly archives the database or the log before the ARCHPCT value is reached, by issuing one of the archive commands. If the ARCHPCT is reached, meaning that the log is almost full, the action that the database manager takes depends on the LOGMODE that is in effect. See
Table 6 for a summary of these actions.
Table 6. Summary of Activity When ARCHPCT Level Is Reached
| LOGMODE Parameter | Activity When ARCHPCT is Reached |
|---|---|
| A | An operator message is issued that requests a database archive. |
| L | An operator message is issued that requests a log archive. |
| Y | Because the log cannot be archived, the value for ARCHPCT is ignored. When the log is full it wraps. If an LUW spans the entire log, a ROLLBACK WORK is forced for that LUW. |
| Note: | To see how full the log is, you can issue the SHOW LOG command. For a description of this command, see the DB2 Server for VSE & VM Operation manual. |
This parameter defines the storage cushion for storage pools.
Its value is expressed as a percentage of space remaining in a storage pool. In multiple user mode processing (and single user mode processing where LOGMODE is not N), if any storage pool gets full to the point where only the SOSLEVEL percentage of storage pool pages is still free, a checkpoint is taken to free any shadow pages in use.
If, following this, only enough pages are freed to bring the number of free pages just above the SOSLEVEL, frequent checkpointing could occur. For more information, see the DB2 Server for VSE & VM Diagnosis Guide and Reference manual. If, however, the number of free storage pool pages is still at or below SOSLEVEL, message ARI0202I is issued once to inform the user that the number of free pages left in the storage pool is fewer than the SOSLEVEL. This message is also issued once in single user mode with LOGMODE=N, but no checkpoint is taken.
Attention: If message ARI0202I is received, it indicates some action may be needed to prevent imminent filling of the storage pool.
One possible action is to stop the application server and extend that storage pool by adding dbextents to it. However, you can remedy the situation without stopping the application server if you have set SOSLEVEL high enough to give you adequate warning. When the message is received, proceed to remove unneeded data from the storage pool, either by dropping dbspaces or tables, or by reorganizing the data with a smaller percentage of free space for each page. In order to do this, you must have adequate warning to schedule the necessary processing.
This parameter:
Depending on the operating procedures at your installation, set DSPLYDEV=C if the output should be sent to SYSLOG; to L if the output should be sent to SYSLST; and to B if the output should be sent to both SYSLOG and SYSLST.
Generally, you will want to set DSPLYDEV to either C or B for debugging a problem from the operator's console; and to L for normal operations.
This parameter defines whether or not dumps are to be taken, and the amount of information to be dumped if they are.
DUMPTYPE=N indicates that a dump is not taken.
DUMPTYPE=F gives you a full partition dump on some error conditions, including trace points.
DUMPTYPE=P gives you a partial dump of the database partition, excluding major phases (read-only code), on certain error conditions. A dump is not taken when a limit error (message ARI0039E) or hardware error (message ARI0041E) occurs, or when a user specification error is detected.
It is recommended that you always use DUMPTYPE=F and not DUMPTYPE=P, as partial dumps generally do not contain enough information for debugging problems. The partial dump output is also generated to the trace tape for some trace points.
Generally, you should not run with DUMPTYPE=N (no dumps), but you may find occasions when you want to prevent dumping.
You can use the SET operator command to change the value of DUMPTYPE without having to stop and restart the application server. See the DB2 Server for VSE & VM Operation manual for more information on the SET operator command.
| Note: | Any changes you make using the SET command are only in effect while the application server is running. If you stop and restart the application server, it will use the settings you specified in the startup procedure. |
For more information on dumps, see the DB2 Server for VSE & VM Diagnosis Guide and Reference manual.
This parameter specifies whether or not special recovery commands are processed at startup.
Only set it to Y when you have a DBSS processing error or a severe user error. For more information on this parameter, see the discussion on starting the application server to recover from DBSS errors in the DB2 Server for VSE & VM Diagnosis Guide and Reference manual.
call the trace facilities during startup (as opposed to the TRACE operator command). Except for TRACWUM and TRACDRRM (which are not supported in single user mode), they are used primarily for tracing in single user mode, but can be set in multiple user mode if you want to start tracing as soon as possible. For information about tracing, refer to the DB2 Server for VSE & VM Operation manual.
The TRACRDS parameter is also used to start security audit tracing during startup. A security audit is a special case of the normal trace facility. Unlike other traces, which are usually only started for problem determination, a security audit trace may be continually active while the database manager is running. This may be a standard procedure for some installations.
If you do not want the security audit to be continually active, you can start and stop it with the TRACE operator command instead.
For more information, see the discussion on security auditing in the DB2 Server for VSE & VM Database Administration manual.
This parameter specifies the amount of memory (in kilobytes) to allocate to the trace buffer. Specifying a nonzero value causes trace output to be stored in a fixed size buffer in memory. Trace records are stored in wrap-around mode in this buffer, and when tracing is turned off, the contents of the buffer are written to disk or to tape (as specified by the ARITRAC FILEDEF statement). The trace buffer is only created if you specify TRACEBUF with at least one of the startup initialization parameters TRACRDS, TRACDBSS, TRACDSC, TRACCONV, TRACDRRM, TRACWUM, or TRACSTG; it is not created if the TRACEBUF default (n=0) is specified. A suggested size for the trace buffer is 100 kilobytes or more. If you do not specify TRACEBUF and tracing is requested, trace records are written directly to disk or tape as the trace points are processed.
Figure 18 identifies the initialization parameters that apply when the database manager is operating in single user mode.
Figure 18. Single User Mode Initialization Parameters
Most of the considerations for setting these parameters are the same as those described under Multiple User Mode Initialization Parameters, with the following exceptions:
If LOGMODE=N, database changes are only committed when a checkpoint is explicitly taken (with COMMIT WORK statements).
The ARCHPCT parameter cannot be specified if LOGMODE=N.
The database manager can write archive information, trace output, and accounting output to tape files. Users can use tape files for input and output to the DBS utility. They can also use tape for input and output for a preprocessor, but this use is not usually done.
To assign tape files, use the usual TLBL job control statements. Most installations place these statements for the trace, database archive, log archive, and accounting tapes in the cataloged procedure used to identify the database data sets. An example of this is under Step 3: Setting Up Your Database Job Control. Other examples are shown throughout the manual as needed.
| Note: | All tapes used by the database manager must use IBM standard (EBCDIC) labels. Unlabeled tapes are not supported. |
When the application server is started in multiple user mode, operator commands can be issued and the operator may receive messages requesting that specific actions be done (for example, mounting a tape).
If you have a single database, start the application server like any batch job, by submitting job control statements or entering an EXEC command for ARISQLDS from the system operator console. To simplify the startup process, keep the DLBL and TLBL job control statements in the standard label area.
The job control example in Figure 19 shows how to start the application server by allowing the default initialization parameters to set up a normal multiple user mode environment.
Figure 19. Job Control to Start in Multiple User Mode
// JOB MULTI // EXEC PROC=ARIS71PL // EXEC PROC=ARIS71DB // EXEC PGM=ARISQLDS,SIZE=AUTO,PARM='DBNAME=SQLDB1_NEWYORK_INV' /* /& |
Notes:
If you have generated more than one DB2 Server for VSE database, you would have a job control procedure for each (for example, ARIS71DB, DBNAME01, and DBNAME02). To start the application server, you would reference the appropriate cataloged procedure.
There is nothing special about using cataloged procedures as shown in Figure 19: this is just one of the ways you can use VSE job control facilities to run the database manager. For other techniques, see your VSE manuals.
The job control statements and procedures must include:
The trace output file can be a tape file or a DASD file. If it is to be a tape file, you need a TLBL statement, and the file name on this statement must be ARITRAC. If it is to be a disk file, you need a DLBL, an EXTENT, and an ASSGN statement. If the disk file is managed by the VSE/VSAM Space Management for SAM Feature, the EXTENT statement is optional, and the ASSGN statement is not applicable. The file name on the DLBL statement must be ARITRAC. For examples of job control statements that you can use for tracing to disk, see the DB2 Server for VSE & VM Diagnosis Guide and Reference manual.
When the application server is started in multiple user mode, batch SQL application programs can be started by normal means. Figure 20 shows the job control statements for starting a batch program and for passing user parameters directly to that program.
| Note: | If you plan to run your application programs in both multiple user mode and single user mode, follow the protocols discussed in the section CALL/RETURN Protocols for Application Programs in Single User Mode. |
Figure 20. Job Control to Start a Batch Application Program
// JOB USER PROGRAM WITH USER PARMS // EXEC USERPROG,SIZE=AUTO,PARM='parm1,parm2' /* /& |
Notes:
Running SQL application programs under VSE/ICCF is the same as running any program except it is recommended that you set GETVIS to AUTO on the /OPTION statement. To run user SQL programs, the VSE/ICCF user would do an /EXEC for the file containing the VSE/ICCF control statements. These statements load the program and specify the GETVIS=AUTO option, as suggested in Figure 21.
Figure 21. Example of VSE/ICCF Control Statements for Running an Application Program
/LOAD MYPROG,PARM='...' /OPTION GETVIS=AUTO /DATA * * * |
All CICS transaction programs written to use the database manager can be called using any of the means available under CICS. Usually, transactions are called directly from a terminal. If the terminal user has signed on to CICS, the CICS user ID is used as the default user ID for SQL operations done by the transaction only if the transaction does not issue an SQL CONNECT statement. If the terminal user has not signed on, the default user ID for CICS users is used. This ID was defined when online support was started through the CIRB or CIRA transaction. For a more complete discussion, see the DB2 Server for VSE & VM Application Programming manual.
CICS transactions can also be initiated by other means not directly tied to users. In these situations, the default user ID will be the one defined by the CIRB or CIRA transaction.
For CICS SQL transactions to be run, the database manager must be running in multiple user mode, and the online support must have been started with either the CIRB or CIRA transaction.
"Pseudo-conversational" refers to a technique for coding CICS transactions that interact with a user at a terminal. The transaction is not active while it is waiting for the user to enter a response. The following scenario shows one method of coding a pseudo-conversational transaction:
ISQL is not pseudo-conversational. It runs as two CICS transactions (named ISQL and CISQ).
When a long-running SQL statement is being processed, the ISQL transaction:
Command in progress. Terminal is now free.
This allows the user to enter ISQL CANCEL to cancel a long-running SQL statement. If this is not done, the ISQL transaction is restarted by the CISQ transaction when the SQL statement completes. The results of the SQL statement are displayed when the ISQL transaction is restarted by the CISQ transaction.
If the ISQL transaction times-out (ends with message ARI7044I) and a pseudo-conversational transaction is started, the following events can cause confusion:
To eliminate this confusion, avoid running pseudo-conversational transactions and ISQL on the same terminal at the same time.
An application program running in single user mode runs in the database partition under the control of the database manager. The application server is started in single user mode (SYSMODE=S), and the program name is provided as an initialization parameter (PROGNAME=name).
Figure 22 shows an example. When the application server is started, it passes control to the application program specified by the PROGNAME parameter. All other initialization parameters are allowed to default.
| Note: | The PROGNAME parameter is not used if STARTUP is specified as C (database generation), E (adding dbextents), L (log reformatting or reconfiguration), S (adding dbspaces), I (reorganization of catalog indexes), or M (catalog migration). These types of startup specify the operation to be performed, so a program name is not needed. |
Figure 22. Job Control to Start in Single User Mode
// JOB SINGLE // EXEC PROC=ARIS71PL // EXEC PROC=ARIS71DB // EXEC PGM=ARISQLDS,SIZE=AUTO,PARM='SYSMODE=S,PROGNAME=name' /* /& |
| Note: | It may be necessary to specify SIZE=(AUTO,nK) for ARISQLDS, where the nK value specifies the amount of storage required by the programming language to load run-time routines and perform dynamic storage allocation. |
When starting the application server in single user mode, you can also specify user parameters to be passed to the application program, along with initialization parameters passed to the application server. A slash (/) must be placed between the application server parameters and the application program parameters, as shown in Figure 23.
Figure 23. Job Control to Start in Single User Mode and Provide User Parameters
// JOB SINGLE WITH USER PARMS // EXEC PROC=ARIS71PL // EXEC PROC=ARIS71DB // EXEC PGM=ARISQLDS,SIZE=AUTO,PARM='SYSMODE=S,PROGNAME=PROG1/parm1,parm2' /* /& |
| Note: | Up to 100 characters can be specified as parameters on the EXEC statement or command. Each parameter must be separated by at least one comma or blank; these commas and blanks also count as characters. The parameter string cannot extend past column 71. If a continuation is needed, column 72 must contain a continuation character (any nonblank character), and the parameters must continue in column 16 of the next line. |
Parameters are passed to the application program by the standard VSE/ESA protocol, as shown in Figure 24. When the database manager has processed its initialization parameters, it passes the address (in register 1) of a pointer to the application program specified by the PROGNAME parameter. (If there are no user parameters, the pointer contains binary zeros.)
Figure 24. Passing User Parameters to a User Application Program
Application program parameters are not displayed along with the initialization parameters. Application program parameters also cannot be specified in a DB2 Server for VSE parameter data set.
In single user mode, an application is called using normal CALL/RETURN protocols, as follows:
| Note: | This same protocol can also be used by programs running in multiple user mode. |
Upon entry, the application program must store the registers in the DB2 Server for VSE save area, and restore them before returning control to the database manager. Failure to do so causes unpredictable results.
The database manager sets an abnormal task termination routine to intercept abnormal end conditions, including program checks.
If the user program issues an STXIT AB macro instruction, this macro overrides the DB2 Server for VSE STXIT AB macro instruction. If the user program issues an STXIT PC macro instruction, this macro overrides the DB2 Server for VSE STXIT AB macro instruction for program checks. A user program should not issue one of these STXIT macro instructions.
Notes:
An application program should always return control to the database manager (if possible). It should never issue a CANCEL, DETACH, DUMP, JDUMP, RETURN or EOJ (or equivalent macro) instruction. These instructions prevent the database manager from doing its normal shutdown processing, such as closing the database files and the trace tape file (if one is being used).
The DB2 Server for VSE abnormal end routines issue CLOSE macro instructions for the database, the trace and accounting files if those facilities were activated, and the SYSLST file if it was opened. This same close process is also done when the application program returns control to the database manager at the end of the job. If you do not return control to the database manager, the files cannot be closed. However, the database manager does not have to close these files. The VSAM automatic close function will be started and the application server will still be accessible. When the application server is next started, VSAM may issue an informational message stating that the files were not closed in the previous run. If tracing or accounting were active, their output files may not have had the last buffer (or buffers) written. And, if the output files were on tape, no tape mark would be written.
The database manager uses an "eye-catcher" technique for determining when a specific module is in error. The eye-catcher is displayed in the DB2 Server for VSE mini-dump. An application program can use the same technique in single user mode, assuming that the DB2 Server for VSE abnormal end exit has not been overridden by a user STXIT AB or STXIT PC macro instruction. A suggested coding example in assembler language is shown in Figure 25.
Figure 25. Use of an Eye-catcher by an Application Program
USING *,15
B SKIPEYE BRANCH AROUND EYE-CATCHER
DC AL1(16) LENGTH OF CHARACTER STRING
DC CL8'progname' PROGRAM NAME EYE-CATCHER
DC CL8'&SYSDATE' DATE PROGRAM COMPILED
DS 0H
SKIPEYE EQU *
STM 14,12,12(13) SAVE DB2 Server for VSE REGISTERS
BALR 12,0 ESTABLISH BASE REGISTERS
DROP 15
USING *,12
LA 11,MYSAVEAR GET ADDRESSABILITY TO MY SAVE AREA
ST 11,8(13) SAVE ADDRESS OF SAVE AREA IN DB2 Server for VSE SAVE AREA
ST 13,MYSAVEAR+4 SAVE ADDRESS OF DB2 Server for VSE SAVE AREA IN SAVE AREA
LR 13,11 SET REGISTER 13 TO MY SAVE AREA
*
Body of the Application Program
*
EXIT L 15,RETCOD SET RETURN CODE (OR SET TO ZERO)
L 13,4(13) GET DB2 Server for VSE SAVE AREA
L 14,12(13) GET DB2 Server for VSE REGISTER 14
LM 0,12,20(13) GET OTHER DB2 Server for VSE REGISTERS
BR 14 RETURN TO DATABASE MANAGER
|
Notes:
When starting the application server, you can change the default parameter values in either of two ways:
You can also combine the two methods. Parameters specified in the parameter data set override the default values, while those specified with the EXEC override both the default values and those specified in the parameter data set. Thus, a user who has a parameter data set with an incorrect parameter value can override the error with a correct specification on the EXEC statement or command.
When all the values of the initialization parameters have been resolved, the final values (or defaults, if no values have been overridden) are displayed on SYSLOG, SYSLST, or both (according to the value of the DSPLYDEV parameter).
You can use up to 100 characters on the EXEC command for specifying parameters. Separate each parameter by at least one comma or blank, but the commas and blanks also count as characters. An example of specifying parameters with the EXEC command is:
EXEC ARISQLDS,SIZE=AUTO,PARM='DSPLYDEV=B,DUMPTYPE=F'
Because of the 100-character limit for parameters on the EXEC statement or command, you may choose to set up your initialization parameters in one or more A-type source members. Such an arrangement allows you to specify more user parameters (if any) when running application programs in single user mode. User parameters (those for the application program itself), cannot be specified in a source member, and must be specified in the PARM field of the job control EXEC statement or command. If you plan to use application program parameters, refer to Specifying User Parameters.
You can store various parameters in A-type source members. You can have as many A-type source members as you need. Each one can start the application server for a slightly different environment. To use the parameters, specify the member name in the PARMID initialization parameter. Figure 26 shows an example of a job that catalogs a source member.
Figure 26. Job to Catalog a Source Member
// JOB CATALPRM // EXEC LIBR ACCESS SUBLIB=LIBRARY.SUBLIB CATALOG PARMXMPL.A DBNAME=SQLDB1_NEWYORK_INV,RMTUSERS=50, DSPLYDEV=B,NDIRBUF=20,SYSMODE=S, PROGNAME=USERPROG,NPAGBUF=20, DUMPTYPE=F COMMENT - FULL PARTITION DUMP NCSCANS=20 /+ /* /& |
The rules for specifying parameters in a member are a little different from those specifying parameters in the job control. In particular: