I've been asked how to retrieve the operating system version, using SQL. While I've been doing this for years with the set of UDF and UDTF routines included free in SQL iQuery, it is rather easy to do, so I thought I'd show everyone how to do it.

First, you need to use C or C++. RPG can be used to retrieve the OS version by calling the same API, however this new "inline" UDF that leaverages the INCLUDE statement only supports SQL and C languages.

The API that returns the operating system version, does so by returning it as an integer. So v7.2 is returned as 720. That API, CEEGPID retrieves the OS version and the platform on which the API is running. Since it is always IBM i, we'll ignore that 2nd piece of information.

Below is the few lines of C code need to extract the OS version and save it into a variable.

  /* Create a C source member in QCSRC in your library */
  /* Modify the OSVER SQL UDF source to point to this source mbr */
  /* Compile the OSVER SQL UDF using RUNSQLSTM  */
  /* (c) Copyright 2017 by R.Cozzi, Jr.         */
{
#include <letype.h>
#include <lecond.h>

     int        osVER = 0;
     int        platform = 0;
     _FEEDBACK  fc;

     CEEGPID( &osVER, &platform, &fc);
     M.M_VER = osVER;
}

You'll note there are only 2 lines of code and 3 declare statements. The call to CEEGPID is all that's need to grab the OS version.

We place the result into the field named M.M_VER. This is a subfield of the "M" data structure. This structure is named inside the SQL source for the UDF by the developer, and the C structure is automatically generated. One thing with this kind in-line "external" UDF, when C is used,  curly brackets are pretty much required to start and end the include member. They act as a "scope delimiter" to keep your code from the IBM-generated C code.

Let's look at the SQL UDF code now.

          -- www.SQLiQuery.com UDF - System Functions

          --  OSVER() - Returns the current version of IBM i.
          -- SELECT osver() into :hostVar FROM sysibm.sysdummy1;
          -- or
          --  VALUES osver() into :hostVar;
          -- e.g., If IBM i is v7r2m0, then 720 is returned.


   -- NOTE: WHEN COMPILING THIS UDF YOU MUST
   -- HAVE QSYSINC ON YOUR LIBRARY LIST 
   --  or the Create will Fail!!!

   -- IBM has fixed this issue with the following PTFs;
      --  7.1 SI63034
      --  7.2 SI63054
      --  7.3 SI63133
   -- If the above PTF is installed for your release level,
   -- this should compile without issue.

   -- Remember *CURLIB is used as the target library
   -- when you avoid qualifying UDFs.

 CREATE or REPLACE FUNCTION OSVER( )
         RETURNS INT
       LANGUAGE SQL
       SPECIFIC OSVersion
       DETERMINISTIC
       SET OPTION COMMIT = *NONE
BEGIN
    DECLARE VER INT;
M: BEGIN
    DECLARE M_VER INT;
    SET M_VER = 0;
    -- NOTE: modify this include to match your situation
    INCLUDE COZTOOLS/QCSRC(OSVER);
    SET VER = M_VER;
  END;
   RETURN VER;
 END;
       

The OSVER UDF returns VER which is declared as an INT (integer). The actual OS version is returned in the M_VER variable. Note that on the 2nd BEGIN statement, I've included a "tag" or "label" identifier of "M:". That name (which can be anything, I just like shorter variable names) is used by the SQL compiler to generate a data structure that contains all the variables declared within the BEGIN and END statements.

The actual C routine is evoked automagically. Basically treating the INCLUDE statement like a CALLB operation, except it is inline code.

Once the C routine assigns a value to the M.M_VER variable and ends, the SQL routine copies that value to the variable named VER which is subsequently returned to the caller as the result of the UDF.

To create (compile) the UDF, you ONLY run the SQL statements via the RUNSQLSTM command. It in turn evokes the C compiler to compile your C source member. It actually comples the C code that the SQL statements are translated to and your code is simple "/copy'd" into their code.

Using OSVER() SQL UDF

To use the UDF once it is compiled simply specify it like any other UDF.

SELECT OSVER() FROM SYSIBM.SYSDUMMY1;

The output result is:

  OSVER    
    730    

If you're using it in STRSQL or as an Embedded SQL within RPG, you can use the VALUES INTO statement to retrieve the OS version into your program or view it in STRSQL. That syntax is:

values osver()

or embedded in RPG

EXEC SQL values osver() INTO :ver;

Pretty cool, huh?

Tip: Remember the library list is searched for your UDFs, so consider qualifying them to the library in which they are stored so you don't have to worry about code working today and failing tomorrow. But each environment has different issues to consider when qualifying things, so it is up to you to make that decision.