Return-Path: X-Original-To: apmail-db-derby-commits-archive@www.apache.org Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 8AF9F411D for ; Tue, 31 May 2011 17:30:53 +0000 (UTC) Received: (qmail 64545 invoked by uid 500); 31 May 2011 17:30:53 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 64515 invoked by uid 500); 31 May 2011 17:30:53 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 64508 invoked by uid 99); 31 May 2011 17:30:53 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 31 May 2011 17:30:53 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED,WEIRD_QUOTING X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 31 May 2011 17:30:49 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id C22952388A60; Tue, 31 May 2011 17:30:28 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r1129814 - /db/derby/docs/trunk/src/adminguide/ Date: Tue, 31 May 2011 17:30:28 -0000 To: derby-commits@db.apache.org From: chaase3@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20110531173028.C22952388A60@eris.apache.org> Author: chaase3 Date: Tue May 31 17:30:27 2011 New Revision: 1129814 URL: http://svn.apache.org/viewvc?rev=1129814&view=rev Log: DERBY-1780 Document all supplied system procedures in the Server and Administration Guide (e.g. class loading utils and import/export) This patch adds 14 topics to the Admin Guide and modifies 4. Patches: DERBY-1780-1.diff Added: db/derby/docs/trunk/src/adminguide/cadminimport16245.dita (with props) db/derby/docs/trunk/src/adminguide/cadminimport27052.dita (with props) db/derby/docs/trunk/src/adminguide/cadminimport98264.dita (with props) db/derby/docs/trunk/src/adminguide/cadminimportdefaultformat.dita (with props) db/derby/docs/trunk/src/adminguide/cadminimportidentitycol.dita (with props) db/derby/docs/trunk/src/adminguide/cadminimportjdbc.dita (with props) db/derby/docs/trunk/src/adminguide/cadminimportlobs.dita (with props) db/derby/docs/trunk/src/adminguide/cadminimportnulls.dita (with props) db/derby/docs/trunk/src/adminguide/radminexportarguments.dita (with props) db/derby/docs/trunk/src/adminguide/radminimport64241.dita (with props) db/derby/docs/trunk/src/adminguide/radminimport91458.dita (with props) db/derby/docs/trunk/src/adminguide/radminimportcodeset.dita (with props) db/derby/docs/trunk/src/adminguide/tadminexporting.dita (with props) db/derby/docs/trunk/src/adminguide/tadminimporting.dita (with props) Modified: db/derby/docs/trunk/src/adminguide/cadminov83312.dita db/derby/docs/trunk/src/adminguide/cadminpreface23947.dita db/derby/docs/trunk/src/adminguide/cadminreplicstartrun.dita db/derby/docs/trunk/src/adminguide/derbyadmin.ditamap Added: db/derby/docs/trunk/src/adminguide/cadminimport16245.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/adminguide/cadminimport16245.dita?rev=1129814&view=auto ============================================================================== --- db/derby/docs/trunk/src/adminguide/cadminimport16245.dita (added) +++ db/derby/docs/trunk/src/adminguide/cadminimport16245.dita Tue May 31 17:30:27 2011 @@ -0,0 +1,48 @@ + + + + + +Importing and exporting data +You can import and export large amounts of data between files and +the database. Instead +of having to use INSERT and SELECT statements, you can use + system procedures to +import data directly from files into tables and to export data from tables into +files. + +bulk import and exportoverview +import proceduresoverview +export proceduresoverview +system proceduresimport and export, overview + + +

The system +procedures import and export data in delimited data file format.

+
    +
  • Use the export system procedures to write data from a database to one +or more files that are stored outside of the database. You can use a procedure +to export data from a table into a file or export data from a SELECT statement +result into a file.
    • +
  • Use the import system procedures to import data from a file into a table. +If the target table already contains data, you can replace or append to the +existing data.
    • +
