Stored Procedure output into File

[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

[VK]

Just any update, am using DB2 UDB V8.0

On Jan 26, 2:11 pm, "VK" <[e-mail]:

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

[Serge Rielau]

VK wrote:

Just any update, am using DB2 UDB V8.0

On Jan 26, 2:11 pm, "VK" <[e-mail]:

>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

[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.

[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 <[e-mail]:

VK wrote:

Just any update, am using DB2 UDB V8.0

On Jan 26, 2:11 pm, "VK" <[e-mail]:

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

[Serge Rielau]

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

[Philip Nelson]

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

[Serge Rielau]

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

[Philip Nelson]

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

[Serge Rielau]

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