Declaring a SQL statement as a variable in db2

I'm trying to pass a variable to my AS400 system but cannot get the syntax right.


--This is what I thought the DB2 syntax was
CREATE OR REPLACE  VARIABLE sqlstmnt VARCHAR(255);
SET sqlstmnt = '
SELECT SELECT * FROM icx
';

RunSqlStm sqlstmtnt;
DROP VARIABLE sqlstmnt;


--also tried
CREATE VARIABLE DYNAMIC :sqlstmnt VARCHAR(255);
SET :sqlstmnt = '

SELECT SELECT * FROM icx
';

RunSqlStm :sqlstmtnt;
DROP VARIABLE :sqlstmnt;


--How I would do this in SQL Server
--this is the goal
DECLARE sqlstmnt VARCHAR(255);
SET sqlstmnt = '

SELECT SELECT * FROM icx
';

EXEC sp_executesql sqlstmtnt;
atpratoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi atprato,

Are you trying to execute a string on the AS400?  If so, just declare it as a varchar object as you would in SQL Server.  After that there are somethings that you need to do differently.


DECLARE sqlstmnt VARCHAR(255);

SET sqlstmnt = 'SELECT * FROM icx';
EXECUTE IMMEDIATE sqlstmtnt;

That won't work.  EXECUTE IMMEDIATE cannot be used to execute a SELECT statement, but it can be used to execute most SQL.

SET sqlstmnt = 'INSERT INTO mytable VALUES (1, 2, 3)';
EXECUTE IMMEDIATE sqlstmtnt;

That would work, assuming that mytable exists and has 3 integer columns.


Kent
Dave FordSoftware Developer / Database AdministratorCommented:
If you're doing a dynamic SELECT statement, it'll return a result-set, so it's easiest to do that in a stored procedure. Give me a minute, and I'll whip up quick example for you.

-- DaveSlash
atpratoAuthor Commented:
Hello all, I should have mentioned, we are sending the statement as text via ODBC (from excel) and trying to get the result back out of the system to excel.  Manipulating the system (stored procedures etc is not an option.)

We do this all the time with a regular select statement but now we are trying to figure out the syntax to wrap the select statement in a variable.  Can anyone correct our syntax for AS400 DB2 iseries system?  (BPCS)
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

tliottaCommented:
How do you plan to handle any result set if you ever do get it to work? (It won't be easy getting it to work, if it ever will.)

An undefined result set seems pretty messy.

Tom
atpratoAuthor Commented:
When we do a straight select statement the result comes right back to the spreadsheet.  I was hoping the result of the select statement wrapped in a variable would work the same way.  Or maybe we need another step in the SQL text where we select the variable?  At any rate, let’s get the syntax right and then we can work on extracting the output correctly.  Thanks!
atpratoAuthor Commented:
Dear Kent, if I was going to use the "insert into" method, could I create and drop "mytable" on the fly?  In other words I would create the table and use the table as the result just long enough to select the result out of it and then delete mytable.

If that would work, what do I need to add to my SQL string to create mytable, select the result out of it and then drop it?  Need help with the syntax please.
Kent OlsenDBACommented:
Hi atprato,

You can drop and create tables this way.  However, you haven't really solved anything by doing this.  Presumably, the data already exists in the database, so moving it to a temporary table and querying the temp table instead of the permanent table doesn't get you anything.

You're better off just querying the permanent table.

Can you post an example of the query that you expect to run?


Thanks,
Kent
atpratoAuthor Commented:
There is no particular select statement.  We have many statements that we run to get data into excel via ODBC.  We are tying to find ways to make them run faster.  Someone suggested that running them wrapped in a varible would be faster.  But what I have gathered from this post is that running the varable won't return any data over the ODBC?  That is why I asked for the syntax to create and drop the table.
tliottaCommented:
We have many statements that we run to get data into excel via ODBC.

Tracing your connection should show that an ODBC client can take the statement and prepare itself for a data transfer. The client can contact the database server and request definitions of the columns. It can then reserve and prepare memory that will receive each column from a result set before even asking for it.

...running the varable won't return any data over the ODBC?

But when you simply send a string to a stored proc, the ODBC client has no reason to think that it should expect a result set nor that the result set has a set of columns that have data definitions that for values that client needs to store. The client has no idea that a result set is coming back nor what row formats are going to look like.

The stored proc hasn't been compiled to hold definitions either. It might have an attribute that says that a result set is expected, but it hasn't been told what the definitions are. It won't be told until run-time.

The effect is a client that doesn't know what it's asking for nor what to expect in return and a stored proc that doesn't know what it's supposed to do. Although it's technically possible that both could be created to handle such a case, it definitely would not be anywhere near as efficient as doing it without using a stored proc with a variable SQL statement.

Now, a stored proc that's written to return a specific result set (a known set of columns) can indeed execute a variable SQL statement. It might have a different WHERE clause each time, but it needs to have a fixed column list and a fixed FROM clause. You can't be changing the table to a different table each time. Not without some significant programming.

In short, it can be done with effort. It almost certainly will not be as efficient for the ODBC client running in Excel. Maybe VB could be used to properly prepare the ODBC request to help the efficiency.

Tom

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.