Friday, November 30, 2018

Db2 for z/OS Global Variables: What is a "Session"?

Has this ever happened to you? You're looking at the Db2 for z/OS SQL Reference (or in the Db2 for z/OS Knowledge Center on the Web), and you see something like this (and the quote below is from the description of the CREATE VARIABLE statement in the SQL Reference, with highlighting added by me):

Global variables have a session scope. Although they are available for use to all sessions that are active at the current server, the value of the global variable is private for each session.

You think to yourself, "OK. That's good to know." And then, "But wait - what's a 'session'?" You scour the Db2 documentation for the definition of "session", and you come up empty. You're left scratching your head. "Is a 'session' a thread? A transaction? Something else?" I am writing today to remove this particular cause of head-scratching (your scalp can thank me later). I will start by giving you the short answer, and then I'll elaborate.

Short answer: the meaning of "session", in a Db2 for z/OS context, depends on the nature of an application's connection to a Db2 server. For "local-to-Db2" applications (meaning, applications that execute in the same z/OS LPAR as the target Db2 system, and are not network-connected to Db2 - examples include CICS transactions and batch jobs), "session" equates to "thread" (referring to the Db2 thread used by the application). For DRDA requesters (applications that access Db2 for z/OS via the Db2 DDF address space, using the DRDA protocol by way of an IBM driver such as the IBM Data Server Driver or Db2 Connect), "session" equates to the logical connection an application has established with the Db2 server. For a REST client (an application interacting with a Db2 system via the REST interface to the DDF address space), "session" equates to transaction.

You may wonder, "Why these differences between local-to-Db2, DRDA requesters and REST clients, with regard to the Db2 meaning of 'session'?" Read on for explanatory information.

Local-to-Db2 applications

Let's start with an easy case: a Db2-accessing batch job (and I'm not talking about a Java batch job that could use a type 4 JDBC driver and therefore be network-connected to Db2, from Db2's perspective). This job will get a Db2 thread at the first issuance of a SQL statement, and that thread will persist until end-of-job. The job might issue multiple COMMITs, but it's always the same thread that's being used for the life of the job. If the batch job involves use of a Db2 global variable, the initial value of the global variable will be its default value (for a user-created Db2 global variable, the default value will be null unless a different default was specified when the variable was created). If the job sets the value of the global variable to X, that will be the global variable's value for the duration of the job, unless it is subsequently changed by the job to some other value. If another, concurrently executing batch job sets the same global variable to value Y, the other batch job sees Y in the global variable, not X, because the two batch jobs are associated with two different Db2 sessions. Pretty simple.

