TCBs, SRBs, and Enclaves

Many Db2 DBAs first hear about Task Control Blocks (TCBs) and Service Request Blocks (SRBs) in an IBM performance class, but not everyone has taken one of those classes. And even for those who have, a refresher is probably in order.

At a high level, for mainframe z/OS programs code can execute in one of two modes: TCB mode, also known as task mode, or SRB mode. Most programs execute under the control of a task. Each thread is represented by a TCB. A program can exploit multiple processors if it is composed of multiple tasks, as most programs are.

An SRB is a control block that represents a routine that performs a particular function or service in a specified address space. SRBs are lightweight and efficient but are available only to supervisor state software. An SRB is similar to a TCB in that it identifies a unit of work to the system. But an SRB cannot “own” storage areas. SRB routines can obtain, reference, use, and free storage areas, but the areas must be owned by a TCB. SRB mode typically is used by operating system facilities and vendor programs to perform certain performance-critical functions.

In general, z/OS dispatches Db2 work in TCB mode if the request is local, or in SRB mode if the request is distributed. These parallel tasks are assigned the same importance as the originating address space. Of course, this is a gross generalization and as zIIPs have become ubiquitous more SRB mode work has been enabled (as only SRBs can run on the zIIP).

Preemptible enclaves are used to do the work on behalf of the originating TCB or SRB address space. Enclaves are grouped by common characteristics and service requests, and because they are preemptible, the z/OS dispatcher—and Workload Manager—can interrupt these tasks for more important ones. There are two types of preemptible SRBs: client SRBs and enclave SRBs.

If the Db2 request is distributed DRDA workload, then it will be executed in enclave SRBs. If the request is coming over a local connection, then it will be dispatched between TCBs, client SRBs, and in some cases enclave SRBs (such as for parallel queries and index maintenance).

What Is an Enclave?

An enclave is a construct that represents a transaction or unit of work. Enclaves are a method of managing mainframe transactions for non-traditional workloads. You can think of an enclave as an anchor point for resource accumulation regardless of where the transaction is executing.

With traditional workloads, it is relatively easy to map the resources consumed to the actual transaction doing the consumption. But with non-traditional workloads (such as web transactions, distributed processing, and so on) it can be more difficult because a transaction can span multiple platforms. Enclaves are used to overcome this difficulty by correlating closely to the end user’s view of the transaction.

So even though a non-traditional transaction can be composed of multiple “pieces” spanning many server address spaces, and can share those address spaces with other transactions, the enclave gives you more effective control over the non-traditional workload. 

Synopsis

Hopefully this short introduction to TCBs, SRBs, and Enclaves has been helpful. At least the next time you hear somebody use these terms you'll have some idea what they are talking about!

Moving from IMS to Db2?

As a Db2 consultant and regular blogger I often receive questions about technical things, but also on strategy and industry trends. I try to help but I always make it clear when answering that I cannot predict the future, only make educated guesses... So with that introduction out of the way, one of the regular questions that I get involves comparing IMS to Db2, or helping to determine if it makes sense to convert from IMS to Db2. Here is the text of a recent question that was posed to me:

"My customer is wondering about the possible advantages of converting his IMS DB/DC system to IMS DC/Db2. The application currently performs well with an internal response time of less than .5 seconds on average. Even with an arrival rate of 425 full-function transactions per second, the queue count rarely goes above 10. This system typically peaks at 12.5 million transactions per twelve-hour day against HDAM and HIDAM databases totaling close to 1 terabyte. The application itself is currently a bit over seven million lines of code. Can you comment on the relative cost vs. advantage of moving an existing application from IMS DB to Db2 along with relative CPU capacity requirements?"

Here is my response:

Well, the main advantages of converting from IMS/DB to Db2 would be the better support for ad hoc query, support of standard SQL (instead of non-standard DL/1) for writing queries and a deeper pool of talent to support the Db2 environment. There are many more skilled Db2 developers out there than there are IMS techies these days. 

The primary benefit of sticking with IMS is the good and predictable performance you currently enjoy. And, of course, there would be no need to convert the database structures or the more difficult-to-achieve rewriting the necessary application code.

So, the high-level metric to keep in mind as you make your decision comes down to this: a well-designed IMS application will perform very fast, perhaps faster than a well-designed Db2 application (but that does not mean that Db2 is slow). On the other hand, it will be easier to develop applications for Db2, especially when data needs to be accessed multiple ways and in an ad hoc manner. 

I guess it boils down to this: How happy are you with the current application, are you able to support it properly and how many other IMS/DB databases do you support? If this is the last IMS/DB database and you are looking to convert away in order to rid yourself of the IMS license, then it makes sense to consider converting. But you should do a project plan and cost/benefit analysis before making your final decision (conversion can be very costly). If you have a lot of other IMS/DB databases, then don't convert to Db2 unless you cannot support the needs of your end users (management, ad hoc support, etc.) using IMS.

