IBM – UDB – The “sysibmadm” schema

Background

Post configuring DBeaver to enable IBM DB2/UDB Instance access, wanted to explore Queries that will help my understanding.

Code

sysibmadm.applications

SQL


/*
	APPLICATIONS administrative view - Retrieve connected database application information
    https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0022011.html
 */
SELECT
           tblApp.DB_NAME AS "database"
         , tblApp.AUTHID "userID"
         , tblApp.APPL_NAME AS "applicationName"
         , tblApp.APPL_ID AS "applicationID"
         , tblApp.CLIENT_NNAME AS "clientHost"
         , tblApp.TPMON_CLIENT_WKSTN AS "clientWorkStation"
         , tblApp.TPMON_CLIENT_APP AS "clientApp"
         , tblApp.CLIENT_PLATFORM AS "clientPlatform"
         , tblApp.CLIENT_PROTOCOL AS "clientProtocol"
         , tblApp.APPL_STATUS AS "applStatus"

--         , tblApp.*

FROM SYSIBMADM.APPLICATIONS tblApp

ORDER by
           tblApp.DB_NAME ASC
         , tblApp.AUTHID ASC
		 , tblApp.APPL_NAME

Output

SYSIBMADM.APPLICATIONS_20180817_0102PM

sysibmadm.snaptab

SQL


SELECT 

          CURRENT_SERVER AS "Database"

       , tblSNT.TABSCHEMA AS "Schema"

       , tblSNT.TABNAME  AS "Table"       

       , tblSNT.TAB_TYPE AS "TableType"

       , tblSNT.TAB_FILE_ID AS "FileIDOfSnapTable"

	  -- , tblSST.FID AS "FileIDOfTable"

       , tblTS.TBSP_NAME AS "TSNameInTableUtil"

	   , tblSST.TBSPACE AS "TSNameDataInSYSTable"

	   , tblSST.INDEX_TBSPACE AS "TSNameIndexInSYSTable"	   

       , tblTS.TBSP_TYPE AS "TSType"

       , tblSNT.DATA_OBJECT_PAGES AS "PagesDO"

       , tblSNT.INDEX_OBJECT_PAGES AS "PagesIO"

       , tblSNT.LOB_OBJECT_PAGES   AS "PagesLOB"    

       , tblSNT.ROWS_READ AS "RowsRead"

       , tblSNT.ROWS_WRITTEN AS "RowsWritten"

       , tblSNT.OVERFLOW_ACCESSES AS "OverflowAccess"

       , tblSNT.PAGE_REORGS AS "PagesReOrg"

FROM   sysibmadm.snaptab tblSNT

INNER JOIN SYSIBM.SYSTABLES tblSST

       on  tblSNT.TABSCHEMA = tblSST.CREATOR

       and tblSNT.TABNAME = tblSST.NAME       

LEFT OUTER JOIN SYSIBMADM.TBSP_UTILIZATION tblTS

        ON tblSNT.TAB_FILE_ID = tblTS.TBSP_ID

ORDER BY

          ( tblSNT.ROWS_READ + tblSNT.ROWS_WRITTEN ) DESC

       ,  tblSNT.TABSCHEMA

       , tblSNT.TABNAME

Output

sysibmadm.snaptab.20180817.1128AM.PNG

SYSIBMADM.TOP_DYNAMIC_SQL

SQL


SELECT 

       -- ( tblTDS.NUM_EXECUTIONS * tblTDS.AVERAGE_EXECUTION_TIME_S ) AS "weight"

         tblTDS.AVERAGE_EXECUTION_TIME_S AS "AverageExecTimeInSec"

       , tblTDS.NUM_EXECUTIONS AS "# of Executions"

       , tblTDS.STMT_TEXT AS "SQL"

       , tblTDS.STMT_SORTS AS "StmtSorts"

       , tblTDS.SORTS_PER_EXECUTION AS "SortsPerExec"      

FROM   SYSIBMADM.TOP_DYNAMIC_SQL tblTDS

ORDER BY
         ( tblTDS.NUM_EXECUTIONS * tblTDS.AVERAGE_EXECUTION_TIME_S ) DESC
       , tblTDS.AVERAGE_EXECUTION_TIME_S DESC
       , tblTDS.NUM_EXECUTIONS DESC

FETCH FIRST 100 ROWS ONLY

;

Output

SYSIBMADM.TOP_DYNAMIC_SQL_20180817_1234PM

