Hi, I have set the library DEVLIB on top of the library list by running the command addlible lib(devlib)
from the command line, so that file PF1 should get picked from that library as I am running one SQL select query from STRSQL
session on IBM i green screen without qualifying the file name with the library name select * from pf1
and getting error PF1 in DEVELOPER type *FILE not found
i.e. SQL0204
.
I can see that I put the library DEVLIB
on the library list and in error i.e. coming on STRSQL
session mentions library DEVELOPER
which is the same as my user profile
name. However, I checked on the machine and there is no library with my user profile created yet, and still, if I create it let’s say then why it’s not picking from the actual library present on top of the library list? Why it is not searching that file in the library list first?
As an alternative, I have to qualify the library name with the file name in the SQL query select * from devlib.pf1
on STRSQL
session to get the results which ran fine and gave me the resulting rows.
But again another error caught up here when qualifying the file name with the library name using slash(/)
instead of dot(.)
, the SQL query is select * from devlib/pf1
and got another error while running this query which is Qualified object name PF1 not valid
. i.e. SQL5016
cause was mentioned here:
Cause . . . . . : One of the following has occurred:
-- The syntax used for the qualified object name is not valid for the
naming option specified. With system naming, the qualified form of an
object name is schema-name/object-name or schema-name.object-name. With SQL
naming the qualified form of an object name is
authorization-name.object-name.
-- The syntax used for the qualified object name is not allowed.
User-defined types, functions, variables, and sequences cannot be qualified
with the schema name using the / separator in the system naming convention
when used in a query.
Recovery . . . : Do one of the following and try the request again:
-- If you want to use the SQL naming convention, specify the SQL naming
option and qualify the object names in the form
authorization-id.object-name.
-- If you want to use the system naming convention, specify the system
naming option and qualify the object names in the form
schema-name/object-name or schema-name.object-name.
-- With the system naming convention, either make sure the user-defined
types, functions, variables can be found in the current path or use the
period form of naming to qualify the object.
Now here, in this error, it talks about the SQL Naming convention thing which is causing both the errors I think. How to change the session attributes of strsql
to search library list namely the Naming convention attribute.
You can change the Naming convention attribute using various ways at different places.
F13(Shift + F1)
to change SQL session attributes onSTRSQL
session.EXEX SQL
SET option naming = *SYS
or
EXEX SQL
SET option naming = *SQL
RUNSQLSTM SRCFILE(libname/srcpf) SRCMBR(srcmbr)
COMMIT(*none) DFTRDBCOL(libname) DBGVIEW(*source)
NAMING(*SYS)
or
RUNSQLSTM SRCFILE(libname/srcpf) SRCMBR(srcmbr)
COMMIT(*none) DFTRDBCOL(libname) DBGVIEW(*source)
NAMING(*SQL)
Looking at both errors
SQL0204
andSQL5016
it is clear that the*SQL
Naming convention is set as default in yourSTRSQL
session attributes.With the
*SQL
Naming convention being set on yourSTRSQL
session you cannot use the qualifierslash(/)
and instead, you have to only rely on usingdot(.)
as a qualifier. Also, with the*SQL
naming convention it will search for the file/table in the schema name same as the user profile on the IBM i system.Therefore, both errors
SQL0204
i.e.PF in DEVELOPER type *FILE not found,
andSQL5016
i.e.Qualified object name PF1 not valid.
are valid errors and they are more of a type of Diagnostic error.Therefore, to fix this problem use both
slash(/)
anddot(.)
as a qualifier and run SQL queries without the need to qualify file/table with library name and instead search in the set library list in the same session you need to change theSTRSQL
session attribute namely Naming convention. So, please change the Naming convention attribute from*SQL
to*SYS
. You can do so by these steps:STRSQL
session pressF13 (shift + F1)
. It will show the following options:option 1
to change session attributes. It will show the following menu:*SQL
to*SYS
.STRSQL
session.ADDLIBLE LIB(DEVLIB)
. Go back to theSTRSQL
session and run SQL queries without qualifying the library name with file/table name or even using any qualifier eitherslash(/)
ordot(.)
if you are qualifying the file/table name.