IBM DB2 for i: Code example

Blocked insert examples (ODBC)

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.

Contact an IBM Sales Specialist

Browse Power Systems

Next generation applications for big data and analytics and cognitive computing are providing unprecedented insights into opportunities, threats and efficiencies. IBM Power Systems is at the forefront of delivering solutions to gain faster insights from analyzing both structured information and unstructured big data. With the secure, flexible and open platform of IBM Power Systems plus solutions and software, organizations can outpace their competitors by delivering faster services, providing differentiated offerings and turning operational cost into investment opportunity.

To draw insights and make better decisions, businesses rely on the secure, flexible and open platform of IBM Power Systems. Built with the first processor designed for big data workloads, the design of Power Systems combines the computing power, memory bandwidth and I/O in ways that are easier to consume and manage, building on strong resiliency, availability and security.

IBM Power Systems deliver flexibility and choice of operating systems to enable your business to support the next generation applications for big data and analytics and cognitive computing that are transforming how organizations work today. Whether running 1, 2, or all 3 - coupled with PowerVM, they maximize the benefit of Power Systems in your business.

Transform your business with Systems Software that enables virtualization, high availability, flexibility, security and compliance on Power Systems™. IBM’s integrated approach to developing Systems and Systems Software together delivers optimized results with Power Systems.

As an open innovation platform, Power Systems is optimized for big data and analytics performance and to deliver scale-out economics and security for the cloud. IBM and IBM Business Partner solutions exploit key capabilities in IBM Power Systems.

Over the last five years thousands of clients have migrated to IBM Power Systems for choice and flexibility. Learn how Power Systems has helped them revolutionise the way IT is developed and delivered, optimise for big data and analytics, and support private, public and hybrid offerings for scale-out or scale-up implementations all while improving business performance, reducing risk, and establishing a platform for growth.

Additional information