+ + Propchange: db/derby/docs/trunk/src/adminguide/cadminimport16245.dita ------------------------------------------------------------------------------ svn:eol-style = native Added: db/derby/docs/trunk/src/adminguide/cadminimport27052.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/adminguide/cadminimport27052.dita?rev=1129814&view=auto ============================================================================== --- db/derby/docs/trunk/src/adminguide/cadminimport27052.dita (added) +++ db/derby/docs/trunk/src/adminguide/cadminimport27052.dita Tue May 31 17:30:27 2011 @@ -0,0 +1,100 @@ + + + + + +Bulk import and export requirements and considerations +There are requirements and limitations that you must consider before +you use the import +and export procedures. + +bulk import and exportrequirements +import proceduresrequirements +import procedureslimitations +export proceduresrequirements +export procedureslimitations +system proceduresimport and export, requirements + + +
+
Database transactions
+
You should issue either a COMMIT or ROLLBACK statement to complete all +transactions and release all table-level locks before you invoke an import +or export procedure. +issues a COMMIT or a ROLLBACK statement after each import and export procedure +is run.Imports are transactional. If an error occurs during bulk import, +all changes are rolled back.
+ +
Database connections
+
To invoke a import +or export procedure, you must be connected to the database into which the +data is imported or from which the data is exported. Other user applications +that access the table with a separate connection do not need to disconnect.
+ +
Classpath
+
You must have the derbytools.jar file in your classpath +before you can use the import or export procedures from +ij.
+ +
The table must exist
+
To import data into a table, the table must already exist in +. The table does not +have to be empty. If the table is not empty, bulk import performs single row +inserts, which result in slower performance.
+ +
Create indexes, keys, and unique constraints before you import
+
To avoid a separate step, create the indexes, keys (primary and foreign), +and unique constraints on tables before you import data. However, if your +memory and disk space resources are limited, you can build the indexes and +primary keys after importing data.
+ +
Data types
+
implicitly converts +the strings to the data type of the receiving column. If any of the implicit +conversions fail, the whole import is aborted. For example, "3+7" cannot be +converted into an integer. An export that encounters a runtime error stops. +You cannot import or export the XML data type.
+ +
Locking during import
+
Import procedures use the same isolation level as the connection in which +they are executed to insert data into tables. During import, the entire table +is exclusively locked irrespective of the isolation level.
+ +
Locking during export
+
Export procedures use the same isolation level as the connection in which +they are executed to fetch data from tables.
+ +
Import behavior on tables with triggers
+
The import procedures enable INSERT triggers when data is appended to the +table. The REPLACE parameter is not allowed when triggers are enabled on the +table.
+ +
Restrictions on the REPLACE parameter
+
If you import data into a table that already contains data, you can either +replace or append to the existing data. You can use the REPLACE parameter +on tables that have dependent tables. The replaced data must maintain +referential integrity; otherwise, the import operation will be rolled back. You +cannot use the REPLACE parameter if the table has triggers enabled.
+ +
Restrictions on tables
+
You cannot use import procedures to import data into a system table or a +declared temporary table.
+
+ + Propchange: db/derby/docs/trunk/src/adminguide/cadminimport27052.dita ------------------------------------------------------------------------------ svn:eol-style = native Added: db/derby/docs/trunk/src/adminguide/cadminimport98264.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/adminguide/cadminimport98264.dita?rev=1129814&view=auto ============================================================================== --- db/derby/docs/trunk/src/adminguide/cadminimport98264.dita (added) +++ db/derby/docs/trunk/src/adminguide/cadminimport98264.dita Tue May 31 17:30:27 2011 @@ -0,0 +1,48 @@ + + + + + +Methods for running the import and export procedures +You can run the import and export procedures from within an SQL +statement using ij or any Java application. + +bulk import and exportmethods for running procedures +import proceduresmethods for running +export proceduresmethods for running +bulk import and exportclient/server mode +import proceduresclient/server mode +export proceduresclient/server mode + + +

The import and export procedures read and write text files, and if you +use an external file when you import or export data, you can also import and +export blob data. The import procedures do not support read-once streams (live +data feeds), because the procedures read the first line of the file to determine +the number of columns, then read the file again to import the data.

+The import and export procedures are server-side utilities that exhibit +different behavior in client/server mode. Typically, you use these procedures +to import data into and export data from a locally running + database. However, you +can use the import and export procedures when + is running in a server +framework if you specify import and export files that are accessible to the +server. + + Propchange: db/derby/docs/trunk/src/adminguide/cadminimport98264.dita ------------------------------------------------------------------------------ svn:eol-style = native Added: db/derby/docs/trunk/src/adminguide/cadminimportdefaultformat.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/adminguide/cadminimportdefaultformat.dita?rev=1129814&view=auto ============================================================================== --- db/derby/docs/trunk/src/adminguide/cadminimportdefaultformat.dita (added) +++ db/derby/docs/trunk/src/adminguide/cadminimportdefaultformat.dita Tue May 31 17:30:27 2011 @@ -0,0 +1,79 @@ + + + + + +File format for input and output +There are specific requirements for the format of the input and +output files when you import and export data. + +import and export proceduresinput and output file format +importing datarequired file format +exporting datarequired file format +system proceduresimport using + + +

The default file format is a delimited text file with the following +characteristics:

