CREATE TRIGGER -> writing to file???

[FM]

Hello:

I want to create a trigger wich performs a write-action to a text file.
I'm intending to create it with:
CREATE TRIGGER TR_ALARMA_TSPRE
AFTER INSERT ON T_ALARMA_TS
FOR EACH STATEMENT
BEGIN ATOMIC
VALUES (SELECT a1,a2,a3 FROM T_TABLE >c:\TRIGGERTEST .LOG);
END

It is possible to do it???

Many thanks in advance,
Felix

[Serge Rielau]

FM wrote:

Hello:

I want to create a trigger wich performs a write-action to a text file.
I'm intending to create it with:
CREATE TRIGGER TR_ALARMA_TSPRE
AFTER INSERT ON T_ALARMA_TS
FOR EACH STATEMENT
BEGIN ATOMIC
VALUES (SELECT a1,a2,a3 FROM T_TABLE >c:\TRIGGERTEST .LOG);
END

Simply define a C UDF that does the job.
I think there samples in sqllib/samples/c and IIRC developerWorks DB2
has some source code as well.

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Gregor Kova�]

Hi!

Go to
http://www-128.ibm.com/developerwork...0302izuha.html
There you will find put_line function that we use a lot for writing stuff
into a file from procedures.
The problem with put_line is that we experience random crashes of the
put_line DLL itself. It can work for months without a problem. And also the
problem is that there is no 64-bit version of it. And I didn't find a way
how to compile it into 64-bit version using MinGW :((( Please let me know
if you have any experience is this field.
So we are using the 32-bit version of put_line on 64-bit version DB2 like a
FENCED function. The problem with this is that after a while we have to
restart the instance, since it seem that put_line takes too much resources
and it doesn't want to cooperate anymore. :(((

My take on this is: It is great as start, but if you know how to write your
own C code do it yourself (and then share the code with me :))) ).
I've also thought about writing a little Java stored procedure for this, but
I'm not wuite sure about the performance of those. I'll have to do more
tests.

Best regards,
Kovi

FM wrote:

Hello:

I want to create a trigger wich performs a write-action to a text file.
I'm intending to create it with:
CREATE TRIGGER TR_ALARMA_TSPRE
AFTER INSERT ON T_ALARMA_TS
FOR EACH STATEMENT
BEGIN ATOMIC
VALUES (SELECT a1,a2,a3 FROM T_TABLE >c:\TRIGGERTEST .LOG);
END

It is possible to do it???

Many thanks in advance,
Felix

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| Gregor Kovac | Gr**********@mi kropis.si |
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Knut Stolze]

Gregor Kova� wrote:

Hi!

Go to

http://www-128.ibm.com/developerwork...0302izuha.html

There you will find put_line function that we use a lot for writing stuff
into a file from procedures.
The problem with put_line is that we experience random crashes of the
put_line DLL itself.

I had a look at the code and it might be that you run into a buffer overflow
that subsequently causes the crash. Another problem may be that null
indicators are incorrectly handled. The code just isn't robust enough and
should be reworked quite a bit... Maybe this one is more stable:
http://tinyurl.com/nfxs9

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Gregor Kova�]

Knut Stolze wrote:

Gregor Kova� wrote:

>Hi!

Go to

http://www-128.ibm.com/developerwork...0302izuha.html

>There you will find put_line function that we use a lot for writing stuff
into a file from procedures.
The problem with put_line is that we experience random crashes of the
put_line DLL itself.

I had a look at the code and it might be that you run into a buffer
overflow
that subsequently causes the crash. Another problem may be that null
indicators are incorrectly handled. The code just isn't robust enough and
should be reworked quite a bit... Maybe this one is more stable:
http://tinyurl.com/nfxs9

Thanks, I'll give it a try.
I've also written a Java stored procedure that writes to a file, but is five
times slower than the C version (put_line) :((

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| Gregor Kovac | Gr**********@mi kropis.si |
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Gregor Kova�]

Knut Stolze wrote:

Gregor Kova� wrote:

>Hi!

Go to

http://www-128.ibm.com/developerwork...0302izuha.html

>There you will find put_line function that we use a lot for writing stuff
into a file from procedures.
The problem with put_line is that we experience random crashes of the
put_line DLL itself.

I had a look at the code and it might be that you run into a buffer
overflow
that subsequently causes the crash. Another problem may be that null
indicators are incorrectly handled. The code just isn't robust enough and
should be reworked quite a bit... Maybe this one is more stable:
http://tinyurl.com/nfxs9

I've seen that you are using SCRATCHPAD in your functions. I'm wondering if
I use the functions in my stored procedures will they work correctly if I
run them at the same time ?

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| Gregor Kovac | Gr**********@mi kropis.si |
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Knut Stolze]

Gregor Kova� wrote:

I've seen that you are using SCRATCHPAD in your functions. I'm wondering
if I use the functions in my stored procedures will they work correctly if
I run them at the same time ?

Yes, they will. Each function invocation has its own scratchpad. Even if
you use the same function multiple times in the same SQL statements, the
scratchpads will not be shared between the occurrences. Of course, the
same applies to function invocations in different SQL statements.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[FM]

Knut Stolze wrote:

Gregor Kovac wrote:

Hi!

Go to

http://www-128.ibm.com/developerwork...0302izuha.html

There you will find put_line function that we use a lot for writing stuff
into a file from procedures.
The problem with put_line is that we experience random crashes of the
put_line DLL itself.

I had a look at the code and it might be that you run into a buffer overflow
that subsequently causes the crash. Another problem may be that null
indicators are incorrectly handled. The code just isn't robust enough and
should be reworked quite a bit... Maybe this one is more stable:
http://tinyurl.com/nfxs9

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Hello Mr.Stolze:
Thank you for this samples wich are very helpfully.
One question: Is there a posibility to dump the results in multiple
lines?

Thank you one more time for your help,
Félix Manresa Madrona
Bases de Datos (DB2/UDB)
Ciudad del Grupo Santander

[Knut Stolze]

FM wrote:

>
Knut Stolze wrote:

>Gregor Kovac wrote:

Hi!

Go to

http://www-128.ibm.com/developerwork...0302izuha.html

There you will find put_line function that we use a lot for writing
stuff into a file from procedures.
The problem with put_line is that we experience random crashes of the
put_line DLL itself.

I had a look at the code and it might be that you run into a buffer
overflow
that subsequently causes the crash. Another problem may be that null
indicators are incorrectly handled. The code just isn't robust enough
and
should be reworked quite a bit... Maybe this one is more stable:
http://tinyurl.com/nfxs9

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Hello Mr.Stolze:
Thank you for this samples wich are very helpfully.
One question: Is there a posibility to dump the results in multiple
lines?

Yes, there is. You can do whatever you want in the file-writing function.
So you can insert linebreaks where you like. (The current implementation
adds a line break after each function call.)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany