从db2中将N个模式中所有表中的数据导出到具有列名称的CSV中 [英] export data from db2 from all tables in N schemas into CSV with column names

问题描述

我正在尝试使用列名将一堆DB2表导出到CSV.我看不出有任何直接的方法可以做到这一点.我遵循了此以获取所需的数据.但是我必须在数百个表上执行该操作.有没有一种方法可以动态获取给定N个模式名称的所有列和表?

I'm trying to export a bunch of DB2 tables to CSV, with column names. I don't see any straight forward way to do this. I followed this to get the data I want. But I have to execute that over hundreds of tables. Is there a way to dynamically get all the columns and tables given N schema names?

我还尝试了

I also tried this which exports all tables to csv in a schema but this doesn't give me column names. So if someone could show me show to change this script to get column names in the CSVs my work is done.

服务器正在运行:Red Hat Linux服务器.

The server is running: Red Hat Linux Server.

推荐答案

使用文件

以下db2命令生成导出脚本:

Using files

The following db2 command generates the export script:

export to exp.sql of del modified by nochardel
select
  x'0a'||'export to file_header of del modified by nochardel VALUES '''||columns||''''
||x'0a'||'export to file_data of del messages messages.msg select '||columns||' from '||tabname_full
||x'0a'||'! cat file_header file_data > '||tabname_full||'.csv'
from
(
select rtrim(c.tabschema)||'.'||c.tabname as tabname_full, listagg(c.colname, ', ') as columns
from syscat.tables t
join syscat.columns c on c.tabschema=t.tabschema and c.tabname=t.tabname
where t.tabschema='SYSIBM' and t.type='T'
group by c.tabschema, c.tabname
--fetch first 10 row only
)
;

最好将上面的命令放置到某些文件中，例如gen_exp.sql并运行它以生成导出脚本:

It's better to place the command above to some file like gen_exp.sql and run it to produce the export script:

db2 -tf gen_exp.sql

每个表的导出脚本exp.sql包含3个命令:
* db2 export命令以逗号分隔的列列表
* db2 export命令获取表数据
*串联命令，将以上两个输出都收集到一个文件中

The export script exp.sql consists of 3 commands for each table:
* db2 export command to get a comma separated list of columns
* db2 export command to get table data
* concatenation command to collect both outputs above to a single file

您可以按以下方式运行此脚本:

You run this script as follows:

db2 -vf exp.sql -z exp.sql.log

使用管道

gen_exp_sh.sql:

Using pipe

gen_exp_sh.sql:

export to exp.sh of del modified by nochardel
select
  x'0a'||'echo "'||columns||'" > '||filename
||x'0a'||'db2 "export to pipe_data of del messages messages.msg select '||columns||' from '||tabname_full||'" >/dev/null 2>&1 </dev/null &'
||x'0a'||'cat pipe_data >> '||filename
from
(
select
  rtrim(c.tabschema)||'.'||c.tabname as tabname_full
, rtrim(c.tabschema)||'.'||c.tabname||'.csv' as filename
, listagg(c.colname, ', ') as columns
from syscat.tables t
join syscat.columns c on c.tabschema=t.tabschema and c.tabname=t.tabname
where t.tabschema='SYSIBM' and t.type='T'
group by c.tabschema, c.tabname
--fetch first 10 row only
)
;

运行如下:

db2 -tf gen_exp_sh.sql

导出shell脚本exp.sh由每个表的3个命令组成:
* echo命令可将以逗号分隔的列列表写入文件
* db2 export命令获取表数据到管道(从后台启动)
*简单的cat命令可从管道中读取数据并将数据添加到具有列列表的同一文件

The export shell script exp.sh consists of 3 commands for each table:
* echo command to write a comma separated list of columns to a file
* db2 export command to get table data to a pipe (started in a background)
* simple cat command to read from the pipe and add data to the same file with the columns list

用法:
您必须先创建管道，然后创建源(dot space script标记-这很重要)导出脚本:

Usage:
You must create the pipe first and source (dot space script notation - it's important) the export script afterwards:

mkfifo pipe_data
db2 connect to mydb ...
. ./exp.sh
rm -f pipe_data

这篇关于从db2中将N个模式中所有表中的数据导出到具有列名称的CSV中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！