db2 修改端口 db2修改语句

目录

一，DDL

1，REORG

2，修改字段长度

3，修改主建

4，添加字段

5，添加注释

6，添加/删除非空约束

7，删除字段

8，分区

1)TIMESTAMP 类型按月自增分区

2)（CHARACTER ）CHAR 类型按月自增分区

3）查看表分区

4）删除表分区

5）添加表分区

9，导出表DDL  = > db2look

二，其他

1，全局变量

2，查看索引

3，查看表信息：字段，结构，注释

4，索引序列

5，rank() over()

6，日期

7，执行脚本

8，导入导出

9，编码问题查看

10，null处理

11，查询表所有COLNAME

12，实现wm_concat ，行转列

1)获取表字段colname

2)先把要转换的字段名变成xml 元素

3)用xmlagg函数把分组后的字段连接

4)替换A标签

5)将表字段clob类型转string类型

13，分页

14，获取前几列

15，索引

---

一，DDL

1，REORG

改变结构需要REORG

--脚本里
REORG table tableName;

--数据库界面工具
CALL SYSPROC.ADMIN_CMD('REORG table tableName');

2，修改字段长度

alter table tableName alter columnName set data type varchar(256);

3，修改主建

alter table tableName drop primary key;

alter table tableName add constraint PK_tableName
 primary key(columnName1,columnName2);

4，添加字段

alter table tableName add column columnName varchar(8);
alter table tableName add column columnName varchar(8) not null;
alter table tableName add column columnName varchar(8) default '1' not null;

5，添加注释

comment on table tableName is '表注释';
comment on cplumn tableName.columnName is '字段注释';

6，添加/删除非空约束

alter table tableName alter columnName set  not null;
alter table tableName alter columnName drop not null;

7，删除字段

alter table tableName drop column columnName;

8，分区

1)TIMESTAMP 类型按月自增分区

CREATE TABLE T1(
  create_time TIMESTAMP(6)
)
PARTITON BY RANGE(create_time)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITON P1 VALUES LESS THAN(TO_TIMESTAMP('2022-01-01 00:00:00','YYYY-MM-DD hh24:mi:ss'))
);


CREATE TABLE T1(
  create_time TIMESTAMP
)
PARTITON BY RANGE(create_time)
(starting minvalue,
 starting '1/1/2020' ending '12/31/2050' every 1 months,
 ending maxvalue
);

2)（CHARACTER ）CHAR 类型按月自增分区

CHARACTER ，CHAR是一样

CREATE TABLE T1(
  create_time CHARACTER(10),
  create_time1 TIMESTAMP(6) GENERATED ALWAYS AS(TO_TIMESTAMP(create_time,'YYYYMMSS'))
)
PARTITON BY RANGE(create_time)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITON P1 VALUES LESS THAN(TO_TIMESTAMP('2022-01-01 00:00:00','YYYY-MM-DD hh24:mi:ss'))
);



CREATE TABLE T1(
  create_time  CHARACTER(10),
  create_time1 TIMESTAMP(6) GENERATED ALWAYS AS(TO_TIMESTAMP(create_time,'YYYYMMSS'))
)
PARTITON BY RANGE(create_time)
(starting minvalue,
 starting '1/1/2020' ending '12/31/2050' every 1 months,
 ending maxvalue
);

3）查看表分区

SELECT * FROM SYSCAT.DATAPARTITIONS WHERE TABNAME='表名';


SELECT DATAPARTITIONNAME,LOWVALUE,HIGHVALUE FROM SYSCAT.DATAPARTITIONS WHERE TABNAME='TEST1';
PART0    MINVALUE                        '2020-01-01-00.00.00.000000'
PART1    '2020-01-01-00.00.00.000000'    '2020-04-01-00.00.00.000000'
PART2    '2020-04-01-00.00.00.000000'    '2020-07-01-00.00.00.000000'
PART3    '2020-07-01-00.00.00.000000'    '2020-10-01-00.00.00.000000'
PART4    '2020-10-01-00.00.00.000000'    '2020-12-31-00.00.00.000000'
PART5    '2020-12-31-00.00.00.000000'    MAXVALUE

