Hi all,

if you want to schedule tasks in SAP IQ with events you might face the problem of retrieving data from the event. As IQ events are running as background processes you will not receive any output to your console. Of course it is possible to insert data into a table but this might not be sufficient in any case. Maybe you check disk space and you need to be informed if a certain filling level is crossed.

This blog describes how to write information into the log-file and into a file on operating system level.

The first example shows a way of writing information into the log-file (stdout). This is the file defined with the -o option in the configuration file. If the -o option is omitted the log-file can be found in the IQ-16_0/logfiles directory. Using the “message to console” statement prints a text into the log-file. Here another job should capture the inserted message. So if you have set up a log checking process this should recognize it.

The example itself checks the database filling level and writes a warning into the log-file when the free space falls below 10%. More event types can be found in the manual:

CREATE EVENT Statement – Reference: Statements and Options – SAP Library


Example 1:

create event “DiskSpaceCheck”

type “DBDiskSpace”

where event_condition(‘DBFreePercent’) < 10

handler

begin

  message ‘low disk space warning!! more than 90% full’

  to console;

end;

The second and third example show how to write the output into a file on operating system. In both examples a database consistency check is performed and the result is written to a file. The difference between sample 2 and 3 is that in the first one an additional step with a temporary table is included.

Example 2:

create event do_dbcc

handler

begin

  select * into #abc from sp_iqcheckdb (‘allocation database’);

  execute immediate(‘unload select * from #abc output to ”/tmp/myout” ‘);

  commit;

END


Example 3:

create event do_dbcc

handler

begin

  execute immediate(‘unload select * from sp_iqcheckdb(”allocation database”) output to ”/tmp/myout”  ‘);

  commit;

END

Best regards,

Juergen

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Alexander Bornes

    Hi Juergen,

    Thanks for the blog entry. It was really relevant for me. However, I really would like to append to the system file.

    If I try to add the append option it fails with an SQLCODE -131 when entering

    begin

    execute immediate (‘unload select * from sp_iqcontext() output to ”[filename]” append’);

    end

    Do you have any idea as to why and how to work around this?

    Regards,

    Alexander

    (0) 
    1. Hans-Juergen Schwindke Post author

      Hi,

      you should try to add an “on” to the append clause. This one works for me:

      create event do_dbcc

      handler

      begin

        execute immediate(‘unload select * from sp_iqcheckdb(”allocation database”) output to ”/tmp/myout” append on  ‘);

        commit;

      END

      Best regards,

      Juergen

      (0) 

Leave a Reply