AS400 STRSQL vs RUNSQLSTM

**Karl Hanson** · Jun 27 '08, 07:21 PM

Re: AS400 STRSQL vs RUNSQLSTM

Eniacson wrote:

I've worked with DB2 for a long time, but I'm new to the AS400
environment.
I'm having a problem with an SQL statement that runs perfectly when I
use STRSQL, but when I batch it and call RUNSQLSTM, the exact same
statement (with a semicolon added at the very end) fails with the
following error:
>
Buffer length longer than record for member SBXARDTL.
Member SBXARDTL not journaled to journal *N.
SBXARDTL in QGPL not valid for operation.
RUNSQLSTM command failed.
SQL9010 received by procedure SBXORCEXT.
>
Now, SBXARDTL is a new table I created, and the SQL statement is an
insert based on a select on another table. Table was empty when I
attempted the batch insert.
SBXORCEXT is the CL program that calls RUNSQLSTM.
>
I could add more details, but for a seasoned AS400 expert among you
probably this is enough info to pinpoint the problem.
>
I will appreciate any hints.
Thanks in advance.

DB2 for i5/OS supports an isolation level of no-commit (NC). This means
SQL can be run in a mode where a transaction can not be committed or
rolled back. For NC, no journaling is required, whereas it is required
for all other isolation levels. The "... not valid for operation."
message is likely SQL7008 with reason code 3 (see below). You might
compare the isolation level (aka commitment control level) used in both
STRSQL and RUNSQLSTM. In STRSQL use F13 option 1 to check if commitment
control is *NONE; then check the value for the COMMIT parameter of
RUNSQLSTM (default is *CHG I believe). If this is the problem and the
RUNSQLSTM default is other than *NONE, you could change it to *NONE, or
set up journaling for the target table (physical file).

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp

Note that when using SQL CREATE SCHEMA, journal objects are
automatically created. Then when an SQL table is created into the
schema, it is automatically journaled.

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/sqlp/rbafyjourg.htm

Message ID . . . . . . . . . : SQL7008

Message file . . . . . . . . : QSQLMSG

Library . . . . . . . . . : QSYS

Message . . . . : &1 in &2 not valid for operation.

Cause . . . . . : The reason code is &3. Reason codes are:

1 -- &1 has no members.

2 -- &1 has been saved with storage free.

3 -- &1 not journaled, no authority to the journal, or the journal
state
is *STANDBY. Files with an RI constraint action of CASCADE, SET NULL,
or
SET DEFAULT must be journaled to the same journal.

--
Karl Hanson

**Eniacson** · Jun 27 '08, 07:21 PM

Re: AS400 STRSQL vs RUNSQLSTM

On May 15, 2:38 pm, Karl Hanson <kchan...@youes s.ibm.comwrote:

Eniacson wrote:

I've worked with DB2 for a long time, but I'm new to the AS400
environment.
I'm having a problem with an SQL statement that runs perfectly when I
use STRSQL, but when I batch it and call RUNSQLSTM, the exact same
statement (with a semicolon added at the very end) fails with the
following error:

>

Buffer length longer than record for member SBXARDTL.
Member SBXARDTL not journaled to journal *N.
SBXARDTL in QGPL not valid for operation.
RUNSQLSTM command failed.
SQL9010 received by procedure SBXORCEXT.

>

Now, SBXARDTL is a new table I created, and the SQL statement is an
insert based on a select on another table. Table was empty when I
attempted the batch insert.
SBXORCEXT is the CL program that calls RUNSQLSTM.

>

I could add more details, but for a seasoned AS400 expert among you
probably this is enough info to pinpoint the problem.

>

I will appreciate any hints.
Thanks in advance.

>
DB2 for i5/OS supports an isolation level of no-commit (NC). This means
SQL can be run in a mode where a transaction can not be committed or
rolled back. For NC, no journaling is required, whereas it is required
for all other isolation levels. The "... not valid for operation."
message is likely SQL7008 with reason code 3 (see below). You might
compare the isolation level (aka commitment control level) used in both
STRSQL and RUNSQLSTM. In STRSQL use F13 option 1 to check if commitment
control is *NONE; then check the value for the COMMIT parameter of
RUNSQLSTM (default is *CHG I believe). If this is the problem and the
RUNSQLSTM default is other than *NONE, you could change it to *NONE, or
set up journaling for the target table (physical file).http://publib.boulder.ibm.com/infoce...v5r4/index.jsp
>
Note that when using SQL CREATE SCHEMA, journal objects are
automatically created. Then when an SQL table is created into the
schema, it is automatically journaled.
>
http://publib.boulder.ibm.com/infoce...ndex.jsp?topic...
>
Message ID . . . . . . . . . : SQL7008
>
Message file . . . . . . . . : QSQLMSG
>
Library . . . . . . . . . : QSYS
>
Message . . . . : &1 in &2 not valid for operation.
>
Cause . . . . . : The reason code is &3. Reason codes are:
>
1 -- &1 has no members.
>
2 -- &1 has been saved with storage free.
>
3 -- &1 not journaled, no authority to the journal, or the journal
state
is *STANDBY. Files with an RI constraint action of CASCADE, SET NULL,
or
SET DEFAULT must be journaled to the same journal.
>
--
Karl Hanson- Hide quoted text -
>
- Show quoted text -

You were right on the mark.
Modifying the CL was problematic due to the change control policies in
place.
Instead I followed the links you provided and found out that adding
the statement below to my sql would override the default for
RUNSQLSTM:
SET TRANSACTION ISOLATION LEVEL NO COMMIT
The job completed successfully after that change.
Thank you!

AS400 STRSQL vs RUNSQLSTM

AS400 STRSQL vs RUNSQLSTM

Comment

Comment