Contents


Overview

Skill Level: Intermediate

Experience with DB2 and MQ application development under z/OS is an advantage.

This recipe demonstrates how to use the RRSAF interface of DB2 for z/OS, the MQ for z/OS API and the Resource Recovery Services (RRS) API in a C program which is compiled and executed from z/OS UNIX.

Ingredients

DB2 for z/OS V10 or higher

MQ for z/OS V7 or higher

IBM XL C/C++ compiler (z/OS feature)

Access to z/OS UNIX shell, preferably via a TELNET or SSH connection

A method for creating a table in a DB2 database and viewing its contents (this recipe will use IBM Data Studio)

A method for creating a MQ queue and putting messages on that queue (this recipe will use IBM MQ Explorer)

It is assumed that you have completed the recipe "My First DB2 Application in C from the z/OS UNIX Shell", as tools and techniques from that recipe will be used here.  See: https://developer.ibm.com/recipes/tutorials/my-first-db2-application-in-c-from-the-zos-unix-shell/

Step-by-step

  1. Preparation

    • From the z/OS UNIX shell, create a directory for this recipe, “cd” to that directory and copy across the files, “dsnuss” and “db2.cfg” from the directory you used for the¬†“My First DB2 Application in C from the z/OS UNIX Shell” recipe:
    $ mkdir rrs2pc
    $ cd rrs2pc
    $ cp ../rrsample/dsnuss .
    $ cp ../rrsample/db2.cfg .
    • Create a local queue in your chosen MQ for z/OS queue manager. ¬†For example, from MQ Explorer, right-click on the “Queues” folder of the queue manager. ¬†Select “New->Local Queue…” from the popup menu. ¬†Enter a queue name in the “New Local Queue” dialog (e.g. RRS2PC.Q) and click “Finish”.
    • Create a table in your chosen DB2 subsystem. ¬†For example, in IBM Data Studio, use a SQL Script to create the table. ¬†DDL to define the table is shown below. ¬†Adapt the schema and database or tablespace to your local environment.
    CREATE TABLE DSN8B10.MQMSG (
    STAMP TIMESTAMP NOT NULL,
    MESSAGE CHAR(70) NOT NULL
    ) IN DATABASE DSN8DB1A;

    Now use “dsnuss” to create a C header file for this table, as follows:

    $ dsnuss DBBG "DCLGEN TABLE(DSN8B10.MQMSG) LIBRARY('MATTO.DCLGEN(MQMSG)') LANGUAGE(C)"
    ex 'MATTO.EXEC(DSNUSS)' 'DBBG DCLGEN TABLE(DSN8B10.MQMSG) LIBRARY(''MATTO.DCLGEN(MQMSG)'') LANGUAGE(C)'
    DSNE905I EXECUTION COMPLETE, MEMBER MQMSG ADDED
    *** Success: found DSNE905I
    *** dsnuss rc = 0
    $ cp "//'MATTO.DCLGEN(MQMSG)'" mqmsg.h
  2. Write the Code

    We will base this application on the code from the¬†“My First DB2 Application in C from the z/OS UNIX Shell” recipe, with some modifications to connect to MQ, retrieve a message and write that message to a DB2 table (or roll back both operations). ¬†The use of the RRSAF API to connect to DB2 ensures SQL operations are coordinated by RRS (as the interface's name implies ☺).

    The code to connect to MQ and open a queue for input is very straightforward, matched by code to disconnect from MQ, as follows:

    int connect_MQ()
    {
    MQLONG cc, rc;
    MQOD od;
    int success = FALSE;

    /* connect to the queue manager */
    MQCONN(MY_MQ_QMGR,
    &hconn,
    &cc,
    &rc);
    diagnoseMQ("MQCONN to qmgr", cc, rc, TRUE);

    if (cc != MQCC_FAILED) {
    /* open the queue for input */
    memcpy(&od, &odModel, sizeof(MQOD));
    strcpy(od.ObjectName, MY_MQ_Q);
    MQOPEN(hconn,
    &od,
    MQOO_INPUT_SHARED,
    &hobj,
    &cc,
    &rc);
    diagnoseMQ("MQOPEN of queue", cc, rc, TRUE);
    if (cc != MQCC_FAILED) {
    success = TRUE;
    }
    else {
    /* disconnect from queue manager */
    MQDISC(&hconn, &cc, &rc);
    diagnoseMQ("MQDISC from qmgr", cc, rc, TRUE);
    }
    }
    return success;
    }

    void disconnect_MQ()
    {
    MQLONG cc, rc;

    /* close queue */
    MQCLOSE(hconn, &hobj, MQCO_NONE, &cc, &rc);
    diagnoseMQ("MQCLOSE of queue", cc, rc, TRUE);

    /* disconnect from the queue manager */
    MQDISC(&hconn, &cc, &rc);
    diagnoseMQ("MQDISC from qmgr", cc, rc, TRUE);
    }

    The code that “does the work” (see below) issues a MQGET and inserts one retrieved message into our DB2 table. ¬†The user is prompted to “commit”, “roll back” or “crash” (with a deliberate “divide by zero”). ¬†The RRS functions, SRRCMIT() and SRRBACK(), are used to commit or roll back the coordinated unit of work. ¬†They are documented here:
    https://www.ibm.com/support/knowledgecenter/SSLTBW_2.1.0/com.ibm.zos.v2r1.ieac100/rrs.htm

    In the code below, note that the MQGMO_SYNCPOINT “get message option” is selected. ¬†This ensures that the MQGET operation is part of the “unit or recovery” (or UR) in RRS speak.

    void doWork()
    {
    MQMD md;
    MQGMO gmo;
    MQLONG cc, rc, bytesRead;
    char answer[80];
    atrs_return_code arc;
    int x, y, z;

    /* get a message from the queue */
    memcpy(&md, &mdModel, sizeof(MQMD));
    memcpy(&gmo, &gmoModel, sizeof(MQGMO));
    gmo.Options = MQGMO_SYNCPOINT |
    MQGMO_NO_WAIT |
    MQGMO_CONVERT |
    MQGMO_ACCEPT_TRUNCATED_MSG;
    MQGET(hconn,
    hobj,
    &md,
    &gmo,
    sizeof(DCLMQMSG.MESSAGE)-1,
    DCLMQMSG.MESSAGE,
    &bytesRead,
    &cc,
    &rc);
    diagnoseMQ("MGET of message", cc, rc, FALSE);
    if (cc == MQCC_FAILED) return;

    /* insert the message into the DB2 table */
    DCLMQMSG.MESSAGE[bytesRead] = NUL;
    EXEC SQL INSERT
    INTO DSN8B10.MQMSG
    (STAMP, MESSAGE)
    VALUES (CURRENT TIMESTAMP, :DCLMQMSG.MESSAGE);
    diagnose("INSERT of message", FALSE);
    if (SQLCODE < 0) {
    SRRBACK(&arc);
    printf("SRRBACK rc=%d\n", arc);
    return;
    }

    /* decide whether to commit or not */
    printf("\n");
    printf("Message fetched from queue:\n");
    printf("\n");
    printf("\t\"%s\"\n", DCLMQMSG.MESSAGE);
    printf("\n");
    printf("Do you want to COMMIT this message to the database?\n");
    printf("Enter C to commit, 0 to crash, or other to roll back\n");
    gets(answer);
    switch (toupper(answer[0])) {
    case 'C':
    printf("Committing...\n");
    SRRCMIT(&arc);
    printf("SRRCMIT rc=%d\n", arc);
    break;
    case '0':
    printf("Crashing...\n");
    x = 0;
    y = 0;
    z = x / y;
    break;
    default:
    printf("Rolling Back...\n");
    SRRBACK(&arc);
    printf("SRRBACK rc=%d\n", arc);
    break;
    }

    printf("\n");
    }

    Now create a file called “rrs2pc.c” and copy and paste the complete program below into that file:

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

    #include <cmqc.h> /* MQ API */
    #include <atrsc.h> /* RRS API */

    /* #include "dsneib.h" */
    /* #include "dsnrib.h" */

    typedef uint32_t RRSAF_FN();
    #pragma linkage(RRSAF_FN, OS)
    typedef void HLI_FN(uint32_t *);
    #pragma linkage(HLI_FN, OS)

    RRSAF_FN *dsnrli;
    HLI_FN *dsnhli;
    #define DSNRLI (*dsnrli)
    #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 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_DB2_SSID "DBBG"
    #define MY_CORRELATION_ID "RRS2PC " /* 12 characters */
    #define MY_ACCOUNTING_TOKEN "MYACCOUNTINGTOKEN " /* 22 characters */
    #define MY_PLAN "MYPLAN " /* 8 characters */

    /* static settings for MQ */
    #define MY_MQ_QMGR "CSQ7"
    #define MY_MQ_Q "RRS2PC.Q"

    EXEC SQL INCLUDE SQLCA;

    EXEC SQL BEGIN DECLARE SECTION;
    #include "mqmsg.h"
    /* following DECLARE only needed because table data is UNICODE */
    EXEC SQL DECLARE :DCLMQMSG VARIABLE CCSID 1047;
    EXEC SQL END DECLARE SECTION;

    /* MQ global variables - for simplicity */
    MQHCONN hconn;
    MQHOBJ hobj;
    MQOD odModel = {MQOD_DEFAULT};
    MQGMO gmoModel = {MQGMO_DEFAULT};
    MQMD mdModel = {MQMD_DEFAULT};

    int load_RRSAF()
    {
    int result;

    result = FALSE;
    dsnrli = (RRSAF_FN *)__fetch("DSNRLI");
    if (dsnrli == NULL) {
    perror("ERROR: Unable to load RRSAF module DSNRLI");
    }
    else {
    dsnhli = (HLI_FN *)__fetch("DSNHLIR");
    if (dsnhli == NULL) {
    perror("ERROR: Unable to load module DSNHLIR");
    __rlse(dsnrli);
    }
    else {
    result = TRUE;
    }
    }

    return result;
    }

    void release_RRSAF()
    {
    __rlse(dsnhli);
    __rlse(dsnrli);
    }

    void printSQLCA()
    {
    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 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();
    }
    }
    }

    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 signon()
    {
    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;
    }

    int create_thread()
    {
    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);
    translate(frc, rc, reason);
    }

    return success;
    }

    int terminate_thread()
    {
    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;
    }

    int terminate_identify()
    {
    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;
    }

    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);
    }
    }

    int connect_DB2()
    {
    int success = FALSE;

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

    return success;
    }

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

    void diagnoseMQ(char *text, MQLONG cc, MQLONG rc, int showSuccess)
    {
    if (cc == MQCC_FAILED) {
    printf("--- %20s FAILED --- cc=%d rc=%d\n", text, cc, rc);
    }
    else if (showSuccess) {
    printf("--- %20s SUCCEEDED --- cc=%d rc=%d\n", text, cc, rc);
    }
    }

    int connect_MQ()
    {
    MQLONG cc, rc;
    MQOD od;
    int success = FALSE;

    /* connect to the queue manager */
    MQCONN(MY_MQ_QMGR,
    &hconn,
    &cc,
    &rc);
    diagnoseMQ("MQCONN to qmgr", cc, rc, TRUE);

    if (cc != MQCC_FAILED) {
    /* open the queue for input */
    memcpy(&od, &odModel, sizeof(MQOD));
    strcpy(od.ObjectName, MY_MQ_Q);
    MQOPEN(hconn,
    &od,
    MQOO_INPUT_SHARED,
    &hobj,
    &cc,
    &rc);
    diagnoseMQ("MQOPEN of queue", cc, rc, TRUE);
    if (cc != MQCC_FAILED) {
    success = TRUE;
    }
    else {
    /* disconnect from queue manager */
    MQDISC(&hconn, &cc, &rc);
    diagnoseMQ("MQDISC from qmgr", cc, rc, TRUE);
    }
    }
    return success;
    }

    void disconnect_MQ()
    {
    MQLONG cc, rc;

    /* close queue */
    MQCLOSE(hconn, &hobj, MQCO_NONE, &cc, &rc);
    diagnoseMQ("MQCLOSE of queue", cc, rc, TRUE);

    /* disconnect from the queue manager */
    MQDISC(&hconn, &cc, &rc);
    diagnoseMQ("MQDISC from qmgr", cc, rc, TRUE);
    }

    void doWork()
    {
    MQMD md;
    MQGMO gmo;
    MQLONG cc, rc, bytesRead;
    char answer[80];
    atrs_return_code arc;
    int x, y, z;

    /* get a message from the queue */
    memcpy(&md, &mdModel, sizeof(MQMD));
    memcpy(&gmo, &gmoModel, sizeof(MQGMO));
    gmo.Options = MQGMO_SYNCPOINT |
    MQGMO_NO_WAIT |
    MQGMO_CONVERT |
    MQGMO_ACCEPT_TRUNCATED_MSG;
    MQGET(hconn,
    hobj,
    &md,
    &gmo,
    sizeof(DCLMQMSG.MESSAGE)-1,
    DCLMQMSG.MESSAGE,
    &bytesRead,
    &cc,
    &rc);
    diagnoseMQ("MGET of message", cc, rc, FALSE);
    if (cc == MQCC_FAILED) return;

    /* insert the message into the DB2 table */
    DCLMQMSG.MESSAGE[bytesRead] = NUL;
    EXEC SQL INSERT
    INTO DSN8B10.MQMSG
    (STAMP, MESSAGE)
    VALUES (CURRENT TIMESTAMP, :DCLMQMSG.MESSAGE);
    diagnose("INSERT of message", FALSE);
    if (SQLCODE < 0) {
    SRRBACK(&arc);
    printf("SRRBACK rc=%d\n", arc);
    return;
    }

    /* decide whether to commit or not */
    printf("\n");
    printf("Message fetched from queue:\n");
    printf("\n");
    printf("\t\"%s\"\n", DCLMQMSG.MESSAGE);
    printf("\n");
    printf("Do you want to COMMIT this message to the database?\n");
    printf("Enter C to commit, 0 to crash, or other to roll back\n");
    gets(answer);
    switch (toupper(answer[0])) {
    case 'C':
    printf("Committing...\n");
    SRRCMIT(&arc);
    printf("SRRCMIT rc=%d\n", arc);
    break;
    case '0':
    printf("Crashing...\n");
    x = 0;
    y = 0;
    z = x / y;
    break;
    default:
    printf("Rolling Back...\n");
    SRRBACK(&arc);
    printf("SRRBACK rc=%d\n", arc);
    break;
    }

    printf("\n");
    }

    int main(int argc, char **argv)
    {
    if (!load_RRSAF()) {
    return 12;
    }

    if (connect_DB2()) {
    if (connect_MQ()) {
    doWork();
    disconnect_MQ();
    }
    disconnect_DB2();
    }

    release_RRSAF();

    return 0;
    }

    Modify the following settings in the code to suit your environment:

    /* static settings for RRSAF connection to DB2 */
    #define MY_DB2_SSID             "DBBG"
    #define MY_CORRELATION_ID       "RRS2PC      "              /* 12 characters */
    #define MY_ACCOUNTING_TOKEN     "MYACCOUNTINGTOKEN     "    /* 22 characters */
    #define MY_PLAN                 "MYPLAN  "                  /*  8 characters */
    
    /* static settings for MQ */
    #define MY_MQ_QMGR              "CSQ7"
    #define MY_MQ_Q                 "RRS2PC.Q"
    
  3. Compile the Program

    Create a file called “Makefile” and copy and paste the following lines into that file, adapting the MVS dataset names, in particular, to your environment:

    rrs2pc: rrs2pc.c
    cp "//'CSQ710.SCSQLOAD(CSQBRSTB)'" csqbrstb.o
    xlc -orrs2pc -Fdb2.cfg -qsql "-qdbrmlib=//'MATTO.DBRMLIB(RRS2PC)'" "-I//'CSQ710.SCSQC370'" rrs2pc.c csqbrstb.o
    dsnuss DBBG "BIND PACKAGE(MYCOLL) MEMBER(RRS2PC) LIBRARY('MATTO.DBRMLIB')"
    dsnuss DBBG "BIND PLAN(MYPLAN) PKLIST(MYCOLL.RRS2PC)"

    ¬†*** Note: each of the 4 ‚Äúrule‚ÄĚ lines MUST start with a ‚Äútab‚ÄĚ character, not 8 spaces.

    In case you are wondering what the CSQBRSTB module in the MQ “SCSQLOAD” library is, it is the MQ “stub” that you use when you want to use the RRS verbs, SRRCMIT and SRRBACK to commit and roll back units of work under RRS control. ¬†The reason why I copy it to a z/OS UNIX file is to make it easier for the compiler to find it. ¬†It is copied each time, to make sure the compiler is using the latest version of the stub. ¬†It is a very small file (~ 16KB).

    To build the program, issue the ‚Äúmake‚ÄĚ command. If all goes well, you should see something like this:

    $ make
    cp "//'CSQ710.SCSQLOAD(CSQBRSTB)'" csqbrstb.o
    xlc -orrs2pc -Fdb2.cfg -qsql "-qdbrmlib=//'MATTO.DBRMLIB(RRS2PC)'" "-I//'CSQ710.SCSQC370'" rrs2pc.c csqbrstb.o
    INFORMATIONAL CCN4430 Compiling with the SQL compiler option resulted in the following message: DSNH4760I DSNHPSRV THE DB2 SQL COPROCESSOR IS USING THE LEVEL 2 INTERFACE UNDER DB2 V11
    dsnuss DBBG "BIND PACKAGE(MYCOLL) MEMBER(RRS2PC) LIBRARY('MATTO.DBRMLIB')"
    ex 'MATTO.EXEC(DSNUSS)' 'DBBG BIND PACKAGE(MYCOLL) MEMBER(RRS2PC) LIBRARY(''MATTO.DBRMLIB'')'
    DSNT254I -DBBG DSNTBCM2 BIND OPTIONS FOR
    PACKAGE = DALLASB.MYCOLL.RRS2PC.()
    ACTION REPLACE
    OWNER MATTO
    QUALIFIER MATTO
    VALIDATE RUN
    EXPLAIN NO
    ISOLATION
    RELEASE
    COPY
    APREUSE
    APCOMPARE
    APRETAINDUP
    BUSTIMESENSITIVE YES
    SYSTIMESENSITIVE YES
    ARCHIVESENSITIVE YES
    APPLCOMPAT V11R1
    DSNT255I -DBBG DSNTBCM2 BIND OPTIONS FOR
    PACKAGE = DALLASB.MYCOLL.RRS2PC.()
    SQLERROR NOPACKAGE
    CURRENTDATA NO
    DEGREE 1
    DYNAMICRULES
    DEFER
    NOREOPT VARS
    KEEPDYNAMIC NO
    IMMEDWRITE INHERITFROMPLAN
    DBPROTOCOL DRDA
    OPTHINT
    ENCODING UNICODE(01208)
    PLANMGMT OFF
    PLANMGMTSCOPE STATIC
    CONCURRENTACCESSRESOLUTION
    EXTENDEDINDICATOR
    PATH
    DSNT275I -DBBG DSNTBCM2 BIND OPTIONS FOR
    PACKAGE = DALLASB.MYCOLL.RRS2PC.()
    QUERYACCELERATION
    GETACCELARCHIVE
    DSNT232I -DBBG SUCCESSFUL BIND FOR
    *** Success: found DSNT232I
    PACKAGE = DALLASB.MYCOLL.RRS2PC.()
    *** dsnuss rc = 0
    dsnuss DBBG "BIND PLAN(MYPLAN) PKLIST(MYCOLL.RRS2PC)"
    ex 'MATTO.EXEC(DSNUSS)' 'DBBG BIND PLAN(MYPLAN) PKLIST(MYCOLL.RRS2PC)'
    DSNT252I -DBBG DSNTBCM1 BIND OPTIONS FOR PLAN MYPLAN
    ACTION REPLACE
    OWNER MATTO
    VALIDATE RUN
    ISOLATION CS
    ACQUIRE USE
    RELEASE COMMIT
    EXPLAIN NO
    DYNAMICRULES RUN
    PROGAUTH DISABLE
    DSNT253I -DBBG DSNTBCM1 BIND OPTIONS FOR PLAN MYPLAN
    NODEFER PREPARE
    CACHESIZE 3072
    QUALIFIER MATTO
    CURRENTSERVER
    CURRENTDATA NO
    DEGREE 1
    SQLRULES DB2
    DISCONNECT EXPLICIT
    NOREOPT VARS
    KEEPDYNAMIC NO
    IMMEDWRITE NO
    DBPROTOCOL DRDA
    OPTHINT
    ENCODING UNICODE(01208)
    CONCURRENTACCESSRESOLUTION
    PATH
    DSNT200I -DBBG BIND FOR PLAN MYPLAN SUCCESSFUL
    *** Success: found DSNT200I
    *** dsnuss rc = 0
  4. Run the Program

    Put a message onto your queue. ¬†For example, using MQ Explorer, open the “Queues” folder for your chosen queue manager. ¬†Right-click on the queue you created in Step 1 and select “Put Test Message…” from the popup menu. ¬†Type in some “Message data”, click “Put message”, then click “Close”

    putmsg

    Now run the program.  You should see somethig like this:

    $ rrs2pc
    --- MQCONN to qmgr SUCCEEDED --- cc=0 rc=0
    --- MQOPEN of queue SUCCEEDED --- cc=0 rc=0

    Message fetched from queue:

    "this is some message data"

    Do you want to COMMIT this message to the database?
    Enter C to commit, 0 to crash, or other to roll back

     Before responding to this prompt, let's have a look around.  If you check the MQ queue, you will see that one application has the queue open for input, but the current depth of the queue is zero:

    q1

    Because an INSERT is pending, the DB2 table is locked.

    Now respond to our program by typing a 'c' then press ENTER.  You should see something like this:

    c
    Committing...
    SRRCMIT rc=0

    --- MQCLOSE of queue SUCCEEDED --- cc=0 rc=0
    --- MQDISC from qmgr SUCCEEDED --- cc=0 rc=0
    $

    Now when you check your MQ queue, you should see the empty queue has no applications holding it open for input:

    q2

    And the DB2 table now holds our message:

    d1

    That's it!  We've successfully implemented two-phase commit!

    Feel free to experiment with the “roll back” and “crash” modes of the program. ¬†When you “roll back”, you expect to see that your original MQ message is reinstated on the queue and nothing is INSERTed into the DB2 table. ¬†

    You expect the same behaviour when you “crash”, but be aware that a “CEEDUMP” file will be written to your z/OS UNIX directory each time. ¬†If you look at this file, you will see an (uncaught) “divide by zero” exception, as expected:

    ...
    Condition Information for Active Routines
    Condition Information for (DSA address 1F0272E0)
    CIB Address: 1F028948
    Current Condition:
    CEE0198S The termination of a thread was signaled due to an unhandled condition.
    Original Condition:
    CEE3209S The system detected a fixed-point divide exception (System Completion Code=0C9).
    ...

    Finally, when your program is waiting at the “Enter C to commit, 0 to crash, or other to roll back” prompt, you can interrogate RRS, via a MVS operator command, requesting a list of all units of recovery. ¬†You should see at least one “Protected” unit of work. ¬†This will be our program:

    D RRS,UR                                                             
    ATR601I 22.38.56 RRS UR SUMMARY 222
    URID SYSTEM GNAME ST TYPE COMMENTS
    D141D9837E4660000000000001010000 S0W1 ADCDPL FLT Unpr
    D141D9847E4663D00000000001010000 S0W1 ADCDPL FLT Unpr
    D14BD4877E4667A00000004201010000 S0W1 ADCDPL FLT Prot

    If you request details for this protected “UR”, you will see that there are two “resource managers” enrolled in this unit of recovery:

    D RRS,UR,D,URID=D14BD4877E4667A00000004201010000                  
    ATR603I 22.44.58 RRS UR DETAIL 224
    URID: D14BD4877E4667A00000004201010000
    SURID:N/A
    LUWID : Not Present
    EID : Not Present
    XID : Not Present

    SYSTEM: S0W1 GNAME: ADCDPL STATE: FLT TYPE: Prot

    URI TOKEN: 7E35B00000002B780055000155555555
    RM NAME: CSQ.RRSATF.IBM.CSQ7
    TYPE: Unpr STATUS: ACTIVE ROLE: Participant STATE: FLT

    ST STATUS DURATION
    BAK: Uncalled
    CMP: Uncalled
    CMT: Uncalled
    DBT: Uncalled
    END: Uncalled
    EFE: Uncalled
    OLA: Uncalled
    PPP: Uncalled
    PRP: Uncalled
    SCK: Uncalled
    URI TOKEN: 7E35B5800000000F0055000155555555
    RM NAME: DSN.RRSATF.IBM.DBBG
    TYPE: Prot STATUS: ACTIVE ROLE: Participant STATE: FLT

    ST STATUS DURATION
    BAK: Uncalled
    CMP: Uncalled
    CMT: Uncalled
    DBT: Uncalled
    END: Uncalled
    EFE: Uncalled
    OLA: Uncalled
    PPP: Uncalled
    PRP: Uncalled
    SCK: Uncalled

1 comment on"Two-Phase Commit under z/OS UNIX between DB2 and MQ"

  1. […] Two-Phase Commit under z/OS UNIX between DB2 and MQ […]

Join The Discussion