Help needed : How to Execute a string SQL statement with DB2

Hi,

I'm now working with DB2, and I can't find out how to execute the
contents of a string Statement, without using a Java/... procedure,
only using SQL statements.
I know that some SQBDs such as SQL Server allows it as the exemple
bellow shows :
EXECUTE 'SELECT * FROM Test'

Is there any way to do so with DB2 ?

Thanks by advance,

Cédric

re: Help needed : How to Execute a string SQL statement with DB2

"Doohan W." <doohan@caramail.com> wrote in message
news:4338ffe6.0407210100.3bcc8129@posting.google.c om...[color=blue]
> Hi,
>
> I'm now working with DB2, and I can't find out how to execute the
> contents of a string Statement, without using a Java/... procedure,
> only using SQL statements.
> I know that some SQBDs such as SQL Server allows it as the exemple
> bellow shows :
> EXECUTE 'SELECT * FROM Test'
>
> Is there any way to do so with DB2 ?
>
> Thanks by advance,
>[/color]
Your question isn't very clear so it is very hard to answer. Do you want to
execute this "string Statement" from a command line or in an application? If
you are executing in an application, what language is the application using?

Also, what DB2 version are you using? What operating system are you using?

Rhino

re: Help needed : How to Execute a string SQL statement with DB2

"Rhino" <rhino1@NOSPAM.sympatico.ca> wrote in message news:<4suLc.20329$Gf7.805126@news20.bellglobal.com >...[color=blue]
> "Doohan W." <doohan@caramail.com> wrote in message
> news:4338ffe6.0407210100.3bcc8129@posting.google.c om...[color=green]
> > Hi,
> >
> > I'm now working with DB2, and I can't find out how to execute the
> > contents of a string Statement, without using a Java/... procedure,
> > only using SQL statements.
> > I know that some SQBDs such as SQL Server allows it as the exemple
> > bellow shows :
> > EXECUTE 'SELECT * FROM Test'
> >
> > Is there any way to do so with DB2 ?
> >
> > Thanks by advance,
> >[/color]
> Your question isn't very clear so it is very hard to answer. Do you want to
> execute this "string Statement" from a command line or in an application? If
> you are executing in an application, what language is the application using?
>
> Also, what DB2 version are you using? What operating system are you using?
>
> Rhino[/color]

Hi,

Well, my matter is the following :
I need to retrieve the description of all the products that are of
the same type.
According to the model chosen, the description is not in the Product
table, but in a Detail table, which is different for each type of
product and has its name contained in the ProductType table (some kind
of dynamic access).
For example, here is the description of the tables used :
CREATE TABLE ProductType(
CType CHAR(3),
...,
TableName VARCHAR(50),
LabelName VARCHAR(50),
KeyProduct VARCHAR(50),
...
)
CREATE TABLE Product(
NPRD DECIMAL(10),
CType CHAR(3),
...,
CommercialReference DECIMAL(10),
...
)
CREATE TABLE DetailVPC(
NVPC DECIMAL(10),
...,
LDES VARCHAR(255),
...,
CREFPRD DECIMAL(10)
)
CREATE TABLE DetailABO(
NABO DECIMAL(8),
...,
LABODES VARCHAR(255),
...,
NREFPRD DECIMAL(10)
)

Having some network overflow, I need to reduce the number of
requests.
Thus, here is the SQL request I had with SQL Server (This piece of
code was in an SQL procedure) :

DECLARE @SQL AS VARCHAR(1000)
SELECT @SQL = 'SELECT P.NPRD AS ProductNumber, P.CommercialReference,
X.' + LabelName + ' AS Description FROM Product AS P INNER JOIN ' +
TableName + ' AS X ON X.' + ProductKey + ' = P.NPRD' FROM ProductType
WHERE CType = @ProductType
EXECUTE @SQL

I'd like to know if DB2 allows me to do so using only SQL statements
(DB2 v8.1.4 using both AS400 and ZLinux).
I've got some alternative ways of working, such as writing the
procedure in JAVA, but my boss'd rather not as JAVA is not understood
by every one in the firm.

