On Aug 12, 5:16*pm, "Mr. K.V.B.L." wrote:
> No matter what I do to try and tweak this code, it's always failing on
> me. *If I enter the statement directly into STRSQL it works fine. *The
> error I'm getting back from SQLExecDirect() is :
>
> sqlRet=-1 NativeError=-7008 SqlState=55019 Message=DFPRAUTP in
> ICCTEST01P not valid for operation.
>
> The SqlState returned by SQLGetDiagRec() isn't listed onhttp://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/c...
>
> The string that is being built by my routine works out to this:
> UPDATE ICCTEST01P.DFPRAUTP SET PASTATION = 'TN0001' WHERE PASCODE =
> 99123 AND PAPREAUTH = 654321
>
> I can post more information including code if someone can point me in
> the right direction. *As always, thanks in advance.
>
> Kelly Beard

My initial searches on this problem led to more 'greek' than useful
information until I found this:

http://forums.systeminetwork.com/isn...p?t-38771.html

So I guess I need to turn on journaling for the DFPRAUTP file? I dont
know why a straight STRSQL statement would work and not
SQLExecDirect() though.

On Aug 12, 5:32*pm, "Mr. K.V.B.L." wrote:
> On Aug 12, 5:16*pm, "Mr. K.V.B.L." wrote:
>
>
>
> > No matter what I do to try and tweak this code, it's always failing on
> > me. *If I enter the statement directly into STRSQL it works fine. *The
> > error I'm getting back from SQLExecDirect() is :
>
> > sqlRet=-1 NativeError=-7008 SqlState=55019 Message=DFPRAUTP in
> > ICCTEST01P not valid for operation.
>
> > The SqlState returned by SQLGetDiagRec() isn't listed onhttp://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/c...
>
> > The string that is being built by my routine works out to this:
> > UPDATE ICCTEST01P.DFPRAUTP SET PASTATION = 'TN0001' WHERE PASCODE =
> > 99123 AND PAPREAUTH = 654321
>
> > I can post more information including code if someone can point me in
> > the right direction. *As always, thanks in advance.
>
> > Kelly Beard
>
> My initial searches on this problem led to more 'greek' than useful
> information until I found this:
>
> http://forums.systeminetwork.com/isn...dex.php?t-3877...
>
> So I guess I need to turn on journaling for the DFPRAUTP file? *I dont
> know why a straight STRSQL statement would work and not
> SQLExecDirect() though.

I'm learning some about this, but I thought I would just add that I'm
using CRTCPPMOD & CRTPGM to build my PGM object. An RPG guy here
showed me some create options for a CRTSQLRPGI command but I don't see
those options just yet in CRTCPPMOD or CRTPGM.

Mr. K.V.B.L. wrote:
> On Aug 12, 5:32 pm, "Mr. K.V.B.L." wrote:
>> On Aug 12, 5:16 pm, "Mr. K.V.B.L." wrote:
>>
>>
>>
>>> No matter what I do to try and tweak this code, it's always failing on
>>> me. If I enter the statement directly into STRSQL it works fine. The
>>> error I'm getting back from SQLExecDirect() is :
>>> sqlRet=-1 NativeError=-7008 SqlState=55019 Message=DFPRAUTP in
>>> ICCTEST01P not valid for operation.
>>> The SqlState returned by SQLGetDiagRec() isn't listed onhttp://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/c...
>>> The string that is being built by my routine works out to this:
>>> UPDATE ICCTEST01P.DFPRAUTP SET PASTATION = 'TN0001' WHERE PASCODE =
>>> 99123 AND PAPREAUTH = 654321
>>> I can post more information including code if someone can point me in
>>> the right direction. As always, thanks in advance.
>>> Kelly Beard
>> My initial searches on this problem led to more 'greek' than useful
>> information until I found this:
>>
>> http://forums.systeminetwork.com/isn...dex.php?t-3877...
>>
>> So I guess I need to turn on journaling for the DFPRAUTP file? I dont
>> know why a straight STRSQL statement would work and not
>> SQLExecDirect() though.
>
> I'm learning some about this, but I thought I would just add that I'm
> using CRTCPPMOD & CRTPGM to build my PGM object. An RPG guy here
> showed me some create options for a CRTSQLRPGI command but I don't see
> those options just yet in CRTCPPMOD or CRTPGM.

Journaling is required if commitment control is used - ie anything other
than COMMIT(*NONE) - you can search the newsgroup for SQL7008 and
probably find more details. Most likely when in STRSQL you were using
*NONE - try F13->option 1 and check the value of "Commitment control" there.

If you don't need commitment control, the connection attribute can be
set to *NONE using SQL_ATTR_COMMIT :
http://publib.boulder.ibm.com/infoce...adpfnsconx.htm

If you are coding CLI then you're likely not also using embedded SQL, so
commands like CRTSQLxxx would not apply. However most (all?) of the
attributes can be set as above.

It's worth noting that when using SQL to create a schema (aka
collection), the i5/OS *LIB object that's created also contains journal
objects, so tables created thereafter are automatically journaled. But
some *FILE objects historically were maintained using native I/O (eg
RPG) and did not use journaling or commitment control. And if no
transaction support is needed with SQL, *NONE can be used to manage
files not journaled.

--
Karl Hanson

As a temporary measure, I found that appending the text "WITH NONE" to
the end of my UPDATE statement allowed the code to work. So...

UPDATE ICCTEST01P.DFPRAUTP SET PASTATION = 'TN0001' WHERE PASCODE =
99123 AND PAPREAUTH = 654321 WITH NONE

However I'll investigate using SQLSetConnectAttr() instead. Thanks
for all the help Karl.

