Importing and exporting data using the CLP

The DB2 Everyplace Command Line Processor for Palm OS, Symbian OS, Windows CE, Win32 platforms, Neutrino, and embedded Linux supports importing data from a file to DB2 Everyplace and exporting DB2 Everyplace data to a file. Importing and exporting data on the Palm OS uses the memo files on the device.

Procedure

Importing data from a file to DB2 Everyplace

To import data from a file on the mobile device to DB2 Everyplace, type: 

IMPORT FROM file_name OF DEL INSERT INTO table_name [(column list)]
file_name is the name of the file to import from.

On Palm OS, file_name is the name of the memo to import from. The file name must appear on the first line of the memo. Palm memos have a limitation of storing 4K bytes text. table_name is the name of an existing table to import into. For example, to import data from a file named mydata.txt to an existing table named mytable, type: 

IMPORT FROM mydata.txt OF DEL INSERT INTO mytable

Exporting data from DB2 Everyplace to a file

To export data from DB2 Everyplace to a file, type: 

EXPORT TO file_name OF DEL stmt
file_name is the name of the file to write the data to. stmt is the SELECT statement to select the data to export. For example, to export all data from the table named mytable to a file named myfile.txt, type: 
EXPORT TO myfile.txt OF DEL SELECT * FROM mytable

Importing and exporting data on Palm OS mobile devices

DB2 Everyplace for Palm OS uses a set of command line tools for Windows and a Palm OS application to import and export data as PDB files.

The Import/Export tools include the following executable files, which are installed on the Windows workstation. These files are located in DB2Everyplace\Clients\utilities:

CSV2DB2e.exe
This file imports data from a Comma Separated Values file (with file extension of .csv) into a DB2 Everyplace table. A DB2e table (for example, named PERSON) is represented by two files, DSY_PERSON and DSY_iPERSON. The DSY_PERSON file contains the data, and DSY_iPERSON contains the indexing information.

DB2e2PDB.exe
This program converts a DB2 Everyplace table into Palm OS PDB format. It then copies the files into the user's directory and informs the HotSync program that there are files to install. If more than one user is defined in the system, a user list will be displayed so that you can select the intended user.

PDB2DB2e.exe
PDB2DB2e.exe converts Palm OS PDB files from the user backup area into a DB2 Everyplace table. If more than one user is defined in the system, a user list will be displayed so that you can select the intended user.

DB2e2CSV.exe
DB2e2CSV exports a DB2 Everyplace table into a CSV file. It also makes use of the DB2 Everyplace system catalog files named DB2eSYSTABLES and DB2eSYSCOLUMNS.

PalmImport.bat
PalmImport.bat combines the operations of CSV2DB2e.exe and DB2e2PDB.exe.

PalmExport.bat
PalmExport.bat combines the operations of PDB2DB2e.exe and DB2e2CSV.exe.

The Import/Export tools include the following Palm OS application:

DB2eImport.prc
This program registers the DB2 Everyplace files transferred by the HotSync program to the local DB2 Everyplace system.

To import data to the Palm OS device:

  1. Create two files with the same name (for example, VNPERSON), one with a .csv extension and one with a .sch extension. The .csv file contains the data, and the .sch file contains the schema for the table to be imported. Note that the file name cannot contain any blank spaces. Sample .csv and .sch files are located in the DemoImport\ folder. The files are named VNPERSON.csv and VNPERSON.sch.

    Table 20 lists the supported data types and their representation in the CSV file.

    Table 20.

    Data type Represented as
    integer (or int) 1234
    smallint 1234
    decimal(n,p) 12.34
    char(n) "John"
    varchar(n) "John"
    date yyyymmdd
    time "14.05.48"
    timestamp "2001-05-01-16.16.51.000000"

    To represent a null value for a column, enter nothing between the commas of the CSV file. For example, three integer columns with a null in the second column would be represented as 1,,3 in the CSV file and become 1, null, 3 in the database.

  2. Start the import tool, PalmImport.bat, providing the schema of the table as a parameter. The schema should be in an associated .sch file. Use the following syntax to start the Import tool: 
    PalmImport.bat path_name file_name

    where path_name is the path to the CSV file, and file_name is the CSV file name in uppercase without an extension. The CSV file name cannot be enclosed in double quotation marks or contain any blank spaces. For example: 

    PalmImport.bat DemoImport VNPERSON

    The imported tables are automatically added to the Palm Install Tool to be installed after the next HotSync operation.

  3. Install the DB2eImport.prc to the Palm OS device using the Palm Install Tool.
  4. Perform a HotSync operation to complete the installation of the imported tables and DB2eImport.prc.
  5. Start the DB2eImport program on the Palm OS device to complete the import.

To export data from the Palm OS device:

  1. Perform a HotSync operation to back up the Palm OS device. Always back up and remove the DB2 Everyplace files ( files starting with DSY ) from the Palm OS user's backup directory before starting a HotSync operation. The backup area on Windows workstations is usually located at: 
    PalmDir\user_name\Backup

    where PalmDir is the directory where the Palm OS software is installed, and user_name is the user name of the Palm OS user.

  2. Start the export program, PalmExport.bat, with the following syntax: 
    PalmExport.bat path_name file_name

    where path_name is the output path, and file_name is the DB2 Everyplace table name in uppercase. The DB2 Everyplace table name cannot be enclosed in double quotation marks or contain any blank spaces. For example: 

    PalmExport.bat DemoExport VNPERSON

    The resulting file is in the same path as the source file.

When an error is encountered, the Import/Export tools reports the number of records processed.

A sample using the Import/Export tools is included in the DB2Everyplace\Clients\utilities directory. The batch files PalmExport.bat and PalmImport.bat provide examples of how to use the CSV2DB2e.exe and DB2e2CSV.exe tools.

Related concepts

Related reference