By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,227 Members | 1,025 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,227 IT Pros & Developers. It's quick & easy.

Stored Procedure output into File

P: n/a
VK
Hello,
Can some one suggest me a way of putting Stored procedure output into a
file, the file should be available on my Computer, I don't want to use
FTP process.
Thanks
vk

Jan 26 '07 #1
Share this Question
Share on Google+
9 Replies


P: n/a
VK
Just any update, am using DB2 UDB V8.0

On Jan 26, 2:11 pm, "VK" <kanuriven...@gmail.comwrote:
Hello,
Can some one suggest me a way of putting Stored procedure output into a
file, the file should be available on my Computer, I don't want to use
FTP process.
Thanks
vk
Jan 26 '07 #2

P: n/a
VK wrote:
Just any update, am using DB2 UDB V8.0

On Jan 26, 2:11 pm, "VK" <kanuriven...@gmail.comwrote:
>Hello,
Can some one suggest me a way of putting Stored procedure output into a
file, the file should be available on my Computer, I don't want to use
FTP process.
Thanks
vk
You mean like this?
db2 "call proc(...)" file.txt

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jan 26 '07 #3

P: n/a
Ian
VK wrote:
Hello,
Can some one suggest me a way of putting Stored procedure output into a
file, the file should be available on my Computer, I don't want to use
FTP process.
Thanks
vk
Not possible, assuming "my computer" means the remote client, as opposed
to the database server.

Jan 26 '07 #4

P: n/a
VK
Hello

say my SP have "Select * from table1" SQL statment... I wanted the out
put of the SQL into a file, and how do I get the file created in DB
server to my system. I have no FTP access.

On Jan 26, 2:27 pm, Serge Rielau <srie...@ca.ibm.comwrote:
VK wrote:
Just any update, am using DB2 UDB V8.0
On Jan 26, 2:11 pm, "VK" <kanuriven...@gmail.comwrote:
Hello,
Can some one suggest me a way of putting Stored procedure output into a
file, the file should be available on my Computer, I don't want to use
FTP process.
Thanks
vkYou mean like this?
db2 "call proc(...)" file.txt

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jan 26 '07 #5

P: n/a
VK wrote:
Hello

say my SP have "Select * from table1" SQL statment... I wanted the out
put of the SQL into a file, and how do I get the file created in DB
server to my system. I have no FTP access.
INSERT the query result into a DECLARE GLOBAL TEMPORARY TABLE.
When the procedure has finished you can select from the temp table and
store in a file on your client as I have shown or via EXPORT.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jan 26 '07 #6

P: n/a
Serge Rielau wrote:
VK wrote:
>Hello

say my SP have "Select * from table1" SQL statment... I wanted the out
put of the SQL into a file, and how do I get the file created in DB
server to my system. I have no FTP access.
INSERT the query result into a DECLARE GLOBAL TEMPORARY TABLE.
When the procedure has finished you can select from the temp table and
store in a file on your client as I have shown or via EXPORT.

Cheers
Serge
Reading between the lines, I expect what VK is looking for is a function
within SQL/PL which would allow him to write output from within the SP to a
file. This is commonly done in Oracle PL/SQL for debugging purposes.

I remember being this discussed at a Viper briefing I attended. It isn't,
AFAIK, possible yet. There was talk of making it available to aid
porting.

I, for one, am against the idea as it means that certain elements of the SP
are then treated outside the Unit of Work (and hence don't get rolled back
in the event of a failure).

In my investigations into developing DB2Unit I would dearly have loved this
functionality : but then came to realise how dangerous it could be. I
then looked for alternative ways of getting test outputs back, and came up
with a second process running the checks "WITH UR".

As Serge has already said you can return information from a GTT, as long as
you aren't rolling everything back beforehand.

Phil
Jan 29 '07 #7

P: n/a
Philip Nelson wrote:
Serge Rielau wrote:
>VK wrote:
>>Hello

say my SP have "Select * from table1" SQL statment... I wanted the out
put of the SQL into a file, and how do I get the file created in DB
server to my system. I have no FTP access.
INSERT the query result into a DECLARE GLOBAL TEMPORARY TABLE.
When the procedure has finished you can select from the temp table and
store in a file on your client as I have shown or via EXPORT.

