Midrange News for the IBM i Community

Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Be careful running SQL Services interactively
has no ratings.
Published: 13 Oct 2015
Revised: 02 Jan 2016 - 3059 days ago
Last viewed on: 18 May 2024 (3362 views) 

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.

Be careful running SQL Services interactively Published by: Bob Cozzi on 13 Oct 2015 view comments

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


Return to midrangenews.com home page.
Sort Ascend | Descend