DB Genie ™

Overview:


With DB2®* applications increasing in complexity and operational environments running on limited resources, the task of constructing efficient utility strategies can become quite cumbersome and time consuming. Much of a Database Administrator’s effort can be consumed by creating and maintaining utility JCL and then organizing it to work optimally with operational resources (initiators, tape drives, etc.). Sometimes this effort alone requires the need for additional staff resources.


DB-GENIE has eliminated the tedious burden of constructing the utility strategy and allows the DBA to focus on its effectiveness. Using a set of robust ISPF based panels, this software goes beyond just creating utility JCL and offers features, exclusive to DB-GENIE, such as Load-Balancing and List-Processing to organize the DB2® objects into job streams that provide optimal performance within operational constraints.


To further enhance utility strategies, DB-GENIE also saves environment specific information for all of your LPAR/Subsystem combinations. Since operational characteristics can be different for each machine (media mnemonics, jobcard parameters,etc.), Environment Specific JCL Parameters (profiles) are defined only once. Then, ANY combination of DB2® utilities can be created for ALL machines at ONE time without concern for the environment specifics of the JCL.

Furthermore, there is also table-level functionality in DB-GENIE that utilizes all of these powerful features.  So, you can optimize not only your utility strategies, but also table-level functions such as moving data between environments.

 


DB-Genie Benefits:

 

A Solution


Creating a customized, efficient utility strategy is imperative to many of today’s DB2® shops. Involved in this process are several constraints (tape drives, initiators, utility batch window, etc.) and can, depending on the number of DB2® objects involved, require a great deal effort from the Database Administrator. DB-GENIE offers several tools and features to make this process much easier. DB-GENIE’s outstanding features are its ease of use and the robust nature of the online panels. Virtually no training is required to use this product. DBA’s will be especially impressed at the power and flexibility behind DB-GENIE’s intuitive screens. There is no other product on the market dedicated to DB2® utility generation with this much functionality.

DB-GENIE gives you complete "select" capability to control the configuration of the DB2® objects within your utility jobs. This allows you to fine tune your backup/recovery strategies to optimize your backup and recovery window.

 

Disaster Recovery is also a priority in many of today's DB2® shops. With DB-GENIE’s Load Balancing and List Processing, Disaster Recovery testing becomes a simpler tas k and you’ll appreciate how DB-GENIE makes this a streamlined and straight forward process.

DB-GENIE makes it simple to maintain your Image Copy, Reorg and Recovery strategies. Utility options and JCL parameters are stored in a profile so that utilities can be re-generated when tablespaces are added or deleted.


DB-GENIE is very useful for generating ad-hoc utilities such as point-in-time Recoveries, Reorgs, Runstats, Rebinds etc. for a tablespace or set of tablespaces.

 


Load-Balancing


In any DB2® shop, a given utility strategy is usually constrained by the operational resources available during it’s window. Given these constraints, it is the DBA’s responsibility to design a utility strategy to exploit all resource opportunities during the execution window.


Load Balancing is simply the process of organizing your utility jobs to get the most throughput in the least amount of time.


For example, let’s say you want to create Image Copy jobs for your production environment. You have a total of 100,000 cylinders allocated to the production environment. Operations has told you that during your image copy window, you can use up to 8 initiators and 8 tape drives. With this information, we can figure out that the most jobs we can have running in parallel is 8 and to properly place the tablespaces in each job, we need to load-balance them. 100,000 cylinders divided by 8 is 12,500 cylinders per job. So, you tell DB-GENIE to create Image Copy jobs with 12,500 cylinders in each and they will be properly load balanced for your environment. Since actual run times may vary based on hardware, DB-GENIE gives you the ability to easily adjust the job/tablespace configuration using List Processing.



List Processing


List processing is an alternative to specifying DB2® objects in the DB-GENIE screens and is part of the architecture that enhances the software's capabilities. This allows you to create an SPF sequential or PDS dataset that contains a list or lists of DB2® objects. You then use this dataset as input to the software.


The three types of list processing managed by DB-GENIE are User List, Include List, and Exclude List. These concepts are fully described in the user guide and, as you will see, they have numerous applications for creating customized, efficient utility strategies.

To reference the example given in the previous section (Load Balancing), let’s say you optimally balanced your image copies into 8 jobs. Load Balancing is a great method to get your first "cut" of jobs. Of these 8 image copy jobs, let's say you have the following elapsed time breakdown:


