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 = 0×0b00
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 = 0×0b00
Database release level = 0×0b00
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.
