Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2013

    Unanswered: Stored Procedure bind


    I am using DB2 UDB v9.7 as database and front end language as Java. I have written SQL stored procedures which are called from Java.

    I wanted to know, when these stored procedures get bound i.e. when I create them on DB2 UDB or when Java code is compiled?

    In Mainframe, I know that there is precompile and bind steps but not sure how this works when we are using db2 udb and java.

    Hope I make myself clear.

    Would appreciate inputs on this.


  2. #2
    Join Date
    May 2003
    The package for the stored procedure is bound when the SP is created. The package can be explicitly rebound, or an automatic rebind can occur if one of the dependent object in the package (such as an index) is dropped and the package is marked as "invalid." But the automatic rebind does not occur until the first time it is executed with package marked as invalid. If the automatic rebind fails for some reason, the package is marked as inoperative, and only an explicit rebind can fix it.

    Dynamic SQL statements within a SQL stored procedure are complied at execution time, just like java statements. Java is always dynamic SQL, even though there is package cache available which can eliminate the need for a recompile of the SQL (if a dynamic SQL statement is submitted that is exactly identical to one already in package cache).

    IBM has dropped the term UDB. It is called DB2 LUW (Linux, UNIX, Windows).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2013
    Thank you for your reply.

    Could you please let me know that is there any way to check that package for stored procedure has been bound i.e any table having that information or some bind file?

  4. #4
    Join Date
    Apr 2012
    Provided Answers: 25
    The easiest way to verify that the stored procedure is bound is to run the already compiled sproc, but if that is impractical then you can query the db2 catalog with SQL to check.

    There are several possible catalog queries that can help.

    If you have for example only 50 stored-procedures in schema XYZ then you would expect 50 valid packages in syscat.packages for pkgschema XYZ. So a simple count on syscat.packages for pkgschema='XYZ' may suffice. You can also check the number of invalid packages (syscat.packages.valid='Y') is what you expect (e.g. zero) for your schema.

    But sometimes you need to know the package-name corresponding to your specific stored procedure name - then you need to join between a few catalog views.

    The query below reports the package-name (in syscat.packages) that corresponds to SQL-procedure %schema%.%sproc% and this query assumes you don't use mixed-case schema-names, this query is OK for DB2 versions 9 and higher.

    CAST(bname AS CHAR(40)) AS packagename
    syscat.procedures ,
    syscat.procedures.specificname=syscat.routinedep.r outinename
    AND syscat.procedures.procschema=UPPER('%schema%')
    AND syscat.procedures.procname =UPPER('%sproc%')
    AND syscat.routinedep.btype='K'
    AND syscat.procedures.valid='Y' -- remove this to show invalid packages
    AND syscat.packages.pkgschema = UPPER('%schema%')
    AND syscat.packages.pkgname = syscat.routinedep.bname
    AND syscat.packages.valid='Y' WITH ur

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts