Topic
2 replies Latest Post - ‏2012-01-10T09:59:44Z by Em_James
R.SchmitzSEBAB
R.SchmitzSEBAB
6 Posts
ACCEPTED ANSWER

Pinned topic Unable to execute SELECT DISTINCT ..... / DB2 SQL Error: SQLCODE=-805

‏2012-01-09T13:02:07Z |
Hello everybody.
When I try to submit some standard-queries for collected data, I get sometimes the following problem (examples):
----------------------------------------------------------------------------------------------------------------
Unable to execute SELECT DISTINCT APPLID,FUNCTION,TYPE,OBJECT FROM ZDB2TS1.CIU_CICS_DATA WHERE (PROGRAM='LFS1772' AND TRANSID='LF44')
com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-805, SQLSTATE=51002, SQLERRMC=DB2T.NULLID.SYSLH203.5359534C564C3031;DISTSERV;04, DRIVER=4.7.85
------------------------------------------------------------------------------------
Unable to execute SELECT DISTINCT APPLID FROM ZDB2TS1.CIU_RESOURCE WHERE (OBJECT='EPUAAA' AND TYPE='PROGRAM')
com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-805, SQLSTATE=51002, SQLERRMC=DB2T.NULLID.SYSLH203.5359534C564C3031;DISTSERV;04, DRIVER=4.7.85
---------------------------------------------------------------------------------------------------------------------------------------------
I use the standard-menu and choose the resources per context-menu (right mouse-key). The table CIU_CICS_DATA shouldn't have more than 100000 entries.
According to the information, available here: > http://ibm-mainframe-tips.blogspot.com/2009/09/sql0805n-on-db2-package-syslh203.html < it is necessary to bind more packages to allow for more statements open at the same time ....
Question:
Could that (missing packages) be the problem behind ? Could the Bind of additional packages be the solution ?
Or could that be a result of other problems ? Is that a common/known Explorer-problem ? Any ideas for a solution ?

Regards
R. Schmitz
Updated on 2012-01-10T09:59:44Z at 2012-01-10T09:59:44Z by Em_James
  • kevmeist
    kevmeist
    249 Posts
    ACCEPTED ANSWER

    Re: Unable to execute SELECT DISTINCT ..... / DB2 SQL Error: SQLCODE=-805

    ‏2012-01-09T13:08:28Z  in response to R.SchmitzSEBAB
    I've not done this with CICS Explorer but isn't this just a BIND issue?

    K
    • Em_James
      Em_James
      9 Posts
      ACCEPTED ANSWER

      Re: Unable to execute SELECT DISTINCT ..... / DB2 SQL Error: SQLCODE=-805

      ‏2012-01-10T09:59:44Z  in response to kevmeist
      Hi , -805 is indeed a BIND problem. I believe it is releated to the APAR PK69015. This describes a problem for -551 and -805 issues. I have copied some of the text here.

      If you are receiving -551 errors or the message that indicates
      that there are no IA tables then this can be caused by the
      user not having EXECUTE authority to the JDBC package
      NULLID.SYSLH200. There may also need other packages that you
      require access to. The solution to this is to GRANT EXECUTE
      authority to the JDBC driver package.

      The -805 errors can be caused if the DB2 Universal Driver (JCC)
      has not been bound in to the z/OS DB2 database. You will need
      to bind the DB2 Universal Driver (JCC) in to DB2.

      Solution.

      If you are receiving -551 errors or the message that indicates
      that there are no IA tables then this can be caused by the
      user not having EXECUTE authority to the JDBC package
      NULLID.SYSLH200. There may also need other packages that you
      require access to. The solution to this is to GRANT EXECUTE
      authority to the JDBC driver package. See the JCL below.

      If you are receiving -805 errors then these can be caused if the
      DB2 Universal Driver (JCC) has not been bound in the z/OS
      database. You will need to bind the DB2 Universal Driver (JCC)
      in to the required DB2 database.
      This can be done in 2 ways:

      1.
      Setup the DB2 Universal Driver (JCC) environment as per the
      README that is supplied with the DB2 Universal Driver (JCC)
      HFS. Then bind the driver from USS using:
      java com.ibm.db2.jcc.DB2Binder
      -url jdbc:db2://server_name:port_number/location_name
      -user YOUR_USERID_IN_CAPS
      -password YOUR_PASSWORD_IN_CAPS
      -action repl -size 20

      2.
      Extract the drivers from the CICSIA directory.
      In the ECLIPSE directory of the IA Explorer install and locate
      the PLUGINS directory. In the PLUGINS directory locate the file
      com.ibm.cics.ia.db2.runtime_2.2.0.jar. Copy this to a different
      location on your hard drive, then use a too to extract the
      contents. Locate the file DB2JCC.JAR in the contents of the
      extracted folder.
      Set up the USS environment variables to include the extracted
      DB2 Universal Driver (JCC) and then bind the driver from USS
      using:
      -url jdbc:db2://server_name:port_number/location_name
      -user YOUR_USERID_IN_CAPS
      -password YOUR_PASSWORD_IN_CAPS
      -action repl -size 20

      Then for CICS users we create a plan called DSNJCC and bind
      the packages etc into it using the following sample JCL:

      //JOBLIB DD DISP=SHR,DSN=SYS2.DB2.SDSNLOAD
      //BINDJDBC EXEC PGM=IKJEFT01,DYNAMNBR=20
      //DBRMLIB DD DISP=SHR,DSN=DSN.DBRMLIB.DATA
      //SYSTSPRT DD SYSOUT=*
      //SYSPRINT DD SYSOUT=*
      //SYSUDUMP DD SYSOUT=*
      //SYSTSIN DD *
      DSN SYSTEM(DB2)
      BIND PLAN(DSNJCC) -
      PKLIST( -
      NULLID., SQLJ1. )
      RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) -
      LIB('DSN.RUNLIB.LOAD')
      END
      //SYSIN DD *
      GRANT EXECUTE ON PLAN DSNJCC TO PUBLIC ;
      /*