As stated in my earlier posts I have to present on the topic "Measuring DB2 activity in terms of MSU / MIPS". My client wants to know the current MSU / MIPS utilization trend for his existing DB2 v8 so that he can decide on how much MSU / MIPS licence he would want for a new DB2 v 10 purchase.

My earlier post:

ibmmainframes.com/about64937.html

I came to know of certain MSU calculation tools SCPT / SCRT (Sub Capacity Planning Tool and Sub Capacity Reporting Tool). I also came to know that MSU is recorded in Type70 RMF records. Beyond these hints I am not getting further material.

The fact is there are several links to SCPT / SCRT and also RMF type70 / type72 records and even some JCLs to calculate MSU. But I am looking to calculate MSU specifically for DB2 subsystem, meaning what is the breakup of MSU measurement for a whole z/OS system and how much is the MSU measurement for DB2. Once the DB2 MSU calculation is available my customer can get to know the size of his current business and can also correlate it with the existing licence cost factor (z/OS and z/OS based software licences are sold on basis of MSUs)

So I began looking how DB2 activity is currently measured. The DB2 performance measurement techniques available on google measure performance in terms of time taken for a DB2 package to execute (while in a "IN-SQL" state). Standard performance measurement tools used are Omegamon / Strobe / BMC Mainview.

As stated earlier these "time-taken-to-complete" statistics are not helping me to achieve my goal of measuring DB2 performance in terms of MSU / MIPS. My customer says he is finding it difficult to decide on DB2 computing capacity purchase using these "Time-taken-to-complete" statistics

My friends are willing to help. If I give them some DB2 code to execute (where I can code some display statements) they can run this DB2 stuff at their end and mail me the results. I will have to google out how to convert this "space / bytes occupied" and "time-taken-to-complete" statistics into MSU / MIPS

At least I will have to make best use of the available help. There are some constraints my friends are facing (in terms of the accesses they currently have).

My friends say they dont have access permissions to create new plans / bind templates / packages in their shops. All the existing packages that they monitor on Omegamon / Strobe / BMC Mainview are closed for further modifications (meaning they cant insert my DB2 stuff in their existing packages).

But access to a basic DB2 subsystem and privilege to execute COBOL programs is there. Meaning they can create tables / can perform insert-update-delete operations using frontend COBOL apps e.t.c

So now I am limited to basic table creation / basic COBOL stuff.

Of course DB2 DML-cum-DDL access / COBOL access might differ from shop-to-shop but certain things have to be common. No matter what type of tablespaces / stogroups are present, no matter what extents / page charecteristics / locking charecteristics are set some things must be common across all shops. No matter what is the COBOL setup (say COBOL II or III) the basic "DISPLAY" statements / "EXEC SQL" statements must remain the same.

Since I am limiting myself to calculation of "space / bytes occupied" and "processing time taken" statistics I am planning to write some basic SQL / COBOL stuff with some display statements to help me get the statistics I need

VisionPLUS MBS (Merchant BankCard System) module generates several output datasets everyday, some of which which contains details on daily ATM transactions. Once the MBS transactions are validated by TRAMS, several VSAM KSDS are created with each containing about a billion records (imagined average)

Each record is of 4000 characters length containing various fields (label and values) such as:

There are several hundreds of fields. Above are core fields I used in the past to troubleshoot (of course troubleshooting / testing VisionPLUS MBS is complicated stuff, just for purposes of this DB2 MSU / MIPS ppt I have taken above 11 fields)

CREATE TABLE ATM_BANK_MASTER(
ATM_CARD_BANK_ID VARCHAR(4) NOT NULL,
BANK_NAME VARCHAR(15) NOT NULL,
ATM_CARD_TYPE VARCHAR(15), /*Say mastercard or visa, am planning to make this nullable since there could be additional types in future */
ATM_OTHER_BANK_TRANSACTION_ALLOWED CHAR(1) NOT NULL,
ASSOCIATED_ATM_ID VARCHAR(4),
PRIMARY KEY(ATM_CARD_BANK_ID),
FOREIGN KEY(ASSOCIATED_ATM_ID) REFERENCES ATM_MASTER (ATM_ID) ON DELETE NO ACTION, /* ATM ids may change but bank name records shouldnt so no action on this table even if ATM_MASTER changes */
) IN <schema name / tablespace name>;

So as I keep adding each record I will be totalling the string lengths of each field value. But I know that a maximum of 178 bytes have to be occupied per record- no more. I need this upper limit for a later calculation

Similarly for records of other tables

................Need to stop here. Will be back soon to complete...................

But I am looking to calculate MSU specifically for DB2 subsystem, meaning what is the breakup of MSU measurement for a whole z/OS system and how much is the MSU measurement for DB2.

Does the site have zIIP or zAAP processors, or are they planning on using them? If so, your calculations will be complicated since these specialty engines reduce the workload on the processor (CP) for DB2.

I would think you could get a good approximation of the value you want by looking at the SMF type 72 subtype 3 records for the service class (or service classes) for DB2. The Service/Report Class Period Data Section has the service units by total, TCB, IOC, central storage, and SRB. And converting service units to MSU is simple. You may have to deal with multiple periods, depending on how the WLM policy is put together, and how much data you need to collect. But at least you won't be spending hours writing posts and analyzing data that adds little, if any, incremental value to the results.

And if you think people are actually reading your entire posts, you are VERY mistaken. We don't care about the DB2 details of what you're doing, especially since we don't need to know those details to point you to the SMF type 72 subtype 3 records.

Apologies for the lengthy post. To be frank I was getting very little pointers from Google. And my customer wanted me to train him on preparing "Return On DB2 Investment" reports. I gave him a presentation on Omegamon / Strobe / BMC Mainview which measures DB2 activity in terms of "time taken to complete" (say this many minutes / this many hours e.t.c), but he said he was unable to correlate these "time" statistics with the original licence cost factor- MSUs.

Robert Sample wrote:
-----------------------------------------------------------------------------------
"Does the site have zIIP or zAAP processors, or are they planning on using them? If so, your calculations will be complicated since these specialty engines reduce the workload on the processor (CP) for DB2."
-----------------------------------------------------------------------------------

I will take a note of this in my presentation.

Robert Sample wrote:
-----------------------------------------------------------------------------------
"I would think you could get a good approximation of the value you want by looking at the SMF type 72 subtype 3 records for the service class (or service classes) for DB2. The Service/Report Class Period Data Section has the service units by total, TCB, IOC, central storage, and SRB. And converting service units to MSU is simple. You may have to deal with multiple periods, depending on how the WLM policy is put together, and how much data you need to collect."
-----------------------------------------------------------------------------------

Thanks Robert for this lead. I will work on this further.

vasanthz wrote:
-----------------------------------------------------------------------------------
"The SCRT report would give you how much MSU, the DB2 subsystem is using broken up by LPAR. There would be a value called TOOL MSU, that is what IBM uses for licensing.
..............................................................................
..............................................................................
..............................................................................

SCRT is not hard to run, just download from IBM, upload to a PS file, execute. wahlah.. you have your SCRT report.