Loading a Db2 for z/OS table by using a client CLI program

The DRDA fast LOAD process, which is also known as zLoad, can be used to execute a Db2 for z/OS LOAD utility statement from a client program. You can write a client CLI program to do that.

Procedure

To load a Db2 for z/OS® from a client CLI application, follow these steps:

Invoke the SQLSetStmtAttr function to set values for the following attributes:

SQL_ATTR_DB2ZLOAD_LOADSTMT

The text of the LOAD control statement.

SQL_ATTR_DB2ZLOAD_UTILITYID

The utility ID, which is a unique identifier that you can set so that you can identify a particular LOAD statement invocation. Setting this attribute is optional.
Allocate a buffer for the data that is to be loaded.
Invoke the SQLSetStmtAttr function to set the SQL_ATTR_DB2ZLOAD_BEGIN attribute to indicate to the CLI driver that the LOAD operation is to begin.
Invoke the SQLPutData function one or more times to send the data that is to be loaded to the CLI driver.
When all the data has been sent to the driver, invoke the SQLSetStmtAttr function to set the SQL_ATTR_DB2ZLOAD_END attribute. That attribute indicates to the CLI driver that the LOAD operation is complete.

Example

The following code fragment demonstrates using the DRDA fast load process to load data from file customer.data into table MYID.CUSTOMER_DATA.

This code fragment uses the STMT_HANDLE_CHECK macro. STMT_HANDLE_CHECK is in utilcli.h, which is shipped with Db2 for Linux®, UNIX, and Windows. For information on STMT_HANDLE_CHECK and the utility functions that it invokes, see the following topics:


#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlcli1.h>
#include "utilcli.h"   /* Header file for CLI sample code */
#include <sqlca.h>