+
    +
  • Rows are separated by a newline
    • +
  • Fields are separated by a comma (,)
    • +
  • Character-based fields are delimited with double quotes +(")
    • +
+Before you perform import or export operations, you +must ensure that the chosen delimiter character is not contained in the data +to be imported or exported. If you chose a delimiter character that is part +of the data to be imported or exported, unexpected errors might occur. The +following restrictions apply to column and character delimiters: +
    +
  • Delimiters are mutually exclusive
    • +
  • A delimiter cannot be a line-feed character, a carriage return, or a blank +space
    • +
  • The default decimal point (.) cannot be a character +delimiter
    • +
  • Delimiters cannot be hexadecimal characters (0-9, a-f, A-F).
    • +
 +

The record delimiter is assumed to be a newline character. The record +delimiter should not be used as any other delimiter.

+

Character delimiters are permitted with the character-based fields (CHAR, +VARCHAR, and LONG VARCHAR) of a file during import. Any pair of character +delimiters found between the enclosing character delimiters is imported into +the database. For example, suppose that you have the following character string: +"What a ""great"" day!" +The preceding character string gets imported into the database as: +What a "great" day!

+

During export, the rule applies in reverse. For example, suppose you have +the following character string: +"The boot has a 3" heel." +The preceding character string gets exported to a file as: +"The boot has a 3""heel."

+

The following example file shows four rows and four columns in the default +file format: +1,abc,22,def +22,,,"a is a zero-length string, b is null" +13,"hello",454,"world" +4,b and c are both null,,

+

The export procedure outputs the following values: +1,"abc",22,"def" +22,,,"a is a zero-length string, b is null" +13,"hello",454,"world" +4,"b and c are both null",,

+ + Propchange: db/derby/docs/trunk/src/adminguide/cadminimportdefaultformat.dita ------------------------------------------------------------------------------ svn:eol-style = native Added: db/derby/docs/trunk/src/adminguide/cadminimportidentitycol.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/adminguide/cadminimportidentitycol.dita?rev=1129814&view=auto ============================================================================== --- db/derby/docs/trunk/src/adminguide/cadminimportidentitycol.dita (added) +++ db/derby/docs/trunk/src/adminguide/cadminimportidentitycol.dita Tue May 31 17:30:27 2011 @@ -0,0 +1,140 @@ + + + + + +Import into tables that contain identity columns +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. The approach that you take +depends on whether the identity column is GENERATED ALWAYS or GENERATED BY +DEFAULT. + +importing datatables with identity columns + + +
Identity columns and the REPLACE parameter +

If the REPLACE parameter is used during import, resets its internal counter +of the last identity value for a column to the initial value defined for the +identity column.

+
+
Identity column is GENERATED ALWAYS +

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.

+

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.

+

The following table definition contains an identity column, +c2, and is used in the examples below:

+CREATE TABLE tab1 (c1 CHAR(30), c2 INT GENERATED ALWAYS AS IDENTITY, + c3 REAL, c4 CHAR(1)) +
    +
  • Suppose that you want to import data into tab1 from a file, +myfile.del, that does not have identity column information. +The myfile.del file contains three fields with the following +data: +Robert,45.2,J +Mike,76.9,K +Leo,23.4,I +To import the data, you must explicitly list the column names in the +tab1 table, except for the identity column c2, +when you call the procedure. For example: +CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', 'C1,C3,C4', + null, 'myfile.del',null, null, null, 0)
    • +
  • Suppose that you want to import data into tab1 from a file, +empfile.del, that also has identity column information. The +file contains three fields with the following data: +Robert,1,45.2,J +Mike,2,23.4,I +Leo,3,23.4,I +To import the data, you must explicitly specify an insert column list without +the identity column c2 and specify the column indexes without +identity column data when you call the procedure. For example: +CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', 'C1,C3,C4', + '1,3,4', 'empfile.del',null, null, null, 0)
    • +
+
+
Identity column is GENERATED BY DEFAULT +

If the identity column is defined as GENERATED BY DEFAULT, an identity value +is generated for a table row only 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:

+
    +
  • You may omit the identity column from the insert column list, in which case + 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.
    • +
  • You may include the identity column in the insert column list, in which case + will use the column +values from the input file. Of course, this option is available only if the +input file actually contains values for the identity column.
    • +
+

The following table definition contains an identity column, +c2, and is used in the examples below:

+CREATE TABLE tab1 (c1 CHAR(30), + c2 INT GENERATED BY DEFAULT AS IDENTITY, + c3 REAL, c4 CHAR(1)) +
    +
  • Suppose that you want to import data into tab1 from a +file, myfile.del, that does not have identity column +information. The myfile.del file contains three fields with the +following data: +Robert,45.2,J +Mike,76.9,K +Leo,23.4,I +To import the data, you must explicitly list the column names in the +tab1 table, except for the identity column c2, +when you call the procedure. For example: +CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', 'C1,C3,C4', + null, 'myfile.del',null, null, null, 0)
    • +
  • Suppose that you want to import data into tab1 from a file, +empfile.del, that also has identity column information. The +file contains three fields with the following data: +Robert,1,45.2,J +Mike,2,23.4,I +Leo,3,23.4,I +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 +null for the insert column list and column indexes parameters +when you call the procedure. For example: +CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', NULL, + NULL, 'empfile.del',null, null, null, 0)
    • +
  • Suppose (again) that you want to import data into tab1 +from a file, empfile.del, that also has identity column +information, but in this case, suppose that you do not wish to use the +identity column values from the input file, but would prefer to allow + 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 c2 +and specify the column indexes without identity column data when you call the +procedure. For example: +CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', 'C1,C3,C4', + '1,3,4', 'empfile.del',null, null, null, 0)
    • +
+
+ + Propchange: db/derby/docs/trunk/src/adminguide/cadminimportidentitycol.dita ------------------------------------------------------------------------------ svn:eol-style = native Added: db/derby/docs/trunk/src/adminguide/cadminimportjdbc.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/adminguide/cadminimportjdbc.dita?rev=1129814&view=auto ============================================================================== --- db/derby/docs/trunk/src/adminguide/cadminimportjdbc.dita (added) +++ db/derby/docs/trunk/src/adminguide/cadminimportjdbc.dita Tue May 31 17:30:27 2011 @@ -0,0 +1,47 @@ + + + + + +Running import and export procedures from JDBC +You can run import and export procedures from a JDBC +program. + +import and export proceduresrunning from JDBC +JDBCrunning import and export procedures + + + +

The following code fragment shows how you might call the +SYSCS_UTIL.SYSCS_EXPORT_TABLE procedure from a Java program. In this example, +the procedure exports the data in the staff table in the +default schema to the staff.dat file. A percentage +(%) character is used to specify the column delimiter.

+PreparedStatement ps=conn.prepareStatement( + "CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (?,?,?,?,?,?)"); + ps.setString(1,null); + ps.setString(2,"STAFF"); + ps.setString(3,"staff.dat"); + ps.setString(4,"%"); + ps.setString(5,null); + ps.setString(6,null); + ps.execute(); + + + Propchange: db/derby/docs/trunk/src/adminguide/cadminimportjdbc.dita ------------------------------------------------------------------------------ svn:eol-style = native Added: db/derby/docs/trunk/src/adminguide/cadminimportlobs.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/adminguide/cadminimportlobs.dita?rev=1129814&view=auto ============================================================================== --- db/derby/docs/trunk/src/adminguide/cadminimportlobs.dita (added) +++ db/derby/docs/trunk/src/adminguide/cadminimportlobs.dita Tue May 31 17:30:27 2011 @@ -0,0 +1,118 @@ + + + + + +Bulk import and export of large objects +You can import and export large objects (LOBs) using the + system +procedures. + +bulk import and exportlarge objects +large objects importing and exporting +importing datalarge objects +exporting datalarge objects +CLOB dataimporting and exporting +BLOB dataimporting and exporting +CHAR FOR BIT DATAimporting and exporting +VARCHAR FOR BIT DATAimporting and exporting +LONG VARCHAR FOR BIT DATAimporting and exporting + + + +
Importing and exporting CLOB and BLOB data +

CLOB and BLOB data can be exported to the same file as the rest of the column +data, or the LOB column data can be exported to a separate external file. When +the LOB column data is exported to a separate external file, reference to the +location of the LOB data is placed in the LOB column in the main export +file.

+

Importing and exporting LOB data using a separate external file might be +faster than storing the LOB data in the same file as the rest of the column +data:

+
    +
  • The CLOB data does not have to be scanned for the delimiters inside the +data
    • +
  • The BLOB data does not need to be converted to hexadecimal format
    • +
+
+
Importing and exporting other binary data +

When you export columns that contain the data types CHAR FOR BIT DATA, +VARCHAR FOR BIT DATA, and LONG VARCHAR FOR BIT DATA, the column data is always +exported to the main export file. The data is written in hexadecimal format. To +import data into a table that has columns of these data types, the data in the +import file for those columns must be in hexadecimal format.

+
+
Importing LOB data from a file that contains all of the +data +

You can use the SYSCS_UTIL.SYSCS_IMPORT_TABLE and +SYSCS_UTIL.SYSCS_IMPORT_DATA procedures to import data into a table that +contains a LOB column. The LOB data must be stored in the same file as the other +column data that you are importing. If you are importing data from a file that +was exported from a non- +source, the binary data must be in hexadecimal format.

+
+
Importing LOB data from a separate external file +

You can use the SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE and +SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE procedures to import LOB data +that is stored in a file that is separate from the main import file. These +procedures read the LOB data using the reference that is stored in the main +import file. If you are importing data from a +non- source, the +references to the LOB data must be in the main import file in the format +lobsFileName.Offset.length/. This is the same method that +the export procedures +use to export the LOB data to a separate external file.

+
+
Exporting LOB data to the same file as the other column +data +

You can use the SYSCS_UTIL.SYSCS_EXPORT_TABLE and +SYSCS_UTIL.SYSCS_EXPORT_QUERY procedures to write LOB data, along with rest of +the column data, to a single export file.

+

CLOB column data is treated same as other character data. Character +delimiters are allowed inside the CLOB data. The export procedures write the +delimiter inside the data as a double-delimiter.

+

BLOB column data is written to the export file in hexadecimal format. For +each byte of BLOB data, two characters are generated. The first character +represents the high nibble (4 bits) in hexadecimal and the second character +represents the low nibble.

+
+
Exporting LOB data to a separate external file from the other +column data +

You can use the SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE and +SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE procedures to write LOB data to a +separate external file. These procedures include the +lobFileName parameter, which specifies the name of the +external file for the LOB data.

+

When you use these procedures, the location of the LOB data is written to the +main export file. The format of the reference to the LOB stored in the main +export file is lobsFileName.Offset.length/.

+
    +
  • Offset is the position in the external file in +bytes
    • +
  • length is the size of the LOB column data in +bytes
    • +
+

If a LOB column value is NULL, length is written as -1. No data conversion +is performed when you export LOB data to an external file. BLOB data is written +in binary format, and CLOB data is written using the codeset that you +specify.

+

See for +examples using each of the import and export procedures.

+ + Propchange: db/derby/docs/trunk/src/adminguide/cadminimportlobs.dita ------------------------------------------------------------------------------ svn:eol-style = native Added: db/derby/docs/trunk/src/adminguide/cadminimportnulls.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/adminguide/cadminimportnulls.dita?rev=1129814&view=auto ============================================================================== --- db/derby/docs/trunk/src/adminguide/cadminimportnulls.dita (added) +++ db/derby/docs/trunk/src/adminguide/cadminimportnulls.dita Tue May 31 17:30:27 2011 @@ -0,0 +1,37 @@ + + + + + +How the import and export procedures process NULL values +In a delimited file, a NULL value is exported as an empty +field. + +import and export proceduresNULL values +importing dataNULL values +exporting dataNULL values + + +

The following example shows the export of a four-column row where the third +column is empty:

+7,95,,Happy Birthday +

The import procedures work the same way; an empty field is imported as +a NULL value.

+ + Propchange: db/derby/docs/trunk/src/adminguide/cadminimportnulls.dita ------------------------------------------------------------------------------ svn:eol-style = native Modified: db/derby/docs/trunk/src/adminguide/cadminov83312.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/adminguide/cadminov83312.dita?rev=1129814&r1=1129813&r2=1129814&view=diff ============================================================================== --- db/derby/docs/trunk/src/adminguide/cadminov83312.dita (original) +++ db/derby/docs/trunk/src/adminguide/cadminov83312.dita Tue May 31 17:30:27 2011 @@ -21,11 +21,17 @@ limitations under the License. -

provides some tools and features to assist database administrators, including:

+

provides some tools +and features to assist database administrators, including:

  • Consistency checker
  • Online backup
    • +
  • Procedures for importing and exporting data
    • +
  • Database replication
  • The ability to put a database's log on a separate device
    • +
  • Locking information monitoring
    • +
  • Reclaiming unused space
-

These tools and features are discussed in part two of this guide. See the sections in that part for more information.

+

These tools and features are discussed in Part Two of this guide. See the +sections in that part for more information.

 Modified: db/derby/docs/trunk/src/adminguide/cadminpreface23947.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/adminguide/cadminpreface23947.dita?rev=1129814&r1=1129813&r2=1129814&view=diff ============================================================================== --- db/derby/docs/trunk/src/adminguide/cadminpreface23947.dita (original) +++ db/derby/docs/trunk/src/adminguide/cadminpreface23947.dita Tue May 31 17:30:27 2011 @@ -38,6 +38,9 @@ line, and the Network Server API to mana

  • Describes advanced topics for Network Server users.

    • +
  • +

    Describes several +Network Server sample programs for Network Server users.

    • Part two: Administration Guide

      @@ -45,6 +48,11 @@ advanced topics for databases.

    • Describes how to back up a database when it is online.

      • +
    • +

      Describes how to import and export large amounts of data between files and +database tables.

      • +
    • +

      Describes how to replicate databases.

    • Describes how to put a database's log on a separate device, which can improve the performance of large databases.

    • Modified: db/derby/docs/trunk/src/adminguide/cadminreplicstartrun.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/adminguide/cadminreplicstartrun.dita?rev=1129814&r1=1129813&r2=1129814&view=diff ============================================================================== --- db/derby/docs/trunk/src/adminguide/cadminreplicstartrun.dita (original) +++ db/derby/docs/trunk/src/adminguide/cadminreplicstartrun.dita Tue May 31 17:30:27 2011 @@ -85,8 +85,10 @@ are not actually there. Therefore, you m slave after a failover by calling either SQLJ.remove_jar followed by SQLJ.install_jar, or SQLJ.replace_jar. (For information on installing jar files, see -"Storing jar files in a database" in the -.)

      +"Loading classes from a database" in the + and +"System procedures for storing jar files in a database" in the +.)

      If the jar files must be available to clients immediately after a failover, you must stop replication and then start replication over again from the beginning, so that the slave database will have the same jar files as the Modified: db/derby/docs/trunk/src/adminguide/derbyadmin.ditamap URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/adminguide/derbyadmin.ditamap?rev=1129814&r1=1129813&r2=1129814&view=diff ============================================================================== --- db/derby/docs/trunk/src/adminguide/derbyadmin.ditamap (original) +++ db/derby/docs/trunk/src/adminguide/derbyadmin.ditamap Tue May 31 17:30:27 2011 @@ -33,6 +33,125 @@ Apache Derby + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + @@ -257,6 +376,34 @@ procedures to perform an online backup"> + + + + + + + + + + + + + + + + + + + + + + + + + + + + Added: db/derby/docs/trunk/src/adminguide/radminexportarguments.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/adminguide/radminexportarguments.dita?rev=1129814&view=auto ============================================================================== --- db/derby/docs/trunk/src/adminguide/radminexportarguments.dita (added) +++ db/derby/docs/trunk/src/adminguide/radminexportarguments.dita Tue May 31 17:30:27 2011 @@ -0,0 +1,96 @@ + + + + + +Parameters for the export procedures +The export +procedures use specific parameters. + +import and export proceduresexport parameters +bulk exportprocedure parameters +exporting dataprocedure parameters + + +

      +
      SCHEMANAME
      +
      Specifies the schema of the table. You can specify a NULL value to use the +default schema name. The SCHEMANAME parameter takes an +input argument that is a VARCHAR(128) data type.
      +       +
      SELECTSTATEMENT
      +
      Specifies the SELECT statement query that returns the data to be exported. +Specifying a NULL value will result in an error. The +SELECTSTATEMENT parameter takes an input argument that is +a VARCHAR(32672) data type.
      +       +
      TABLENAME
      +
      Specifies the table name of the table or view from which the data is to +be exported. This table cannot be a system table or a declared temporary table. +The string must exactly match the case of the table name. Specifying a NULL +value results in an error. The TABLENAME parameter takes +an input argument that is a VARCHAR(128) data type.
      +       +
      FILENAME
      +
      Specifies the file to which the data is to be exported. If the path is +omitted, the current working directory is used. If the name of a file that +already exists is specified, the export utility overwrites the contents of the +file; it does not append the information. The specified location of the file +should refer to the server-side location if you are using the Network Server. +Specifying a NULL value results in an error. The FILENAME +parameter takes an input argument that is a VARCHAR(32672) data type.
      +       +
      COLUMNDELIMITER
      +
      Specifies a column delimiter. The specified character is used in place of a +comma to signify the end of a column. You can specify a NULL value to use the +default value of a comma. The COLUMNDELIMITER parameter +must be a CHAR(1) data type.
      +       +
      CHARACTERDELIMITER
      +
      Specifies a character delimiter. The specified character is used in place of +double quotation marks to enclose a character string. You can specify a NULL +value to use the default value of a double quotation mark. The +CHARACTERDELIMITER parameter takes an input argument that +is a CHAR(1) data type.
      +       +
      CODESET
      +
      Specifies the code set of the data in the export file. The code set name +should be one of the Java supported character encoding sets. Data is converted +from the database code page to the specified code page before writing to the +file. You can specify a NULL value to write the data in the same code page +as the JVM in which it is being executed. The CODESET +parameter takes an input argument that is a VARCHAR(128) data type.
      +       +
      LOBSFILENAME
      +
      Specifies the file that the large object data is exported to. If the path is +omitted, the LOB file is created in the same directory as the main export file. +If you specify the name of an existing file, the export utility overwrites the +contents of the file. The data is not appended to the file. If you are using the +Network Server, the file should be in a server-side location. Specifying a NULL +value results in an error. The LOBSFILENAME parameter takes +an input argument that is a VARCHAR(32672) data type.
      +
      +

      If you create a schema, table, or column name as a non-delimited identifier, +you must pass the name to the export procedure using all uppercase characters. +If you created a schema or table name as a delimited identifier, you must pass +the name to the export procedure using the same case that was used when it was +created.

      +
      + + Propchange: db/derby/docs/trunk/src/adminguide/radminexportarguments.dita ------------------------------------------------------------------------------ svn:eol-style = native Added: db/derby/docs/trunk/src/adminguide/radminimport64241.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/adminguide/radminimport64241.dita?rev=1129814&view=auto ============================================================================== --- db/derby/docs/trunk/src/adminguide/radminimport64241.dita (added) +++ db/derby/docs/trunk/src/adminguide/radminimport64241.dita Tue May 31 17:30:27 2011 @@ -0,0 +1,103 @@ + + + + + +Parameters for the import procedures +The import +procedures use specific parameters. + +import and export proceduresimport parameters +bulk importprocedure parameters +importing dataprocedure parameters + + +
      +
      +
      SCHEMANAME
      +
      Specifies the schema of the table. You can specify a NULL value to use +the default schema name. The SCHEMANAME parameter takes +an input argument that is a VARCHAR(128) data type.
      +       +
      TABLENAME
      +
      Specifies the name of the table into which the data is to be imported. +This table cannot be a system table or a declared temporary table. The string +must exactly match the case of the table name. Specifying a NULL value results +in an error. The TABLENAME parameter takes an input +argument that is a VARCHAR(128) data type.
      +       +
      INSERTCOLUMNS
      +
      Specifies the comma-separated column names of the table into which the +data will be imported. You can specify a NULL value to import into all columns +of the table. The INSERTCOLUMNS parameter takes an input +argument that is a VARCHAR(32672) data type.
      +       +
      COLUMNINDEXES
      +
      Specifies the comma-separated column indexes (numbered from one) of the +input data fields that will be imported. You can specify a NULL value to use all +input data fields in the file. The COLUMNINDEXES parameter +takes an input argument that is a VARCHAR(32672) data type.
      +       +
      FILENAME
      +
      Specifies the name of the file that contains the data to be imported. +If the path is omitted, the current working directory is used. The specified +location of the file should refer to the server side location if you are using +the Network Server. Specifying a NULL value results in an error. The +FILENAME parameter takes an input argument that is a +VARCHAR(32672) data type.
      +       +
      COLUMNDELIMITER
      +
      Specifies a column delimiter. The specified character is used in place +of a comma to signify the end of a column. You can specify a NULL value to +use the default value of a comma. The COLUMNDELIMITER +parameter takes an input argument that is a CHAR(1) data type.
      +       +
      CHARACTERDELIMITER
      +
      Specifies a character delimiter. The specified character is used in place +of double quotation marks to enclose a character string. You can specify a +NULL value to use the default value of a double quotation mark. The +CHARACTERDELIMITER parameter takes an input argument that +is a CHAR(1) data type.
      +       +
      CODESET
      +
      Specifies the code set of the data in the input file. The code set name +should be one of the Java supported character encoding sets. Data is converted +from the specified code set to the database code set (UTF-8). You can specify +a NULL value to interpret the data file in the same code set as the JVM in +which it is being executed. The CODESET parameter takes +an input argument that is a VARCHAR(128) data type.
      +       +
      REPLACE
      +
      A non-zero value for the replace parameter will import in REPLACE mode, +while a zero value will import in INSERT mode. REPLACE mode deletes all existing +data from the table by truncating the table and inserts the imported data. +The table definition and the index definitions are not changed. You can import +with REPLACE mode only if the table already exists. INSERT mode adds the +imported data to the table without changing the existing table data. Specifying +a NULL value results in an error. The REPLACE parameter +takes an input argument that is a SMALLINT data type.
      +
      +

      If you create a schema, table, or column name as a non-delimited identifier, +you must pass the name to the import procedure using all uppercase characters. +If you created a schema, table, or column name as a delimited identifier, you +must pass the name to the import procedure using the same case that was used +when it was created.

      +
      +       +       Propchange: db/derby/docs/trunk/src/adminguide/radminimport64241.dita ------------------------------------------------------------------------------ svn:eol-style = native Added: db/derby/docs/trunk/src/adminguide/radminimport91458.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/adminguide/radminimport91458.dita?rev=1129814&view=auto ============================================================================== --- db/derby/docs/trunk/src/adminguide/radminimport91458.dita (added) +++ db/derby/docs/trunk/src/adminguide/radminimport91458.dita Tue May 31 17:30:27 2011 @@ -0,0 +1,119 @@ + + + + + +Examples of bulk import and export +All of the examples in this section are run using the +ij utility. + +import and export proceduresexamples +bulk exportexamples +exporting dataexamples +bulk importexamples +importing dataexamples + + +
      Example: Importing all data from a file +

      The following example shows how to import data into the STAFF table in a +sample database from the myfile.del file. The data will be +appended to the existing data in the table.

      +CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE( + null,'STAFF','myfile.del',null,null,null,0); +
      +
      Example: Importing all data from a delimited file +

      The following example shows how to import data into the STAFF table in a +sample database from a delimited data file, myfile.del. This +example defines the percentage character (%) as the string +delimiter, and a semicolon as the column delimiter. The data will be appended to +the existing data in the table.

      +CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE( + null,'STAFF','c:\output\myfile.del',';','%',null,0); +
      +
      Example: Importing all data from a table, using a separate +import file for the LOB data +

      The following example shows how to import data into the STAFF table in a +sample database from a delimited data file, staff.del. The +import file staff.del is the main import file and contains +references that point to a separate file which contains the LOB data. This +example specifies a comma as the column delimiter. The data will be appended to +the existing data in the table.

      +CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE( + null,'STAFF','c:\data\staff.del',',','"','UTF-8',0); +
      +
      Example: Importing data into specific columns, using a separate +import file for the LOB data +

      The following example shows how to import data into several columns of the +STAFF table. The STAFF table includes a LOB column in a sample database. The +import file, staff.del, is a delimited data file. The +staff.del file contains references that point to a separate +file which contains the LOB data. The data in the import file is formatted using +double quotation marks (") as the string delimiter and a comma +(,) as the column delimiter. The data will be appended to the +existing data in the STAFF table.

      +CALL SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE( + null, 'STAFF', 'NAME,DEPT,SALARY,PICTURE', '2,3,4,6', + 'c:\data\staff.del', ',','"','UTF-8', 0); +
      +
      Example: Exporting all data from a table to a single export +file +

      The following example shows how to export data from the STAFF table in a +sample database to the file myfile.del.

      +CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE( + null,'STAFF','myfile.del',null,null,null); +
      +
      Example: Exporting data from a table to a single delimited +export file +

      The following example shows how to export data from the STAFF table to a +delimited data file, myfile.del, with the percentage character +(%) as the character delimiter, and a semicolon as the column +delimiter from the STAFF table.

      +CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE( + null,'STAFF','c:\output\myfile.del',';','%',null); +
      +
      Example: Exporting all data from a table, using a separate +export file for the LOB data +

      The following example shows how to export data from the STAFF table in a +sample database to the main file, staff.del, and the LOB export +file, pictures.dat.

      +CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE(null,'STAFF', + 'c:\data\staff.del',',','"','UTF-8', 'c:\data\pictures.dat'); +
      +
      Example: Exporting data from a query to a single export +file +

      The following example shows how to export employee data in department 20 from +the STAFF table in a sample database to the file +awards.del.

      +CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY( + 'SELECT * FROM STAFF WHERE dept=20', + 'c:\output\awards.del',null,null,null); +
      +
      Example: Exporting data from a query, using a separate export +file for the LOB data +

      The following example shows how to export employee data in department 20 from +the STAFF table in a sample database to the main file, +staff.del, and the LOB data to the file +pictures.dat.

      +CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE( + 'SELECT * FROM STAFF WHERE dept=20', + 'c:\data\staff.del', ',' ,'"', + 'UTF-8','c:\data\pictures.dat'); +
      +       +       Propchange: db/derby/docs/trunk/src/adminguide/radminimport91458.dita ------------------------------------------------------------------------------ svn:eol-style = native Added: db/derby/docs/trunk/src/adminguide/radminimportcodeset.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/adminguide/radminimportcodeset.dita?rev=1129814&view=auto ============================================================================== --- db/derby/docs/trunk/src/adminguide/radminimportcodeset.dita (added) +++ db/derby/docs/trunk/src/adminguide/radminimportcodeset.dita Tue May 31 17:30:27 2011 @@ -0,0 +1,49 @@ + + + + + +CODESET values for import and export procedures +Import and export procedures accept arguments to specify codeset +values. You can specify the codeset (character encoding) for import and export +procedures to override the system default. + +import and export procedurescodesets +codeset valuesimport and export procedures + + +
      +

      For a table that shows a sample of the character encodings supported +by the Java Development Kit, see "derby.ui.codeset property" in the +. To review the complete list +of character encodings, refer to your Java documentation.

      +
      +
      Examples: Specifying the codeset in import and export +procedures +

      The following example shows how to specify UTF-8 encoding to export to the +staff.dat table:

      +CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE( + NULL,'STAFF','staff.dat',NULL,NULL,'UTF-8') +

      The following example shows how to specify UTF-8 encoding to import from the +staff.dat table:

      +CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE( + NULL,'STAFF','staff.dat',NULL,NULL,'UTF-8',0) +
      +       +       Propchange: db/derby/docs/trunk/src/adminguide/radminimportcodeset.dita ------------------------------------------------------------------------------ svn:eol-style = native Added: db/derby/docs/trunk/src/adminguide/tadminexporting.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/adminguide/tadminexporting.dita?rev=1129814&view=auto ============================================================================== --- db/derby/docs/trunk/src/adminguide/tadminexporting.dita (added) +++ db/derby/docs/trunk/src/adminguide/tadminexporting.dita Tue May 31 17:30:27 2011 @@ -0,0 +1,106 @@ + + + + + + +Exporting data using the built-in procedures +You can use the + export procedures to +export all of the data from table or query, or to export LOB data separately +from the other data. + +import and export proceduresbulk export +exporting data +system proceduresexport using + + + + +Use the following table to choose the correct procedure for the type +of export that you want to perform. For examples of these procedures, see +. + + +Using the built-in export procedures + + + + + +Type of Export +Procedure to Use + + + + +To export all the data from a table to a single export +file, including the LOB data +SYSCS_UTIL.SYSCS_EXPORT_TABLE + (IN SCHEMANAME VARCHAR(128), + IN TABLENAME VARCHAR(128), + IN FILENAME VARCHAR(32672), + IN COLUMNDELIMITER CHAR(1), + IN CHARACTERDELIMITER CHAR(1), + IN CODESET VARCHAR(128)) + + +To export all the data from a table, and place the LOB +data into a separate export file +SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE + (IN SCHEMANAME VARCHAR(128), + IN TABLENAME VARCHAR(128), + IN FILENAME VARCHAR(32672), + IN COLUMNDELIMITER CHAR(1), + IN CHARACTERDELIMITER CHAR(1), + IN CODESET VARCHAR(128), + IN LOBSFILNAME VARCHAR(32672)) +

      A reference to the location of the LOB data is placed in the LOB column in +the main export file.

       +       + +To export the result of a SELECT statement to a single +file, including the LOB data +SYSCS_UTIL.SYSCS_EXPORT_QUERY + (IN SELECTSTATEMENT VARCHAR(32672), + IN FILENAME VARCHAR(32672), + IN COLUMNDELIMITER CHAR(1), + IN CHARACTERDELIMITER CHAR(1), + IN CODESET VARCHAR(128)) + + +To export the result of a SELECT statement to a main +export file, and place the LOB data into a separate export file +SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE + (IN SELECTSTATEMENT VARCHAR(32672), + IN FILENAME VARCHAR(32672), + IN COLUMNDELIMITER CHAR(1), + IN CHARACTERDELIMITER CHAR(1), + IN CODESET VARCHAR(128), + IN LOBSFILENAME VARCHAR(32672)) +

      A reference to the LOB data is written to the main export file.

       +       + + +
      +       +       +       +       +       Propchange: db/derby/docs/trunk/src/adminguide/tadminexporting.dita ------------------------------------------------------------------------------ svn:eol-style = native Added: db/derby/docs/trunk/src/adminguide/tadminimporting.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/adminguide/tadminimporting.dita?rev=1129814&view=auto ============================================================================== --- db/derby/docs/trunk/src/adminguide/tadminimporting.dita (added) +++ db/derby/docs/trunk/src/adminguide/tadminimporting.dita Tue May 31 17:30:27 2011 @@ -0,0 +1,123 @@ + + + + + + +Importing data using the built-in procedures +You can use the + import procedures to +import all of the data from a table or query, or to import LOB data separately +from the other data. + +import and export proceduresbulk import +importing data +system proceduresimport using + + + + +Use the following table to choose the correct procedure for the type +of import that you want to perform. For examples of these procedures, see +. + + +Using the built-in import procedures + + + + + +Type of Import +Procedure to Use + + + + +To import all the data to a table, where the import file +contains the LOB data +SYSCS_UTIL.SYSCS_IMPORT_TABLE + (IN SCHEMANAME VARCHAR(128), + IN TABLENAME VARCHAR(128), + IN FILENAME VARCHAR(32672), + IN COLUMNDELIMITER CHAR(1), + IN CHARACTERDELIMITER CHAR(1), + IN CODESET VARCHAR(128), + IN REPLACE SMALLINT) + + +To import the data to a table, where the LOB data is +stored in a separate file and the main import file contains all of the other +data with a reference to the LOB data +SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE + (IN SCHEMANAME VARCHAR(128), + IN TABLENAME VARCHAR(128), + IN FILENAME VARCHAR(32672), + IN COLUMNDELIMITER CHAR(1), + IN CHARACTERDELIMITER CHAR(1), + IN CODESET VARCHAR(128), + IN REPLACE SMALLINT) +

      The import utility looks in the main import file for a reference to the +location of the LOB data. The format of the reference to the LOB stored in the +main import file must be lobsFileName.Offset.length/.

      +       +       + +To import data from a file to a subset of columns in a +table, where the import file contains the LOB data +SYSCS_UTIL.SYSCS_IMPORT_DATA + (IN SCHEMANAME VARCHAR(128), + IN TABLENAME VARCHAR(128), + IN INSERTCOLUMNS VARCHAR(32672), + IN COLUMNINDEXES VARCHAR(32672), + IN FILENAME VARCHAR(32672), + IN COLUMNDELIMITER CHAR(1), + IN CHARACTERDELIMITER CHAR(1), + IN CODESET VARCHAR(128), + IN REPLACE SMALLINT) +

      You must specify the INSERTCOLUMNS parameter on the table into which data +will be imported. You must specify the COLUMNINDEXES parameter to import data +fields from a file to column in a table.

       +       + +To import data to a subset of columns in a table, where +the LOB data is stored in a separate file and the main import file contains all +of the other data with a reference to the LOB data +SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE + (IN SCHEMANAME VARCHAR(128), + IN TABLENAME VARCHAR(128), + IN INSERTCOLUMNS VARCHAR(32672), + IN COLUMNINDEXES VARCHAR(32672), + IN FILENAME VARCHAR(32672), + IN COLUMNDELIMITER CHAR(1), + IN CHARACTERDELIMITER CHAR(1), + IN CODESET VARCHAR(128), + IN REPLACE SMALLINT) +

      The import utility looks in the main import file for a reference to the +location of the LOB data. The format of the reference to the LOB stored in the +main import file must be lobsFileName.Offset.length/.

      +       +       + + +
      +       +       +       +       Propchange: db/derby/docs/trunk/src/adminguide/tadminimporting.dita ------------------------------------------------------------------------------ svn:eol-style = native