• 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
    Trinity Guard

    Register to Attend: Get Your Guard Up!

    Cyberattacks are on the rise and projected to grow further in 2021. This remains true even on Power Systems, especially for Linux and IBM i IFS.

    Join this 1-hour session to learn how to identify vulnerabilities and secure Power Systems from costly security breaches.

    Thursday, February 18

    12 Noon CT  / 10AM PT  /  1PM ET

    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

  • 2021 Predictions for IBM i: Part Two
  • Zend Server for IBM i Now 64-Bit, Available Via RPM
  • New IBM i Logo, Publication
  • Four Hundred Monitor, January 20
  • IBM i PTF Guide, Volume 23, Number 3
  • IBM i Software And Power Systems Upgrades Keep Rolling Forward
  • Preparing For What’s Next In A Thoughtful, Structured Way
  • Guru: Fall Brings New RPG Features, Part 3
  • ARCAD Plugs IBM i DevOps Suite Into Microsoft Azure
  • Park Place Buys Curvature To Become Maintenance Goliath

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 © 2021 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.