相关技术摘要_使用db2摘要(未做整理)

friendlessstar

　　[1]返回指定，第start行到第end行的记录
String sql=new String("select * from (select id,name," +
"rownumber() over() as rn from test) a1 where rn between "+start+" and "+end+");
　　有条件返回指定记录行的记录，注意，使用了更多一重select嵌套
public List query(String tablename,String columns,String condition,String order,int start,int end)
{
if(start==0||end==0)
{
query(tablename,columns,condition,order,0);
}

//String sql=new String("select * from (select "+table_prefix+tablename+".*," +"rownumber() over() as rn from "+table_prefix+tablename+" "+condition+" "+order+") a1 where rn between "+start+" and "+end);

String sql=new String("select * from (select a0.*,rownumber() over() as rn from (select * from "+table_prefix+tablename+" "+condition+" "+order+" ) a0) a1 where rn between "+start+" and "+end);

return springJdbcTemplate.queryForList(sql);
}
-------------------------------------------------------------------------------------------------------------------
　　[2]返回指定的，前n行记录
select * from test fetch first n rows only
-------------------------------------------------------------------------------------------------------------------
　　[3]找不到db2 jdbc驱动的问题
[3-1]本地java应用程序
使用 "COM.ibm.db2.jdbc.app.DB2Driver" DBDriver
使用连接字符串 "jdbc:db2:Test";
安装目录下的/IBM/SQLLIB/java/db2java.zip设置到当前项目的Build Path中去
在Eclipse 3.2X中把/IBM/SQLLIB/java/db2java.zip
的路径配置到[properties]->[java Build Path]->[Libraries]中
[3-2]Web应用程序
DriverManagerDataSource dataSource = new DriverManagerDataSource ();
dataSource.setDriverClassName( "com.ibm.db2.jcc.DB2Driver");
dataSource.setUrl( "jdbc:db2://localhost:50000/test");
安装目录下的/IBM/SQLLIB/java/db2jcc.jar db2jcc_license_cu.jar设置到当前项目的Build Path中去
-------------------------------------------------------------------------------------------------------------------
　　[4]数据的导入导出，可以先选中，数据库中表，然后，鼠标右键，弹出菜单
执行，那里的，导入，导出命令即可
-------------------------------------------------------------------------------------------------------------------
　　[5]表结构的导出
同上，执行导出DDL即可
-------------------------------------------------------------------------------------------------------------------
　　[6]表结构的导入
把SQL文件（从其它DB2服务器导出的DDL文件）,可以在db2命令行下使用下面的命令
db2 -vtf sql文件名
-------------------------------------------------------------------------------------------------------------------
　　[7]导入导出数据库
http://topic.csdn.net/t/20050405/21/3911920.html
//导出数据库
db2move &dbname export [-sn 模式名] [-l 存放导出数据的绝对路径]
//导入数据库
db2move &remote_dbname(already new create) import -u username -p password
　　备注：
[1]源db2move所在路径中的db2move.lst文件(包括msg、ixf文件)， 可能必须要放到目标db2move所在路径中
[2]注意力 目标的表空间要足够大 如果没有源大的话 你要在目标机上添加足够大的表空间
-------------------------------------------------------------------------------------------------------------------
　　[8]测试连接
db2 connect to 数据名 user 用户名 using 密码
-------------------------------------------------------------------------------------------------------------------
　　[9]DB2基本命令(用于查询)
http://hi.baidu.com/daiguofu/blog/item/e058b019fb64be4543a9adca.html
-------------------------------------------------------------------------------------------------------------------
　　[10]DB2的字段类型
http://www.dingl.com/view.shtml?xh=175
-------------------------------------------------------------------------------------------------------------------
　　
[11]通过db2cc使用Linux下的DB2 9.1.x服务
su root
xhost +
su -db2inst1
home/dasusr1/das/bin/db2admin start
db2start
opt/ibm/db2/V9.1/bin/db2cc
-------------------------------------------------------------------------------------------------------------------
[12]往DB2记录行中插入TimeStamp类型数据时，可以以"yyyy-mm-dd hh:mm:ss"字符串方式插入，
注意如果3月份写成3而不是03形式的话，在Java代码中插入这种类型数据为失败！即2007-03-04.....
而不是2007-3-4....，以"2007-3-4"，形式必然出错。
-------------------------------------------------------------------------------------------------------------------
[13]DB2基础：约束
http://www.ibm.com/developerworks/cn/db2/library/techarticles/dm-0401melnyk/index.html
-------------------------------------------------------------------------------------------------------------------
[14]《DB2编程基础要点(1)》(含：create 含自动生成id的table)
http://www.pcdog.com/edu/db2/2005/12/s134260.html
-------------------------------------------------------------------------------------------------------------------
[15]DB2 基础: 在 DB2 Universal Database 中创建第一个触发器
http://www.ibm.com/developerworks/cn/db2/library/techarticles/0308bhogal/0308bhogal.html
-------------------------------------------------------------------------------------------------------------------
[16]java中取记录数量(struts,spring)
listForm=db.query("count(*)", "where RCategory='"+category+"'", "",0);
if(listForm==null||listForm.size()<=0)
{
...
}
else
{
//get records count
Map mapT=(Map)listForm.get(0);
Integer nnnn=(Integer)mapT.get("1");
nPageTotalSize=nnnn.intValue();
-------------------------------------------------------------------------------------------------------------------
[17]db2 backup db WCMV6 user db2inst1 using password to c:/WCMV6
RESTORE DATABASE source_db_name USER user_name USING password FROM backup_dir_name ...
　　备份db2数据库。
[db2inst1@localhost TRS]$ db2 backup db NBDA
Backup successful. The timestamp for this backup image is : 20071213170446
[db2inst1@localhost TRS]$ db2 backup db TRSWCMV6
Backup successful. The timestamp for this backup image is : 20071213170511
　　
还原db2数据库
db2 restore db NBDA
-------------------------------------------------------------------------------------------------------------------