NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
D-30
Using DEFINEs With SQL Programs
You normally supply DEFINEs at explicit SQL compilation for any DEFINE names
you use in static SQL statements. SQL attempts to resolve such DEFINE names
during explicit compilation, if possible (even though the names might be re-resolved
at load or execution time according to the rules that follow). If corresponding
DEFINEs do not exist, the SQL compiler issues a warning and produces a program
that, although valid, requires recompilation with appropriate DEFINE values. (SQL
can automatically recompile programs at run time, as discussed in the NonStop
SQL/MP programming manual for your host language. If you prohibit
recompilation, however—or if appropriate DEFINEs are still missing by the time
the statement executes—an error occurs.)
Dynamic SQL statements are not affected by explicit compilation, so there is no
reason to supply DEFINEs for those statements prior to execution.
For SQL programs executable from Guardian, you can use the EXPLAIN DEFINES
option on the SQLCOMP command to automatically generate an OBEY command
file that contains ADD DEFINE commands for the DEFINEs used in your program.
To generate the obey file in TACL obey format, use the OBEY FORM option.
You can use the command file to add the same DEFINEs again at run time. See the
NonStop SQL/MP programming manual for your host language for details.
•
DEFINEs required at execution time
To use DEFINEs in programs, you must inherit DEFINEs from your TACL or OSS
shell process or use system procedure calls to create DEFINEs within your program.
An error occurs unless a DEFINE exists at execution time for each DEFINE name
used in an executed statement. The DEFINE must exist at the time SQL resolves the
corresponding DEFINE name.
You can direct your program to access a different set of objects than the ones you
specified at compilation by supplying different DEFINE values at run time than at
compile time. Depending on the compilation options you specified, the similarity
between the objects, and the SIMILARITY CHECK attribute of the objects,
changing DEFINE values at run time causes successful similarity checks,
recompilation, or errors.
To inherit DEFINEs in a program, set DEFMODE ON before you start the program;
to avoid inheriting DEFINEs, set DEFMODE OFF before you start the program,
then set it ON within the program.
DEFINEs you create from an executing program remain in effect until you alter
them, delete them, or terminate the program. DEFINEs you inherit from another
process and then modify within an executing program revert to their previous
attribute values (that is, the values they had when you started the program) when the
program terminates. Any changes you make to inherited attributes within the
program apply only within the program.
•
Resolution of DEFINE names in static SQL
DEFINE names in a static SQL statement that was compiled WITHOUT a
CONTROL QUERY BIND NAMES AT EXECUTION directive in effect are
resolved at SQL-load time (just before the first SQL statement in the program