ODBC behavior with pre-bound packages - Forum - DataDirect Connect - Progress Community

ODBC behavior with pre-bound packages

 Forum

ODBC behavior with pre-bound packages

This question is not answered

Hi,
I'm trying to use your ODBC drivers against z/OS DB2 v10 ( and V12 in the future).

Having some dated experience with z/OS DB2 customers, the DB admins tend not to give applications bind access. The DB admins want to manually bind the packages and then just give the application execute permission on the packages.

So I uploaded the dbrms and jcl that you supplied and ran it, creating the packages with the different sorts of Isolation levels. However, when I ran the application that's using your drivers, it got bind errrors.

Setting plan A aside, I granted my users "create in" and bindadd authority. The first user to use the drivers worked OK, but then the 2nd user generated an exception trying to free the package

I ran across this article in your knowledgebase from a couple yearsback on this issue:
knowledgebase.progress.com/.../3017

This seems like a bug. At the least, your drivers should support access from multiple users.

And getting back to my initial assertion, you drivers shouldn't need to bind their self to the database if they are are already bound.

Questions:
1) Is there some sort of configuration process I'm missing that would allow the drivers not to attempt to bind theirself.

2) If #1 isn't currently possible, is there some sort of configuration action available that will stop the drivers from trying to free packages? Concurrent access by multiple users is a must!