4）删除表分区

第一步：把表TEST1 的PART5 分区卸载到新的表TEST1_PART5中

ALTER TABLE 表名 DETACH PARTITION 分区名 INTO TABLE 新表名（推荐当前表名_分区名）

ALTER TABLE TEST1 DETACH PARTITION PART5 INTO TABLE TEST1_PART5

第二步：删除表

DROP TABLE TEST1_PART5

实例：

#1,原有表分区
SELECT DATAPARTITIONNAME,LOWVALUE,HIGHVALUE FROM SYSCAT.DATAPARTITIONS WHERE TABNAME='TEST1';
PART0    MINVALUE                        '2020-01-01-00.00.00.000000'
PART1    '2020-01-01-00.00.00.000000'    '2020-04-01-00.00.00.000000'
PART2    '2020-04-01-00.00.00.000000'    '2020-07-01-00.00.00.000000'
PART3    '2020-07-01-00.00.00.000000'    '2020-10-01-00.00.00.000000'
PART4    '2020-10-01-00.00.00.000000'    '2020-12-31-00.00.00.000000'
PART5    '2020-12-31-00.00.00.000000'    MAXVALUE

#2,卸载删除表TEST1的PART5分区
ALTER TABLE TEST1 DETACH PARTITION PART5 INTO TABLE TEST1_PART5
DROP TABLE TEST1_PART5

#3,再次查询表分区
SELECT DATAPARTITIONNAME,LOWVALUE,HIGHVALUE FROM SYSCAT.DATAPARTITIONS WHERE TABNAME='TEST1';
PART0    MINVALUE                        '2020-01-01-00.00.00.000000'
PART1    '2020-01-01-00.00.00.000000'    '2020-04-01-00.00.00.000000'
PART2    '2020-04-01-00.00.00.000000'    '2020-07-01-00.00.00.000000'
PART3    '2020-07-01-00.00.00.000000'    '2020-10-01-00.00.00.000000'
PART4    '2020-10-01-00.00.00.000000'    '2020-12-31-00.00.00.000000'

#4,卸载PART4
ALTER TABLE TEST1 DETACH PARTITION PART4 INTO TABLE TEST1_PART4
SELECT DATAPARTITIONNAME,LOWVALUE,HIGHVALUE FROM SYSCAT.DATAPARTITIONS WHERE TABNAME='TEST1';
PART0    MINVALUE                        '2020-01-01-00.00.00.000000'
PART1    '2020-01-01-00.00.00.000000'    '2020-04-01-00.00.00.000000'
PART2    '2020-04-01-00.00.00.000000'    '2020-07-01-00.00.00.000000'
PART3    '2020-07-01-00.00.00.000000'    '2020-10-01-00.00.00.000000'

#5,查看PART4卸载表TEST1_PART4
SELECT DATAPARTITIONNAME,LOWVALUE,HIGHVALUE FROM SYSCAT.DATAPARTITIONS WHERE TABNAME='TEST1_PART4';
PART0        

#6,删除卸载表
DROP TABLE TEST1_PART4

5）添加表分区

ALTER TABLE 表名 ADD PARTITION "分区名" STARTING '开始日期' ending  '截止日期' ;

ALTER TABLE TEST1 ADD PARTITION "PART4" STARTING '10/1/2020' ending  '12/31/2020' ;
ALTER TABLE TEST1 ADD PARTITION "PART5" STARTING '1/1/2021' ending  '4/1/2021';
ALTER TABLE TEST1 ADD PARTITION "PART6"  ending  MAXVALUE;

#查看添加后表TEST1分区情况
SELECT DATAPARTITIONNAME,LOWVALUE,HIGHVALUE FROM SYSCAT.DATAPARTITIONS WHERE TABNAME='TEST1';
PART0    MINVALUE                       '2020-01-01-00.00.00.000000'
PART1    '2020-01-01-00.00.00.000000'    '2020-04-01-00.00.00.000000'
PART2    '2020-04-01-00.00.00.000000'    '2020-07-01-00.00.00.000000'
PART3    '2020-07-01-00.00.00.000000'    '2020-10-01-00.00.00.000000'
PART4    '2020-10-01-00.00.00.000000'    '2020-12-31-00.00.00.000000'
PART5    '2021-01-01-00.00.00.000000'    '2021-04-01-00.00.00.000000'
PART6    '2021-04-01-00.00.00.000000'    MAXVALUE

