Systems Engineering and RDBMS

Checking instance and database parameters

Posted by decipherinfosys on March 21, 2007

In yesterday’s post, we had covered how to check for init.ora parameters and their settings in Oracle. In this post, we will cover SQL Server and DB2 LUW.

MS SQL Server provides system stored procedures sp_configure (for instance level parameters – we had covered this in an earlier post – search for sp_configure on this site) and sp_dboption (for database level parameters) to display and set the parameter values. Following command shows all the database options which are set currently for the database. You can execute it from Query analyzer or management studio (SQL Server 2005):

exec sp_dboption ‘DECIPHER’

Output will be

The following options are set
———————————–
trunc. log on chkpt.
torn page detection
arithabort
autoshrink
quoted identifier
auto create statistics
auto update statistics

If you want to look for specific option then, following is the syntax with the output. It will display option name and whether the option is on or off.

exec sp_dboption ‘DECIPHER’, ‘torn page detection’

OptionName            CurrentSetting
—————        —————–
torn page detection    ON

Using extra parameter value ‘ON’ or ‘OFF’ we can set the value of specific dboption.

Alternatively, we can use DATABASPROPERYEX function to look at the current setting of database options. Following is a small SQL to check the value for three database options. It returns current setting of the property of the selected database but only one at a time and hence we have used UINON ALL operator.

SELECT ‘ANSI_NULLS’as OptionName,
CASE DATABASEPROPERTYEX(‘DECIPHER’ , ‘IsAnsiNullsEnabled’ )
WHEN 1 THEN ‘ON’
WHEN 0 THEN ‘OFF’
ELSE ‘UNKNOWN’
END as CurrentSetting
UNION ALL
SELECT ‘QUOTED_IDENTFIERS’,
CASE DATABASEPROPERTYEX(‘DECIPHER’ , ‘IsQuotedIdentifiersEnabled’ )
WHEN 1 THEN ‘ON’
WHEN 0 THEN ‘OFF’
ELSE ‘UNKNOWN’
END
UNION ALL
SELECT ‘ARITH_ABORT’,
CASE DATABASEPROPERTYEX(‘DECIPHER’ , ‘IsArithmeticAbortEnabled’)
WHEN 1 THEN ‘ON’
WHEN 0 THEN ‘OFF’
ELSE ‘UNKNOWN’
END
GO

And output is as under.

OptionName        CurrentSetting
—————– ————–
ANSI_NULLS        OFF
QUOTED_IDENTFIERS ON
ARITH_ABORT       ON

DB2 LUW also provides commands to view and set parameter values at instance (database manager) level or database level.  Just like Oracle and SQL Server, some of these parameters can be changed dynamically other requires instance to be bounced. Instance level parameters are common for all the databases on that particular instance.  And as name suggests, database level parameters are specific to each database.

Instance level parameters can be obtained using get dbm cfg command. If you are already on db2 command prompt then just issue following command.

db2 => get dbm cfg

OR, if you are at command prompt, then issue following command. It is followed by output. Output is modified and abbreviated.

D:\Program Files\IBM\SQLLIB\BIN>db2 get dbm cfg

Database Manager Configuration

Node type = Enterprise Server Edition with local and remote clients

Database manager configuration release level            = 0x0b00

Database monitor heap size (4KB)          (MON_HEAP_SZ) = 66
Java Virtual Machine heap size (4KB)     (JAVA_HEAP_SZ) = 512
Audit buffer size (4KB)                  (AUDIT_BUF_SZ) = 0
Size of instance shared memory (4KB)  (INSTANCE_MEMORY) = AUTOMATIC
Backup buffer default size (4KB)            (BACKBUFSZ) = 1024
Restore buffer default size (4KB)           (RESTBUFSZ) = 1024

In similar way, for database level parameters use get db cfg command. Here we are requesting parameters for specific database.

D:\Program Files\IBM\SQLLIB\BIN>db2 get db cfg for decipher

Database Configuration for Database decipher

Database configuration release level                    = 0x0b00
Database release level                                  = 0x0b00

Database territory                                      = US
Database code page                                      = 1252
Database code set                                       = IBM-1252
Database country/region code                            = 1
Database collating sequence                             = UNIQUE
Alternate collating sequence              (ALT_COLLATE) =
Database page size                                      = 4096

To update instance level and database level parameters we have to use update dbm cfg and update db cfg command. Following is the example of updating database parameter LOGFILSIZ.

D:\Program Files\IBM\SQLLIB\BIN>db2 “update db cfg for decipher using LOGFILSIZ
30000”
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, all
applications must disconnect from this database before the changes become
effective.

As mentioned earlier, for certain instance level (database manger configuration) parameters, database manger should be stopped and restarted using db2stop and db2start command so that changed parameter values become effective. For database configuration parameters, all the application must disconnect from the database, de-activate the database if it is already activated and then re-activate it. On the first new connection, changed value will be effective.

One Response to “Checking instance and database parameters”

  1. […] Posted by decipherinfosys on February 14, 2009 In one of our previous blog post, we showed how we can check database parameters and their values in different ways in Oracle and in SQLServer and DB2 LUW. […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: