Planning Analytics

 View Only
  • 1.  2.0.9.14 - ODBCOutput Errors

    IBM Champion
    Posted Tue August 02, 2022 05:11 PM

    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


  • 2.  RE: 2.0.9.14 - ODBCOutput Errors
    Best Answer

    Posted Wed August 03, 2022 05:33 AM

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



  • 3.  RE: 2.0.9.14 - ODBCOutput Errors

    Posted Wed August 03, 2022 09:04 AM
    You can add a :   SELECT TOP 1 * FROM [MyTable] WHERE Version = ''XYZ''; IF @@ROWCOUNT > 0 ; DELETE FROM [MyTable] WHERE Version = ''XYZ'';

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



  • 4.  RE: 2.0.9.14 - ODBCOutput Errors

    IBM Champion
    Posted Wed August 03, 2022 09:09 AM
    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
    ------------------------------



  • 5.  RE: 2.0.9.14 - ODBCOutput Errors

    IBM Champion
    Posted Wed August 03, 2022 09:09 AM
    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
    ------------------------------



  • 6.  RE: 2.0.9.14 - ODBCOutput Errors

    IBM Champion
    Posted Wed August 03, 2022 09:27 AM

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



  • 7.  RE: 2.0.9.14 - ODBCOutput Errors

    Posted Wed August 03, 2022 10:13 AM
    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
    ------------------------------