USing SQL in CL (QSYS2.NETSTAT_INFO)

[phdeliege]

Dear,

I'm trying to retrieve information from QSYS2.NETSTAT_INFO in a CL.

I know that I have to pass the RUNSQL command to a PF to be able to retrieve data but it's not working.

Here is an example of my code:

PGM

DCLF FILE(qtemp/t1)
DCL &SQLSTMT TYPE(*CHAR) LEN(1000)
DCL VAR(&TCPSTATE) TYPE(*CHAR) LEN(12)
DCL VAR(&SOCKSTATE) TYPE(*CHAR) LEN(13)
DCL VAR(&SOCKERR) TYPE(*INT)

MONMSG CPF0000

CHGVAR VAR(&SQLSTMT) +
VALUE('CREATE TABLE qtemp.t1 AS +
(SELECT +
REMOTE_ADDRESS,REMOTE_PORT,TCP_STATE,SOCKET_STATE, SOCKET_ERROR +
FROM QSYS2.NETSTAT_INFO WHERE +
REMOTE_ADDRESS = '10.10.5.20') WITH DATA')
RUNSQL SQL(&SQLSTMT) COMMIT(*NONE) NAMING(*SQL)

RCVF
MONMSG MSGID(CPF0864)
CHGVAR VAR(&TCPSTATE) VALUE(&TCP_STATE)
CHGVAR VAR(&SOCKSTATE) VALUE(&SOCKET_STATE)
CHGVAR VAR(&SOCKERR) VALUE(&SOCKET_ERROR)

ENDPGM

My first question, is how should I create the file (QTEMP/T1) prior to compile my program ?

What other mistake I'm doing ?

I'm a little bit lost... thanks in advance for your support.

Best regards,

[Vectorspace]

Does this have to be CL? It would be much easier with SQLRPGLE.

If it does have to be CL - then to read a table in a CL program with DCLF, then that table must already exist at compile time.

Rather than creating the table dynamically within your RUNSQL, I would create a permanent table (DDS or SQL table, your choice) in a permanent library. Let's call it MYLIB/NETDTL
The DCLF statement would not be qualified - simply DCLF FILE(NETDTL)
Then I can compile the program against the permanent NETDTL table.

Then before the RUNSQL, I would do a CPYF of NETDTL to copy it to QTEMP
And then in the RUNSQL I would insert into that table rather than creating it on the fly. I.e. the SQL would be 'insert into qtemp/NETDTL select * from...'

Then I think it should work, providing when the program is run QTEMP is higher in the libl than MYLIB.

However - I do not know for a fact that the file is only opened by the first RCVF. If it is opened at program start this will not work. If it is opened at program start I am sure there is a way to defer opening until RCVF, but I cannot remember it.

[Vectorspace]

FYI, the column names for NETSTAT_INFO when read by CL would probably not be &TCP_STATE, &SOCKET_STATE, &SOCKET_ERROR. SQL Tables like this, each column has a long SQL column name like this, and a short (max 10 char) System column name. It would be the System column names that a CL program would see with DCLF/RCVF.

FYI, this is how easy it would be in an SQLRPGLE program:

Dcl-S TCPState varchar(12);
Dcl-S SocketState varchar(13);
Dcl-S SocketError int;

exec sql
  select TCP_STATE, SOCKET_STATE, SOCKET_ERROR
    into :TCPState, SocketState, SocketError
    from QSYS2.NETSTAT_INFO
   where REMOTE_ADDRESS = '10.10.5.20'
   limit 1;

[Vectorspace]

Typo in the SQLRPGLE code sample, and I cannot edit my post. This is the corrected one:

Dcl-S TCPState varchar(12);
Dcl-S SocketState varchar(13);
Dcl-S SocketError int;

exec sql
  select TCP_STATE, SOCKET_STATE, SOCKET_ERROR
    into :TCPState, :SocketState, :SocketError
    from QSYS2.NETSTAT_INFO
   where REMOTE_ADDRESS = '10.10.5.20'
   limit 1;

[Brian Rusch]

I may be going against the grain, but I like working in CL. To create the file before compiling the CL program, simply run the RUNSQL command on a command line. The three issues that I can see are the IP address in the where clause needs to have 2 quotes around it (' '10.10.5.20' '), the field names of the T1 file are going to be the system names (not the long names), and the character fields of the T1 file are going to be variable length, which is hard to work with in CL. To take care of the field naming and the variable length issues, redefine the fields in the RUNSQL command. In the below example, they have been renamed to eliminate the need to do a CHGVAR after the RCVF:

PGM
             DCLF       FILE(QTEMP/T1)
             DCL        VAR(&SQLSTMT) TYPE(*CHAR) LEN(1000)

             CHGVAR     VAR(&SQLSTMT) VALUE('Create Table QTEMP.T1 +
                          AS (Select char(Remote_Address) as +
                          RmtAddr, Remote_Port as RmtPort, +
                          char(Tcp_State) as TcpState, +
                          char(Socket_State) as SockState, +
                          Socket_Error as SockErr From +
                          QSYS2.NETSTAT_INFO Where Remote_Address = +
                          ''10.10.5.20'') With Data')
             DLTF       FILE(QTEMP/T1)
             MONMSG     MSGID(CPF0000)
             RUNSQL     SQL(&SQLSTMT) COMMIT(*NONE)

             RCVF
             MONMSG     MSGID(CPF0864) EXEC(RETURN)
ENDPGM

[TedHolt]

Just to follow up on Vectorspace's comment, the first RCVF opens the file.

[phdeliege]

Hello all and thank you very much for your support.

As I'm more familiar with CLP, I decided to use the way Brian mentioned. And it's working fine !
I just made a small pgm tp create the T1 file and thus to be able to compile the main program.

If any other are interested about the code, here are the 2 program:


1 - Create T1 File:
PGM
DCL &SQLSTMT TYPE(*CHAR) LEN(1000)
CHGVAR VAR(&SQLSTMT) VALUE('CREATE TABLE QTEMP.T1 +
AS (SELECT CHAR(REMOTE_ADDRESS) AS RMTADD, REMOTE_PORT AS RMTP, +
CHAR(TCP_STATE) AS TCPSTATE, CHAR(SOCKET_STATE) AS SOCSTATE, +
SOCKET_ERROR AS SOCERR FROM +
QSYS2.NETSTAT_INFO WHERE REMOTE_ADDRESS = +
''10.10.5.20'') WITH DATA')
RUNSQL SQL(&SQLSTMT) COMMIT(*NONE)
ENDPGM

Once the file is created, I was able to compile the main program:

2-
PGM

DCLF FILE(QTEMP/T1)
DCL &SQLSTMT TYPE(*CHAR) LEN(1000)
CHGVAR VAR(&SQLSTMT) VALUE('CREATE TABLE QTEMP.T1 +
AS (SELECT CHAR(REMOTE_ADDRESS) AS RMTADD, REMOTE_PORT AS RMTP, +
CHAR(TCP_STATE) AS TCPSTATE, CHAR(SOCKET_STATE) AS SOCSTATE, +
SOCKET_ERROR AS SOCERR FROM +
QSYS2.NETSTAT_INFO WHERE REMOTE_ADDRESS = +
''10.7.4.23'') WITH DATA')
DLTF FILE(QTEMP/T1)
MONMSG MSGID(CPF0000)
RUNSQL SQL(&SQLSTMT) COMMIT(*NONE)
MONMSG MSGID(CPF0864)

RCVF

...

ENDPGM


Thanks one more time for all your support.

Best regards,

Pierre.

[Chris Brunner]

Or create a view once so your pgm1 is not needed any more.

CREATE OR REPLACE VIEW LIBRARY.T1 AS
(
SELECT CHAR(REMOTE_ADDRESS) AS RMTADD,
REMOTE_PORT AS RMTP,
CHAR(TCP_STATE) AS TCPSTATE,
CHAR(SOCKET_STATE) AS SOCSTATE,
SOCKET_ERROR AS SOCERR
FROM QSYS2.NETSTAT_INFO
WHERE REMOTE_ADDRESS = '10.10.5.20'
)