Friday, December 28, 2012

DB2 for z/OS: Clearing Up a Native SQL Procedure Misconception

A few days ago I received a question that I believe reflects a rather widely held misconception about DB2 for z/OS native SQL procedures. In the interest of clearing the air, I'll provide in this entry the information that I communicated to my question-asking friend.

Native SQL procedures were introduced with DB2 9 for z/OS in new-function mode (or with DB2 10 in new-function mode, for those organizations that migrated or are migrating to DB2 10 from DB2 V8). Native SQL procedures changed the DB2 for z/OS stored procedure game in a big way, as I pointed out in an entry posted several years ago to the blog I maintained while working as an independent DB2 consultant. One of the important differences between a native SQL procedure and an external stored procedure (such as a COBOL stored procedure) is what I'd call the execution locale. Whereas an external stored procedure executes at least partially in a WLM-managed stored procedure address space (and I'll explain that "partially" in a moment), a native SQL procedure executes entirely in the DB2 database services address space (also known as DBM1). That difference prompted the question to which I referred in my opening: to what address space is the CPU time associated with execution of a native SQL procedure charged?

The person who asked this question was thinking that because a native SQL procedure executes in the DB2 DBM1 address space, the CPU cost of executing a native SQL procedure would show up in the CPU consumption of DBM1. While I can see how one could arrive at that conclusion, it is not correct. The CPU cost of executing a native SQL procedure is in fact charged to the address space through which the stored procedure call came to DBM1; so, if a native SQL procedure is called by a network-attached DRDA requester, the CPU cost of executing the native SQL procedure will be charged to the DB2 DDF address space. If the caller is a CICS transaction program, the CPU cost of executing a called native SQL procedure will be charged to the CICS region in which the transaction executes.

If you think about it, this makes sense. There are two key factors to keep in mind here. First, though a native SQL procedure executes in the DB2 database services address space, it executes under the task of the application process that issued the stored procedure call (e.g., a DDF enclave SRB or a CICS subtask TCB or a batch address space's TCB). This is true of any SQL statement: the statement executes in DBM1, but under the task of the statement-issuing process, and associated CPU consumption is charged back to the address space through which the statement came to DBM1. [There is a slight exception to this rule: if a SQL statement is issued by an external stored procedure, the CPU cost of executing the statement will be charged to the address space through which the stored procedure call came to DBM1, as opposed to being charged to the WLM-managed address space in which the SQL-issuing stored procedure runs.]

The second key factor is this: a native SQL procedure is comprised wholly of SQL statements. Some of these statements will likely be data manipulation statements (e.g., SELECT, INSERT, DELETE, UPDATE), and some will likely be "logic" statements (e.g., LOOP, IF, WHILE), but it's all SQL. Yes, a native SQL procedure executes in the DBM1 address space, but SQL statements ALWAYS execute in the DBM1 address space. That's why I stated earlier that an external DB2 stored procedure generally executes "partially" in a WLM-managed stored procedure address space: if that external DB2 stored procedure issues SQL statements (and a stored procedure program doesn't have to issue SQL statements), those SQL statements will execute -- as is true of all SQL statements -- in the DB2 DBM1 address space; so, it could be said that the external stored procedure will execute partially in its assigned WLM-managed stored procedure address space (for execution of program code other than SQL statements) and partially in the DB2 DBM1 address space (for execution of SQL statements issued by the stored procedure program). A native SQL procedure executes entirely in DBM1 because it is comprised entirely of SQL statements. And where does the cost of SQL statement execution show up? In the address space through which the SQL statements come to DBM1 (with the aforementioned slight exception related to external stored procedures). And that's why the CPU cost of native SQL procedure execution gets charged to the address space through which the associated CALL statement comes to DB2.

I hope that this information will be useful to you. Best wishes to all for a successful 2013.

No comments:

Post a Comment