4 jobs finish in 30 minutes, 2 jobs finish in 60 minutes, and 2 jobs finish in 90 minutes.


Even though the image copies are Load Balanced, these elapsed times are an indication of bottlenecks at the I/O level. Ideally, we want all 8 jobs finishing at about the same time. This is just one situation where we can use List Processing to further tailor our image copy strategy.


When we generated these 8 image copy jobs, DB-GENIE created a User List for us. This is a PDS with individual members named corresponding to each of the 8 jobs. Each member contains the database & tablespace used for that particular job. Now, all we have to do is move some of the tablespaces from the 90 minute jobs to the 30 minute jobs (within the User List PDS members) and re-generate the 8 image copy jobs. This couldn't be any easier!!

NOTE: It's important to remember these powerful concepts (Load Balancing & List Processing) apply to ALL utilities, not just image copies. In fact, all concepts described below apply to ALL utilities as well.



Exclude Processing


Exclude processing allows you to specify DB2® Objects that you DON’T want included in the selection process.

For example, you may want to generate a RUNSTATS job for all 50 tablespaces in database XYZ except for tablespaces 5, 10, 17, & 29.


Exclude processing is a very powerful method of refining your utility jobs to meet your needs.



Exception Processing


Exception Processing gives you the flexibility to generate utilities only for objects that need the utility to be executed. For example, you can tell DB-GENIE to create an Image Copy job for every tablespace that is in a COPY PENDING status. Likewise, you can create a Recovery job for every tablespace or indexspace that is in a RECOVERY PENDING status.


Exception Processing makes it very easy to resolve DB2® Object status issues in a timely manner.



Environment Specific JCL Parameters (Environment Lists)


To DB-GENIE, an environment is an eight character field defined as a combination of system id (or LPAR) and DB2® subsystem id. You use this identifier to define all the associated mnemonics for that particular environment. You do this for all applicable environments only ONCE.


Then, no matter what LPAR you are actually logged on to currently, you can generate utility JCL for ANY or ALL of the environments you have defined to DB-GENIE. Thus, for example, it is possible to refresh ALL utilities for ALL environments with the push of the ENTER key.


Also, you never have to edit the JCL for each environment. The JCL is tailored for a particular environment and the sizes of the tablespaces in that environment.



Session Parameter Storage


If you like, DB-GENIE will save all utility options and JCL parameters for a particular session. Thus, you can specify what options you want to use for each utility and before you exit the software, you can save all options for future reference/use.

 

 

This can be extremely useful for refreshing utility JCL with the same parameters that were used when it was created originally (without having to remember and re-specify all the utility options!!).



Partition Level Processing


All utilities that can run at the partition level, can also be created by DB-GENIE at the partition level.



Table Level Processing


All features mentioned above, can also be executed at the table level.  This is extremely useful for moving data between environment, but ensuring the jobs created are optimized.



Batch Processing


All features mentioned above, can also be run in batch mode.  This is useful for setting up batch jobs with recurring execution.



Open-Ended Architecture


DB-GENIE has been designed to allow the users to create their own personal skeletons, which can then be used by the internal generating engine. This allows you to set up a job with the steps you desire and then you can use DB-GENIE’s full generating capabilities to meet your own personal objectives. Skeletons can be created for running DB2® commands in batch or for creating GDG bases for image copies.



Generate Dynamic JCL


Generating utility JCL dynamically eliminates the possibility of utility jobs abending due to work datasets being undersized because of unaccounted-for table growth. Each time you generate a utility, DB-GENIE interrogates the DB2® Catalog for sizing information for allocating sufficient work dataset space specific to that DB2® object. This means you can "refresh" your entire utility strategy, as often as you wish, for any number of reasons (work file sizing concerns, DB2® objects being added or removed from the strategy, etc).


Generating utility JCL dynamically also means you don’t need "canned" JCL for those ad hoc utility executions. Just specify your objects through List-Processing, or directly on the screen, and let DB-GENIE generate current JCL.



Resource Savings


Significant resources are reduced in constructing effective utility strategies. With features such as Load-Balancing and List-Processing, combined with DB-GENIE’s JCL engine, a great deal of time and effort will be saved.

*DB2® Is A Registered Trademark Of IBM Corporation