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

SQL PL

P: n/a
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!
Dec 5 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
Dec 6 '07 #2

P: n/a
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
Dec 6 '07 #3

P: n/a
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
Dec 6 '07 #4

P: n/a
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
Dec 6 '07 #5

P: n/a
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
Dec 7 '07 #6

P: n/a
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.


Dec 7 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.