Re: Run Sql Scripts Global Variables question

On 21-Mar-2014 08:24 -0700, Gary Thompson wrote:

Got latest version of Run Sql Scripts and noticed a new (to me)
Global Variables Tab at the bottom, to the right of the familiar
Messages.

I created a variable:

CREATE OR REPLACE @LIB1 VARCHAR(10);
SET @VAR1 = 'MYLIB1'

Variable shows in the Global Variables Tab as:
QGPL/@LIB1 'MYLIB1'

I then try a query:
select * from @LIB1/MYFILE;

QGPL is in the list of schemas in Sys i Nav and in the lib list of
the job supporting my Run Sql Scripts session, yet I've not been able
to run the query ?

Message SQL0204 shows the variable name used as the file name and
the file name used as the member name.

The jog log for my session shows: MYFILE in @LIB1 type *FILE not
found.

Similar results when using Naming convention *SQL ?

They are SQL [Global] Variables, not script[ing] variables. Where a [host or other] variable can be used within a SLQ statement remains the same as before; i.e. "Global variables can be used in any SQL statement that allows a variable."

FWiW, [ignoring the inconsistency in naming as both @VAR1 and @LIB1, thus assuming each reference is the one name @LIB1] even if the variable were supported in that context, the name would need to be explicitly qualified to ensure the value is not ambiguous; i.e. @LIB would need to be referred to in the statement as "select * from qgpl.@LIB/MYFILE" because, per the docs, "Names that are the same should be explicitly qualified."

<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcvariable.htm>
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference -> Statements
_CREATE VARIABLE_
"The CREATE VARIABLE statement defines a global variable at the application server. ..."

<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzch2refvar.htm>
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference -> Language elements
_Variables_
"A variable in an SQL statement specifies a value that can be changed when the SQL statement is executed.

There are several types of variables used in SQL statements:

_global variable_
Global variables are defined using the CREATE VARIABLE statement. For more information about how to refer to global variables see Global variables.
..."

<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzglobalvar.htm>
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference -> Language elements -> Variables
_Global variables_
"Global variables are named memory variables that you can access and modify through SQL statements.

Global variables enable you to share relational data between SQL statements without the need for application logic to support this data transfer. ...
...
Global variable names are qualified names. When a global variable is referenced without the schema name, the SQL path is used for name resolution.
...
Global variables can be used in any SQL statement that allows a variable. Global variables can be referenced within any expression except ...
..."