Any query to a DB2 server must first go through a bind step. The bind step not only validates the query and parameters, but it also 'compiles' the query into the most efficient form (as the database engine sees it) given the data queried, indexes available, table statistics, etc, etc. The results of a bind (the final query executed internally) one day, may not be the same as the results of a bind on another day because contents of one or more tables may have grown (or shrunk) making it more efficient to execute the query differently now. This may not appear to make much sense if you're only querying one table, but if your joining multiple tables with sub-selects, it can have a real impact. The bind step is a fact of life with DB2. You either do it in advance or the DB2 server will do it for you internally when the query is executed.

There's significant overhead involved in the bind step. If you're making repetitive database operations, that overhead can be greatly reduced by binding the queries in advance. When the query is bound, DB2 stores the final compiled version of the query within its own environment and the program executes the stored, bound query directly, bypassing the internal bind step on every call. In compiled languages like COBOL, you submit a pre-compile step which scans the code for queries, binds them, then comments the original code replacing it with new calls to the bound queries. If the database statistics change significantly later, the bound queries may become inefficient again and will require re-binding. Rebinding existing stored queries happens automatically with a database re-org (so don't have to continually update your code, but you do have to bring the databases down for the re-org). If your queries are ad-hoc, one time operations, then there's nothing to be gained by binding in advance.

At least that's how binding in DB2 works (to my recollection) with compiled languages. I don't know how DBI and interpreted Perl handle the bind step. It may very well be that what you call a bind step through the DBI interface may have nothing to do with the DB2 bind step.

In reply to Re: Is bind useful for one-time queries and commands? by ruzam
in thread Is bind useful for one-time queries and commands? by talexb

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.