Scenario:
Table 1 is in library lib1
Table 2 is in library lib2
Library List is set as lib1, lib2
When I use dynamic SQL like:
sqlcmd = 'select ''1'' from table1 a join table2 b on a.id = b.id';
exec sql PREPARE S1 FROM :sqlCMD;
exec sql DECLARE C1 scroll CURSOR FOR S1;
exec sql OPEN C1;
exec sql fetch C1 into :CharField;
I get the error table2 cannot be found.
If I use static SQL like this:
select '1' into :CharField from table1 a join table2 b on a.id = b.id
It does work. Why? Is there an SQL option to change this behaviour?
If I use:
from lib1.table1 a join lib2.table2 b on a.id = b.id
in dynamic SQL, it would also work.
Unqualified alias, constraint, external program, index, mask, nodegroup, package, permission, sequence, table, trigger, view, and XSR object names are implicitly qualified by the default schema.
The default schema is specified as follows:
For static SQL statements:
- If the DFTRDBCOL parameter is specified on the CRTSQLxxx command (or with the SET OPTION statement), the default schema is the schema-name that is specified for that parameter.
- In all other cases, the default schema is based on the naming convention. For SQL naming, the default schema is the authorization identifier of the statement.
For system naming, the default schema is the job library list (*LIBL).
- For dynamic SQL statements the default schema depends on whether a default schema has been explicitly specified. The mechanism for explicitly specifying this depends on the interface used to dynamically prepare and execute SQL statements.
- If a default schema is not explicitly specified: For SQL naming, the default schema is the run-time authorization identifier. For system naming, the default schema is the job library list (*LIBL).
You are probably going to want to add
exec sql
set option
NAMING = *SYS,
DYNDFTCOL = *YES,
DFTRDBCOL = *NONE;
optionally, this should also work
exec sql
set option
NAMING = *SYS,
DYNDFTCOL = *NO;