EndIdleConnections.sql

--
-- Description: Users are leaving their "Run SQL Scripts" sessions open for an extended period of time.
--              Although there is a configurable option to "automatically close cursor to release locks",
--              this does not disconnect the session.  This can present a security risk if physical access
--              to the connected workstation is compromised.
--
--              How can we use SQL to identify ODBC/JDBC connections that have been idling for a long time
--              and proactively disconnect them?  Follow along below.
--
-- DISCLAIMER:  These examples are provided for informational and educational purposes only.  There is no
--              guarantee or warranty, and you should use this information at your own risk. :)


--the NETSTAT_INFO view contains details about TCP connections such as the remote/local IP address and port, idle time, etc
SELECT i.*
    FROM qsys2.netstat_info i;
stop;

--the NETSTAT_JOB_INFO view contains some overlapping details, but also some unique details like job names and users
SELECT j.*
    FROM qsys2.netstat_job_info j;
stop;

--join the views using some of the fields that they have in common (connection type, local/remote address, local/remote port)
SELECT i.*, j.*
    FROM qsys2.netstat_info i
         JOIN qsys2.netstat_job_info j
             ON i.connection_type = j.connection_type
                 AND i.remote_address = j.remote_address
                 AND i.remote_port = j.remote_port
                 AND i.local_address = j.local_address
                 AND i.local_port = j.local_port;
stop;

--add a WHERE clause with a predicate to include only the QZDASOINIT and QZDASSINIT jobs which handle ODBC/JDBC connections
SELECT i.*, j.*
    FROM qsys2.netstat_info i
         JOIN qsys2.netstat_job_info j
             ON i.connection_type = j.connection_type
                 AND i.remote_address = j.remote_address
                 AND i.remote_port = j.remote_port
                 AND i.local_address = j.local_address
                 AND i.local_port = j.local_port
    WHERE j.job_name_short in ('QZDASOINIT', 'QZDASSINIT'); --only include ODBC/JDBC jobs
stop;

--in the WHERE clause, add a threshold for the idle_time - i.e. include jobs that have been idle for more than 3600 seconds
SELECT i.*, j.*
    FROM qsys2.netstat_info i
         JOIN qsys2.netstat_job_info j
             ON i.connection_type = j.connection_type
                 AND i.remote_address = j.remote_address
                 AND i.remote_port = j.remote_port
                 AND i.local_address = j.local_address
                 AND i.local_port = j.local_port
    WHERE j.job_name_short in ('QZDASOINIT', 'QZDASSINIT') --only include ODBC/JDBC jobs
          AND i.idle_time > 3600; --set the desired "idle timeout" in seconds
stop;

--in the WHERE clause, omit certain user profiles from being timed out (i.e. critical application service accounts)
SELECT i.*, j.*
    FROM qsys2.netstat_info i
         JOIN qsys2.netstat_job_info j
             ON i.connection_type = j.connection_type
                 AND i.remote_address = j.remote_address
                 AND i.remote_port = j.remote_port
                 AND i.local_address = j.local_address
                 AND i.local_port = j.local_port
    WHERE j.job_name_short in ('QZDASOINIT', 'QZDASSINIT') --only include ODBC/JDBC jobs
          AND i.idle_time > 3600 --set the desired "idle timeout" in seconds
          AND j.authorization_name NOT IN ('EXCLUDE1', 'EXCLUDE2', 'EXCLUDE3'); --omit certain user profiles from timing out
stop;

--build a sample of the required ENDTCPCNN command which is used to end a connection (local/remote address and local/remote port)
SELECT 'ENDTCPCNN PROTOCOL(*TCP) LCLINTNETA(''localaddr'') LCLPORT(localport) RMTINTNETA(''remoteaddr'') RMTPORT(remoteport)' as cmd,
       i.*, j.*
    FROM qsys2.netstat_info i
         JOIN qsys2.netstat_job_info j
             ON i.connection_type = j.connection_type
                 AND i.remote_address = j.remote_address
                 AND i.remote_port = j.remote_port
                 AND i.local_address = j.local_address
                 AND i.local_port = j.local_port
    WHERE j.job_name_short LIKE 'QZDAS%' --only include ODBC/JDBC jobs
          AND i.idle_time > 3600 --set the desired "idle timeout" in seconds
          AND j.authorization_name NOT IN ('EXCLUDE1', 'EXCLUDE2', 'EXCLUDE3'); --omit certain user profiles from timing out
stop;

