Pages

Tuesday, July 11, 2023

Using SQL to get Network Attributes

This new View, NEWORK_ATTRIBUTE_INFO, was introduced as part of IBM i TR2 and 7.4 TR8. It shows the same information as the Display Network Attributes command, DSPNETA, and allows you to retrieve the same information as you would using the Retrieve Network Attributes command, RTVNETA.

I will admit I don't use this information much, in fact the only attribute I use is the Current System name. In a CL program I retrieve it using RTVNETA:

01  DCL VAR(&SYSNAME) TYPE(*CHAR) LEN(10)

02  RTVNETA SYSNAME(&SYSNAME)

Line 1: The variable I will use to contain the partition's name.

Line 2: Using the RTVNETA command to retrieve the "system name" into the variable.

The first time I encounter a SQL View I always run a Select statement to view the contents of all its columns. As this is the first time I will be using the NEWORK_ATTRIBUTE_INFO View I would use:

01  SELECT * FROM QSYS2.NETWORK_ATTRIBUTE_INFO

It will return one result as there is only one set of network attributes.

I have compared the results from the NETWORK_ATTRIBUTE_INFO View with the DSPNETA, and found four columns present in the View that are not displayed with the DSPNETA:

  • SERVER_NETWORK_ID_COUNT:  Number of network id values in the next column
  • SERVER_NETWORK_ID_COUNT:  APPN servers returned in a JSON array
  • CLIENT_REQUEST_LIBRARY:  Library the program in CLIENT_REQUEST_PROGRAM is found. If that column null this will be null too
  • CLIENT_REQUEST_PROGRAM:  If CLIENT_REQUEST_ACCESS is "*PROGRAM" then this is the program's name. Or null

This is the statement I would use to find what is in those "extra" columns. And yes, I know my results has more than four columns.

01  SELECT HOST_NAME,
02         SERVER_NETWORK_ID_COUNT,
03         SERVER_NETWORK_IDS,
04         CLIENT_REQUEST_ACCESS,
05         CLIENT_REQUEST_LIBRARY,
06         CLIENT_REQUEST_PROGRAM
07    FROM QSYS2.NETWORK_ATTRIBUTE_INFO

Line 1: With things like this I always want the partition's name in the result.

Line 4: This is not a new column. I added it to the results so that I can understand the results of the two following client request columns, which are new.

The results are:

         SERVER_
         NETWORK
HOST_    _ID_
NAME     COUNT    SERVER_NETWORK_IDS
-------  -------  --------------------------------------------------------
DEV750         1  {"NET_IDS":[{"NET_ID":"*LCLNETID", "CTL_POINT":"*ANY"}]}



CLIENT_   CLIENT_    CLEINT_
REQUEST_  REQUEST_   REQUEST_
ACESS     LIBRARY    PROGRAM
--------  ---------  ---------
*REGFAC   <NULL>     <NULL>

As the CLIENT_REQUEST_ACCESS does not contain "*PROGRAM" the following two columns are null.

I showed at the top of the program how I use the RTVNETA command to get the system name in a CL program. Using NETWORK_ATTRIBUTE_INFO I could do the same in a RPG program:

01  **free
02  dcl-s PartitionName char(10) ;

03  exec sql SELECT HOST_NAME INTO :PartitionName
04             FROM QSYS2.NETWORK_ATTRIBUTE_INFO ;

Line 1: It's 2023 so all new code should be in totally free RPG.

Line 2: Declaration of the variable to contain the value from the HOST_NAME column.

Lines 3 and 4: This statement is SQL's equivalent of a RPG Chain operation. When the row is found the value from the HOST_NAME column is moved to the RPG variable PartitionName. Notice that the RPG variable's name is prefix by a colon ( : ), this denotes that this is a RPG program variable.

When I need the partition's name in my RPG programs I have always used the following:

05  exec sql SET :PartitionName = CURRENT_SERVER ;

To be honest I am not going to change from using this to using the NETWORK_ATTRIBUTE_INFO View.

If you use the network attributes for more than the "system name" I would be interested to hear what you use. Please let me know in the comments, below.

 

You can learn more about the NETWORK_ATTRIBUTE_INFO View from the IBM website here.

 

This article was written for IBM i 7.5 TR2 and 7.4 TR8.

No comments:

Post a Comment

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.