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:

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 202 lists the supported data types and their representation in the CSV file.

Table 202.

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.
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.
Install the DB2eImport.prc to the Palm OS device using the Palm Install Tool.
Perform a HotSync operation to complete the installation of the imported tables and DB2eImport.prc.
Start the DB2eImport program on the Palm OS device to complete the import.

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 export data from the Palm OS device:

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.
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

Overview of the CLP application

Related reference

CLP commands