|
The following are coding examples (implemented with C & Visual Basic) demonstraing one technique for performing blocked inserts with the IBM iSeries Access for Windows ODBC driver. The DB2 UDB for iSeries 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 UDB for iSeries programming interfaces, go to the System i5 Infocenter.
Examples: 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.
=================================================
=================================================
The next example is a Visual Basic block insert that is significantly faster than a "parameterized" insert.
|
|
|
