Informix

  • 1.  CDC through connection managers

    Posted Wed May 19, 2021 07:48 AM

    Hi!

    We have recently tested a product called QDI (or Qlik Replicate) from Qlik for consuming log data from an Informix instance through CDC. When making a direct connection to on specific server this works without issues (apart from some limitations in Informix CDC). However, since our instance is part of an HDR cluster, and we normally connect to it using a group of connection managers, we wanted to utilize the connection managers (or, at least one connection manager during the first test) for redirecting the CDC session to the current primary server.

    While the connection seems to work, the first CDC api command, cdc_opensess(), fails with error code -83721:
    00007348: 2021-05-19T10:41:52:602897 [SOURCE_CAPTURE ]I: Connected (informix_endpoint_esql.c:1349)
    00007348: 2021-05-19T10:41:52:602897 [SOURCE_CAPTURE ]T: open cdc api session: recs per read: 256, bytes per read: 131072, poll interval: 5 (informix_endpoint_capture.c:299)
    00007348: 2021-05-19T10:41:52:603896 [SOURCE_CAPTURE ]I: CDC_OPENSESS for server grp_hist_oltp Timeout 0 Max read recs 256 (informix_endpoint_esql.c:1458)
    00007348: 2021-05-19T10:41:52:604895 [SOURCE_CAPTURE ]T: cdc_opensess retval = -83721 (informix_endpoint_esql.c:1487)Error in 'cdc_opensess': API retval = -83721, CDC error = 'UNKNOWN' (informix_endpoint_esql.c:1491)


    It took a while before I found the meaning of this error, apparently this is 

    CDC_E_ARG1 The first parameter passed to the function is not valid.

    According to the API documentation the first parameter is the server name:

    >>-cdc_opensess--(--"--server_name--"--,--session_ID--,--timeout--,-->
    
    >--max_recs--,--major_version--,--minor_version--)-------------><


    Now is the question, is this a problem/limitation with Informix CDC, or is it with Qlik Replicate? Have anyone utilized CDC in combination with a connection manager?



    ------------------------------
    Øyvind Gjerstad
    Developer/Architect
    PostNord AS
    ------------------------------


  • 2.  RE: CDC through connection managers

    IBM Select
    Posted Mon June 14, 2021 09:01 AM
    Hi Øyvind,

    just stumbling over this again, realizing there's no reply yet.

    That trace log presumably is from that Qlik application?  And the server name it seems to be using (grp_hist_oltp) looks to be either an sqlhosts group name or a CM SLA name, right?

    My guess is the application simply gets this name through a getenv("INFORMIXSERVER") call which would certainly be good enough in simple case, but not here.
    So the application probably had to get the acutal server name to feed into cdc_opensess() through a dbinfo('servername') call.

    On the other hand, looking at cdc_opensess() internally, this servername currently doesn't really serve any practical purpose and, as per a quick test, could even be an empty string (not NULL, though).

    HTH,
    Andreas

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



  • 3.  RE: CDC through connection managers

    IBM Select
    Posted Wed September 15, 2021 11:17 AM
    Eventually checked further on this, and it turns out SLA connections are well supported and the above presumed problem (SLA name passed as server name) isn't actually a problem.
    Only problem I'd see is when such SLA would get you to a secondary server, but that apparently wouldn't even fail in cdc_opensess().

    So any clue what might be leading to this -83721 would be highly welcome.

    Reading again the initial problem description, "we normally connect to it using a group of connection managers", I'm wondering how this is done and whether the connection to the Connection Managers possibly is done through an sqlhosts group connect?

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



  • 4.  RE: CDC through connection managers

    IBM Select
    Posted Wed September 15, 2021 11:22 AM
    ... and whether connecting that QDI then directly to one CM SLA would still see the problem.

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



  • 5.  RE: CDC through connection managers

    Posted Wed September 15, 2021 11:25 AM
    Andreas:

    Yes, if you have more than one connection manager, which is recommended best practice, you would set up an SQLHOSTS group for the SLA listing all of the connection managers so that if one is offline the client will connect to one of the others

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 6.  RE: CDC through connection managers

    IBM Select
    Posted Fri September 24, 2021 10:40 AM
    Edited by Andreas Legner Fri September 24, 2021 10:44 AM
    For those keen on workarounds, this would simply overwrite any server name passed into cdc_opensess() and make it work even when connecting through a group of connection managers via sqlhosts group connect:
    database syscdcv1;

    drop function if exists informix.cdc_opensess;

    create dba function informix.cdc_opensess_v1
    (
    informix.lvarchar, {In: servername}
    informix.integer, {In: sessionid or0}
    informix.integer, {In: timeoutinsecs for readcalls }
    informix.integer, {In: maxnum recs for readcalls }
    informix.integer, {In: interface behavior maj version}
    informix.integer {In: interface behavior minversion}
    )
    returns informix.integer external name '(cdc_apiudr_opensess)' language C;

    create procedure informix.cdc_opensess(servname lvarchar, sid int, tmo int, maxrec int, majvers int, minvers int) returning int;
    define myname lvarchar;
    define retval integer;
    select DBSERVERNAME into myname from sysmaster:sysdual;
    execute function informix.cdc_opensess_v1(trim(myname), sid, tmo, maxrec, majvers, minvers) into retval;
    return retval;
    end procedure;​
    ------------------------------
    Andreas Legner
    ------------------------------