Contents


Overview

Skill Level: Intermediate

Some z/OS TSO/E experience required. UNIX, DB2 and C/C++ experience is an advantage.

This recipe describes the process of writing and compiling a DB2 application using the RRSAF interface. The application is written in C and the entire development process is conducted using the z/OS UNIX shell and HFS files - no ISPF or JCL.

Ingredients

DB2 for z/OS V10 or higher

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

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

Step-by-step

  1. Find a nice playground

    For this exercise, you will need a DB2 for z/OS subsystem to which you have CONNECT privilege and “BIND” privilege to BIND packages into a collection.  From there, you need authority to BIND your packages into a plan.  You may need the assistance of a database administrator, if you don't already have these privileges for a suitable collection and plan.

    You will also need SELECT access to a table in that subsystem.  In what follows, I use a sample table shipped with DB2, DSN8B10.DEPT, but you can adapt this recipe to any table you like, and for that matter, any combination of SQL statements you like.

    We will be doing most of this work in the z/OS UNIX shell, so you will need a z/OS user ID with an OMVS segment and appropriate home directory, shell program, etc.

    To make life easier in the z/OS UNIX shell, make sure the current directory is in your PATH and the appropriate load libraries for the above DB2 subsystem are in your STEPLIB.  The best way to do this is to put statements like these in the “.profile” file in your home directory:

    export PATH=.:/bin
    export STEPLIB=DSNB10.DBBG.SDSNEXIT:DSNB10.SDSNLOAD

    This will establish the correct PATH whenever you log on to the z/OS UNIX shell.  You need to log out of z/OS UNIX and log back in, for these changes to take effect.

  2. Create a DSN command for z/OS UNIX

    To create a C header file which describes the DB2 table(s) your program will access, and to “bind” the package and plan associated with your program, you would use the DSN command under TSO.  Unfortuantely, there is no equivalent of this command under z/OS UNIX, so we will create one.

    Create a member called “DSNUSS” in an existing MVS partitioned dataset (say a CLIST library).  Add the following lines to this new PDS member:

    /* REXX */
    dropbuf
    parse upper arg ssid cmd
    queue cmd
    queue "END"
    ADDRESS TSO "DSN SYSTEM("ssid")"
    dropbuf

    From the z/OS UNIX shell, create a subdirectory for this recipe, and “cd” to that directory.  For example:

    $ mkdir rrsample
    $ cd rrsample
    $

    Create a file called “dsnuss” in this directory.  Add the following lines to this file, substituting the name of your PDS (above) for “MATTO.EXEC” on the second last line:

    /* REXX */
    /* Execute a DB2 DSN command from z/OS UNIX */
    /* */
    /* Usage: dsnuss DB2_ssid "command_string" */

    parse upper arg ssid cmd

    /* double up any apostrophes in the command */
    outcmd = ""
    do i = 1 to length(cmd)
    c = substr(cmd, i, 1)
    outcmd = outcmd || c
    if c = "'" then do
    outcmd = outcmd || c
    end
    end

    /* execute the command under TSO */
    cmd = "ex 'MATTO.EXEC(DSNUSS)' '"ssid" "outcmd"'"
    'tso "'cmd'" > dsnuss.out'

    /* analyse command output to test for success */
    marker.0 = 0
    if word(outcmd, 1) = "BIND" then do
    if left(word(outcmd, 2), 7) = "PACKAGE" then do
    marker.0 = 1
    marker.1 = "DSNT232I"
    end
    if left(word(outcmd, 2), 4) = "PLAN" then do
    marker.0 = 1
    marker.1 = "DSNT200I"
    end
    end
    if word(outcmd, 1) = "DCLGEN" then do
    marker.0 = 2
    marker.1 = "DSNE904I"
    marker.2 = "DSNE905I"
    end
    myrc = 0
    if marker.0 > 0 then do
    address syscall "readfile dsnuss.out file."
    myrc = 12
    do i = 1 to file.0
    say file.i
    do j = 1 to marker.0
    if pos(marker.j, file.i) > 0 then do
    myrc = 0
    say "*** Success: found "marker.j
    end
    end
    end
    end
    else do
    "cat dsnuss.out"
    end
    say "*** dsnuss rc = "myrc
    return myrc

     Make this file executable.  For example:

    $ chmod +x dsnuss
  3. Create a header file to declare the DB2 table

    To assist the IBM XL C/C++ compiler's SQL coprocessor (which is essentially the traditional DB2 precompiler embedded inside the compiler), we need to provide a C header file which contains the declaration of the table, and host variables to access the table's columns.

    We will use our newly-minted “dsnuss” command to do this, but first, allocate a MVS partitioned dataset, with RECFM=F (or FB), LRECL=80 to hold the C header file we will generate.  In what follows, I have used a PDS called “MATTO.DCLGEN”.

    Create the C header file for your DB2 table, substituting the DB2 subsystem ID, DB2 table and the output PDS for your environment:

    $ dsnuss DBBG "DCLGEN TABLE(DSN8B10.DEPT) LIBRARY('MATTO.DCLGEN(DEPT)') LANGUAGE(C) INDVAR(YES) ACTION(REPLACE)"
    ex 'MATTO.EXEC(DSNUSS)' 'DBBG DCLGEN TABLE(DSN8B10.DEPT) LIBRARY(''MATTO.DCLGEN(DEPT)'') LANGUAGE(C) INDVAR(YES) ACTION(REPLACE)'
    DSNE905I EXECUTION COMPLETE, MEMBER DEPT ADDED
    *** Success: found DSNE905I
    *** dsnuss rc = 0
    $

     Now copy that header file to your z/OS UNIX directory:

    $ cp "//'MATTO.DCLGEN(DEPT)'" dept.h

     The resulting C header file looks like this:

    MATTO:/u/matto/rrsample: >cat dept.h
    /*********************************************************************/
    /* DCLGEN TABLE(DSN8B10.DEPT) */
    /* LIBRARY(MATTO.DCLGEN(DEPT)) */
    /* ACTION(REPLACE) */
    /* LANGUAGE(C) */
    /* APOST */
    /* INDVAR(YES) */
    /* ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS */
    /*********************************************************************/
    EXEC SQL DECLARE DSN8B10.DEPT TABLE
    ( DEPTNO CHAR(3) NOT NULL,
    DEPTNAME VARCHAR(36) NOT NULL,
    MGRNO CHAR(6),
    ADMRDEPT CHAR(3) NOT NULL,
    LOCATION CHAR(16)
    ) ;
    /*********************************************************************/
    /* C DECLARATION FOR TABLE DSN8B10.DEPT */
    /*********************************************************************/
    struct
    { char DEPTNO??(4??);
    struct
    { short int DEPTNAME_len;
    char DEPTNAME_data??(36??);
    } DEPTNAME;
    char MGRNO??(7??);
    char ADMRDEPT??(4??);
    char LOCATION??(17??);
    } DCLDEPT;
    /*********************************************************************/
    /* INDICATOR VARIABLE ARRAY */
    /*********************************************************************/
    short int IDEPT??(5??);
    /*********************************************************************/
    /* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 5 */
    /*********************************************************************/

    Don't be flummoxed by the ??( and ??) “trigraphs”.  They are simply a codepage-independent way of representing square brackets.

    Notice that host variables for “CHAR(n)” columns are declared as char[n+1], so fixed-length character columns are returned as null-terminated C strings.  However, host variables for VARCHAR(n) columns are declared as a 2-byte length, followed by a n-byte buffer, with no allowance for a null at the end.  We need to be aware of this distinction in our code.

    The indicator variable array, IDEPT[5], is created by the DCLGEN command, when INDVAR(YES) is specified, as above.  This array provides one element per column in the table.  The code in this receipe demonstrates the use of this array to detect null values in the MGRNO and LOCATION columns.

  4. Write the Code

    The next few steps discuss important sections of the code.  Don't copy and paste the code from these sections into a source file just yet.  The time to do this is at the “Tying It All Together” step.

    The major sections of our code are:

    • Loading the RRSAF interface
    • Dealing with errors from RRSAF
    • Connecting to the DB2 subsystem
    • Invoking SQL statements
  5. Loading the RRSAF Interface

    We are going to load the RRSAF interface dynamically, so our code doesn't need to be re-linked if service is applied to DB2.

    We also need to communicate to the C compiler that the entry point for the RRSAF calls, DSNRLI, and the entry point for the SQL calls inserted by the coprocessor, DSNHLI, use the MVS “OS” linkage convention.  The most robust way to do this is to create a “typedef” for the function signatures used by each entry point, then use a “#pragma” to declare any function with these signatures as “OS” linkage convention.

    Because we will retrieve function pointers when we dynamically load these entry points, but the coprocessor, in particular, will insert static calls to the DSNHLI function, we use “#define's” to map the uppercase functions to the lowercase function pointers:

    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)

    We load the entry points dynamically as follows.  Note that when the interface is loaded dynamically, the DSNHLIR entry point should be used to process SQL calls:

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

    And release them to keep the “use count” accurate, before exiting the program:

    void release_RRSAF()
    {
    __rlse(dsnhli);
    __rlse(dsnrli);
    }
  6. Dealing with Errors from RRSAF

    RRSAF functions are documented here:
    https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/apsg/src/tpc/db2z_rrsafconnectionfunctions.html

    The general form of a RRSAF function call is:

    frc = DSNRLI(verb, required parameters, ..., optional parameters, ...)

    The verb is an 18-byte text buffer containing the name of the function to be invoked.  A call to DSNRLI always returns a “first return code”, which we capture in variable, frc.  If the parameter list is unable to be interpreted successfully, DSNRLI returns a value of 200.  Otherwise, the first optional parameter is a return code, followed by a reason code.  When we invoke a RRSAF function, we will always specify these optional parameters, as this is the only way to retrieve a reason code for a failed function call.  When the call to DSNRLI is complete, the return code parameter passed back will always match frc, unless frc = 200.  If frc is zero, the function call succeeded.

    If a function call fails, but frc is not 200, we can call the TRANSLATE verb to retrieve information about the failure.  Armed with the return code and reason code for a prior failure, TRANSLATE will set the SQLCODE, SQLSTATE and SQLERRM within a SQLCA structure to provide information about the failure.  However, the TRANSLATE function can only be called after a successful call to the IDENTIFY function, which initiates a connection to a DB2 subsystem.

    Our code to call the TRANSLATE function looks like the following.  We will use common code to display the SQLCA for RRSAF errors, and SQL errors later in the code:

    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();
    }
    }
    }
  7. Connecting to the DB2 Subsystem

    Connecting to DB2 using RRSAF is a 3-step process.

    First, we use the IDENTIFY function to “identify” the DB2 subsystem to which we want to connect.  This can be an explicit subsystem ID, or a “group attach” can be attempted.  We're going to keep it simple and specify an explicit subsystem id.  A successful IDENTIFY call returns 2 structures that contain information about the connected subsystem.  We won't examine that information here.  The interested reader is referred to another recipe for an exploration of this topic:
    https://developer.ibm.com/recipes/tutorials/what-rrsaf-identify-can-tell-you-about-an-active-db2-for-zos-subsystem/

    We will print out the return code and reason code parameters, but we can't call TRANSLATE if IDENTIFY fails, as noted above.  The other required parameters are set to zero:

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

    Next we need to SIGNON to the DB2 subsystem.  This establishes our current user ID as the ID that will be used to test privileges in DB2.  This function takes 3 required parameters.  The first is a correlation ID which is written to trace records and is also shown in the output of a DB2 DISPLAY THREAD operator command.  For example:

    -DBBG DIS THREAD(*)                                          
    DSNV401I -DBBG DISPLAY THREAD REPORT FOLLOWS -
    DSNV402I -DBBG ACTIVE THREADS - 943
    NAME ST A REQ ID AUTHID PLAN ASID TOKEN
    RRSAF T 20 RRSAMPLE MATTO MYPLAN 0066 525
    V437-WORKSTATION=RRSAF
    USERID=MATTO
    APPLICATION NAME=RRSAMPLE
    V442-CRTKN=RRSAMPLE

    The second parameter is an accounting token which is written to trace records.  We don't need to set this value.  The third parameter, if set to “COMMIT”, causes an accounting record to be written each time a COMMIT of a unit of work is taken.  Any other value defers accounting until the end of the program or a switch of the user ID.

    Our call to the SIGNON function looks like this:

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

    Last, we call CREATE_THREAD to allocate resources in DB2 to process our SQL requests.  We need to pass the name of the plan to which the package for our program is bound.  The collection name parameter is ignored because we are passing an explicit plan name.  We will specify “INITIAL” as the third parameter to prevent subsequent SIGNON calls from changing the user ID under which our SQL will execute – we don't need this capability.

    Our CREATE_THREAD call looks like this:

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

    There are calls required to clean up the resources allocated by CREATE_THREAD and IDENTIFY.  You will see these functions in the complete source code, when we tie it all together.

  8. Invoking SQL Statements

    Now we get to do what we came for – issue SQL statements.  I am not going to go into exhaustive detail here.  I assume you know how to code SQL into a high-level langauge application.  If not, here is a good starting point: http://www.ibm.com/developerworks/rational/library/embedded-sql-xl-c-cpp-zos/

    In this program, we are going to fetch the rows from a table and display what we find.

    In the usual way, we need to include the SQL Communications Area (SQLCA) to receive error and status information, declare the host variables that will send/receive values to/from table columns (we will include our DCLGEN'd C header to do this), and we need to declare a cursor to fetch the rows from the table.

    So, our SQL “preamble” looks like this:

    EXEC SQL INCLUDE SQLCA;

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

    EXEC SQL DECLARE C1 CURSOR FOR
    SELECT DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION
    FROM DSN8B10.DEPT
    ORDER BY DEPTNO;

    Now, our SQL code to display the contents of the table looks like this, supplemented by a “diagnose” function to spit out error information, if a SQL call fails:

    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 doSQL()
    {
    char deptname[37];
    char mgrno[7];
    char location[17];

    EXEC SQL OPEN C1;
    diagnose("OPEN CURSOR", TRUE);
    if (SQLCODE == 0) {
    printf("\n");
    printf("DPT DEPARTMENT NAME MGRNO ADP LOCATION \n");
    printf("--- ------------------------------------ ------ --- ----------------\n");
    while (SQLCODE == 0) {
    EXEC SQL FETCH C1
    INTO :DCLDEPT.DEPTNO,
    :DCLDEPT.DEPTNAME,
    :DCLDEPT.MGRNO :IDEPT[2],
    :DCLDEPT.ADMRDEPT,
    :DCLDEPT.LOCATION :IDEPT[4];
    diagnose("FETCH AT CURSOR", FALSE);
    if (SQLCODE == 0) {
    GRAB(deptname, DCLDEPT.DEPTNAME.DEPTNAME_data, 36);
    deptname[DCLDEPT.DEPTNAME.DEPTNAME_len] = NUL;
    if (IDEPT[2] < 0) {
    strcpy(mgrno, "?");
    }
    else {
    strcpy(mgrno, DCLDEPT.MGRNO);
    }
    if (IDEPT[4] < 0) {
    strcpy(location, "?");
    }
    else {
    strcpy(location, DCLDEPT.LOCATION);
    }
    printf("%-3s %-36s %-6s %-3s %-16s\n",
    DCLDEPT.DEPTNO, deptname, mgrno, DCLDEPT.ADMRDEPT, location);
    }
    }
    printf("\n");
    EXEC SQL CLOSE C1;
    diagnose("CLOSE CURSOR", TRUE);
    }

    EXEC SQL COMMIT;
    diagnose("COMMIT WORK", TRUE);
    }
  9. Tying It All Together

    Now all we need is a simple “mainline” to bring this all together – something like this:

    int main(int argc, char **argv)
    {
    char *ssid;

    /* check for command line parameter */
    if (argc != 2) {
    printf("Usage: rrssample DB2_ssid\n");
    return 12;
    }
    ssid = argv[1];

    if (!load_RRSAF()) {
    return 12;
    }

    if (identify(ssid)) {
    if (signon()) {
    if (create_thread()) {
    doSQL();
    terminate_thread();
    }
    }
    terminate_identify();
    }

    release_RRSAF();

    return 0;
    }

    Notice that our program takes a single command line parameter – the DB2 subsystem ID to which we want to connect.

    Now you can copy and paste the following complete program into a source file, “rrsample.c”, in your z/OS UNIX directory:

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

    /* #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_CORRELATION_ID "RRSAMPLE " /* 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 "dept.h"
    /* following DECLARE only needed because table data is UNICODE */
    EXEC SQL DECLARE :DCLDEPT VARIABLE CCSID 1047;
    EXEC SQL END DECLARE SECTION;

    EXEC SQL DECLARE C1 CURSOR FOR
    SELECT DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION
    FROM DSN8B10.DEPT
    ORDER BY DEPTNO;

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

    void doSQL()
    {
    char deptname[37];
    char mgrno[7];
    char location[17];

    EXEC SQL OPEN C1;
    diagnose("OPEN CURSOR", TRUE);
    if (SQLCODE == 0) {
    printf("\n");
    printf("DPT DEPARTMENT NAME MGRNO ADP LOCATION \n");
    printf("--- ------------------------------------ ------ --- ----------------\n");
    while (SQLCODE == 0) {
    EXEC SQL FETCH C1
    INTO :DCLDEPT.DEPTNO,
    :DCLDEPT.DEPTNAME,
    :DCLDEPT.MGRNO :IDEPT[2],
    :DCLDEPT.ADMRDEPT,
    :DCLDEPT.LOCATION :IDEPT[4];
    diagnose("FETCH AT CURSOR", FALSE);
    if (SQLCODE == 0) {
    GRAB(deptname, DCLDEPT.DEPTNAME.DEPTNAME_data, 36);
    deptname[DCLDEPT.DEPTNAME.DEPTNAME_len] = NUL;
    if (IDEPT[2] < 0) {
    strcpy(mgrno, "?");
    }
    else {
    strcpy(mgrno, DCLDEPT.MGRNO);
    }
    if (IDEPT[4] < 0) {
    strcpy(location, "?");
    }
    else {
    strcpy(location, DCLDEPT.LOCATION);
    }
    printf("%-3s %-36s %-6s %-3s %-16s\n",
    DCLDEPT.DEPTNO, deptname, mgrno, DCLDEPT.ADMRDEPT, location);
    }
    }
    printf("\n");
    EXEC SQL CLOSE C1;
    diagnose("CLOSE CURSOR", TRUE);
    }

    EXEC SQL COMMIT;
    diagnose("COMMIT WORK", TRUE);
    }

    int main(int argc, char **argv)
    {
    char *ssid;

    /* check for command line parameter */
    if (argc != 2) {
    printf("Usage: rrssample DB2_ssid\n");
    return 12;
    }
    ssid = argv[1];

    if (!load_RRSAF()) {
    return 12;
    }

    if (identify(ssid)) {
    if (signon()) {
    if (create_thread()) {
    doSQL();
    terminate_thread();
    }
    }
    terminate_identify();
    }

    release_RRSAF();

    return 0;
    }
  10. Building the Program

    We are going to compile our program using the “xlc” command, invoking its built-in SQL coprocessor.  However, xlc doesn't use the STEPLIB environment variable to locate the DB2 precompiler.  Instead, it needs a configuration file to specify any load libraries required for its operation.

    So, copy the default xlc configuration file into your current directory, as follows:

    $ cp /usr/lpp/cbclib/xlc/sample/xlc.cfg db2.cfg

    Now edit “db2.cfg” and change the last line of the file from:

              steplib           = NONE

    to:

              steplib           = DSNB10.DBBG.SDSNEXIT:DSNB10.SDSNLOAD

    Allocate a MVS partitioned dataset, with RECFM=F (or FB), LRECL=80 to hold the DB2 “DBRM” file generated by the SQL coprocessor, when the program is compiled. In what follows, I have used a PDS called “MATTO.DBRMLIB”.

    This is the point in the process where we get the “payback” for putting all the tools in place to work in the z/OS UNIX environment.  Now, create a file called “Makefile” to build the program.  Add the following lines to this file, adapting this Makefile to match your environment (MVS dataset names, in particular):

    rrsample: rrsample.c
    xlc -orrsample -qsql "-qdbrmlib=//'MATTO.DBRMLIB(RRSAMPLE)'" -Fdb2.cfg rrsample.c
    dsnuss DBBG "BIND PACKAGE(MYCOLL) MEMBER(RRSAMPLE) LIBRARY('MATTO.DBRMLIB')"
    dsnuss DBBG "BIND PLAN(MYPLAN) PKLIST(MYCOLL.RRSAMPLE)"

     *** Note: each of the 3 “rule” lines MUST start with a “tab” character, not 8 spaces.

    To build the program, issue the “make” command.  If all goes well, you should see something like this:

    $ make
    xlc -orrsample -qsql "-qdbrmlib=//'MATTO.DBRMLIB(RRSAMPLE)'" -Fdb2.cfg rrsample.c
    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(RRSAMPLE) LIBRARY('MATTO.DBRMLIB')"
    ex 'MATTO.EXEC(DSNUSS)' 'DBBG BIND PACKAGE(MYCOLL) MEMBER(RRSAMPLE) LIBRARY(''MATTO.DBRMLIB'')'
    DSNT254I  -DBBG DSNTBCM2 BIND OPTIONS FOR
               PACKAGE = DALLASB.MYCOLL.RRSAMPLE.()
               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.RRSAMPLE.()
               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.RRSAMPLE.()
               QUERYACCELERATION
               GETACCELARCHIVE
    DSNT232I  -DBBG SUCCESSFUL BIND FOR
    *** Success: found DSNT232I
               PACKAGE = DALLASB.MYCOLL.RRSAMPLE.()
    *** dsnuss rc = 0
    dsnuss DBBG "BIND PLAN(MYPLAN) PKLIST(MYCOLL.RRSAMPLE)"
    ex 'MATTO.EXEC(DSNUSS)' 'DBBG BIND PLAN(MYPLAN) PKLIST(MYCOLL.RRSAMPLE)'
    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
    $
    
  11. Run the Program

    Now we are ready to run the program.  Just provide a subsystem ID (in upper case) as a parameter.  This parameter is the subsystem ID of the DB2 subsystem to which you want the program to connect.

    You should see something like this:

    $ rrsample DBBG
    ***          OPEN CURSOR SUCCEEDED *** (sqlcode=0)
    
    DPT DEPARTMENT NAME                      MGRNO  ADP LOCATION
    --- ------------------------------------ ------ --- ----------------
    A00 SPIFFY COMPUTER SERVICE DIV.         000010 A00
    B01 PLANNING                             000020 A00
    C01 INFORMATION CENTER                   000030 A00
    D01 DEVELOPMENT CENTER                   ?      A00
    D11 MANUFACTURING SYSTEMS                000060 D01
    D21 ADMINISTRATION SYSTEMS               000070 D01
    E01 SUPPORT SERVICES                     000050 A00
    E11 OPERATIONS                           000090 E01
    E21 SOFTWARE SUPPORT                     000100 E01
    F22 BRANCH OFFICE F2                     ?      E01
    G22 BRANCH OFFICE G2                     ?      E01
    H22 BRANCH OFFICE H2                     ?      E01
    I22 BRANCH OFFICE I2                     ?      E01
    J22 BRANCH OFFICE J2                     ?      E01
    
    ***         CLOSE CURSOR SUCCEEDED *** (sqlcode=0)
    ***          COMMIT WORK SUCCEEDED *** (sqlcode=0)
    $

Join The Discussion