IBM Support

Enable GET DIAGNOSTICS MESSAGE_TEXT to work against external UDF and UDTFs

News


Abstract

Enable GET DIAGNOSTICS MESSAGE_TEXT to work against external UDF and UDTFs

Content

You are in: IBM i Technology Updates > Db2 for i - Technology UpdatesDb2 for i Functional Enhancements > Enable GET DIAGNOSTICS MESSAGE_TEXT to work against external UDF and UDTFs

When external User Defined Functions (UDF) and User Defined Table Functions (UDTF) are created with PARAMETER STYLE SQL, the external function has the ability to return message text to describe a failure.

The SQL Reference describes the message text parameter as:

A VARCHAR(1000) output parameter for the message text.

Prior to this change, the application had no way of retrieving the message text using SQL.

SQL applications can retrieve the unique message text returned by the UDF/UDTF via the GET DIAGNOSTICS SQL statement.

For example:

EXEC SQL GET DIAGNOSTICS CONDITION 1 :Lclmsgtext = MESSAGE_TEXT;

Note:

  • The GET DIAGNOSTICS SQL statement must be executed immediately after execution of the SQL statement which used the UDF or UDTF
  • If the UDF or UDTF did not return anything for the message text, GET DIAGNOSTICS will return the message text associated with the SQL message identifier

[{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB08","label":"Cognitive Systems"}}]

Document Information

Modified date:
14 January 2020

UID

ibm11167682