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

​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] ------------------------------

That is the one – if it doesn't have the CD let know and I can copy mine. ------Original Message------ ​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] ------------------------------ #Informix

​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 ------------------------------

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] ------------------------------

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------ 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] ------------------------------ #Informix

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 ------------------------------

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 ------------------------------

What is the af stack showing ? Cheers Paul ------Original Message------ 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 ------------------------------ #Informix

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 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 ------Original Message------ 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 ------------------------------ #Informix

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------ 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 ------------------------------ #Informix

​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 ------------------------------

What is the stack showing ? ------Original Message------ ​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)) <= 503/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 ------------------------------ #Informix