Thanks.

Cédric

re: Help needed : How to Execute a string SQL statement with DB2

"Doohan W." <doohan@caramail.com> wrote in message
news:4338ffe6.0407220159.22ea45b5@posting.google.c om...[color=blue]
> "Rhino" <rhino1@NOSPAM.sympatico.ca> wrote in message[/color]
news:<4suLc.20329$Gf7.805126@news20.bellglobal.com >...[color=blue][color=green]
> > "Doohan W." <doohan@caramail.com> wrote in message
> > news:4338ffe6.0407210100.3bcc8129@posting.google.c om...[color=darkred]
> > > Hi,
> > >
> > > I'm now working with DB2, and I can't find out how to execute the
> > > contents of a string Statement, without using a Java/... procedure,
> > > only using SQL statements.
> > > I know that some SQBDs such as SQL Server allows it as the exemple
> > > bellow shows :
> > > EXECUTE 'SELECT * FROM Test'
> > >
> > > Is there any way to do so with DB2 ?
> > >
> > > Thanks by advance,
> > >[/color]
> > Your question isn't very clear so it is very hard to answer. Do you want[/color][/color]
to[color=blue][color=green]
> > execute this "string Statement" from a command line or in an[/color][/color]
application? If[color=blue][color=green]
> > you are executing in an application, what language is the application[/color][/color]
using?[color=blue][color=green]
> >
> > Also, what DB2 version are you using? What operating system are you[/color][/color]
using?[color=blue][color=green]
> >
> > Rhino[/color]
>
> Hi,
>
> Well, my matter is the following :
> I need to retrieve the description of all the products that are of
> the same type.
> According to the model chosen, the description is not in the Product
> table, but in a Detail table, which is different for each type of
> product and has its name contained in the ProductType table (some kind
> of dynamic access).
> For example, here is the description of the tables used :
> CREATE TABLE ProductType(
> CType CHAR(3),
> ...,
> TableName VARCHAR(50),
> LabelName VARCHAR(50),
> KeyProduct VARCHAR(50),
> ...
> )
> CREATE TABLE Product(
> NPRD DECIMAL(10),
> CType CHAR(3),
> ...,
> CommercialReference DECIMAL(10),
> ...
> )
> CREATE TABLE DetailVPC(
> NVPC DECIMAL(10),
> ...,
> LDES VARCHAR(255),
> ...,
> CREFPRD DECIMAL(10)
> )
> CREATE TABLE DetailABO(
> NABO DECIMAL(8),
> ...,
> LABODES VARCHAR(255),
> ...,
> NREFPRD DECIMAL(10)
> )
>
> Having some network overflow, I need to reduce the number of
> requests.
> Thus, here is the SQL request I had with SQL Server (This piece of
> code was in an SQL procedure) :
>
> DECLARE @SQL AS VARCHAR(1000)
> SELECT @SQL = 'SELECT P.NPRD AS ProductNumber, P.CommercialReference,
> X.' + LabelName + ' AS Description FROM Product AS P INNER JOIN ' +
> TableName + ' AS X ON X.' + ProductKey + ' = P.NPRD' FROM ProductType
> WHERE CType = @ProductType
> EXECUTE @SQL
>
> I'd like to know if DB2 allows me to do so using only SQL statements
> (DB2 v8.1.4 using both AS400 and ZLinux).
> I've got some alternative ways of working, such as writing the
> procedure in JAVA, but my boss'd rather not as JAVA is not understood
> by every one in the firm.
>[/color]
I think I'm going to let someone else try to answer your question; I have
very little familiarity with DB2 on AS/400 and none with ZLinux.
Furthermore, I'm confused when you say you are using DB2 V8.1.4 on AS/400:
as far as I can determine from the IBM site, the latest version of DB2 on
AS/400 is V5.3.

Rhino

