svn commit: r886656 - /db/derby/docs/trunk/src/tools/ctoolsimportidentitycol.dita

[bp...@apache.org]

Author: bpendleton
Date: Thu Dec  3 02:18:44 2009
New Revision: 886656

URL: http://svn.apache.org/viewvc?rev=886656&view=rev
Log:
DERBY-482: Cover GENERATED BY DEFAULT in "Importing with identity cols" page

This change adds and re-organizes the text on the "Importing into tables
with identity columns" page in the Tools guide. Previously, the page
described considerations for a table with a GENERATED ALWAYS column; this
change adds additional information to describe situations that arise with
GENERATED BY DEFAULT columns.


Modified:
    db/derby/docs/trunk/src/tools/ctoolsimportidentitycol.dita

Modified: db/derby/docs/trunk/src/tools/ctoolsimportidentitycol.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/tools/ctoolsimportidentitycol.dita?rev=886656&r1=886655&r2=886656&view=diff
==============================================================================
--- db/derby/docs/trunk/src/tools/ctoolsimportidentitycol.dita (original)
+++ db/derby/docs/trunk/src/tools/ctoolsimportidentitycol.dita Thu Dec  3 02:18:44 2009
@@ -22,25 +22,33 @@
 <title>Import into tables that contain identity columns</title>
 <shortdesc>You can use the either the SYSCS_UTIL.SYSCS_IMPORT_DATA procedure
 or the SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE procedure to import
-data into a table that contains an identity column. </shortdesc>
+data into a table that contains an identity column.
+The approach that you take depends on whether the identity column is
+GENERATED ALWAYS or GENERATED BY DEFAULT.
+</shortdesc>
 <prolog><metadata>
 <keywords><indexterm>importing data<indexterm>tables with identity columns</indexterm></indexterm>
 </keywords>
 </metadata></prolog>
 <conbody>
-<p>If the identity column is defined as GENERATED ALWAYS, an identity value
-is generated for a table row whenever the corresponding row field in the input
-file does not contain a value for the identity column. When a corresponding
-row field in the input file already contains a value for the identity column,
-the row cannot be inserted into the table and the import operation will fail.
-To prevent such scenarios, the following examples show how to specify parameters
-in the SYSCS_UTIL.SYSCS_IMPORT_DATA and SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE
-procedures to ignore data for the identity column from the file, or omit the
-column name from the insert column list.</p>
+<section><title>Identity columns and the REPLACE parameter</title>
 <p>If the <parmname>REPLACE</parmname> parameter is used during import, <ph
 conref="../conrefs.dita#prod/productshortname"></ph> resets its internal counter
 of the last identity value for a column to the initial value defined for the
 identity column.</p>
+</section>
+<section><title>Identity column is GENERATED ALWAYS</title>
+<p>If the identity column is defined as GENERATED ALWAYS, an identity value
+is always generated for a table row.
+When a corresponding
+row in the input file already contains a value for the identity column,
+the row cannot be inserted into the table and the import operation will fail.
+</p>
+<p>
+To prevent such failure, the following examples show how to specify parameters
+in the SYSCS_UTIL.SYSCS_IMPORT_DATA and SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE
+procedures to ignore data for the identity column from the file, and omit the
+column name from the insert column list.</p>
 <p>The following table definition contains an identity column, <codeph>c2</codeph> and
 is used in the examples below:</p>
 <codeblock>CREATE TABLE tab1 (c1 CHAR(30), c2 INT GENERATED ALWAYS AS IDENTITY, 
@@ -56,7 +64,7 @@
 you call the procedure. For example:   <codeblock>CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', 'C1,C3,C4', 
     null, 'myfile.del',null, null, null, 0)
    </codeblock></li>
-<li>Suppose that you want import data into <codeph>tab1</codeph> from a file <codeph>empfile.del</codeph> that
+<li>Suppose that you want to import data into <codeph>tab1</codeph> from a file <codeph>empfile.del</codeph> that
 also has identity column information. The file contains three fields with
 the following data: <codeblock>Robert,1,45.2,J
 Mike,2,23.4,I
@@ -66,5 +74,71 @@
 procedure. For example: <codeblock>CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', 'C1,C3,C4', 
     '1,3,4', 'empfile.del',null, null, null, 0)</codeblock></li>
 </ul>
+</section>
+<section><title>Identity column is GENERATED BY DEFAULT</title>
+<p>
+If the identity column is defined as GENERATED BY DEFAULT, an identity value
+is only generated for a table row if no explicit value is given.
+This means that you have several options, depending on the contents of your
+input file, and the desired outcome of the import processing:
+<ul>
+<li>You may omit the identity column from the insert column list, in which case 
+<ph conref="../conrefs.dita#prod/productshortname"></ph>
+will generate a new value for the identity column for each input row.
+You may use this option whether or not the input file contains values for
+the identity column, but
+note that if the input file contains values for the identity column, you
+must also then omit the identity column from the column indexes when you
+call the procedure.
+</li>
+<li>You may include the identity column in the insert column list, in which case
+<ph conref="../conrefs.dita#prod/productshortname"></ph>
+will use the column values from the input file. Of course, this option is
+only available if the input file actually contains values for the identity
+column.
+</li>
+</ul>
+</p>
+<p>The following table definition contains an identity column,
+<codeph>c2</codeph> and is used in the examples below:</p>
+<codeblock>CREATE TABLE tab1 (c1 CHAR(30),
+   c2 INT GENERATED BY DEFAULT AS IDENTITY, 
+   c3 REAL, c4 CHAR(1))</codeblock>
+<ul>
+<li>Suppose that you want to import data into <codeph>tab1</codeph> from a
+file <codeph>myfile.del</codeph> that does not have identity column information.
+The <codeph>myfile.del</codeph> file contains three fields with the following
+data: <codeblock>Robert,45.2,J
+Mike,76.9,K
+Leo,23.4,I   </codeblock> To import the data, you must explicitly list the
+column names in the <codeph>tab1</codeph> table except for the identity column <codeph>c2</codeph> when
+you call the procedure. For example:   <codeblock>CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', 'C1,C3,C4', 
+    null, 'myfile.del',null, null, null, 0)
+   </codeblock></li>
+<li>Suppose that you want to import data into <codeph>tab1</codeph> from a file <codeph>empfile.del</codeph> that
+also has identity column information. The file contains three fields with
+the following data: <codeblock>Robert,1,45.2,J
+Mike,2,23.4,I
+Leo,3,23.4,I   </codeblock> 
+In this case, suppose that you wish to use the existing identity column values
+from the input file.
+To import the data, you may simply pass <codeph>null</codeph>
+for the insert column list and column indexes parameters when you call the
+procedure. For example: <codeblock>CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', NULL, 
+    NULL, 'empfile.del',null, null, null, 0)</codeblock></li>
+<li>Suppose (again) that you want to import data into <codeph>tab1</codeph>
+from a file <codeph>empfile.del</codeph> that
+also has identity column information, but in this case, suppose that you
+do <b>not</b> wish to use the identity column values from the input file,
+but would prefer to allow
+<ph conref="../conrefs.dita#prod/productshortname"></ph>
+to generate new identity column values instead.
+In this case, to import the data, you must specify
+an insert column list without the identity column <codeph>c2</codeph> and
+specify the column indexes without identity column data when you call the
+procedure. For example: <codeblock>CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', 'C1,C3,C4', 
+    '1,3,4', 'empfile.del',null, null, null, 0)</codeblock></li>
+</ul>
+</section>
 </conbody>
 </concept>