int main(int argc, char * argv[])
{
  SQLRETURN cliRC = SQL_SUCCESS;
  int rc = 0;
  SQLHANDLE henv;                  /* Environment handle  */
  SQLHANDLE hdbc;                  /* Connection handle   */
  SQLHANDLE hstmt;                 /* Statement handle    */
  SQLCHAR loadStmt[2000];          /* LOAD statement text */
  SQLINTEGER loadStmtLen = 0;   /* LOAD statement length  */
  SQLCHAR loadFileName[200] = "customer.data";
                               /* Name of file with data  */
                               /* to be loaded            */
  FILE* pFile;             /* File handle for LOAD input  */
  size_t lSize = 0;        /* Input file size             */
  SQLLEN iPutDataSize = 0; /* LOAD input buffer size      */
  SQLCHAR *pBuffer = NULL; /* LOAD input buffer pointer   */
  SQLINTEGER Retcode = 0;
  SQLSMALLINT iStrLen = 0;
  SQLCHAR Msgbuff[3000];   /* LOAD messages buffer        */ 
  ...
  /********************************************************/
  /* Allocate a statement handle.                         */
  /********************************************************/
  cliRC = SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);
  DBC_HANDLE_CHECK(hdbc, cliRC);
  /********************************************************/
  /* Assign the LOAD control statement to the loadStmt    */
  /* variable.                                            */
  /********************************************************/
  sprintf((char*)loadStmt, 
   (char*)"TEMPLATE SORTIN DSN &JO..&ST..SORTIN.T&TIME.  " +
   "SPACE(10,10) CYL DISP(NEW,DELETE,DELETE) TEMPLATE SORTOUT " +
   "DSN &JO..&ST..SORTOUT.T&TIME.  SPACE(10,10) CYL " +
   "DISP(NEW,DELETE,DELETE) LOAD DATA INDDN SYSCLIEN " +
   "WORKDDN(SORTIN,SORTOUT) REPLACE PREFORMAT LOG(NO) " +
   "REUSE NOCOPYPEND FORMAT DELIMITED EBCDIC " +
   "INTO TABLE MYID.CUSTOMER_DATA NUMRECS 30000");
  loadStmtLen = strlen((char*)loadStmt);
  /********************************************************/
  /* Open file block.cust.del, which contains the data to */
  /* be loaded, and associate it with file handle pfile.  */
  /********************************************************/
  pFile = fopen((char*)loadFileName, "rb" );
  if ( pFile == NULL)
  {
    printf("Error allocating LOAD input file\n");
    return -1;
  }
  /********************************************************/
  /* Set the pointer to the end of the file.              */
  /********************************************************/
  cliRC = fseek(pFile , 0 , SEEK_END);
  if ( cliRC )
  {
    printf("Cannot set the file pointer to the end of the file\n");
    return -1;
  }
  /********************************************************/
  /* Get the current position in the file, which is the   */
  /* file size.                                           */
  /********************************************************/
  lSize = ftell (pFile);
  if ( lSize <= 0 )
  {
    printf("Cannot get the size of the file\n");
    return -1;
  }
  /********************************************************/
  /* Set the pointer to the beginning of the file.        */
  /********************************************************/
  cliRC = fseek( pFile, 0L, SEEK_SET );
  if ( cliRC )
  {
    printf("Cannot set the file pointer to the beginning of the file\n");
    return -1;
  }
  /********************************************************/   
  /* Allocate an input buffer. For this example, the      */
  /* buffer size is half the file size.                   */
  /********************************************************/
  iPutDataSize = ( lSize / 2);
  pBuffer = (SQLCHAR*)malloc( (iPutDataSize+1));
  /********************************************************/   
  /* Read a block of data from the file into the buffer.  */
  /********************************************************/
  fread(pBuffer, 1, iPutDataSize, pFile); 
  /********************************************************/
  /* Set attributes for doing a zLoad operation.          */
  /********************************************************/
  /********************************************************/
  /* Set statement attribute SQL_ATTR_DB2ZLOAD_LOADSTMT   */
  /* to a pointer to the loadStmt variable.               */
  /********************************************************/
  cliRC  = SQLSetStmtAttr(hstmt, 
   (SQLINTEGER)SQL_ATTR_DB2ZLOAD_LOADSTMT, 
   (SQLPOINTER)loadStmt, (SQLINTEGER)loadStmtLen);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* Check result  */
  /********************************************************/
  /* Set statement attribute SQL_ATTR_DB2ZLOAD_BEGIN to   */
  /* SQL_TRUE, to indicate that loading data is to start. */
  /********************************************************/
  cliRC = SQLSetStmtAttr(hstmt, 
   (SQLINTEGER)SQL_ATTR_DB2ZLOAD_BEGIN,
   (SQLPOINTER)SQL_TRUE, SQL_IS_INTEGER);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* Check result  */
  /********************************************************/
  /* Send the data in the input buffer to the CLI driver. */
  /********************************************************/
  cliRC = SQLPutData(hstmt, pBuffer, iPutDataSize);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* Check result  */
  /********************************************************/
  /* Move the input file pointer to the next block of     */
  /* data                                                 */
  /********************************************************/
  if ( lSize % 2 == 1)
  {
    iPutDataSize = iPutDataSize +1;
  }
  /********************************************************/
  /* Read the other half of the data.                     */
  /********************************************************/
  fread(pBuffer, 1, iPutDataSize, pFile); 
  /********************************************************/
  /* Send the remaining data to the CLI driver.           */
  /********************************************************/
  cliRC = SQLPutData(hstmt, pBuffer, iPutDataSize);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
  /********************************************************/    
  /* Set the statement attribute to SQL_TRUE, to indicate */
  /* that loading data is to end.                         */
  /********************************************************/
  cliRC = SQLSetStmtAttr(hstmt,
   (SQLINTEGER)SQL_ATTR_DB2ZLOAD_END,
   (SQLPOINTER)SQL_TRUE, SQL_IS_INTEGER);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC); /* Check result  */
  /********************************************************/
  /* Close the input file                                 */
  /********************************************************/
  fclose(pFile);
  /********************************************************/
  /* Retrieve the return code from the zLoad.             */
  /********************************************************/
  cliRC = SQLGetDiagField( SQL_HANDLE_STMT, hstmt, 1, 
    SQL_DIAG_DB2ZLOAD_RETCODE, &Retcode, 
    SQL_IS_INTEGER, NULL);
  /********************************************************/
  /* Retrieve the messages from the zLoad.                */
  /********************************************************/
  cliRC = SQLGetDiagField( SQL_HANDLE_STMT, hstmt, 1, 
    SQL_DIAG_DB2ZLOAD_LOAD_MSGS, (SQLPOINTER)Msgbuff, 3000, 
    &iStrLen);
  ...
}