Getting the System Name using SQL

What appeared at first look to be a simple challenge given on Twitter:

How would you get the system name of a partition via a SQL statement?

In a CL program this is simple as I can retrieve the system name using the Retrieve Network Attributes command, RTVNETA.

03 RTVNETA SYSNAME(&SYSNAME)

So how to get the same information using SQL?

I first found that there is a keyword in the GET DIAGNOTICS SQL statement called SERVER_NAME, which I can use in a RPG program:

01 exec sql GET DIAGNOSTICS CONDITION 1 :Server = SERVER_NAME ; 02 dsply Server ;

I found another method I could use in a RPG program using the SQL VALUES statement:

04 exec sql VALUES CURRENT SERVER INTO :Server ; 05 dsply Server ;

The two returned the same value. Confident I had found the answer I tried the program, I had both SQL statements in, on several IBM i. The first four I used the value displayed was the same as the value retrieved by the RTVNETA command. The fifth IBM i threw up a surprise the value returned by the RTVNETA command was not the same as that returned by the SQL statements.

On further investigation I found that the SQL statements were returning the Db2 for i database name, not the system name. The first four IBM i had probably not changed the name of their system, and IBM i default is that the shipped system and database names are the same.

If you are curious to learn the name of the Db2 for i database name on your IBM i you can use the DSPRDBDIRE to view it.

DSPRDBDIRE Display Relational Database Entry Detail Relational database . . . .: XXXXXXXX

Going back through the documentation for the GET DIAGNOSTICS there is not a keyword that will return the system name. I have to admit I was disappointed. This left me with another approach, create a User Define Function.

I have written about User Defined Functions before, and I use them extensively in my own work. All those I had created called RPG subprocedures in a service program. This gave me an opportunity to try something new, write a User Defined Function in another language. I am going to write this UDF to call a CL program. I could have written one to call a RPG subprocedure, that would call the QWCRSSTS API, but why do that when I can easily get the same information from the RTVNETA command?

My CL program is very simple, and it returns just a single value the system name, &SYSNAME.

01 PGM PARM(&SYSNAME) 02 DCL VAR(&SYSNAME) TYPE(*CHAR) LEN(8) 03 RTVNETA SYSNAME(&SYSNAME) 04 ENDPGM

I compile this source to create the program RTVNETAPGM in my library.

Once the program object exists I can create the SQL statement to create the new function.

01 CREATE OR REPLACE FUNCTION MYLIB.GETSYSNAME () 02 RETURNS CHAR(8) 03 LANGUAGE CL 04 NO SQL 05 PARAMETER STYLE DB2SQL 06 DETERMINISTIC 07 EXTERNAL NAME 'MYLIB/RTVNETAPGM' 08 PROGRAM TYPE MAIN ;

Let me explain how this is different from where I had called a procedure in a service program.

Line 1: Notice that there is no passed parameter to this function as the parentheses at the end of the line contain nothing.

Line 2: The CL program called by this function returns a single value that is character 8 long.

Line 3: The program that is called in written in CL.

Line 4: The program does not contain any SQL statements.

Line 5: There are different types of parameter styles. Through a process of trial and error I discovered that if a program is called the parameter style needs to be DB2SQL.

Line 6: DETERMINISTIC means that if the function is called with the same input as before it will return the same output, without needing to call the program. In this case the system name is always going to be the same, therefore, it is more efficient to make the function deterministic.

Line 7: This is the name of the program that needs to be called.

Line 8: As it is a program the program type is MAIN. I assume as the main procedure should be called, rather than a subprocedure.

This SQL statement is in a source member, therefore, I can create it using the Run SQL Statements command, RUNSQLSTM.

Now I can use my new UDF in any SQL statement to retrieve the system name.

SELECT MYLIB.GETSYSNAME() FROM SYSIBM.SYSDUMMY1 GETSYSNAME DEV730

Notice that I do have to give the empty parentheses when using the UDF. If I do not then SQL assumes that GETSYSNAME is a global variable.

Challenge completed!

 

This article was written for IBM i 7.3, and should work for earlier releases too.