Automic Workload Automation

  • Private Community
 View Only
Back to discussions
Expand all | Collapse all

DB2/Z: NO AUTHORIZED PROCEDURE NAMED SYSPROC.DSNWZP HAVING COMPATIBLE ARGUMENTS WAS FOUND. SQLCODE=-440, SQLSTATE=42884

  • 1.  DB2/Z: NO AUTHORIZED PROCEDURE NAMED SYSPROC.DSNWZP HAVING COMPATIBLE ARGUMENTS WAS FOUND. SQLCODE=-440, SQLSTATE=42884

    Broadcom Partner
    Posted Jan 26, 2023 06:45 AM
    Dear all

    Situation:
    We try to run Stored Procedures on DB2 Databases located on MVS - which should not matter, but who knows / see below ...

    Running SQL-statements - e.g. SELECT ... - runs fine, therefore we know that in general the situation is ok: SQL-Agent, CONN-Objects, DB2 user access, etc.

    But when running any StoredProcedure we always get the following message:

    2023-01-26 10:11:20 - U02000005 Job 'JOBS.TESTSP.SQL.NATIV' with RunID '1136009' is to be started.
    2023-01-26 10:11:21 - U02012001 Successfully established connection to 'Z033:9936/DBP9:currentSchema=UC4TEST;' with user 'g008653'.
    2023-01-26 10:11:21 DB2
    2023-01-26 10:11:21 DSN12015
    2023-01-26 10:11:21 - U02012014 JDBC Driver version: 4.32.28
    2023-01-26 10:11:21 call sysproc.dsnwzp(?)
    2023-01-26 10:11:21 DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=PROCEDURE;SYSPROC.DSNWZP, DRIVER=4.32.28
    2023-01-26 10:11:21 NO AUTHORIZED PROCEDURE NAMED SYSPROC.DSNWZP HAVING COMPATIBLE ARGUMENTS WAS FOUND. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.32.28
    2023-01-26 10:11:21 - U02004025 Job-script execution was aborted.
    2023-01-26 10:11:21 - U02012006 SQL Rollback executed

    (Of course) The same StoredProcedures do run correctly  using the same users when run by the db2-cli.

    When StoredProcedures are run on non-MVS-DB2 they also run fine. So we have no systematic problem.


    Did somebody come accross a similar problem?


    Btw: We did some experiments with the CONN-(JDBC-parameters)Database-AdvancedSettings during the problem analysis and had the impression, that they are just not taken into account.


     


    ------------------------------
    Stefan Lerch
    Senior Consultant
    at www.systempartners.ch​

    For AUTOMIC trainings please check https://www.systempartners.ch/en/homee/services/training/trainingautomic/
    ------------------------------


  • 2.  RE: DB2/Z: NO AUTHORIZED PROCEDURE NAMED SYSPROC.DSNWZP HAVING COMPATIBLE ARGUMENTS WAS FOUND. SQLCODE=-440, SQLSTATE=42884

    Broadcom Partner
    Posted Feb 14, 2023 08:38 AM

    Dear all

    Well, the outcome of our research was not really what we wanted to see: Calling StoreProcedures in Db2® for z/OS® by using any JDBC-connection has a very high probability to not work because Db2® for z/OS® does not support dynamic execution of the CALL statement. We found no way to make this work using "a specific JDBC-Parameter definition" within Automic's CALL-object ( together with the RA-DB-Agent).

    For more details please refer to https://www.ibm.com/docs/en/db2/11.5?topic=sql-calling-stored-procedures-in-jdbc-applications .

    A more technical approach is given in https://sourceforge.net/p/squirrel-sql/bugs/1457/

    Stefan



    ------------------------------
    Stefan Lerch
    Senior Consultant
    at www.systempartners.ch​

    For AUTOMIC trainings please check https://www.systempartners.ch/en/homee/services/training/trainingautomic/
    ------------------------------

    Original Message