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!

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

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

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

On Dec 5, 2:57 pm, JudyK <judy.k...@usbank.com> 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!

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

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

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