SYSIBMADM.ENV_SYS_RESOURCES

SQL


SELECT  * 

FROM  SYSIBMADM.ENV_SYS_RESOURCES tblSR

WHERE  tblSR.NAME IN
(
           'CPU_TOTAL'
         , 'CPU_SPEED'
         , 'MEMORT_TOTAL'
         , 'MEMORY_FREE'
         , 'CPU_USAGE_TOTAL'

)

;

Output

SYSIBMADM.ENV_SYS_RESOURCES_20180817_0113PM

SYSIBMADM.DBPATHS

SQL


/*
 * DBPATHS administrative view - Retrieve database paths
 * https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.5.0<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>/com.ibm.db2.luw.sql.rtn.doc/doc/r0022037.html
*/
SELECT 

	     tblDP.PATH
	  ,  tblDP.TYPE

FROM SYSIBMADM.DBPATHS tblDP

ORDER BY
	     tblDP.PATH
	  ,  tblDP.TYPE

Output

sysibmadm.dbpaths_20180817_0142PM

SYSIBMADM.ENV_SYS_RESOURCES

SQL


SELECT  * 

FROM  SYSIBMADM.ENV_SYS_RESOURCES tblSR

WHERE  tblSR.NAME IN
(
           'CPU_TOTAL'
         , 'CPU_SPEED'
         , 'MEMORT_TOTAL'
         , 'MEMORY_FREE'
         , 'CPU_USAGE_TOTAL'

)

;

Output

SYSIBMADM.ENV_SYS_RESOURCES_20180817_0113PM

SYSIBMADM.TBSP_UTILIZATION

SQL


SELECT 

          CURRENT_SERVER AS "Database" 

		, tblTBU.TBSP_NAME AS "TSNAME"

		, tblTBU.TBSP_ID AS "TSID"		  

		, tblTBU.TBSP_TYPE AS "TSType"

		, tblTBU.TBSP_CONTENT_TYPE AS "TSContentType"	

		, tblTBU.TBSP_TOTAL_SIZE_KB/1024 AS "TotalSizeMB"

		, tblTBU.TBSP_USED_SIZE_KB/1024 AS "SizeMB"		

		, tblTBU.TBSP_FREE_SIZE_KB/1024 AS "FreeMB"

		, tblTBU.TBSP_UTILIZATION_PERCENT AS "%Util"		

		, tblTBU.TBSP_INCREASE_SIZE AS "TBIncreaseSize"

		, tblTBU.TBSP_INCREASE_SIZE_PERCENT AS "TBIncreaseSize%"

		-- , tblTBU.*

FROM   SYSIBMADM.TBSP_UTILIZATION tblTBU

ORDER BY

          tblTBU.TBSP_TOTAL_SIZE_KB DESC

		, tblTBU.TBSP_USED_SIZE_KB DESC         

        , tblTBU.TBSP_NAME ASC

Output

SYSIBMADM.TBSP_UTILIZATION_20180817_0200PM.PNG

SYSIBMADM.SNAPAPPL

SQL


SELECT 

         tblAppInfo.PRIMARY_AUTH_ID AS "AuthorizationID"

        , sum(tblAppSnap.ROWS_READ) AS "RowsRead"

        , sum(tblAppSnap.ROWS_WRITTEN) AS "RowsWritten"

FROM   SYSIBMADM.SNAPAPPL tblAppSnap

INNER JOIN SYSIBMADM.SNAPAPPL_INFO tblAppInfo

	on tblAppSnap.AGENT_ID = tblAppInfo.AGENT_ID

GROUP BY
         tblAppInfo.PRIMARY_AUTH_ID

ORDER BY
     sum
     	(
     		  tblAppSnap.ROWS_READ
     		+ tblAppSnap.ROWS_WRITTEN
 		) desc

Output

SYSIBMADM.SNAPAPPL_20180817_0208PM.PNG

SYSIBMADM.SNAPAPPL_INFO

SQL


SELECT 

         tblAppInfo.PRIMARY_AUTH_ID
       , tblAppInfo.APPL_NAME
       , tblAppInfo.DB_NAME
       , tblAppInfo.CLIENT_NNAME

       , tblAppSnap.ROWS_READ
       , tblAppSnap.ROWS_WRITTEN       

       , tblAppInfo.TPMON_CLIENT_WKSTN
       , tblAppInfo.TPMON_CLIENT_APP
       , tblAppInfo.CLIENT_PLATFORM
       , tblAppInfo.CLIENT_PROTOCOL

       , tblAppInfo.IS_SYSTEM_APPL

