DB2 plan for DSNTEP2 / DSNTEP4

Mainak_Dalal · New User Joined: 05 May 2010 Posts: 19 Location: USA

I am trying to understand how the batch Spufi programs DSNTEP2 / 4 can process any sql statement against any database schema in a DB2 subsystem. These are PL/I programs bound to DB2 plan DSNTEP2 (or any other name) to process SQL dynamically, when invoked through TSO batch program IKJEFT01 in a JCL.

Now, when a DB2 plan is created, it has a qualifier in Bind Plan command and the plan or any package bound to the plan can execute sql against the the objects within that schema / qualifier only.
But through DSNTEP program we can execute sql against any schema dynamically. I am not sure how the plan access is working here. Can someone please help understand the process here?

Thanks in advance

Akatsukami · Posted: Thu Jan 21, 2016 1:23 am

Binding indicates which of the FOO tables on the system the program should operate on; the subsystem and schema are specified at (package) bind time. If you do triple qualification of table names in the dynamic SQL, you can operate on any table (assuming, of course, that you have the appropriate privileges on it).

Mainak_Dalal · New User Joined: 05 May 2010 Posts: 19 Location: USA

Akatsukami, thanks for reply. What are FOO tables? Also what does qualifier signifies in BIND PLAN statement? Will the plan be able to access tables outside the schema / qualifier?

Also by triple qualification, do you mean DB.Schema.Table?

Akatsukami · Posted: Thu Jan 21, 2016 1:46 am

I was an avid reader of Ed Yourdon; he used FOO and BAR as examples for pretty much.

A package is bound to a given subsystem and schema. Since a plan can have more than one package in it, it can access many subsystems and schemata, although a given routine can only access a certain subsystem/schema combination at a time (accessing a different combination requires a rebind).

Triple qualification would be subsystem.schema.table. I use dynamic SQL in Rexx frequently; the tables are always specified that way.