Updated March 10, 2023
Introduction to DB2 export
DB2 provides the different kinds of commands to the user; the DB2 export is one of the commands that the DB2 provides. In which that export command is useful to export the data from one database to another external file format. We can export them by using the SQL statement or by using hierarchical data. Basically, the export command is used to take the backup of our database as per the user requirement. Sometimes system failure or system may crash, or sometimes database may be removed from the system or application. At that time, we can use these backup files to restore all data or information.
Syntax
export to specified file name of specified file type select * from specified table name
Explanation
In the above syntax, we use export with different parameters as follows.
Specified file name: It is a database file name that we created to store the backup in the specified format that we need.
Select: Select the statement we used to fetch the data from the specified table that we need.
How does the export command work in DB2?
Now let’s see how the export command works in DB2 as follows.
First, we need some permission to operate the export command as follows.
- We need database access authority on each table and view.
- We also need a database access authority.
- We also need control and select privilege on each table and view.
After access permission, the export command uses different parameters for execution as follows.
1.To filename:
Determines the name of the document to which information is to be exported. On the off chance that the total way to the record isn’t indicated, the export utility uses the currently available directory and the default drive as the objective. On the off chance that the name of a document that exists is determined, the export utility overwrites the substance of the record; it doesn’t affix the data.
2. Of filetype:
It is used to specify the output file format by using the two following parameters as follows.
DEL- DEL means delimited, and it is used in ASCII format, used by file manager programs.
IXF- That means Integration Exchange Format; it is only for PC that means binary format.
3. LOBS TO lob-path:
Determines at least one way to catalogs in which the LOB records are to be put away. There should be, in any event, one document for every LOB way, and each record should contain at any rate one LOB. The most extreme number of ways that can be indicated is 999. This setting verifiably initiates the LOBSINFILE conduct.
4. LOBFILE filename:
It indicates at least one base document name for the LOB records. At the point when the namespace is depleted for the primary name, the subsequent name is utilized, etc. This setting certainly initiates the LOBSINFILE conduct. At the point when you make LOB documents during an export activity, record names are built by affixing the current base name from this rundown to the current way (from throw way). At that point, you should attach a 3-digit arrangement number to begin, and the three-character identifier throws.
5. XML TO xml-path:
Specifies at least one way to catalogs in which the XML documents are to be put away. There is at any rate one record for every XML way, and each document contains in any event one XQuery Data Model (XDM) case. Assuming more than one way is indicated, XDM examples are circulated equitably among the ways.
6. XML FILE filename:
It determines at least one base record name for the XML documents. At the point when the namespace is depleted for the main name, the subsequent name is utilized, etc.
At the point when you make XML documents during an export activity, record names are built by attaching the current base name from this rundown to the current way (from xml-way). At that point, you should affix a 3-digit grouping number and the three-character identifier xml.
7. MESSAGES message-file:
It determines the objective for notice and blunder messages that happen during an export activity. If the record exists, the export utility attaches the data. On the off chance that message-record is discarded, the messages are kept in touch with standard yield.
8. Select-statement:
It determines the SELECT or XQUERY explanation that profits the information to be sent out. On the off chance that the proclamation causes a mistake, a message is kept in touch with the message record (or to standard yield). On the off chance that the SELECT proclamation is as SELECT * FROM specified table name and the table contains certainly covered-up sections, you should unequivocally indicate whether information for the secret segments is remembered for the export activity.
Examples of DB2 export
Let’s see the different examples of export commands as follows.
First, create a new table as follows.
create table emp (emp_id int not null, emp_first_name varchar(60), emp_last_name varchar(60), emp_email varchar(40), PRIMARY KEY(emp_id));
Explanation
The end output of the above statement we illustrate by using the following screenshot.
After that, insert some records into the emp for export operation and perform the export command as follows.
db2 export to test.ixf of ixf messages file1.txt select * from emp
Explanation
In the above statement, we use export utility to export records from the emp table to test.ixf file format as shown. Before the execution of the above statement, we must connect to the specified database. The end output of the above statement we illustrate by using the following screenshot.
Rules and regulation for export in DB2
- Make certain to finish every table activity and delivery all locks before you start a fare activity. This progression should be possible by giving a COMMIT after closing all cursors opened WITH HOLD or giving a ROLLBACK.
- Table assumed names could be utilized in the SELECT articulation.
- You may experience the SQL message when it doesn’t appear to be appropriate.
- The messages set in the message document incorporate the data gotten back from the message recovery administration. Each message starts on another line.
- The export command stores the not null values.
Conclusion
We hope from this article you learn the DB2 export command. From the above article, we have learned the basic syntax of the export command, and we also see different examples of export commands. From this article, we learned how and when we use the DB2 export command.
Recommended Articles
This is a guide to DB2 export. Here we discuss the basic syntax of the export command, and we also see different examples of export commands. You may also have a look at the following articles to learn more –
