RC/Migrator for Db2 for z/os: CALC math function in generated JCL of Global changes
Article ID: 54827
Updated On:
Products
Issue/Introduction
How can I apply arithmetic equations to numeric values like Primary and Secondary space quantities on Tablespaces and Indexes during analysis to make changes which would cater to normal increases and decreases in size?
When using RC/Migrator migration, alteration or comparison functions there is often a need to manipulate the values associated to the Tablespace and Indexspace Primary and Secondary quantity, to cater to the needs of the target environment. If a migration is occurring to a production environment then the size of Tablespaces and Indexes will normally be larger than on the development environment. Likewise, one might want to reduce the size for a similar reason. A task like this is very time intensive if there are hundreds of objects involved and, if it is done manually.
Environment
Release: R19, R20
Component: RCC, RCM
Resolution
The Primary and Secondary Quantity is able to be manipulated with arithmetic operations via the CALC function in Symbolic Parameters. The CALC function is placed into the "TO" field of the global change next to the Primary or Secondary Quantity of a Tablespace or Index.
TSSV _ STOGROUP > VCAT (MO) __________________ __________________
TSVS _ VCAT > STOGROUP (MO) __________________ __________________
TSPQ _ PRIMARY QUANTITY *_________________ CALC(%/90*100)____
TSSQ _ SECONDARY QUANTITY *_________________ CALC(%/7-10000)___
TSER _ ERASE RULE __________________ __________________
TSFP _ FREEPAGE __________________ __________________
The CALC function has four operators:
Subtraction(-), Division(/), Multiplication(*) and addition(+).
The use of the percentage (%) character is as a means of substitution for the FROM value into an equation. In the from field an "*" or "%" have the same function.
In the "TO" field of a Global Change.
Example: CALC(%/2%)
This expands to..........CALC(720/2720)
if the FROM value is "720". The first % substitutes to 720 divided by 2720(2 concatenated with 720). Some rules of the CALC function regarding the mechanics of the way the equations are resolved:
- The remainder on divide operations is truncated and not rounded.
- Calculations resulting in a negative number, lose the sign and become positive or the absolute value.
- Calculations resulting in a value larger than seven digits are truncated from the right most end. They are not rounded.
- In DB2 for any page size, if pri/sec qty is greater than 4194304, then it is set to 4194304 when it's executed. In the DDL generated it will still have the larger number
- If a calculation produces a zero (0) as a result then, that will be in the generated DDL. It produces an SQL error when executed.
Examples based on the above rules.
By reading across the page from the left, you can see the Original value, the calculation to be used, then the result of the CALC. Next look closely at the Working to see how values are treated and lastly the Description.
One Step Calculations
This first group illustrates some examples of simple one step equations.
| Value | Calc | Result | Working | Description |
| 1000 | CALC(%*10) | 10000 | 1000 * 10 = 10000 | Multiply 1000 by 10 |
| 8000 | CALC(%/10) | 800 | 8000 / 10 = 800 | Divide 8000 by 10 |
| 500 | CALC(%*2) | 1000 | 500 * 2 = 1000 | Multiply 500 by 2 |
| 500 | CALC(%/2) | 250 | 500 / 2 = 250 | Divide 500 by 2 |
| 1000 | CALC(%*10%) | 1010000 | 1000 * 101000 = 101000000 Result truncated to 1010000 Two digits lost! | Multiply 1,000 by 101,000,000 Here the original value is concatenated with part of the equation. |
| 8000 | CALC(%/10%) | 0 | 8000 / 108000 = 0.07 truncated to 0 A '0' is not valid for pri/sec qty | Divide 8000 by 108,000 Here the original value is concatenated with part of the equation. |
| 500 | CALC(%*2%) | 1250000 | 500 * 2500 = 1250000 | Multiply 500 by 2,500 |
| 500 | CALC(%/2%) | 0 | 500 / 2500 = 0.2 Result truncated to 0 A '0' is not valid for pri/sec qty | Divide 500 by 2,500. Here the original value is concatenated with part of the equation. |
| 1000 | CALC(%+300) | 1300 | 1000 + 300 = 1300 | Add 300 to value |
| 8000 | CALC(%-300) | 7700 | 8000 - 300 = 7700 | Take 300 from value |
| 500 | CALC(%+2%) | 3000 | 500 + 2500 = 3000 | Add 2,500 to value Here the original value is concatenated with part of the equation. |
| 500 | CALC(%-2%) | 2000 | 500 - 2500 = -2000 absolute value = 2000 Sign is lost! | Subtract 2,500 from 500 Here the original value is concatenated with part of the equation |
Multi-step calculations
This group illustrates equations where there is an intermediate result to consider.
| Calc | Result | Working | Description | |
| 720 | CALC(%*10 /100) | 792 | 720 * 110 = 79200, 79200 / 100 = 792 | Increase value by 10% |
| 720 | CALC(%*90/100) | 648 | 720 * 90 = 64800, 64800 / 100 = 648 | Reduce value by 10% This method is used so that precision is not lost due to the truncation of the remainder with a divide. |
| 4252 | CALC(%/90*100) | 4700 | 4252 / 90 = 47.2444444 truncated to 47 , 47 * 100 = 4700 remainder lost! | Divide value by 90 and multiply truncated result by 100 |
| 720 | CALC(720/90*100) | 800 | 720 / 90 = 8, 8 * 100 = 800 | Divide value by 90 and multiply the result by 100 |
| 200 | CALC(%/20-%) | 190 | 200/20 = 10, 10 - 200 = -190 , absolute value = 190 sign is lost! | Divide 200 by 20 and subtract 200. Here the original value is used twice in the equation. |
| 200 | CALC(%/20+%) | 210 | 200/20 = 10, 10 + 200 = 210 | Divide 200 by 20 and add 200 Here the original value is used twice in the equation. |
| 200 | CALC(%/7+1111111) | 1111139 | 200 / 7 = 28.57 Truncate to 28, 28 + 1111111 = 11111139 | Divide 200 by 7 and add 1,111,111 |
| 200 | CALC(%/7*1000000) | 2800000 | 200 / 7 = 28.57 Truncated to 28, 28 *1000000= 2800000 | Divide 200 by 7 and multiply 1,000,000 |
| 200 | CALC(%/7-10000) | 9972 | 200 / 7 = 28.57, Truncated to 28, 28 - 10000 = -9972 , absolute value = 9972 Sign is lost!! | Divide 200 by 7 and subtract 10,000 |
Unrelated value equations
This last group illustrates how an equation which does not rely on the existing value can be used to replace that value.
| Orig. Value | Calc | Result | Working | Description |
| Any | CALC(90) | 90 | Old value changed to 90 | Replace all existing values with 90 |
| Any | CALC((4+4)/2*1)) | 4 | 4+4 = 8, 8 / 2 = 4 , 4 * 1 = 4 | Replace all existing values with the result of this unrelated equation. |
Additional Information
RC/Migrator User Guide:
The CALC Symbolic Parameters.
Feedback