re: Help needed : How to Execute a string SQL statement with DB2

This statement...

EXECUTE IMMEDIATE variable

will dynamically execute the statement in the string variable. It
doesn't work for SELECT statements; DB2 SQL syntax is a bit behind the
times :)

Breck

doohan@caramail.com (Doohan W.) wrote in message news:<4338ffe6.0407220159.22ea45b5@posting.google. com>...[color=blue]
> "Rhino" <rhino1@NOSPAM.sympatico.ca> wrote in message news:<4suLc.20329$Gf7.805126@news20.bellglobal.com >...[color=green]
> > "Doohan W." <doohan@caramail.com> wrote in message
> > news:4338ffe6.0407210100.3bcc8129@posting.google.c om...[color=darkred]
> > > Hi,
> > >
> > > I'm now working with DB2, and I can't find out how to execute the
> > > contents of a string Statement, without using a Java/... procedure,
> > > only using SQL statements.
> > > I know that some SQBDs such as SQL Server allows it as the exemple
> > > bellow shows :
> > > EXECUTE 'SELECT * FROM Test'
> > >
> > > Is there any way to do so with DB2 ?
> > >
> > > Thanks by advance,
> > >[/color]
> > Your question isn't very clear so it is very hard to answer. Do you want to
> > execute this "string Statement" from a command line or in an application? If
> > you are executing in an application, what language is the application using?
> >
> > Also, what DB2 version are you using? What operating system are you using?
> >
> > Rhino[/color]
>
> Hi,
>
> Well, my matter is the following :
> I need to retrieve the description of all the products that are of
> the same type.
> According to the model chosen, the description is not in the Product
> table, but in a Detail table, which is different for each type of
> product and has its name contained in the ProductType table (some kind
> of dynamic access).
> For example, here is the description of the tables used :
> CREATE TABLE ProductType(
> CType CHAR(3),
> ...,
> TableName VARCHAR(50),
> LabelName VARCHAR(50),
> KeyProduct VARCHAR(50),
> ...
> )
> CREATE TABLE Product(
> NPRD DECIMAL(10),
> CType CHAR(3),
> ...,
> CommercialReference DECIMAL(10),
> ...
> )
> CREATE TABLE DetailVPC(
> NVPC DECIMAL(10),
> ...,
> LDES VARCHAR(255),
> ...,
> CREFPRD DECIMAL(10)
> )
> CREATE TABLE DetailABO(
> NABO DECIMAL(8),
> ...,
> LABODES VARCHAR(255),
> ...,
> NREFPRD DECIMAL(10)
> )
>
> Having some network overflow, I need to reduce the number of
> requests.
> Thus, here is the SQL request I had with SQL Server (This piece of
> code was in an SQL procedure) :
>
> DECLARE @SQL AS VARCHAR(1000)
> SELECT @SQL = 'SELECT P.NPRD AS ProductNumber, P.CommercialReference,
> X.' + LabelName + ' AS Description FROM Product AS P INNER JOIN ' +
> TableName + ' AS X ON X.' + ProductKey + ' = P.NPRD' FROM ProductType
> WHERE CType = @ProductType
> EXECUTE @SQL
>
> I'd like to know if DB2 allows me to do so using only SQL statements
> (DB2 v8.1.4 using both AS400 and ZLinux).
> I've got some alternative ways of working, such as writing the
> procedure in JAVA, but my boss'd rather not as JAVA is not understood
> by every one in the firm.
>
> Thanks.
>
> Cédric[/color]

re: Help needed : How to Execute a string SQL statement with DB2

Thanks Breck...
I'll make up with this.

Cédric