9，导出表DDL  = > db2look

用法：

db2look -d Database_Name -e -t TABLE_NAME -o t1.sql
db2look -d Database_Name -e -t TABLE_NAME TABLE_NAME2 -o t2.sql

说明：

Syntax: db2look -d DBname [-e] [-xs] [-xdir Path] [-u Creator] [-z Schema]
                          [-t Tname1 Tname2...TnameN] [-tw Tname] [-xdep] [-xddep]
                          [-n Nname1 Nname2...NnameN] [-nw Nname]
                          [-o Fname] [-a] [-m] [-c] [-r] [-l] [-x] [-xd] [-f] [-h]
                          [-fd] [-td x] [-noview] [-i userID] [-w password]
                          [-v Vname1 Vname2 ... VnameN] [-dp] [-ct]
                          [-wrapper WrapperName] [-server ServerName] [-nofed]
                          [-wlm] [-ap] [-mod] [-cor] [-wrap] [-noimplschema] [-nostatsclause]
                          [-wrapper WrapperName] [-server ServerName][-fedonly] [-nofed]

        db2look [-h]

        -d: Database Name: This must be specified

        -e: Extract DDL file needed to duplicate database
       -xs: Export XSR objects and generate a script containing DDL statements
     -xdir: Path name: the directory in which XSR objects will be placed
        -u: Creator ID: If -u and -a are both not specified then $USER will be used
        -z: Schema name: If -z and -a are both specified then -z will be ignored
        -t: Generate statistics for the specified tables
       -tw: Generate DDLs for tables whose names match the pattern criteria (wildcard characters) of the table name
        -n: Generates statistics for the specified nicknames
       -nw: Generates DDL statements for nicknames with names that match the pattern 
            criteria (wildcard characters) of the nickname name
     -xdep: Generate authorization DDL's for the tables and their dependent objects
            It's supported only with -t and -tw.
    -xddep: Generate authorization DDL's for the tables and their dependent objects
            including authorizations granted by SYSIBM during object creation time.
            It's supported only with -t and -tw.
       -ap: Generate AUDIT USING Statements
      -wlm: Generate WLM specific DDL Statements
      -mod: Generate DDL statements for Module
      -cor: Generate DDL with CREATE OR REPLACE clause
     -wrap: Generates obfuscated versions of DDL statements
        -h: More detailed help message
        -o: Redirects the output to the given file name
        -a: Generate statistics for all creators
        -m: Run the db2look utility in mimic mode
            -c: Do not generate COMMIT statements for mimic
            -r: Do not generate RUNSTATS statements for mimic
        -l: Generate Database Layout: Database partition groups, Bufferpools and Table spaces
        -x: Generate Authorization statements DDL excluding the original definer of the object
       -xd: Generate Authorization statements DDL including the original definer of the object
        -f: Extract configuration parameters and environment variables
       -td: Specifies x to be statement delimiter (default is semicolon(;))
        -i: User ID to log on to the server where the database resides
        -w: Password to log on to the server where the database resides
   -noview: Do not generate CREATE VIEW ddl statements
  -wrapper: Generates DDLs for federated objects that apply to this wrapper
   -server: Generates DDLs for federated objects that apply to this server
  -FEDONLY: Only created Federated DDL Statements
    -nofed: Do not generate Federated DDL
       -fd: Generates db2fopt statements for environment and configuration parameters.
        -v: Generate DDL for view only, this option is ignored when -t is specified
       -dp: Generate DROP statement before CREATE statement
       -ct: Generate DDL Statements by object creation time
-noimplschema: Do not generate CREATE SCHEMA ddl for implicitly created schemas
-nostatsclause: Do not include statistics clause in CREATE INDEX DDL
 -createdb: Generate a CREATE DATABASE command
-printdbcfg: Generate UPDATE DB CFG commands for the database configuration parameters

补充说明：

