IBM i Global

IBM i 

A space for professionals working with IBM’s integrated OS for Power systems to exchange ideas, ask questions, and share expertise on topics like RPG and COBOL development, application modernization, open source integration, system administration, and business continuity.


#Power


#IBMi
#Power
 View Only

  • 1.  ACS SQL Sessions Automatic Timeout

    Posted Mon August 04, 2025 09:50 AM
    Edited by Sami Abou Karam Tue August 05, 2025 04:31 AM

    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


  • 2.  RE: ACS SQL Sessions Automatic Timeout

    Posted Tue August 05, 2025 08:57 AM
    Edited by Steven Riedmueller Tue August 05, 2025 04:32 PM

    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
    ------------------------------

    Original Message


  • 3.  RE: ACS SQL Sessions Automatic Timeout

    Posted Tue August 05, 2025 04:08 PM

    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
    ------------------------------

    Original Message


Related Content