A number of parameters are available which provide information about your database or influence the management of your database. These are grouped as follows:
The following parameters provide general information about the database:
With the exception of copyprotect, these parameters are provided for informational purposes only.
This parameter specifies the release level of the configuration file.
This parameter indicates the release level of the database manager which can use the database. In the case of an incomplete or failed migration, this parameter will reflect the release level of the unmigrated database and may differ from the release parameter (the release level of the database configuration file). Otherwise the value of database_level will be identical to value of the release parameter.
This parameter shows the territory used to create the database. Territory is used by the database manager to determine country parameter values. For more information about how the database manager uses the territory, see the Quick Beginnings .
This parameter shows the country code used to create the database. The country parameter is derived based on the territory parameter. For more information, see the Quick Beginnings .
This parameter shows the codeset that was used to create the database. Codeset is used by the database manager to determine codepage parameter values. For more information about how the database manager uses the codeset, see the Quick Beginnings .
This parameter shows the code page that was used to create the database. The codepage parameter is derived based on the codeset parameter. For more information, see the Quick Beginnings .
This parameter can only be displayed using the GET DATABASE CONFIGURATION API. It cannot be displayed through the command line processor or the Control Center.
This parameter provides 260 bytes of database collating information. The first 256 bytes specify the database collating sequence, where byte "n" contains the sort weight of the code point whose underlying decimal representation is "n" in the code page of the database.
The last 4 bytes contain internal information about the type of the collating sequence. You can treat it as an integer applicable to the platform of the database. There are three values:
If you use this internal type information, you need to consider byte reversal when retrieving information for a database on a different platform.
You can specify the collating sequence at database creation time.
This parameter enables the copy-protect attribute and is disabled by default. Prior to Version 2 of the database manager, the default was to enable the copy-protect attribute.
This parameter does not apply to UNIX-based environments.
The backup database and restore database utilities are not affected by the copyprotect parameter. It is possible to back up a copy-protected database, restore it to a different workstation, and then catalog and access the database.
Attention: Remove copy-protection from all databases before reinstalling either the database manager or the operating system. If you do not remove copy-protection, you will receive an error when you attempt to access the database. After you have reinstalled, you can enable copy-protection.
The following parameters provide information about the state of the database:
If set on, this parameter indicates that you must do a full backup of the database before accessing the it. This parameter is only on if the database configuration is changed so that the database moves from being nonrecoverable to recoverable (that is, initially both the logretain and userexit parameters were set to NO, then either one or both of these parameters is set to YES, and the update to the database configuration is accepted).
This parameter indicates whether the database is in a consistent state.
YES indicates that all transactions have been committed or rolled back so that the data is consistent. If the system "crashes" while the database is consistent, you do not need to take any special action to make the database usable.
NO indicates that a transaction is pending or some other task is pending on the database and the data is not consistent at this point. If the system "crashes" while the database is not consistent, you will need to restart the database using the RESTART DATABASE command to make the database usable. For more information about the RESTART DATABASE command, see the Command Reference.
This parameter can indicate one of the following states:
The recovery (using ROLLFORWARD DATABASE) must complete before you can access the database or table space. For more information about ROLLFORWARD DATABASE, see the Command Reference .
If set, this parameter indicates that log files are being retained for use in roll-forward recovery.
This parameter is set when the logretain parameter setting becomes active.
If set ON, this indicates that the database manager is enabled for roll-forward recovery and that the user exit program will be used to archive and retrieve log files when called by the database manager.
This parameter states whether a RESTORE PENDING status exists in the database.
Multipage file allocation is used to improve insert performance. It applies to SMS table spaces only. If enabled, all SMS table spaces are affected: there is no selection possible for individual SMS table spaces.
The default for the parameter is NO: multipage file allocation is not enabled.
Following database creation, the parameter may be set to YES which indicates that multipage file allocation is enabled. This is done using the db2empfa tool. Once set to YES, the parameter cannot be changed back to NO.
The following parameters provide information to influence the compiler:
This parameter sets the default value that determines the handling of arithmetic errors and retrieval conversion errors as errors or warnings during SQL statement compilation. For static SQL statements, the value of this parameter is associated with the package at bind time. For dynamic SQL DML statements, the value of this parameter is used when the statement is prepared.
Attention: If you change the dft_sqlmathwarn value for a database, the behaviour of check constraints, triggers, and views that include arithmetic expressions may change. This may in turn have an impact on the data integrity of the database. You should only change the setting of dft_sqlmathwarn for a database after carefully evaluating how the new arithmetic exception handling behaviour may impact check constraints, triggers, and views. Once changed, subsequent changes require the same careful evaluation.
As an example, consider the following check constraint, which includes a division arithmetic operation:
A/B > 0
When dft_sqlmathwarn is "No" and an INSERT with B=0 is attempted, the division by zero is processed as an arithmetic error. The insert operation fails because DB2 cannot check the constraint. If dft_sqlmathwarn is changed to "Yes", the division by zero is processed as an arithmetic warning with a NULL result. The NULL result causes the ">" predicate to evaluate to UNKNOWN and the insert operation succeeds. If dft_sqlmathwarn is changed back to "No", an attempt to insert the same row will fail, because the division by zero error prevents DB2 from evaluating the constraint. The row inserted with B=0 when dft_sqlmathwarn was "Yes" remains in the table and can be selected. Updates to the row that cause the constraint to be evaluated will fail, while updates to the row that do not require constraint re-evaluation will succeed.
Before changing dft_sqlmathwarn from "No" to "Yes", you should consider rewriting the constraint to explicitly handle nulls from arithmetic expressions. For example:
( A/B > 0 ) AND ( CASE
WHEN A IS NULL THEN 1
WHEN B IS NULL THEN 1
WHEN A/B IS NULL THEN 0
ELSE 1
END
= 1 )
can be used if both A and B are nullable. And, if A or B is not-nullable, the corresponding IS NULL WHEN-clause can be removed.
Before changing dft_sqlmathwarn from "Yes" to "No", you should first check for data that may become inconsistent, for example by using predicates such as the following:
WHERE A IS NOT NULL AND B IS NOT NULL AND A/B IS NULL
When inconsistent rows are isolated, you should take appropriate action to correct the inconsistency before changing dft_sqlmathwarn. You can also manually re-check constraints with arithmetic expressions after the change. To do this, first place the affected tables in a check pending state (with the OFF clause of the SET CONSTRAINTS statement), then request that the tables be checked (with the IMMEDIATE CHECKED clause of the SET CONSTRAINTS statement). Inconsistent data will be indicated by an arithmetic error, which prevents the constraint from being evaluated.
Recommendation: Use the default setting of no, unless you specifically require queries to be processed that include arithmetic exceptions. Then specify the value of yes. This situation can occur if you are processing SQL statements that, on other database managers, provide results regardless of the arithmetic exceptions that occur.
This parameter specifies the default value for the CURRENT DEGREE special register and the DEGREE bind option.
The default value is 1.
A value of 1 means no intra-partition parallelism. A value of -1 means the optimizer determines the degree of intra-partition parallelism based on the number of processors and the type of query.
The degree of intra-partition parallelism for a SQL statement is specified at statement compilation time using the CURRENT DEGREE special register or the DEGREE bind option. The maximum runtime degree of intra-partition parallelism for an active application is specified using the SET RUNTIME DEGREE command. The Maximum Query Degree of Parallelism (max_querydegree) configuration parameter specifies the maximum query degree of intra-partition parallelism for all SQL queries.
The actual runtime degree used is the lowest of:
The query optimization class is used to direct the optimizer to use different degrees of optimization when compiling SQL queries. This parameter provides additional flexibility by setting the default query optimization class used when neither the SET CURRENT QUERY OPTIMIZATION statement nor the QUERYOPT bind command are used.
The query optimization classes currently defined are:
Recommendation: For more information and guidance for selecting a suitable query optimization class, see "Adjusting the Optimization Class".
For more information on how a program can retrieve and modify database configuration parameters, see API Reference.
This parameter allows you to specify the number of "most frequent values" that will be collected when the WITH DISTRIBUTION option is specified on the RUNSTATS command. Increasing the value of this parameter increases the amount of statistics heap (stat_heap_sz) used when collecting statistics.
The "most frequent value" statistics help the optimizer understand the distribution of data values within a column. A higher value results in more information being available to the SQL optimizer but requires additional catalog space. When 0 is specified, no frequent-value statistics are retained, even if you request that distribution statistics be collected.
Updating this parameter can help the optimizer obtain better selectivity estimates for some predicates (=, <, >, IS NULL, IS NOT NULL) over data that is non-uniformly distributed. More accurate selectivity calculations may result in the choice of more efficient access plans.
After changing the value of this parameter, you need to:
For more information, see "Collecting and Using Distribution Statistics".
Recommendation: In order to update this parameter you should determine the degree of non-uniformity in the most important columns (in the most important tables) that typically have selection predicates. This can be done using an SQL SELECT statement that provides an ordered ranking of the number of occurrences of each value in a column. You should not consider uniformly distributed, unique, long, or LOB columns. A reasonable practical value for this parameter lies in the range of 10 to 100.
Note that the process of collecting frequent value statistics requires significant CPU and memory (stat_heap_sz) resources.
This parameter controls the number of quantiles that will be collected when the WITH DISTRIBUTION option is specified on the RUNSTATS command. Increasing the value of this parameter increases the amount of statistics heap (stat_heap_sz) used when collecting statistics.
The "quantile" statistics help the optimizer understand the distribution of data values within a column. A higher value results in more information being available to the SQL optimizer but requires additional catalog space. When 0 or 1 is specified, no quantile statistics are retained, even if you request that distribution statistics be collected.
Updating this parameter can help obtain better selectivity estimates for range predicates over data that is non-uniformly distributed. Among other optimizer decisions, this information has a strong influence on whether an index scan or a table scan will be chosen. (It is more efficient to use a table scan to access a range of values that occur frequently and it is more efficient to use an index scan for a range of values that occur infrequently.)
After changing the value of this parameter, you need to:
For more information, see "Collecting and Using Distribution Statistics".
Recommendation: This default value for this parameter guarantees a maximum estimation error of approximately 2.5% for any single-sided range predicate (>, >=, <, or <=), and a maximum error of 5% for any BETWEEN predicate. A rough rule of thumb for determining the number of quantiles is:
For example, 25 quantiles should result in a maximum estimate error of 4% for BETWEEN predicates and of 2% for ">" predicates. A reasonable practical value for this parameter lies in the range of 10 to 50.