db2基础操作
1.启动
db2 start
2.连接
db2 connect to [dbname] user [username] using [password]
3.列出库
db2 list db directory
4.列出表
db2 list tables
5.查询库
db2 “select * from [tablename] where [conditions]”
6.生成建表脚本
db2look –d pmp –e –o f:\export\init.sql
7.建表(导入表结构,无数据)
db2 –tvf f:\export\init.sql
8.导出数据
db2move [dbname] export
#with filter
db2move[dbname] export -tn [tablename] -tc [table-definer] -l [path]
9.导入数据 用import(新建或填充)load(仅填充)
db2move [dbname] import
10.导出csv
db2 “export to d:\data\tab1.ixf of ixf lobs to d:\data\ lobfile lobs modified by lobsinsepfiles messages d:\data\tab1.msg select * from schema_name.table_name”
schema_name 是表所属,table_name是表名, lobsinsepfiles 或 lobsinfile 是生成lob文件 前一个是生成每个,后面是生成到一个文件中
11.导出表结构(所有表)
db2look –d [dbname]–e –o f:\export\init.sql
执行建表脚本
db2 –tvf f:\export\init.sql
12.查看版本
db2licm -l
db2导出所有表至csv
1.linux
for t in $(db2 -x "select rtrim(tabschema) || '.' || rtrim(tabname) from syscat.tables where tabschema = 'DB2INST1'"); do db2 "export to $t.csv of del modified by coldel, select * from $t"; done
会把DB2INST1这个schema下面的所有表 以csv的格式输出到当前目录 路径和命名方式可以自行调整
2.win
windows的for语句
for {%% | %} in () do []
但因为对bat脚本不太熟悉,浅浅测试了一下,会把b2 -x “select rtrim(tabschema) || ‘.’ || rtrim(tabname) from syscat.tables where tabschema = ‘DB2INST1′”这个部分解析成字符串
所以我的之前操作是 先把表名保存到单独的txt文件,再进行循环,后来找chatgpt帮我写了一下
for /f %t in (test.txt) do db2 "export to %t.csv of del modified by coldel, select * from %t"
@echo offfor /f "tokens=2 delims==" %%a in ('db2 "select tabname from syscat.tables where tabschema='SCHEMA_NAME'"') do (db2 "export to %%a.csv of del modified by nochardel coldel, select * from %%a")pause
参考
https://stackoverflow.com/questions/56691654/export-data-from-db2-from-all-tables-in-n-schemas-into-csv-with-column-names
IBM Documentation