SQL Error *N (38501) - empty LibraryList

[vuxat]

Hi,

when I enable SQL support it throws error *N (38501) and it cannot load the library list.
I searched for the problem and found, that our IBM i Version is not up to date ...

Please add this to the Documentation for "Enable SQL":
If the IBM i Version is prior to 7.4 April 2021 (PTF Group SF99704 Level <= 11) then the library list cannot be retrieved, as the SPLIT command is not yet implemented.
https://www.ibm.com/support/pages/rpg-cafe-new-built-ins-lower-upper-and-split

src/api/IBMiContent.ts
SYSTOOLS.SPLIT(...)

If you feel bold enough and know a way to find out if SYSTOOLS.SPLIT is available or not
you could try to add a stand-in function in the temporary library (default = ILEDITOR) to mitigate this:
It looks a bit funky but it works. It is a variant from the following link I changed a bit on the last part.
For sure someone can come up with nicer code.
https://stackoverflow.com/questions/1305804/split-a-varchar-in-db2-to-retrieve-a-value-inside/10175295

CREATE OR REPLACE FUNCTION ILEDITOR.SPLIT(INPUT_LIST VARCHAR(3200),
	       DELIMITER VARCHAR(200))
RETURNS TABLE(ELEMENT VARCHAR(3200))
SPECIFIC SPLIT
LANGUAGE SQL
MODIFIES SQL DATA
NO EXTERNAL ACTION
F1: BEGIN
return
	with source(str, del) as
	(select INPUT_LIST, DELIMITER from sysibm.sysdummy1),
	target(str, del) as
	(select source.str, source.del from source
	where length(source.str) > 0
	union all
	select 
	(case when (instr(target.str, target.del) > 0) 
	then substr(target.str, 
	instr(target.str, target.del)+1, 
	length(target.str)-instr(target.str, target.del)) else null end),
	(case when (instr(target.str, target.del) > 0) 
	then target.del else null end)
	from target
	where length(target.str) > 0
	)
	select (CASE WHEN instr(str, target.del) > 0 
        THEN substring(str, 1, instr(str, target.del) -1) ELSE str END) str 
        from target where str is not null;
END

SELECT * FROM table(ILEDITOR.SPLIT('CURLIB,QTEMP,QGPL', ','))
more in line with the real split:
SELECT * FROM table(ILEDITOR.SPLIT(INPUT_LIST => 'CURLIB,QTEMP,QGPL', DELIMITER => ','))
(DROP FUNCTION ILEDITOR.SPLIT)

Thank you.

Best regards,
Stefan

[worksofliam]

@vuxat Hi!

What version of IBM i are you on out of curiosity? Generally we only support 7.3 and up and I'd be surprised if this doesn't work on 7.3 (I always use 7.3)

[vuxat]

@worksofliam V7R4M0, with the last "feature" PTFs from January 2021. With System Admins concentrating on security updates instead of everything (like said features).
SPLIT appeared in 7.3 and 7.4 in April 2021. So your PTFs SI76098 and SI76100 must have been updated after that date.

So the same goes with 7.3 prior to April 2021.

[worksofliam]

I think if you've found a bug, you should raise an issue! I can look at it right away.

[vuxat]

@worksofliam thank you. I opened issue #1081

[chrjorgensen]

@vuxat You're mixing RPG %SPLIT function arriving in April 2021 with the SQL function SYSTOOLS.SPLIT, which became available in April 2019.

It is - in my opinion as an IBM i system administrator - just as important to install the DB2 for i PTF group as it is to install the security, HIPER and cumulative PTF groups! The DB2 for i PTF group contains enhancements AND FIXES for SQL and the database, which all IBM i applications is built upon and use. I know there are some in the community, who work by the "if it ain't broken, don't fix it" mantra, but this is IMHO a dangerous attitude, since we don't know if it's broken before it has been exploited or an error has occurred. It is much better to stay current and get the fixes before the error/exploit occurs - and this will also bring new functionality as a bonus.

If you can't persuade the admins to install the DB2 for i PTF group and have access to a system where the function is installed, you can extract the SQL source for the function (all tools in SYSTOOLS can have their source generated) and create it on the system without the function.

[vuxat]

