注册

DB2常用sql语句大全

2020-03-30 添加评论
分享

没有找到相关结果

    已邀请:

    nccloud

    1、修改参数

    db2 update dbm cfg using 参数名 新参数值

    2、获取参数

    db2 get dbm cfg
    
#支持  db2 get dbm cfg  >> file 管道 及其他筛选命令



    待梳理


    CREATE DATABASE nc658 USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM CATALOG TABLESPACE  MANAGED BY DATABASE USING ( FILE '/newfs/db2data/nc658/Catalogdata1' 768000 )  USER TABLESPACE  MANAGED BY DATABASE USING ( FILE '/newfs/db2data/nc658/Userdata1' 512000 )  TEMPORARY TABLESPACE  MANAGED BY DATABASE USING ( FILE '/newfs/db2data/nc658/Tempspace1' 768000 )


    connect to nc658 user db2inst1 using db2inst1 


    CREATE Bufferpool NCUSED4 SIZE 102400 PAGESIZE 4K


    CREATE Bufferpool NCUSED16 SIZE 38400 PAGESIZE 16K


    CREATE REGULAR TABLESPACE NNC_DATA01 PAGESIZE 16K MANAGED BY DATABASE USING ( FILE '/newfs/db2data/nc658/nnc_data01' 1228800) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCHSIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16


    CREATE REGULAR TABLESPACE NNC_INDEX01 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE '/newfs/db2data/nc658/nnc_index01' 819200) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4


    CREATE  USER TEMPORARY  TABLESPACE USERTEMP PAGESIZE 16 K  MANAGED BY SYSTEM  USING ( '/newfs/db2data/nc658/Usertemp1' ) EXTENTSIZE 32 OVERHEAD 10.67 PREFETCHSIZE 32 TRANSFERRATE 0.04 BUFFERPOOL  NCUSED16 


    CREATE  SYSTEM TEMPORARY  TABLESPACE TEMPSPACE2 PAGESIZE 16 K  MANAGED BY SYSTEM  USING (  '/newfs/db2data/nc658/Tempspace2' ) EXTENTSIZE 32 OVERHEAD 10.67 PREFETCHSIZE 32 TRANSFERRATE 0.04 BUFFERPOOL  NCUSED16


    GRANT  DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE  TO USER db2inst1;


    GRANT  DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE  TO USER db2inst1;


    GRANT USE OF TABLESPACE NNC_DATA01 TO USER db2inst1 WITH GRANT OPTION;

    GRANT USE OF TABLESPACE NNC_INDEX01 TO USER db2inst1 WITH GRANT OPTION;

    GRANT USE OF TABLESPACE USERTEMP TO USER db2inst1 WITH GRANT OPTION;

    CONNECT RESET;


    ----------------------------------------------------------------------------------------------------

    --1.应用程序支持层堆大小 (aslheapsz) (4K)

    update dbm cfg using aslheapsz  1024

    --2.排序堆阈值 (sheapthres) (4K)

    update dbm cfg using sheapthres  40000 

    --3.代理程序的最大数目 (maxagents)

    update dbm cfg using maxagents  300

    --4.代理程序池大小 (NUM_POOLAGENTS)

    update dbm cfg using NUM_POOLAGENTS  100



    数据库配置参数

    假设NC数据库名称为“db2_0917 ”

    --1.数据库堆 (DBHEAP)(4K)

    update database configuration for nc658 using DBHEAP  10240 automatic

    --2.日志缓冲区大小 (logbufsz) (4K)

    update database configuration for nc658 using logbufsz  2048 automatic

    --3.编目高速缓存大小 (CATALOGCACHE_SZ) (4K)

    update database configuration for nc658 using CATALOGCACHE_SZ  3072 automatic

    --4.用于锁定列表的最大内存 (locklist) (4K)

    update database configuration for nc658 using locklist  10240 automatic

    --5.最大应用程序控制堆大小 (app_ctl_heap_sz) (4K)

    -- update database configuration for nc658 using app_ctl_heap_sz  4096

    update database configuration for nc658 using appl_memory automatic

    --6.排序堆大小 (sortheap)(4K)

    update database configuration for nc658 using sortheap  4096 automatic

    --7.语句堆大小 (stmtheap) (4K)

    update database configuration for nc658 using stmtheap  3072 automatic

    --8.应用程序堆大小 (applheapsz)(4K)

    update database configuration for nc658 using applheapsz  2048 automatic 

    --9.程序包高速缓存大小 (pckcachesz) (4K)

    update database configuration for nc658 using pckcachesz  20480 automatic

    --12. 逐步升级前锁定列表的最大百分比 (maxlocks)

      update database configuration for nc658 using maxlocks  80

    --13. 异步页清除程序数目 (NUM_IOCLEANERS)

    update database configuration for nc658 using NUM_IOCLEANERS  1 automatic

    --14. I/O 服务器数目 (NUM_IOSERVERS)

    update database configuration for nc658 using NUM_IOSERVERS  1 automatic

    --15. 活动应用程序的最大数目 (MAXAPPLS)

    --16. 活动应用程序的平均数目 (AVG_APPLS)

    update database configuration for  nc658using MAXAPPLS  600

    t  using AVG_APPLS  200update database configuration for nc658using AVG_APPLS  200

    --17.日志文件的大小 (logfilsiz) (4K)

    update database configuration for nc658 using logfilsiz  51200

    --18.主日志文件数目 (logprimary)

    update database configuration for nc63 using logprimary  180

    --19.辅助日志文件数目 (LOGSECOND)

    update database configuration for nc63 using logsecond  70

    --20.组提交计数 (MINCOMMIT)

    update database configuration for nc658 using MINCOMMIT  1


    检查挂起状态的表：

    select * from syscat.tables where status<>'N'

    set integrity for tablename all immediate unchecked 

    reorg table tablename



    复制表：

    create table sm_patch_version_bak like sm_patch_version--空表

    insert into sm_patch_version_bak select * from sm_patch_version;



    11:04赵双双DB2里有两种create table 后跟另一个表的语句：不过都是建的空表，需要再用insert into插入数据

    create table sm_patch_version_bak like sm_patch_version--空表

    insert into sm_patch_version_bak select * from sm_patch_version;


    create table sm_patch_version_bak as (select * from sm_patch_version) definition only;--表定义，空表

    insert into sm_patch_version_bak select * from sm_patch_version;


    db2修改日志路径：

    db2 update db cfg for nc63 using  newlogpath /newfs/db2log/nc63



    db2 "select 'db2 SET INTEGRITY FOR DB2INST.'||tabname||'  GENERATED COLUMN, FOREIGN KEY, MATERIALIZED QUERY, CHECK, STAGING FULL ACCESS IMMEDIATE UNCHECKED

    ' from syscat.tables where tabschema='DB2INST'" >1set.sh



    db2 "select 'db2 reorg table DB2INST.'||tabname||' allow read access' from syscat.tables where tabschema='DB2INST'" >2re.sh


    db2收集统计信息：

    db2 runstats on table db2inst.test


    db2启用oracle兼容模式

    db2set  DB2_COMPATIBILITY_VECTOR=ORA



    CREATE DATABASE nc658 USING CODESET UTF-8 TERRITORY CN COLLATE USING SYSTEM CATALOG TABLESPACE  MANAGED BY DATABASE USING ( FILE '/newfs/db2data/Catalogdata1' 128000 )  USER TABLESPACE  MANAGED BY DATABASE USING ( FILE '/newfs/db2data/Userdata1' 512000 )  TEMPORARY TABLESPACE  MANAGED BY DATABASE USING ( FILE '/newfs/db2data/Tempspace1' 768000 )


    connect to nc658 user db2inst1 using db2inst1 


    CREATE Bufferpool NCUSED4 SIZE 102400 PAGESIZE 4K


    CREATE Bufferpool NCUSED16 SIZE 38400 PAGESIZE 16K


    CREATE REGULAR TABLESPACE NNC_DATA01 PAGESIZE 16K MANAGED BY DATABASE USING ( FILE '/newfs/db2data/nnc_data01' 204800) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCHSIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16


    CREATE REGULAR TABLESPACE NNC_INDEX01 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE '/newfs/db2data/nnc_index01' 409600) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4


    CREATE  USER TEMPORARY  TABLESPACE USERTEMP PAGESIZE 16 K  MANAGED BY SYSTEM  USING ( '/newfs/db2data/Usertemp1' ) EXTENTSIZE 32 OVERHEAD 10.67 PREFETCHSIZE 32 TRANSFERRATE 0.04 BUFFERPOOL  NCUSED16 


    CREATE  SYSTEM TEMPORARY  TABLESPACE TEMPSPACE2 PAGESIZE 16 K  MANAGED BY SYSTEM  USING (  '/newfs/db2data/Tempspace2' ) EXTENTSIZE 32 OVERHEAD 10.67 PREFETCHSIZE 32 TRANSFERRATE 0.04 BUFFERPOOL  NCUSED16


    GRANT  DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE  TO USER db2inst1;


    GRANT  DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE  TO USER db2inst1;


    GRANT USE OF TABLESPACE NNC_DATA01 TO USER db2inst1 WITH GRANT OPTION;

    GRANT USE OF TABLESPACE NNC_INDEX01 TO USER db2inst1 WITH GRANT OPTION;

    GRANT USE OF TABLESPACE USERTEMP TO USER db2inst1 WITH GRANT OPTION;

    CONNECT RESET;


    杀连接

    db2 list application

    db2 "force application (Appl)"



    C:\> db2 force applications all 

     DB20000I FORCE APPLICATION 命令成功完成。

     DB21024I 此命令为异步的，可能未能立即生效。

     C:\> db2 list applications 

     SQL1611W “数据库系统监视器”没有返回任何数据。








    5系列

    CREATE DATABASE nc57 USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM CATALOG TABLESPACE  MANAGED BY DATABASE USING ( FILE '/newfs/db2data/nc57/Catalogdata1' 768000 )  USER TABLESPACE  MANAGED BY DATABASE USING ( FILE '/newfs/db2data/nc57/Userdata1' 512000 )  TEMPORARY TABLESPACE  MANAGED BY DATABASE USING ( FILE '/newfs/db2data/nc57/Tempspace1' 768000 )


    connect to nc57 user db2inst1 using db2inst1 


    CREATE Bufferpool NCUSED4 SIZE 102400 PAGESIZE 4K


    CREATE Bufferpool NCUSED16 SIZE 38400 PAGESIZE 16K


    CREATE REGULAR TABLESPACE NNC_DATA01 PAGESIZE 16K MANAGED BY DATABASE USING ( FILE '/newfs/db2data/nc57/nnc_data01' 204800) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCHSIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16

    CREATE REGULAR TABLESPACE NNC_DATA02 PAGESIZE 16K MANAGED BY DATABASE USING ( FILE '/newfs/db2data/nc57/nnc_data02' 20480) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCHSIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16

    CREATE REGULAR TABLESPACE NNC_DATA03 PAGESIZE 16K MANAGED BY DATABASE USING ( FILE '/newfs/db2data/nc57/nnc_data03' 20480) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCHSIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16


    CREATE REGULAR TABLESPACE NNC_INDEX01 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE '/newfs/db2data/nc57/nnc_index01' 204800) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4

    CREATE REGULAR TABLESPACE NNC_INDEX02 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE '/newfs/db2data/nc57/nnc_index02' 40960) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4

    CREATE REGULAR TABLESPACE NNC_INDEX03 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE '/newfs/db2data/nc57/nnc_index03' 204800) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4



    CREATE  USER TEMPORARY  TABLESPACE USERTEMP PAGESIZE 16 K  MANAGED BY SYSTEM  USING ( '/newfs/db2data/nc57/Usertemp1' ) EXTENTSIZE 32 OVERHEAD 10.67 PREFETCHSIZE 32 TRANSFERRATE 0.04 BUFFERPOOL  NCUSED16 


    CREATE  SYSTEM TEMPORARY  TABLESPACE TEMPSPACE2 PAGESIZE 16 K  MANAGED BY SYSTEM  USING (  '/newfs/db2data/nc57/Tempspace2' ) EXTENTSIZE 32 OVERHEAD 10.67 PREFETCHSIZE 32 TRANSFERRATE 0.04 BUFFERPOOL  NCUSED16







    CREATE DATABASE nctest USING CODESET UTF-8 TERRITORY CN COLLATE USING SYSTEM CATALOG TABLESPACE  MANAGED BY DATABASE USING ( FILE 'E:\db2_data\Catalogdata1' 128000 )  USER TABLESPACE  MANAGED BY DATABASE USING ( FILE 'E:\db2_data\nnc_data01' 512000 )  TEMPORARY TABLESPACE  MANAGED BY DATABASE USING ( FILE 'E:\db2_data\Tempspace1' 768000 )


    2020-03-30 0 0
    分享

    nccloud

    DB2查看授权


    db2licm -l


    DB2 添加授权


    db2licm -a 授权文件地址   



    查看表空间


    list tablespaces show detail   


    扩展表空间

     alter tablespace SYSCATSPACE extend(all 1024M)


    查看版本查看数据库版本

    db2licm -l   

    2021-04-20 0 0
    分享

    nccloud

    1、    增加新的日志文件：
    ALTER TABLESPACE 表空间 ADD (File '文件' 大小);
    ALTER TABLESPACE TBS_DMT ADD  (File '2data1/tbs/DMF_02' 50G );

    2、    扩大原有的日志文件：
    ALTER TABLESPACE 表空间 RESIZE  (File '文件' 大小)
    ALTER TABLESPACE TBS_DM_INDT RESIZE  (FILE '2data1/tbs/DM_IND_01' 60G);

    2021-07-08 0 0
    分享

    nccloud

    1、进入db2运行环境

    db2admin

    2、连接数据库

    db2 connect to auto0706 user db2admin using db2admin

    3、查看修改

    db2 list tablespaces show detail

    2021-07-08 0 0
    分享

    nccloud

    超级用户（原始用户）赋权给普通用户：GRANT DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE  TO USER 用户名;

    2021-11-16 0 0
    分享

    nccloud

    报错：com.ibm.db2.jcc.am.BatchUpdateException: [jcc][t4][102][10040][3.63.123] Batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch.



    释放：db2diag.log 日志 



    windows操作

    1、服务器进入db2cmd环境

    2、db2diag -A d:/db2diagbak  //其中 d为d盘，db2diagbak  为备份目录， 执行后DB2会将日志备份到该目录并重命名。效果如下：

    http://nccloud.yytimes.com/uploads/answer/20211117/34b24665b4a302d8cf0478940f13ca4a.png



    db2diag.log是用来记录DB2数据库运行中的信息的文件。
    可以通过此文件，查看记录的有关DB2数据库详细的错误信息。此文件也是不断增大的，需要定期进行清理。 
    可以通过查看实例的配置参数DIAGPATH，来确定db2diag.log文件是放在哪个目录下：db2 get dbm cfg 
    如果Diagnostic data directory path(DIAGPATH) = /home/db2inst1/sqllib/db2dump，
    则此文件是放在/home/db2inst1/sqllib/db2dump目录下。
    当文件系统/home的使用率达到80%－90%左右时，应及时删除db2diag.log文件。 
    请按以下正确步骤操作:
    确认应用、DB2已经停止。
    将原db2diag.log文件备份到其它文件系统下。 
    删除db2diag.log文件。删除后，DB2会自动创建一个新的文件。

    注意：如果你觉得以上操作不保险的话，Db2提供了自动归档db2diag.log的命令
    db2diag -a 自动将该文件以日期时间命名
    2021-11-17 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    Copyright © 2025