FROM   SYSIBMADM.SNAPAPPL tblAppSnap

INNER JOIN SYSIBMADM.SNAPAPPL_INFO tblAppInfo

	on tblAppSnap.AGENT_ID = tblAppInfo.AGENT_ID

ORDER BY

     (
     	  tblAppSnap.ROWS_READ

     	+ tblAppSnap.ROWS_WRITTEN
     )
     desc

Output

SYSIBMADM.SNAPAPPL_INFO.20180817_0220PM.PNG

 

Source Code

GitHub

DanielAdeniji/IBMUDBSchemaSYSIBMADM
Link

DBeaver – Database Connection Configuration – IBM – DB2 / UDB JDBC Driver

Background

As always coming back to DBeaver.

This time to connect to a UDB Instance running on Linux.

BTW, UDB means Universal Database.

UDB JDBC Driver

Artifacts

DB2 JDBC Driver Versions and Downloads

The URL for DB2 JDBC Drivers is Link.

Here is a current snapshot.

artifacts_db2drivers_20180816_1236PM

v11.1 FP0 ( GA )

We chose the v11 FP0 ( GA ) which is a baseline install; specifically 4.21.29.

Others might choose v11 M3 FP3; which includes the latest patches.

The URL is Link.

Here is a screen shot:

artifacts_db2drivers_v11_20180816_0110PM

IBM Data Server Driver for JDBC and SQLJ ( JCC Driver )

As all we need is the JDBC Driver, we chose “IBM Data Server Driver for JDBC and SQLJ (JCC Driver)“.

One needs membership account to actually download, so please login in with one if you have already registered.  Else, please create one, as it is free.

Download

In short, we downloaded “IBM Data Server Driver for JDBC and SQLJ (JCC Driver)“.

Extracted

Image

zip_extracted_20180816_0216PM.PNG

Textual

  1. db2jcc.jar
    • JDBC v3.0
  2. db2jcc4.jar
    • JDBC 4.0

Preparation

Network

Port Number

Let us determine the Network Port Number that our DB/2 Instance is listening on.

db2 get dbm cfg

Syntax


db2 get dbm cfg

Sample


db2 get dbm cfg | grep SVCE

Output

db2getdbmcfg_20180816_0230PM.PNG

Databases

Outline

Let us determine the list of databases that are available on our UDB Instance.

db2 list database directory

Syntax


 db2 list database directory

Sample


 db2 list database directory | grep "Database name"

Output

listDatabases_20180816_0242PM.PNG

 

DBeaver

Database

Connections

Steps

Select new connection type
Image

SelectNewConnectionType_20180816_0644AM.PNG

Textual
  1. DB2
    • DB2 LUW
Driver settings – Download driver files
Image

downloadDriverFiles_20180816_0647AM.PNG

Textual
  1. Click on the “Add JARs” button
Driver settings – Edit Driver – Add File – Post
Image

EditDriver_AddFile_After_20180816_0647AM.PNG

Textual
  1. JAR file “db2jcc4.jar” added
Create new connection – DB2 Connection Settings – Tab – General
Image

EditDriver_Tab_Database_20180816_0709AM ( Edited ).PNG

Textual
  1. Host
  2. Port
  3. Database
  4. username
  5. Password
Create new connection – DB2 Connection Settings – finish connection creation
Image

finishConnection_20180816_0719AM ( Edited ).PNG

 

References

  1. IBM
    • Home > DB2 for Linux UNIX and Windows 9.5.0 > Database administration > Administrative interfaces > Administrative SQL routines and views > Supported routines and views > Snapshot routines and views
      • APPLICATIONS administrative view – Retrieve connected database application information
        Link

 

jruby – Get Version Information

Background

As part of a troubleshooting session that I will be undertaken I wanted to start getting familiar with what jruby has in terms of API for discovering the Java Version that it is running under.

Code

Ruby

Version.rb


require 'date'

require 'java'

require 'rbconfig'
include RbConfig

require 'io/console'

java_import java.lang.System

