CODEWITHSUNDEEP
db2
Rahul Patidar
Rahul Patidar

Reputation: 209

Exporting DB2 data in '|' delimitted Format

I have requirement in which i need to export db2 tables data in '|' delimited file. i tried multiple command but i'm not able to export data in | delimited file. I am using db2 through web URL and not using any command-line to connect. I have tried below commands.

    select
    strip(char(col1)) ||'|'
    ||strip(char(col2)) ||'|'
    ||strip(char(col3)) ||'|'
     from tablename ;

also I have tried using export Command.

EXPORT TO emp.ixf of IXF select * FROM  schemaname.tablename fetch first 10 rows only

Tried below command also.

    export to tab.rel of del modified by colde10x09 select * from  schemaname.tablename limit 10

But Nothing worked for me. Can someone please guide me how should i Export this data in | delimited format?

Upvotes: 0

Views: 362

Answers (1)

mao
mao

Reputation: 12267

Please try using 0x7C (the hex value for the ASCII vertical bar / vertical line symbol), assuming that you want that character in ASCII. Do check whether your character data has this value in char/varchar type columns.

E.g this command to the Db2 CLP (command line processor):

export to tab.rel of del modified by colde10x7C select * from EDW_ODS.ODS_GST_EWB_PARTA_ITEM_DTLS

Note that you cannot run the above as an SQL statement, because export is not SQL, it is a command understood only by the Db2 CLP.

You can however use ADMIN_CMD() stored procedure with the export command, if your Db2-server runs on Linux/Unix/Windows.

If your Db2-server runs on Z/OS or i-series, this is not available, but instead special purpose export tools are available for those platforms to dump data in many formats. Always tag your Db2-server platform when asking for help wiht Db2. You can also use plain SQL select for smaller amounts of data, as long as you get your syntax correct and know your source and target encodings.

For Db2-LUW servers, you can read more about using the ADMIN_CMD procedure with SQL and EXPORT at this link. Note that when you use ADMIN_CMD , any paths that you specify are relative to the Db2-server (not to your workstation).

Upvotes: 1

Related Questions