Wednesday, August 29, 2018

Getting the System Name using SQL

get as400 system name using only 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.

6 comments:

  1. Great work; since my mission is never to write another CL I would have done it with RPGLE but same final solution.

    ReplyDelete
    Replies
    1. I could have used an API called from within a RPG program, but why when I can get the system name so easily using CL.

      Delete
  2. Consider using the native QSYS2 view: SELECT LOCAL_HOST_NAME FROM QSYS2.TCPIP_INFO

    ReplyDelete
    Replies
    1. That is very good.

      Using RTVNETA = DEV730

      Using Jesper's SQL statement = DEV730.RZKH.DE

      Delete
  3. wow, this is awesome!

    ReplyDelete
  4. As mentioned above, the DB2 current server (local RDB name) and the system name may not always be the same. This is almost certain to be the case with a DR target system (we have about a half a dozen of these at my company).

    As an alternative to CURRENT SERVER, there are two IBM-supplied views from which you can get the system name. The two views are SYSTEM_STATUS_INFO and NETWORK_ATTRIBUTE_INFO, both in QSYS2. The column name in both that contains the system name is HOST_NAME. (There is also a column PARTITION_NAME in SYSTEM_STATUS_INFO that has the same value as HOST_NAME.)

    NETWORK_ATTRIBUTE_INFO contains the same information as seen with DSPNETA. SYSTEM_STATUS_INFO contains the same data as in the header fields in DSPSYSSTS/WRKSYSSTS, and a lot more.

    Documentation on NETWORK_ATTRIBUTE_INFO is in the IBM i Information Center at Database/Performance and query optimization/IBM i Services/Communication Services. Documentation on SYSTEM_STATUS_INFO is found at Database/Performance and query optimization/IBM i Services/Work Management Services.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.