1 。db2 中如何查看某个序列被哪里表使用??
2 :如何查看某个序列的使用情况 ? 例如序列当前值,最大值 ,目的是评估某个序列别使用到最大值后,会出问题
3: 查看某个表中的自增列identity always 和defaults 的最大值和当前值 ?
SELECT OBJECT_NAME, COLUMN_NAME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'SEQUENCE' AND OBJECT_NAME LIKE '%sequencename%';
其中,sequencename为要查询的序列名称。
SELECT SEQUENCE_NAME, NEXTVAL
FROM DBA_SEQUENCES
WHERE SEQUENCE_NAME = 'sequencename';
其中,sequencename为要查询的序列名称。该语句将返回序列名称和下一个值。
SELECT TABLE_NAME, COLUMN_NAME, MAX(IDENTITY_VALUE) AS MAX_IDENTITY_VALUE, CURRENT_VALUE AS CURRENT_VALUE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'databasename' AND TABLE_NAME = 'tablename' AND IS_AUTOINCREMENT IN ('TRUE','1') AND DATA_TYPE IN ('NUMBER','SMALLINT');
其中,databasename和tablename分别为要查询的数据库名和表名。该语句将返回表名、列名、最大自增值和当前值。
收起1、无法直接查询那个sequence被使用,可以查看sequence的值来大致判断是否被使用了。
2、sequence建议设置成bigint。
查看sequence使用情况:
select b.SEQSCHEMA,
b.SEQNAME,
b.NEXTCACHEFIRSTVALUE,
b.MINVALUE,
b.MAXVALUE,
b.CACHE
from SYSCAT.SEQUENCES b where b.seqschema not like 'SYS%' and b.seqtype<>'I';
序列达到最大值后出问题:
$ db2 alter sequence seq1 restart with 2147483646
$ db2 "insert into table3 values nextval for seq1"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0359N The range of values for the identity column or sequence is
exhausted. SQLSTATE=23522
3、查看自增列的使用情况
(1) 查看自增列的使用情况:
SELECT a.SEQID,
a.TABSCHEMA,
a.TABNAME,
a.colname,
b.SEQSCHEMA,
b.SEQNAME,
b.NEXTCACHEFIRSTVALUE,
b.MINVALUE,
b.MAXVALUE,
a.CACHE
FROM SYSCAT.COLIDENTATTRIBUTES a, SYSCAT.SEQUENCES b
WHERE a.SEQID = b.SEQID;
收起语句一:
SELECT SEQSCHEMA,
SEQNAME,
SEQID,
SEQTYPE,
CYCLE,
START,
NEXTCACHEFIRSTVALUE,
MAXVALUE,
DEC((FLOAT(NEXTCACHEFIRSTVALUE*100.0)/FLOAT(MAXVALUE)),5,2) AS PCT_USED,
case when DATATYPEID=16 then 9999999999999999999999999999999 when DATATYPEID=20 then 9223372036854775807 when DATATYPEID=24 then 2147483647 when DATATYPEID=28 then 32767 else null end as EXTENMAXVALUE
FROM SYSCAT.SEQUENCES
WHERE SEQSCHEMA='${SEQSCHEMA}'
AND SEQNAME='${SEQNAME}'
AND OWNERTYPE='U'
WITH UR
这条语句可以查DB2数据库中指定的序列信息,如果SEQTYPE为"S" 那就是序列,如果为 “I”那就是自增列,可以通过以下语句查到是哪个表、哪一例的:
SELECT
co.TABSCHEMA ,
co.TABNAME,
co.COLNAME
FROM SYSCAT.SEQUENCES as sq ,SYSCAT.COLIDENTATTRIBUTES as co
WHERE SEQSCHEMA='${SEQSCHEMA}' AND SEQNAME='${SEQNAME}' AND sq.SEQID=co.SEQID
WITH UR
收起