--retrieve only the columns required for populating the ENDTCPCNN command parameters, plus the columns included in the WHERE predicates
SELECT 'ENDTCPCNN PROTOCOL(*TCP) LCLINTNETA(''localaddr'') LCLPORT(localport) RMTINTNETA(''remoteaddr'') RMTPORT(remoteport)' as cmd,
       i.local_address, i.local_port, i.remote_address, i.remote_port, --these parameters are required for the ENDTCPCNN command
       j.job_name, i.idle_time, j.authorization_name --these parameters are part of our WHERE clause
    FROM qsys2.netstat_info i
         JOIN qsys2.netstat_job_info j
             ON i.connection_type = j.connection_type
                 AND i.remote_address = j.remote_address
                 AND i.remote_port = j.remote_port
                 AND i.local_address = j.local_address
                 AND i.local_port = j.local_port
    WHERE j.job_name_short in ('QZDASOINIT', 'QZDASSINIT') --only include ODBC/JDBC jobs
          AND i.idle_time > 3600 --set the desired "timeout" in seconds
          AND j.authorization_name NOT IN ('EXCLUDE1', 'EXCLUDE2', 'EXCLUDE3'); --omit certain user profiles from timing out
stop;

--concatenate together the ENDTCPCNN command, populating the parameters using the data returned by the SQL views
SELECT 'ENDTCPCNN PROTOCOL(*TCP) LCLINTNETA('''  || i.local_address  || ''') LCLPORT(' || i.local_port  || 
           ') RMTINTNETA(''' || i.remote_address || ''') RMTPORT(' || i.remote_port || ')' as cmd,
       j.job_name, i.idle_time, j.authorization_name --these parameters are part of our WHERE clause
    FROM qsys2.netstat_info i
         JOIN qsys2.netstat_job_info j
             ON i.connection_type = j.connection_type
                 AND i.remote_address = j.remote_address
                 AND i.remote_port = j.remote_port
                 AND i.local_address = j.local_address
                 AND i.local_port = j.local_port
    WHERE j.job_name_short in ('QZDASOINIT', 'QZDASSINIT') --only include ODBC/JDBC jobs
          AND i.idle_time > 3600 --set the desired "idle timeout" in seconds
          AND j.authorization_name NOT IN ('EXCLUDE1', 'EXCLUDE2', 'EXCLUDE3'); --omit certain user profiles from timing out
stop;

--once the query is returning the jobs that you want to timeout, add "QSYS2.QCMDEXC()" around the command string so it will be executed
SELECT qsys2.qcmdexc('ENDTCPCNN PROTOCOL(*TCP) LCLINTNETA('''  || i.local_address  || ''') LCLPORT(' || i.local_port  || 
                         ') RMTINTNETA(''' || i.remote_address || ''') RMTPORT(' || i.remote_port || ')') as cmd,
       j.job_name, i.idle_time, j.authorization_name --these parameters are part of our WHERE clause
    FROM qsys2.netstat_info i
         JOIN qsys2.netstat_job_info j
             ON i.connection_type = j.connection_type
                 AND i.remote_address = j.remote_address
                 AND i.remote_port = j.remote_port
                 AND i.local_address = j.local_address
                 AND i.local_port = j.local_port
    WHERE j.job_name_short in ('QZDASOINIT', 'QZDASSINIT') --only include ODBC/JDBC jobs
          AND i.idle_time > 3600 --set the desired "idle timeout" in seconds
          AND j.authorization_name NOT IN ('EXCLUDE1', 'EXCLUDE2', 'EXCLUDE3'); --omit certain user profiles from timing out
stop;

--to allow this query to run in batch, add a CREATE TABLE AS around the query
CREATE TABLE qtemp.ENDCONNS as (
SELECT qsys2.qcmdexc('ENDTCPCNN PROTOCOL(*TCP) LCLINTNETA('''  || i.local_address  || ''') LCLPORT(' || i.local_port  || 
                         ') RMTINTNETA(''' || i.remote_address || ''') RMTPORT(' || i.remote_port || ')') as cmd,
       j.job_name, i.idle_time, j.authorization_name --these parameters are part of our WHERE clause
    FROM qsys2.netstat_info i
         JOIN qsys2.netstat_job_info j
             ON i.connection_type = j.connection_type
                 AND i.remote_address = j.remote_address
                 AND i.remote_port = j.remote_port
                 AND i.local_address = j.local_address
                 AND i.local_port = j.local_port
    WHERE j.job_name_short in ('QZDASOINIT', 'QZDASSINIT') --only include ODBC/JDBC jobs
          AND i.idle_time > 3600 --set the desired "idle timeout" in seconds
          AND j.authorization_name NOT IN ('EXCLUDE1', 'EXCLUDE2', 'EXCLUDE3') --omit certain user profiles from timing out
) with data;
stop;

--The query now could be scheduled to run in batch using the CL command "RUNSQLSTM"
--  You can save the query into a stream file in the IFS and pass that path into the RUNSQLSTM command
--  i.e. RUNSQLSTM SRCSTMF('/utilities/EndIdleConnections.sql') COMMIT(*NONE) NAMING(*SQL)
--
--  Alternatively you can put the SQL into a source file member and pass that to the RUNSQLSTM command
--  i.e. RUNSQLSTM SRCFILE(UTILITY/QSQLSRC) SRCMBR(ENDCONNS) COMMIT(*NONE) NAMING(*SQL)
--
--  There are other alternatives as well, such as using CREATE PROCEDURE to create a compiled program that you can "CALL" just like 
--      any other program, but we'll leave that for another time.