Re: Query on commitment control in SQL scripts

On 03-Mar-2015 09:26 -0600, Darryl Freinkel wrote:

On Tue, Mar 3, 2015 at 10:12 AM, CRPence wrote:

On 03-Mar-2015 08:18 -0600, Darryl Freinkel wrote:

I am running a series of SQL scripts using iNavigator's script
option.

Issue:
I am not using commitment control but when I run the scripts,
the system is logging messages that the files is not being
journaled.

What messages? Something like msg SQL7008 rc3 for DML, or perhaps
instead, msg SQL7905 for DDL CREATE TABLE requests?

Questions:
How do I suppress the messages?

Depends on what are the requests being performed and the messages
being issued; too little context has been offered for a reader to
provide worthwhile comment.

Tell the script to not use commitment control. In SQLRPG I use the
set OPTION COMMIT = *NONE. Is there something equal to this in
scripts?

On DML requests code the WITH isolation clause, e.g. WITH NC, or
issue the statement SET ISOLATION LEVEL for a dynamic request to
establish the CmtCtl level similar to how the non-executable SET
OPTION from the embedded SQL statement in pre-compiled
host-language source.

The above should have noted the _SET TRANSACTION statement_ with the ISOLATION LEVEL clause:
<http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzsettraj.htm>

Otherwise the setting can be established initially in the database
/connection/; those prior mentioned capabilities to override any
one statement or the effective /session/. Vern responded about how
the JDBC settings can address the issue of the isolation level
established for the connection that is used by the RunSQL script
utility.

An additional link, re the above:
IBM i 7.2->Database->Reference->SQL reference->Concepts->Isolation level
<http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzisol.htm>

There are 2 repeating messages:

1. CPF4328 member "name" not journaled to Journal *N
2. SQL7008 "filename" in QTEMP not valid for operation - Code = 3.

To establish the _context_ for an error message, the request for which the error was issued must be known; often the messages spooled from the joblog using LOG(4 0 *SECLVL) filtering will show the program names that send and are recipient of the messages, and that helps to further refine or reveal context, as in most cases the actual SQL statement is not also logged [and must be determined by another means].

Presumably the requests receiving that error were SQL DML statement requests, for which the WITH isolation-level clause could resolve on a statement-level. Otherwise the isolation-level can be controlled for the scope of the TRANSACTION or the /connection/ as already discussed.