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. |
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"