class Version

    # ruby class variables are prefixed with '@@' .
    @@javaRTVersion = "";
    @@rubyPlatform = "";
    @@hostOS = "";
    @@acquired = false;

    #Constructor
    def initialize()

    end

    def self.acquire

        @@javaRTVersion = System.getProperties["java.runtime.version"]

        @@rubyPlatform = RUBY_PLATFORM

        @@hostOS = CONFIG['host_os']

    end 

    # hostOS
    def self.hostOS

        if (@@acquired == false)

            self.acquire()

            @@acquired = true

        end

        return @@hostOS

    end 

    # java runtime version
    def self.javaRTVersion

        if (@@acquired == false)

            self.acquire()

            @@acquired = true

        end

        return @@javaRTVersion

    end     

    # the getter is defined to return the class variable when called
    def self.rubyPlatform

        if (@@acquired == false)

            self.acquire()

            @@acquired = true

        end

        return @@rubyPlatform

    end 

end

def waitForKeyPress

    puts
    puts

    puts "Press enter to continue"

    STDIN.getch()

end 

objV = Version.new()

# Access it's attributes.
puts
puts "hostOS is " + Version.hostOS
puts "javaRTVersion is " + Version.javaRTVersion
puts "rubyPlatform is " + Version.rubyPlatform

waitForKeyPress()

Command File

Objective

Ran again JDK 1.8, 1.9 ( v9 ) and 1.10 ( v10 ).

initiate.cmd


setlocal

REM SET JAVA_HOME to Version 1.8
rem set "JAVA_HOME=C:\Program Files\Java\jdk1.8.0_181"
rem set "JAVA_HOME=C:\Program Files\Java\jdk-9.0.4"
set "JAVA_HOME=C:\Program Files\Java\jdk-10.0.2"

set "JRUBY_BIN=C:\Downloads\jruby\v9.2\zip\jruby-dist-9.2.0.0-bin\jruby-9.2.0.0\bin"

set "_app=Version.rb"

%JRUBY_BIN%\jruby.bat "%_app%"

endlocal

Output

JDK 1.8

javaVersion_run_1DOT8_20180816_0435AM

JDK 1.9

Image

javaVersion_run_1DOT9_20180816_0439AM

Textual


WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by org.jruby.runtime.encoding.EncodingService to field java.io.Console.cs
WARNING: Please consider reporting this to the maintainers of org.jruby.runtime.encoding.EncodingService
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release

hostOS is mswin32
javaRTVersion is 9.0.4+11
rubyPlatform is java

JDK 1.10

Image

javaVersion_run_1DOT10_20180816_0445AM

Textual


WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by org.jruby.runtime.encoding.EncodingService to field java.io.Console.cs
WARNING: Please consider reporting this to the maintainers of org.jruby.runtime.encoding.EncodingService
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release

hostOS is mswin32
javaRTVersion is 10.0.2+13<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;">&#65279;</span>
rubyPlatform is java

jruby – Sample App – Classes – blackBook

Background

Now that we have jruby installation out of the way let us create a simple app that creates a class.

BTW, our jruby Installation post is here.

 

Code

blackbook.rb


###############################################
#Foundational Sample Script
#https://gist.github.com/dougal/24876
###############################################
require 'date'

class Blackbook

    #Declare Accessor Methods   
    attr_accessor :name, :relationship, :dateAdded

    
    # ruby class variables are prefixed with '@@' .
    # set number of entries
    @@iNumberofEntries = 0;
    
    #Constructor
    def initialize(name, relationship, dateAdded)
    
        @name = name
        @relationship = relationship
        @dateAdded = dateAdded
        
        #Increment number of entries
        @@iNumberofEntries = @@iNumberofEntries + 1
        
    end
  
    # Class Method numberofEntries
    # Returns Class Variable @@iNumberofEntries
    def self.numberofEntries
    
        return @@iNumberofEntries
        
    end  
    
end

# Get Current Date
dtNow = Time.now

# Date Formats
dateFormat="%b %d %Y"
dateFormatYYYYMMDD="YYYYMMDD"

# Create the new instance ( Prince Charles)
dob = "19481114"
dobCharles = Date.parse(dob);
entryCharles = Blackbook.new('Charles', 'Prince', dobCharles)

# Access it's attributes.
puts "Entry Name is " + entryCharles.name
puts "Relationship is " + entryCharles.relationship
puts "Date Added is " + entryCharles.dateAdded.strftime(dateFormat);
puts ""

# Create the new instance ( Lady Diana )
dob = "19610701"
dobDiana = Date.parse(dob);
entryDiana = Blackbook.new('Diana', 'Princess', dobDiana)

