Re: SQL with variables

Mark S. Waterbury wrote:

<<SNIP>>

1. edit "scripts" (text source physical file members) to remove
all qualification (library or schema names) from SQL statements.
2. Issue CHGCURLIB to the library ("schema" or collection in SQL
terminology) where you want to create your tables and views.
3. issue the RUNSQLSTM with the appropriate "script" member.

You could even create a CL program to automate steps 2 and 3
above, if needed.

This seems to work, regardless of whether *SQL or *SYS naming
convention is used (e.g. on the RUNSQLSTM command).

That doing so "seems to work", may be a false inference. Or perhaps inferred from cases where the current library is also specified as the DFTRDBCOL or is set as the /current schema/.?

AFaIK the only way the CREATE for either *SQL & *SYS naming would effect the same target library being *CURLIB, would be if\when the current library matched the /authorization identifier/; i.e. if the library name on the CHGCURLIB matched the user name used to perform the SQL. For example if both the user name and the current library were the same, then performed using either *SQL or *SYS, each would effect creating the VIEW into the library of that name; except when the unqualified referenced [TABLE, ?] objects were not also in the current library... then the statement would fail for *SQL, yet the statement would function for *SYS, but only when the unqualified referenced [TABLE, VIEW, ?] objects were found in the *LIBL [library list]. The *SYS even has the odd behavior of creating into the library of the based-on object(s) irrespective of current library; at least for CREATE VIEW [and for just one referenced TABLE in the FROM clause], so even current library is suspect as the assumed definitive target for a CREATE with either a[qualified or unqualified reference.

Regards, Chuck