The IBM DB2 database was first released in 1987, and since then, it has been upgraded and redesigned. Its current stable release (Db2 11.5.5) is a robust cloud-enabled database on Windows Servers and various Unix-like operating systems. Some EHRs and cloud-based healthcare databases have their data stored in DB2 databases. If you need to perform a DB2 database extraction for research, analysis or archiving, here are the basic steps you need to take.
1. Choose Your Export Format
It is possible to export DB2 database data into various formats. For instance, you may choose to export data into
- Comma-separated values (CSV)
- Tab-separated values
- Microsoft Excel (xlsx)
- Structured Query Language (SQL) script
- eXtensible Markup Language (XML)
- Javascript Object Notation (JSON)
- Integrated file exchange (IXF)
Some formats like XML, CSV, IXF and tab-separated values can be created directly using the Export command. Exporting to simple text-based formats like CSV allows you to keep the data in a form that almost any tool or software can read, load, and transform. So it’s advisable to work with the CSV format and any other filetype you choose.
2. Connect to the Database Server
Unless you’re working on the server where the database is installed, you need to connect to the database from your workstation. The simplest method is to connect through a command-line interface. But you can also connect through third-party apps and tools.
To connect, you need the following details about the DB2 database including:
- Hostname
- User ID
- Password
You can obtain connection details for a cloud-based database from the web console of the cloud service provider.
Ensure that you have a supported Db2 driver package installed on your workstation. Then add the relevant entries to the db2dsdriver.cfg configuration file. You can find more information on connecting to a Db2 database in IBM’s support section.
An example command for connecting to Db2 database server with the following parameters:
- URL: ehr.provider.com:5021/db2ehr
- User ID: user01
- Password: pwd01
is as follows: CONNECT to ehr.provider.com:5021/db2ehr USER user01 USING pwd01
3. Extract the Data
To extract the data from the database table, we will use the Export command. You can also use the IBM Studio GUI to extract data.
The following command exports data from the patient table into a delimited format. The comma serves as a column delimiter, while the full stop is a decimal point.
db2 export to patient.csv of del modified by chardel” colder, decpt.
select * from patient
To avoid unnecessary errors, ensure that you completely release all table locks after completing any other table operations in the database.
4. Close the Database Connection
If you are not performing any other task on your database, it is better to close the connection. After the export is completed, the connection to the database will be closed. However, you can close the connection to the database explicitly.
Type DISCONNECT or TERMINATE at the command prompt.
5. Inspect the Data
To ensure that the data you exported is accurate and complete, use a spreadsheet program like Microsoft Excel, Google Sheets, or LibreOffice Calc to view the .csv file.
That is a simple way to extract data from a Db2 database. In practice, healthcare data is complex, with multiple joins and relationships. If you need to extract data for archiving or migration, consider working with a health data migration specialist.
Contact MediQuant for DB2 Database Extraction
Give us a call at MediQuant at 844.286.8683 or visit our contact page to book a free consultation to discuss your DB2 database extraction and migration needs.