导出结构中  OCTETS指的就是字节，跟没加一样；

CHAR  == CHARACTER

二，其他

1，全局变量

--创建全局变量
create VARIABLE h_time varchar(10) default '2021-12-16';

--设置全局变量的值
set h_time = '2021-12-16';

--在现有日期上加减
set h_time = (select current date -12 days from sysibm.sysdummy1);

--在数据库指定参数上加减
set h_time =(select current date -(select integer(columnName) +1 
from tableName where id =1) days from sysibm.sysdummy1);

--获取全局变量
values h_time;

--删除全局变量
drop VARIABLE h_time;

2，查看索引

select * from SYSSTAT.INDEXES  WHERE TABSCHEMA='databaseName';

3，查看表信息：字段，结构，注释

SELECT T.COLNAME,T.TYPENAME , T.LENGTH, T.SCALE,T.NULLS,T.REMARKS 
FROM syscat.colums T 
where T.TABSCHEMA ='databaseName' AND T.TABNAME='tableName';

4，索引序列

--创建（从0开始，最大9999，每次张1）
CREATE SEQUENCE  sequenceName  AS INTEGER START 
WITH 1 INCREMENT BY 0 MINVALUE 9999 NO MAXVALUE CYCLE CACHE 100 ORDER; 

--查询序列当前值
select sequenceName .prevval  from sysibm.sysdummy1;
values prevval  for sequenceName ;

--查询序列下一个值
select sequenceName.nextval from sysibm.sysdummy1;
values nextval for sequenceName ;

--删除序列
DROP SEQUENCE sequenceName;

--新增时使用序列
insert into tableName(id,name) values(nextval for sequenceName,'帅哥') 

左补零 ：数字左补零，数字长度不定。事例是补齐8位
insert into tableName(id,name) values(right(digits(cast(nextval for sequenceName as bigint)),8),'帅哥')

5，rank() over()

select * from (
select rank() over(partition by columnName1 order by columnName2 desc) rk,
t.* from tableName t
)t1 where t1.rk <=1

6，日期

SELECT TO_TATE('','YYYY-MM-dd HH24:mi:ss') FROM sysibm.dual;
SELECT CURRENT DATE -10 DAYS FROM SYSIBM.SYSDUMMY1;


--上一月数据
where month(mydatecol)=month(current date)-1
and year(mydatecol) = year(current date)

WEEK（）返回参数中一年的第几周，用范围在 1-54 的整数值表示。以星期日作为一周的开始
VALUES WEEK('2012-05-25')--21

VALUES YEAR(CURRENT TIMESTAMP);/*2012*/  
VALUES YEAR('2012-05-25 21:18:12');/*2012*/
VALUES MONTH(CURRENT TIMESTAMP);/*5*/  
VALUES MONTH('2012-05-25 21:18:12');/*5*/ 
VALUES DAY(CURRENT TIMESTAMP);/*25*/  
VALUES DAY('2012-05-25 21:18:12');/*25*/
VALUES HOUR(CURRENT TIMESTAMP);/*21*/  
VALUES HOUR('2012-05-25 21:18:12');/*21*/
VALUES MINUTE(CURRENT TIMESTAMP);/*18*/  
VALUES MINUTE('2012-05-25 21:18:12');/*18*/
VALUES SECOND(CURRENT TIMESTAMP);/*12*/   
VALUES SECOND('2012-05-25 21:18:12');/*12*/
VALUES DATE(CURRENT TIMESTAMP);/*2012/5/25 0:00:00*/  
VALUES DATE('2012-05-25 21:18:12');/*2012/5/25 0:00:00*/
VALUES TIME(CURRENT TIMESTAMP);/*21:18:12*/  V
ALUES TIME('2012-05-25 21:18:12');/*21:18:12*/
VALUES TIMESTAMP(CURRENT TIMESTAMP);/*2012/5/25 21:18:12*/  
VALUES TIMESTAMP('2012-05-25 21:18:12');/*2012/5/25 21:18:12*/

7，执行脚本

--连接用户
db2 connect to databaseName;

--进入命令行
db2


--执行
db2 -txf tableName-update.sql -z tableName-update.log

8，导入导出

