Overview

Skill Level: Advanced

Familiarity with Db2 for z/OS and C programming is highly desirable

This recipe constructs a C program that calls the IFI "READS" verb to extract Db2 log records from a running subsystem. Several system programming techniques are evoked to achieve this.

Ingredients

Db2 for z/OS V11 or higher

z/OS V2.1 or higher

IBM XL C/C++ for z/OS V2.1.1 or higher

Lots of patience - this is heavy stuff!

*** WARNING *** To be able to read log records from an active Db2 subsystem, a program calling IFI must run APF-authorised, it must allocate common storage and invokes IFI in supervisor state.  If you intend to use this code, or an adaptation of it, test it thoroughly in a non-production environment.  Be aware that potential security exposures exist with APF-authorised programs and using the IFI to extract large quantities of log records can impact the performance of Db2.

Step-by-step

  1. Switching between supervisor and problem state

    For this exercise, we need to allocate a “return area” for the IFI in “extended common storage” (ECSA) in key 7 – the storage key used internally by Db2.¬† To allocate this memory, and to call the IFI passing this memory, our program needs to switch to supervisor state.¬† For security and stability reasons, the program should switch back to problem state as soon as the elevated state is no longer required.

    z/OS provides the MODESET macro for this purpose.¬† We create two simple functions to “wrap” this macro to set problem and supervisor state respectively:

    int32_t setprob(void)
    {
    int32_t rc;

    __asm( " SYSSTATE ARCHLVL=2\n"
    " MODESET MODE=PROB,KEY=NZERO\n"
    " ST 15,%0"
    : "=m"(rc)
    :
    : "r0", "r1", "r14", "r15");

    return rc;
    }

    int32_t setsup(void)
    {
    int32_t rc;

    __asm( " SYSSTATE ARCHLVL=2\n"
    " MODESET MODE=SUP,KEY=ZERO\n"
    " ST 15,%0"
    : "=m"(rc)
    :
    : "r0", "r1", "r14", "r15" );

    return rc;
    }

    Note the presence of the “SYSSTATE ARCHLVL=2” macro call.¬† This instructs High-Level Assembler (HLASM), invoked by the C compiler, to use branch-relative jumps so there is no dependence on an active “USING” in the generated code.

    The MODESET macro is described here:
    https://www.ibm.com/support/knowledgecenter/en/SSLTBW_2.3.0/com.ibm.zos.v2r3.ieaa300/modset.htm

    The SYSSTATE macro is described here:
    https://www.ibm.com/support/knowledgecenter/en/SSLTBW_2.1.0/com.ibm.zos.v2r1.ieaa900/state.htm

    Inline assembly capabilites of the XL C/C++ compiler are described here:
    https://www.ibm.com/developerworks/rational/library/rational-using-inline-assembly-for-zos-xl-cc-compiler-trs/index.html

    Note that this recipe uses the ASM option (introduced in XL C/C++ V2.1.1), rather than METAL, to embed Assembler code.

  2. Allocate common storage for the IFI return area

    We need to supply a chunk of key 7 ECSA to IFI for Db2 to populate with log records to pass back to our application.  We need to explicitly free this storage when our application exits, to prevent leakage of common storage, which can cause z/OS stability problems.

    IFI can work with 64-bit common storage, but we will allocate 31-bit common storage in this recipe.

    We use the STORAGE macro to allocate and free this storage.  We implement this as a generic capability, where storage pool, key and size to be allocated are parameters:

    void *get31SpK(uint32_t sp, uint32_t key, uint32_t len)
    {
    int32_t rc1, rc2;
    void *addr;

    /* set supervisor state */
    rc1 = setsup();
    if (rc1 != 0) {
    addr = NULL;
    }
    else {
    /* obtain storage from nominated pool */
    key <<= 4;
    __asm( " SYSSTATE ARCHLVL=2\n"
    " STORAGE OBTAIN,"
    "LENGTH=(%2),"
    "SP=(%3),"
    "KEY=(%4),"
    "LOC=31,"
    "CALLRKY=NO\n"
    " ST 1,%0\n"
    " ST 15,%1"
    : "=m"(addr), "=m"(rc2)
    : "r"(len), "r"(sp), "r"(key)
    : "r0", "r1", "r14", "r15");
    if (rc2 != 0) {
    addr = NULL;
    }

    /* back to problem state */
    setprob();
    }

    return addr;
    }

    void free31SpK(uint32_t sp, uint32_t key, uint32_t len, void *addr)
    {
    int32_t rc1, rc2;

    /* set supervisor mode, key=zero */
    rc1 = setsup();
    if (rc1 == 0) {
    /* release storage */
    key <<= 4;
    __asm( " SYSSTATE ARCHLVL=2\n" /* force relative branch */
    " STORAGE RELEASE,"
    "ADDR=%1,"
    "LENGTH=(%2),"
    "SP=(%3),"
    "KEY=(%4),"
    "COND=YES,"
    "CALLRKY=NO\n"
    " ST 15,%0"
    : "=m"(rc2)
    : "m"(addr), "r"(len), "r"(sp), "r"(key)
    : "r0", "r1", "r14", "r15");

    /* back to problem state */
    setprob();
    }

    return;
    }

    The STORAGE macro is described here:
    https://www.ibm.com/support/knowledgecenter/en/SSLTBW_2.3.0/com.ibm.zos.v2r3.ieaa400/stobre.htm

  3. Test for APF-authorised

    If our program “abends” (i.e. terminates abnormally), the most likely cause is that the program is not running APF-authorised.¬† We can attempt to handle this condition by registering an abend “handler” that tests for abend code x’047′, produces a helpful message and exits without producing a dump.¬† Our abend handler calls the Language Environment (LE) routine, CEE3CIB, to retrieve the Condition Information Block (CIB) which contains the current abend code:

    void abendHandler(int sig)
    {
    unsigned int abcd;

    _CEECIB *cib_ptr;
    _FEEDBACK cibfc;

    /* get abend code */
    CEE3CIB(NULL, &cib_ptr, &cibfc);

    /* verify that CEE3CIB was successful */
    if ( _FBCHECK ( cibfc , CEE000 ) != 0 ) {
    printf("CEE3CIB failed with message number %d\n",
    cibfc.tok_msgno);
    }
    else {
    if (cib_ptr->cib_abf) {
    abcd = (((unsigned int)(cib_ptr->cib_abcd)) >> 12) & 0xfff;
    printf("Abend code: %03X\n", abcd);
    if (cib_ptr->cib_arcv) {
    printf("Abend reason: %d\n", cib_ptr->cib_abrc);
    }
    else {
    printf("Abend reason: NULL\n");
    }
    if (abcd == 0x47) { /* not APF-authorised */
    printf("ERROR: This program must run APF-authorised\n");
    exit(12);
    }
    }
    }
    signal(sig, SIG_DFL);

    return;
    }

    The CEE3CIB routine is described here:
    https://www.ibm.com/support/knowledgecenter/en/SSLTBW_2.3.0/com.ibm.zos.v2r3.ieaa400/stobre.htm

    We will use the signal() C library function to set the above handler for SIGABND signals.

  4. Convert DSECT macros to C headers

    When calling IFI to retrieve log records, a number of structures are passed to/from the interface.  These structures are defined in Assembler macros, but we need C headers.  Use JCL like the following to invoke the DSECT Conversion Utility to convert the required macros to C headers:

    //EDCDSECT JOB NOTIFY=&SYSUID              
    // EXPORT SYMLIST=(MACRO)
    // SET INLIB=DSNB10.SDSNMACS
    // SET MACRO=DSNDWQAL
    // SET OUTLIB=MATTO.H
    //*----------------------------------------
    //IRXARGTB EXEC PROC=EDCDSECT,
    // INFILE=DUMMY,
    // OUTFILE=&OUTLIB(&MACRO),
    // DPARM='EQU(DEF),LOC(En_US.IBM-1047),PP',
    // LIBPRFX=CEE,
    // LNGPRFX=CBC
    //ASSEMBLE.SYSLIB DD DSN=&INLIB,DISP=SHR
    //ASSEMBLE.SYSIN DD *,SYMBOLS=JCLONLY
    &MACRO
    END
    /*

    The following macros are needed for this exercise.  They can be found in the hlq.SDSNMACS library shipped with Db2:

    • DSNDIFCA
    • DSNDQJ00
    • DSNDQW04
    • DSNDQWHS
    • DSNDQWT0
    • DSNDWQAL

    Once you have created these headers, you can leave them in a PDS, or copy them to a z/OS UNIX file system directory, with a lowercase name and an extension of ‘.h’.¬† For example, the following ‘cp’ command from the z/OS UNIX shell will do the trick (repeated for each header file):

    cp "//'MATTO.H(DSNDIFCA)'" /u/matto/include/dsndifca.h

     If you leave the headers in a PDS, make sure you invoke the C compiler specifying the NOSEQ and NOMAR compiler options.

    *** WARNING: EDCDSECT can produce somewhat imperfect results for some fields in a DSECT.¬† For example, a halfword (or C short) is frequently defined in a macro as “XL2”.¬† EDCDSECT converts this to “char[2]”, rather than “short”.¬† Also check the end of the structures that EDCDSECT generates.¬† Frequently, the macro will declare something like “IFCEND DS 0F” (i.e. zero fullwords), which EDCDSECT converts to “int ifcend;” – now a sizeof(struct) returns the wrong length.¬† You will notice that I have corrected for these idiosyncracies in the program code (with suitable explanatory comments), rather than patching the header files.

    The DSECT Conversion Utility is described here:
    https://www.ibm.com/support/knowledgecenter/en/SSLTBW_2.3.0/com.ibm.zos.v2r3.cbcux01/dsect.htm

  5. Initialise IFI parameter blocks

    For convenience, we define a structure, IFIPARMS, to carry around all the parameters required by an IFI call.¬† After allocating this structure, we allocate “user key” and “key 7 common storage” versions of the return area.¬† We allocate key 7 ECSA from storage pool 231.¬† A return area of at least 66KB is recommended.

    Then we initialise each parameter with values that won’t change from call to call.

    The parameters of an IFI call are as follows:

    1. Function: an 8-character, space-padded, nul-terminated field specifying the IFI “verb” to be executed.¬† In our log reading program, this is the synchronous read verb, “READS¬†¬† “.
    2. IFCA: The Instrumentation Facility Communications Area (IFCA), defined by the DSNDIFCA macro, is initialised with its eyecatcher and the correct structure length.
      The IFCA is described in detail here:
      https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/perf/src/tpc/db2z_ifca.html
    3. Return Area: The return area is initialised with the allocated size and eyecatcher.
      The special requirements for the return area when reading complete log records (IFCID 0306) are described here:
      https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/admin/src/tpc/db2z_specifylogrecordreturnarea.html
    4. IFCID Area: The IFCID area is a list of the IFCIDs to be returned.  In our case, we require a single IFCID, 0306, to be returned, so we initialise the list accordingly.
      The IFCID area is described here:
      https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/perf/src/tpc/db2z_ifcaifcid.html
    5. Qualification Area: The qualification area specifies which log records we want returned.¬† In addition to setting the length and eyecatcher, we make some basic choices here, for all subsequent IFI calls: for example, decompress any compressed records.¬† The “mode” of the IFI call and the filter “criteria” will be specified for each call.
      The qualification area field meanings when reading complete log records are described here:
      https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/admin/src/tpc/db2z_logrecordqualify.html
      Since we initialise the qualification area to nulls (x’00’) this has the side effect that log records in a data sharing environment will be merged.¬† See:
      https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/admin/src/tpc/db2z_readlogcomplete.html
    #define     MAXRETURN   66 * 1024

    typedef struct {
    uint32_t length;
    struct qw0306of qw0306of;
    uint8_t data[MAXRETURN];
    } RETURNAREA, *PRETURNAREA;

    typedef struct {
    uint16_t length;
    uint16_t reserved;
    uint16_t if0306;
    } IFCIDAREA, *PIFCIDAREA;

    typedef struct {
    uint8_t function[9];
    struct ifca ifca;
    PRETURNAREA pra, pk7ra;
    IFCIDAREA ifcid;
    struct wqal qual;
    } IFIPARMS, *PIFIPARMS;

    int ifiInit(PIFIPARMS *ppifi)
    {
    PIFIPARMS pifi;

    pifi = (PIFIPARMS)malloc(sizeof(IFIPARMS));
    *ppifi = pifi;

    /* set ABEND handler */
    signal(SIGABND, abendHandler);

    /* allocate user key return area */
    pifi->pra = (PRETURNAREA)malloc(sizeof(RETURNAREA));

    /* allocate SP=231,KEY=7 return area */
    pifi->pk7ra = (PRETURNAREA)get31SpK(231, 7, sizeof(RETURNAREA));
    if (pifi->pk7ra == NULL) {
    printf("ERROR: Couldn't allocate common storage for return area\n");
    return FALSE;
    }

    /* initialise function */
    strcpy(pifi->function, "READS ");

    /* initialise IFCA */
    memset(&pifi->ifca, '\0', sizeof(pifi->ifca));
    /* IFCALEN defined as XL2 rather than H and struct has extraneous int on the end - Ugh! */
    *(uint16_t *)pifi->ifca.ifcalen = sizeof(pifi->ifca) - 4;
    memcpy(pifi->ifca.ifcaid, "IFCA", 4);

    /* initialise return area */
    setsup();
    memset(pifi->pk7ra, '\0', sizeof(RETURNAREA));
    pifi->pk7ra->length = MAXRETURN;
    memcpy(pifi->pk7ra->qw0306of.qw0306cs, "I306", 4);
    setprob();

    /* initialise IFCID structure */
    memset(&pifi->ifcid, '\0', sizeof(pifi->ifcid));
    pifi->ifcid.length = sizeof(IFCIDAREA);
    pifi->ifcid.if0306 = 306;

    /* initialise record qualification structure */
    memset(&pifi->qual, '\0', sizeof(pifi->qual));
    pifi->qual.wqallen = wqalln11;
    memcpy(pifi->qual.wqaleye, "WQAL", 4);
    pifi->qual.wqallopt = wqallop1; /* decompress any compressed records */

    return TRUE;
    }

    void ifiTerm(PIFIPARMS pifi)
    {
    free(pifi->pra);
    free31SpK(231, 7, sizeof(RETURNAREA), pifi->pk7ra);
    free(pifi);
    }

    *** WARNING: When our program terminates, we must take care to free the allocated ECSA storage, in particular, to prevent leakage of common storage, which can affect the stability of z/OS.  The sample code in this recipe makes no attempt to free common storage if the program terminates prematurely due to a signal (e.g. Ctrl+C) or fatal exception.

  6. Call IFI to read complete log records

    We can construct a generic function to call IFI to perform a synchronous read in supervisor state, copy the key 7 return area to our user key return area, then return to problem state:

    int ifiReads(PIFIPARMS pifi)
    {
    int rc;

    /* read log record */
    setsup();
    pifi->pk7ra->qw0306of.qw0306ld = 0; /* reset the returned length */
    rc = 0;
    rc = DSNWLI(pifi->function,
    &pifi->ifca,
    pifi->pk7ra,
    &pifi->ifcid,
    &pifi->qual);
    memcpy(pifi->pra, pifi->pk7ra, sizeof(struct qw0306of) + pifi->pk7ra->qw0306of.qw0306ld);
    setprob();

    return rc;
    }

    We call this routine from high-level routines for each of the modes, selected by setting WQALLMOD in the qualification area.

    WQALLMOD = ‘H’ – Get high RBA

    The function below retrieves the high RBA value, which is returned in the IFCA, not the return area.¬† Accordingly, a return code of x’4′ and reason code of x’E60804′ is ‘normal’ and indicates that the return area contains no data.

    int ifiGetHighRba(PIFIPARMS pifi)
    {
    int i, rc, reason;

    pifi->qual.wqallmod = 'H'; /* retrieve high RBA/LRSN */

    /* read log record */
    rc = ifiReads(pifi);

    /* print out the high RBA/LRSN */
    printf("ifiGetHighRba: DSNWLI READS rc=%d\n", rc);
    rc = pifi->ifca.ifcarc1;
    reason = pifi->ifca.ifcarc2;
    printf("IFCA rc=%08X, reason=%08X\n", rc, reason);
    printf("High RBA/LRSN: ");
    for (i = 0; i < 10; i++) {
    printf("%02X", pifi->ifca.ifcahlrs[i]);
    }
    printf(" \n");

    return (rc == 4 && reason == 0xe60804);
    }

    WQALLMOD = ‘D’ – Read a single log record

    The function below retrieves a single log record, matching the specified RBA (or LRSN for data sharing environments).¬† If the log record exists and can be returned, the IFCA return and reason codes should be zero.¬† Note that we set the filter criterion, WQALLCRI to “all”, so we can retrieve any type of record.

    int ifiGetSingleRba(PIFIPARMS pifi, uint8_t *rba)
    {
    int rc, reason, result;

    result = FALSE;

    pifi->qual.wqallmod = 'D'; /* retrieve single RBA/LRSN */
    memcpy(pifi->qual.wqallrba._wqalrba10, rba, 10);
    pifi->qual.wqallcri = wqallcra; /* get all log records */

    /* read log record */
    rc = ifiReads(pifi);

    /* print out the single record */
    printf("ifiGetSingleRba: DSNWLI READS rc=%d\n", rc);
    rc = pifi->ifca.ifcarc1;
    reason = pifi->ifca.ifcarc2;
    printf("IFCA rc=%08X, reason=%08X\n", rc, reason);
    if (rc < 8) {
    result = parseReturnArea(pifi, MAXRBA);
    }

    return result;
    }

    WQALLMOD = ‘F’ – Read first set of log records

    WQALLMOD = ‘N’ – Read next set of log records

    WQALLMOD = ‘T’ – Terminate reading set of records

    Finally, we implement a set of functions designed to retrieve a set of log records between a starting and ending RBA.¬† Once we encounter the ending RBA, we won’t retrieve any more log records, but if the IFI indicates that more are available, we should instruct the IFI to “release” any resources it is holding to keep track of where we are in the record retrieval process.

    The function below kicks off the process, setting a flag in our IFIPARMS structure if more log records are available.¬† We implement two filter criteria, WQALLCRI=x’00’, which will only return data change records for tables with the DATA CAPTURE CHANGES attribute set and unit of recovery control records, or WQALLCRI=x’FF’, which will return all log records.

    int ifiGetFirstRba(PIFIPARMS pifi, uint8_t *rba, uint8_t *endRba)
    {
    int rc, reason, result;

    result = FALSE;

    pifi->qual.wqallmod = 'F'; /* retrieve first RBA/LRSN */
    memcpy(pifi->qual.wqallrba._wqalrba10, rba, 10);
    pifi->qual.wqallcri = pifi->getAll ? wqallcra : wqallcr0;

    /* read log records */
    rc = ifiReads(pifi);

    /* print out the return structure */
    printf("ifiGetFirstRba: DSNWLI READS rc=%d\n", rc);
    rc = pifi->ifca.ifcarc1;
    reason = pifi->ifca.ifcarc2;
    printf("IFCA rc=%08X, reason=%08X\n", rc, reason);
    if (rc < 8) {
    result = parseReturnArea(pifi, endRba);
    }
    pifi->moreRecords = (rc == 4 && reason == 0xe60812) ? FALSE : TRUE;

    return result;
    }

    The function below retrieves the next group of qualifying log records.

    int ifiGetNextRba(PIFIPARMS pifi, uint8_t *endRba)
    {
    int rc, reason, result;

    result = FALSE;

    pifi->qual.wqallmod = 'N'; /* retrieve single RBA/LRSN */

    /* read log record */
    rc = ifiReads(pifi);

    /* print out the next records */
    printf("ifiGetNextRba: DSNWLI READS rc=%d\n", rc);
    rc = pifi->ifca.ifcarc1;
    reason = pifi->ifca.ifcarc2;
    printf("IFCA rc=%08X, reason=%08X\n", rc, reason);
    if (rc < 8) {
    result = parseReturnArea(pifi, endRba);
    }
    pifi->moreRecords = (rc == 4 && reason == 0xe60812) ? FALSE : TRUE;

    return result;
    }

    Finally, if we reach the target “end RBA” but the IFI is holding a “cursor” in the log records, expecting us to call the “next” function (WQALLMOD=’N’) to get more, we need to give the IFI an opportunity to release any resources it is holding.

    int ifiGetTerminate(PIFIPARMS pifi)
    {
    int rc, reason;

    pifi->qual.wqallmod = 'T'; /* terminate F/N mode */

    /* read log record */
    rc = ifiReads(pifi);

    /* print out return code */
    printf("ifiGetTerminate: DSNWLI READS rc=%d\n", rc);
    rc = pifi->ifca.ifcarc1;
    reason = pifi->ifca.ifcarc2;
    printf("IFCA rc=%08X, reason=%08X\n", rc, reason);
    /* Terminate sends back a return area, but it is idnetical to revious command output */
    /* printf("Terminating record: (len=%u)\n", sizeof(struct qw0306of) + pifi->pra->of0306.qw0306ld);
    spitOut((void *)pifi->pra, sizeof(struct qw0306of) + pifi->pra->of0306.qw0306ld); */

    return (rc == 4 && reason == 0xe60804);
    }

     

  7. (Optional) Filter log records by table

    If we only want to return log records for tables with DATA CAPTURE CHANGES set, we can get the IFI to further filter what records are returned by specifying a list of up to 50,000 database ID/page set ID pairs, corresponding to the tables of interest.  So, how do you find the database id (DBID) and page set ID (PSID) for a particular table?  You look in the system catalog, of course.  The following query returns the DBID and PSID for a given creator and table name.

    SELECT B.DBID, B.PSID
    FROM SYSIBM.SYSTABLES A, SYSIBM.SYSTABLESPACE B
    WHERE A.CREATOR = creator
    AND A.NAME = table_name
    AND B.NAME = A.TSNAME
    AND B.DBNAME = A.DBNAME

    From this, we create a routine (below) that looks up the DBID/PSID for a list of tables of interest, and places them in the structure expected by the IFI.¬† A pointer to this structure is loaded into the qualification area.¬† This filtering mechanism only operates, in our case, when WQALLCRI = x’00’.¬† If we are returning all log records (WQALLCRI = x’FF’), this list of DBID/PSID pairs is ignored by the IFI.

    int lookupTables(PIFIPARMS pifi, PTABLELIST ptl)
    {
    int i, ngood, size;
    struct wqls *pls;
    struct wqlsdbps *pdbps;

    ngood = 0;
    if (ptl->nTables > 0) {
    /* allocate and initialise a WQLS structure */
    /* NOTE: struct wqls has extra char at the end, so subtract 1 from size */
    size = sizeof(struct wqls)-1 + ptl->nTables * sizeof(struct wqlsdbps);
    pls = (struct wqls *)malloc(size);
    memset((void *)pls, NUL, size);
    pls->wqlslen = size;
    memcpy(pls->wqlseye, "WQLS", 4);
    memcpy(pls->wqlstype, "DBPS", 4);
    pls->wqlsitem = ptl->nTables;
    /* set pointer to WQLS in WQAL structure */
    pifi->qual.wqalwql4 = (void *)pls;
    pdbps = (struct wqlsdbps *)(void *)&pls->wqlslist;
    /* get DBID and PSID for tablespace corresponding to each table */
    for (i = 0; i < ptl->nTables; i++) {
    DCLSYSTABLES.CREATOR.CREATOR_len = (int16_t)strlen(ptl->tables[i].creator);
    strncpy(DCLSYSTABLES.CREATOR.CREATOR_data, ptl->tables[i].creator, 128);
    DCLSYSTABLES.NAME.NAME_len = (int16_t)strlen(ptl->tables[i].table);
    strncpy(DCLSYSTABLES.NAME.NAME_data, ptl->tables[i].table, 128);
    EXEC SQL
    SELECT B.DBID, B.PSID
    INTO :DCLSYSTABLESPACE.DBID, :DCLSYSTABLESPACE.PSID
    FROM SYSIBM.SYSTABLES A, SYSIBM.SYSTABLESPACE B
    WHERE A.CREATOR = :DCLSYSTABLES.CREATOR
    AND A.NAME = :DCLSYSTABLES.NAME
    AND B.NAME = A.TSNAME
    AND B.DBNAME = A.DBNAME;
    diagnose("SELECT DBID,PSID", FALSE);
    if (SQLCODE == 0) {
    printf("Tablespace for table %s.%s, DBID=%u (x'%04X'), PSID=%u (x'%04X')\n",
    ptl->tables[i].creator, ptl->tables[i].table,
    DCLSYSTABLESPACE.DBID, DCLSYSTABLESPACE.DBID,
    DCLSYSTABLESPACE.PSID, DCLSYSTABLESPACE.PSID);
    pdbps->wqlsdbid = DCLSYSTABLESPACE.DBID;
    pdbps->wqlspsid = DCLSYSTABLESPACE.PSID;
    ngood++;
    }
    else {
    printf("SELECT of DBID and PSID for table %s.%s failed. SQLCODE=%d\n",
    ptl->tables[i].creator, ptl->tables[i].table, SQLCODE);
    }
    EXEC SQL COMMIT;
    diagnose("COMMIT after SELECT", FALSE);
    pdbps++;
    }
    }

    return (ptl->nTables == ngood);
    }
  8. (Partially) Parse the returned log records

    We need a function to parse the log record(s), if any, returned in the return area.  The return area is mapped by the following macros:

       QW0306OF | QWT02 | QWHS | QW0306 | QW0306L(s)

    The return area only contains the first 3 offset/length/count “triplets” of the QWT02 structure.¬† Each log record is returned in a QW0306L structure.¬† The number of log records returned is available in the QW0306CT field of the QW0306 structure.

    This recipe doesn’t attempt to interpret log records, beyond extracting the record type/subtype and producing a hex dump of the record.¬† The interested reader should refer to the DSNDQJ00 macro in hlq.SDSNMACS for details of “unit of recovery control” and “data change” records.

    Our parsing routine (below) also needs to determine if we have reached the target “end RBA”, so we know whether to attempt to fetch more records.

    int parseReturnArea(PIFIPARMS pifi, uint8_t *endRba)
    {
    int i, j, n, ended;
    char temp[17];
    struct qwt0 *pqwt02;
    struct qwhs *pqwhs;
    struct qw0306 *pqw0306;
    struct qw0306l *pqw0306l;
    struct dsndlrh_v2 *plrh;
    uint32_t lenlr, lenlrh;
    uint8_t *porigin;

    printf("QW0306OF:\n");
    GRAB(temp, pifi->pra->qw0306of.qw0306cs, 4);
    printf(" QW0306CS: '%s'\n", temp);
    GRAB(temp, pifi->pra->qw0306of.qw0306ft, 8);
    printf(" QW0306FT: '%s'\n", temp);
    printf(" QW0306LD: %u bytes\n", pifi->pra->qw0306of.qw0306ld);
    porigin = (uint8_t *)(void *)&pifi->pra->qw0306of.qw0306ld;

    pqwt02 = (struct qwt0 *)(void *)(porigin + sizeof(uint32_t));
    printf("QWT02:\n");
    printf(" QWT02PSO: %5u L: %3u N: %3u\n",
    (uint32_t)pqwt02->qwt02pso,
    (uint32_t)*(uint16_t *)(void *)&pqwt02->qwt02psl,
    (uint32_t)*(uint16_t *)(void *)&pqwt02->qwt02psn);
    printf(" QWT02R1O: %5u L: %3u N: %3u\n",
    (uint32_t)pqwt02->qwt02r1o,
    (uint32_t)*(uint16_t *)(void *)&pqwt02->qwt02r1l,
    (uint32_t)*(uint16_t *)(void *)&pqwt02->qwt02r1n);
    printf(" QWT02R2O: %5u L: %3u N: %3u\n",
    (uint32_t)pqwt02->qwt02r2o,
    (uint32_t)*(uint16_t *)(void *)&pqwt02->qwt02r2l,
    (uint32_t)*(uint16_t *)(void *)&pqwt02->qwt02r2n);

    printf("QWHS:\n");
    pqwhs = (struct qwhs *)(void *)((uint8_t *)(void *)porigin + (int32_t)pqwt02->qwt02pso);
    printf(" QWHSLEN: %u bytes\n", (uint32_t)*(uint16_t *)pqwhs->qwhslen); /* length defined as XL2 */
    printf(" QWHSTYP: %02X\n", pqwhs->qwhstyp);
    printf(" QWHSIID: IFCID %04u\n", (uint32_t)*(uint16_t *)pqwhs->qwhsiid);
    GRAB(temp, pqwhs->qwhsssid, 4);
    printf(" QWHSSSID: '%s'\n", temp);
    GRAB(temp, pqwhs->qwhslocn, 16);
    printf(" QWHSLOCN: '%s'\n", temp);
    GRAB(temp, pqwhs->qwhslwid._qwhsnid, 8);
    printf(" QWHSNID: '%s'\n", temp);
    GRAB(temp, pqwhs->qwhslwid._qwhslunm, 8);
    printf(" QWHSLUNM: '%s'\n", temp);
    GRAB(temp, pqwhs->qwhssid, 4);
    printf(" QWHSSID: '%s'\n", temp);

    printf("QW0306:\n");
    pqw0306 = (struct qw0306 *)(void *)((uint8_t *)(void *)porigin + (int32_t)pqwt02->qwt02r1o);
    printf(" QW0306ES: ");
    for (i = 0; i < 10; i++) {
    printf("%02X", pqw0306->qw0306fb._qw0306es[i]);
    }
    printf(" \n");
    n = *(uint32_t *)pqw0306->qw0306fb._qw0306ct;
    printf(" QW0306CT: %u log records\n", n);

    /* iterate through log records until we run out, or rba is greater than endRba */
    ended = FALSE;
    pqw0306l = (struct qw0306l *)(void *)((uint8_t *)(void *)porigin + (int32_t)pqwt02->qwt02r2o);
    for (j = 0; j < n && !ended; j++) {
    if (memcmp(pqw0306l->qw0306lh.qw0306rm._qw0306rl, endRba, 10) > 0) { /* gone past end RBA */
    ended = TRUE;
    }
    else {
    printf("--- Log record %u ---\n", j+1);
    printf("QW0306L:\n");
    printf(" QW0306RL: ");
    for (i = 0; i < 10; i++) {
    printf("%02X", pqw0306l->qw0306rl[i]);
    }
    printf(" \n");
    plrh = (struct dsndlrh_v2 *)(void *)&pqw0306l->qw0306lr;
    printf("LRH:\n");
    lenlr = *(uint32_t *)(void *)&plrh->lrhll1_v2;
    printf(" LRHLL: %u bytes\n", lenlr);
    printf(" LRHRTYPE: %04X\n", (uint32_t)*(uint16_t *)plrh->lrhrtype_v2);
    printf(" LRHSTYPE: %04X\n", (uint32_t)*(uint16_t *)plrh->lrhstype_v2);
    lenlrh = (uint32_t)plrh->lrhlen_v2;
    printf(" Log record body (%u bytes):\n", lenlr - lenlrh);
    spitOut((uint8_t *)(void *)plrh + lenlrh, lenlr - lenlrh);
    pqw0306l = (struct qw0306l *)(void *)((uint8_t *)((void *)pqw0306l) +
    sizeof(struct qw0306l)-1 +
    *(uint32_t *)(void *)&plrh->lrhll1_v2);
    }
    }
    return (!ended && pifi->ifca.ifcarc1 == 0);
    }
  9. Tying it all together

    We implement a simple main function that calls a routine to parse command-line arguments and calls the appropriate IFI routines (above).¬† We support modes of operation implied by the following “usage” display, which appears when invalid (or no) command-line arguments are supplied:

    Usage: db2log ssid commmand [args ...]
    Commands:
    H get high RBA
    A startRba [{endRba|$}] get records of all types
    F startRba [{endRba|$} [creator.table ...]] get CDC and UR control records
    D rba get single log record

    ‘ssid’ is the Db2 subsystem ID from which log records are to be extracted. ‘$’ corresponds to leaving the “end RBA” unspecified (i.e. keep reading log records until there are no more available).

    To establish and tear down the connection to Db2, we borrow code from this recipe: https://developer.ibm.com/recipes/tutorials/my-first-db2-application-in-c-from-the-zos-unix-shell/

    So, here is the complete program source, which for subsequent compilation, I have named db2log.c:

    #include <ceeedcct.h>
    #include <ctype.h>
    #include <leawi.h>
    #include <signal.h>
    #include <stdio.h>
    #include <stdint.h>
    #include <stdlib.h>
    #include <string.h>

    #include "dsndifca.h"
    #include "dsndqj00.h"
    #include "dsndqw04.h"
    #include "dsndqwhs.h"
    #include "dsndqwt0.h"
    #include "dsndwqal.h"

    typedef int RRSAF_FN(char *, ...);
    #pragma linkage(RRSAF_FN, OS)
    typedef int IFI_FN(char *, ...);
    #pragma linkage(IFI_FN, OS)
    typedef void HLI_FN(uint32_t *);
    #pragma linkage(HLI_FN, OS)

    RRSAF_FN *dsnrli;
    IFI_FN *dsnwli;
    HLI_FN *dsnhli;

    #define DSNRLI (*dsnrli)
    #define DSNWLI (*dsnwli)
    #define DSNHLI (*dsnhli)

    #define RRSAF_IDENTIFY "IDENTIFY "
    #define RRSAF_SIGNON "SIGNON "
    #define RRSAF_CREATE_THREAD "CREATE THREAD "
    #define RRSAF_TERMINATE_THREAD "TERMINATE THREAD "
    #define RRSAF_TERMINATE_IDENTIFY "TERMINATE IDENTIFY"
    #define RRSAF_TRANSLATE "TRANSLATE "

    #define IFI_READS "READS "
    #define MAXRBA "FFFFFFFFFFFFFFFFFFFF"

    #define NUL '\0'
    #define FALSE 0
    #define TRUE -1

    /* macro to load a character field into a null-terminated string */
    #define GRAB(A, B, C) { \
    memcpy((A), (B), (C)); \
    (A)[(C)] = NUL; \
    }

    /* static settings for RRSAF connection to DB2 */
    #define MY_CORRELATION_ID "DB2LOG " /* 12 characters */
    #define MY_ACCOUNTING_TOKEN "MYACCOUNTINGTOKEN " /* 22 characters */
    #define MY_PLAN "MYPLAN " /* 8 characters */

    EXEC SQL INCLUDE SQLCA;
    EXEC SQL BEGIN DECLARE SECTION;
    #include "systables.h"
    #include "systablespace.h"
    EXEC SQL DECLARE :DCLSYSTABLES.NAME VARIABLE CCSID 1047;
    EXEC SQL DECLARE :DCLSYSTABLES.CREATOR VARIABLE CCSID 1047;
    EXEC SQL END DECLARE SECTION;

    typedef struct {
    char creator[129];
    char table[129];
    int16_t dbid;
    int16_t obid;
    } TABLEENTRY, *PTABLEENTRY;

    typedef struct {
    int nTables;
    PTABLEENTRY tables;
    } TABLELIST, *PTABLELIST;

    #define MAXRETURN 66 * 1024

    typedef struct {
    uint32_t length;
    struct qw0306of qw0306of;
    uint8_t data[MAXRETURN];
    } RETURNAREA, *PRETURNAREA;

    typedef struct {
    uint16_t length;
    uint16_t reserved;
    uint16_t if0306;
    } IFCIDAREA, *PIFCIDAREA;

    typedef struct {
    uint8_t function[9];
    struct ifca ifca;
    PRETURNAREA pra, pk7ra;
    IFCIDAREA ifcid;
    struct wqal qual;
    int getAll;
    int moreRecords;
    } IFIPARMS, *PIFIPARMS;

    /* SMF offset/length/count triplet */
    typedef struct {
    uint32_t offset;
    uint16_t length;
    uint16_t count;
    } TRIPLET, *PTRIPLET;

    /* private function prototypes */
    void abendHandler(int sig);
    int connect_DB2(char *ssid);
    int create_thread(void);
    void diagnose(char *text, int showSuccess);
    void disconnect_DB2(void);
    void free31SpK(uint32_t sp, uint32_t key, uint32_t len, void *addr);
    void *get31SpK(uint32_t sp, uint32_t key, uint32_t len);
    int identify(char *ssid);
    int ifiGetFirstRba(PIFIPARMS pifi, uint8_t *rba, uint8_t *endRba);
    int ifiGetHighRba(PIFIPARMS pifi);
    int ifiGetNextRba(PIFIPARMS pifi, uint8_t *endRba);
    int ifiGetSingleRba(PIFIPARMS pifi, uint8_t *rba);
    int ifiGetTerminate(PIFIPARMS pifi);
    int ifiInit(PIFIPARMS *ppifi);
    int ifiReads(PIFIPARMS pifi);
    void ifiTerm(PIFIPARMS pifi);
    int load_RRSAF(void);
    int lookupTables(PIFIPARMS pifi, PTABLELIST ptl);
    int parseArgs(int argc, char **argv, char *ssid, char *cmd, uint8_t *startRba, uint8_t *endRba, PTABLELIST ptl);
    int parseRba(char *in, uint8_t *out);
    int parseReturnArea(PIFIPARMS pifi, uint8_t *endRba);
    int parseTables(PTABLELIST ptl, int argc, char **argv);
    void printSQLCA(void);
    void release_RRSAF(void);
    int32_t setprob(void);
    int32_t setsup(void);
    int signon(void);
    void spitOut(unsigned char *buf, int len);
    int terminate_identify(void);
    int terminate_thread(void);
    void translate(uint32_t frc, uint32_t rc, uint32_t reason);

    int main(int argc, char **argv)
    {
    char cmd;
    uint8_t ssid[5], startRba[10], endRba[10];
    PIFIPARMS pifi;
    TABLELIST tl;

    tl.tables = NULL;
    if (parseArgs(argc, argv, ssid, &cmd, startRba, endRba, &tl)) {
    if (load_RRSAF()) {
    if (connect_DB2(ssid)) {
    if (ifiInit(&pifi)) {
    switch(cmd) {
    case 'H': /* get high RBA */
    ifiGetHighRba(pifi);
    break;
    case 'A': /* get first, then next to end RBA */
    pifi->getAll = TRUE;
    if (ifiGetFirstRba(pifi, startRba, endRba)) {
    while(ifiGetNextRba(pifi, endRba)) {}
    }
    if (pifi->moreRecords) {
    ifiGetTerminate(pifi);
    }
    break;
    case 'F': /* get first, then next to end RBA */
    pifi->getAll = FALSE;
    if (lookupTables(pifi, &tl)) { /* look up DBID/OBID for table list, if any */
    if (ifiGetFirstRba(pifi, startRba, endRba)) {
    while(ifiGetNextRba(pifi, endRba)) {}
    }
    if (pifi->moreRecords) {
    ifiGetTerminate(pifi);
    }
    }
    break;
    case 'D': /* get single RBA */
    ifiGetSingleRba(pifi, startRba);
    break;
    default:
    break;
    }
    ifiTerm(pifi);
    }
    disconnect_DB2();
    }
    else {
    printf("ERROR: Failed to connect to Db2");
    }
    release_RRSAF();
    }
    else {
    printf("ERROR: Failed to load RRSAF interface\n");
    }
    }
    if (tl.tables != NULL) {
    free(tl.tables);
    }

    return 0;
    }

    void abendHandler(int sig)
    {
    unsigned int abcd;

    _CEECIB *cib_ptr;
    _FEEDBACK cibfc;

    /* get abend code */
    CEE3CIB(NULL, &cib_ptr, &cibfc);

    /* verify that CEE3CIB was successful */
    if ( _FBCHECK ( cibfc , CEE000 ) != 0 ) {
    printf("CEE3CIB failed with message number %d\n",
    cibfc.tok_msgno);
    }
    else {
    if (cib_ptr->cib_abf) {
    abcd = (((unsigned int)(cib_ptr->cib_abcd)) >> 12) & 0xfff;
    printf("Abend code: %03X\n", abcd);
    if (cib_ptr->cib_arcv) {
    printf("Abend reason: %d\n", cib_ptr->cib_abrc);
    }
    else {
    printf("Abend reason: NULL\n");
    }
    if (abcd == 0x47) { /* not APF-authorised */
    printf("ERROR: This program must run APF-authorised\n");
    exit(12);
    }
    }
    }
    signal(sig, SIG_DFL);

    return;
    }

    int connect_DB2(char *ssid)
    {
    int i, success = FALSE;
    char vSsid[5];

    strcpy(vSsid, " ");
    for (i = 0; i < (strlen(ssid) < 4 ? strlen(ssid) : 4); i++) {
    vSsid[i] = (char)toupper(ssid[i]);
    }

    if (identify(vSsid)) {
    if (signon()) {
    if (create_thread()) {
    success = TRUE;
    }
    }
    if (!success) {
    terminate_identify();
    }
    }

    return success;
    }

    int create_thread(void)
    {
    uint32_t frc;
    uint32_t rc, reason;
    int success;

    success = TRUE;
    rc = 0;
    reason = 0;
    frc = DSNRLI(RRSAF_CREATE_THREAD,
    MY_PLAN
    " ",
    "INITIAL ",
    &rc,
    &reason);

    if (frc != 0 || rc != 0) {
    success = FALSE;
    printf("CREATE THREAD failed. frc: %u rc: x'%08X' reason: x'%08X'\n",
    frc, rc, reason);
    }

    return success;
    }

    void diagnose(char *text, int showSuccess)
    {
    if (SQLCODE < 0) {
    printf("*** %20s FAILED ***\n", text);
    printSQLCA();
    }
    else if (showSuccess) {
    printf("*** %20s SUCCEEDED *** (sqlcode=%d)\n", text, SQLCODE);
    }
    }

    void disconnect_DB2(void)
    {
    terminate_thread();
    terminate_identify();
    }

    void free31SpK(uint32_t sp, uint32_t key, uint32_t len, void *addr)
    {
    int32_t rc1, rc2;

    /* set supervisor mode, key=zero */
    rc1 = setsup();
    if (rc1 == 0) {
    /* release storage */
    key <<= 4;
    __asm( " SYSSTATE ARCHLVL=2\n" /* force relative branch */
    " STORAGE RELEASE,"
    "ADDR=%1,"
    "LENGTH=(%2),"
    "SP=(%3),"
    "KEY=(%4),"
    "COND=YES,"
    "CALLRKY=NO\n"
    " ST 15,%0"
    : "=m"(rc2)
    : "m"(addr), "r"(len), "r"(sp), "r"(key)
    : "r0", "r1", "r14", "r15");

    /* back to problem state */
    setprob();
    }

    return;
    }

    void *get31SpK(uint32_t sp, uint32_t key, uint32_t len)
    {
    int32_t rc1, rc2;
    void *addr;

    /* set supervisor state */
    rc1 = setsup();
    if (rc1 != 0) {
    addr = NULL;
    }
    else {
    /* obtain storage from nominated pool */
    key <<= 4;
    __asm( " SYSSTATE ARCHLVL=2\n"
    " STORAGE OBTAIN,"
    "LENGTH=(%2),"
    "SP=(%3),"
    "KEY=(%4),"
    "LOC=31,"
    "CALLRKY=NO\n"
    " ST 1,%0\n"
    " ST 15,%1"
    : "=m"(addr), "=m"(rc2)
    : "r"(len), "r"(sp), "r"(key)
    : "r0", "r1", "r14", "r15");
    if (rc2 != 0) {
    addr = NULL;
    }

    /* back to problem state */
    setprob();
    }

    return addr;
    }

    int identify(char *ssid)
    {
    uint32_t frc;
    uint32_t rc, reason;
    /* PDSNRIB prib; */
    /* PDSNEIB peib; */
    void *prib;
    void *peib;
    int success;

    success = TRUE;
    rc = 0;
    reason = 0;
    frc = DSNRLI(RRSAF_IDENTIFY,
    ssid,
    &prib,
    &peib,
    0,
    0,
    &rc,
    &reason);

    if (frc != 0 || rc != 0) {
    success = FALSE;
    printf("IDENTIFY failed. frc: %u rc: x'%08X' reason: x'%08X'\n",
    frc, rc, reason);
    }

    return success;
    }

    int ifiGetFirstRba(PIFIPARMS pifi, uint8_t *rba, uint8_t *endRba)
    {
    int rc, reason, result;

    result = FALSE;

    pifi->qual.wqallmod = 'F'; /* retrieve first RBA/LRSN */
    memcpy(pifi->qual.wqallrba._wqalrba10, rba, 10);
    pifi->qual.wqallcri = pifi->getAll ? wqallcra : wqallcr0;

    /* read log record */
    rc = ifiReads(pifi);

    /* print out the return structure */
    printf("ifiGetFirstRba: DSNWLI READS rc=%d\n", rc);
    rc = pifi->ifca.ifcarc1;
    reason = pifi->ifca.ifcarc2;
    printf("IFCA rc=%08X, reason=%08X\n", rc, reason);
    if (rc < 8) {
    result = parseReturnArea(pifi, endRba);
    }
    pifi->moreRecords = (rc == 4 && reason == 0xe60812) ? FALSE : TRUE;

    return result;
    }

    int ifiGetHighRba(PIFIPARMS pifi)
    {
    int i, rc, reason;

    pifi->qual.wqallmod = 'H'; /* retrieve high RBA/LRSN */

    /* read log record */
    rc = ifiReads(pifi);

    /* print out the high RBA/LRSN */
    printf("ifiGetHighRba: DSNWLI READS rc=%d\n", rc);
    rc = pifi->ifca.ifcarc1;
    reason = pifi->ifca.ifcarc2;
    printf("IFCA rc=%08X, reason=%08X\n", rc, reason);
    printf("High RBA/LRSN: ");
    for (i = 0; i < 10; i++) {
    printf("%02X", pifi->ifca.ifcahlrs[i]);
    }
    printf(" \n");

    return (rc == 4 && reason == 0xe60804);
    }

    int ifiGetNextRba(PIFIPARMS pifi, uint8_t *endRba)
    {
    int rc, reason, result;

    result = FALSE;

    pifi->qual.wqallmod = 'N'; /* retrieve single RBA/LRSN */

    /* read log record */
    rc = ifiReads(pifi);

    /* print out the next records */
    printf("ifiGetNextRba: DSNWLI READS rc=%d\n", rc);
    rc = pifi->ifca.ifcarc1;
    reason = pifi->ifca.ifcarc2;
    printf("IFCA rc=%08X, reason=%08X\n", rc, reason);
    if (rc < 8) {
    result = parseReturnArea(pifi, endRba);
    }
    pifi->moreRecords = (rc == 4 && reason == 0xe60812) ? FALSE : TRUE;

    return result;
    }

    int ifiGetSingleRba(PIFIPARMS pifi, uint8_t *rba)
    {
    int rc, reason, result;

    result = FALSE;

    pifi->qual.wqallmod = 'D'; /* retrieve single RBA/LRSN */
    memcpy(pifi->qual.wqallrba._wqalrba10, rba, 10);
    pifi->qual.wqallcri = wqallcra; /* get all log records */

    /* read log record */
    rc = ifiReads(pifi);

    /* print out the single record */
    printf("ifiGetSingleRba: DSNWLI READS rc=%d\n", rc);
    rc = pifi->ifca.ifcarc1;
    reason = pifi->ifca.ifcarc2;
    printf("IFCA rc=%08X, reason=%08X\n", rc, reason);
    if (rc < 8) {
    result = parseReturnArea(pifi, MAXRBA);
    }

    return result;
    }

    int ifiGetTerminate(PIFIPARMS pifi)
    {
    int rc, reason;

    pifi->qual.wqallmod = 'T'; /* terminate F/N mode */

    /* read log record */
    rc = ifiReads(pifi);

    /* print out return code */
    printf("ifiGetTerminate: DSNWLI READS rc=%d\n", rc);
    rc = pifi->ifca.ifcarc1;
    reason = pifi->ifca.ifcarc2;
    printf("IFCA rc=%08X, reason=%08X\n", rc, reason);
    /* Terminate sends back a return area, but it is idnetical to revious command output */
    /* printf("Terminating record: (len=%u)\n", sizeof(struct qw0306of) + pifi->pra->of0306.qw0306ld);
    spitOut((void *)pifi->pra, sizeof(struct qw0306of) + pifi->pra->of0306.qw0306ld); */

    return (rc == 4 && reason == 0xe60804);
    }

    int ifiInit(PIFIPARMS *ppifi)
    {
    PIFIPARMS pifi;

    pifi = (PIFIPARMS)malloc(sizeof(IFIPARMS));
    *ppifi = pifi;

    /* set ABEND handler */
    signal(SIGABND, abendHandler);
    /* catch other signals so common storage can be freed */
    signal(SIGINT, abendHandler);

    /* allocate user key return area */
    pifi->pra = (PRETURNAREA)malloc(sizeof(RETURNAREA));

    /* allocate SP=231,KEY=7 return area */
    pifi->pk7ra = (PRETURNAREA)get31SpK(231, 7, sizeof(RETURNAREA));
    if (pifi->pk7ra == NULL) {
    printf("ERROR: Couldn't allocate common storage for return area\n");
    return FALSE;
    }

    /* initialise function */
    strcpy(pifi->function, "READS ");

    /* initialise IFCA */
    memset(&pifi->ifca, '\0', sizeof(pifi->ifca));
    /* IFCALEN defined as XL2 rather than H and struct has extraneous int on the end - Ugh! */
    *(uint16_t *)pifi->ifca.ifcalen = sizeof(pifi->ifca) - 4;
    memcpy(pifi->ifca.ifcaid, "IFCA", 4);

    /* initialise return area */
    setsup();
    memset(pifi->pk7ra, '\0', sizeof(RETURNAREA));
    pifi->pk7ra->length = MAXRETURN;
    memcpy(pifi->pk7ra->qw0306of.qw0306cs, "I306", 4);
    setprob();

    /* initialise IFCID structure */
    memset(&pifi->ifcid, '\0', sizeof(pifi->ifcid));
    pifi->ifcid.length = sizeof(IFCIDAREA);
    pifi->ifcid.if0306 = 306;

    /* initialise record qualification structure */
    memset(&pifi->qual, '\0', sizeof(pifi->qual));
    pifi->qual.wqallen = wqalln11;
    memcpy(pifi->qual.wqaleye, "WQAL", 4);
    pifi->qual.wqallopt = wqallop1; /* decompress any compressed records */
    pifi->qual.wqallcri = wqallcr0; /* only CDC and UR control records */

    return TRUE;
    }

    int ifiReads(PIFIPARMS pifi)
    {
    int rc;

    /* read log record */
    setsup();
    pifi->pk7ra->qw0306of.qw0306ld = 0; /* reset the returned length */
    rc = 0;
    rc = DSNWLI(pifi->function,
    &pifi->ifca,
    pifi->pk7ra,
    &pifi->ifcid,
    &pifi->qual);
    memcpy(pifi->pra, pifi->pk7ra, sizeof(struct qw0306of) + pifi->pk7ra->qw0306of.qw0306ld);
    setprob();

    return rc;
    }

    void ifiTerm(PIFIPARMS pifi)
    {
    free(pifi->pra);
    free31SpK(231, 7, sizeof(RETURNAREA), pifi->pk7ra);
    if (pifi->qual.wqalwql4 != NULL) {
    free(pifi->qual.wqalwql4);
    }
    free(pifi);
    }

    int load_RRSAF(void)
    {
    int result;
    void (*fn1)(void);
    void (*fn2)(void);
    void (*fn3)(void);

    result = FALSE;
    fn1 = fetch("DSNRLI");
    if (fn1 == NULL) {
    perror("ERROR: Unable to load RRSAF module DSNRLI");
    }
    else {
    memcpy(&dsnrli, &fn1, sizeof(fn1));
    fn2 = fetch("DSNWLIR");
    if (fn2 == NULL) {
    perror("ERROR: Unable to load module DSNWLIR");
    release(fn1);
    }
    else {
    memcpy(&dsnwli, &fn2, sizeof(fn2));
    fn3 = fetch("DSNHLIR");
    if (fn3 == NULL) {
    perror("ERROR: Unable to load module DSNHLIR");
    release(fn1);
    release(fn2);
    }
    else {
    memcpy(&dsnhli, &fn3, sizeof(fn3));
    result = TRUE;
    }
    }
    }

    return result;
    }

    int lookupTables(PIFIPARMS pifi, PTABLELIST ptl)
    {
    int i, ngood, size;
    struct wqls *pls;
    struct wqlsdbps *pdbps;

    ngood = 0;
    if (ptl->nTables > 0) {
    /* allocate and initialise a WQLS structure */
    /* NOTE: struct wqls has extra char at the end, so subtract 1 from size */
    size = sizeof(struct wqls)-1 + ptl->nTables * sizeof(struct wqlsdbps);
    pls = (struct wqls *)malloc(size);
    memset((void *)pls, NUL, size);
    pls->wqlslen = size;
    memcpy(pls->wqlseye, "WQLS", 4);
    memcpy(pls->wqlstype, "DBPS", 4);
    pls->wqlsitem = ptl->nTables;
    /* set pointer to WQLS in WQAL structure */
    pifi->qual.wqalwql4 = (void *)pls;
    pdbps = (struct wqlsdbps *)(void *)&pls->wqlslist;
    /* get DBID and PSID for tablespace corresponding to each table */
    for (i = 0; i < ptl->nTables; i++) {
    DCLSYSTABLES.CREATOR.CREATOR_len = (int16_t)strlen(ptl->tables[i].creator);
    strncpy(DCLSYSTABLES.CREATOR.CREATOR_data, ptl->tables[i].creator, 128);
    DCLSYSTABLES.NAME.NAME_len = (int16_t)strlen(ptl->tables[i].table);
    strncpy(DCLSYSTABLES.NAME.NAME_data, ptl->tables[i].table, 128);
    EXEC SQL
    SELECT B.DBID, B.PSID
    INTO :DCLSYSTABLESPACE.DBID, :DCLSYSTABLESPACE.PSID
    FROM SYSIBM.SYSTABLES A, SYSIBM.SYSTABLESPACE B
    WHERE A.CREATOR = :DCLSYSTABLES.CREATOR
    AND A.NAME = :DCLSYSTABLES.NAME
    AND B.NAME = A.TSNAME
    AND B.DBNAME = A.DBNAME;
    diagnose("SELECT DBID,PSID", FALSE);
    if (SQLCODE == 0) {
    printf("Tablespace for table %s.%s, DBID=%u (x'%04X'), PSID=%u (x'%04X')\n",
    ptl->tables[i].creator, ptl->tables[i].table,
    DCLSYSTABLESPACE.DBID, DCLSYSTABLESPACE.DBID,
    DCLSYSTABLESPACE.PSID, DCLSYSTABLESPACE.PSID);
    pdbps->wqlsdbid = DCLSYSTABLESPACE.DBID;
    pdbps->wqlspsid = DCLSYSTABLESPACE.PSID;
    ngood++;
    }
    else {
    printf("SELECT of DBID and PSID for table %s.%s failed. SQLCODE=%d\n",
    ptl->tables[i].creator, ptl->tables[i].table, SQLCODE);
    }
    EXEC SQL COMMIT;
    diagnose("COMMIT after SELECT", FALSE);
    pdbps++;
    }
    }

    return (ptl->nTables == ngood);
    }

    int parseArgs(int argc, char **argv,
    char *ssid,
    char *pcmd,
    uint8_t *startRba, uint8_t *endRba,
    PTABLELIST ptl)
    {
    int good = FALSE;

    if (argc >= 3) {
    if (strlen(argv[1]) < 5) {
    strcpy(ssid, argv[1]);
    if (strlen(argv[2]) == 1) {
    *pcmd = (char)toupper(argv[2][0]);
    switch (*pcmd) {
    case 'H': /* get high RBA */
    if (argc == 3) {
    good = TRUE;
    }
    break;
    case 'A': /* get all log records */
    case 'F': /* get DATA CAPTURE CHANGES log records between start and end */
    if (argc > 3) {
    if (parseRba(argv[3], startRba)) {
    if (argc > 4 && strcmp(argv[4], "$") != 0) {
    if (parseRba(argv[4], endRba)) {
    good = TRUE;
    }
    }
    else {
    parseRba(MAXRBA, endRba);
    good = TRUE;
    }
    if (good && argc > 5) {
    ptl->nTables = argc - 5;
    ptl->tables = (PTABLEENTRY)malloc(ptl->nTables * sizeof(TABLEENTRY));
    good = parseTables(ptl, argc-5, argv+5);
    }
    else {
    ptl->nTables = 0;
    }
    }
    }
    break;
    case 'D': /* get single RBA */
    if (argc == 4 && parseRba(argv[3], startRba)) {
    good = TRUE;
    }
    break;
    default:
    break;
    }
    }
    }
    }
    if (!good) {
    printf(" \n");
    printf("Usage: db2log ssid commmand [args ...]\n");
    printf(" \n");
    printf(" Commands:\n");
    printf(" H get high RBA\n");
    printf(" A startRba [{endRba|$}] get records of all types\n");
    printf(" F startRba [{endRba|$} [creator.table ...]] get CDC and UR control records\n");
    printf(" D rba get single log record\n");
    printf(" \n");
    }

    return good;
    }

    int parseRba(char *in, uint8_t *out)
    {
    int i, j, good;
    char txt[25], test[25], word[9];
    uint32_t fullword;
    uint16_t halfword;

    good = FALSE;

    /* move hex string into 24-byte buffer, padded on the left with zeroes */
    if (strlen(in) <= 20) {
    j = strlen(in) - 1;
    for (i = 23; i >= 0; i--) {
    if (j >= 0) {
    txt[i] = (char)toupper(in[j--]);
    }
    else {
    txt[i] = '0';
    }
    }
    txt[24] = NUL;
    /* parse each 8-byte "word" */
    for (i = 0; i < 3; i++) {
    if (i == 0) { /* parse a halfword */
    strncpy(word, txt + 4, 4);
    word[4] = NUL;
    sscanf(word, "%04X", &fullword);
    halfword = (uint16_t)fullword;
    memcpy(out, &halfword, 2);
    sprintf(test, "%08X", fullword);
    }
    else { /* parse a fullword */
    strncpy(word, txt + 8*i, 8);
    word[8] = NUL;
    sscanf(word, "%08X", &fullword);
    memcpy(out + 4*i - 2, &fullword, 4);
    sprintf(test + strlen(test), "%08X", fullword);
    }
    }
    if (strcmp(txt, test) == 0) {
    good = TRUE;
    }
    }

    if (!good) {
    printf("ERROR: '%s' is not a valid 20-digit hexadecimal number\n", in);
    }
    return good;
    }

    int parseReturnArea(PIFIPARMS pifi, uint8_t *endRba)
    {
    int i, j, n, ended;
    char temp[17];
    struct qwt0 *pqwt02;
    struct qwhs *pqwhs;
    struct qw0306 *pqw0306;
    struct qw0306l *pqw0306l;
    struct dsndlrh_v2 *plrh;
    uint32_t lenlr, lenlrh;
    uint8_t *porigin;

    printf("QW0306OF:\n");
    GRAB(temp, pifi->pra->qw0306of.qw0306cs, 4);
    printf(" QW0306CS: '%s'\n", temp);
    GRAB(temp, pifi->pra->qw0306of.qw0306ft, 8);
    printf(" QW0306FT: '%s'\n", temp);
    printf(" QW0306LD: %u bytes\n", pifi->pra->qw0306of.qw0306ld);
    porigin = (uint8_t *)(void *)&pifi->pra->qw0306of.qw0306ld;

    pqwt02 = (struct qwt0 *)(void *)(porigin + sizeof(uint32_t));
    printf("QWT02:\n");
    printf(" QWT02PSO: %5u L: %3u N: %3u\n",
    (uint32_t)pqwt02->qwt02pso,
    (uint32_t)*(uint16_t *)(void *)&pqwt02->qwt02psl,
    (uint32_t)*(uint16_t *)(void *)&pqwt02->qwt02psn);
    printf(" QWT02R1O: %5u L: %3u N: %3u\n",
    (uint32_t)pqwt02->qwt02r1o,
    (uint32_t)*(uint16_t *)(void *)&pqwt02->qwt02r1l,
    (uint32_t)*(uint16_t *)(void *)&pqwt02->qwt02r1n);
    printf(" QWT02R2O: %5u L: %3u N: %3u\n",
    (uint32_t)pqwt02->qwt02r2o,
    (uint32_t)*(uint16_t *)(void *)&pqwt02->qwt02r2l,
    (uint32_t)*(uint16_t *)(void *)&pqwt02->qwt02r2n);

    printf("QWHS:\n");
    pqwhs = (struct qwhs *)(void *)((uint8_t *)(void *)porigin + (int32_t)pqwt02->qwt02pso);
    printf(" QWHSLEN: %u bytes\n", (uint32_t)*(uint16_t *)pqwhs->qwhslen); /* length defined as XL2 */
    printf(" QWHSTYP: %02X\n", pqwhs->qwhstyp);
    printf(" QWHSIID: IFCID %04u\n", (uint32_t)*(uint16_t *)pqwhs->qwhsiid);
    GRAB(temp, pqwhs->qwhsssid, 4);
    printf(" QWHSSSID: '%s'\n", temp);
    GRAB(temp, pqwhs->qwhslocn, 16);
    printf(" QWHSLOCN: '%s'\n", temp);
    GRAB(temp, pqwhs->qwhslwid._qwhsnid, 8);
    printf(" QWHSNID: '%s'\n", temp);
    GRAB(temp, pqwhs->qwhslwid._qwhslunm, 8);
    printf(" QWHSLUNM: '%s'\n", temp);
    GRAB(temp, pqwhs->qwhssid, 4);
    printf(" QWHSSID: '%s'\n", temp);

    printf("QW0306:\n");
    pqw0306 = (struct qw0306 *)(void *)((uint8_t *)(void *)porigin + (int32_t)pqwt02->qwt02r1o);
    printf(" QW0306ES: ");
    for (i = 0; i < 10; i++) {
    printf("%02X", pqw0306->qw0306fb._qw0306es[i]);
    }
    printf(" \n");
    n = *(uint32_t *)pqw0306->qw0306fb._qw0306ct;
    printf(" QW0306CT: %u log records\n", n);

    /* iterate through log records until we run out, or rba is greater than endRba */
    ended = FALSE;
    pqw0306l = (struct qw0306l *)(void *)((uint8_t *)(void *)porigin + (int32_t)pqwt02->qwt02r2o);
    for (j = 0; j < n && !ended; j++) {
    if (memcmp(pqw0306l->qw0306lh.qw0306rm._qw0306rl, endRba, 10) > 0) { /* gone past end RBA */
    ended = TRUE;
    }
    else {
    printf("--- Log record %u ---\n", j+1);
    printf("QW0306L:\n");
    printf(" QW0306RL: ");
    for (i = 0; i < 10; i++) {
    printf("%02X", pqw0306l->qw0306rl[i]);
    }
    printf(" \n");
    plrh = (struct dsndlrh_v2 *)(void *)&pqw0306l->qw0306lr;
    printf("LRH:\n");
    lenlr = *(uint32_t *)(void *)&plrh->lrhll1_v2;
    printf(" LRHLL: %u bytes\n", lenlr);
    printf(" LRHRTYPE: %04X\n", (uint32_t)*(uint16_t *)plrh->lrhrtype_v2);
    printf(" LRHSTYPE: %04X\n", (uint32_t)*(uint16_t *)plrh->lrhstype_v2);
    lenlrh = (uint32_t)plrh->lrhlen_v2;
    printf(" Log record body (%u bytes):\n", lenlr - lenlrh);
    spitOut((uint8_t *)(void *)plrh + lenlrh, lenlr - lenlrh);
    pqw0306l = (struct qw0306l *)(void *)((uint8_t *)((void *)pqw0306l) +
    sizeof(struct qw0306l)-1 +
    *(uint32_t *)(void *)&plrh->lrhll1_v2);
    }
    }
    return (!ended && pifi->ifca.ifcarc1 == 0);
    }

    int parseTables(PTABLELIST ptl, int argc, char **argv)
    {
    int i, ngood, bad;
    char temp[258], *pdot, *ptab;

    ngood = 0;
    for (i = 0; i < argc; i++) {
    bad = TRUE;
    if (strlen(argv[i]) <= 257) {
    strcpy(temp, argv[i]);
    pdot = strchr(temp, '.');
    if (pdot != NULL) {
    *pdot = NUL;
    ptab = pdot + 1;
    if (strlen(temp) > 0 && strlen(temp) <= 128 &&
    strlen(ptab) > 0 && strlen(ptab) <= 128) {
    strcpy(ptl->tables[i].creator, temp);
    strcpy(ptl->tables[i].table, ptab);
    bad = FALSE;
    ngood++;
    }
    }
    }
    if (bad) {
    printf("ERROR: Bad creator.table, '%s'\n", argv[i]);
    }
    }

    return (ngood == argc);
    }

    void printSQLCA(void)
    {
    char sqlstate[6];
    char sqlerrm[70];
    int i;

    GRAB(sqlstate, sqlca.sqlstate, 5);
    GRAB(sqlerrm, sqlca.sqlerrmc, sqlca.sqlerrml);
    /* replace any x'ff' delimiters with '|' */
    for (i = 0; i < strlen(sqlerrm); i++) {
    if (sqlerrm[i] == 0xFF) sqlerrm[i] = '|';
    }
    printf("SQLCODE = %d\n", sqlca.sqlcode);
    printf("SQLSTATE = %s\n", sqlstate);
    printf("SQLERRM = %s\n", sqlerrm);
    }

    void release_RRSAF(void)
    {
    void (*fn1)(void);
    void (*fn2)(void);
    void (*fn3)(void);

    memcpy(&fn1, &dsnrli, sizeof(fn1));
    memcpy(&fn2, &dsnwli, sizeof(fn2));
    memcpy(&fn3, &dsnhli, sizeof(fn3));
    release(fn1);
    release(fn2);
    release(fn3);
    }

    int32_t setprob(void)
    {
    int32_t rc;

    __asm( " SYSSTATE ARCHLVL=2\n"
    " MODESET MODE=PROB,KEY=NZERO\n"
    " ST 15,%0"
    : "=m"(rc)
    :
    : "r0", "r1", "r14", "r15");

    return rc;
    }

    int32_t setsup(void)
    {
    int32_t rc;

    __asm( " SYSSTATE ARCHLVL=2\n"
    " MODESET MODE=SUP,KEY=ZERO\n"
    " ST 15,%0"
    : "=m"(rc)
    :
    : "r0", "r1", "r14", "r15" );

    return rc;
    }

    int signon(void)
    {
    uint32_t frc;
    uint32_t rc, reason;
    int success;

    success = TRUE;
    rc = 0;
    reason = 0;
    frc = DSNRLI(RRSAF_SIGNON,
    MY_CORRELATION_ID,
    MY_ACCOUNTING_TOKEN,
    "AT_END",
    &rc,
    &reason);

    if (frc != 0 || rc != 0) {
    success = FALSE;
    printf("SIGNON failed. frc: %u rc: x'%08X' reason: x'%08X'\n",
    frc, rc, reason);
    translate(frc, rc, reason);
    }

    return success;
    }

    void spitOut(unsigned char *buf, int len)
    {
    int i, j;
    unsigned char txt[16];

    j = 0;
    for (i = 0; i < len; i++) {
    if (j == 0) {
    printf("%04X:", i);
    }
    printf(" %02X", buf[i]);
    if (j == 7) printf(" ");
    if (isprint(buf[i]))
    txt[j] = buf[i];
    else
    txt[j] = '.';
    j++;
    if (j == 16) {
    printf(" >");
    for (j = 0; j < 16; j++) {
    printf("%c", txt[j]);
    }
    printf("<\n");
    j = 0;
    }
    }
    if (j > 0) {
    for (i = j; i < 16; i++) printf(" ");
    if (j < 7) printf (" ");
    printf(" >");
    for (i = 0; i < j; i++) {
    printf("%c", txt[i]);
    }
    printf("<\n");
    }
    }

    int terminate_identify(void)
    {
    int32_t frc;
    uint32_t rc, reason;
    int success;

    success = TRUE;
    rc = 0;
    reason = 0;
    frc = DSNRLI(RRSAF_TERMINATE_IDENTIFY,
    &rc,
    &reason);

    if (frc != 0 || rc != 0) {
    success = FALSE;
    printf(
    "TERMINATE IDENTIFY failed. frc: %u rc: x'%08X' reason: x'%08X'\n",
    frc, rc, reason);
    translate(frc, rc, reason);
    }

    return success;
    }

    int terminate_thread(void)
    {
    uint32_t frc;
    uint32_t rc, reason;
    int success;

    success = TRUE;
    rc = 0;
    reason = 0;
    frc = DSNRLI(RRSAF_TERMINATE_THREAD,
    &rc,
    &reason);

    if (frc != 0 || rc != 0) {
    success = FALSE;
    printf(
    "TERMINATE THREAD failed. frc: %u rc: x'%08X' reason: x'%08X'\n",
    frc, rc, reason);
    translate(frc, rc, reason);
    }

    return success;
    }

    void translate(uint32_t frc, uint32_t rc, uint32_t reason)
    {
    uint32_t irc;
    uint32_t jrc, jreason;

    if (frc == rc &&
    (reason >> 16 == 0x00f3 && reason != 0x00f30006)) {
    /* reason is "translatable" */
    irc = DSNRLI(RRSAF_TRANSLATE,
    &sqlca,
    &jrc,
    &jreason);
    if (irc != 0 || jrc != 0) {
    printf("TRANSLATE failed. frc: %u rc: x'%08X' reason: x'%08X'\n",
    irc, jrc, jreason);
    }
    else {
    printSQLCA();
    }
    }
    }
  10. Compile the program

    We compile the source and mark the executable as APF-authorised, then BIND the DBRM as indicated in the following Makefile:

    db2log: db2log.c
    xlc -odb2log -qnomar -qnoseq -qsql -Fdb2.cfg "-I//'MATTO.H'" \
    -Wl,AC=1 -qasm -qasmlib=sys1.maclib "-qdbrmlib=//'MATTO.DBRMLIB(DB2LOG)'" db2log.c
    extattr +a db2log
    dsnuss DBBG "BIND PACKAGE(MYCOLL) MEMBER(DB2LOG) LIBRARY('MATTO.DBRMLIB') ACTION(REPLACE)"

    Note that you will most likely need a compiler configuration file (see the other recipe mentioned above) to ensure hlq.SDSNLOAD is in the STEPLIB for the compile.

    Note also the -qnomar and -qnoseq options, mentioned earlier, which are needed because I am including the EDCDSECT-generated headers from a PDS.

  11. Run the program

    Make sure hlq.SDSNLOAD is in your STEPLIB, then you are ready to run your program.

    If your program is not running APF-authorised, you will see the following, when you try to run it:

    $ db2log dbbg h
    Abend code: 047
    Abend reason: NULL
    ERROR: This program must run APF-authorised

    As an example of what to expect when it is working as intended, here is the output from the ‘D’ = ‘read a single record’ command for a log record associated with a SQL UPDATE operation:

    $ db2log dbbg d 15779D395
    ifiGetSingleRba: DSNWLI READS rc=0
    IFCA rc=00000000, reason=00000000
    QW0306OF:
    QW0306CS: 'I306'
    QW0306FT: 'V10 '
    QW0306LD: 319 bytes
    QWT02:
    QWT02PSO: 28 L: 86 N: 1
    QWT02R1O: 114 L: 14 N: 1
    QWT02R2O: 128 L: 0 N: 1
    QWHS:
    QWHSLEN: 86 bytes
    QWHSTYP: 01
    QWHSIID: IFCID 0306
    QWHSSSID: 'DBBG'
    QWHSLOCN: 'DALLASB '
    QWHSNID: 'NETD '
    QWHSLUNM: 'DBBGLU1 '
    QWHSSID: 'S0W1'
    QW0306:
    QW0306ES: 00000000000000000000
    QW0306CT: 1 log records
    --- Log record 1 ---
    QW0306L:
    QW0306RL: 0000000000015779D395
    LRH:
    LRHLL: 163 bytes
    LRHRTYPE: 0600
    LRHSTYPE: 0001
    Log record body (83 bytes):
    0000: 8C 01 AC 00 02 00 00 00 02 00 00 00 00 00 00 01 >................<
    0010: 57 6D 52 64 2B 02 00 00 00 3B 09 01 00 03 29 11 >._..............<
    0020: 00 12 00 0A 00 21 80 00 E3 88 85 99 85 40 40 40 >........There <
    0030: 40 40 00 00 21 00 00 01 C8 85 93 93 96 40 40 40 > ......Hello <
    0040: 40 40 40 40 E6 96 99 93 84 40 40 40 40 40 00 80 > World ..<
    0050: 00 04 D2 >..K<

Join The Discussion