IBM Support

Unsolicited SQL updates from User Defined Function

Troubleshooting


Problem

When a field from the result of any SQL Query operation (for example, DirectSQL or GetByFilter) is passed to a User Defined Function within a Policy, Impact outputs an SQL Update to update that field back in the database table.

Symptom

Impact runs an unexpected SQL Update to update a field from a look-up with the value it already has.

Cause

The result from an SQL query operation is an SQLOrgNode. When passing an SQLOrgNode to a User Defined Function, the function assigns the same value to the variable, causing Impact to call an action to update the field.

This is recorded as IZ67227: UNSOLICITED SQL UPDATE STATEMENT PRODUCED BY FUNCTION CALL WHEN LOOPING THROUGH GETBY* RESULT SET with more information available from the link below.

Diagnosing The Problem

Create a simple Policy with a User Defined Function (UDF) that performs a simple action against a String. In the same Policy, create a DirectSQL() Function to query some field from a database, then pass a suitable field to the UDF. You will see in the PolicyLogger log file that Impact runs an SQL Update to update that same field with the same value.

Here's a simple Policy example:

    Function MyFunction(MyString)
    {
      NodeLength=Length(MyString);
    }

    MyEvents=GetByFilter("My_AlertsStatus_DT", "Class=1111", false);
    NumMyEvents=Length(MyEvents);
    LoopCount=0;
    While(LoopCount < NumMyEvents)
    {
      MyEvent=MyEvents[LoopCount];
      MyFunction(MyEvent.Node);
      LoopCount=LoopCount+1;
    }

Resolving The Problem

Do not pass a field directly to a user-defined function. Instead, assign the value of the field to a variable, then pass that variable to the user-defined function.

From the While{} section of the example above, use:

    While(LoopCount<NumMyEvents)
    {
      MyEventNode=MyEvents[LoopCount].Node;
      MyFunction(MyEventNode);
      LoopCount=LoopCount+1;
    }

Related Information

[{"Product":{"code":"SSSHYH","label":"Tivoli Netcool\/Impact"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Netcool\/Impact","Platform":[{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"},{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"}],"Version":"6.1;6.1.1;7.1.0","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

Historical Number

38861 660 706

Document Information

Modified date:
17 June 2018

UID

swg21421783