DB2 - How to run an ad hoc select query with a parameter in IBM System i Access for Windows GUI Tool

Question

I would like to run some ad hoc select statements in the IBM System I Navigator tool for DB2 using a variable that I declare.

For example, in the SQL Server world I would easily do this in the SQL Server Management Studio query window like so:

DECLARE @VariableName varchar(50);
SET @VariableName = 'blah blah';

select * from TableName where Column = @VariableName;

How can I do something similar in the IBM System I Navigator tool?

Solution 1

I ran across this post while searching for the same question. My coworker provided the answer. It is indeed possible to declare variables in an ad hoc SQL statement in Navigator. This is how it is done:

CREATE OR REPLACE VARIABLE variableName VARCHAR(50);
SET variableName = 'blah';
SELECT * FROM table WHERE column = variableName;
DROP VARIABLE variableName;

If you don't drop the variable name it will hang around until who knows when...

Solution 2

At the moment, we're working on the same issue at work. Unfortunaly, we concluded that this is not possible. I agree, it would be great but it just doesn't work that way. iNavigator doesn't support SET or Define. You can do that in embedded SQL but this is not embedded SQL. Even if you create a separate document (xxx.sql), then need to open this document to run the script what makes it an interactive script (that is, DECLARE SECTION is not allowed).

As an alternative, in the SQL screen/script you can use CL:. Anything after this prefix is executed as CL command. You may manipulate your tables (e.g. RNMF) this way.

As a second alternative, the iSeries does support Rexx scripts (default installed with the os). Rexx is good dynamic script language and it does support embedded SQL. I've done that a lot of times and it works great. I even created scripts for our production environment.

Just create one 'default' script with an example PREPARE and CURSOR statement and copy at will. With that script you can play around. See the Rexx manual for the correct syntax of exec-sql. Also, you do have STDIN and STDOUT but you can use 'OVRDBF' to point to a database table (physical file). Just let me know if you need an example Rexx script.

Notice that the manual "SQL embedded programming" does have Rexx examples.

Comments

[Ken Burkhardt]

Hmmmmm... not the answer I was hoping for! :-( "Is there someone else up there we could talk to?" (Monty Python and the Holy Grail (1975)

[robertnl]

If the going gets though, the though get going ... (but if "ad hoc sql + variables" is the thing you need, you should really try Rexx. Don't forget that it's interpreted. So, even with embedded sql: edit your source, save and run. It works like a charm)

[ajeh]

SQL State: 42601 Vendor Code: -104 Message: [SQL0104] Token OR was not valid.

[Charles]

Must be using an old version.. The CREATE OR REPLACE support was added at 7.1. As was the support for global variables.

[David]

I had to specify a schema to create the variable in... CREATE OR REPLACE VARIABLE schema.variable DECIMAL(8,0); ... and SET schema.variable = 20170721;