将结果从 DB2 导出到 CSV，包括通过 bash 的列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/51100182/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Export results from DB2 to CSV including column names via bash
提问by Sjostrom
This question branches off a question already asked.
这个问题从一个已经问过的问题分支出来。
I want to make a csv file with the db2 results including column names.
我想用包含列名的 db2 结果制作一个 csv 文件。
EXPORT TO ...
SELECT 1 as id, 'COL1', 'COL2', 'COL3' FROM sysibm.sysdummy1
UNION ALL
(SELECT 2 as id, COL1, COL2, COL3 FROM myTable)
ORDER BY id
While this does work, I am left with an unwanted column and rows of 1 and 2's
虽然这确实有效，但我留下了一个不需要的列和 1 和 2 的行
Is there a way to do this via the db2 command or a full bash alternative without redundant columns while keeping the header at the top?
有没有办法通过 db2 命令或没有冗余列的完整 bash 替代方法来做到这一点，同时将标题保持在顶部？
e.g.
例如
Column 1 Column 2 Column 3
data 1 data 2 data3
... ... ...
Column 1 Column 2 Column 3
data 1 data 2 data3
... ... ...
instead of:
代替：
1 Column 1 Column 2 Column 3
2 data 1 data 2 data3
2 ... ... ...
1 Column 1 Column 2 Column 3
2 data 1 data 2 data3
2 ... ... ...
回答by Stavr00
All the answers I've seen use two separate export statements. The first generates the column headers:
我见过的所有答案都使用两个单独的导出语句。第一个生成列标题：
db2 "EXPORT TO /tmp/header.csv of del
SELECT
SUBSTR(REPLACE(REPLACE(XMLSERIALIZE(CONTENT XMLAGG(XMLELEMENT(NAME c,colname)
ORDER BY colno) AS VARCHAR(1500)),'<C>',', '),'</C>',''),3)
FROM syscat.columns WHERE tabschema=${SCHEMA} and tabname=${TABLE}"
then the query body
然后是查询正文
db2 "EXPORT TO /tmp/body.csv of del
SELECT * FROM ${SCHEMA}.${TABLE}"
then
然后
cat /tmp/header.csv /tmp/body.csv > ${TABLE}.csv
回答by Paul Vernon
This would work for your simple case
这适用于您的简单案例
EXPORT TO ...
SELECT C1, C2, C3 FROM (
SELECT 1 as id, 'COL1' as C1, 'COL2' as C2, 'COL3' as C3 FROM sysibm.sysdummy1
UNION ALL
(SELECT 2 as id, COL1, COL2, COL3 FROM myTable)
)
ORDER BY id
Longer term, EXTERNAL TABLEsupport (already in Db2 Warehouse) which has the INCLUDEHEADERoption is (I guess) going to appear in Db2 at some point.
从长远来看，EXTERNAL TABLE具有INCLUDEHEADER选项的支持（已经在 Db2 Warehouse 中）（我猜）将在某个时候出现在 Db2 中。
回答by Sjostrom
If you just want the headers for the extracted data and you want those headers to always be on top and you want to be able to change the names of those headers so it appears more user-friendly and put it all into a CSV file.
如果您只想要提取数据的标题，并且您希望这些标题始终位于顶部，并且您希望能够更改这些标题的名称，使其看起来更加用户友好并将其全部放入 CSV 文件中。
You can do the following:
您可以执行以下操作：
# Creates headers and new output file
HEADERS="ID,USERNAME,EMAIL,ACCOUNT DISABLED?"
echo "$HEADERS" > "$OUTPUT_FILE"
# Gets results from database
db2 -x "select ID, USERNAME, DISABLED FROM ${SCHEMA}.USER WHERE lcase(EMAIL)=lcase('$USER_EMAIL')" | while read ID USERNAME DISABLED ;
do
# Appends result to file
echo "${ID},${USERNAME},${USER_EMAIL},${DISABLED}" >> "$OUTPUT_FILE"
done
No temporary files or merging required.
不需要临时文件或合并。
回答by AngocA
I wrote a stored procedure that extracts the header via describe command. The names can be retrieved from a temporary table, and be exported to a file. The only thing it is still not possible is to concatenate the files via SQL, thus a cat to both file and redirection to another file is necessary as last step.
我写了一个存储过程，通过describe 命令提取标题。可以从临时表中检索名称，并将其导出到文件中。唯一仍然不可能的是通过 SQL 连接文件，因此最后一步需要一个 cat 到文件和重定向到另一个文件。
CALL DBA.GENERATE_HEADERS('SELECT * FORM SYSCAT.TABLES') @
EXPORT TO myfile_header OF DEL SELECT * FROM SESSION.header @
EXPORT TO myfile_body OF DEL SELECT * FORM SYSCAT.TABLES @
!cat myfile_header myfile_body > myfile @
The code of the stored procedure is at: https://gist.github.com/angoca/8a2d616cd1159e5d59eff7b82f672b72More information at: https://angocadb2.blogspot.com/2019/11/export-headers-of-export-in-db2.html.
存储过程的代码在：https: //gist.github.com/angoca/8a2d616cd1159e5d59eff7b82f672b72更多信息在：https: //angocadb2.blogspot.com/2019/11/export-headers-of-export-in- db2.html。
回答by mao
Db2 for Linux/Unix/Windows lacks a (long overdue) simple opting (to the export command) for this common requirement.
适用于 Linux/Unix/Windows 的 Db2 缺少针对此常见要求的（早该）简单选择（导出命令）。
But using the bash shell you can run two separate exports (one for the column-headers, the other for the data ) and concat the results to a file via an intermediate named pipe.
但是使用 bash shell，您可以运行两个单独的导出（一个用于列标题，另一个用于数据）并通过中间命名管道将结果连接到文件中。
Using an intermediate named pipe means you don't need two flat-file copies of the data.
使用中间命名管道意味着您不需要数据的两个平面文件副本。
It is ugly and awkward but it works.
这是丑陋和尴尬，但它的工作原理。
Example fragment (you can initialize the variables to suit your environment):
示例片段（您可以初始化变量以适合您的环境）：
mkfifo ${target_file_tmp}
(( $? != 0 )) && print "\nERROR: failed to create named pipe ${target_file_tmp}" && exit 1
db2 -v "EXPORT TO ${target_file_header} of del SELECT 'COL1', 'COL2', 'COL3' FROM sysibm.sysdummy1 "
cat ${target_file_header} ${target_file_tmp} >> ${target_file} &
(( $? > 0 )) && print "Failed to append ${target_file} . Check permissions and free space" && exit 1
db2 -v "EXPORT TO ${target_file_tmp} of del SELECT COL1, COL2, COL3 FROM myTable ORDER BY 1 "
rc=$?
(( rc == 1 )) && print "Export found no rows matching the query" && exit 1
(( rc == 2 )) && print "Export completed with warnings, your data might not be what you expect" && exit 1
(( rc > 2 )) && print "Export failed. Check the messages from export" && exit 1
