IBM FlashSystems Assessment tool for IBM i workloads

About this tool:

This tool is meant to help you determine which IBM i jobs candidates that would best benefit from IBM FlashSystems technology. This tool is not a replacement for iDoctor nor the SSD Analyser tools. It is more to be seen as complementary to those tools. Also, it can be used as a standalone without requiring iDoctor nor SSD Analyser to be installed.

Credits:
Fabian Michel, Client Technical Architect – Consulting IT Specialist IBM Systems
Brian Turner, iSeries Technical Consultant, Support – APSU

Contents

• Disclaimer
• How to use the tool
• Sample assessment results
• FAQ

Disclaimer

Target audience

IBM i specialists with storage and performance knowledge.

Disclaimer

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.

How to use the tool

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. Ensure that your system is configured for performance data collection.

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. Once performance data has been collected, select the appropriate performance data from your system.

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. Copy the selected performance data using the memebr name identified in the previous step to a new library for exporting to the FLiP tool.

3.1 CRTLIB LIB(new_lib)
3.2 CPYPFRCOL FROMCOL(mbr_name) FROMLIB(QPFRDATA) TOLIB(new_lib) COLTYPE(*ALL)

4. Extract the selected performance data using System i Navigator and SQL, to a comma separated file (.csv)

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;	

4. Select the desired Flash storage capacity and the skew level (if using EasyTier), and navigate through each tab view results.

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.

Sample assessment results

Summary of selected Jobs

Current total job runtime (min)

Current vs Projected Average Read Latency for selected jobs (ms)

Job Candidates – Projected Read Wait Time Percentage

Current vs projected Total Job Run Time (min)

Current vs Projected Total Job Run Time (%)

Total Job Runtime Improvement Factor

Current vs Projected Average CPU Utilisation for selected jobs

FAQ

Prerequisites, frequent potential issues & their solutions.

• IBM i Navigator for Windows must be installed on the machine where the query will run and must have sufficient access to the IBM i system (at client, BP or IBM) where the performance collection data is located.
• If the IBM i MGTCOL was collected on IBM v6.1, then you’ll have to restore the MGTCOL on an IBM i v7.1 system before creating the performance data and then run the query. Pre-v7.1 IBM i did not have the necessary metrics created.
• Don’t forget to check that JDBC settings for translation are set to translated CCSID 65535, otherwise the query result will be unreadable. Please refer to the Command Line Reference.
• If multiple MGTCOL’s are provided, either create the performance data within separate libraries for each MGTCOL or create an alias on the MGTCOL/ Member that you want to query (see below for more information). It is recommended that you work with only one MGTCOL at a time in separate libraries so that you only need to adapt the SET SCHEMA at the beginning of the query.

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