Solved

Declaring a SQL statement as a variable in db2

Posted on 2012-03-09
11
1,665 Views
Last Modified: 2013-08-01
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;
0
Comment
Question by:atprato
  • 4
  • 2
  • 2
  • +1
11 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 37703464
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
0
 
LVL 18

Expert Comment

by:daveslash
ID: 37703567
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
0
 

Author Comment

by:atprato
ID: 37703611
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)
0
 
LVL 27

Expert Comment

by:tliotta
ID: 37703820
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
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:atprato
ID: 37705266
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!
0
 

Author Comment

by:atprato
ID: 37705308
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.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37705393
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
0
 

Author Comment

by:atprato
ID: 37705507
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.
0
 
LVL 27

Accepted Solution

by:
tliotta earned 500 total points
ID: 37716340
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
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now