IBM DB2 for i: Code example

This C programming example demonstrates one technique for performing blocked inserts with the IBM i Access for Windows ODBC driver. A Visual Basic programming example is also avalable. A Visual Basic blocked insert is signficantly faster than a parameterized Insert.

The DB2 for i SQL CLI (Call Level Interface) also supports blocked inserts, click here for details on CLI blocked inserts. For more details on blocked inserts and other DB2 for i programming interfaces, go to the IBM i Info Center.

Example: Blocked insert calls from C

//     Block insert variables and statements
            // variable declares
    HSTMT s_Ordlin1;                   // Order Line Insert.
    UDWORD rowcnt;                     // for Blocked insert.
    long   lOrderNum[15];
    // array of order numbers for blocked insert
    UINT   uiDistrict[15];
    // District Number (from TxnLeader).
    char   szWid[15][5];       // Warehouse id.
    UINT   uiOl_ctr[15];       // Order Line Counter.
    char   szSplyWid[15][5];   // Supply warehouse id.
    char   szIid[15][7];       // Item id
    char   szOlAmt[15][10];
    // $ amount for ordered item (7,2)
    UINT   uiZero [15];        // delivery date & time
    UINT   uiOlQty[15];        // Quantity Ordered - for 
                               // non-numeric.
    char   szDistrctInfo[15][25];  //District info.
 
    UINT    m_Warehouse;      // Member copy of the warehouse #.
    long    m_OrderNum;       // Order Number.
    UINT    m_District;       // District Number (from TxnLeader).
    Order_Line  m_OrdLines[16];
     // Array for interface
 
   // Prepare the block insert statement
     Strcpy(tmpbfr,"INSERT INTO library/ORDLIN ");
     strcat(tmpbfr," VALUES (?,?,?,?,?,?,?,?,?,?,?)");
 
  ret= SQLPrepare(s_Ordlin1,(unsigned char far *)tmpbfr,SQL_NTS);
  // Set the parameters. This table has 11 columns.
 
  ret=SQLBindParameter(s_Ordlin1, 1,SQL_PARAM_INPUT,SQL_C_LONG,
  SQL_DECIMAL,9,0,lOrderNum,0,NULL);         //order id

  ret=SQLBindParameter(s_Ordlin1, 2,SQL_PARAM_INPUT,SQL_C_SHORT,
  SQL_DECIMAL,3,0,uiDistrict,0,NULL);        //district id
 
  ret=SQLBindParameter(s_Ordlin1, 3,SQL_PARAM_INPUT,SQL_C_CHAR,
  SQL_CHAR,4,0,szWid[0],5,NULL);   //warehouse id
 
  ret=SQLBindParameter(s_Ordlin1, 4,SQL_PARAM_INPUT,SQL_C_SHORT,
  SQL_DECIMAL,3,0,&uiOI_ctr,0,NULL);     //number of order lines
 
  ret=SQLBindParameter(s_Ordlin1, 5,SQL_PARAM_INPUT,SQL_C_CHAR,
  SQL_CHAR,4,0,szSplyWid,5,NULL);        //supplying wrhs id
 
  ret=SQLBindParameter(s_Ordlin1, 6,SQL_PARAM_INPUT,SQL_C_CHAR,

  SQL_CHAR,6,0,szIid,7,NULL);                    //item id
 
  ret=SQLBindParameter(s_Ordlin1, 7,SQL_PARAM_INPUT,SQL_C_SHORT,

  SQL_NUMERIC,3,0,uiOlQty,0,NULL);                  //quantity
 
  ret=SQLBindParameter(s_Ordlin1, 8,SQL_PARAM_INPUT,SQL_C_CHAR,

  SQL_NUMERIC,7,2,szOlAmt,10,NULL);  //A float for $ amount

                                     //or purchase
 
  ret=SQLBindParameter(s_Ordlin1, 9,SQL_PARAM_INPUT,SQL_C_SHORT,

  SQL_NUMERIC,8,0,uiZero,0,NULL); //Delivery date  (set to zero)
 
  ret=SQLBindParameter(s_Ordlin1,10,SQL_PARAM_INPUT,SQL_C_SHORT,

  SQL_NUMERIC,6,0,uiZero,0,NULL); //Delivery time  (set to zero)
 
  ret=SQLBindParameter(s_Ordlin1,11,SQL_PARAM_INPUT,SQL_C_CHAR,

  SQL_CHAR,24,0,szDistrctInfo,25,NULL);//District data waste space
 
  //********************  Insert into ORDLIN  ****************
   rowcnt = 0;
 
   // m_OrderCount will contain the number of rows to insert
   // rowcnt is a pointer to keep track of current row number
      ret=SQLParamOptions(s_Ordlin1, NewOrd_IO-> m_OrderCount,

      &rowcnt);
 
   // Fill the parameters for the Blocked Insert by moving the
   // data from the input array to the parameters.

   for(ol_ctr=1;ol_ctr<=NewOrd_IO->m_OrderCount;ol_ctr++)
  {
    lOrderNum[ol_ctr-1] =  NewOrd_IO->m_OrderNum;
    uiDistrict[ol_ctr-1] = NewOrd_IO->m_District;
    sprintf(s_parm[3],"%04u",NewOrd_IO->m_Warehouse);
    strcpy(szWid[ol_ctr-1],s_parm[3]);
    uiOl_ctr[ol_ctr-1] = ol_ctr;
    sprintf(s_parm[5],"%04u",*NewOrd_IO->m_OrdLines
    [ol_ctr].Supp_W);
    strcpy(szSplyWid[ol_ctr-1], s_parm[5]);
    sprintf(s_parm[6],"%06ld",*NewOrd_IO->m_OrdLines
    [ol_ctr].Item_ID);
    strcpy(szIid[ol_ctr-1], s_parm[6]);
    uiOlQty[ol_ctr-1] = *NewOrd_IO->m_OrdLines
    [ol_ctr].Qty;
    sprintf(s_parm[8],"%+09.2f",*NewOrd_IO->m_OrdLines
    [ol_ctr].Amount);
    strcpy(szOlAmt[ol_ctr-1], s_parm[8]);
    sprintf(s_parm[13],"%s",txttemp[
    NewOrd_IO->m_District]);
    strcpy(szDistrctInfo[ol_ctr-1], s_parm[13]);
   }   // end of for loop
 
  ret=SQLExecute(s_Ordlin1);        // Execute Order Line insert.