Free hand sql with promt variables

Damien Blood

Free hand sql with promt variables
Hi All,

Is there a way in DB2 that you can
write basic sql and it will prompt you
for variable input.

i.e. In oracle you could use:

select * from emp
where mgr_no = &ID
order by emp_no;

Then it would prompt you for &ID.

Enter value for ID:

Is there a similar method in DB2?

I am writing some freehand sql in a Business Objects
front end application and I want it to prompt users
for input.

Any ideas?

Cheers

Regards

Damien Blood
Support Consultant


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

Should you require further information please contact
us using the details below.

website: http://www.acuma.co.uk
email: [login to unmask email]
**********************************************************************



Thomas Schulz

Re: Free hand sql with promt variables
(in response to Jeff L'Italien)
Hey Damien

you could realize this with a ISPF Dialog.

REXX Procedure
Send a Panel to the User
Let enter them the variables
Build your Spufi with REXX and submit it

Best regrads
Thomas

Damien Blood schrieb:
>
> Hi All,
>
> Is there a way in DB2 that you can
> write basic sql and it will prompt you
> for variable input.
>
> i.e. In oracle you could use:
>
> select * from emp
> where mgr_no = &ID
> order by emp_no;
>
> Then it would prompt you for &ID.
>
> Enter value for ID:
>
> Is there a similar method in DB2?
>
> I am writing some freehand sql in a Business Objects
> front end application and I want it to prompt users
> for input.
>
> Any ideas?
>
> Cheers
>
> Regards
>
> Damien Blood
> Support Consultant
>
> **********************************************************************
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
> are addressed. If you have received this email in error please notify
> the system manager.
>
> Should you require further information please contact
> us using the details below.
>
> website: http://www.acuma.co.uk
> email: [login to unmask email]
> **********************************************************************
>
>
>



Jeff L'Italien

Re: Free hand sql with promt variables
(in response to Damien Blood)
Damien,

This type of functionality is available within the QMF toolset.

Regards,
Jeff L'Italien
American Express



From: "Damien Blood" <[login to unmask email]>@RYCI.COM> on 01/10/2002 02:43
PM GMT

Please respond to "DB2 Data Base Discussion List" <[login to unmask email]>

Sent by: "DB2 Data Base Discussion List" <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Free hand sql with promt variables


Hi All,

Is there a way in DB2 that you can
write basic sql and it will prompt you
for variable input.

i.e. In oracle you could use:

select * from emp
where mgr_no = &ID
order by emp_no;

Then it would prompt you for &ID.

Enter value for ID:

Is there a similar method in DB2?

I am writing some freehand sql in a Business Objects
front end application and I want it to prompt users
for input.

Any ideas?

Cheers

Regards

Damien Blood
Support Consultant


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

Should you require further information please contact
us using the details below.

website: http://www.acuma.co.uk
email: [login to unmask email]
**********************************************************************








Mark Anzmann

Re: Free hand sql with promt variables
(in response to Thomas Schulz)
You can do that exact same function within QMF...

Mark Anzmann
Tech Services Manager
DHRIS Program - Team Accenture
Phone: 410-238-1213
email: [login to unmask email]

>>> [login to unmask email] 01/10/02 09:43AM >>>
Hi All,

Is there a way in DB2 that you can
write basic sql and it will prompt you
for variable input.

i.e. In oracle you could use:

select * from emp
where mgr_no = &ID
order by emp_no;

Then it would prompt you for &ID.

Enter value for ID:

Is there a similar method in DB2?

I am writing some freehand sql in a Business Objects
front end application and I want it to prompt users
for input.

Any ideas?

Cheers

Regards

Damien Blood
Support Consultant


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

Should you require further information please contact
us using the details below.

website: http://www.acuma.co.uk
email: [login to unmask email]
**********************************************************************


the



David Seibert

Re: Free hand sql with promt variables
(in response to Mark Anzmann)
You can code and store queries to accomplish this using QMF (Query
Management Facility), a separately purchasable product/feature to accomplish
this. There's a QMF on z/OS, OS/390 and a QMF for Windows as far as I know.

What's your operating system?

David Seibert
Compuware Corporation File-AID Product Architect
[login to unmask email]



Richard Sharpe

Re: Free hand sql with promt variables
(in response to David Seibert)
How about on UNIX or Linux ?

-----Original Message-----
From: Seibert, Dave [mailto:[login to unmask email]
Sent: Thursday, January 10, 2002 10:46 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Free hand sql with promt variables


You can code and store queries to accomplish this using QMF (Query
Management Facility), a separately purchasable product/feature to accomplish
this. There's a QMF on z/OS, OS/390 and a QMF for Windows as far as I know.

What's your operating system?

David Seibert
Compuware Corporation File-AID Product Architect
[login to unmask email]








Max Scarpa

Re: Free hand sql with promt variables
(in response to Richard Sharpe)
Hi

I did somethin similar using REXX and DSQCIX (REXX-QMF interface) but I
don't know if is what you need.

Regards
Max Scarpa



Todd Burch

Re: Free hand sql with promt variables
(in response to Max Scarpa)
I'm not sure how "orable" works, but you might consider this.

DB2 SQL allows what are called parameter markers. They are represented by
questions marks (?) in dynamic SQL. They are placeholders for variables in
dynamic SQL, where you would typically have host variables in static SQL.



A quick example:

Static SQL:

INSERT INTO TABLE (COL1, COL2) VALUES (:HV1, '1') ;

Prior to issuing this INSERT, you would initialize variable "hv" to the value you
wanted to insert.

Dynamic SQL:

INSERT INTO TABLE (COL1, COL2) VALUES (? , '1') ;

This statement would be PREPAREd, and then you would issue "EXECUTE ... USING :HV"
or "EXECUTE ... USING DESCRIPTOR D1..."

The difference in which one you would use depends on when you have the data
available to insert. If you don't have the data at PREPARE time, use parameter markers.
Pretty much any dialog language (like ISPF dialog services) can be used to parse SQL
statements and look for variables or symbolics, prompt a user for a value, complete the
SQL statements, and pass it off to a process that can issue the SQL. Do you want to
buy or build? Are you aware of SPUFI (an ISPF dialog that comes with DB2 OS/390)?


Now, I don't know how QMF does it (does it delay the PREPARE until it resolves all
the variables, or does it use parameter markers and supply the values at EXECUTE time?),
so QMF may or may not be a solution to your problem.

<if you are OS/390>
CDB Software has a product called CDB/REXX that is an extension to REXX that allows you to
issue DB2 SQL from REXX. IBM has one too, called DSNREXX, but I don't know if it allows
the use of parameter markers. Relational Architects has one, and BMC picked one up when
it bought Boole & Babbage (not sure of its availability or support though).

CDB supports parameter markers for all SQL statements that allow them.
</if you are OS/390>

Todd Burch



-----Original Message-----
From: [login to unmask email]
Sent: Thursday, January 10, 2002 9:31 AM
To: [login to unmask email]
Subject: Free hand sql with promt variables


-------------------------------------------------------------------------- --
Hi All,

Is there a way in DB2 that you can
write basic sql and it will prompt you
for variable input.

i.e. In oracle you could use:

select * from emp
where mgr_no = &ID
order by emp_no;

Then it would prompt you for &ID.

Enter value for ID:

Is there a similar method in DB2?

I am writing some freehand sql in a Business Objects
front end application and I want it to prompt users
for input.

Any ideas?

Cheers

Regards

Damien Blood
Support Consultant
Todd Burch
CDB Software, Inc.
Tel: 800-627-6561 or 281-920-3305
Internet Address: [login to unmask email]
Home Page: http://www.cdbsoftware.com



Sanjeev (CTS) S

Re: Free hand sql with promt variables
(in response to Todd Burch )
Damien,

Andy pointed out very correctly that it is not what is done by DB2 or "may
be" oracle. It is the job of the parser which can be front end/complete
product. I am also not aware of if it comes with Oracle or not. But i am
sure it can be done with QMF and DB2, and QMF is a separate product.

With Best Regards,
Sanjeev

> -----Original Message-----
> From: Damien Blood [SMTP:[login to unmask email]
> Sent: Thursday, January 10, 2002 8:13 PM
> To: [login to unmask email]
> Subject: Free hand sql with promt variables
>
> Hi All,
>
> Is there a way in DB2 that you can
> write basic sql and it will prompt you
> for variable input.
>
> i.e. In oracle you could use:
>
> select * from emp
> where mgr_no = &ID
> order by emp_no;
>
> Then it would prompt you for &ID.
>
> Enter value for ID:
>
> Is there a similar method in DB2?
>
> I am writing some freehand sql in a Business Objects
> front end application and I want it to prompt users
> for input.
>
> Any ideas?
>
> Cheers
>
> Regards
>
> Damien Blood
> Support Consultant
>
>
> **********************************************************************
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
> are addressed. If you have received this email in error please notify
> the system manager.
>
> Should you require further information please contact
> us using the details below.
>
> website: http://www.acuma.co.uk
> email: [login to unmask email]
> **********************************************************************
>
>
>
>
>