The SET_CONFIG stored procedure updates the database and database manager configuration parameters that are returned by the GET_CONFIG procedure.
The SET_CONFIG procedure accepts an input XML document that contains configuration parameters and their values, uses this information to update the specified configuration parameters, and returns an output XML document that indicates the update status of each configuration parameter.
>>-SET_CONFIG--(--major_version--,--minor_version--,--requested_locale--,--> >--xml_input--,--xml_filter--,--xml_output--,--xml_message--)--><
The schema is SYSPROC.
Currently, the only supported value for requested_locale is en_US.
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.
db2 "call sysproc.set_config (null,null,null,null,null,?,?)"
Value of output parameters
--------------------------
Parameter Name : MAJOR_VERSION
Parameter Value : 1
Parameter Name : MINOR_VERSION
Parameter Value : 0
Parameter Name : XML_OUTPUT
Parameter Value : -
Parameter Name : XML_MESSAGE
Parameter Value : -
Return Status = 0
Example 2: Run a script called setconfig.sql that updates a few database and database manager configuration parameters.
setconfig.sql:
call sysproc.set_config(1,0,'en_US',blob('
<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
<key>Document Type Name</key><string>Data Server Set Configuration Input</string>
<key>Document Type Major Version</key><integer>1</integer>
<key>Document Type Minor Version</key><integer>0</integer>
<key>Document Locale</key><string>en_US</string>
<key>Database Manager Configuration Parameter Settings</key>
<dict>
<key>diaglevel</key><dict><key>Parameter Value</key>
<dict>
<key>Value</key><string>4</string>
</dict>
</dict>
<key>fcm_num_buffers</key>
<dict>
<key>Parameter Value</key>
<dict>
<key>Value</key><string>4096</string>
</dict>
<key>Value Flags</key>
<dict>
<key>Value</key><string>MANUAL</string>
</dict>
</dict>
<key>instance_memory</key>
<dict>
<key>Deferred Value</key>
<dict>
<key>Value</key><string>7424</string>
</dict>
<key>Deferred Value Flags</key>
<dict>
<key>Value</key><string>AUTOMATIC</string>
</dict>
</dict>
</dict>
<key>Database Partition</key>
<dict>
<key>All</key>
<dict>
<key>Database Configuration Parameter Settings</key>
<dict>
<key>avg_appls</key>
<dict>
<key>Parameter Value</key>
<dict>
<key>Value></key><string>2</string>
</dict>
<key>Value Flags</key>
<dict>
<key>Value</key><string>AUTOMATIC</string>
</dict>
</dict>
<key>database_memory</key>
<dict>
<key>Deferred Value</key>
<dict>
<key>Value</key><string>2</string>
</dict>
<key>Deferred Value Flags</key>
<dict>
<key>Value</key><string>MANUAL</string>
</dict>
</dict>
</dict>
</dict>
</dict>
</dict>
</plist>'), null, ?,?)@
Value of output parameters
--------------------------
Parameter Name : MAJOR_VERSION
Parameter Value : 1
Parameter Name : MINOR_VERSION
Parameter Value : 0
Parameter Name : XML_OUTPUT
Parameter Value : x'3C3F78...'
Parameter Name : XML_MESSAGE
Parameter Value : -
Return Status = 0
<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
<key>Document Type Name</key>
<string>Data Server Set Configuration Output</string>
<key>Document Type Major Version</key><integer>1</integer>
<key>Document Type Minor Version</key><integer>0</integer>
<key>Data Server Product Name</key><string>QDB2/AIX64</string>
<key>Data Server Product Version</key><string>9.7.0.0</string>
<key>Data Server Major Version</key><integer>9</integer>
<key>Data Server Minor Version</key><integer>7</integer>
<key>Data Server Platform</key><string>AIX 64BIT</string>
<key>Document Locale</key><string>en_US</string>
<key>Database Manager Configuration Parameter Settings</key>
<dict>
<key>Display Name</key>
<string>Database Manager Configuration Parameter Settings</string>
<key>diaglevel</key>
<dict>
<key>Display Name</key><string>diaglevel</string>
<key>Parameter Value</key>
<dict>
<key>Display Name</key><string>Parameter Value</string>
<key>Value</key><string>4</string>
</dict>
<key>Parameter Update Status</key>
<dict>
<key>Display Name</key><string>Parameter Update Status</string>
<key>SQLCODE</key>
<dict
<key>Display Name</key><string>SQLCODE</string>
<key>Value</key><integer>0</integer>
</dict>
<key>Message Tokens</key>
<dict>
<key>Display Name</key><string>Message Tokens</string>
<key>Value</key><array><string></string></array>
</dict>
<key>SQLSTATE</key>
<dict>
<key>Display Name</key><string>SQLSTATE</string>
<key>Value</key><string></string>
</dict>
</dict>
</dict>
<key>fcm_num_buffers</key>
<dict>
<key>Display Name</key><string>fcm_num_buffers</string>
<key>Parameter Value</key>
<dict>
<key>Display Name</key><string>Parameter Value</string>
<key>Value</key><string>4096</string>
</dict>
<key>Value Flags</key>
<dict>
<key>Display Name</key><string>Value Flags</string>
<key>Value</key><string>MANUAL</string>
</dict>
<key>Parameter Update Status</key>
<dict>
<key>Display Name</key><string>Parameter Update Status</string>
<key>SQLCODE</key><dict>
<key>Display Name</key><string>SQLCODE</string>
<key>Value</key> <integer>0</integer>
</dict>
<key>Message Tokens</key>
<dict>
<key>Display Name</key><string>Message Tokens</string>
<key>Value</key><array><string></string></array>
</dict>
<key>SQLSTATE</key>
<dict>
<key>Display Name</key><string>SQLSTATE</string>
<key>Value</key><string></string>
</dict>
</dict>
</dict>
<key>instance_memory</key>
<dict>
<key>Display Name</key><string>instance_memory</string>
<key>Deferred Value</key>
<dict>
<key>Display Name</key><string>Deferred Value</string>
<key>Value</key><string>7424</string>
</dict>
<key>Deferred Value Flags</key>
<dict>
<key>Display Name</key><string>Deferred Value Flags</string>
<key>Value</key><string>AUTOMATIC</string>
</dict>
<key>Parameter Update Status</key>
<dict>
<key>Display Name</key><string>Parameter Update Status</string>
<key>SQLCODE</key>
<dict>
<key>Display Name</key><string>SQLCODE</string>
<key>Value</key><integer>0</integer>
</dict>
<key>Message Tokens</key>
<dict>
<key>Display Name</key><string>Message Tokens</string>
<key>Value</key><array><string></string></array>
</dict>
<key>SQLSTATE</key>
<dict>
<key>Display Name</key><string>SQLSTATE</string>
<key>Value</key><string></string>
</dict>
</dict>
</dict>
</dict>
<key>Database Partition</key>
<dict>
<key>Display Name</key><string>Database Partition</string>
<key>All</key>
<dict>
<key>Display Name</key><string>All</string>
<key>Database Configuration Parameter Settings</key>
<dict>
<key>Display Name</key>
<string>Database Configuration Parameter Settings</string>
<key>avg_appls</key>
<dict>
<key>Display Name</key><string>avg_appls</string>
<key>Parameter Value</key>
<dict>
<key>Display Name</key><string>Parameter Value</string>
<key>Value</key><string>2</string>
</dict>
<key>Value Flags</key>
<dict>
<key>Display Name</key><string>Value Flags</string>
<key>Value</key><string>AUTOMATIC</string>
</dict>
<key>Parameter Update Status</key>
<dict>
<key>Display Name</key><string>Parameter Update Status</string>
<key>Update Coverage</key>
<dict>
<key>Display Name</key><string>Update Coverage</string>
<key>Value</key><string>Complete</string>
</dict>
<key>SQLCODE</key>
<dict>
<key>Display Name</key><string>SQLCODE</string>
<key>Value</key><integer>0</integer>
</dict>
<key>Message Tokens</key>
<dict>
<key>Display Name</key><string>Message Tokens</string>
<key>Value</key><array><string></string> </array>
</dict>
<key>SQLSTATE</key>
<dict>
<key>Display Name</key><string>SQLSTATE</string>
<key>Value</key><string></string>
</dict>
</dict>
</dict>
<key>database_memory</key>
<dict>
<key>Display Name</key><string>database_memory</string>
<key>Deferred Value</key>
<dict>
<key>Display Name</key><string>Deferred Value</string>
<key>Value</key><string>2</string>
</dict>
<key>Deferred Value Flags</key>
<dict>
<key>Display Name</key><string>Deferred Value Flags</string>
<key>Value</key><string>MANUAL</string>
</dict>
<key>Parameter Update Status</key>
<dict>
<key>Display Name</key><string>Parameter Update Status</string>
<key>Update Coverage</key>
<dict>
<key>Display Name</key><string>Update Coverage</string>
<key>Value</key><string>Complete</string>
</dict>
<key>SQLCODE</key>
<dict>
<key>Display Name</key><string>SQLCODE</string>
<key>Value</key><integer>0</integer>
</dict>
<key>Message Tokens</key>
<dict>
<key>Display Name</key><string>Message Tokens</string>
<key>Value</key><array><string></string></array>
</dict>
<key>SQLSTATE</key>
<dict>
<key>Display Name</key><string>SQLSTATE</string>
<key>Value</key><string></string>
</dict>
</dict>
</dict>
</dict>
</dict>
</dict>
</dict>
</plist>
Example 3: Specify a filter to return the value for a specific configuration parameter.
db2 "call sysproc.set_config(1,0, 'en_US', blob('
<plist version="1.0">
<dict>
<key>Document Type Name</key><string>Data Server Set Configuration Input</string>
<key>Document Type Major Version</key><integer>1</integer>
<key>Document Type Minor Version</key><integer>0</integer>
<key>Document Locale</key><string>en_US</string>
<key>Database Manager Configuration Parameter Settings</key>
<dict>
<key>diaglevel</key>
<dict>
<key>Parameter Value</key>
<dict>
<key>Value</key><string>4</string>
</dict>
</dict>
<key>fcm_num_buffers</key>
<dict>
<key>Parameter Value</key>
<dict>
<key>Value</key><string>4096</string>
</dict>
<key>Value Flags</key>
<dict>
<key>Value</key><string>MANUAL</string>
</dict>
</dict>
<key>instance_memory</key>
<dict>
<key>Deferred Value</key>
<dict>
<key>Value</key><string>7424</string>
</dict>
<key>Deferred Value Flags</key>
<dict>
<key>Value</key><string>AUTOMATIC</string>
</dict>
</dict>
</dict>
<key>Database Partition</key>
<dict>
<key>All</key>
<dict>
<key>Database Configuration Parameter Settings</key>
<dict>
<key>avg_appls</key>
<dict>
<key>Parameter Value</key>
<dict>
<key>Value></key><string>2</string>
</dict>
<key>Value Flags</key>
<dict>
<key>Value</key><string>AUTOMATIC</string>
</dict>
</dict>
<key>database_memory</key>
<dict>
<key>Deferred Value</key>
<dict>
<key>Value</key><string>2</string>
</dict>
<key>Deferred Value Flags</key>
<dict>
<key>Value</key><string>MANUAL</string>
</dict>
</dict>
</dict>
</dict>
</dict>
</dict>
</plist>'),
blob('/plist/dict/key[.="Database Manager Configuration Parameter Settings"]
/following-sibling::dict[1]/key[3]
/following-sibling::dict[1]/dict[1]/key[.="Value"]
/following-sibling::string[1]'),?,?)"
The following is an example of output from this query:
Value of output parameters
--------------------------
Parameter Name : MAJOR_VERSION
Parameter Value : 1
Parameter Name : MINOR_VERSION
Parameter Value : 0
Parameter Name : XML_OUTPUT
Parameter Value : x'34303936'
Parameter Name : XML_MESSAGE
Parameter Value : -
Return Status = 0
The following value is returned for xml_output:4096
Example 4: Call the procedure from a function.
EXEC SQL BEGIN DECLARE SECTION;
sqlint16 getconfigMaj;
sqlint16 getconfigMin;
SQL TYPE IS BLOB(2M) xmlOutput;
SQL TYPE IS BLOB(2K) xmlOutMessage;
EXEC SQL END DECLARE SECTION;
getconfigMaj = 1;
getconfigMin = 0;
EXEC SQL CALL SYSPROC.SET_CONFIG(
:getconfigMaj,
:getconfigMin,
'en_US',
BLOB('blob('
<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
<key>Document Type Name</key>
<string>Data Server Set Configuration Input</string>
<key>Document Type Major Version</key><integer>1</integer>
<key>Document Type Minor Version</key><integer>0</integer>
<key>Document Locale</key><string>en_US</string>
<key>Database Manager Configuration Parameter Settings</key>
<dict>
<key>diaglevel</key><dict><key>Parameter Value</key>
<dict>
<key>Value</key><string>4</string>
</dict>
</dict>
<key>fcm_num_buffers</key>
<dict>
<key>Parameter Value</key>
<dict>
<key>Value</key><string>4096</string>
</dict>
<key>Value Flags</key>
<dict>
<key>Value</key><string>MANUAL</string>
</dict>
</dict>
<key>instance_memory</key>
<dict>
<key>Deferred Value</key>
<dict>
<key>Value</key><string>7424</string>
</dict>
<key>Deferred Value Flags</key>
<dict>
<key>Value</key><string>AUTOMATIC</string>
</dict>
</dict>
</dict>
<key>Database Partition</key>
<dict>
<key>All</key>
<dict>
<key>Database Configuration Parameter Settings</key>
<dict>
<key>avg_appls</key>
<dict>
<key>Parameter Value</key>
<dict>
<key>Value></key><string>2</string>
</dict>
<key>Value Flags</key>
<dict>
<key>Value</key><string>AUTOMATIC</string>
</dict>
</dict>
<key>database_memory</key>
<dict>
<key>Deferred Value</key>
<dict>
<key>Value</key><string>2</string>
</dict>
<key>Deferred Value Flags</key>
<dict>
<key>Value</key><string>MANUAL</string>
</dict>
</dict>
</dict>
</dict>
</dict>
</dict>
</plist>'),
null,
:xmlOutput,
:xmlOutMessage );