On Aug 12, 8:21*pm, Karl Hanson wrote:
> Mr. K.V.B.L. wrote:
> > On Aug 12, 5:32 pm, "Mr. K.V.B.L." wrote:
> >> On Aug 12, 5:16 pm, "Mr. K.V.B.L." wrote:
>
> Journaling is required if commitment control is used - ie anything other
> than COMMIT(*NONE) - you can search the newsgroup for SQL7008 and
> probably find more details. *Most likely when in STRSQL you were using
> *NONE - try F13->option 1 and check the value of "Commitment control" there.
>
> If you don't need commitment control, the connection attribute can be
> set to *NONE using SQL_ATTR_COMMIT :http://publib.boulder.ibm.com/infoce...ndex.jsp?topic...
>
> If you are coding CLI then you're likely not also using embedded SQL, so
> commands like CRTSQLxxx would not apply. However most (all?) of the
> attributes can be set as above.
>
> It's worth noting that when using SQL to create a schema (aka
> collection), the i5/OS *LIB object that's created also contains journal
> objects, so tables created thereafter are automatically journaled. But
> some *FILE objects historically were maintained using native I/O (eg
> RPG) and did not use journaling or commitment control. And if no
> transaction support is needed with SQL, *NONE can be used to manage
> files not journaled.
>
> --
> Karl Hanson

Mr. K.V.B.L. wrote:
> As a temporary measure, I found that appending the text "WITH NONE" to
> the end of my UPDATE statement allowed the code to work. So...
>
> UPDATE ICCTEST01P.DFPRAUTP SET PASTATION = 'TN0001' WHERE PASCODE =
> 99123 AND PAPREAUTH = 654321 WITH NONE
>
> However I'll investigate using SQLSetConnectAttr() instead. Thanks
> for all the help Karl.
>
> On Aug 12, 8:21 pm, Karl Hanson wrote:
>> Mr. K.V.B.L. wrote:
>>> On Aug 12, 5:32 pm, "Mr. K.V.B.L." wrote:
>>>> On Aug 12, 5:16 pm, "Mr. K.V.B.L." wrote:
>> Journaling is required if commitment control is used - ie anything other
>> than COMMIT(*NONE) - you can search the newsgroup for SQL7008 and
>> probably find more details. Most likely when in STRSQL you were using
>> *NONE - try F13->option 1 and check the value of "Commitment control" there.
>>
>> If you don't need commitment control, the connection attribute can be
>> set to *NONE using SQL_ATTR_COMMIT :http://publib.boulder.ibm.com/infoce...ndex.jsp?topic...
>>
>> If you are coding CLI then you're likely not also using embedded SQL, so
>> commands like CRTSQLxxx would not apply. However most (all?) of the
>> attributes can be set as above.
>>
>> It's worth noting that when using SQL to create a schema (aka
>> collection), the i5/OS *LIB object that's created also contains journal
>> objects, so tables created thereafter are automatically journaled. But
>> some *FILE objects historically were maintained using native I/O (eg
>> RPG) and did not use journaling or commitment control. And if no
>> transaction support is needed with SQL, *NONE can be used to manage
>> files not journaled.
>>
>

Yes the statement isolation-clause overrides the default (in this case
connection) isolation level.
http://publib.boulder.ibm.com/infoce...afzmstisol.htm

Also fyi - in the job where you test your program, if you can first get
the job into debug mode, more detailed info may exist in job log
messages. SQL7008 is a good example, where in this case reason code 3
explains the journaling requirement. (SQL7008 may have been present even
w/o being in debug mode, but more SQL "informational" messages are sent
when in debug mode, that can help with functional or performance
issues). Another useful tool is Database Monitor:
http://publib.boulder.ibm.com/infoce...onexamples.htm

===> dspmsgd sql7008 qsqlmsg

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

--
Karl Hanson

There should be a CLI connection attribute that can be specified to change the
Commit Level to *NONE which then disables the need for journaling.

Mr. K.V.B.L. wrote:
> On Aug 12, 5:32 pm, "Mr. K.V.B.L." wrote:
>> On Aug 12, 5:16 pm, "Mr. K.V.B.L." wrote:
>>
>>
>>
>>> No matter what I do to try and tweak this code, it's always failing on
>>> me. If I enter the statement directly into STRSQL it works fine. The
>>> error I'm getting back from SQLExecDirect() is :
>>> sqlRet=-1 NativeError=-7008 SqlState=55019 Message=DFPRAUTP in
>>> ICCTEST01P not valid for operation.
>>> The SqlState returned by SQLGetDiagRec() isn't listed onhttp://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/c...
>>> The string that is being built by my routine works out to this:
>>> UPDATE ICCTEST01P.DFPRAUTP SET PASTATION = 'TN0001' WHERE PASCODE =
>>> 99123 AND PAPREAUTH = 654321
>>> I can post more information including code if someone can point me in
>>> the right direction. As always, thanks in advance.
>>> Kelly Beard
>> My initial searches on this problem led to more 'greek' than useful
>> information until I found this:
>>
>> http://forums.systeminetwork.com/isn...dex.php?t-3877...
>>
>> So I guess I need to turn on journaling for the DFPRAUTP file? I dont
>> know why a straight STRSQL statement would work and not
>> SQLExecDirect() though.
>
> I'm learning some about this, but I thought I would just add that I'm
> using CRTCPPMOD & CRTPGM to build my PGM object. An RPG guy here
> showed me some create options for a CRTSQLRPGI command but I don't see
> those options just yet in CRTCPPMOD or CRTPGM.

--
Kent Milligan
ISV Enablement - System i
kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
>>> ibm.com/iseries/db2
(opinions stated are not necessarily those of my employer)