All Replies
  • John,

    The behavior you describe here certainly sounds unexpected. By default, the job you ran should have created packages in the NULLID schema and then granted permissions on them to PUBLIC. Are all your users in the PUBLIC schema?

    Upon connecting to the server, the driver issues a query against the SYSIBM.SYSPACKAGE table to check for the existence of the packages and if they're not found, it will try to create them. Do your users have SELECT privileges on this table? If the query against the SYSPACKAGE table fails, then the driver will assume the packages do not exist and try to create them. This might explain why you are seeing the driver try to create the required packages for every user that connects.

    It is not expected for you to grant BINDADD and CREATE IN to your individual users. Package creation is something of an expensive process, so the expectation is that it is done once and then the packages are simply available for the drivers to utilize.

    Please check on the items listed above and let me know what you find.

  • odbctrace01.out

    Hi,
    Thanks for the information. I was not previously setting the users up with select access against sysibm.syspackage. I was not aware it was a requirement.

    Your question: "Are all your users in the PUBLIC schema?" - I'm not sure what you are trying to distinguish. (That I know of) If a privilege doesn't exist for a specific user, then that user would pick up any privileges granted to public.

    However, I cleaned up, bind the packages with the job, granted execute against the packages and select against sysibm.syspackage. I still get the bind auth error

    I collected trace, here is the file
    (See attached file: odbctrace01.out)

    Here are the details of what I did:

    To start clean:

    • I removed all of the privileges I had previously granted for the users, dropped the packages.
    • Then, I re-ran the BIND job, binding the packages and granting execute to public. (results below)
                            G                    Package                      H B E C
      Sel Grantor  Grantee  T Collection         Name     Grant Timestamp     G D X O
          JGOOD*   *        * *                  *        *                   * * * *
      --- -------- -------- - ------------------ -------- ------------------- - - - -
          JGOOD    PUBLIC     NULLID             DDOC510A 2018-07-13-20.32.48     Y  
          JGOOD    PUBLIC     NULLID             DDOC510B 2018-07-13-20.32.48     Y  
          JGOOD    PUBLIC     NULLID             DDOC510C 2018-07-13-20.32.48     Y  
          JGOOD    PUBLIC     NULLID             DDOU510A 2018-07-13-20.32.48     Y  
          JGOOD    PUBLIC     NULLID             DDOU510B 2018-07-13-20.32.48     Y  
          JGOOD    PUBLIC     NULLID             DDOU510C 2018-07-13-20.32.48     Y  
          JGOOD    PUBLIC     NULLID             DDOR510A 2018-07-13-20.32.48     Y  
          JGOOD    PUBLIC     NULLID             DDOR510B 2018-07-13-20.32.48     Y  
          JGOOD    PUBLIC     NULLID             DDOR510C 2018-07-13-20.32.48     Y  
          JGOOD    PUBLIC     NULLID             DDOS510A 2018-07-13-20.32.48     Y  
          JGOOD    PUBLIC     NULLID             DDOS510B 2018-07-13-20.32.48     Y  
          JGOOD    PUBLIC     NULLID             DDOS510C 2018-07-13-20.32.48     Y  
          JGOOD    PUBLIC     NULLID             DDON510A 2018-07-13-20.32.48     Y  
          JGOOD    PUBLIC     NULLID             DDON510B 2018-07-13-20.32.48     Y  
          JGOOD    PUBLIC     NULLID             DDON510C 2018-07-13-20.32.48     Y  

    • Then, I granted select against syspackage to public
      odb2 "grant select on SYSIBM.SYSPACKAGE to public"
    • I used a jupyter notebook to verify that the same user that uses the odbc access can select * from sysibm.syspackage
    • Then from a linux system, I setup ODBC trace:
      [jgood@localhost(192.168.164.223) ~/src/example]$ grep -i trace /opt/SDAP712/odbc.ini
      Trace=1
      TraceFile=/home/jgood/src/example/odbctrace.out
      TraceDll=/opt/SDAP712/lib/XEtrc27.so
      TraceOptions=1
    •  - and ran an modified version of example.c that just runs one SQL request

    ./simple 'DB2 Wire Protocol' zmml01  db2au01 'select count(*) from mlz.churn_cust_sum'

    ./simple DataDirect Technologies, Inc. ODBC Example Application.
    connecting to datasource: 'DB2 Wire Protocol', uid: 'zmml01', password: 'db2au01', opt1: '', opt2: ''
    SQLSTATE = S1000
    NATIVE ERROR = -567
    MSG = [IBM SPSS][ODBC DB2 Wire Protocol driver][DB2]AUTHORIZATION ERROR BIND ZMML01.  DSNXBAC.NULLID.DDOS510A.(01) BINDADD

    SQLConnect: Retrying Connect.
    SQLSTATE = S1000
    NATIVE ERROR = -567
    MSG = [IBM SPSS][ODBC DB2 Wire Protocol driver][DB2]AUTHORIZATION ERROR BIND ZMML01.  DSNXBAC.NULLID.DDOS510A.(01) BINDADD

    SQLConnect: Retrying Connect.
    SQLSTATE = S1000
    NATIVE ERROR = -567
    MSG = [IBM SPSS][ODBC DB2 Wire Protocol driver][DB2]AUTHORIZATION ERROR BIND ZMML01.  DSNXBAC.NULLID.DDOS510A.(01) BINDADD

    SQLConnect: Retrying Connect.

    Regards,

    John Goodyear
    z Systems Analytics zChampion
    WSC z Systems Applied Technologies
    Herndon, VA


     "Brian Derwart" ---07/13/2018 09:54:44 AM---Update from Progress Community [INVALID URI REMOVED

  • John,

    Thanks for the details of your troubleshooting efforts. You can disregard my question about the PUBLIC schema as I was just trying to make sure you hadn't done anything out of the ordinary like revoke user privileges to PUBLIC.

    Since the driver's verification of the existence of packages is an internal process, could you please create a network packet trace for me to examine? To enable the network packet logging, set EnablePacketLogging=1 in the odbc.ini file within the data source definition that you've configured for the Progress DB2 driver. Please also send the data source definition from that file to me so I can check all the other settings you're using and determine the cause of this issue.

    The network packet log will show me the response from the DB2 server during the driver's check for the packages which should point us to the exact reason for this failure.

    Regards,

    Brian

  • odbc-trace2.zip

    Hi,
    I enabled packet logging for the datasource definition (DB2 Wire Protocol) in the odbc.ini file and re-ran the sample application.

    Included in the zip file is the odbc.ini file, the trace output, and the packet trace.



    (See attached file: odbc-trace2.zip)


    Regards,

    John Goodyear
    z Systems Analytics zChampion
    WSC z Systems Applied Technologies
    Herndon, VA


     "Brian Derwart" ---07/15/2018 01:21:03 PM---Update from Progress Community [INVALID URI REMOVED

  • John,

    I apologized, but I led you slightly astray in a previous comment I made. If you look in the packet logs you provided, you can see that the driver attempts to select from SYSIBM.SYSPACKSTMT in order to validate the existence of the packages rather than the SYSIBM.SYSPACKAGE table. The reply from the server indicates a permissions error on this system table.

    You can see in the reply starting on line 93 of the file pkt20180715230101951_01.out that the SQL Code returned is -551 (Hex: FF FF FD D9) with SQLState 42501.

    Please grant SELECT permissions to your user on the SYSIBM.SYSPACKSTMT table and let me know if it resolves this issue.

    Thanks,

    Brian

  • Hi.
    I revoked access to SYSPACKAGE and granted select for
    SYSPACKSTMT.

    Running the test program, I'm able to run the query without any errors, and it runs quickly with no delays as before when the user was granted bind-add/create in and the packages were getting rebound on every call. I check of the timestamp on the packages confirms that they were not rebound.

    I then dropped the packages, re-ran the test app which failed due to bind permission (expected).


    Then I ran /opt/SDAP712/bin/bind27 'DB2 Wire Protocol' which successfully rebound the packages
    . After that the test app worked.

    So in addition to confirming the bind JCL and the bind27 utility bind the packages in a manner suitable for running the test application, (seems) to confirm that select access to
    SYSPACKSTMT for the connecting user is sufficient . I'm going to have to run this on a different system to make sure I didn't miss any permissions.

    Documentation
    On the topic of documentation, I curious where the
    SYSPACKSTMT access requirement is document, or should be documented. I couldn't find it in your doc.


    Security
    Thinking in terms of security, I expect that some DBADMs would not want to grant users access to
    SYSPACKSTMT. It could be considered a security vulnerability as it gives the users access to all of the statements in the packages bound in the database. Do you know if this has ever been a concern?

    Has development considered just trying open the package, and if it fails, confirm via the error code that the package doesn't exist? Having said that, I'm not sure what the IBM CLI packages did...


    Regards,

    John Goodyear
    z Systems Analytics zChampion
    WSC z Systems Applied Technologies
    Herndon, VA


     "Brian Derwart" ---07/16/2018 02:40:30 PM---Update from Progress Community [INVALID URI REMOVED