How about a CICS or an IMS transaction? Still simple, absent thread reuse (I'll get to the thread reuse situation momentarily). The transaction, like a batch job, gets a Db2 thread at the first issuance of a SQL statement, and the thread persists until end-of-transaction. If the transaction involves use of a Db2 global variable, the global variable will initially have its default value. If the transaction sets the global variable to some value, that value will persist, unless subsequently changed by the transaction, until the transaction completes processing and its Db2 thread is deallocated. If two concurrently executing transactions set the value of the same Db2 global variable to X and Y, respectively, the one transaction will see the value X in the global variable and the other transaction will see the value Y in the global variable, because the two transactions are associated with two different Db2 sessions. Again, pretty simple.

Things get a little more interesting in the case of thread reuse by CICS or IMS transactions. Thread reuse is good for transaction CPU efficiency (especially when paired with Db2 packages bound with RELEASE(DEALLOCATE)), but it changes the effective meaning of "session" in a Db2 for z/OS context. Why? Because absent thread reuse, there is a one-to-one correspondence between transactions and Db2 threads. In a transactional thread reuse situation, there is a many-to-one relationship between transactions and threads - in other words, multiple transactions reuse the same CICS-Db2 or IMS-Db2 thread (that's the point of thread reuse). What does this mean for a given transaction program? Well, let's say that one transaction sets the value of Db2 global variable GVAR to X. The transaction completes, and another transaction reuses the same Db2 thread. With regard to that second transaction, what will be the initial value of the Db2 global variable GVAR? Will it be GVAR's default value? NO - it will be X, the value placed in GVAR by the transaction that previously used the thread. Why is this so? Because the scope of a Db2 global variable is a session, and for a local-to-Db2 application, "session" equates to "thread", and in a thread reuse situation multiple transactions will use a particular thread. The moral to this story: if you have a transaction program that puts sensitive information in a Db2 global variable, and this is (or could be) a thread reuse situation, you'd better make sure that you re-set the global variable to its default value before end-of-transaction; otherwise, a transaction that subsequently uses the same thread will be able to see the sensitive information your transaction placed in the global variable.

DRDA requesters

This situation gets really interesting, because the whole application-thread relationship is quite a bit different versus the CICS-Db2 or IMS-Db2 case. A DRDA client application will establish a connection to a Db2 for z/OS server, and very typically the application will stay connected to the Db2 system for a considerable period of time (and it may establish several connections to the Db2 server). Getting more specific, it is the "logical connection" that matters here - the connection that the application perceives that it has with the Db2 server. I say "logical connection" because physical connectivity to the Db2 server involves things like "transports" provided by the IBM Data Server Driver (or Db2 Connect) - something that makes connection pooling possible through the servicing of n logical connections to a Db2 server with fewer-than-n physical connections (connection pooling is good for client-server application scalability and efficiency).

How about threads? Well, a given logical connection between a DRDA client application will likely involve use of any number of Db2 threads. How so? Consider the "regular" DBAT case (DBATs - database access threads - are the kind used for applications accessing Db2 via the DDF address space, and by "regular" I mean DBATs that are not of the high-performance variety). A DRDA client application has established a logical connection to a Db2 server, and a transaction uses that connection. To service the transaction, Db2 takes the application's logical connection from the inactive state (its state when not being used by a transaction) to the active state, and assigns to the now-active connection a DBAT from the DBAT pool. The transaction completes, the DBAT is separated from the connection and goes back to the DBAT pool, and the connection goes back to the inactive state until it's needed for another transaction.

OK, how about the high-performance DBAT case? A high-performance DBAT is instantiated when a Db2 package bound with RELEASE(DEALLOCATE) is allocated to a "regular" DBAT for execution (in other words, the "regular" DBAT becomes a high-performance DBAT in that situation). Once instantiated, the high-performance DBAT will NOT go back into the DBAT pool at end-of-transaction; instead, it will remain dedicated to the connection through which it was instantiated (a connection identified with an application server IP address and an ephemeral port), and will be reused, up to 200 times, by transactions associated with the connection (thus, high-performance DBATs deliver performance benefits similar to those provided by CICS-Db2 protected entry threads and threads between IMS wait-for-input (WFI) and pseudo-WFI regions and a Db2 system).

In a Db2 data sharing system, in which something called Sysplex workload balancing is active (and it's active by default with the IBM Data Server Driver and Db2 Connect), the situation gets even more interesting: a DRDA client application establishes a connection to the Db2 data sharing group (at least it should, versus connecting to an individual group member), and different transactions associated with the same logical connection to the Db2 system end up executing on different members of the data sharing group, using DBATs (regular and/or high-performance) provided by the individual member Db2 subsystems.

I've provided, above, "behind the curtain" information on the workings of Db2 for z/OS as a DRDA server, and now I'll tell you that all this DBAT stuff is essentially irrelevant to the meaning of "session" in a DRDA requester context. The stuff that Db2 for z/OS does with regular and high-performance DBATs, and with active and inactive connections, is invisible to a DRDA client application. It's kind of like the difference between logical and physical database design: a Db2 table looks the same to an application program, regardless of whether it's in a traditional segmented or a universal partition-by-growth or a range-partitioned (universal or otherwise) table space. That's Db2 plumbing. Because a given logical connection to a Db2 server is what it is from the DRDA client application perspective, regardless of the number and/or type of DBATs used to service the transactions associated with the connection, it absolutely would not make sense to equate "session" with "thread" in this case. If a DRDA transaction using a logical connection to a Db2 server sets the value of Db2 global variable GVAR to X, that value will remain X (unless subsequently changed) for the life of the logical connection. In that sense, the situation is similar to the one described above for CICS-Db2 or IMS-Db2 thread reuse: if you do not want the value placed in a global variable by a DRDA transaction to be seen by a subsequent transaction associated with the same logical connection to the Db2 system, reset the value of the global variable before end-of-transaction.

If two transactions associated with two different logical connections between a DRDA client application (or applications) and a Db2 system place the values X and Y, respectively, in the same Db2 global variable, the one transaction will see X in the global variable and the other will see Y, because the scope of a global variable is a session, and two different logical connections to a Db2 server system are two different sessions.

REST clients

This one's pretty easy: for a client transaction using Db2's native REST interface, "session" equates to transaction. Period. Why so comparatively simple? Because REST is a so-called "stateless" model. Using the REST interface, each and every interaction with Db2 gets a "logical fresh start" on the Db2 side, and that means, among other things, automatic resetting of any global variable set by a REST-using transaction back to its default value at end-of-transaction. If Db2 global variable GVAR is set to X by a REST-using transaction, a subsequent REST-using transaction will not see X in GVAR at transaction start time - it will see GVAR's default value. If two different, concurrently executing REST-using transactions put X and Y, respectively, in the same Db2 global variable, the one transaction will see X in the global variable and the other will see Y, because the scope of a global variable is a session, and each and every REST-using transaction is associated with a different Db2 session.

In summary...

Keep the simple answer to the Db2 session question in mind:
  • For local-to-Db2, non-DDF-using applications: session = thread
  • For DRDA client applications: session = logical connection to Db2 server
  • For REST clients: session = transaction
I hope that this information will be helpful to you, and that you can take "Db2 session" off of your head-scratcher list.


  1. I was scratching my head. Thanks for explaining this.

  2. What about TCPIP protocol bassed connection? If two users login in a web app and set some value in that variable like User A set 'a' and B set 'b', will that variable hold two different values for individual user session? Or will 'a' be replaced with 'b' if B login just after A.

    1. The sections of the blog entry under the headings, "DRDA requesters" and "REST clients" both pertain to client applications that have (typically) a TCP/IP connection to the Db2 for z/OS server. DRDA - distributed relational database architecture - is Db2's distributed database protocol, and the underlying communications protocol is usually TCP/IP. REST clients are also expected to be TCP/IP-connected to a Db2 for z/OS server.

      With regard to your question, if the two users are executing transactions that themselves use two different logical connections from the application to the Db2 for z/OS server, and both transactions reference the same Db2 global variable in a SQL statement, each transaction will get its own instance of the global variable, and user A will see the 'a' that he put into his instance of the global variable, and user B will see the 'b' that he put into his instance of the global variable.

      If, on the other hand, user A executes his transaction and then user B executes his transaction, and the latter utilizes the same logical connection from the application to the Db2 server that had been used for A's transaction, if B in his transaction looks at the content of the global variable before putting anything into it, he will see the 'a' put there by A's transaction, if 1) user A did not reset the value of the global variable at end-of-transaction, and 2) if B's transaction is the first one to re-use the logical connection that A's transaction used (or if an intervening transaction did not change the global variable's value, or also put 'a' into it). The life of the global variable is the life of the logical connection (between the application and Db2) through which it was instantiated (it will be instantiated for the connection when first referenced in a Db2-processed SQL statement issued via the connection).


  3. If I am using OLEDB connection, is con.Open() creates a session and con.Close() close that session? Is variable value is available only in between open and close connection?

    1. I'm not an OLE DB expert, but if con.Open would be used to establish a logical connection between the application and a Db2 for z/OS server, and if con.Close were to close that logical connection, a Db2 global variable instantiated within the logical connection would persist through to the end of the connection, and would go away at the close of the connection.


  4. Could you please tell the correct answer from given options?

    There are two independent DB2 subsystems, SB1 and SB2. Global variable GV1 exists on SB1 and GV2 exists on SB2. Application program AP1 starts its execution on SB1 using GV1.

    Which statement is the true for the value of GV1? in below two scenarios

    A.AP1 connects to SB2 using a three-part table name.
    B.AP1 connects to SB2 using statement CONNECT to SB2

    1. Here is what I think you are asking:

      "If there is a Db2 global variable called GV1 defined on the Db2 for z/OS subsystem named SB1, and my application process connects to a different Db2 for z/OS subsystem called SB2 - either by way of a 3-part object name with SB2's location as the high-level qualifier, or via an explicitly requested connection to SB2 - then can my application process access a value in GV1 (or place a value in GV1) while the process is connected to SB2?"

      Assuming I have correctly phrased your question, the answer is, "No." To use a global variable defined on Db2 subsystem SB1 your application needs to be currently connected to SB1.

      Hope this helps.