CURLIB in SQL job

[p-behr]

db2i Version 1.15.1

The current library is not set properly in SQL job.
In Code for IBM i, I set the current library. I can use the RUNSQLSTM Action to create an unqualified object using *SYS naming, and it will be created in the current library.

When I start an SQL job, it uses the library list from Code for IBM i. The libraries are in the proper order, but the current library is not set.

If I use Ctrl+R to interactively run the same CREATE statement using *SYS naming, the table will be created...

But it's created in QGPL:

I can change to use *SQL naming instead of *SYS naming, it does seem to use the first library as the default (even without the current library being set). Would very much like a way to set the current library for *SYS naming.

Thanks!

[SanjulaGanepola]

@p-behr I am not an expert on JDBC options, but just from taking a look at the docs, it seems that this is the expected behavior for *SQL vs *SYS naming. In the case of *SYS naming there is no default SQL schema.

One option would be to set the CURRENT SCHEMA special register:

SET CURRENT SCHEMA MYLIB;

CREATE OR REPLACE TABLE example
(
    fld1 CHAR(5)
);

@forstie Am I missing some other way to do this?

[p-behr]

@SanjulaGanepola yes, I agree. I am using *SYS naming, and would like the objects to be created in the current library (which works if I use the RUNSQLSTM Action from Code for IBM i). The problem [I think] is that the current library is not getting set for the SQL job used by Db2 for i (all the libraries are there, but no current library), so the objects are being created in QGPL if I run the statement interactively...and I am not sure how to set the current library for the SQL job.

[forstie]

Hi both @p-behr and @SanjulaGanepola
Please review your configuration under the SQL JOB MANAGER.
Once you config this to match your preferences, use the 'Save settings to config' to avoid having to set them into the future.

[forstie]

I tried a test similar to Patrick's an observed the same behavior.
We're discussing this in an email exchange.

[SanjulaGanepola]

I am closing this issue assuming the problem was resolved over email. Please re-open if not.

[p-behr]

My library list in Code for IBM i:

When I start a database job, it gets the right libraries in the proper order:

The problem is that PBEHR is supposed to be set as the CURRENT LIBRARY.

If we look at the remote database job, the library is part other USR portion:

It should be set as current library:

This causes problem when creating unqualified database objects.
I usually do not specify the schema in my source; I generally control the target library by setting my current library.
I expect most objects (views excluded) to be created in the current library, but if CURLIB isn't set properly they end up in QGPL...

It could be that I'm missing something, or doing something wrong in this example...I am certainly open to suggestion :-)
But regardless, the current library is not set properly.
I do not have permission to reopen the issue.