CODEWITHSUNDEEP
variablesglobalibm-midrangenavigator
Marcus
Marcus

Reputation: 1

IBM DB2: Run SQL Scripts

Using the iSeries Navigator, the "Run SQL Scripts" utility.

Is it possible to use a global variable to do the following:

  1. Create or Replace Variable FileName Char(21) Default 'V51BPCSF00.ESN'

  2. Select * From FileName

Thanks

Upvotes: 0

Views: 2819

Answers (2)

Gabriel
Gabriel

Reputation: 1

  1. Create or Replace Variable FileName Char(21) Default 'V51BPCSF00.ESN'

  2. SELECT FileName from sysibm.sysdummy1

sysibm.sysdummy1 is a "fake" file to allow this type of select that needs a complete SQL statement.

I specify a library for the variable so I know where it is, and delete it afterwards if it's serving as a temporary variable

Create or Replace Variable MY_LIB.FileName Char(21) Default 'V51BPCSF00.ESN'

Do stuff .....

DROP Variable MY_LIB.FileName

Upvotes: 0

Charles
Charles

Reputation: 23793

Theoretically, compound statements are supported (since 2014 in v7r1+ )so you might think to try something like so..

--create variable
Create or Replace Variable FileName Char(21) Default 'CWILT/MYTABLE';

then the compound statement

begin
  declare myStmt varchar(500);
  
  set myStmt = 'Select * from ' concat filename;
  
  prepare s1 from myStmt;
  execute s1;
end;

But that didn't work for me.

However, behind the scenes the DB is simply creating a stored procedure with your compound statement. You can simply do it yourself.

create or replace procedure cwilt5.test
language SQL
dynamic result sets 1
begin
  declare myStmt varchar(500);
  declare c1 cursor with return to client for s1 ;
  
  set myStmt = 'Select * from ' concat filename;
  
  prepare s1 from myStmt;
  open c1;
end;

While the above uses the global variable as requested, I'd probably just pass in the file name as a parameter.

Key thing to realize is that variables aren't supported in the FROM clause of a static SQL statement. You have to use dynamic SQL, PREPARE & EXECUTE or OPEN if you need to return a result set.

Upvotes: 1

Related Questions