Overview

Skill Level: Any Skill Level

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

This recipe provides a step-by-step guide to creating a C program that leverages the ODBC API of DB2 for z/OS. 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. Introduction

    When writing an application that accesses DB2 for z/OS, part of the “build” process traditionally involves “binding” your application to the target database subsystem.  This extra step creates a “package” in the database which associates your executable program with the DB2 actions that will be conducted in the database subsystem on its behalf.  Programming languages that are spared this inconvenience include REXX (via the DSNREXX interface) and Java (via JDBC).

    For C/C++ programmers, DB2 for z/OS provides an ODBC API, which does not require that you “bind” your application to the database.  You may be familiar with ODBC in a Microsoft Windows context, but it is in fact an “open” API, supported by a number fo database vendors on a variety of platforms, including mainframe.

    For this exercise, you will need a DB2 for z/OS subsystem to which you have CONNECT privilege.  You will also need SELECT access to a table in that subsystem.  In what follows, I use a sample table shipped with DB2, DSN8B10.EMP, 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 library for your version of DB2 is 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.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.

    A detailed description of the ODBC API can be found in the DB2 for z/OS Knowledge Center:
    https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/odbc/src/tpc/db2z_hdint.html

  2. Handles

    ODBC uses handles to represent the database driver (referred to as the “environment”), database connection and SQL statement(s) in your application.  Allocating these handles and connecting to the database is a straightforward process, usually performed in the following sequence:

    • Allocate an environment handle
    • Allocate a database connection handle
    • Connect to the database
    • Allocate a statement handle

    In the case of a z/OS application communicating with a local DB2 subsystem, the “database name” to which you connect is the DDF location name.  You can find the location name for your DB2 subsystem by issuing a “DISPLAY DDF” operator command against the DB2 subsystem, for example:

    -DBBG DISPLAY DDF                                              
    DSNL080I -DBBG DSNLTDDF DISPLAY DDF REPORT FOLLOWS: 801
    DSNL081I STATUS=STARTD
    DSNL082I LOCATION LUNAME GENERICLU
    DSNL083I DALLASB NETD.DBBGLU1 -NONE
    DSNL084I TCPPORT=5035 SECPORT=5037 RESPORT=5036 IPNAME=-NONE
    DSNL085I IPADDR=::192.168.0.61
    DSNL086I SQL DOMAIN=S0W1.DAL-EBIS.IHOST.COM
    DSNL105I CURRENT DDF OPTIONS ARE:
    DSNL106I PKGREL = COMMIT
    DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE

    When your program no longer needs a connection to the database, it should release the above handles, in the following sequence:

    • Free the statement handle
    • Disconnect from the database
    • Free the database connection handle
    • Free the environment handle

    Create a z/OS UNIX file, odbcsamp.c, to hold the source code of our sample application.  Edit this file (using ‘vi’ or ‘oedit’) and insert the following code to manage handles.  The diagnose() function will be discussed later and you will see how these functions are called when we tie all the steps together into a main() function.

    int allocEnv(SQLHANDLE FAR *phenv)
    {
    SQLRETURN rc;

    rc = SQLAllocHandle(
    SQL_HANDLE_ENV,
    SQL_NULL_HANDLE,
    phenv);
    diagnose(rc, SQL_HANDLE_ENV, SQL_NULL_HANDLE, "allocEnv", "SQLAllocHandle");

    return (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO);
    }

    int allocDbc(SQLHANDLE henv, SQLHANDLE FAR *phdbc)
    {
    SQLRETURN rc;

    rc = SQLAllocHandle(
    SQL_HANDLE_DBC,
    henv,
    phdbc);
    diagnose(rc, SQL_HANDLE_ENV, henv, "allocDbc", "SQLAllocHandle");

    return (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO);
    }

    int allocStmt(SQLHANDLE hdbc, SQLHANDLE FAR *phstmt)
    {
    SQLRETURN rc;

    rc = SQLAllocHandle(
    SQL_HANDLE_STMT,
    hdbc,
    phstmt);
    diagnose(rc, SQL_HANDLE_DBC, hdbc, "allocStmt", "SQLAllocHandle");

    return (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO);
    }

    int connect(SQLHANDLE hdbc, SQLCHAR FAR *location)
    {
    SQLRETURN rc;

    rc = SQLConnect(
    hdbc,
    location,
    SQL_NTS,
    NULL, /* use primary user ID of running application */
    SQL_NTS,
    NULL,
    SQL_NTS);
    diagnose(rc, SQL_HANDLE_DBC, hdbc, "connect", "SQLConnect");

    return (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO);
    }

    int disconnect(SQLHANDLE hdbc)
    {
    SQLRETURN rc;

    rc = SQLDisconnect(
    hdbc);
    diagnose(rc, SQL_HANDLE_DBC, hdbc, "disconnect", "SQLDisconnect");

    return (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO);
    }

    int freeStmt(SQLHANDLE hstmt)
    {
    SQLRETURN rc;

    rc = SQLFreeHandle(
    SQL_HANDLE_STMT,
    hstmt);
    diagnose(rc, SQL_HANDLE_STMT, hstmt, "freeStmt", "SQLFreeHandle");

    return (rc == SQL_SUCCESS);
    }

    int freeDbc(SQLHANDLE hdbc)
    {
    SQLRETURN rc;

    rc = SQLFreeHandle(
    SQL_HANDLE_DBC,
    hdbc);
    diagnose(rc, SQL_HANDLE_DBC, hdbc, "freeDbc", "SQLFreeHandle");

    return (rc == SQL_SUCCESS);
    }

    int freeEnv(SQLHANDLE henv)
    {
    SQLRETURN rc;

    rc = SQLFreeHandle(
    SQL_HANDLE_ENV,
    henv);
    diagnose(rc, SQL_HANDLE_ENV, henv, "freeEnv", "SQLFreeHandle");

    return (rc == SQL_SUCCESS);
    }
  3. Errors

    Our program can encounter two types of errors: errors recognised by the ODBC API, and errors recognised by the DB2 subsystem.  The “diagnose” function below ignores “successes” (which may include retrieving a vallue for a table column where the returned value is truncated, because the buffer provided to receive the value is not big enough).  The function produces a specific message for the “no (more) rows” warning (ie. sqlcode = 100).

    For any errors where we can pass a valid handle, ODBC provides a SQLGetDiagRec function which returns one or more records of explanatory text for the error (see: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/odbc/src/tpc/db2z_fngetdiagrec.html )

    Insert the following code at the top of the source file.  Set the “#define DDF_LOCATION” to the appropriate DDF location name for your DB2 subsystem.

    #include <stdio.h>
    #include <string.h>
    #include <sqlcli1.h>

    #define DDF_LOCATION "DALLASB"

    void diagnose(
    SQLRETURN rc,
    SQLSMALLINT handleType,
    SQLHANDLE handle,
    SQLCHAR FAR *function,
    SQLCHAR FAR *method)
    {
    SQLCHAR buffer[SQL_MAX_MESSAGE_LENGTH + 1];
    SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1];
    SQLINTEGER sqlcode;
    SQLSMALLINT i;
    SQLINTEGER nativeError;
    SQLSMALLINT msgSize;
    SQLRETURN src;

    /* no major problem */
    if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
    return;

    /* no (more) records */
    if (rc == SQL_NO_DATA_FOUND) {
    printf("Warning: No (more) records in result set\n");
    return;
    }

    printf("ERROR occurred in %s() calling %s()\n", function, method);
    switch (rc) {
    case SQL_ERROR:
    printf("Operation returned SQL error (rc=%d)\n", rc);
    break;
    case SQL_INVALID_HANDLE:
    printf("Operation returned invalid handle (rc=%d)\n", rc);
    break;
    default:
    printf("Operation returned rc=%d\n", rc);
    break;
    }

    if (handle != SQL_NULL_HANDLE) {
    src = SQL_SUCCESS;
    printf("Additional diagnotic information:\n");
    for (i = 1; (src == SQL_SUCCESS || src == SQL_SUCCESS_WITH_INFO); i++) {
    src = SQLGetDiagRec(handleType, handle, i, sqlstate, &nativeError, buffer, sizeof(buffer), &msgSize);
    if (src == SQL_SUCCESS || src == SQL_SUCCESS_WITH_INFO) {
    buffer[msgSize] = '\0';
    printf("%s\n", buffer);
    }
    }
    }
    }
  4. Execute

    Now we need a function to pass an SQL statement to the DB2 subsystem for execution.  ODBC provides a SQLExecDirect function for this purpose.  It prepares the statement and executes it in a single step.  Add the following code to the end of odbcsamp.c:

    int execute(SQLHANDLE hstmt, SQLCHAR FAR *command)
    {
    SQLRETURN rc;

    rc = SQLExecDirect(
    hstmt,
    command,
    SQL_NTS);
    diagnose(rc, SQL_HANDLE_STMT, hstmt, "execute", "SQLExecDirect");

    return (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO);
    }
  5. Bind

    I said there was no “bind”, but I lied.  We need to give ODBC the address of buffers/variables to receive data (i.e. column values) returned from our SQL statement, if appropriate.  In our sample program, we will issue a SQL SELECT, so we need to “bind” C variables to receive the column values returned for each row retrieved from the database.  ODBC provides a SQLBindCol function for this purpose.  For each column in the result set, you pass it the column position (1, 2, …), the C datatype of the variable to receive the value and a pointer to the C variable.

    Add the following code to the end of odbcsamp.c:

    int bindCol(
    SQLHANDLE hstmt,
    SQLUSMALLINT icol,
    SQLSMALLINT ctype,
    SQLPOINTER pvalue,
    SQLINTEGER maxlen,
    SQLINTEGER FAR *plen)
    {
    SQLRETURN rc;

    rc = SQLBindCol(
    hstmt,
    icol,
    ctype,
    pvalue,
    maxlen,
    plen);
    diagnose(rc, SQL_HANDLE_STMT, hstmt, "bind", "SQLBindCol");

    return (rc == SQL_SUCCESS);
    }
  6. Fetch

    Using SQLExecDirect to execute a SQL statement causes the statement to be executed in the DB2 subsystem, but doesn’t return the result set from a SELECT, for example.  To access the result set, having “bound” the variables that will receive the column values, we need to issue a SQLFetch call to retrieve each row.

    Add the following code to the end of odbcsamp.c:

    int fetchRow(SQLHANDLE hstmt)
    {
    SQLRETURN rc;

    rc = SQLFetch(hstmt);
    diagnose(rc, SQL_HANDLE_STMT, hstmt, "fetch", "SQLFetch");

    return rc;
    }
  7. Issue a SELECT and display the rows returned

    Now we have all the machinery we need to issue a SQL command and display the result set it returns.  If we issue the following SELECT statement, we expect multiple rows to be returned, each with two columns, being a string and a decimal value, respectively.

    SELECT LASTNAME, SALARY FROM DSN8B10.EMP ORDER BY 1 

    Add the following code to the end of odbcsamp.c to execute this SELECT statement and retrieve the result set:

    void run(SQLHANDLE hstmt)
    {
    SQLRETURN rc;
    SQLCHAR lastname[16];
    SQLINTEGER lastnameLen;
    double salary;

    if (execute(hstmt, "SELECT LASTNAME, SALARY FROM DSN8B10.EMP ORDER BY 1")) {
    if (bindCol(hstmt, 1, SQL_C_CHAR, lastname, sizeof(lastname), &lastnameLen)) {
    if (bindCol(hstmt, 2, SQL_C_DOUBLE, &salary, 0, NULL)) {
    rc = SQL_SUCCESS;
    printf("Last Name Salary\n");
    printf("--------------- ---------\n");
    while (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO) {
    rc = fetchRow(hstmt);
    if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO) {
    lastname[lastnameLen] = '\0';
    printf("%-15s %9.2f\n", lastname, salary);
    }
    }
    }
    }
    }
    }

    Note that if the returned column value is a string, as in the case of LASTNAME in our example, you should also specify the maximum length of your buffer that receives the value, and you should also provide a “length” variable that ODBC can set to the length of the string that it returns. Note also that ODBC does not always null-terminate the string value it returns, so your code should do this, before accessing the string value.

  8. Tie it all together

    Now we can write our main() program to allocate the necessary handles, connect to the database, run our SQL, then clean up.

    Add the following code to the end of odbcsamp.c, then save the file and exit the editor:

    int main()
    {
    SQLHANDLE henv, hdbc, hstmt;

    if (allocEnv(&henv)) {
    if (allocDbc(henv, &hdbc)) {
    if (connect(hdbc, DDF_LOCATION)) {
    if (allocStmt(hdbc, &hstmt)) {
    run(hstmt);
    freeStmt(hstmt);
    }
    disconnect(hdbc);
    }
    freeDbc(hdbc);
    }
    freeEnv(henv);
    }
    }
  9. Build the executable

    From the HFS directory where you saved odbcsamp.c, issue the following command to compile the program into an executable:

    xlc -oodbcsamp -I "//'DSNB10.SDSNC.H'" -qdll odbcsamp.c "//'DSNB10.SDSNC.EXP(DSNAOCLI)'"

    Note that DSNB10.SDSNC.EXP is an alias to DSNB10.SDSNMACS.  If this alias does not exist on your system, you may need the assistance of a systems programmer to create the alias for you.

  10. Identify a default DB2 subsystem

    For ODBC to allocate an “environment”, it needs to be able to identify a default DB2 subsystem.  This can be any subsystem in the LPAR where your executable will run.  One way to do this is to include a load library containing a DSNHDECP module in your STEPLIB.  For example:

    export STEPLIB=DSNB10.DBBG.SDSNEXIT:DSNB10.SDSNLOAD

    The other method is to create an “initialization file” for ODBC and point to it with the DSNAOINI environment variable.  Let’s give this a try!  Create a HFS file called odbc.ini and add the following lines to this file (where DBBG is replaced with the name of an appropriate DB2 subsystem for your environment):

    [COMMON] 
    MVSDEFAULTSSID=DBBG

    Now make this file known to ODBC by issuing the following z/OS UNIX command:

    export DSNAOINI="./odbc.ini"
  11. Run the program

    We’re now ready to run the executable, which should produce output like the following:

    $ ./odbcsamp
    Last Name Salary
    --------------- ---------
    ADAMSON 25280.00
    BROWN 27740.00
    GEYER 40175.00
    GOUNOT 23840.00
    HAAS 52750.00
    HENDERSON 29750.00
    JEFFERSON 22180.00
    JOHNSON 17250.00
    JONES 18270.00
    KWAN 38250.00
    LEE 25370.00
    LUCCHESI 46500.00
    LUTZ 29840.00
    MARINO 28760.00
    MEHTA 19950.00
    NICHOLLS 28420.00
    O'CONNELL 29250.00
    PARKER 15340.00
    PEREZ 27380.00
    PIANKA 22250.00
    PULASKI 36170.00
    QUINTANA 23800.00
    SCHNEIDER 26250.00
    SCOUTTEN 21340.00
    SETRIGHT 15900.00
    SMITH 17750.00
    SMITH 19180.00
    SPENSER 26150.00
    STERN 32250.00
    THOMPSON 41250.00
    WALKER 20450.00
    YOSHIMURA 24680.00
    Warning: No (more) records in result set
    $

    And there you have it – a C application accessing DB2 with no BIND!

Join The Discussion