Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.
The new SQL functions (aka "Services") IBM is shipping are nice and very useful for the SQL user. However, care should be taken when using some of these functions interactively.
Specifically, SQL Services sometimes use Java to communicate with remote services, such as PTF levels, HTTP status, network settings, etc. A little known fact is that Java forces the job into "SQL Server Mode". This happens with any Java-based SQL processing.
When a job is placed into SQL Server Mode, the legacy STRSQL (Start SQL) interactive SQL program no longer functions; it does not work in server mode. None of your interactive SELECT statements will work via STRSQL. I am still working on what happens when you run one of the SQL Services from within STRSQL (I only use SQL Query File's SQL Command Line Tool).
To resolve this issue, you can either signoff/end the job and start a new job, [EDIT: See update below] or you can use another SQL tool for interactive SQL processing, such as SQLQueryFile.com
SQL Query File allows you to specify whether or not to use SQL Server mode; it uses which ever mode is active. This is why it allows you to run interactive SELECT statements even when STRSQL can't.
So be careful and be prepared.
[UPDATE: On current PTF/TRx levels, this issue doesn't seem to occur as often. Also, just for fun, I tried entering connect reset into the interactive SQL session (i.e., STRSQL) and it did reset the connection and I was able to use it again without signing off/on. ]