# Access it's attributes.
puts "Entry Name is " + entryDiana.name
puts "Relationship is " + entryDiana.relationship
puts "Date Added is " + entryDiana.dateAdded.strftime(dateFormat);
puts ""

#Get Number of entries
strNumberofEntries = Blackbook.numberofEntries.to_s
puts "Number of entries in black book " + strNumberofEntries

invoke.cmd


setlocal

REM SET JAVA_HOME to Version 1.8
set "JAVA_HOME=C:\Program Files\Java\jdk1.8.0_181"
rem set "JAVA_HOME=C:\Program Files\Java\jdk-9.0.4"
REM set "JAVA_HOME=C:\Program Files\Java\jdk-10.0.2"

set "JRUBY_BIN=C:\Downloads\jruby\v9.2\zip\jruby-dist-9.2.0.0-bin\jruby-9.2.0.0\bin"

set "_app=blackbook.rb"

%JRUBY_BIN%\jruby.bat "%_app%"

endlocal

output

jruby – Installation and Hello World App

Background

Because of a side trek down the ElasticSearch road, specifically Logstash, wanted to develop a more congruent understanding of jruby.

Here we go.

jruby

per-requisite

Java JDK

Java JDK v1.8

Please download and ensure that Java’s JDK version 1.8 is downloaded and installed.

BTW, here is the link to Java JDK v1.8.

 

Artifact

jruby is available here.

Here is a current screen shot.

Image

 

Tabulate

Here is a quick break down:

Package OS Platform Packaging Installation
JRuby 9.2.0.0 Binary .tar.gz Linux Binary Unzip/Extract
JRuby 9.2.0.0 Binary .zip Windows Binary Unzip/Extract
JRuby 9.2.0.0 Windows Executable Windows Install4j Wizard  – x32 bit Run Installer
JRuby 9.2.0.0 Windows Executable (x64) Windows Install4j Wizard – x64 bit Run Installer
JRuby 9.2.0.0 Source .zip Multi-Platform Source Code Extract
JRuby 9.2.0.0 Complete .jar Multi-Platform Jar File Copy
JRuby-jars 9.2.0.0 .gem

 

Explanation

  1. Windows Executable
    • Most MS Windows users should go with the Windows Executable
    • Depending on OS bitness, please go with 32 or 64 bit
  2. Binary
    • Infrequent or Evaluation users should go with Binary Zip
  3. Source
    • Developers should go with the Source Code
  4. Complete Jar
    • OEM

 

Choice

We went the “Binary Zip” Route.

Download

Downloaded “jruby-dist-9.2.0.0-bin.zip

 

Extract

Using 7-Zip extracted the zip file.

Here is what our extracted folders look like.

 

Coding

Let us write a very simple script.

Hello World

Code

Ruby Source Code


# Beginner Ruby App
# https://en.wikibooks.org/wiki/Ruby_Programming/Hello_world
puts 'Hello, world!'

# Query Environment Variables
userDomain=ENV["USERDOMAIN"]
username=ENV["USERNAME"]

# Concatenate User Domain and Username
yourName=userDomain + '\\' + username

# Display Output
puts 'Your Full Username is ' + yourName



 

Invoke

Command File to initiate jruby script.


setlocal

REM SET JAVA_HOME to Version 1.8
set "JAVA_HOME=C:\Program Files\Java\jdk1.8.0_181"
rem set "JAVA_HOME=C:\Program Files\Java\jdk-9.0.4"
rem set "JAVA_HOME=C:\Program Files\Java\jdk-10.0.2"

set "JRUBY_BIN=C:\Downloads\jruby\v9.2\zip\jruby-dist-9.2.0.0-bin\jruby-9.2.0.0\bin"

set "_app=helloWorld.rb"

%JRUBY_BIN%\jruby.bat "%_app%"

endlocal


 

Output

Here is the output of invoking the script.

Linux – Profiling System Resource Utilization – Day 1

Background

Since we are already talking about profiling Applications, let us quickly highlight some of the available tools for measuring Application Resource Utilization on a Linux box.

Terminology

  1. Memory
    • buffer
      • Amount of memory that is buffered. In simple words, buffers are used for caching of filesystem metadata (permissions, location, etc.) and tracking in-flight pages. ( Link )
    •  cache
      • Amount of memory that is cached. Cache contains data that has already been read from the disk and is kept in memory for possible future use, f.e a pdf file or web browser pages. ( Link )

Outline

  1. top ( Link )
  2. sar ( Link )
  3. free ( Link )
  4. iostat ( Link )

