The CANCEL_WORK stored procedure cancels either a specific activity (for example, a SQL statement), or all activity for a connected application.
>>-CANCEL_WORK--(--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.
<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
<key>Document Type Name</key><string>Data Server Cancel Work Input</string>
<key>Document Type Major Version</key><integer>1</integer>
<key>Document Type Minor Version</key><integer>0</integer>
<key>Required Parameters</key>
<dict>
<key>Application Handle</key>
<dict>
<key>Display name</key><string>Application Handle</string>
<key>Value</key><integer>10</integer>
<key>Hint</key>
<string>
Numeric value equivalent to the application handle to be cancelled
</string>
</dict>
</dict>
<key>Optional Parameters</key>
<dict>
<key>Unit Of Work Id</key>
<dict>
<key>Display Name</key><string>Unit Of Work Id</string>
<key>Value</key><integer>20</integer>
<key>Hint</key>
<string>
Numeric value that specifies the unit of work id of the activity
that is to be cancelled
</string>
</dict>
<key>Activity Id</key>
<dict>
<key>Display Name</key><string>Activity Id</string>
<key>Value</key><integer>10</integer>
<key>Hint</key>
<string>
Numeric value equivalent to the activity id to be cancelled
</string>
</dict>
</dict>
</dict>
</plist>
If you specify the application handle of the
application where the stored procedure is running, the procedure returns
a warning (SQL20458). Major version | Minor version | xml_output value |
---|---|---|
NULL | NULL | NULL |
1 | 0 | The status of the activity that the procedure attempted to cancel. |
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.
Example 1: Return the highest supported version of the procedure.
db2 "call sysproc.cancel_work(null,null,null,null,null,?,?)"
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 : -
Parameter Name : XML_MESSAGE
Parameter Value : -
Return Status = 0
Example 2: Cancel a specific activity.
db2 "call sysproc.cancel_work(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 Cancel Work Input</string>
<key>Document Type Major Version</key><integer>1</integer>
<key>Document Type Minor Version</key><integer>0</integer>
<key>Required Parameters</key>
<dict>
<key>Application Handle</key>
<dict>
<key>Display name</key><string>Application Handle</string>
<key>Value</key><integer>1</integer>
<key>Hint</key>
<string>
Numeric value equivalent to the application handle to be cancelled
</string>
</dict>
</dict>
<key>Optional Parameters</key>
<dict>
<key>Unit Of Work Id</key>
<dict>
<key>Display Name</key><string>Unit Of Work Id</string>
<key>Value</key><integer>2</integer>
<key>Hint</key>
<string>
Numeric value that specifies the unit of work id of the activity
that is to be cancelled
</string>
</dict>
<key>Activity Id</key>
<dict>
<key>Display Name</key><string>Activity Id</string>
<key>Value</key><integer>3</integer>
<key>Hint</key>
<string>
Numeric value equivalent to the activity id to be cancelled
</string>
</dict>
</dict>
</dict>
</plist> ) ,null,?,?)"
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'3C3F78...'
Parameter Name : XML_MESSAGE
Parameter Value : -
Return Status = 0
If the CANCEL_WORK procedure is able to cancel the activity, the XML output document contains the following content:
<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict><key>Document Type Name</key><string>Data Server Cancel Work 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>Successful Cancel Work Message</key>
<dict>
<key>Display Name</key><string>Successful Cancel Work Message</string>
<key>Value</key><string>The activity has been cancelled successfully</string>
<key>Hint</key><string></string>
</dict>
</dict>
</plist>
Example 2: Cancel the application.
db2 "call sysproc.cancel_work(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 Cancel Work Input</string>
<key>Document Type Major Version</key><integer>1</integer>
<key>Document Type Minor Version</key><integer>0</integer>
<key>Required Parameters</key>
<dict>
<key>Application Handle</key>
<dict>
<key>Display name</key><string>Application Handle</string>
<key>Value</key><integer>101</integer>
<key>Hint</key>
<string>
Numeric value equivalent to the application handle to be cancelled
</string>
</dict>
</dict>
</dict>
</plist> ),null,?,?)"
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'3C3F78...'
Parameter Name : XML_MESSAGE
Parameter Value : -
Return Status = 0
If the CANCEL_WORK procedure is able to cancel the application, the XML output document contains the following content:
<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
<key>Document Type Name</key><string>Data Server Cancel Work 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>Successful Cancel Work Message</key>
<dict>
<key>Display Name</key><string>Successful Cancel Work Message</string>
<key>Value</key>
<string>The application has been cancelled successfully</string>
<key>Hint</key><string></string>
</dict>
</dict>
</plist>
Example 3: Specify a filter to return the value of a successful cancel work message.
db2 "call sysproc.cancel_work(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 Cancel Work Input</string>
<key>Document Type Major Version</key><integer>1</integer>
<key>Document Type Minor Version</key><integer>0</integer>
<key>Required Parameters</key>
<dict>
<key>Application Handle</key>
<dict>
<key>Display name</key><string>Application Handle</string>
<key>Value</key><integer>101</integer>
<key>Hint</key>
<string>
Numeric value equivalent to the application handle to be cancelled
</string>
</dict>
</dict>
</dict>
</plist> ),blob('/plist/dict/key[.="Successful Cancel Work Message"]
/following-sibling::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'3C3F78...'
Parameter Name : XML_MESSAGE
Parameter Value : -
Return Status = 0
The following value is returned for xml_output:
"The application has been cancelled successfully"