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