3
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.
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
-----------------------------------------------------------------------------------------------
I am assuming DB2 v8
A sample COBOL-DB2 project
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:
CUST_ID
CUST_NAME
CUST_HISTORY_CODE
CUST_TYPE_CD
ATM_CARD_BANK_ID
ATM_ID
DT_OF_ATM_TRANSACTION
ATM_TRANSACTION_TYPE_CD
ATM_TRANSACTION_RESULT_CD
CASH_WITHDRAWN
ATM_TRANSACTION_ERR_CD
......................
......................
......................
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)
So if I have to start normalization
1st Normal form- all redundant data groups to be removed and primary keys to be identified
2nd Normal Form- Identify functional dependency on non-repeating data groups (foreign key - primary key relation ship)
3rd Normal Form- All transitive dependencies to be removed
1st Normal form- primary keys will be CUST_ID, CUST_TYPE_CD, ATM_CARD_BANK_ID, ATM_ID, ATM_TRANSACTION_TYPE_CD, ATM_TRANSACTION_RESULT_CD, ATM_TRANSACTION_ERR_CD (7 primary keys)
2rd Normal form- above fields will be foreign keys in other tables
3rd Normal form- yet to come across in this example
So right now 7 different primary key tables have been identified
CREATE TABLE ATM_CUSTOMER(
CUST_ID VARCHAR(10) NOT NULL,
CUST_NAME VARCHAR(30) NOT NULL,
CUST_ACCT_NO DECIMAL(15) NOT NULL,
CUST_TYPE_CD VARCHAR(4) NOT NULL,
CUST_ADDR VARCHAR(50) NOT NULL,
CUST_PH_NO VARCHAR(15) NOT NULL, /* To allow for characters like + in ph nos */
CUST_EMAIL_ID VARCHAR(20),
CUST_ATM_CARD_NO VARCHAR(30) NOT NULL, /*Imagining some banks allow alphanumeric characters in ATM card nos */
ATM_CARD_BANK_ID VARCHAR(4) NOT NULL,
PRIMARY KEY(CUST_ID),
FOREIGN KEY(CUST_TYPE_CD) REFERENCES CUSTOMER_TYPES (CUST_TYPE_CD) ON DELETE CASCADE,
FOREIGN KEY(ATM_CARD_BANK_ID) REFERENCES ATM_BANK_MASTER (ATM_CARD_BANK_ID) ON DELETE CASCADE,
FOREIGN KEY(CUST_ACCT_NO) REFERENCES CUSTOMER_ACCOUNT_MASTER (CUST_ACCT_NO) ON DELETE CASCADE
) IN <schema name / tablespace name>;
CREATE TABLE CUSTOMER_TYPES(
CUST_TYPE_CD VARCHAR(4) NOT NULL,
CUST_TYPE_SHORT_DESC VARCHAR(10),
CUST_TYPE_LONG_DESC VARCHAR(40),
LOAN_ALLOWED CHAR(1) NOT NULL,
PRIMARY KEY(CUST_TYPE_CD) ) IN <schema name / tablespace name>;
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>;
CREATE TABLE ATM_MASTER(
ATM_ID VARCHAR(4) NOT NULL,
ATM_LOCATION_AREA VARCHAR(10) NOT NULL,
ATM_LOCATION_CITY VARCHAR(10) NOT NULL,
ATM_LOCATION_STATE VARCHAR(10) NOT NULL,
ATM_LOCATION_COUNTRY VARCHAR(20) NOT NULL,
ATM_SUPPLIER_ID VARCHAR(4) NOT NULL,
ATM_BANK_NETWORK_ID VARCHAR(4) NOT NULL,
ATM_OTHER_BANK_TRANSACTION_ALLOWED CHAR(1) NOT NULL,
ATM_OPERATIONAL_CHARECTERISTICS_CD VARCHAR(4) NOT NULL, /* Taking into account ATM may be cash-dispense type only or cash-dispenseent -cum- cheque drop facilty type e.t.c */
PRIMARY KEY(ATM_ID),
FOREIGN KEY(ATM_LOCATION_AREA,ATM_LOCATION_CITY,ATM_LOCATI ON_STATE,ATM_LOCATION_COUNTRY) REFERENCES COUNTRY_MASTER (ATM_LOCATION_AREA,ATM_LOCATION_CITY,ATM_LOCATION_ STATE,ATM_LOCATION_COUNTRY)
FOREIGN KEY(ATM_SUPPLIER_ID) REFERENCES ATM_SUPPLIER_MASTER(ATM_SUPPLIER_ID),
FOREIGN KEY(ATM_BANK_NETWORK_ID) REFERENCES ATM_BANK_NETWORK_MASTER (ATM_BANK_NETWORK_ID) ) IN <schema name / tablespace name>;
.................................................. ..
.................................................. ..
.................................................. ..
(Will attach more)
SO the DB design I have come up with is something like
Primary entry point for the COBOL apps- ATM_MASTER table
************** *************************
*ATM_CUSTOMER*----------References----- *CUSTOMER_ACCOUNT_MASTER*
************** |*************************
|
|
|****************
|*CUSTOMER_TYPES*
|****************
|
|
|*****************
|*ATM_BANK_MASTER*
*****************
There are more tables but I will bring them later.
Now for a COBOL code that inserts records into these tables and simultaneously tells me "space / bytes occupied" and "time-taken-to-complete" statistics
Before I put in insert statements let me calculate the length of each record in ATM_MASTER
CUST_ID VARCHAR(10) +
CUST_NAME VARCHAR(30) +
CUST_ACCT_NO DECIMAL(15) +
CUST_TYPE_CD VARCHAR(4) +
CUST_ADDR VARCHAR(50) +
CUST_PH_NO VARCHAR(15) +
CUST_EMAIL_ID VARCHAR(20) +
CUST_ATM_CARD_NO VARCHAR(30) +
ATM_CARD_BANK_ID VARCHAR(4)
(10) + (30) + (15) + (4) + (50) + (15) + (20) + (30) + (4) = 178 bytes
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
................I need to stop here. Will be back soon to complete...................
