File opened on vvOut.execSqlToJson remains allocated

[tgrannall]

Files are being opened on our IBM i by vvOut.execSqlToJson, and possibly other Valence commands. Is there any way to force the system to close these? We attempt to keep our system available all the time. The HTTP server jobs are very long running jobs and often do not end until an IPL. For system maintenance purposes, we need to refresh the files on a regular basis and need exclusive access to do this. The files we are concerned about are MQT (MATERIALIZED QUERY TABLE) files and the data is refreshed from our production database.
Thanks. Tim

[robert.swanson]

The open data paths you see in the Valence CGI jobs are a natural by-product of the way IBM i tries to make repeatedly called SQL statements run as efficiently as possible. They typically don't cause any problems, but if you really need to terminate the open paths you have a couple choices:

(1) Just prior to refreshing data from production, restart the server instance -- i.e., via STRTCPSVR SERVER(*HTTP) RESTART(*HTTP) HTTPSVR(VALENCEXX). There really is no downside to this approach, other than the possibility that some users might momentarily notice a brief delay if they're using the instance being restarted (their sessions are otherwise preserved). If there are sessions in use at the time you're trying to refresh the data, there is also a small possibility that an active user might reopen one of the data paths you wanted closed. In such a case, you might be better served to end the server instance (ENDTCPSVR), do the refresh, then restart the server instance.

(2) You can have Valence perform housekeeping after each call to ensure all open data paths from called procedures are closed. This is done by going into the Portal Admin app, Settings section, and setting the "Activation Group to Reclaim" to either *ELIGIBLE or the name of the activation group that your programs use (if it's a universal value) and a reclaim interval of 1. Note that if your programs are running in the VALENCE activation group (rather than their own named group) then this option won't work for you. The main downsides to this approach are that repeatedly invoked SQL statements may take longer to run, and there is a possible performance penalty on non-SQL calls as well, since the RCLACTGRP process may add extra time to each Ajax call. See the Valence Guide for specifics about setting activation groups. There is also a good discussion about activation groups on the Valence Forum.

-Rob

[tgrannall]

Thanks, Rob.
I am sure with either of these we will be able to solve our issue.
Regards, Tim