MVSFORUMS.com

[Manas Biswal]

Hi,

I have a sort of silly question maybe but it has bothered me for quite a while so I will go with it. I was just wondering about the logical need of a plan in DB2. Let us consider tools like SPUFI or QMF. We specify queries there and they are executed without preparing any sort of a plan for them. Someone told me that system plans are used by those tools and the queries are bound to those plans on the fly. Is it the case?. If it is so, can anyone please provide me with a link detailing all that.
I was wondering if plans are logically needed by DB2 (Apart from the Query Optimization part). Queries written in a DB2 application program can be passed to the DB2 run time supervisor (This can be done at the pre-compilation time) and then at run time the DBMS can directly parse the queries and execute them. Is there an absolute necessity of binding to a plan apart from query optimization.
I have dabbled a little in ASP/MYSQL/MS-ACCESS. There we don't bind queries to any plan.(I am not aware of any system level plans, if used though). The queries are parsed and run on the fly by the DBMS.
Considering this, Is it logically necessary to have the plan stuff in DB2?.
Hope I am speaking sense.
Please get back to me in case of any clarifications.
Regards,
Manas

[kolusu]

Manas,

SQL statements submitted to SPUFI (SQL processor using file input). SPUFI prepares and executes these statements dynamically.

To access DB2 data, an SQL statement requires an access path.DB2 determines the access path for a statement at either of these times:

• When you bind the plan or package that contains the SQL statement
  
• When the SQL statement executes
  

For dynamic SQL, such as statements issued through SPUFI, DB2 determines the access path when the statement executes. For statements that do not execute often, this method is usually acceptable. However, an application typically runs the same SQL statements repeatedly. In this case, determining the access path at run time wastes system resources, because the system must determine the same path repeatedly. To conserve your system resources, use static SQL. Access paths for static SQL are determined only at BIND or REBIND time.

DB2 comes with a set of programs to process dynamic SQL which are used as a productivity aid. They are DSNTEP2,DSNTIAD,DSNTIAUL,SPUFI. QMF is also similar tool but it is not free. It is add-on product.

Hope this helps...

cheers

kolusu

[Manas Biswal]

Hi Kolusu,

Thanks for your reply. I of course know the need of bind of static SQLs in an application program.
Actually I heard from someone that in case of SQLs processed by SPUFI or QMF, there are certain system level plans that are used on the fly. I would like some information about the same. In case of dynamic SQLs also which are used in an application program, the SQLs are bound to certain system plans in the PREPARE statement. I would just like confirmation and some information about these system level plans.

Regards,
Manas