Search code examples
sqltriggersqueueibm-midrange

How to make a data queue in AS400 that is triggered by a SQL trigger?

I would need to make a data queue that is triggered by mine SQL Trigger; The SQL Trigger is already done but I don't know how to implement the data queue

Solution

  • Generally, SQL is intended to act upon databases. Interacting with operating system objects is not easily done. However, DB2 for i will allow you to execute programs, including the API to send a message to a data queue: QSNDDTAQ. It is ugly.

    call qsnddtaq ('DTAQ      ', 'LIBRARY   ', x'0008F', 'From SQL')

    The parameters are:

    1. Data queue name (10 bytes)
    2. Data queue library name (10 bytes)
    3. Message length (packed(5,0))
    4. Message contents

    If the SQL trigger uses variables rather than literals it will not need to do anything special to format the length - the literal shown is what a packed(5,0) looks like internally.

    If I were assigned this task I would create a CL program and call that rather than the API. Then I would be able to use packed(15,5) for the length parameter, which is the 'natural' cross-language number length as well as provide for trapping errors.