In terms of CPU requirements, Db2 will consume more CPU than IMS. Db2 optimizes queries internally whereas IMS programmers construct access paths to data. This additional requirement will cause Db2 to consume more CPU. But, of course, that additional CPU brings with it the enormous benefit of database optimization and better ad hoc query support.

Furthermore, if you do decide to convert from IMS to Db2, be sure to use Db2 as it was intended to be used. In other words, don't just convert segments to tables and be done. Make sure that you normalize your design and come up with a good "relational" Db2 database design. Also, be sure to train your programmers to write SQL queries. That means changing mindsets from the record-at-a-time processing of IMS to the group-at-a-time processing of Db2 and SQL.

For example, if you have master file processing logic it needs to be re-examined in Db2. Instead of open file, read record from file, use key to read record from database... you should be joining tables and reading from the join. This requires a different mindset and coding technique. 

As an additional consideration, keep in mind that Db2 is a much "newer" database technology than the hierarchical infrastructure used by IMS. So it stands to reason that newer capabilities and features will likely be implemented in Db2 before they are implemented in IMS; for example, AI and machine learning capabilities. That does not mean that IMS is obsolete, it isn't; IBM still maintains and upgrades IMS (at the time of writing, the current version is IMS 15.2 which was released for GA in March 2020).

Summary

Of course, this answer has been necessarily brief. I encourage you to read up on Db2 and understand its many differences with regard to IMS before jumping into a "go"/"no go" decision. Best of luck...

Wishing Z Mainframe a Happy Birthday

Today, April 7, 2022, the IBM mainframe officially celebrates its 58th birthday. The IBM 360 was launched on April 7, 1964 and the world of enterprise computing has never been the same.

I'm old enough to remember in the early 1990s when Stewart Alsop of InfoWorld predicted the last mainframe would be unplugged on March 15, 1996. And ever since he made that woefully inaccurate prediction many think that the mainframe is no longer relevant. But they are wrong... Here we are, 26 years after that infamous predicted drop-dead date and the mainframe is still going strong! 

Mainframes are designed to be able to process a trillion web transactions a day with the capability to process 1.1 million transactions per second. And 70% of the Fortune 500 use mainframes!

To all of you diligent mainframe users out there, today is indeed a day to celebrate... another year has gone by, and mainframes are still here... running the world!

On The Road Again… IDUG North American Db2 Tech Conference 2022

Well, it looks like this year IDUG will be a live, in-person event again after a couple years of living virtually. The COVID pandemic took its toll on IT events and conferences, but I, for one, am looking forward to getting back out there and mingling – safely – with my Db2 colleagues. And the North American IDUG conference Boston this July is just the place to do it!

Yes, I know that July is still 3 months away, but it is definitely time to start making your plans now to attend this year's IDUG... because let’s face it, you probably haven’t traveled in a while so you’ll be rusty and need all the time you can get to plan effectively!  Starting now means that there’s still time to get your manager’s approval, make your travel plans, find all those suitcases you'll need that have been collecting dust, familiarize yourself with your airline's travel requirements, buy a new box of KN-95 masks, and be where all the cool Db2 folks will be this July 11-14, 2022.

If you’ve ever attended an IDUG conference before then you know why I’m excited. IDUG offers a world of phenomenal educational opportunities delivered by IBM developers, vendor experts, users, and consultants from all over the world. There will be a slew of informative technical sessions on all of the latest and greatest Db2 technologies and features. 

At this point, the agenda has not been set yet, but c’mon, you know it is going to be a great event. Especially with Db2 Vnext looming on the horizon, right?

So get on it and start planning to be in Boston this July. And if you need any help justifying your attendance at this year’s IDUG event, IDUG offers a handy template for creating a justification letter for your boss. You’ll need to make some changes for the 2022 event, but it is a nice starting point.

The Bottom Line

The IDUG Db2 Tech Conference is the place to be to learn all about Db2 from IBMers, gold consultants, IBM champions, end-users, ISVs, and more. With all of this great stuff going on this year in Boston, why wouldn't you want to be there!?!?

Db2 User-Defined Functions (UDFs)

A user-defined function, or UDF for short, enables you to customize Db2 to your shop's requirements. It is a very powerful feature that  can be used to add procedural functionality, coded by the user, to Db2. The UDF, once coded and implemented extends the functionality of Db2 by enabling users to specify the UDF in SQL statements just like built-in SQL functions.

Additional functionality and capability aside, it can also be complex to deliver and requires detailed knowledge, additional application development skills, and extra administrative handling.

User-defined functions are ideal for organizations wishing to utilize Db2 and SQL to perform specialized, corporate routines performing business logic and data transformation.

Types of UDFs

There are two ways of creating a user-defined function: you can code your own program from scratch using a traditional programming language; or you can use an existing function as a template, of sorts.