@worksofliam @chrjorgensen I reviewed the links and made some tests again.
Yes, I mixed up the target of the integration. (I think, because they said in the rpg cafe entry, that it is part of the DB2 fix package)
I am sorry for that.
I am new to this company and also IBM i in general (Oct. 2022) and the way the system was used and maintained in the last 30 years is ... "fascinating"...

But, from my tests I got the same sad result.
The following is a kind of documentation and also the solution - it got discovered!

I ran it with SYSTOOLS:

SELECT * FROM table(SYSTOOLS.SPLIT( INPUT_LIST => 'CURLIB,QTEMP', DELIMITER => ',' ))

Without SYSTOOLS, just to be sure - same.
According to the Documentation, it should be with SYSTOOLS ... sooo....
https://www.ibm.com/docs/en/i/7.4?topic=services-split-table-function

The SQL Error is this one:

38501	Error occurred while calling a user-defined function, procedure, or trigger (using the SIMPLE CALL or SIMPLE CALL WITH NULLS calling convention).	-443

Which is weird, because the function ... is there? ... but it is called incorrectly?
In DBeaver I can see a very short flash of the table with ORDINAL_POSITION, ELEMENT then the error is thrown.

Either some PTFs or fixes need to be applied or, as you stated, extract the function from systools and apply it myself.
I found this:
https://www.ibm.com/docs/en/i/7.4?topic=systools-using
Looks simple enough but I cannot see the Schema but in the 5250 terminal I found the library and the compiled programs.

I then used this SQL to get it out:

CALL QSYS2.GENERATE_SQL(DATABASE_OBJECT_NAME => 'SPLIT'
			, DATABASE_OBJECT_LIBRARY_NAME => 'SYSTOOLS'
			, DATABASE_OBJECT_TYPE => 'FUNCTION')

Finally - it worked - and now I also found the problem.

The ESCAPE char is the problem in my version of SPLIT. I attached it in a text file.
original_systools_split_20230221.txt

https://www.ibm.com/docs/en/i/7.4?topic=services-split-table-function

SELECT * FROM table(SYSTOOLS.SPLIT( INPUT_LIST => 'CURLIB,QTEMP', DELIMITER => ',', ESCAPE => ''))

No recreate or anything - just added the ...third... parameter so it does not default to NULL

After that I tried to just run the create again - same results (fail and success) with and without escape.
So it is a buggy version.

Can you maybe extract yours and attach it? It would be highly appreciated.
If not, I will try to repair it or run it over with my version until the right PTFs are applied.

Thank you anyway for your time and patience. Your work is awesome and I will try to find a way to use your vscode plugins.
original_systools_split_20230221.txt

[chrjorgensen]

@vuxat Welcome to the wonderful world of IBM i...! The place where old habits rarely die... 🤣
I really love this platform, it is so unique and offers so much.

Beware of DB2 on StackOverflow and other sites on the net - DB2 for i has some specialties different from the other DB2 products. Always check the SQL reference for IBM i first: https://www.ibm.com/docs/en/i/7.4?topic=reference-sql

I have extracted the SPLIT function from SYSTOOLS on my IBM i 7.4 sandbox:
SYSTOOLS_SPLIT_74.sql.txt

HTH

[vuxat]

I guess I have "Vanilla" 7.4 of DB2 for i

[vuxat]

@chrjorgensen Brutal workaround - I dropped it in SYSTOOLS, compiled it in the TEST library and then copied it over to SYSTOOLS via the 5250 terminal.
It SPLITS! (reset comments and permissions after that for "SYSTOOLS".SPLIT)

[chrjorgensen]

@vuxat Remember you can have multiple SQL function with the same name in a schema, they just need to have a different parameter signature. So maybe your first function was still there and the second was created beside it? This has fooled me several times, and I now always use the specific name for a function.

Glad to hear you made it work!

[chrjorgensen]

@vuxat Ah, okay - now I see that you watched the vanishing of the program object... well, then the function must have been removed as expected. Or your IBM i instance is so old and not up to date, that the function drop and/or create did not work correctly.

Please pick on the admins to install the DB2 for i PTF group! 😄

[vuxat]

@chrjorgensen I watched the function drop / being deleted many times - then afterwards ran the create again - but only the copy from another library version worked.
Yes, I will have a stern talking-to with them 👍
Thanks again and have a nice day!