A customer has recently upgraded their PA servers to 2.0.9.14, since then the behavior of ODBCOutputs has changed.

They have a TI process that has a simple:
ODBCOutput ( 'MyDSN', Expand ( 'DELETE FROM [MyTable] WHERE Version = ''XYZ'';') );

Previously (in older versions) if the table contained 0 rows where the version field was equal to XYZ, the process would just delete 0 rows and then continue.
In 2.0.9.14 if the WHERE clause returns 0 rows then the process aborts with an empty error message:
Error: Prolog procedure line (8): Error executing SQL query: "DELETE FROM [MyTable] WHERE Version = 'XYZ';". Error: ""

In 2.0.9.14, if the WHERE clause returns 1 or more rows then the process completes successfully. 

The customer in question is running an Oracle DB, I have just spun up a test environment and encountered the same issue on an MS SQL DB also.
So it is not localized to a specific Database software.


Is this a known change in behavior? And if so is there a wider reach where other ODBC connectivity that worked previously may also now start causing errors?

I am aware of workarounds in terms of coding that I can employ to resolve this issue but just looking at this stage to understand if this is considered "expected behavior" or potentially a bug.
And if it's expected behavior then what else do I need to look out for?

Thanks,
Declan

------------------------------
Declan Rodger
Technical Director
Spitfire Analytics
------------------------------

#PlanningAnalyticswithWatson

Hi Declan,

This is a logged APAR:

PH46281 PROCESS ABORT ON SQL_NO_DATA ODBC ERROR FOR ODBCOUTPUT WITH DELETE STATEMENT ON RECORD THAT DOES NOT EXIST IN DB2

Despite the title, this is a generic issue and will affect other DBs. The issue is flagged to be fixed in PA 2.0.9.15

Best regards
Jill

------------------------------
JILL MACLAURIN
------------------------------

You can add a :   SELECT TOP 1 * FROM [MyTable] WHERE Version = ''XYZ''; IF @@ROWCOUNT > 0 ; DELETE FROM [MyTable] WHERE Version = ''XYZ'';​

------------------------------
Tong Meng Lee
------------------------------

Hi Declan,

As you are pointing out, the issue is with statements that affects zero rows, both delete and insert statements. The workaround we are using, is to add a IF statement to the SQL output, and only executing it there i a rowcount <> zero.

Workaround for

    DELETE FROM [SCHEMA].[TABLE] WHERE X = Y 

would then be:

   IF ( (SELECT COUNT(1) FROM [SCHEMA].[TABLE] WHERE X = Y) <> 0) BEGIN DELETE FROM [SCHEMA].[TABLE] WHERE X = Y END

It can be cumbersome to go trough all processes, especially for longer SQL statements. We have stopped upgrading any customers beyond 2.0.9.12 due to the issues. We are also seeing issues importing from some sources, using non-unicode data, where processes are taking 100x longer then before. 



------------------------------
Emil Malmberg Fosdal
------------------------------

Hi Declan,

As you are pointing out, the issue is with statements that affects zero rows, both delete and insert statements. The workaround we are using, is to add a IF statement to the SQL output, and only executing it there i a rowcount <> zero.

Workaround for

    DELETE FROM [SCHEMA].[TABLE] WHERE X = Y 

would then be:

   IF ( (SELECT COUNT(1) FROM [SCHEMA].[TABLE] WHERE X = Y) <> 0) BEGIN DELETE FROM [SCHEMA].[TABLE] WHERE X = Y END

It can be cumbersome to go trough all processes, especially for longer SQL statements. We have stopped upgrading any customers beyond 2.0.9.12 due to the issues. We are also seeing issues importing from some sources, using non-unicode data, where processes are taking 100x longer then before.

------------------------------
Emil Malmberg Fosdal
Solution Architect
Cognitech A/S
------------------------------

Hi Everyone,

Thanks @JILL MACLAURIN for confirming the APAR. I had stumbled on that one and​ thought it sounded right but sadly I could only see the title of the APAR and didn't have access to see the detail behind it.

Emil - the speed difference you have mentioned is an interesting one that we haven't noticed yet but will certainly be looking out for.

Generally speaking, I always recommend customers to upgrade at least 1 version back for the PA server and keep note of the fix lists from later releases so that they can choose a version with known issues that they can live with. As opposed to a version with unknown issues.

In terms of the actual fixes everyone has suggested - thankyou for all the ideas and I am sure they will be useful to anyone else who stumbles across this. But we actually went a different route and are already implementing the fixes - I am sure there are hundreds of ways to work around this one.
The DBAs I am working with here came up with the idea of wrapping around something like below:
Expand ( 'begin DELETE FROM MyTable WHERE MyColumn like (''%MyVariable%'') ; EXCEPTION WHEN NO_DATA_FOUND THEN NULL;END;' );
But as I say there are many ways to work around this one if rolling back the upgrade is not an option.

Thanks,

Declan

------------------------------
Declan Rodger
Technical Director
Spitfire Analytics
------------------------------

Hi,
Normally we would use:

ODBCOutput ( 'MyDSN', Expand ( 'DELETE FROM %MyTable% WHERE (SELECT COUNT(*) FROM %MyTable% WHERE Version = ''XYZ'' ) > 0 AND Version = ''XYZ'';') );

Hope this helps.
Regards.

------------------------------
Jose Luis Senas
Data Analyst
Kimberly-Clark Mexico
Mexico City
------------------------------