db2数据库-探路

一 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 block
			e.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.正确代码

CREATE
    TRIGGER "FJL"."SERVICE_GROUP_USER_DELETE"
    AFTER DELETE
    ON "FJL"."SERVICE_GROUP"
    REFERENCING OLD  NEW
    FOR EACH ROW
BEGIN
    DELETE FROM "FJL"."SERVICE_USER" WHERE SERVICENAME = OLD.NAME;
END 

2. 错误代码

CREATE
    TRIGGER "FJL"."SERVICE_GROUP_USER_INSERT"
    AFTER INSERT
    ON "FJL"."SERVICE_GROUP"
    REFERENCING OLD AS "OLD" NEW AS "NEW"
    FOR EACH ROW
BEGIN
    INSERT 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"查看参数的用途。