How to execute SQL statements and export table records in DB2

How can I avoid an error when executing SQL statements in DB2? This is a problem that many people have mentioned. The following describes how to execute SQL statements in DB2 and export table records for your reference.

When writing DB2 SQL statements on the console, many people habitually press enter to wrap the line, and an error is reported when the Result Statement is executed directly. This is because the default delimiter of DB2 is space,

If you want to write a line feed statement, you must add \ at the end of each line of statement, \ indicates line feed.
Another method is to connect to DB2 and input the db2-t command, so that \ is not required at the end of each line of statements \. you only need to press enter to wrap the line. After the statement is written, enter a semicolon (;) to submit the statement for running.
We recommend that you use the following method to execute SQL statements in DB2 and export table records.

 

JXBI_js1: / db2home / mart796> touch crw.txt
JXBI_js1: / db2home / mart796> db2 CONNECT TO jxmart USER ***** USING ******
 
Database Connection Information
 
Database server = DB2 / AIX64 8.2.5
SQL authorization ID = MART796
Local database alias = JXMART
 
### db -t command indicates that the statement allows line breaks, with a semicolon; as the terminator
JXBI_js1: / db2home / mart796> db2 -t
(c) Copyright IBM Corporation 1993, 2002
Command Line Processor for DB2 SDK 8.2.5
 
You can issue database manager commands and SQL statements from the command
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd
 
For general help, type:?.
For command help, type:? Command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
 
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
 
For more detailed help, refer to the Online Reference Manual.
 
db2 => EXPORT TO "/db2home/mart796/crw.txt" OF DEL MODIFIED BY coldel0x09
db2 (cont.) => SELECT RN_USER_ID,
db2 (cont.) => HIS_USER_ID,
db2 (cont.) => RN_USER_NUMBER,
db2 (cont.) => HIS_USER_NUMBER,
db2 (cont.) => RN_BASE_COUNTY_CODE,
db2 (cont.) => RN_BASE_PLAN_ID,
db2 (cont.) => RN_CREATE_DATE
db2 (cont.) => FROM JA.MARTD_DMRN_USER_MS
db2 (cont.) => WHERE RN_DATE = '2008-07-01'
db2 (cont.) =>;
SQL3104N The Export utility is beginning to export data to file
"/db2home/mart796/crw.txt".
 
SQL3105N The Export utility has finished exporting "554848" rows.
 
Number of rows exported: 554848
db2 => quit;
DB20000I The QUIT command completed successfully.

At this point, a total of 584848 records are exported. The next step is to get the records and import them to the local ORACLE database. This is relatively simple.
 

Usage of the DB2 create server statement

Common DB2 management commands

In-depth discussion of DB2 table connection principles

Common DB2 cycle usage

Implementation of creating a database in DB2