• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • TR8 DB2 For i Enhancements, Part 2

    December 3, 2014 Michael Sansoterra

    In the first part of this series, I introduced several of the DB2 for i enhancements made in IBM i 7.1 Technology refresh 8 (and in IBM i 7.2).

    These enhancements were of interest to developers and administrators. Likewise, this second set of enhancements we are about to review will be of interest to developers but may hold even greater interest for those in an administrative role.

    QSYS2.GET_JOB_INFO enhancement

    This DB2 for i service table function has two new enhancements:

    1. The job parameter now accepts the special value ‘*’ to return information about the current job.

    2. New column V_SQL_STATEMENT_TEXT is now returned.

    Sample usage:

    -- return information about the current job
    SELECT * FROM TABLE(QSYS2.GET_JOB_INFO('*')) x
    
    -- Find out what SQL statement Uncle Jed is running 
    -- that is killing the system
    SELECT V_SQL_STATEMENT_TEXT
    FROM TABLE(QSYS2.GET_JOB_INFO('003813/UNCLEJED/QZDASOINIT')) x 
    

    This first example provides developers with a way to retrieve audit information about the current job. The second example shows how the function can aid administrators to identify a SQL statement that is causing performance issues.

    Reference: QSYS2.GET_JOB_INFO()

    New Columns Added To View QSYS2.USER_INFO

    Recall that this relatively new “service” view returns information about user profiles (similar to dumping the output of WRKUSRPRF to an *OUTFILE). Assuming the proper authorization to query the view is in place, this view allows a DB2 query to return users with specific special authorities (like *ALLOBJ), invalid sign-on attempts, recent password changes, etc. This view is useful for providing auditors with real-time information about user profile states.

    Some new columns have been added to the view and they’re shown here (and all new columns are NULLABLE).

    Column Name

    System Column

    Data Type

    Description

    USER_OWNER

    USER_OWNER

    VARCHAR(10)

    The user profile that owns this user profile.

    USER_CREATOR

    CREATOR

    VARCHAR(10)

    The user profile that created this user profile.

    SIZE

    SIZE

    DECIMAL(15,0)

    Size of the user profile, in bytes.

    CREATION_
    TIMESTAMP

    TIMESTAMP

    TIMESTAMP

    Timestamp of when the user profile was created.

    LAST_USED_
    TIMESTAMP

    LASTUSED

    TIMESTAMP

    The date the user profile was used last. The time
    portion of the timestamp will always be 0.

    DAYS_USED_
    COUNT

    DAYSUSED

    INTEGER

    The number of days the user profile has been used
    on the system.

    LAST_RESET_
    TIMESTAMP

    LASTRESET

    TIMESTAMP

    The date when the days used count was last reset
    to zero. The time portion of the timestamp will always be 0.

    Reference: QSYS2.USER_INFO View

    Procedures QSYS2.DUMP_PLAN_CACHE_TOPN And QSYS2.EXTRACT_STATEMENTS

    If you are responsible for monitoring system performance, then you’ll absolutely love these procedures. The first procedure QSYS2.DUMP_PLAN_CACHE_TOPN dumps the most expensive SQL statements (expensive being defined as the largest cumulative elapsed time) in the active plan cache to a database file of your own choosing:

    -- Identify the top 15 most expensive queries and
    -- dump them to TRACE/SQLHOGS (i.e. a cache snapshot)
    CALL QSYS2.DUMP_PLAN_CACHE_TOPN('TRACE','SQLHOGS',15);
    

    While you can query the output file (TRACE/SQLHOGS) directly, you’ll notice there will probably be more than 15 rows and that it’s difficult to decipher. Its contents are much easier understood by using Visual Explain or a tool such as the EXTRACT_STATEMENTS procedure. EXTRACT_STATEMENTS is used to pull statement info from a “plan cache snapshot” file and put it in an “easy to read” result set:

    -- Extract all statements for jobs where the user is MIKE
    CALL QSYS2.EXTRACT_STATEMENTS('TRACE', 'SQLHOGS',
    ADDITIONAL_PREDICATES=>' AND QQUSER=''MIKE''',
    ORDER_BY=>'ORDER BY QQUCNT DESC');
    

    The result set contains quite a few columns.

    The documentation for this procedure reveals there are quite a few parameter options. The above example shows that the ADDITIONAL_PREDICATES parameter is passed a string value containing WHERE clause predicates for the purpose of filtering the data in the snapshot.

    Here are a few other examples of how to use the procedure (assuming that TRACE/SQLHOGS is the plan cache snapshot):

    -- Extract all database server jobs (QZDASOINIT) where 
    MIKE is the current user
    CALL QSYS2.EXTRACT_STATEMENTS('TRACE', 'SQLHOGS',
    ADDITIONAL_PREDICATES=>' AND QQJOB=''QZDASOINIT'' 
    AND QVC102=''MIKE''',
    ORDER_BY=>'ORDER BY QQUCNT DESC');
    
    -- Return all statements showing "audit" information
    CALL QSYS2.EXTRACT_STATEMENTS('TRACE', 'SQLHOGS',
    ADDITIONAL_SELECT_COLUMNS=>'*AUDIT',
    ORDER_BY=>'ORDER BY QQUCNT DESC');
    

    Of course to use parameters like ORDER_BY, ADDITIONAL_SELECT_COLUMNS and ADDITIONAL_PREDICATES, you have to know what column names to supply. These column names can be identified by querying the plan cache snapshot file directly. Some of the column names are cryptic but in most cases it’s easy to tell what they mean. There’s lots of interesting information available to filter on including:

    • QQC183 (host or IP address)
    • QQI6 (Statement run time in microseconds)

    Additional information about these columns (and their definitions) can be found at this IBM Database Monitor View link. The EXTRACT_STATEMENTS procedure can be used to programmatically pull info from any plan cache snapshot, including those created using System i Navigator.

    Of course, be careful when embedding predicates in the criteria of a parameter; all single quotes will need to be doubled up (a.k.a. escaped) so that: QQUSER=’MIKE’ becomes QQUSER=”MIKE” when embedded in the parameter’s value.

    Having an easy way to dump the plan cache can help identify the queries that are taking the most time on the system. Further, it can be done with relatively easy programmatically so your system can monitor itself while you have your feet up. Hopefully in the future IBM will tweak it even more so that an admin can select the Top N queries utilizing the highest CPU, I/O, etc.

    References: Dump_Plan_Cache_TopN Procedure, QSYS2.EXTRACT_STATEMENTS() procedure

    QSYS2.SYSPROGRAMSTMTSTAT And QSYS2.SYSPACKAGESTMTSTAT

    These new catalog views contain useful information about the prepared SQL statements that are stored in embedded SQL programs or SQL packages on the IBM i. Check them out as they contain information like:

    • Number of times the statement was executed.
    • The number of times the access plan was rebuilt.
    • The aggregate number of rows returned.
    • The number of host variables in each statement.
    • The SQL text of each statement.

    These views can provide a way to find all stored SQL statements with a certain expression or text, which statements affect the largest number of rows, and which statements are being rebuilt too frequently by DB2.

    For example, if you have many embedded SQL programs with the constant ‘DEBTOR’ and you want to find all instances of this constant so you can replace it, run this statement:

    SELECT * 
    FROM QSYS2.SYSPROGRAMSTMTSTAT 
    WHERE STATEMENT_TEXT LIKE '%''DEBTOR''%';
    

    The results will show all prepared statements with the constant and what embedded SQL program they’re found in. Dynamic SQL statements using this constant (run with PREPARE or EXECUTE IMMEDIATE) in an embedded SQL program will not show up in this list.

    Likewise, to look through all SQL packages (used by DRDA and ODBC/JDBC/OLE DB/.NET, etc.) for statements containing the constant ‘DEBTOR’, the following query will work:

    SELECT * 
    FROM QSYS2.SYSPACKAGESTMTSTAT 
    WHERE STATEMENT_TEXT LIKE '%''DEBTOR''%';
    

    Keep in mind that packages only store preparable statements and are not guaranteed to contain every statement submitted by, for example, a JDBC client. In my testing, I found that the various numbers (like execution count) returned by these views do persist after an IPL.

    Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

    RELATED STORY

    TR8 DB2 For i Enhancements, Part 1



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    PERFSCAN

    Revolutionary Performance Management Software

    At Greymine, we recognize there is a void in the IT world for a dedicated performance management company and also for a performance management tool that’s modern, easy to use, and doesn’t cost an arm and a leg. That’s why we created PERFSCAN.

    PERFSCAN is designed to make your job easier. With revolutionary technology, an easy-to-read report and graphics engine, and real time monitoring, tasks that used to take days can now take minutes. This means you will know your system better and will be able to provide better service to your customers.

    OUR FEATURES

    PERFSCAN is full of robust features that don’t require you to take a three-day class in order to use the product effectively.

    Customizable Performance Reporting

    Whether you are troubleshooting a major system problem or simply creating a monthly report, PERFSCAN lets you select any combination of desired performance metrics (CPU, Disk, and Memory).

    User Defined Performance Guidelines

    No matter if you are a managed service provider managing complex systems in the cloud or a customer analyzing your on-premises solution, PERFSCAN gives you the flexibility to define all mission critical guidelines how they need to be.

    Understanding The Impact Of Change

    Tired of all the finger pointing when performance is suffering? PERFSCAN’s innovative What’s Changed and Period vs. Period analysis creates a culture of proof by correlating known environmental changes with system performance metrics.

    Comprehensive Executive Summary

    Creating performance graphs is easy. Understanding what they mean is another thing. With one mouse click, PERFSCAN includes an easy-to-understand executive summary for each core metric analyzed.

    Combined Real-Time Monitor And Performance Analysis Tool

    With PERFSCAN’s combined built in enterprise real-time monitor and historical performance analysis capability, you will always know how your mission-critical systems are performing.

    Cloud Performance Reporting Is Easy

    Managing performance for production systems in the cloud can be a black hole to many system administrators. The good news is PERFSCAN analyzes all core metrics regardless of the location. That’s why MSPs and customers love PERFSCAN.

    Detailed Job Analysis

    PERFSCAN shows detailed top job analysis for any desired period. All metrics are displayed in two ways: Traditional Report and Percentage Breakdown Pie Chart. This toggle capability instantly shows the jobs using the most system resources.

    Save Report Capability

    Your boss lost the report you gave to him on Friday. Now what do you do? With PERFSCAN’s save report capability, any report can be retrieved in a matter of seconds.

    Professional PDF Reporting With Branding

    Creating professional looking reports for your customers has never been easier with PERFSCAN. Branding for our partners and service provider customers is easy with PERFSCAN.

    Check it out at perfscan.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Cilasoft:  Stay on top of your most difficult IBM i security challenges with our Auditing and Security Suite.
    Profound Logic Software:  Now On-Demand Webinar: "See What i Can Do with Mobile Applications"
    BCD:  Beat Your Q4 Numbers with Real-Time Analytics on IBM i. Watch video!

    More IT Jungle Resources:

    System i PTF Guide: Weekly PTF Updates
    IBM i Events Calendar: National Conferences, Local Events, and Webinars
    Breaking News: News Hot Off The Press
    TPM @ EnterpriseTech: High Performance Computing Industry News From ITJ EIC Timothy Prickett Morgan

    Web Dev Tool from mrc Goes ‘Responsive’ Entry Power8 Systems Get Express Pricing, Fat Memory

    Leave a Reply Cancel reply

Volume 14, Number 26 -- December 3, 2014
THIS ISSUE SPONSORED BY:

Focal Point Solutions Group
WorksRight Software
Shield Advanced Solutions

Table of Contents

  • Fuzzy Matching In RPG
  • TR8 DB2 For i Enhancements, Part 2
  • Admin Alert: What Should An IBM i Administrator Do, Part 1

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Fortra Issues 20th State of IBM i Security Report
  • FNTS Launches Managed Services for Power Servers in IBM Cloud
  • Total LTO Shipped Capacity Up Slightly in 2022
  • Four Hundred Monitor, May 24
  • Update On Critical Security Vulnerability In PowerVM
  • Critical Security Vulnerability In PowerVM Hypervisor
  • IBM Power: Hosted On-Premises Or In The Cloud?
  • Guru: Watch Out For This Pitfall When Working With Integer Columns
  • As I See It: Bob-the-Bot
  • IBM i PTF Guide, Volume 25, Number 21

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2023 IT Jungle