MVSFORUMS.com

[haqshaik]

Hi,
I have never used dynamic sql's in the application programs. When we write a embebbed dynamic sql, in cobol what are the bind pararameters that we need to set? I am aware of that REOPT(VARS) will be used for dynamic SQL's. It would be great if some one can let me know the bind card for compilation. While runnig the pgm with static SQL, assosication of the pgm name with the plan name is required. In case of dynamic sql, how do we run the progrm?

Thanks,
Salauddin

[CZerfas]

The program preparation process (precompile, compile, link, bind) is exactly the same for static or dynamic SQL. The same is true for execution, since you have to associate a plan name with the name of the first module of your application.

If you use CAF, the picture is different (but using CAF means, that you have to code many of the internals by yourself, so better think if you need the extended flexibility).

The difference between static and dynamic SQL is completely within your coding. You have to code statements for doing a "mini-bind" for the SQL statement put together during program execution (i.e. issue a PREPARE). The main difference is the usage of working storage (to use COBOL terminology).

Since with static SQL the structure of the data you fetch from DB2 is known to you, you simply issue a FETCH into host variables defined via "normal" coding. With true dynamic SQL you don't know the SQL statement during coding time, and therefor the storage areas within your program, whitch will receive the data fetched from DB2, have to be dynamically allocated during run-time by your program using the SQLDA structure.

That is why I always hesitate to use true dynamic SQL, since the coding for the allocation of these extra data areas is a nuisance.

By the way: REOPT(VARS) is intended for static SQL with host variables as operands in your WHERE clause. REOPT(VARS) means, that for every execution of a static SQL statement with host variables the access path of the statement to the data is checked by DB2. That gives you potentially dramatic improved access paths, but you pay the extra execution overhead during runtime.

regards
Christian

[haqshaik]

Thanks Christine. I am aware of the usage of SQLDA. I could not find in the manuals so far i have looked the preparation process. So i was bit confused.Thanks for correcting me.

Thanks,
Salauddin