db2数据库

数据库"/>

db2数据库

目录

• 一 java连接数据库demo
• 二 常用操作语句
• 三 创建触发器
• • 1.正确代码
  • 2. 错误代码
  • 3.终端上 批量执行创建触发器
• 四 查看系统帮助

一 java连接数据库demo

package com.example.demo.db2;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class Db2Test {public static void main(String[] args) {try {Class<?> class1 = Class.forName("com.ibm.db2.jcc.DB2Driver");//jdbc:db2://ip:portNum/dbName:currentSchema=db2inst1;useAffectedRows=true;String dbUrl="jdbc:db2://ip:50000/mydb:currentSchema=ABM;useAffectedRows=true;";String username="db2inst1";String password="db2inst1-pwd";Connection conn = DriverManager.getConnection(dbUrl, username,password);String sql=" SELECT * FROM CUSTOMERS";PreparedStatement pst = conn.prepareStatement(sql);ResultSet rs = pst.executeQuery();while (rs.next()) {String name = rs.getString("name");System.out.println(name);}} catch (ClassNotFoundException | SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}
}

String dbUrl="jdbc:db2://ip:端口/用户名:currentSchema=当前Schema名，也可以对比mysql的库名;useAffectedRows=true;";
注意：true;的冒号一定要有！

二 常用操作语句

-- 建表语句CREATE TABLE CUSTOMERS (ID INT NOT NULL  primary key GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1),NAME CHAR(10) NOT NULL DEFAULT 'NO NAME');REORGCHK  TABLE ABM.ABM_MODULE;
reorg TABLE ABM.ABM_MODULE;
-- DB2 SQL Error: SQLCODE=-668, SQLSTATE=57016
CALL SYSPROC.ADMIN_CMD('REORG  table ABM.ABM_MODULE ') ;
CALL SYSPROC.ADMIN_CMD('REORG  table ABM.CUSTOMERS ') ;
-- 添加主键
ALTER TABLE ABM_MODULE ADD PRIMARY KEY('ID') ;
alter table ABM_MODULE add primary key (ID);-- 添加外键ALTER TABLE ABM.CUSTOMERS ADD CONSTRAINT CUSTOMERS_FK FOREIGN KEY (ABMID) REFERENCES ABM.ABM_MODULE(ID);-- 设置非空
alter table ABM_MODULE alter ID set not NULL;-- 设置自增Alter table ABM_MODULE alter column id set generated always as identity (start with 1,increment by 1);-- 查看主键
DESCRIBE table ABM_MODULE; --无用SELECT TABSCHEMA,TABNAME,COLNAME,KEYSEQ
FROM SYSCAT.COLUMNS
WHERE KEYSEQ IS NOT NULL
-- 查看索引select * from sysibm.sysindexes where tbname = 'ABM_MODULE';select char(TABNAME,20) TABNAME,char(TABSCHEMA,10) TABSCHEMA,char(INDNAME,20) INDNAME,char(OWNER,10) OWNER,INDEXTYPE,char(COLNAMES,50) COLNAMES from syscat.indexes where tabname='ABM_MODULE'
select char(TABNAME,20) TABNAME,char(TABSCHEMA,10) TABSCHEMA,char(INDNAME,20) INDNAME,char(OWNER,10) OWNER,INDEXTYPE,char(COLNAMES,50) COLNAMES from syscat.indexes where tabname='CUSTOMERS'-------------- INSERT INTO CUSTOMERS (NAME) VALUES ('fjl1');INSERT INTO ABM_MODULE (ID,NAME) VALUES ('2','fjl');SELECT * FROM CUSTOMERS LIMIT 0,2;SELECT * FROM ABM_MODULE LIMIT 1,2;select count(*) as totalNum FROM (SELECT * FROM CUSTOMERS)LIMIT 
ALTER TABLE ABM_MODULE ALTER ID NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1)

三 创建触发器

1.正确代码

CREATETRIGGER "FJL"."SERVICE_GROUP_USER_DELETE"AFTER DELETEON "FJL"."SERVICE_GROUP"REFERENCING OLD  NEWFOR EACH ROW
BEGINDELETE FROM "FJL"."SERVICE_USER" WHERE SERVICENAME = OLD.NAME;
END 

2. 错误代码

CREATETRIGGER "FJL"."SERVICE_GROUP_USER_INSERT"AFTER INSERTON "FJL"."SERVICE_GROUP"REFERENCING OLD AS "OLD" NEW AS "NEW"FOR EACH ROW
BEGININSERT INTO "FJL"."SERVICE_USER"(USERNAME, SERVICENAME) VALUES (NEW.CREATEUSER, NEW.NAME);
END ;

错误点

1. OLD AS “OLD” NEW AS “NEW”： AS “OLD”、AS “NEW” 中的双引号不要
2. 最后的语句分隔符“;”不要
3. TRIGGER 前可能存在table（换行符） 要替换成空格

3.终端上 批量执行创建触发器

1.新建test.sql
2.每个触发器以@结束
3.登录
db2 connect to 用户名
4.执行脚本命令
db2 -td@ -vf test.sql
执行成功会提示
The SQL command completed successfully

四 查看系统帮助

如：可通过在“命令窗口”中执行命令"db2 ?options"查看参数的用途。