top

The “top” command lists the processes running on a Linux box.

top stands for “Table of Processes“.

Sample Reading

MySQL

Here are sample outputs from a MySQL Box.

Sample #1
Image

top_linux_mysql_20180812_0531PM.PNG

Output
  1. Our box has been up for 14 days
  2. CPU
    • User :- 0.2%
    • System :- 0.5%
    • Nice :- 0%
    • Idle :- 99.2%
  3. Memory
    • Mem: 1872716k total, 1598392k used, 274324k free, 204700k buffers
      • Total :- 1872716k ( 1872 MB / 1.82 GB )
      • Used :- 1598392k ( 1598 MB / 1.60 GB )
      • Free :- 274324k ( 274 MB )
  4. Swap
    • Swap: 1048572k total, 87176k used, 961396k free, 578420k cached
      • Total :-1048572K ( 1048.572 MB / 1.05 GB )
      • Used :- 87176k ( 87 MB  )
      • Free :- 961396k ( 961 MB )
  5. Processes
    • Process /MySQL
      • PID ( Process ID )
      • USER
        • MySQL
      • PR ( User Priority )
        • 20
      • NI ( Nice )
        • 0
      • VIRT ( Virtual Memory )
        • 1789 MB
          • Our database server, MYSQL, requested 1800 MB
      • RES ( Resident Memory )
        • 568 MB
          • Our database server, MYSQL, is using actual memory of 568 MB
      • SHR ( How much of Total Virtual Memory this process is sharing with others)
        • 6048
        • What is the measurement?
      • S ( Process State )
      • %CPU
      • %MEM
      • TIME+
      • COMMAND

Functionalities

Outline

  1. Order By

Order By

To review or change the ordering please press the Shift-O button.

The “Current Sort Field” window appears.

Image

top_sort_20180813_0316AM

Please review the available sort fields and press the corresponding character.

sar

Definition

The sar common stands for “System Activity Report“.

Options

Memory-rMemory free, usedsar -r

Objective Option Description Sample
Processor Cumulative -p Processor Cumulative sar -P ALL
Processor Detail -P Process All or Specific sar -P ALL
Processor Run Queue and Load Average -q Reports run queue and load average sar -q
Swap Space Utilization -S Swap space utilization sar -S
Storage I/O -b Storage IO Transactions ( Count & Bytes) sar -b
Storage I/O @ Block Device Level -d IO Transactions ( Count & Bytes) sar -d
Network -n Network sar -n DEV

Scenarios

Review current Days report.

Outline

Get Stats for current day.

Code

sar

Output

linux_db2_20180813_0338AM.PNG

Explanation
  1. CPU
    • All available CPU
  2. %user
    • User applications CPU Percentile usage
  3. %nice
    • CPU Percentile usage attributed to applications running under “nice” mode
  4. %System
    • % CPU by system applications ( Kernel, device drivers )
  5. %iowait
    • % CPU Idle waiting for I/O completion
    • References
      • Red Hat Enterprise Linux – What Is the Meaning of %iowait as Reported by Utilities Such as sar or top?
        Link

Review past reports

Outline

Get Stats for the 7th day of the current Month

Code

sar -p -f /var/log/sa/sa07

Output

linux_db2_20180812_0957PM

Review CPU Utilization full and individualized

Outline

Get Stats for current day.

Code

sar -P ALL 

Output

linux_db2_20180814_0341AM

Explanation
  1. CPU
    • All available CPU
  2. %user
    • User applications CPU Percentile usage
  3. %nice
    • CPU Percentile usage attributed to applications running under “nice” mode
  4. %System
    • % CPU by system applications ( Kernel, device drivers )
  5. %iowait
    • % CPU Idle waiting for I/O completion
    • References
      • Red Hat Enterprise Linux – What Is the Meaning of %iowait as Reported by Utilities Such as sar or top?
        Link

Review Memory Utilization

Outline

Get Stats for current day.

Code

sar -r 

Output

sar-r-20180814-0347AM.PNG

Explanation
  1. kbmemfree
    • Free Memory in KB
  2. kbmemused
    • Used Memory in KB
  3. %memused
    • Percentile ( %) of Memory Used
  4. kbbuffers
    • Buffers for disk writes
  5. kbcached
    • Used as cached for disk reads
  6. kbcommit
    • Amount of Memory needed for current workload.  Total Memory needed to ensure that we do not run out of Memory.
  7. %commit
    • % Total Memory that is actually in use per current workload

