Informix

 View Only
Expand all | Collapse all

User Defined Routine (UDR) example required - OSAD Array

  • 1.  User Defined Routine (UDR) example required - OSAD Array

    Posted Mon February 24, 2020 09:02 AM

    I need your guidance, please. I am looking for an example code for a User Define Routine (UDR)  made in C or C++ in order for me to create one to be used in stored procedures and SQL statements. I've found none in the internet and none in github. I need to implement the Optimal String Alignment Distance (osad) routine that even though is a small routine, it requires to create a 121 by 121 array of integers.

    I am desperate to find an example so I can achieve my work task that seems almost impossible without that routine. Any help or guidance is greatly appreciated. We are using Informix 12.1 at work.



    ------------------------------
    Hugo Zambrano
    zambranoh@ottawapolice.ca
    hugoezam@yahoo.com
    ------------------------------

    #Informix


  • 2.  RE: User Defined Routine (UDR) example required - OSAD Array

    IBM Champion
    Posted Mon February 24, 2020 09:14 AM
    Look for Jacques Le Roy book on EBay - it will tell you everything you need

    If you can't find it ping me and I dig out the ISBN

    Cheers
    Paul

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC



    Original Message


  • 3.  RE: User Defined Routine (UDR) example required - OSAD Array

    Posted Mon February 24, 2020 09:37 AM
    ​Thanks for your answer,

    I found a book in Amazon (ISBN 978-0130137098) entitled "Informix Dynamic Server.2000: Server-Side Programming in C" by Jacques Roy from 1999. It seems to me too old. Do you think it would still be a useful book?.  I have the routine in 4Js Genero, I will convert it to C and send it over.


    ------------------------------
    [Zambrano] [Hugo]
    [Informix DBA]
    [Ottawa Police]
    [Ottawa] [ON]
    [(613)236-1222, 5575]
    ------------------------------

    Original Message


  • 4.  RE: User Defined Routine (UDR) example required - OSAD Array

    IBM Champion
    Posted Mon February 24, 2020 05:26 PM

    That is the one – if it doesn't have the CD let know and I can copy mine.

     

     




    Original Message


  • 5.  RE: User Defined Routine (UDR) example required - OSAD Array

    Posted Fri February 28, 2020 06:10 PM
    Edited by System Fri January 20, 2023 04:41 PM
    ​Hi Everybody,

    I have good news, after many days of trying and after many off-line email messages from Paul Watson directing me to the right path I was able to have my OSAD UDR working. Paul, thank you, thank you very much for your help. I couldn't have done it with your guidance. For every one benefit, the "IBM Informix DataBlade API Programmer's Guide" PDF manual was of great help, and every time Paul give me a clue, I was expanding my knowledge of that clue in the manual.  Oh man, the last problem for which I almost give up was that the CREATE FUNCTION was using VARCHAR when it should have been LVARCHAR, that little detail almost kill me. I am going to upload the osad.c program here for another person that could come in the future. Another thing that helped me was the clue from Andreas Legner to look for the checksum example in the Informix demo.

    CREATE FUNCTION osad(LVARCHAR, LVARCHAR) RETURNS INTEGER
       WITH (HANDLESNULLS) 
       EXTERNAL NAME '/usr1/informix/lib/osad.so(osad)'
       LANGUAGE C;

    Note: I uploaded the c code and the make file but they do not show.

    Update: As I clarified in a later post, my function was crashing the engine until I use STACK=65536

    ------------------------------
    Zambrano, Hugo
    Informix DBA
    Ottawa Police
    Ottawa, ON
    (613)236-1222, 5575
    ------------------------------

    Original Message


  • 6.  RE: User Defined Routine (UDR) example required - OSAD Array

    Posted Sat February 29, 2020 05:50 AM
    Edited by System Fri January 20, 2023 04:37 PM
    This book is not too old at all. bear in mind that  Informix developments done decades ago are still compatible with today's technologies

    Not always the case with other technologies :-)

    And just wondering where my answer to you a few days ago has gone ...

    It said that John Miller III had written some material about C based UDR. Any material produced by John is top level resource!
    In this case,he explains very well how you should proceed to build a UDR in C, with all contextual info you need to understand.

    Look for the 'C03_Miller_Writing_C-UDR' presentation in the IIUG Conferences repository, for year 2012 

    here
    http://iiugworld.iiug.dyndns.org/2012/2012-sessions.zip
    This is the location where you will find all the IIUG conference presentations since 2008

    please note that this url is temporary and anytime will be changed to http://iiugworld.iiug.org/2012/2012-sessions.zip )

    Eric


    ------------------------------
    [eric] [Verceletto] []
    [Founder]
    [kandooerp.org]
    [Pont l'Abbé] [France]
    [+33 626 52 50 68]
    ------------------------------

    Original Message


  • 7.  RE: User Defined Routine (UDR) example required - OSAD Array

    IBM Champion
    Posted Sat February 29, 2020 07:56 AM
    I have noticed that not all posts seem to make it - I regularly see replies but not the original questions 

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC



    Original Message


  • 8.  RE: User Defined Routine (UDR) example required - OSAD Array

    Posted Sat February 29, 2020 11:00 AM
    Hi Eric,
    I found your comment on the Blogs post, not the Discussion!
    I don't know why Hugo's same post was registered in two places.

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------

    Original Message


  • 9.  RE: User Defined Routine (UDR) example required - OSAD Array

    Posted Mon March 02, 2020 09:16 AM
    Thanks Eric form your advice. Even though ​I already manage to create the UDR using the "IBM Informix DataBlade API Programmer's Guide" PDF manual and Paul Watson help, I will check John Miller's documents. At this point my problem is slightly different: My osad UDR is working 90% of the time. Now I am testing it with thousands of records and it is crashing the engine for some reason. One of my guesses is that perhaps one index on the array may be out of limits, the second guess is that perhaps I am not feeing all the resources I need to free. I compiled the UDR with debugging and assigned the UDR to a virtual processor and use gdb as per Paul guidance, but I haven't been able to step trough the C program: when I use "layout next" I can only see a buch of lines that do not have any sense to me. So far I have supposed the mi_ variables only have meaning inside the Informix engine, I am going to try to run the UDR C-program stand a lone with a bunch of test data to try to find out where the problem lies.

    ------------------------------
    Zambrano, Hugo
    Informix DBA
    Ottawa Police
    Ottawa, ON
    (613)236-1222, 5575
    ------------------------------

    Original Message


  • 10.  RE: User Defined Routine (UDR) example required - OSAD Array

    IBM Champion
    Posted Mon March 02, 2020 09:22 AM

    What is the af stack showing ?

     

    Cheers

    Paul

     




    Original Message


  • 11.  RE: User Defined Routine (UDR) example required - OSAD Array

    Posted Mon March 02, 2020 09:22 AM

    John's document also explains fundamentals of UDR and how it works, so it may that he explains something that you did not consider in your initial code.

     

    Let's see what happens

     

    Eric Vercelletto
    Data Management Architect and Owner / Begooden IT Consulting
    Board of Directors, International Informix Users group
    IBM Champion 2013,2014,2015,2016,2017,2018,2019,2020
    ibm-champion-rgb-130px

    Tel:     +33(0) 298 51 3210
    Mob : +33(0)626 52 50 68
    skype: begooden-it
    Google Hangout: eric.vercelletto@begooden-it.com
    Email:     eric.vercelletto@begooden-it.com
    www :     http://www.vercelletto.com
    www  https://kandooerp.org

    image001.jpg@01CDC3E9.1425CBB0

    image002.jpg@01CDC3E9.1425CBB0

    image003.jpg@01CDC3E9.1425CBB0

     

     




    Original Message


  • 12.  RE: User Defined Routine (UDR) example required - OSAD Array

    IBM Champion
    Posted Mon March 02, 2020 11:53 AM

    If it is crashing on exit then you might be encountering a 12.10.FC1 bug.  If you mi_free'd all your memory the session SAPI memory clean could try and release the memory again and would crash on a NULL pointer exception.

     

    The stack trace will show if that is the case

     

    On the GBD front, as long as you have compiled with –ggdb then you can attach to the PID of your CPUVP.  Then within gbd run br osad (break at osad), run your SQL and gbd should trap the code. At that point run list and you should see your C code.

     

    If the UDR crashes (af's) but the engine doesn't then always restart your engine, not always needed but always the safest thing to do, your UDR could have crapped on memory elsewhere J

     

    You shouldn't be using a lot of memory, AFAIR the default duration should be PER_ROUTINE, so in your function it should 'self-clean' on routine exit.  If you are processing a lot of rows then switching the duration to PER_STMT_EXEC then you can mi_alloc the memory once, then copy the data from the lvarchar structure via mi_get_vardata and mi_get_varlen into your mi_alloc'd memory, avoiding the allocation via mi_lvarchar_string. Lots of checking for overruns etc but you get the idea

     

    Cheers

    Pail

     




    Original Message


  • 13.  RE: User Defined Routine (UDR) example required - OSAD Array

    Posted Mon March 02, 2020 02:32 PM
    Edited by System Fri January 20, 2023 04:12 PM
    ​Hi Eric,

    I just finished following the document from Miller. I think I am following the steps. What is bothering me is that the SELECT statement using the osad function is able to find many records but at one point, during the search, a memory problem occurs. It seems to me that interactive debugging does not helps because I need to process hundreds or thousands of records in order to get the problem. I have tried it using different memory durations without success.

      /* if ( mi_switch_mem_duration(PER_STATEMENT)== MI_ERROR ) { return 96 }; */
      (void)mi_switch_mem_duration(PER_STMT_EXEC);

      SELECT DISTINCT p.pin, p.dob, p.lname, p.fname, p.address
     FROM person_name AS p
     JOIN person_name AS p2 ON p2.dob = p.dob AND p2.fname = p.fname AND osad(TRIM(p2.lname),TRIM(p.lname)) <= 5
    UNION
     <another SELECT using osad()>
    UNION
      <another SELECT using osad()>

    Some times I replace my osad() function for another wrapping SPL function (that calls osad) that gives me a percentage: osad_tp(str1,str2) <= 0.6 and I do check for zero before doing a division.

    03/02/20 13:20:29 Assert Failed: Exception Caught. Type: MT_EX_OS, Context: mem

    03/02/20 13:20:29 IBM Informix Dynamic Server Version 12.10.FC7WE

    03/02/20 13:20:29   Who: Session(62, ocrpsdba@hugospc, -1, 0x44f88508)

                                    Thread(92, sqlexec, 44f4c1f0, 1)

                                    File: mtex.c Line: 420

    03/02/20 13:20:29   Action: Please notify IBM Informix Technical Support.

    03/02/20 13:20:29 stack trace for pid 1671 written to /usr1/informix/tmp/af.4444e6c

    03/02/20 13:20:29   See Also: /usr1/informix/tmp/af.4444e6c, shmem.4444e6c.0

    03/02/20 13:20:31 Exception Caught. Type: MT_EX_OS, Context: mem



    ------------------------------
    Zambrano, Hugo
    Informix DBA
    Ottawa Police
    Ottawa, ON
    (613)236-1222, 5575
    ------------------------------

    Original Message


  • 14.  RE: User Defined Routine (UDR) example required - OSAD Array

    IBM Champion
    Posted Mon March 02, 2020 02:39 PM

    What is the stack showing ? 

     




    Original Message


  • 15.  RE: User Defined Routine (UDR) example required - OSAD Array

    Posted Mon March 02, 2020 04:23 PM

    Hi Paul,

     

    I don't know how to answer your question about the stack. I guess you are asking for the debugger's data. I'll try again to see if I can make that debugger to show something I can understand following your instructions. So far I have the feeling that the engine fails when the engine is performing a check point. I have run the query successfully as long as it does not take several minutes.

     

    -- Hugo

     




    Original Message


  • 16.  RE: User Defined Routine (UDR) example required - OSAD Array

    IBM Champion
    Posted Mon March 02, 2020 04:25 PM

    It will be near the top of the af file

     




    Original Message


  • 17.  RE: User Defined Routine (UDR) example required - OSAD Array

    IBM Champion
    Posted Mon February 24, 2020 09:30 AM
    Maybe what's in $INFORMIXDIR/demo/checksum can serve as a template for what you'd like to achieve?

    ------------------------------
    Andreas Legner
    ------------------------------

    Original Message


  • 18.  RE: User Defined Routine (UDR) example required - OSAD Array

    Posted Mon February 24, 2020 10:25 AM
    Thank Andreas for your answer. I did not realized there was already an example quite close to me in the informixdir. I'll check it out and work from there. Thanks a lot. I will leave this Thread open in case I have problems.

    ------------------------------
    [Zambrano] [Hugo]
    [Informix DBA]
    [Ottawa Police]
    [Ottawa] [ON]
    [(613)236-1222, 5575]
    ------------------------------

    Original Message


  • 19.  RE: User Defined Routine (UDR) example required - OSAD Array

    Posted Mon February 24, 2020 11:38 AM
      |   view attached

    Not C++ but, Java UDR examples, I have some.   I attached a presentation of mine.   Spent quite a bit of time writing Java UDR's in Informix in the last decade.   Jean Georges Perrin and I also had a more in depth version of my presentation in IBM Developerworks but after a brief search I could not find it – if I can, I will send that link also.

     

    Good luck with this powerful feature!

     

    Hal Maner

    M Systems International, Inc.

     




    Attachment(s)

    pdf
    NA06L02.pdf   268 KB 1 version
    Original Message


  • 20.  RE: User Defined Routine (UDR) example required - OSAD Array

    Posted Tue March 03, 2020 01:38 PM
    Edited by System Fri January 20, 2023 04:24 PM

    Hi Paul,

     

    I just changed the FUNCTION registration adding STACK=65536 and PARALLELIZABLE (I remember you suggested that in an email), and I am also now running the SQL statement after ISOLATION DIRTY READ as I had the filling the engine is trying to put that huge SELECT as a transaction and perhaps filling up the physical log. So far is good, but it is too soon to sing victory.

     

    WITH (HANDLESNULLS, PARALLELIZABLE, STACK=65536)

     
    ​Update: All indicates that adding a size to the stack using STACK=65536 did the job. I just finished testing the osad UDR with this option for thousands of records and the engine did not complained.  The option PARALLELIZABLE may have helped to speeded up. and since the routine handles null I included that flag too.

    I want to thanks all of you, and specially Paul Watson, for your tips and help.

     

    -- Hugo

     




    Original Message


  • 21.  RE: User Defined Routine (UDR) example required - OSAD Array

    Posted Tue March 03, 2020 01:45 PM

    Select as a transaction sounds weird, unless your default ISO level is ANSI or repeatable read ?

     

    Anyway you look to be on the right path

     

    Crossing fingers ....

     

    Eric Vercelletto
    Data Management Architect and Owner / Begooden IT Consulting
    Board of Directors, International Informix Users group
    IBM Champion 2013,2014,2015,2016,2017,2018,2019,2020
    ibm-champion-rgb-130px

    Tel:     +33(0) 298 51 3210
    Mob : +33(0)626 52 50 68
    skype: begooden-it
    Google Hangout: eric.vercelletto@begooden-it.com
    Email:     eric.vercelletto@begooden-it.com
    www :     http://www.vercelletto.com
    www  https://kandooerp.org

    image001.jpg@01CDC3E9.1425CBB0

    image002.jpg@01CDC3E9.1425CBB0

    image003.jpg@01CDC3E9.1425CBB0

     

     




    Original Message