--备份数据和表结构
export to bkdata-tableName-备份日期.ixf of ixf 
select * from tableName  db2look -d database -t  tableName
 -e -o bkddl-tableName-备份日期.sql


--备份表数据
export bk_tableName.ixf of ixf select * from tableName


--导入表数据
import from bk_tableName.ixf of ixf insert into tableName


--备份txt,csv 数据
export to bk_tableName_20230117.csv of del select * from tableNamewhere id  in ('a','b')

9，编码问题查看

db2 ? sql编码（SQLCODE）

[db2@ 1.1.1.1~] db2 ? sq12214
QL2214N This user ID does not have the authorityto run the REORGutility on table<name>
xplanation:
n attempt was made to reorganize the specified table or its indexesithout the appropriate authorization.Appropriate authorization is thethe fo7Towing authorities :ONTROLpriviTege on the table or one0TSYSCTRL，YSADM，SYSMAINT，DBADM，SQLADM02
The utility stops processing.
Jser response:
on as a user with theogesubmit the REORG utilitycommana.
appropriate authority

10，null处理

输入类型为整型，且允许为空的，可以使用COALESCE(inputParameter,0)，把空转换成0；

#确保num为null时仍能被更新
update tbName set num=COALESCE(num,0)+1 where id=1

11，查询表所有COLNAME

SELECT COLNAME FROM SYSCAT.COLUMNS 
WHERE TABNAME='TABLE' AND TABSCHEMA='SCHEMA'  order by colno;

12，实现wm_concat ，行转列

1)获取表字段colname

SELECT  TABNAME  as full_tab_name,colname  FROM SYSCAT.COLUMNS   W
HERE TABNAME=’表名称’  ORDER BY COLNO

2)先把要转换的字段名变成xml 元素

SELECT full_tab_name,xmlelement (NAME a, colname || ',')  FROM (
     SELECT  TABNAME  as full_tab_name,colname  FROM SYSCAT.COLUMNS   
     WHERE TABNAME=’表名称’  ORDER BY COLNO
)

3)用xmlagg函数把分组后的字段连接

SELECT 
   full_tab_name,
   xmlagg (xmlelement (NAME a, colname || ',')),
   xml2clob (xmlagg (xmlelement (NAME a, colname || ',')))    AS col_str
FROM (
    SELECT  TABNAME  as full_tab_name,colname  FROM SYSCAT.COLUMNS   
    WHERE TABNAME=’表名称’  ORDER BY COLNO
)  
group by full_tab_name

4)替换A标签

SELECT 
      replace (
               replace (
                       xml2clob (xmlagg (xmlelement (NAME a, colname || ','))),
                       '<A>',''
                       ),
               '</A>',''
               )  AS col_str
FROM (
    SELECT  TABNAME  as full_tab_name,colname  FROM SYSCAT.COLUMNS   
    WHERE TABNAME=’表名称’  ORDER BY COLNO
)  
group by full_tab_name

5)将表字段clob类型转string类型

select cast(col_str as varchar ) col_str from (
  SELECT 
     replace(
             replace(
                     xml2clob (xmlagg (xmlelement (NAME a, colname || ','))),
                     '<A>',''
                    ),
              '</A>',''
            )  AS col_str
  FROM (
    SELECT  TABNAME  as full_tab_name,colname  FROM SYSCAT.COLUMNS   
    WHERE TABNAME=’表名称’  ORDER BY COLNO
  )  
  group by full_tab_name
)

13，分页

select * from (
  select tmp_table.*,ROWNUMBER()OVER() AS row_id from
  (select * from table_name)tmp_table
) where row_id  between 1 and 10

14，获取前几列

select * from test1 order by id  FETCH FIRST num ORWS ONLY;

#获取id排序，第一行数据
select * from test1 order by id  FETCH FIRST 1 ORWS ONLY;

15，索引

--创建单个普通索引
CREATE INDEX 索引名 ON 模式名.表名(列名1);

--创建联合索引
CREATE INDEX 索引名 ON 模式名.表名(列名1,列名2);
--创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 模式名.表名(列名1,列名2,...);

--删除索引
DROP INDEX 索引名;