SQL PL

[JudyK]

I've been working in Oracle for many years, but am relatively new to
DB2. My question is concering SQL PL. Can you write to a flat file
from SQL PL? For example, if you're writing a stored procedure to
purge records from a table, and you want to log those records to a
flat file, is there a way to do that in SQL PL? We're running UDB 8
for LUW. Thanks!

[Serge Rielau]

JudyK wrote:

I've been working in Oracle for many years, but am relatively new to
DB2. My question is concering SQL PL. Can you write to a flat file
from SQL PL? For example, if you're writing a stored procedure to
purge records from a table, and you want to log those records to a
flat file, is there a way to do that in SQL PL? We're running UDB 8
for LUW. Thanks!

There is no "built-in" procedure (if you exclude EXPORT..).
You'll need to write your own in C or Java.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Knut Stolze]

Serge Rielau wrote:

JudyK wrote:

>I've been working in Oracle for many years, but am relatively new to
DB2. My question is concering SQL PL. Can you write to a flat file
from SQL PL? For example, if you're writing a stored procedure to
purge records from a table, and you want to log those records to a
flat file, is there a way to do that in SQL PL? We're running UDB 8
for LUW. Thanks!

There is no "built-in" procedure (if you exclude EXPORT..).
You'll need to write your own in C or Java.

And there are several examples available to do just that.

p.s: One question that comes up with that functionality immediately is that
all procedures/routines are executed on the database server and not the
client. Thus, if you write to a flat file, you write it on the database
server - which may or may not be acceptable in your environment.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Serge Rielau]

Knut Stolze wrote:

p.s: One question that comes up with that functionality immediately is that
all procedures/routines are executed on the database server and not the
client. Thus, if you write to a flat file, you write it on the database
server - which may or may not be acceptable in your environment.

Judy claims an Oracle background.., so yes, that will be acceptable to
her ;-)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[jefftyzzer]

On Dec 5, 2:57 pm, JudyK <[e-mail]:

I've been working in Oracle for many years, but am relatively new to
DB2. My question is concering SQL PL. Can you write to a flat file
from SQL PL? For example, if you're writing a stored procedure to
purge records from a table, and you want to log those records to a
flat file, is there a way to do that in SQL PL? We're running UDB 8
for LUW. Thanks!

Judy,

While there is no UTL_FILE analog in DB2's SQL PL, don't dishearten--
DB2 has a lot to offer ;-) One very cool thing that DB2 has are
modifying table functions: the ability to SELECT from an INSERT,
UPDATE, or DELETE.

See this link for more: www.vldb.org/conf/2004/IND1P1.PDF.

In your example, you would SELECT the rows affected by your DELETE to
see those records you've deleted. What I might do in your situation is
something like this:

Step 1:

Create a text file (let's call it step1File.txt) on the database
server containing the following:

CONNECT TO <DATABASE>;
SET SCHEMA <SCHEMA>;
SELECT * FROM OLD TABLE (DELETE FROM X WHERE Y=Z);
TERMINATE;

Step 2:

Call the script from step 1, redirecting its output to a file:

db2 -tvf step1File.txt deletedRows.out

I'm not saying the process is as straightforward as it might be in
Oracle, but each RDBMS has its strenghts and weaknesses, no?

--Jeff

[Knut Stolze]

Serge Rielau wrote:

Knut Stolze wrote:

>p.s: One question that comes up with that functionality immediately is
that all procedures/routines are executed on the database server and not
the
client. Thus, if you write to a flat file, you write it on the database
server - which may or may not be acceptable in your environment.

Judy claims an Oracle background.., so yes, that will be acceptable to
her ;-)

That explains it, I guess. Doing something external to the database on the
database server is something that is rarely acceptable to me. Hence, my
careful wording... ;-)

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Dan van Ginhoven]

Create a text file (let's call it step1File.txt) on the database
server containing the following:

CONNECT TO <DATABASE>;
SET SCHEMA <SCHEMA>;
SELECT * FROM OLD TABLE (DELETE FROM X WHERE Y=Z);
TERMINATE;

You could run it on the client as an export statement

db2 =EXPORT TO "c:\tmp\delete.txt" OF DEL MESSAGES "c:\tmp\delete.msg"
select * from old table (delete from repertoar where Tonart = 'G' )

Number of rows exported: 42

MSG file:
SQL3104N The Export utility is beginning to export data to file
"c:\tmp\delete.txt".

SQL3100W Column number "6" (identified as "KOMMENTAR") in the output DEL
format file is longer than 254 bytes. ( My comment: Varchar 400)

SQL3105N The Export utility has finished exporting "42" rows.