Solved

Declaring a SQL statement as a variable in db2

Posted on 2012-03-09
11
1,747 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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
 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
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…

823 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