This month I wish to delve down into the depths of threads. Going into as much detail as I think easily possible! Many thanks to one of my esteemed readers who wished this as a sort of Db2 Thread 101 boot camp style thing!
Remember: if you ever have a wish about some detail or topic you would like more data about – just drop me an e-mail and I will, most probably, add it to my list of things to do… These lists never ever get shorter, do they?
What is a Thread?
To start with, there are two types of thread…
- Allied threads
- Database access threads (DBATs)
Allied threads mean subsystems like TSO, Batch, IMS, CICS, CAF and RRSAF, and for the DBATs, these are remote access requests.
How does Db2 handle it?
It goes through a list of checks to see if it has enough scope for the request. Checks here are against various ZPARMs. Starting with the biggies:
CTHREAD (Default 200 Range 1 – 20000) Limit the number of allied threads.
MAXDBAT (Default 200 Range 0 – 19999) Limit the number of database access threads. Setting this to zero basically “hides” this subsystem from the outside world.
The sum of these two cannot exceed 20000.
If in data sharing you also get the bonus of queueing client connections:
MAXCONQN (Default OFF, Values: OFF, ON, 1 – 19999) If the number of in-use DBATs reached the MAXDBAT value then how many to allow waiting in a queue. If the number of “waiters” exceeds this value, then the oldest will simply be closed.
MAXCONQW (Default OFF, Values: OFF, ON, 5 – 3600) The time duration in seconds that a client connection waits before the client connection is closed.
For TSO and CAF you use:
IDFORE (Default 50 Range 1 – 20000) Limits the number of threads that are from Db2 TSO Foreground. This includes DB2I, QMF and any foreground applications.
IDBACK (Default 50 Range 1 – 20000) Limits the number of threads from batch. This includes batch jobs and utilities.
These two ZPARMS limit the number of threads indirectly.
For inbound DDF connections we have:
CONDBAT (Default 10000 Range 0 – 150000) Limits the maximum number of concurrent inbound DDF connections. It must be greater than or equal to MAXDBAT.
Finally, a couple of “master switches” are available to us:
IDTHTOIN (Default none Range 0 – 9999) This is the time out value on TCP/IP synchronous receive operations. If exceeded, the thread is cancelled and all locks and cursors are released. Threads are checked roughly every two minutes.
TCPKPALV (Default 120, Values: ENABLE, DISABLE, 1 – 65534) Is the TCP/IP config KeepAlive value to be overridden with a different value? ENABLE – the TCP/IP KeepAlive config value is not overridden. DISABLE – KeepAlive probing is disabled. Any other value – The TCP/IP KeepAlive config value is overridden with this value of seconds. If used, consider setting close to IDTHTOIN value. Top tip: Avoid using small values otherwise severe overhead may be caused.
Talking about Connections…
For Db2 to do any work for anyone, you must first “connect” to it. Quite how you connect to it depends on lots of things but there are two absolute classics:
- Running a batch program on the mainframe
- Running dynamic SQL that “wants” to run on the mainframe
There are others, of course, and we will get to them in good time, dear readers!
How does it work?
Well, if you want a program to talk to Db2, the two must be introduced to each other just like humans used to be introduced by exchanging letters. Programs and Db2 do it by “connecting” to each other. There are multiple connection possibilities which are actually called “z/OS attachment facilities”. They are:
- CICS (Customer Information Control System)
- IMS (Information Management System)
- TSO (Time Sharing Option)
- CAF (Call Attachment Facility)
- RRS (Resource Recovery Services)
Who you gonna call?
Depending on “where” you are running, you must pick one of these to “start the conversation”. For example, WebSphere users will all actually be connecting using RRS, whereas for TSO you can use TSO, CAF or even RRS. Which facility you can, or may, use really does depend on what you want to do and exactly where, logically, you are running.
The Others…
I mentioned earlier that there are other ways of talking to Db2 and this is, as far as I am aware, the full list:
- Static SQL
- Embedded Dynamic SQL
- ODBC (Excel etc.)
- JDBC (JAVA)
- SQLJ (Also JAVA)
- Db2 for Linux, UNIX and Windows (Dynamic distributed SQL)
The first two and the last in the list are the “normal” ways, but however you connect, you must build a thread first!
Threads in CICS
Every transaction in CICS needs its own thread. They are created by transactions at the point when the application issues its first SQL or Db2 command request. The thread stays active until it is no longer required which is normally a SYNCPOINT. Thread creation costs CPU, so when a thread is about to be released, CICS checks to see if it could be reused. If no other use exists then the thread is terminated, unless it is designated as “protected”, in which case it “hangs around” until the protection time limit expires, (by default this is two check cycles which equates to around 45 seconds). These protected threads make a lot of sense as it saves the high CPU cost of always creating and terminating a thread.
CICS has three types of thread:
Pool threads – These are your standard low volume CICS transaction threads unless you are doing something special. They are defined in the pool threads section of the DB2CONN definition.
Entry threads – These are a little bit special and are designed for fast response and high-volume transactions. They are defined using a DB2ENTRY definition.
Command threads – These are special as they are reserved for the CICS Db2 attachment facility for issuing commands to the DSNC transaction. If a command thread is not available it will automatically overflow to use one of the pool threads.
In CICS, you can use the CICS RDO (resource definition online) to tune and define the threads you have.
There is no “simple” ZPARM limit for CICS threads, it is controlled by the RCT (resource control table) TYPE=INIT THRDMAX value.
Threads in IMS
They are created by transactions at the point when the application issues its first SQL. The thread stays active until the application terminates.
The number of IMS regions is the maximum number of concurrent threads.
COMMIT
If the package controlling the SQL is bound with RELEASE(COMMIT), then at COMMIT everything is freed including the thread storage. If bound with RELEASE(DEALLOCATE), then thread storage can be released. This is where high performance DBATs are able to score points!
Re-use it or Lose it!
For allied thread re-use bind with RELEASE(DEALLOCATE) but watch out for the cursor table getting large. If using created temporary tables, the logical work file space is not released until the thread is deallocated.
For DBATs use thread pooling – Make sure ZPARM CMTSTAT is INACTIVE (The ACTIVE option is deprecated these days!). That’s it!
Deep Dive on DBATs
DBATs come with Inactive connection support by splitting out DDF connections from the actual threads that are doing the work. This creates a pool of DBATs created for inbound DRDA connections. A connection makes temporary use of a DBAT to execute a unit-of-work and then releases it straight back to the pool at COMMIT or ROLLBACK. The DBAT is counted as being in-use only when it is actively processing a request.
The benefit of this is obvious: a few DBATs and a large throughput! Each DDF connection uses just 7.5KB of xxxxDIST memory whereas each DBAT uses about 200KB.
All of this adds up to large CPU, real memory & virtual memory savings. There are, as always, a few annoying exceptions that “block” an inactive connection:
- A hop to another Location
- An open and HELD cursor, a held LOB locator or a package bound with KEEPDYNAMIC(YES)
- A declared temporary table is active
Are you INACTIVE?
If you are using CMTSTAT = INACTIVE, and I hope you are, the DBATs can then come in three different groups: Ordinary Pooled, KEEPDYNAMIC-refresh and High-Performance:
- Ordinary Pooled – If not being used it “hangs around” until the POOLINAC ZPARM is reached or, if being used, after processing 500 units-of-work.
- KEEPDYNAMIC-refresh – To enable this you must first make sure that the you have enabled “automatic client reroute”, either in sysplex workload balancing or seamless failover for the group. These DBATs stay until unused for more than one hour plus a random number from 0 – 60 of minutes, or no new transaction for 20 minutes plus a random number from 0 – 20 of minutes is added. This option is very cool due to the point 2) mentioned in the list above as KEEPDYNAMIC(YES) is great for SQL Reuse but kills inactive DBATs. [The random numbers mentioned here are sometimes documented, sometimes not…] Using this, the client will seamlessly re-route the work to another DBAT without any impact to the application and also allow clean-up of the old DBAT.
- High-Performance – For this the DDF PKGREL option must be set to be BNDOPT or BNDPOOL and the package must be bound with RELEASE(DEALLOCATE). Then the DBATs are associated with a package for the life of the DBAT. They are only terminated on a clean transaction boundary after 500 units of work, or the POOLINAC ZPARM is reached -unless it is set to zero, in which case 120 seconds is used. One last thing: BNDOPT will allow the DBAT to be deallocated when the connection terminates and BNDPOOL will return the DBAT to the pool when the connection terminates.
High Performance DBATs – Problem?
Of course! No such thing as a free lunch, is there? The problem with these beasts, is that they hold package locks and table space intent locks that basically kill you when you try to do ALTERs on objects, or some utilities. or even package REBINDs. You can be saved here by a temporary MODIFY DDF PKGREL(COMMIT) command to switch it off, do your important work and then switch it back on again! Do not forget this last step!!!
I hope you found this little stroll down the threading road interesting!
TTFN,
Roy Boxwell