RE: Weird COMMIT <> *NONE required in query -- MIDRANGE-L

Add NC to the last part of your query.

-Matt

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

I missed that one from Birgitta. Mmm...interesting challenge to do this in a non-commitment control environment application Guess I know what I can do :-) Thanks guys! JS El vie, 16 ago 2024 a las 13:
External (javiersanchezbarquero@xxxxxxxxx<mailto:javiersanchezbarquero@xxxxxxxxx>)

Report This Email<https://protection.inkyphishfence.com/report?id=d2luY29mb29kcy9tYXR0LnR5bGVyQHdpbmNvZm9vZHMuY29tLzc3ZTZkNGQyMTE3YzJiMGMzZjVjM2Y3YTkwYWE3NTQ4LzE3MjM4Mzg3NTMuNTc=#key=4b2dd9a24bd2318e63e54f656e5698e0>

I missed that one from Birgitta. Mmm...interesting challenge to do this in

a non-commitment control environment application

Guess I know what I can do :-)

Thanks guys!

JS

El vie, 16 ago 2024 a las 13:54, Charles Wilt (<charles.wilt@xxxxxxxxx<mailto:charles.wilt@xxxxxxxxx>>)

escribió:

https://urldefense.com/v3/__https://www.ibm.com/docs/en/i/7.4?topic=objects-large-object-locators__;!!O6xM9Yim9Yk!51dy6ZA8lWHw7H2u1FYi4sgIITEgAOybmJETjdnh5sphlOagl-Gr7lwlELGwEaw8Jkr9kA6GvXZo1MQ-fEhlJdRyyYng7JY$<https://urldefense.com/v3/__https:/www.ibm.com/docs/en/i/7.4?topic=objects-large-object-locators__;!!O6xM9Yim9Yk!51dy6ZA8lWHw7H2u1FYi4sgIITEgAOybmJETjdnh5sphlOagl-Gr7lwlELGwEaw8Jkr9kA6GvXZo1MQ-fEhlJdRyyYng7JY$>

A large object (LOB) locator is a small, easily managed value that is used

to refer to a much larger value.

Specifically, a LOB locator is a 4-byte value stored in a host variable

that a program uses to refer to a LOB value held in the database server.

Using a LOB locator, a program can manipulate the LOB value as if it were

stored in a regular host variable. When you use the LOB locator, there is

no need to transport the LOB value from the server to the application (and

possibly back again).

The LOB locator is associated with a LOB value, not a row or physical

storage location in the database. Therefore, after selecting a LOB value

into a locator, you cannot perform an operation on the original row(s) or

table(s) that have any effect on the value referenced by the locator. The

value associated with the locator is valid until the unit of work ends, or

the locator is explicitly freed, whichever comes first. The FREE LOCATOR

statement releases a locator from its associated value. In a similar way, a

commit or rollback operation frees all LOB locators associated with the

transaction.

LOB locators can also be passed to and returned from UDFs. Within the UDF,

those functions that work on LOB data can be used to manipulate the LOB

values using LOB locators.

https://urldefense.com/v3/__https://www.ibm.com/docs/en/i/7.4?topic=functions-get-clob-from-file__;!!O6xM9Yim9Yk!51dy6ZA8lWHw7H2u1FYi4sgIITEgAOybmJETjdnh5sphlOagl-Gr7lwlELGwEaw8Jkr9kA6GvXZo1MQ-fEhlJdRyPmIngyw$<https://urldefense.com/v3/__https:/www.ibm.com/docs/en/i/7.4?topic=functions-get-clob-from-file__;!!O6xM9Yim9Yk!51dy6ZA8lWHw7H2u1FYi4sgIITEgAOybmJETjdnh5sphlOagl-Gr7lwlELGwEaw8Jkr9kA6GvXZo1MQ-fEhlJdRyPmIngyw$>

The function will read the file specified by the argument, convert the data

to the default job CCSID, and return it as a CLOB locator. The function

must be run under commitment control. The locator will be freed when a

COMMIT or ROLLBACK is performed.

On Fri, Aug 16, 2024 at 1:42 PM Javier Sanchez <

javiersanchezbarquero@xxxxxxxxx<mailto:javiersanchezbarquero@xxxxxxxxx>> wrote:

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<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

To subscribe, unsubscribe, or change list options,

visit: https://urldefense.com/v3/__https://lists.midrange.com/mailman/listinfo/midrange-l__;!!O6xM9Yim9Yk!51dy6ZA8lWHw7H2u1FYi4sgIITEgAOybmJETjdnh5sphlOagl-Gr7lwlELGwEaw8Jkr9kA6GvXZo1MQ-fEhlJdRyH1OwOzI$<https://urldefense.com/v3/__https:/lists.midrange.com/mailman/listinfo/midrange-l__;!!O6xM9Yim9Yk!51dy6ZA8lWHw7H2u1FYi4sgIITEgAOybmJETjdnh5sphlOagl-Gr7lwlELGwEaw8Jkr9kA6GvXZo1MQ-fEhlJdRyH1OwOzI$>

or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>

Before posting, please take a moment to review the archives

at https://urldefense.com/v3/__https://archive.midrange.com/midrange-l__;!!O6xM9Yim9Yk!51dy6ZA8lWHw7H2u1FYi4sgIITEgAOybmJETjdnh5sphlOagl-Gr7lwlELGwEaw8Jkr9kA6GvXZo1MQ-fEhlJdRygbRwJsU$<https://urldefense.com/v3/__https:/archive.midrange.com/midrange-l__;!!O6xM9Yim9Yk!51dy6ZA8lWHw7H2u1FYi4sgIITEgAOybmJETjdnh5sphlOagl-Gr7lwlELGwEaw8Jkr9kA6GvXZo1MQ-fEhlJdRygbRwJsU$> .

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related

questions.

--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list