• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Using Built-In Global Variables In DB2 For i 7.2

    May 14, 2014 Michael Sansoterra

    The long awaited announcement of IBM i 7.2 comes with several enhancements to DB2 for i. In this tip, I’m going to explore the new set of built-in global variables that come with DB2 for i 7.2. If you’re unfamiliar with global variables, see New in DB2 for i 7.1: Use Global Variables to Track Environment Settings or the CREATE VARIABLE statement.

    The list of built-in global variables can be divided into three groups:

    Group 1: Client TCP/IP Info

    CLIENT_IPADDR VARCHAR(128)

    CLIENT_PORT INT

    As their names imply, these global variables return the IP address and port of the current client’s connection. This is very useful when auditing the origin of changes to the database. For IBM i 6.1, 7.1 users (with recent group PTF levels) and 7.2 users, this same information and more can be retrieved using the QSYS2.TCPIP_INFO “DB2 for i Services” view (except this view doesn’t return information when a telnet host connects). These variables will return NULL if the connection was not made using TCP/IP or SSL.

    Group 2: Current Routine Info

    ROUTINE_SCHEMA VARCHAR(128)

    ROUTINE_SPECIFIC_NAME VARCHAR(128)

    ROUTINE_TYPE CHAR(1)

    These global variables return the schema, name, and type of the current running SQL routine (i.e., function or procedure; triggers are not included). This is similar in concept to retrieving the name of the currently executing RPG program using the information in the program status data structure. The ROUTINE_TYPE will return a ‘P’ for a procedure and an ‘F’ for a function. These variables return a NULL when they’re not accessed in the context of a routine.

    Pay attention that the specific name is returned instead of the normal name. Since procedures and functions can be “overloaded” in DB2 for i, multiple procedures and functions in the same schema can share the same name so long as the parameter signatures are different. It’s actually the routine’s “specific name” that uniquely identifies it. If you want to look up the normal function or procedure name using the specific name, just query the SYSROUTINES catalog view.

    The thing that vexes me about these built-in variables is that I recently wrote a table function called CALLSTACKENTRY to do the same thing. Why weren’t these variables available in IBM i 7.1?! At least the CALLSTACKENTRY table function can still be used to identify the caller of the current program.

    Group 3: DRDA Package Info

    PACKAGE_NAME VARCHAR(128)

    PACKAGE_SCHEMA VARCHAR(128)

    PACKAGE_VERSION VARCHAR(64)

    Finally, the last group of built-in global variables is intended for DRDA users. DRDA allows a local DB2 instance to connect to and execute statements against other DB2 databases (including other server platforms such as Windows, Linux, etc.). For a brief intro to what DRDA can do see Bringing Home The Data and the references at the end of the tip.

    The PACKAGE_SCHEMA and PACKAGE_NAME identify the SQL package (*SQLPKG) object that is being used to execute the query.

    The PACKAGE_VERSION variable will return NULL unless the package was created by another version of DB2 such as DB2 for LUW.

    The following column expression shows one way auditing can be done using these new global variables:

    UPDATE MYTABLE
    SET MYDATA=MY_DATA_EXPRESSION,
    CHANGED_BY_PGM=COALESCE(
    SYSIBM.ROUTINE_SPECIFIC_NAME,
    SYSIBM.PACKAGE_NAME,
    SYSIBM.CLIENT_IPADDR,
    'SQL Script')
    WHERE ...
    

    The CHANGED_BY_PGM column will be populated with the first non-NULL value it encounters among the various global variables as a means of identifying the origin of the change.

    All of these global variables are session scoped, located in the SYSIBM schema and are read only. (For all practical purposes, the new built-in variables seem like they should’ve been implemented as special registers, but I’m sure my ignorance prevents me from understanding why they’re variables.) These built-in global variables are a great asset as they provide an application with information about the context of the database connection. This type of information is used a great deal in application areas of auditing and security.

    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 STORIES

    Bringing Home The Data

    Retrieve The Call Stack In DB2 For i

    New in DB2 for i 7.1: Use Global Variables to Track Environment Settings



                         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
    Briteskies

    From RPG development, technical consulting services, and EDI solutions, to eCommerce integration and security audits, Briteskies can handle it all. As a full-service consulting company, we have IBM i experts in place to help you on a short-term project basis or long-term assignment.

    Read our blog for more information about our expertise. Check out our Customer Success Stories to see how we help companies improve their efficiency.

    Learn more at www.briteskies.com or Contact Us Now to see how we can help keep your business running.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Symtrax:  SAP Webinar: Go Paperless with ePayslips for SAP - 13th of May, 4pm EDT
    LANSA:  Webinar: Mobile and the IBM i: Why Should You Care? May 21, 9 am PT/11 am CT/Noon ET
    RJS Software Systems:  Webinar: Learn how WebForms can save time and money. May 27.

    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

    Magic Software Still Has The Touch In Q1 IBM Patches Heartbleed Vulnerability in Power Systems Firmware

    Leave a Reply Cancel reply

Volume 14, Number 11 -- May 14, 2014
THIS ISSUE SPONSORED BY:

ProData Computer Services
PowerTech
WorksRight Software

Table of Contents

  • Using Built-In Global Variables In DB2 For i 7.2
  • Interpreting Stream File Timestamps
  • Admin Alert: When Journaling Slows Down Your System, And What To Do About It

Content archive

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

Recent Posts

  • IBM i Development and Modernization is Getting A Fresche Start with Some Ground-Breaking Subscriptions
  • CloudSAFE And Focal Point Solutions Group Combine Services, Unify Brands
  • Guru: Partitioning Result Sets Using SQL
  • As I See It: Elusive Connections
  • IBM i PTF Guide, Volume 25, Number 47
  • AWS Inks Deal With Connectria To Have a Power Play
  • IBM i Shops Have Alternatives to Db2 Web Query
  • Eradani Lays Waste to API Payload Restrictions
  • Four Hundred Monitor, November 15
  • Old PHP and Other PASE Apps Break on IBM i 7.5

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