Tutorial on using special delimiters to process data in DB2, db2 delimiters

Tutorial on using special delimiters to process data in DB2, db2 delimiters

I received a request a few days ago, using the special symbol '|' as the field separator. I thought it was not done for a long time. Later, under the guidance of the team leader, I checked the script before the project. The inspiration for me is that when the project encounters strange requirements, it is best to refer to the previous code, because these problems may already be encountered by colleagues. There is no answer on the internet for such questions.

First, let's take a look at the syntax of db2's data import and export, that is, the usage of export, import, and load.

-Export: used for data export. The general syntax is "export to filename of filetype select colname from table where xx". But you will modify some character delimiters and encoding and format them, therefore, the syntax is a little more complex.

-Import: used for data import. The general syntax is import from filename of filetype insert colname into table where xx. Other aspects are the same as export.

-Load: used for loading data. The syntax is more complex than import. For more information, see the db2 document.

For details, refer to this article DB2 data Import (Import) Export (Export) (Load)

After briefly introducing the syntax, let's go back to our focus. How can we use the special symbol '|' to export data using a field delimiter?

First, we can see that db2 provides charsel as the field delimiter, but this can only use single characters as the separator. '|' is a string and cannot be used. My approach is, perform preprocessing in the query statement and then export the statement. Because db2 statements are generally used in scripts for processing, the processing idea is attached here.

1. Obtain the field name.

Colnames = select colname from syscat. columns where tabname = '$ {tabname}' and tabschema = '$ {tabschema}' order by colno

2. Convert the field name from a column to a row and use '|' as the delimiter to save it in the sh variable.

Result = SELECT listparts (colname, '|') c from $ {colnames}

3. Use the export Statement to export data

Select $ {result} as field from "$ {tabschema}. $ {tabname}" where xxx

Due to the problem of md display, the actual code is different from the one posted here. You can refer to my github Script: export_data.sh to export data.

This is the processing of the export, and the import can be reversed. The processing logic is the same, that is, how to eliminate special delimiters.