ACS/RSS - How current SQL environment can be overridden to instead use current *LIBL of the job nnnnnn/QUSER/QZDASOINIT?

Question

IBM i CLI (QCMDEXC): I've a file in library DATALIB, I do CHGLIBL, RUNQRY on the file and I get file data smoothly. Because *LIB DATALIB is in the *LIBL, it works fine.

I try to follow same on ACS's Run SQL Scripts, as below

CL: CHGLIBL LIBL(QTEMP TSTLIB1 DATALIB);
CL: CHGLIBL LIBL(QTEMP TSTLIB1 DATALIB) CURLIB(DATALIB);

SELECT * FROM DataFile;

CHGLIBL doesn't work, setting DATALIB as *CURLIB doesn't work either.

On checking the SQL Environment, I notice that while initiating RSS, the CURRENT SCHEMA gets set to TESTUSER (by default), and that is why I get the SQLSTATE 42704 error (SQLCODE -204).

Request note: In addition to [ibm-midrange], this issue is better scoped by tags [ibm-acs] and [db2-sql]. Looking at the acceptability and growing use of IBM ACS and DB2 for i SQL, I expected them to be in existence by now. Anyways, I tried creating them but I was told that it requires at least 1500 reputation, which I clearly lacks rn. So, the reputed one, if you're reading so far, could you please do the needful?

Answer 1

You need to use System (*SYS) naming when running SQL statement on the IBM i in order for the library list to be searched for unqualified table.

Edit --> JDBC Configurations

You'll want the Format tab.

Also note you can figure the library list you want to use on System tab.