bcarter@risingroad.com (Breck Carter) wrote in message news:<f2e33aaa.0407220954.55129733@posting.google. com>...[color=blue]
> This statement...
>
> EXECUTE IMMEDIATE variable
>
> will dynamically execute the statement in the string variable. It
> doesn't work for SELECT statements; DB2 SQL syntax is a bit behind the
> times :)
>
> Breck
>
> doohan@caramail.com (Doohan W.) wrote in message news:<4338ffe6.0407220159.22ea45b5@posting.google. com>...[color=green]
> > "Rhino" <rhino1@NOSPAM.sympatico.ca> wrote in message news:<4suLc.20329$Gf7.805126@news20.bellglobal.com >...[color=darkred]
> > > "Doohan W." <doohan@caramail.com> wrote in message
> > > news:4338ffe6.0407210100.3bcc8129@posting.google.c om...
> > > > Hi,
> > > >
> > > > I'm now working with DB2, and I can't find out how to execute the
> > > > contents of a string Statement, without using a Java/... procedure,
> > > > only using SQL statements.
> > > > I know that some SQBDs such as SQL Server allows it as the exemple
> > > > bellow shows :
> > > > EXECUTE 'SELECT * FROM Test'
> > > >
> > > > Is there any way to do so with DB2 ?
> > > >
> > > > Thanks by advance,
> > > >
> > > Your question isn't very clear so it is very hard to answer. Do you want to
> > > execute this "string Statement" from a command line or in an application? If
> > > you are executing in an application, what language is the application using?
> > >
> > > Also, what DB2 version are you using? What operating system are you using?
> > >
> > > Rhino[/color]
> >
> > Hi,
> >
> > Well, my matter is the following :
> > I need to retrieve the description of all the products that are of
> > the same type.
> > According to the model chosen, the description is not in the Product
> > table, but in a Detail table, which is different for each type of
> > product and has its name contained in the ProductType table (some kind
> > of dynamic access).
> > For example, here is the description of the tables used :
> > CREATE TABLE ProductType(
> > CType CHAR(3),
> > ...,
> > TableName VARCHAR(50),
> > LabelName VARCHAR(50),
> > KeyProduct VARCHAR(50),
> > ...
> > )
> > CREATE TABLE Product(
> > NPRD DECIMAL(10),
> > CType CHAR(3),
> > ...,
> > CommercialReference DECIMAL(10),
> > ...
> > )
> > CREATE TABLE DetailVPC(
> > NVPC DECIMAL(10),
> > ...,
> > LDES VARCHAR(255),
> > ...,
> > CREFPRD DECIMAL(10)
> > )
> > CREATE TABLE DetailABO(
> > NABO DECIMAL(8),
> > ...,
> > LABODES VARCHAR(255),
> > ...,
> > NREFPRD DECIMAL(10)
> > )
> >
> > Having some network overflow, I need to reduce the number of
> > requests.
> > Thus, here is the SQL request I had with SQL Server (This piece of
> > code was in an SQL procedure) :
> >
> > DECLARE @SQL AS VARCHAR(1000)
> > SELECT @SQL = 'SELECT P.NPRD AS ProductNumber, P.CommercialReference,
> > X.' + LabelName + ' AS Description FROM Product AS P INNER JOIN ' +
> > TableName + ' AS X ON X.' + ProductKey + ' = P.NPRD' FROM ProductType
> > WHERE CType = @ProductType
> > EXECUTE @SQL
> >
> > I'd like to know if DB2 allows me to do so using only SQL statements
> > (DB2 v8.1.4 using both AS400 and ZLinux).
> > I've got some alternative ways of working, such as writing the
> > procedure in JAVA, but my boss'd rather not as JAVA is not understood
> > by every one in the firm.
> >
> > Thanks.
> >
> > Cédric[/color][/color]

re: Help needed : How to Execute a string SQL statement with DB2

Thanks anyway Rhino...

I'll try and get the real version number then, as it seems that my
administrator mistook... Probably a client version number instead of
the server version number I guess...

Anyway, I think this is an SQL statement usage that I miss... Perhaps
DB2 is still far from using SQL 2.

Cédric

