Suppose you want to transfer data from a db2 database table to another db2 database or within the same database, one of the solution is to use db2 export / import command . Using db2 export / import you can move your data to excel also.
DB2 export command exports data selected by SQL statement from a table or view to an external files in the format of del, asc (ascii format), wsf, ixf etc.. del - delimited , wsf - worksheet format , ixf - Integration Exchange Format.
The DEL format uses delimiter and column separator and decimal point. The default string delimiter is double quote " and column separator is comma(,) and Decimal Point is dot(.), you can also specify your own string delimiter & column separator, decimal point. Another format supported by DB2 is IXF (Integration Exchange Format). It is a generic relational database exchange format which supports an end-of-record delimiter . IXF architecture supports to exchange of relational database structures and data. IXF is used for transferring data across platforms. The following examples illustrates the use of Export command. You can see more about db2 import command at db2 import.
Example 1 : Export all rows in the Employee table to the file emp.ixf with message file msgs.txt for writing the messages or warnings
db2 export to emp.ixf of ixf messages msgs.txt select * from employee
Example 2:The following command exports all rows in the Employee table to the file emp.del.
db2 export to emp.del of del select * from employee
Example 3: The following command exports all rows in the Employee table to the file employee.del using the column delimiter semi colon(;) and string delimiter single quote (') and the decimal point comma,
db2 export to employee.del of del modified by chardel'' coldel; decpt, select * from employee
Output of the above is command is as follows.
'1002';'XYZ';+00050003,00;+00060000,00
'1003';'ABC';+00048750,00;+00055550,00
Some of the Parameters applicable to DB2 Export command:
MESSAGES message-file : Specifies the destination file to be created for writing warnings and error messages which is generated during an export operation. If the file already exists, then warnings & errors are appended. If msg file is omitted, the messages are written on the console.
In the Example 1, msgs.txt is created for information . Sample msgs.txt file has the messages of SQL3104N
The Export utility is beginning to export data to file "emp.ixf". , SQL3105N The Export utility has finished exporting "2" rows.
METHOD N col-name : Specifies 1 or more column names to be written in the output file. If it is not specified, the column names in the table are used. This is valid only for WSF and IXF files .
Example : db2 export to emp.ixf of ixf method N (empcode , empname) messages msgs.txt select * from employee
MODIFIED BY filetype-mod : Specifies file type of DEL , IXF , WSF
0 comments:
Post a Comment