There are two types of user-defined functions that can be written from scratch: scalar functions and table functions. A scalar function is applied to a column or expression and operates on a single value. DB2 offers multiple built-in scalar functions, each of which can be applied to a column value or expression. Examples of built-in scalar functions include LTRIM, SQRT, and SUBSTR. You can read more about Db2 built-in functions in my earlier blog post, A Quick Intro to Db2 SQL Functions.

Table functions are a different type of function that, when invoked, returns an entire table. A table function is specified in the WHERE clause of a SELECT statement taking the place of a table, view, synonym, or alias.

Scalar and table user-defined functions are referred to as external functions, because they are written and developed outside of (or external to) DB2. External UDFs must be written in a host programming language. DB2 UDB user-defined functions can be written in Assembler, C, COBOL, or PL/I.

The final type of user-defined function is a sourced function. A sourced function is based on a function that already exists, that is it can be based on a built-in function or another user-defined function that has already been created. A sourced function can also be based on an existing scalar or column function.

User-defined functions are similar in functionality to application subroutines. But user-defined functions are different because they are used inside of SQL statements. This gives them great power. A user-defined function is not a substitute for an application subroutine, subprogram, or procedure. Instead, user-defined functions are used to extend the functionality of the SQL language.

The Schema

User-defined functions, user-defined distinct types, stored procedures, and triggers all are associated with a schema. By default, the schema name is the authid of the process that issues the CREATE FUNCTION, CREATE DISTINCT TYPE, CREATE PROCEDURE, or CREATE TRIGGER statement.

You can specify a schema name when you create a user-defined function, user-defined type, or trigger. If the schema name is not the same as the SQL authorization ID, then the issuer of the statement must have either SYSADM or SYSCTRL authority, or the authid of the issuing process must have the CREATEIN privilege on the schema.

For example, the following statement creates a user-defined function named FUNCX in the schema named MYSCHEMA:

     CREATE FUNCTION MYSCHEMA.FUNCX ...

If the MYSCHEMA component was not included in the CREATE statement, then the schema would default to the authid of the person (or process) that executed the CREATE statement. In short, the schema is set to the owner of the function. If the CREATE statement was embedded in a program, the owner is the authid of the owner of the plan or package; if the statement is dynamically prepared, the owner is the authid in the CURRENT SQLID special register.

Table Functions

Table functions are different in nature than scalar functions. A table function is designed to return multiple columns and rows. In other words, the output of a table function is itself a table. An example using a table function follows:

SELECT WINNER, WINNER_SCORE,
             LOSER, LOSER_SCORE
      FROM   GAME_RESULTS(5)
      WHERE  LOSER_SCORE = 0;

In this case, the table function GAME_RESULTS( ) is used to return the win/loss statistics for football games. The table function can be used in SQL statements just like a regular DB2 table. The function program is designed to fill the rows and columns of the "table." The input parameter is an INTEGER value corresponding to the week the game was played; if 0 is entered, all weeks are considered. The query above would return all results where the losing team was shut out (had 0 points) during the fifth week of the season.

The following or similar CREATE FUNCTION statement could be used to define the GAME_RESULTS( ) function:

      CREATE FUNCTION GAME_RESULTS(INTEGER)
     RETURNS TABLE (WEEK INTEGER,
                    WINNER CHAR(20),
                    WINNER_SCORE INTEGER,
                    LOSER CHAR(20),
                    LOSER_SCORE INTEGER)
       EXTERNAL NAME GAMES
        LANGUAGE C
        PARAMETER STYLE DB2SQL
          NO SQL
          DETERMINISTIC
          NO EXTERNAL ACTION
          FENCED
          SCRATCHPAD
          FINAL CALL
          DISALLOW PARALLEL
          CARDINALITY 300;

The parameter identifying this as a table function is the RETURNS TABLE parameter. This parameter is used to define the columns of the table function. The function program must create these rows itself or from another data source such as a flat file.

The value supplied for the CARDINALITY parameter is only an estimate. It is provided to help Db2 optimize statements using the table function. It is possible to return more or fewer rows than is specified in CARDINALITY.

Sourced Functions 

Sourced functions are created from functions that already exist. A sourced function can be based on any existing function, whether built-in (scalar and column) or user-defined (scalar). The biggest reasons that sourced functions are created is to enable functions for user-defined distinct data types. This is required because DB2 implements strong typing. Here is an example of creating a sourced UDF:

 CREATE FUNCTION FINDWORD
    (DOCUMENT, VARCHAR(50))
  RETURNS INTEGER
  SPECIFIC FINDWORDDOC
  SOURCE SPECIFIC FINDWORDCLOB;

In this example a new function, FINDWORD, is created from an existing function FINDWORDCLOB. The function finds the location of the specified word expressed as a VARCHAR(50) value in the supplied DOCUMENT. The function returns an INTEGER indicating the location of the word in the DOCUMENT. DOCUMENT is a user-defined data type based on a CLOB data type.

Summary

UDFs provide powerful functionality that can be used to customize Db2 SQL functionality and standardize your operational functtions and algorithms into your DBMS environment.