Review Swap Space Utilization

Outline

Get Swap Space for current day.

Code

sar -S 

Output

sar-S-20180814-0755AM.PNG

Explanation
  1. kbswpfree
    • Free Swap Space in KB
  2. kbswpused
    • Used Swap Space in KB
  3. %swpused
    • Percentile ( %) of Swap Space Used
  4. kbswpcad
    • Amount of cached swap memory in kilobytes. This is memory that once was swapped out, is swapped back in but still also is in the swap area (if memory is needed it doesn’t need to be swapped out again because it is already in the swap area. This saves I/O). ( Link )
  5. %swpcad
    • Percentage of cached swap memory in relation to the amount of used swap space. ( Link )

Review I/O Transfer Rates

Outline

Get I/O Transfer rates and read and writes blocks.

Code

sar -d -f /var/log/sa/sa07 

Output
sar-d_20180814_0924AM.PNG
Explanation
  1. DEV
  2.  tps
    • Total number of transfers per second that were issued to physical devices. A transfer is an I/O request to a physical device. Multiple logical requests can be combined into a single I/O request to the device. A transfer is of indeterminate size. ( Link )
  3. rd_sec/s
    • Number of read requests per second
  4. wr_sec/s
    • Number of writes per second
  5. avgrq-sz
    • The average size (in sectors) of the requests that were issued to the device. ( Link )
  6. avgqu-sz
    • Average Queue Size
    • The average queue length of the requests that were issued to the device. ( Link )
  7. await
    • The average time (in milliseconds) for I/O requests issued to the device to be served. This includes the time spent by the requests in queue and the time spent servicing them. ( Link )
  8. svctm
    • The average service time (in milliseconds) for I/O requests that were issued to the device. Warning! Do not trust this field any more. This field will be removed in a future sysstat version. Percentage of CPU time during which I/O requests were issued to the device (bandwidth utilization for the device). Device saturation occurs when this value is close to 100%. ( Link )
  9. %util
    • Percentage of CPU time during which I/O requests were issued to the device (bandwidth utilization for the device). Device saturation occurs when this value is close to 100%. ( Link )
Outline

Get I/O Transfer rates and read and writes blocks and in print friendly mode.

  1. sar -dp
    • Option
      • d is disk
      • p is print friendly
    • Crediting
      • Community Home Servers and Operating Systems Operating System – Linux System Administration “sar -d” devices – how to map to /dev/sdNNN device…
        Link
Code

sar -dp -f /var/log/sa/sa07 

Output

sar-dp_20180814_1140AM.PNG

Review I/O Transfer Rates

Outline

Get I/O Transfer rates and read and writes blocks.

Code

sar -b 

Output
sar-b-20180814-0810AM.PNG
Explanation
  1. tps
    • Total number of transfers per second that were issued to physical devices. A transfer is an I/O request to a physical device. Multiple logical requests can be combined into a single I/O request to the device. A transfer is of indeterminate size. ( Link )
  2. rtps
    • Number of read requests per second
  3. wtps
    • Number of writes per second
  4. bread/s
    • Number of sectors read per second.  A sector is 512 bytes  ( Link )
  5. bwrtn/s
    • Number of sectors written per second.  A sector is 512 bytes ( Link )

Reports run queue and load average (sar -q)

Outline

This reports the run queue size and load average of last 1 minute, 5 minutes, and 15 minutes.

Code

sar -q 

Output

sar-q_linux_db2_20180814_1217PM.PNG

Explanation
  1. runq-sz :- Run Queue Size
  2. plist-sz :- Process List
  3. ldavg-1 :- Load Average Size taken 1 minute ago
  4. ldavg-5 :- Load Average Size taken 5 minutes ago
  5. ldavg-15 :- Load Average Size taken 15 minutes ago
  6. blocked :- Number of blocked tasks

Reports run queue and load average (sar -n)

Outline

This reports the run queue size and load average of last 1 minute, 5 minutes, and 15 minutes.

Code

sar -n DEV -f /var/log/sa/sa07 -s 18:00:00 -e 23:59:00 | grep 'IFACE\|eth0'

Output

sar-n-DEV-linux_db2_20180814_1250PM

Explanation
  1. IFACE
    • Interface
  2. rxpck/s
    • Receive packets
  3. txpck/s
    • Transmit packets
  4. rxkB/s
    • Receive Kb/s
  5. txkB/s
    • Transmit Kb/s
  6. rxcmp/s
  7. txcmp/s
  8. rxmcst/s


