IBM i specialists with storage and performance knowledge.
This tool is delivered as-is, with no support and no warranty of any kind about the projected results. As the name says, it is what it is : a tool to give you an idea of those IBM i jobs that are the more likely to take benefits from Flash Technology. It gives you a good overall idea of how much they can benefit from it, but it does not constitute a warranty of any kind about the projected results. It is and remains an estimation, nothing less, nothing more.
These results are estimates and averages based on certain assumptions and conditions. These results are based on measurements and analysis with a variety of workloads, performance characterisations of systems hardware and software, and best performance practices. Please ensure that realistic inputs have been provided for the high-level configuration, the workload definitions, and user options. Actual customer results may differ significantly. This sizing estimate is not an offer or contract. It is a preliminary estimation provided to you for informational purposes only to help you predict a possible hardware configuration for a given workload. ALL INFORMATION PROVIDED HEREIN IS PROVIDED ON AN AS-IS BASIS, WITHOUT ANY WARRANTY OF ANY KIND.
Before starting the tool, an input file must be prepared. Follow the steps below to generate a .csv file with the MGTCOL object.
Please complete the following steps:
1.1 GO PERFORM
1.2 Select Option 2 from the resulting menu – “Collect Performance data”.
1.3 Ensure that the status of the collection is “started” on the resulting display. If not, using Option 2 – “Configure Performance Collection” followed by Option 1 – “Start Performance Collection” will begin collecting the performance data.
2.1 GO PERFORM
2.2 Select Option 7 from the resulting menu – “Display performance data”.
2.3 Ensure that the correct library has been selected on the resulting display (e.g. QPFRDATA or QMPGDATA).
2.4 Select Option 1 – “Select” against one of the available performance collection members to view that member.
2.5 Make a note of the performance member that is showing high disk utilisations (this is the best candidate to show how IBM Flashsystems (r) can improve your configuration.
3.1 CRTLIB LIB(new_lib)
3.2 CPYPFRCOL FROMCOL(mbr_name) FROMLIB(QPFRDATA) TOLIB(new_lib) COLTYPE(*ALL)
4.1 Start “System I Navigator” and connect to your system.
4.2 Expand Database and then right click on the database with the same name as your system.
4.3 In the drop down menu select “Run SQL Scripts”.
4.4 Select the connection menu item and change the JDBC Settings.
4.5 Select the Translation TAB and tick the option “Translate CCSID 65535”, then click “connect”.
4.6 Select the options menu item and tick the option to “Allow Save Results”.
4.7 In the free format text window, cut and paste the SQL provided ensuring that the library name is changed to the library you created in the previous step.
4.8 Select the run menu item and Select All.
4.9 Right Click within the resulting data display and select “Save Results” from the drop down menu shown.
4.10 Select a location and name of the .csv file.
SET SCHEMA library_name;
SELECT
CASE
WHEN JBTYPE = 'V'
THEN JBNAME
ELSE TRIM(JBNAME) || '/' || TRIM(JBUSER) || '/' || TRIM(JBNBR) || ' - ' || TRIM(JBTHID)
END AS "Job Name",
DECIMAL(SUM(BIGINT(WT.JWTM05) + BIGINT(WT.JWTM06)) * .000001, 7, 2) AS "Disk Read Time (s)",
DECIMAL(SUM(BIGINT(WT.JWTM07) + BIGINT(WT.JWTM08) + BIGINT(WT.JWTM09) + BIGINT(WT.JWTM10)) * .000001, 7, 2) AS "Disk non-Read Time (s)",
DECIMAL(SUM(BIGINT(MI.JBCPU)) * .001, 12, 2) AS "CPU time (s)",
CASE
WHEN SUM(BIGINT(WT.JWCT05)+BIGINT(WT.JWCT06)) <= 0
THEN NULL
ELSE DECIMAL(DECIMAL(ROUND(SUM(BIGINT(JWTM05) + BIGINT(JWTM06)) * .001, 3), 15, 3) / SUM(BIGINT(JWCT06) + BIGINT(JWCT05)), 8, 3)
END AS "Average Disk Read Resp. Time (msec)",
CASE
WHEN DECIMAL(ROUND(SUM(JBCPU) * .001, 3), 12, 3) <= 0
THEN NULL
ELSE BIGINT(ROUND(DECIMAL(ROUND(SUM(BIGINT(JWTM05) + BIGINT(JWTM06)) * .000001, 3), 12, 3) / DECIMAL(ROUND(SUM(JBCPU) * .001, 3), 12, 3), 0))
END AS "Disk Read Time vs CPU Time ratio", --Note - I replaced * .000001 by .001 to have msec instead of sec
DECIMAL(SUM(BIGINT(WT.JWTM30)) * .000001, 7, 2) AS "Idle Time (s)",
DECIMAL(SUM(BIGINT(WT.JWTM04) + BIGINT(WT.JWTM11) + BIGINT(WT.JWTM14) + BIGINT(WT.JWTM15) + BIGINT(WT.JWTM16) + BIGINT(WT.JWTM17) + BIGINT(WT.JWTM18) + BIGINT(WT.JWTM19) + BIGINT(WT.JWTM20) + BIGINT(WT.JWTM21) + BIGINT(WT.JWTM22) + BIGINT(WT.JWTM24) + BIGINT(WT.JWTM25) + BIGINT(WT.JWTM26) + BIGINT(WT.JWTM27) + BIGINT(WT.JWTM28) + BIGINT(WT.JWTM29) + BIGINT(WT.JWTM30) + BIGINT(WT.JWTM31) + BIGINT(WT.JWTM32) + BIGINT(WT.JWTM12) + BIGINT(WT.JWTM13)) * .000001, 7, 2) AS "Other Wait Time (s)",
DECIMAL(SUM(BIGINT(WT.JWTM01) + BIGINT(WT.JWTM02) + BIGINT(WT.JWTM05) + BIGINT(WT.JWTM06) + BIGINT(WT.JWTM07) + BIGINT(WT.JWTM08) + BIGINT(WT.JWTM09) + BIGINT(WT.JWTM10) + BIGINT(WT.JWTM04) + BIGINT(WT.JWTM11) + BIGINT(WT.JWTM12) + BIGINT(WT.JWTM13) + BIGINT(WT.JWTM14) + BIGINT(WT.JWTM15) + BIGINT(WT.JWTM16) + BIGINT(WT.JWTM17) + BIGINT(WT.JWTM18) + BIGINT(WT.JWTM19) + BIGINT(WT.JWTM20) + BIGINT(WT.JWTM21) + BIGINT(WT.JWTM22) + BIGINT(WT.JWTM24) + BIGINT(WT.JWTM25) + BIGINT(WT.JWTM26) + BIGINT(WT.JWTM27) + BIGINT(WT.JWTM28) + BIGINT(WT.JWTM29) + BIGINT(WT.JWTM30) + BIGINT(WT.JWTM31) + BIGINT(WT.JWTM32)) * .000001, 7, 2) AS "Total Time (s)",
--Note - This column has been added as Disk_vs_Total_Time ratio to help filtering the best job candidates
DECIMAL(
DECIMAL(SUM(BIGINT(WT.JWTM05) + BIGINT(WT.JWTM06)) * .000001, 7, 2)
/
DECIMAL(SUM(BIGINT(WT.JWTM01) + BIGINT(WT.JWTM02) + BIGINT(WT.JWTM05) + BIGINT(WT.JWTM06) + BIGINT(WT.JWTM07) + BIGINT(WT.JWTM08) + BIGINT(WT.JWTM09) + BIGINT(WT.JWTM10) + BIGINT(WT.JWTM04) + BIGINT(WT.JWTM11) + BIGINT(WT.JWTM12) + BIGINT(WT.JWTM13) + BIGINT(WT.JWTM14) + BIGINT(WT.JWTM15) + BIGINT(WT.JWTM16) + BIGINT(WT.JWTM17) + BIGINT(WT.JWTM18) + BIGINT(WT.JWTM19) + BIGINT(WT.JWTM20) + BIGINT(WT.JWTM21) + BIGINT(WT.JWTM22) + BIGINT(WT.JWTM24) + BIGINT(WT.JWTM25) + BIGINT(WT.JWTM26) + BIGINT(WT.JWTM27) + BIGINT(WT.JWTM28) + BIGINT(WT.JWTM29) + BIGINT(WT.JWTM30) + BIGINT(WT.JWTM31) + BIGINT(WT.JWTM32)) * .000001, 7, 2)
* 100, 7, 1)
as "Disk vs Total Time"
--Note - End of column DISK_vs_TOTAL_TIME
FROM
QAPMJOBMI AS MI
INNER JOIN
QAPMJOBWT AS WT
ON
(WT.JWTDE = MI.JBTDE) AND (WT.INTNUM = MI.INTNUM)
WHERE
((MI.JBTYPE <> 'V'))
GROUP BY
CASE
WHEN JBTYPE = 'V'
THEN JBNAME
ELSE TRIM(JBNAME) || '/' || TRIM(JBUSER) || '/' || TRIM(JBNBR) || ' - ' || TRIM(JBTHID)
END
ORDER BY
2 DESC,
CASE
WHEN JBTYPE = 'V'
THEN JBNAME
ELSE TRIM(JBNAME) || '/' || TRIM(JBUSER) || '/' || TRIM(JBNBR) || ' - ' || TRIM(JBTHID)
END
--Note - This has been added to reduce the returned data set
FETCH FIRST 1000 ROWS ONLY;
Easy Tier
Where both SSD’s and Hard disks drives are present within a system configuration, Easytier will transfer frequently accessed data to SSD for faster access.
Skew Level
The skew level generally refers to how evenly or unevenly small and random I/Os are spread over the total capacity.
A light skew level refers to a very small percentage of the capacity containing small/ random I/Os. At this level, any upgrades are uneconomical, as there is an insignificant difference between the impact of busiest extents against least active.
Within heavily skewed workloads, a large percentage of small/ random I/Os are transferred to SSDs to free up enterprise drives. The IOs would generally take up a small portion of the data on the SSD, where within this instance a small upgrade to faster hardware will yield significantly improved performance.
It is recommended that any potential upgrades to your configuration specify between 3 to 10% of total usable capacity in SSD for safety reasons.
Multiple MGTCOL (members) – create ALIAS
You can create an alias using the create alias command:
CREATE ALIAS mylibrary/myAlias FOR memberLibrary/memberFile(memberName)
This will allow you to run sql against that member using the alias like you would do for any other file:
SELECT * FROM myLibrary/myAlias
It is important to note that the alias will remain after your session as it is not temporary. If you do not require the alias to remain after the session, either create the alias in QTEMP or explicitly drop the alias once you are done with it:
IBM i Flash Assessment Tool – jobs candidate query.sql Right click and “save link as”.
DROP ALIAS myLibrary/myAlias