96 lines
5.4 KiB
HTML
96 lines
5.4 KiB
HTML
|
<?xml version="1.0" encoding="UTF-8"?>
|
||
|
|
<!DOCTYPE html
|
||
|
|
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
||
|
|
<html lang="en-us" xml:lang="en-us">
|
||
|
|
<head>
|
||
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
|
|
<meta name="security" content="public" />
|
||
|
|
<meta name="Robots" content="index,follow" />
|
||
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
||
|
|
<meta name="DC.Type" content="reference" />
|
||
|
|
<meta name="DC.Title" content="ODBC blocked insert statement" />
|
||
|
|
<meta name="abstract" content="The blocked INSERT statement provides a means to insert multiple rows with a single SQLExecute request. For performance, it provides the one of the best ways to populate a table, at times providing a tenfold performance improvement over the next best method." />
|
||
|
|
<meta name="description" content="The blocked INSERT statement provides a means to insert multiple rows with a single SQLExecute request. For performance, it provides the one of the best ways to populate a table, at times providing a tenfold performance improvement over the next best method." />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzaikodbcperfconsd.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1999, 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1999, 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="blockedinserts" />
|
||
|
|
<meta name="DC.Language" content="en-us" />
|
||
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
||
|
|
<!-- US Government Users Restricted Rights -->
|
||
|
|
<!-- Use, duplication or disclosure restricted by -->
|
||
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
||
|
|
<title>ODBC blocked insert statement</title>
|
||
|
|
</head>
|
||
|
|
<body id="blockedinserts"><a name="blockedinserts"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">ODBC blocked insert statement</h1>
|
||
|
|
<div><p>The blocked <strong>INSERT</strong> statement provides a means to insert
|
||
|
|
multiple rows with a single <strong>SQLExecute</strong> request. For performance, it
|
||
|
|
provides the one of the best ways to populate a table, at times providing
|
||
|
|
a tenfold performance improvement over the next best method.</p>
|
||
|
|
<div class="section"><p>The three forms of INSERT statements that can be executed from
|
||
|
|
ODBC are: </p>
|
||
|
|
<ul><li>INSERT with VALUES using constants</li>
|
||
|
|
<li>INSERT with VALUES using parameter markers</li>
|
||
|
|
<li>blocked INSERT</li>
|
||
|
|
</ul>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>The INSERT with VALUES using constants statement is the least
|
||
|
|
efficient method of performing inserts. For each request, a single INSERT
|
||
|
|
statement is sent to the server where it is prepared, the underlying table
|
||
|
|
is opened, and the record is written.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>Example: </p>
|
||
|
|
<pre>
|
||
|
|
INSERT INTO TEST.TABLE1 VALUES('ENGINEERING',10,'JONES','BOB')
|
||
|
|
</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>The INSERT with VALUES using parameter markers statement performs
|
||
|
|
better than the statement that uses constants. This form of the INSERT statement
|
||
|
|
allows for the statement to be prepared only once and then reused on subsequent
|
||
|
|
executions of the statement. It also allows the table on the server to remain
|
||
|
|
open, thus removing the overhead of opening and closing the file for each
|
||
|
|
insert.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>Example: </p>
|
||
|
|
<pre>
|
||
|
|
INSERT INTO TEST.TABLE1 VALUES (?, ?, ?, ?)
|
||
|
|
</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>The blocked INSERT statement most efficiently performs inserts
|
||
|
|
into a table when multiple records can be cached on the client and sent at
|
||
|
|
once. The advantages with blocked INSERT are: </p>
|
||
|
|
<ul><li>The data for multiple rows is sent in one communication request rather
|
||
|
|
than one request per row.</li>
|
||
|
|
<li>The server has an optimized path built into the database support for blocked
|
||
|
|
INSERT statements.</li>
|
||
|
|
</ul>
|
||
|
|
<p>Example:</p>
|
||
|
|
<pre> INSERT INTO TEST.TABLE1 ? ROWS VALUES (?, ?, ?, ?)</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>The INSERT statement has additional syntax that identifies it
|
||
|
|
as a blocked INSERT. The "? ROWS" clause indicates that an additional parameter
|
||
|
|
will be specified for this INSERT statement. It also indicates that the parameter
|
||
|
|
will contain a row count that determines how many rows will be sent for that
|
||
|
|
execution of the statement. The number of rows must be specified by means
|
||
|
|
of the <strong>SQLSetStmtAttr</strong> API. </p>
|
||
|
|
<div class="note"><span class="notetitle">Note:</span> With the V5R1 driver, you do not
|
||
|
|
need to specify the "? ROWS" clause to iSeries™ servers. V4R5 iSeries servers
|
||
|
|
added this support via PTFs SF64146 and SF64149.</div>
|
||
|
|
</div>
|
||
|
|
<div class="section"> <dl><dt class="dlterm">To view examples of blocked insert calls from C:</dt>
|
||
|
|
<dd> See <a href="rzaikextfetch.htm#extfetch">Block insert and block fetch C example</a></dd>
|
||
|
|
</dl>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzaikodbcperfconsd.htm" title="See any of the following ODBC performance topics.">iSeries Access for Windows ODBC performance</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|