Free

Definition

Display amount of free and used memory in the system.

Outline

Review Memory usage.

Usage

Usage – Raw Data

Code

free

Output

free_linux_db2_20180814_04432AM.PNG

Explanation
  1. total
    • Total Memory
  2. used
    • Used Memory
  3. free
    • Free Memory
  4. shared
    • Shared Memory
  5. buffers
    • Memory allotted for writing
  6. cached
    • Memory allotted for reading

Usage – Human Readable

Code

free -h

Output

free_linux_db2_-h_20180814_0732AM.PNG

iostat

Definition

Reports IO Statistics.

Outline

Review IO Statistics on each device.

Usage

Usage – Raw Data

Code

iostat -N

Output

iostat_db2_linux_20180815_0506PM.PNG

 

Explanation
  1. Summary
    • Average CPU
      • User
        • 7.4%
      • System
        • 2.51%
  2. Detail
    • Device
      • data1-db2data–11
        • tps :- 226.42
        • Blk_read/s :- 23254.73
        • Blk_wrtn/s :- 347.39
        • Blk_read :- 35060914332
        • Blk_wrtn :- 523763058
      • data1-dbbackup–11
        • tps :- 796.31
        • Blk_read/s :- 5785.45
        • Blk_wrtn/s :- 6187.70
        • Blk_read :- 8722664698
        • Blk_wrtn :- 9329126392

References

  1. BooleanWorld
    • Top Command
      • A Guide to the Linux Top Command
        Link
  2. Lily Feng
    • Top Command
      • The difference among VIRT, RES, and SHR in top output
        Link
  3. nixCraft
    • How do I Find Out Linux CPU Utilization?
      Link
  4. Ramesh Natarajan
    • The Geek Stuff
      • 10 Useful Sar (Sysstat) Examples for UNIX / Linux Performance Monitoring
        Link
  5. Neeraj Bhatia
    • Linux Memory Utilization
      Link
  6. Lisenet
    • Tomas
      • Measure and Troubleshoot Linux Memory Resource Usage
        Link
  7. HowtoForge
    • Linux Free Command Explained for Beginners (6 Examples)
      Link
  8.  stackExchange
    • top
      • Top – what does Virtual memory size mean? …linux/ubuntu
        Link
      • Process ‘niceness’ vs. ‘priority’
        Link
  9. serverfault
    • top
      • Memory Usage Numbers In top/htop
        Link
  10. Quora
    • top
      • What is the difference between the NI and PR values in the top(1) command’s output? I know NI is the nice value, which ranges from -19 to 20, but what is the significance of PR value?
        Link

Thamo Naidoo

 

Background

A perspective, critical, and insider look at the Apostolic movement.

 

Videos

  1. Developing Hebronic Communities of Love (Oneness) Session 1
    • Community of the Beloved
      Published  On :- 2018-August-14th
      Link
    • POA KZN 12 APR – Thamo Naidoo – Developing Hebronic Communities of Love (Oneness) Session 1
      Channel :- ApostolicGate Media
      Published  On :- 2017-April-15th
      Link
    • POA KZN 12 APR – Thamo Naidoo – Developing Hebronic Communities of Love (Oneness) Session 2
      Channel :- ApostolicGate Media
      Published  On :- 2017-April-15th
      Streamed Live from Southern Sun, Elangeni, Durban
      Link
  2. Perspectives on the Apostolic KZN – Thamo Naidoo –
    • Perspectives on the Apostolic KZN – Thamo Naidoo – Apostles and Apostolic Communities Session 3
      Channel :- ApostolicGate Media
      Published  On :- 2017-March-16th
      Link
    • Perspectives on the Apostolic KZN – Thamo Naidoo – The Feast of Tabernacles Session 2
      Channel :- ApostolicGate Media
      Published  On :- 2017-March-16th
      Link
    • Perspectives on the Apostolic KZN – Thamo Naidoo – Apostles and Apostolic Communities Session 1
      Channel :- ApostolicGate Media
      Published  On :- 2018-June-21st
      Link
  3. POA GAU 15 MAR – Thamo Naidoo – Profile of an Apostolic People
    • POA GAU 15 MAR – Thamo Naidoo – Profile of an Apostolic People – A case study of Judah Session 1
      Channel :- ApostolicGate Media
      Published  On :- 2017-March-16th
      Link