471,071 Members | 917 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,071 software developers and data experts.

AS400 STRSQL vs RUNSQLSTM

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.
Jun 27 '08 #1
2 17671
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...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
Jun 27 '08 #2
On May 15, 2:38*pm, Karl Hanson <kchan...@youess.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!
Jun 27 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by alan_sec | last post: by
13 posts views Thread by Sehboo | last post: by
1 post views Thread by Iain | last post: by
1 post views Thread by accyboy1981 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.