"Rhino" <rhino1@NOSPAM.sympatico.ca> wrote in message news:<YOQLc.1158$Fj.81571@news20.bellglobal.com>.. .[color=blue]
> "Doohan W." <doohan@caramail.com> wrote in message
> news:4338ffe6.0407220159.22ea45b5@posting.google.c om...[color=green]
> > "Rhino" <rhino1@NOSPAM.sympatico.ca> wrote in message[/color]
> news:<4suLc.20329$Gf7.805126@news20.bellglobal.com >...[color=green][color=darkred]
> > > "Doohan W." <doohan@caramail.com> wrote in message
> > > news:4338ffe6.0407210100.3bcc8129@posting.google.c om...
> > > > Hi,
> > > >
> > > > I'm now working with DB2, and I can't find out how to execute the
> > > > contents of a string Statement, without using a Java/... procedure,
> > > > only using SQL statements.
> > > > I know that some SQBDs such as SQL Server allows it as the exemple
> > > > bellow shows :
> > > > EXECUTE 'SELECT * FROM Test'
> > > >
> > > > Is there any way to do so with DB2 ?
> > > >
> > > > Thanks by advance,
> > > >
> > > Your question isn't very clear so it is very hard to answer. Do you want[/color][/color]
> to[color=green][color=darkred]
> > > execute this "string Statement" from a command line or in an[/color][/color]
> application? If[color=green][color=darkred]
> > > you are executing in an application, what language is the application[/color][/color]
> using?[color=green][color=darkred]
> > >
> > > Also, what DB2 version are you using? What operating system are you[/color][/color]
> using?[color=green][color=darkred]
> > >
> > > Rhino[/color]
> >
> > Hi,
> >
> > Well, my matter is the following :
> > I need to retrieve the description of all the products that are of
> > the same type.
> > According to the model chosen, the description is not in the Product
> > table, but in a Detail table, which is different for each type of
> > product and has its name contained in the ProductType table (some kind
> > of dynamic access).
> > For example, here is the description of the tables used :
> > CREATE TABLE ProductType(
> > CType CHAR(3),
> > ...,
> > TableName VARCHAR(50),
> > LabelName VARCHAR(50),
> > KeyProduct VARCHAR(50),
> > ...
> > )
> > CREATE TABLE Product(
> > NPRD DECIMAL(10),
> > CType CHAR(3),
> > ...,
> > CommercialReference DECIMAL(10),
> > ...
> > )
> > CREATE TABLE DetailVPC(
> > NVPC DECIMAL(10),
> > ...,
> > LDES VARCHAR(255),
> > ...,
> > CREFPRD DECIMAL(10)
> > )
> > CREATE TABLE DetailABO(
> > NABO DECIMAL(8),
> > ...,
> > LABODES VARCHAR(255),
> > ...,
> > NREFPRD DECIMAL(10)
> > )
> >
> > Having some network overflow, I need to reduce the number of
> > requests.
> > Thus, here is the SQL request I had with SQL Server (This piece of
> > code was in an SQL procedure) :
> >
> > DECLARE @SQL AS VARCHAR(1000)
> > SELECT @SQL = 'SELECT P.NPRD AS ProductNumber, P.CommercialReference,
> > X.' + LabelName + ' AS Description FROM Product AS P INNER JOIN ' +
> > TableName + ' AS X ON X.' + ProductKey + ' = P.NPRD' FROM ProductType
> > WHERE CType = @ProductType
> > EXECUTE @SQL
> >
> > I'd like to know if DB2 allows me to do so using only SQL statements
> > (DB2 v8.1.4 using both AS400 and ZLinux).
> > I've got some alternative ways of working, such as writing the
> > procedure in JAVA, but my boss'd rather not as JAVA is not understood
> > by every one in the firm.
> >[/color]
> I think I'm going to let someone else try to answer your question; I have
> very little familiarity with DB2 on AS/400 and none with ZLinux.
> Furthermore, I'm confused when you say you are using DB2 V8.1.4 on AS/400:
> as far as I can determine from the IBM site, the latest version of DB2 on
> AS/400 is V5.3.
>
> Rhino[/color]