Currently, users often leave ACS (Access Client Solutions) SQL sessions open after executing their queries. These idle sessions remain connected for extended periods, which may introduce potential security risks and consume unnecessary system resources.
Is there any way to implement a configurable timeout mechanism that automatically disconnects inactive ACS SQL sessions after 15 minutes of inactivity?
Thanks

#IBMiAccessClientSolutions

Hi Sami.  I don't think such a setting exists currently, but I think its a great idea.  You might consider submitting an "IBM Idea" at https://ideas.ibm.com/

In the meantime, if you wanted to develop a solution yourself, this SQL should give you a good start.

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, i.remote_address
    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 find ODBC/JDBC jobs
          AND j.remote_address NOT IN ('0.0.0.0', '::') --ignore the listener server jobs
          AND idle_time > 3600 --set the desired "timeout" in seconds
          AND AUTHORIZATION_NAME NOT IN ('EXCLUDE1', 'EXCLUDE2', 'EXCLUDE3') --exclude or omit certain user profiles
          ;

Once you have "tuned" the query to only find the jobs that you want to timeout, you could add QSYS2.QCMDEXC() around the command string like this, and it will kill those connections:

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, i.remote_address
    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 find ODBC/JDBC jobs
          AND j.remote_address NOT IN ('0.0.0.0', '::') --ignore the listener server jobs
          AND idle_time > 3600 --set the desired "timeout" in seconds
          AND AUTHORIZATION_NAME NOT IN ('EXCLUDE1', 'EXCLUDE2', 'EXCLUDE3') --exclude or omit certain user profiles
          ;

The final piece of the puzzle to automate this to run every 15 minutes (for example) would be to wrap the whole thing with a "CREATE TABLE AS ()" so that it could run in batch.

(Corrected from CREATE FILE to CREATE TABLE - thank you Rich!)

------------------------------
Steven Riedmueller
Speaker, Mentor, and Advocate
Kisco Systems, LLC
------------------------------

For us, our networking config via the firewall takes care of this  - idle connections are ended after a couple of hours.

For internal users, I like Steven's idea but..Create FILE Is not correct..it would be a CREATE TABLE..or more preferably, Create or Replace Table WITH DATA ON REPLACE DELETE ROWS.

Having said all that..in ACS - you do have this option available...it would have be configured though  - I don't believe there is a global setting for this

------------------------------
Rich Malloy
Principal Systems Engineer - IBMi
Cox Automotive
Draper UT
------------------------------