Re: Weird COMMIT <> *NONE required in query

This is an older version from an example I used to show some of my
presentations.
I'm quite sure that I had in the slides that GET_CLOB_FROM_FILE,
GET_DBCLOB_FROM_FILE, GET_BLOB_FROM_FILE and GET_XML_FILE have to be
executed under commitment control.
These functions return a LOB-Locator which is a kind of pointer that can be
used in SQL. A LOB Locator only points to the data and does not copy the
data!
... and like any pointer a LOB Locator has to be freed. And LOB Locators
are/can be freed with COMMIT and ROLLBACK.

Instead of using the GET_CLOB_FROM_FILE function, you may use the IFS_READ
or IFS_READ_UTF8 Function.
When using this functions there is no need to run under commitment
control.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert
Einstein)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Javier
Sanchez
Sent: Friday, 16 August 2024 21:42
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Weird COMMIT <> *NONE required in query

I am trying to run this query using iACS:

With x as (
*-- Split IFS File into Rows (at CRLF)* Select Ordinal_Position
as
RowKey, Element as RowInfo
from Table(SysTools.Split(*Get_Clob_From_File*(
'/myfolder/EmailReceived.csv'), x'0D25')) a
Where *Trim*(Element) > '|'),
y as (
*-- Split IFS File Rows into Columns (and remove leading/trailing double
quotes ")*
Select x.*, Ordinal_Position ColKey,
*Trim*(B '"' from Element) as ColInfo
from x cross join Table(SysTools.Split(RowInfo, '|')) a)

*-- Return the Result as Table*Select RowKey,
*Min*(Case When ColKey = *1* Then ColInfo End) as From,
*Min*(Case When ColKey = *2* Then ColInfo End) as To,
*Min*(Case When ColKey = *3* Then ColInfo End) as CC,
*Min*(Case When ColKey = *4* Then ColInfo End) as Reply,
*Min*(Case When ColKey = *5* Then ColInfo End) as Subject,
*Min*(Case When ColKey = *6* Then ColInfo End) as SendDate,
*Min*(Case When ColKey = *7* Then ColInfo End) as Content,
*Min*(Case When ColKey = *7* Then ColInfo End) as Body

From y

Where RowKey > *1*
*-- Remove header*Group By RowKey;

The .csv file has the pipe character as field separator, and the header
line
is as follows:

From|To|CC|ReplyTo|Subject|SentDate|ContentType|Body

There is sample contents in the .csv that I won't need to show.
The thing is that I am getting this SQL error:

<error>
Message: [SQL0443] LOB and XML locators are not allowed with COMMIT(*NONE).
Cause . . . . . : Either a trigger program, external procedure, or
external function detected and returned an error to SQL. If the error
occurred in a trigger program, the trigger was on table QDBSSUDF2 in schema
QSYS. If the error occurred in an external procedure or function, the
external name is QDBSSUDF2 in schema QSYS. The associated text is LOB and
XML locators are not allowed with COMMIT(*NONE).. If the error occurred in
a trigger program, the associated text is the type of trigger program. If
the error occurred in an external function, the associated text is the text
of the error message returned from the external function. Recovery . . . :
Refer to the joblog for more information regarding the detected error.
Correct the error and try the request again.
</error>

Can someone please explain why is this? I am not using updates or deletes.
LOB and XML locators are not allowed with COMMIT(*NONE)????

What is that? FCS!

Thanks in advance.

JS
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.