IFS_READ normally returns a CLOB(2G) doesn't it? It seems like you'd be getting pointers on every read. Maybe trying casting the results as a character string within the SQL instead of the select *.
Something like:
EXEC SQL DECLARE CURSOR1 CURSOR FOR SELECT cast(line as char(500)) FROM TABLE(QSYS2.IFS_READ(PATH_NAME => :FileName))
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Brian Garland via MIDRANGE-L
Sent: Monday, June 13, 2022 10:55 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Brian Garland <Brian.Garland@xxxxxxxxxx>
Subject: SQL table function IFS_READ
CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
This is my first time trying this function (always used API calls before).
After reading 512 rows the next fetch fails with SQLSTATE 38501 and there are pointer errors in Db2 functions listed in the job log.
If I delete some rows from the start of the IFS file it will read past the row it stopped on before and fail on the new row 513.
Just doing a basic cursor:
EXEC SQL DECLARE CURSOR1 CURSOR FOR SELECT * FROM TABLE(QSYS2.IFS_READ(PATH_NAME => :FileName)); EXEC SQL OPEN CURSOR1; DOU SQLSTATE >= '02000';
EXEC SQL FETCH NEXT FROM CURSOR1 INTO :IFS_Input;
IF SQLSTATE >= '02000';
LEAVE;
ENDIF;
// code to process input
ENDDO;
EXEC CLOSE CURSOR1;
Brian J. Garland
Vermont Information Processing, Inc.
brian.garland@xxxxxxxxxx
--
This email and any files transmitted with it are confidential and intended solely for the use of the individual or company to whom they are addressed.
Do not disclose, distribute, or copy this email to others outside your company. If you have received this email in error, please notify the sender immediately and delete this email from your system.
--
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.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
