• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6621
  • Last Modified:

Is there a print statement in DB2?

Is there any equivalent of DBMS_OUTPUT in Oracle or PRINT in SQL Server for DB2 or do I need to jump over my head again for this sort of thing?
0
mersis
Asked:
mersis
  • 4
  • 2
  • 2
2 Solutions
 
momi_sabagCommented:
there is no rquivalent to dbms_output
you can not debug print from a sql procedure
0
 
mersisAuthor Commented:
thats concise and pretty clear. Are there any ways round it? All I want is to be able to run a STORED PROC in COMMAND EDITOR and see those outputs as the STORED PROC is executed.
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

What I usually do in that situation is to create a log-table. Wherever I would've used dbms_output, I build a message string and insert it into the log-table.

I can then inspect the log-table to determine the flow and progress of the stored proc.

HTH,
DaveSlash
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
momi_sabagCommented:
you could write a stored procedure that receive a string as a parameter and writes that string to a file, and invoke that stored procedure from your function, but you won't be able to receive any output to the command editor window
0
 
mersisAuthor Commented:
Ok, could you give me some code for either of the proposed solutions. In particular its important that I see all the messages untill the stored proc has crashed, not only few in the beginning that were flushed.
What I mean is that imagine you have 100 statements. After each statement there is DBMS_OUTPUT-like statement. Now when executing my stored proc an error occurs at the statement 50 and everything is rolled back. I would like to see all the 49 messages that were generated in the previous successfully run statements. How can I achieve this?
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

Pretty simple ....

First create the log table:

create table LogTable (
   theMessage varchar(1000)
)

Then in your program:
<do some stuff>
insert into LogTable values('after doing some stuff') with NC;
<do more stuff>
insert into LogTable values('after doing more stuff') with NC;
<do even more stuff>
insert into LogTable values('after doing even more stuff') with NC;

You get the picture. You could even build a customized string with variable values and use that in your insert to LogTable. (That's the way I usually do it.)

Notice that by using "with NC", the rollback won't affect the log-table.

HTH,
DaveSlash
0
 
mersisAuthor Commented:
daveslash, thanks very much, looks like a solution indeed. But I would rather prefer writing to a file if that is also possible?
0
 
mersisAuthor Commented:
daveslash, my DB2 (v9.1 for Windows) does not understand "WITH NC". It only knows of RR, RS, CS, UR. Which one do I need to be using so that rollback does not affect inserts into LogTable table?
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now