IDUG Content Committee

Javier Estrada Benavides

javier.strad@gmail.com


Setting your Db2 zParms to the right values can be a never ending task, but luckily the previous article from Emil Kotrc (found in here), gave a structure on what you’ll find, and several pointers so you can study some recommendations from other Db2 users and see how that fits to your site.

The other side of the picture, as it is also mentioned there, is that we live in a big world of “it depends” situations. zParms will take an effect over the entire Db2 subsystem and its applications, therefore it means that in most cases you’ll need to take the following steps:

• Study your environment over time.

• Study the zParms that can bring the best optimal conditions to your Db2.

• Find a theoretical value you wish to set for your zParms.

• Negotiate with the application owners for this value, the impact, and a time frame so you can execute the change. (And yes, ideally you should test with them if the new values brought the expected benefit).

The problem is quite clear, as in many cases, you’ll reach situations where the ideal value of a zParm doesn’t match your application’s point of view. In this article, we’ll take a look at one such scenario and how you can take advantage of other Db2 features to accomplish the task without any zParm change.

The premise is simple: I can’t set a value of xxxxxx zParm that satisfies all my applications. How can I set the ideal value for only one or a subset?

Db2 comes to the rescue with DSN profiles. And it has been presented in several conferences showing its potential. In here we’ll discuss only a fraction of what we can do with DSN profiles to work with zParms (or not work with them, but do the same task).

A reminder on DSN profiles

Db2 offers the opportunity to define what an application is, or how to narrow the scope to specific plans/packages/threads, and apply a specific behavior to them so you won’t have to set a global behavior in the zParm stone tablets of truth.

The functionality is similar to RLF, following these steps:

• Locate the Db2 Catalog table SYSIBM.DSN_PROFILE_TABLE

  • Insert a record that will point to the scope of the application. This can be, for example, a Db2 Package name, a Collection, user id or IP address.

This record will be the “definition of an application”, and once you have it, we’ll add behavior or special properties by using this profile ID (which is an identity column) together with the next table.

• Locate the Db2 Catalog table SYSIBM.DSN_PROFILE_ATTRIBUTES

  • Insert the appropriate values for the following columns:

    • PROFILEID: As this suggests, point to the profile ID from the previous table.

    • KEYWORDS: This column contains the action Db2 will take when the application has connected or has been found.

    • ATTRIBUTE1 and ATTRIBUTE2: Depending on the value of the column KEYWORDS, these columns will contain a threshold to look out for and an action to take if the threshold has been met.

It’s important to note that the values in these Db2 Catalog tables need to follow specific combinations to obtain a specific effect in your applications. For the full listing of the available combinations, go to this link and you’ll find a table with a summary of values and controls available.

The problem we want to solve

Let’s remember our problem once more:

I can’t set a value of xxxxxx zParm that satisfies all my applications. How can I set the ideal value for only one application or a subset?

We will now take a look at a couple of scenarios where we can encounter this problem.

Setting the right idle thread timeout

One of the first headaches you’ll find when you migrate a remote application from a distributed environment to Db2 on z/OS, is that they’ll assume the same environment conditions apply. For example, let’s take a look at a couple of statements.

• In my old environment, no database connections had a time limit.

• In my old environment, there was only 1 database and only 1 application running.

I’m sure you’ve heard the same comment several times, showing the real problem:

How do you find the right idle thread timeout (zPARM IDTHTOIN) when you have 50 applications connecting to DDF?

The headache comes from having a common agreement between the following factors:

• Your zParm IDTHTOIN that controls the idle thread timeout.

• The timeout value in the JDBC/ODBC/other connection pool.

• Making sure the connection pool on the client server pre-tests the connections before assigning them to the programs.

It is indeed possible that you can’t find a common agreement or the change request was denied from the teams working on the client servers (that’s also a common scenario). But it doesn’t mean that you can’t take any action from Db2, and it is in here that DSN profiles come to our rescue.

Using DSN profiles to set idle thread timeouts

From the previous sections, we can guess that all we need to do is find the right combination of values to achieve our goal, but let’s set the rules first:

1. You won’t change your IDTHTOIN zParm.

2. When application X connects from server Y, it will have a specific idle thread timeout value. No other application should be affected by this change.

Now, here is the recipe for that behavior:

Table DSN_PROFILE_TABLE

Identify your application. Use one of the filtering criteria columns that fits more appropriately to your case:

• Column LOCATION if you want to limit the scope to a specific IP address.

  • Similarly, you can also do it with the column CLIENT_WRKSTNNAME.

• Column AUTHID if you want to match it to the user id in the data source. For example, your application might be connecting from several severs.

Table DSN_PROFILE_ATTRIBUTES

Establish the new threshold. Use the following combination of values

• Column KEYWORDS. Insert the string ‘MONITOR IDLE THREADS’.

• Column ATTRIBUTE2. Place the threshold value for this specific profile. This is the column where you’ll insert the ideal idle thread timeout for this specific application.

• Column ATTRIBUTE1. Choose the appropriate action to take when the threshold value (in the previous point) has been met. There are several possibilities, from a warning message or termination of the thread.

For the full list of the values in the column ATTRIBUTE1, and the general instructions, follow this link to the official IBM documentation.

Next step: Refresh your activated profiles

Simply execute the following command to activate your DSN profiles

-START PROFILE

(And try not to confuse it with statistics profiles, as they’re very different concepts).

Reloading and stopping DSN Profiles

If you’re testing the change and you want to change the thresholds or terminate the profile, you do not need to restart DDF or change any zPARM. Just take the following action:

To restart your DSN profiles.

Simply execute the START command to refresh the behavior.

-START PROFILE

To stop your DSN profiles

Now you will have two choices.

To stop a specific DSN Profile

You can certainly stop specific profiles while keeping others active.

1. Go back to the Db2 Catalog table DSN_PROFILE_TABLE.

   1. Locate the column PROFILE_ENABLED and set it to the value ‘N’.

2. Refresh the profile definitions.

   1. Execute the START PROFILE command to refresh your active profiles.

To stop all DSN Profiles

If you want to disable the actions from all profiles, execute the following command.

-STOP PROFILE

Don’t forget that the zPARM PROFILE_AUTOSTART can control if your profiles are activated at Db2 startup.

Can I use DSN Profiles to “change” other subsystem parameters?

While it is not possible to use DSN Profiles for changing any zParm, we can use them for a subset of cases. The full listing of possibilities can be found in this link. You should also remember that many zParms can also come in the flavor of a global variable or a special register, and using DSN Profiles will take advantage of those alternatives. In the following articles you will see some examples.

Closing thoughts

Changing a zPARM value can be a trivial task and several tools can let you do it in simple ways in a matter of seconds, and on the other hand, changing values can require that you spend a considerable amount of time in negotiations and study. The key point to ask ourselves as DBAs is whether we really want to change a zPARM that applies to everyone or if there’s another solution. Db2 brought several controls by leveraging DSN Profiles so we can apply certain effects to the application level and have a finer control and the example described in this article is only one of many benefits we have at our advantage.