Cheers
Serge

Reading between the lines, I expect what VK is looking for is a function
within SQL/PL which would allow him to write output from within the SP to a
file. This is commonly done in Oracle PL/SQL for debugging purposes.

I remember being this discussed at a Viper briefing I attended. It isn't,
AFAIK, possible yet. There was talk of making it available to aid
porting.

I, for one, am against the idea as it means that certain elements of the SP
are then treated outside the Unit of Work (and hence don't get rolled back
in the event of a failure).

In my investigations into developing DB2Unit I would dearly have loved this
functionality : but then came to realise how dangerous it could be. I
then looked for alternative ways of getting test outputs back, and came up
with a second process running the checks "WITH UR".

As Serge has already said you can return information from a GTT, as long as
you aren't rolling everything back beforehand.

Phil
DGTT support "NOT LOGGED ON ROLLBACK PRESERVE ROWS". I had it added to
support tracing in SQL Procedures.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jan 29 '07 #8

P: n/a
Serge Rielau wrote:
Philip Nelson wrote:
>Serge Rielau wrote:
>>VK wrote:
Hello

say my SP have "Select * from table1" SQL statment... I wanted the out
put of the SQL into a file, and how do I get the file created in DB
server to my system. I have no FTP access.
INSERT the query result into a DECLARE GLOBAL TEMPORARY TABLE.
When the procedure has finished you can select from the temp table and
store in a file on your client as I have shown or via EXPORT.

Cheers
Serge

Reading between the lines, I expect what VK is looking for is a function
within SQL/PL which would allow him to write output from within the SP to
a
file. This is commonly done in Oracle PL/SQL for debugging purposes.

I remember being this discussed at a Viper briefing I attended. It
isn't,
AFAIK, possible yet. There was talk of making it available to aid
porting.

I, for one, am against the idea as it means that certain elements of the
SP are then treated outside the Unit of Work (and hence don't get rolled
back in the event of a failure).

In my investigations into developing DB2Unit I would dearly have loved
this
functionality : but then came to realise how dangerous it could be. I
then looked for alternative ways of getting test outputs back, and came
up with a second process running the checks "WITH UR".

As Serge has already said you can return information from a GTT, as long
as you aren't rolling everything back beforehand.

Phil
DGTT support "NOT LOGGED ON ROLLBACK PRESERVE ROWS". I had it added to
support tracing in SQL Procedures.

Cheers
Serge

Thanks for that : missed it in the docs (and we're still on V8.2).

Phil
Jan 29 '07 #9

P: n/a
Philip Nelson wrote:
Serge Rielau wrote:
>Philip Nelson wrote:
>>Serge Rielau wrote:

VK wrote:
Hello
>
say my SP have "Select * from table1" SQL statment... I wanted the out
put of the SQL into a file, and how do I get the file created in DB
server to my system. I have no FTP access.
INSERT the query result into a DECLARE GLOBAL TEMPORARY TABLE.
When the procedure has finished you can select from the temp table and
store in a file on your client as I have shown or via EXPORT.

Cheers
Serge

Reading between the lines, I expect what VK is looking for is a function
within SQL/PL which would allow him to write output from within the SP to
a
file. This is commonly done in Oracle PL/SQL for debugging purposes.

I remember being this discussed at a Viper briefing I attended. It
isn't,
AFAIK, possible yet. There was talk of making it available to aid
porting.

I, for one, am against the idea as it means that certain elements of the
SP are then treated outside the Unit of Work (and hence don't get rolled
back in the event of a failure).

In my investigations into developing DB2Unit I would dearly have loved
this
functionality : but then came to realise how dangerous it could be. I
then looked for alternative ways of getting test outputs back, and came
up with a second process running the checks "WITH UR".

As Serge has already said you can return information from a GTT, as long
as you aren't rolling everything back beforehand.

Phil
DGTT support "NOT LOGGED ON ROLLBACK PRESERVE ROWS". I had it added to
support tracing in SQL Procedures.
Thanks for that : missed it in the docs (and we're still on V8.2).
It's in DB2 V8.2
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jan 30 '07 #10

This discussion thread is closed

Replies have been disabled for this discussion.