NAB using *SYS instead of *SQL naming standard

[beckhamrryyaann]

Hi, when using NAB and writing sql for my data source I do not want to hard code my libraries where my data files live. I would like to use the SQL naming convention *SYS, that way the SQL queries will use my library list. Right now it looks like it defaults to *SQL and throws my default SCHEMA as my user name home directory when trying to find a table. How can I tell NAB to use *SYS?

[robert.swanson]

We wouldn't typically recommend hard-coding library names in any NAB data sources. Instead just let the library list associated with the user's Valence environment control the files/tables that are accessed.

It sounds like you're saying the CGI job's library list is being ignored when you're pulling data through a NAB data source in your instance. Are you certain that's actually what's happening?

I'm pretty sure the "*SYS vs *SQL" naming convention thing is simply a reference to how library names or schemas are specified in SQL statements, with *SYS being the IBM i "slash" convention of LIBRARY/FILE, and *SQL being the SQL standard of LIBRARY.FILE. You should be able to use slashes and dots interchangeably in NAB data sources. Though, again, we typically do not recommend specifying library names at all.

If you see evidence your library list is being ignored, we may need to get on a meeting with you to see what's going on. We've never encountered that before.

[beckhamrryyaann]

Sorry Rob, I don't think my original question was clear. This is happening when a NAB SQL data source is using a SQL User Defined Function in the query. The Libraries are not hard coded in the actual NAB data source editor. The problem seems to be with the tables used in the UDF. Those tables are not able to be found unless I hard code them.

[robert.swanson]

Interesting. Does the UDF behave differently (with regard to the library list it's using) when you call it interactively, i.e. in STRSQL?

[beckhamrryyaann]

Robert, I created a simple test case to demonstrate what I'm talking about.
I created a SQL UDF called cnx_test. It has a reference to one file and I did not qualify the library in the UDF. When I try to call the UDF from a NAB data source it cannot find the library and you can see its trying to use my default home directory rbec5463. This looks like its using *SQL naming convention. I need to be able to use my UDF's without having to hard code the libraries. thanks!

[robert.swanson]

Okay, and to confirm... If you do this:

• Log in to a green screen 5250 session
• ADDLIBLE (library in which BLUEMST is located)
• STRSQL
• SELECT mmiprod01.test_cnx() as vv0 from sysibm.sysdummy1

This statement works as expected?

[beckhamrryyaann]

Well I learned something.
It is also doing the same thing when I use STRQL or ACS Run SQL Scripts. I didn't realize the library had to be hard coded. I am not a fan of that. Surely there has to be a way to get this to work without hard coding. Maybe its how I am creating my function....a parameter I need to change or something, but it is definitely not an issue with Valence. I will keep looking into this and post back if I have any updates.

[robert.swanson]

Yeah I believe under the covers UDFs execute in a separate batch job, so you have to hard-code the library names because it doesn't know the library list of the calling job.

You may just need to create separate copies of the UDF within each of your database libraries, with each UDF copy referencing the respective library in which it's located.