Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2002
    Location
    cincinnati
    Posts
    18

    Unanswered: how to write udf in C in db2 udb (7.2) on aix

    Hi,
    Our requirement is this. For example we have got a table T1 having two columns C1 and C2. Whenever C2 is updated we would like put a string (FOR EXAMPLE SAY ERRROR) in db2diag.log file. In our company we use bmc patrol for monitoring. So we can teach bmc patrol to trigger alarm the moment it sees the string.
    Any idea to accomplish the above will be highly appreciated.
    I thought of doing like this. Write a trigger for update on table T1 column C2. The trigger in turns calls a UDF which puts the string ERROR in db2diag.log.
    I have no idea how to write udf's. I have gone through the samples in udb, where the example is given for scalarUDF function. I edited the script and has put the following string in C.
    system ("print 'ERROR' > /home/db2inst1/sqllib/db2diag.log");
    I complied that c script, looks like got compiled. If someone provides me the total code it will be grate.
    Thanks
    Jagadish
    jagadish dara

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: how to write udf in C in db2 udb (7.2) on aix

    My first thought is to AVOID writing to db2diag.log file ... You can make BMC check some other file, say C2UpdateMonitor.log by writing the message into this file ...

    Or if BMC provides some means of calling(like APIs) its alerting system, your C program(or you can even consider writing Java UDFs) can directly calll it ...

    HTH

    Cheers

    Sathyaram


    Originally posted by jdara1
    Hi,
    Our requirement is this. For example we have got a table T1 having two columns C1 and C2. Whenever C2 is updated we would like put a string (FOR EXAMPLE SAY ERRROR) in db2diag.log file. In our company we use bmc patrol for monitoring. So we can teach bmc patrol to trigger alarm the moment it sees the string.
    Any idea to accomplish the above will be highly appreciated.
    I thought of doing like this. Write a trigger for update on table T1 column C2. The trigger in turns calls a UDF which puts the string ERROR in db2diag.log.
    I have no idea how to write udf's. I have gone through the samples in udb, where the example is given for scalarUDF function. I edited the script and has put the following string in C.
    system ("print 'ERROR' > /home/db2inst1/sqllib/db2diag.log");
    I complied that c script, looks like got compiled. If someone provides me the total code it will be grate.
    Thanks
    Jagadish

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

  4. #4
    Join Date
    Dec 2002
    Location
    cincinnati
    Posts
    18

    udf in C

    Hi,
    Thanks for the reply. I didnot use that since I donot know java but i am good at C. After reading the db2 documentaion and samples in C. I have written the C program myself and could able to accomlish the task whatever I wanted.

    Thanks
    Jagadish
    jagadish dara

  5. #5
    Join Date
    Jul 2001
    Posts
    32
    Assumption:
    YOu have written a C function say (SAMPLE) to write to a file when ever column C2 is updated and You have created dynamic link library(DLL) say (SAMPLE.dll) for this C function and copied this to a db2 function path.




    Now you can register this DLL as a UDF as follows



    DROP FUNCTION SAMPLE@

    CREATE FUNCTION SAMPLE(Argument data types)
    RETURNS INTEGER
    EXTERNAL NAME SAMPLE!SAMPLE'
    LANGUAGE C
    PARAMETER STYLE DB2SQL
    DETERMINISTIC
    NO SQL
    NO EXTERNAL ACTION@


    I think, this will help you. Let me know.

    Thanks,
    Thangam
    Last edited by thangam; 12-31-02 at 05:48.

  6. #6
    Join Date
    Dec 2002
    Posts
    10
    Thangam and Jagdish

    We are also trying to do simlilar kind of stuff. Is it possible for you to post your code ?? it would be of great help to us

  7. #7
    Join Date
    Jul 2001
    Posts
    32
    Originally posted by Swaminaathan
    Thangam and Jagdish

    We are also trying to do simlilar kind of stuff. Is it possible for you to post your code ?? it would be of great help to us

    ***** Already I have given you a sample. What code do you want? How to create DLL? or How do we write to a file using C function?
    Last edited by thangam; 01-01-03 at 03:24.

  8. #8
    Join Date
    Dec 2002
    Posts
    10
    Originally posted by thangam
    ***** Already I have given you a sample. What code do you want? How to create DLL? or How do we write to a file using C function?
    Thangam.

    I need both actually. How to write to a file using C funtion andhow to create the DLL.. I see from your earlier post how to register the DLL as a funtion. Thanks

  9. #9
    Join Date
    Dec 2002
    Location
    cincinnati
    Posts
    18

    udf in C

    Hi,
    I am pasting whatever I have done to make it to work.

    Before trying please do the following.
    You must have a C Complier on your unix box. For this write a small c programm and compile then you will know whether you are able to compile sucessfully or not?
    In our case, it is AIX. IBM C complier is XLC.

    I just copied all the files from /home/db2inst1/sqllib/samples/c to /tmp. I tried everything under /tmp. The reason for this is , during compliation if the C program needs some header files etc you will have them since I copied the total stuff to /tmp directory.

    I created a table called t1 having only 2 columns c1 and c2 in sample database. I have written a trigger if the value of the column c2 becomes 1 then only it will place a string called as APPDOWN in the db2diag.log file. Patrol triggers alarms the moment it finds APPDOWN in the db2diag.log file.


    /************************************************** *****************************
    **
    ** Source File Name = fun.c
    **
    **
    ************************************************** *****************************/

    #include <stdio.h>
    #include <string.h>
    #include <stdlib.h>
    #include <sqludf.h>



    /************************************************** ***********************
    * function funUDF
    ************************************************** ************************/
    #ifdef __cplusplus
    extern "C"
    #endif
    void SQL_API_FN funUDF (
    SQLUDF_VARCHAR *outNewSalary)
    {
    system ("print 'APPDOWN' >> /home/db2inst1/sqllib/db2dump/db2diag.log");
    system ("chmod 777 /tmp/jagadish");
    *outNewSalary = 0;
    }

    /*END OF fun.c*/
    #########################################33333
    before issuing the following linking and compile command make sure this.
    In the above example the c program name is fun.c so you must have file like
    thin in the same directory.
    file name is fun.exp
    the file fun.exp should contain the function name like this
    funUDF

    bldudf fun funUDF

    after sucessfull compilation create the udf like this

    drop function funUDF;
    CREATE FUNCTION funUDF( ) RETURNS integer
    EXTERNAL NAME 'fun!funUDF'
    NO SQL PARAMETER STYLE DB2SQL
    LANGUAGE C ;
    #################################
    #################################
    drop trigger trg1;
    create trigger trg1 after update of c2 on t1 referencing new as N for each row
    mode db2sql when ( N.c2 = 1 ) begin atomic values(funUDF()); end;
    ############################

    let me know if you have any questions.

    Thanks
    Jagadish
    jagadish dara

  10. #10
    Join Date
    May 2003
    Posts
    2
    I am having some problem with a slightly complex UDF written in C on AIX (DB2 v7.2). I am calling DB2 APIs DB2GetSnapshotSize & DB2GetSnapshot from the C UDF. Both the functions return SQLCODE -487. Error is SQL0487N Routine "<routine-name>" (specific name "<specific-name>") attempted to execute an SQL statement. Apperantly, the API calls are taken as SQL calls. As NO SQL has to be specified in all External UDFs, these calls are being rejected. Is there a way we can use these DB2 APIs in UDFs?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •