Database

DB2获得当前版本Get Version SQL语句

Categories: Database; Tagged with: DB2 • SQL; @ October 3rd, 2010 18:12

SQL语句: "SELECT service_level, fixpack_num FROM TABLE (sysproc.env_get_inst_info()) as INSTANCEINFO";

(more…)

通过ALERT为DB2 TABLE增加/删除主键 ADD/DROP PRIMARY KEY

Categories: Database; Tagged with: DB2 • Primary key • SQL; @ October 3rd, 2010 15:01

Drop Primary Key 移除主键:

ALTER TABLE TABLE2 DROP PRIMARY KEY

Add Primary Key 增加主键:

ALTER TABLE TABLE2 ADD ID2 INTEGER NOT NULL DEFAULT 1; ALTER TABLE TABLE2 ADD PRIMARY KEY(ID2);

See: http://bytes.com/topic/db2/answers/670160-how-change-primary-key-already-existing-table

DB2获得表内主键 Get PrimaryKeys

Categories: Database; Tagged with: DB2 • JDBC • Primary key; @ October 3rd, 2010 14:50

SQL:

SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR NOT LIKE ‘SYS%’ AND TBNAME = ‘TABLENAME‘ AND KEYSEQ > 0 ORDER BY KEYSEQ ASC;

(more…)

使用JDBC获取DB2的所有表(getTables)

Categories: Database; Tagged with: DB2 • getTables • JDBC • SQL; @ October 2nd, 2010 23:15

直接使用metadata.getTables似乎不能获取DB2中指定Database下的所有Table, 但可使用如下语句获取:

"SELECT CREATOR,NAME FROM SYSIBM.SYSTABLES WHERE CREATOR NOT LIKE ‘SYS%’";

参考链接:

View Tables of DB2 Database using JDBC http://bytes.com/topic/db2/answers/704725-view-tables-db2-database-using-jdbc

使用JDBC type4连接DB2

Categories: Database; Tagged with: DB2 • JDBC; @ October 2nd, 2010 22:57

1. 首先要在工程中加入DB2连接文件db2jcc.jar, 该文件可从DB2安装目录中找到.
2. 建立连接:

	public void setUp() throws Exception {
		Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
		Properties connProperties = new Properties();
		connProperties.put("user", "ADMINISTRATOR");
		connProperties.put("password", "liguoliang.com");

		connection = DriverManager.getConnection(db2.getJdbcUrl("192.168.1.113", 50000, "meta"), connProperties);
	}

3. 执行SQL语句举例:

	public void validateConnection(Connection connection) throws SQLException {
		if(connection == null) {
			throw new SQLException("Connection is null");
		}

		Statement stmt = null;
		ResultSet rs = null;

		try {
			stmt = connection.createStatement();
			rs = stmt.executeQuery("VALUES(1)"); // 该语句将返回1
			rs.next();
			if(rs.getInt(1) != 1) {
				throw new SQLException("SQL Exception: values(1) != 1");
			}
		}finally {
			JDBCUtilities.close(rs);
		}
	}

有用的链接:

JDBC连接DB2的一些总结

用java访问数据库DB2代码的实际操作 http://database.51cto.com/art/201008/216908.htm

使用 JDBC 连接不同版本 DB2 数据库的兼容性问题 http://www.ibm.com/developerworks/cn/data/library/techarticles/0402chenjunwei/0402chenjunwei.html#author1

Newer Posts <-> Older Posts