How does the generation of jobs and utility statements work? Is it an online process or is it done in batch?
While the execution of the utilities themselves must happen in batch, virtually everything else in TUC can be done in both online and batch. Utility statements and jobs are generated in advance to allow processing of all objects. You can have TUC regenerate new utility statements at runtime for certain objects after you have changed a runtime option for the respective utility. If only a few objects are involved, you might want to do it online. If a high number of objects are affected, it takes a little longer to re‐generate the statements, so doing it in batch is preferred.
Does TUC analyse the objects and submit jobs in a permanent, cyclic fashion or rather on demand? Which things can you actually do in real time?
There is a so‐called daily job, which should be scheduled to run once a day after the creation, modification and deletion of objects that might be part of the regular end‐of‐day work has ended. This daily job analyses all databases that reside in the respective DB2 subsystem. This way it is ensured that all objects are covered by TUC’s maintenance routines. In addition, TUC is then able to request the execution of maintenance jobs for all objects that fulfil certain criteria. This usually happens by handing over a set of jobs to the regular job scheduler that is already in place. It is also possible to request the generation of a utility job for one or more objects using the ISPF interface at any time. You can then submit this generated job manually.
Does TUC analyse the entire DB2 subsystem or only parts of it (e.g., only certain databases)? Is it possible to influence that?
The daily job analyses the entire DB2 subsystem because it checks – among other things – if objects have been created or dropped. For these objects, the corresponding TUC profiles are also created or deleted. It is, however, possible to have TUC perform this analysis for certain profiles only (profiles are used to store groups of objects together with the desired utility options – see below) and generate the required utility jobs for the objects that require maintenance. This can be done using the ISPF interface.
What does the job scheduler interface look like? Which schedulers are supported? Is it possible to reuse existing job nets?
TUC uses so‐called orders to process requests for utility executions. An order contains all the jobs and utility statements that are required to successfully execute the desired utilities. The execution itself is left to the job scheduler. Currently, TUC supports OPC and Control M. Support for other job schedulers can be added by providing adequate skeletons. If you wish to reuse existing job nets, TUC can generate the batch jobs with an option designed to process the last pending order.
How does TUC generate the job names?
The job name is generated using a freely modifiable SQL expression. This expression may contain variables that refer to the current DB2 subsystem or the utility for which the job is being generated. You can use all the functions that SQL provides in order to generate a name of up to 8 characters, even complex CASE statements can be used.
How are thresholds defined? What is their scope?
For each object, an independent set of thresholds can be specified. When these thresholds are exceeded, TUC triggers the execution of the required
jobs and generates the utility statements for the affected objects.
Are partitions treated separately?
TUC supports partition level granularity. For example, when creating an image copy, the partitions of a partitioned table space can be processed by multiple jobs that run in parallel. TUC automatically detects if all partitions or only some of them require processing and generates the utility statements accordingly.
Is it possible to generate jobs for non IBM utilities?
Because all statements are generated using skeletons, it is possible to use utilities from any vendor. Skeletons for IBM and BMC utilities are shipped with TUC. To support other vendors, the respective skeletons have to be added to TUC’s skeleton library.
How do you group objects together and use a common set of parameters, jobs, etc. for them?
A group of objects with their own set of utility options is called a profile in TUC. The objects that are contained in a profile can be processed by TUC in a uniform fashion, using the same options for all objects. Profiles are the fundamental elements on which TUC operates. In order to process a single database, it must be contained in a profile. TUC’s daily job makes sure that there is an automatic database profile for every single database in the DB2 subsystem. This profile has the same name as the database and contains all the objects (table spaces and recoverable indexes) of that database. It is possible to create an arbitrary number of profiles and put any objects in them. It is also possible that the same object is part of more than one profile.
Does TUC take care of RUNSTATS and REBIND?
The skeletons that are used by TUC are coded in a way that ensures the collection of inline statistics when utilities like REORG are executed. Also, all affected plans and packages are automatically rebound upon request. If a job scheduler is used, it is possible to execute multiple parallel REORG jobs and do the REBIND after all REORG jobs have ended successfully. Of course, a job to collect RUNSTATS and do a REBIND can also be executed manually for the objects of a profile using the ISPF interface.
Can TUC avoid REBIND if the access path does not improve?
TUC only automates the generation of utility jobs and statements. It does not analyse the access path. A different product must be used if access path analysis is desired.
Is it possible to alter the space allocation of an object? Can this be done dynamically?
TUC calculates the space for tablespaces and indexes based on row estimations provided by the user or collected by statistics for each table. Estimations are corrected automatically if the total number of rows exceeds the last estimation. TUC will only increase these values, but never decrease them. TUC automatically generate ALTER statements to increase the PRIQTY and SECQTY values before objects are reorganised so that the new values take effect.
Does TUC use LISTDEF and TEMPLATE? How complex are the jobs that TUC generates?
Whenever a utility allows the usage of LISTDEF and TEMPLATE, it will be used by TUC. The utility statements are stored in a separate library. The actual jobs are very simple and basically consist only of a procedure execution. The utility procedure will then use the statements from the library in order to execute the utility.
Is it possible to use identical settings for different DB2 subsystems?
For each DB2 subsystem, TUC maintains a separate set of settings. These are stored in DB2 tables that are located in the respective subsystem. It is possible to export the settings from one subsystem into a flat file and import them into a different subsystem. When importing settings, TUC automatically adapts subsystem‐specific values to fit to the target subsystem.