DB2 存储过程,关于for循环

zhangenming20080324 2011-10-18 04:37:24
String financeId = "";
String firstFinanceId = "";

List<TbCsmCustomerFinanceBO> list = ds.selectByHqlQuery("select bo from TbCsmCustomerFinanceBO bo where bo.customerNum = ? order by bo.financeStatementDeadline",new Object[] {customerNum});
int j = 0;
for (int i = 0; i < list.size(); i++) {
TbCsmCustomerFinanceBO tbCsmCustomerFinanceBO = (TbCsmCustomerFinanceBO) list.get(i);
if (i == 0) {//资产总额取上月底财务报表中“资产合计”科目数据
firstFinanceId = tbCsmCustomerFinanceBO.getCustomerFinanceId();
sql = "select sum(BEFORE_PROJECT_VALUE) project_value from TB_CSM_FINANCE_STATEMENT_DATA"
+ " where CUSTOMER_FINANCE_ID = '"
+ firstFinanceId
+ "' and PROJECT_CD in ('PNO001000045','PMO001000046','PNN001000033','PMN001000039')";

if ("1".equals(tbCsmCustomerFinanceBO.getRegulatedInd())) {
sql = sql.replaceAll("BEFORE_PROJECT_VALUE","AFTER_PROJECT_VALUE");
}

List list1 = (List) ds.selectBySqlQuery(sql, null);

if (list1 != null && list1.size() > 0) {
Map map = (Map) list1.get(0);

if( map.get("project_value")!=null){
assetTotalAmt = ((BigDecimal) map.get("project_value")).doubleValue();
}
}
}

//销售总额取近三年年度财务报表中“主营业务收入”算术平均值,如果应用新会计准则(2006)的财务报表,则取“营业收入”算术平均值。
if ("1".equals(tbCsmCustomerFinanceBO.getFinanceStatementTypeCd())) {//年报
if (j < 3) {
financeId = tbCsmCustomerFinanceBO.getCustomerFinanceId();
sql = "select sum(BEFORE_PROJECT_VALUE) project_value from TB_CSM_FINANCE_STATEMENT_DATA"
+ " where CUSTOMER_FINANCE_ID = '"
+ financeId
+ "' and PROJECT_CD in ('PNO002000001','PMO002000001','PNN002000001','PMN002000002')";

if ("1".equals(tbCsmCustomerFinanceBO.getRegulatedInd())) {
sql = sql.replaceAll("BEFORE_PROJECT_VALUE","AFTER_PROJECT_VALUE");
}

List list1 = (List) ds.selectBySqlQuery(sql, null);
if (list1 != null && list1.size() > 0) {
Map map = (Map) list1.get(0);
if( map.get("project_value")!=null){
venditionAmt = venditionAmt + ((BigDecimal) map.get("project_value")).doubleValue();
}
}
j = j + 1;
}
}
}
if (j != 0) {
venditionAmt = venditionAmt / j;
}

我有这么一段java程序,想用存储过程做,就是通过查询继续,然后for循环在继续做逻辑判断处理。请问大家谁有例子啊,网上找了几个没看到。。。对存储过程不熟,请大家帮忙,给个例子之类的,谢谢。
...全文
831 1 打赏 收藏 转发到动态 举报
写回复
用AI写文章
1 条回复
切换为时间正序
请发表友善的回复…
发表回复
zhaojianmi1 2011-10-18
  • 打赏
  • 举报
回复

-- db2 connect to inte user db2admin using db2admin
--编译过程 db2 -td@ -vf PR_income_check.db2

--运行过程
--db2 call PR_income_check()



--删除过程
drop procedure PR_income_check()@

--创建PR_income_check过程
create procedure PR_income_check()
language sql
begin

--定义变量

declare V_RPTNO_DATE VARCHAR(10);
declare V_RPTNO_YEAR CHARACTER(2);
declare V_DATE DATE;

declare VAR_CUSTNAME VARCHAR(128);

--循环计数器
declare loopcount INT;
declare loopcontrol INT;

--校验日志相关变量
declare V_TBL_NAME CHARACTER(50);
declare V_PK_VALUE VARCHAR(255);
declare V_ERR_TYPE CHARACTER(4);
declare V_DESCRIPTION VARCHAR(255);

declare STR_TBL_CUSTOMERINFO CHARACTER(50) default 'TBL_CUSTOMERINFO';
declare STR_TBL_BRANCHINFO CHARACTER(50) default 'TBL_BRANCHINFO';
declare STR_TBL_BANKINFO CHARACTER(50) default 'TBL_BANKINFO';
declare STR_TBL_PAYMETHODCODE CHARACTER(50) default 'TBL_PAYMETHODCODE';
declare STR_TBL_CCYCODE CHARACTER(50) default 'TBL_CCYCODE';
declare STR_TBL_COUNTRY CHARACTER(50) default 'TBL_COUNTRY';
declare STR_TBL_TXCODE CHARACTER(50) default 'TBL_TXCODE';

declare V_PK VARCHAR(255);



--定义数据select错误的相关异常处理的参数
declare tblname char(50) default 'TBL_INCOME_TMP';
declare errLog VARCHAR(400);
declare sqlcode integer ;
declare sqlstate char(5) ;
declare rowcount int;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found
set rowcount = 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '22007'
set rowcount = 6;

-- DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
-- set rowcount = 2;
declare exit handler for sqlexception
call logNote(tblname, errLog, sqlcode, sqlstate);


-------
call logNote(tblname, 'PR_income_check开始',0, '0');
-------
set errLog = '游标取数,TBL_INCOME_TMP';


set V_TBL_NAME = 'TBL_INCOME_TMP';
set loopcount = 0;
set loopcontrol = 1;


for checkLoop as check_cursor cursor with hold for
--游标取数TBL_INCOME_TMP,放入变量
select
RPTNO V_RPTNO,
DS_ID V_DS_ID,
-- YEAR V_YEAR,
-- PAYDATE V_PAYDATE,
BRANCHCODESELF V_BRANCHCODESELF,
BRANCHCODE V_BRANCHCODE,
BANKCODESELF V_BANKCODESELF,
BANKCODE V_BANKCODE,
CUSTTYPE V_CUSTTYPE,
-- IDCODE V_IDCODE,
CUSTCODE V_CUSTCODE,
CUSTNAME V_CUSTNAME,
-- OPPNAME V_OPPNAME,
TXCCY V_TXCCY,
TXCCYSELF V_TXCCYSELF,
TXAMT V_TXAMT,
TXAMTUSD V_TXAMTUSD,
EXRATE V_EXRATE,
LCYAMT V_LCYAMT,
LCYAMTUSD V_LCYAMTUSD,
-- LCYACC V_LCYACC,
FCYAMT V_FCYAMT,
FCYAMTUSD V_FCYAMTUSD,
-- FCYACC V_FCYACC,
OTHAMT V_OTHAMT,
OTHAMTUSD V_OTHAMTUSD,
-- OTHACC V_OTHACC,
METHODSELF V_METHODSELF,
METHOD V_METHOD,
-- BANKBUSICODE V_BANKBUSICODE,
INCHARGECCY V_INCHARGECCY,
INCHARGECCYSELF V_INCHARGECCYSELF,
INCHARGEAMT V_INCHARGEAMT,
-- INCHARGEAMTUSD V_INCHARGEAMTUSD,
OUTCHARGECCY V_OUTCHARGECCY,
OUTCHARGECCYSELF V_OUTCHARGECCYSELF,
OUTCHARGEAMT V_OUTCHARGEAMT,
-- OUTCHARGEAMTUSD V_OUTCHARGEAMTUSD,
-- BANKUSERTEL V_BANKUSERTEL,
COUNTRYCODE V_COUNTRYCODE,
COUNTRYCODESELF V_COUNTRYCODESELF,
PAYTYPE V_PAYTYPE,
TXCODE1 V_TXCODE1,
TXCODE1SELF V_TXCODE1SELF,
TXAMT1 V_TXAMT1,
TXAMT1USD V_TXAMT1USD,
-- TXREM1 V_TXREM1,
TXCODE2 V_TXCODE2,
TXCODE2SELF V_TXCODE2SELF,
TXAMT2 V_TXAMT2,
TXAMT2USD V_TXAMT2USD,
-- TXREM2 V_TXREM2,
-- ISWRITEOFF V_ISWRITEOFF,
-- BILLNO V_BILLNO,
-- RPTUSER V_RPTUSER,
-- RPTTEL V_RPTTEL,
-- RPTDATE V_RPTDATE,
-- UNDERLIMIT V_UNDERLIMIT,
-- PAYATTR V_PAYATTR,
-- PAYATTRSELF V_PAYATTRSELF,
-- ISPRINTED V_ISPRINTED,
-- OSAMT V_OSAMT,
-- OSAMTUSD V_OSAMTUSD,
-- WRITEOFFAMT V_WRITEOFFAMT,
-- WRITEOFFAMTUSD V_WRITEOFFAMTUSD,
-- WRITEOFFUSER V_WRITEOFFUSER,
-- WRITEOFFTEL V_WRITEOFFTEL,
-- WRITEOFFDATE V_WRITEOFFDATE,
RPTSTS V_RPTSTS,
RECSTS V_RECSTS,
-- FATCHNO V_FATCHNO,
CHECK_STATE V_CHECK_STATE
from TBL_INCOME_TMP
for update
do

set rowcount = 1;

set V_PK_VALUE = V_RPTNO;
update TBL_INCOME_TMP set CHECK_STATE = '1' where current of check_cursor;


--数据源校验
--Ds_Id为1、2、3或4
-- if V_DS_ID = 1 or V_DS_ID = 2 or V_DS_ID = 3 or V_DS_ID = 4 then

-- else

-- end if;


--对金额字段进行非空处理
--收款金额TxAmt
--结汇金额LcyAmt
--现汇金额FcyAmt
--其他金额OthAmt
update TBL_INCOME_TMP set TxAmt = coalesce(V_TXAMT, 0) where current of check_cursor;
update TBL_INCOME_TMP set LcyAmt = coalesce(V_LCYAMT, 0) where current of check_cursor;
update TBL_INCOME_TMP set FcyAmt = coalesce(V_FCYAMT, 0) where current of check_cursor;
update TBL_INCOME_TMP set OthAmt = coalesce(V_OTHAMT, 0) where current of check_cursor;

--对限额标记字段进行赋值
if V_TXAMT <= 2000 then
update TBL_INCOME_TMP set UNDERLIMIT = 'Y' where current of check_cursor;
else
update TBL_INCOME_TMP set UNDERLIMIT = 'N' where current of check_cursor;
end if;


--申报号里的时间相关校验
set V_RPTNO_YEAR = substr(V_RPTNO, 13, 2);
set rowcount = 1;
if V_RPTNO_YEAR > '60' then
set V_RPTNO_DATE = '19' || V_RPTNO_YEAR || '-' || substr(V_RPTNO, 15 ,2) || '-' || substr(V_RPTNO, 17 ,2);
else
set V_RPTNO_DATE = '20' || V_RPTNO_YEAR || '-' || substr(V_RPTNO, 15 ,2) || '-' || substr(V_RPTNO, 17 ,2);
end if;
set V_DATE = cast(V_RPTNO_DATE as date);
if rowcount = 6 then
update TBL_INCOME_TMP set CHECK_STATE = '0' where current of check_cursor;
set V_ERR_TYPE = '0101';
set V_DESCRIPTION = '涉外收入申报单:申报单号中日期非法:'||V_RPTNO_DATE;
call check_log(V_TBL_NAME, V_PK_VALUE, V_ERR_TYPE, V_DESCRIPTION);
end if;


--——————————————————————————————————
--非删除记录才校验
if not (V_RECSTS = 'D') then
--——————————————————————————————————


--单位代码的校验
--组织机构代码CustCode在单位基本情况表TBL_CUSTOMERINFO中是否存在

--if not ((V_DS_ID = 1 or V_DS_ID = 2) and V_RPTSTS = '0') then
set rowcount = 1;
select
CUSTNAME
into
VAR_CUSTNAME
from TBL_CUSTOMERINFO
where CUSTCODE = V_CUSTCODE;
if rowcount = 0 and V_CUSTTYPE = 'C' and V_RPTSTS = '1' then
update TBL_INCOME_TMP set CHECK_STATE = '0' where current of check_cursor;
set V_ERR_TYPE = '0102';
set V_DESCRIPTION = '涉外收入申报单:单位代码不存在';
call check_log(V_TBL_NAME, V_PK_VALUE, V_ERR_TYPE, V_DESCRIPTION);
set V_PK = V_CUSTCODE;
set V_DESCRIPTION = '单位基本情况表:单位代码不存在';
call check_log(STR_TBL_CUSTOMERINFO, V_PK, V_ERR_TYPE, V_DESCRIPTION);
else
if (V_DS_ID = 1 or V_DS_ID = 2) and (V_CUSTNAME is null or ltrim(V_CUSTNAME) = '') then
update TBL_INCOME_TMP set CUSTNAME = VAR_CUSTNAME where current of check_cursor;
end if;
end if;
--end if;


--——————————————————————————————————
--非删除记录才校验
end if;
--——————————————————————————————————



set loopcount = loopcount + 1;

if loopcount >= 8000 * loopcontrol then
set loopcontrol = loopcontrol + 1;
call logNote(tblname, 'PR_income_check处理了'||char(loopcount)||'条数据',0, '0');
commit;
end if;


end for;

-------
call logNote(tblname, 'PR_income_check结束',0, '0');

commit;

end@
PART I – 概览.........................................................................................................................11 第 1章 – DB2 Express-C是什么?..........................................................................................13 1.1免费开发、部署和分发… 无限制!...............................................................................13 1.2用户帮助和技术支持.....................................................................................................14 1.3 DB2服务器..................................................................................................................14 1.4 DB2客户端和驱动........................................................................................................14 1.5 应用程序开发的自由性.................................................................................................15 1.6 DB2 版本号与 DB2 版本分类........................................................................................16 1.7升级到其它的 DB2版本................................................................................................16 1.8 DB2 Express-C的维护.................................................................................................16 1.9相关免费软件................................................................................................................17 1.9.1 IBM数据工作室(Data Studio)...........................................................................17 1.9.2 DB2 Net Search Extender .....................................................................................17 1.9.3 Starter Toolkit for DB2 on Rails.............................................................................17 1.9.4 Web 2.0 Starter Toolkit for DB2 ............................................................................17 1.9.5 WebSphere Application Server – Community Edition............................................18 第 2章 – DB2相关特性和产品................................................................................................19 2.1 DB2 Express-C订购中包含的功能...............................................................................22 2.1.1 Fix packs补丁包...................................................................................................22 2.1.2高可用性灾难恢复(HADR) ................................................................................22 2.1.3数据复制(Data Replication)...............................................................................22 2.2 DB2 Express-C所不具备的功能...................................................................................23 2.2.1数据库分区............................................................................................................23 2.2.2连接集中器(Connection Concentrator ).............................................................23 2.2.3 Geodetic Extender ................................................................................................23 2.2.4工作负载管理(Workload Management, WLM) .......................................................24 2.3 DB2相关收费产品........................................................................................................24 2.3.1 DB2连接(DB2 Connect)...................................................................................24 2.3.2 WebSphere Federation Server..............................................................................24 2.3.3 WebSphere Replication Server .............................................................................25 第 3章 – 安装 DB2..................................................................................................................27 3.1安装前提条件................................................................................................................27 3.2操作系统中的安装权限.................................................................................................27 3.3安装向导.......................................................................................................................27 3.4自动安装.......................................................................................................................31 实验 #1 安装DB2 Express-C,创建 SAMPLE数据库........................................................32 第 4章 – DB2的应用环境.......................................................................................................35 实验 #2 - 创建一个新的数据库............................................................................................43 4.1 DB2配置......................................................................................................................44 4.1.1 环境变量................................................................................................................44 4.1.2 数据库管理器配置文件(dbm cfg) ...........................................................................44 4.1.3 数据库配置文件(db cfg)....................................................................................46 4.1.4 DB2 概要文件注册表.............................................................................................47 4.2 DB2管理服务器...........................................................................................................48 实验 #3 – 实例、数据库和配置管理....................................................................................49 第 5章 – DB2工具..................................................................................................................51 5.1控制中心(Control Center).........................................................................................52 5.2命令编辑器(Command Editor) .................................................................................55 5.3 SQL帮助向导(SQL Assist Wizard ).........................................................................57 5.4 显示SQL按钮..............................................................................................................58 实验 #4 使用脚本填充EXPRESS数据库...........................................................................59 5.5 脚本..............................................................................................................................60 5.5.1 SQL脚本...............................................................................................................60 5.5.2操作系统(shell)脚本..........................................................................................61 实验 #5 为EXPRESS数据库创建一个安装脚本.................................................................62 5.6任务中心(Task Center )...........................................................................................65 5.6.1工具目录(Tools Catalog)数据库........................................................................65 5.7 日志(Journal)...........................................................................................................66 5.8运行状况监视器(Health Monitor ).............................................................................67 5.8.1运行状况中心(Health Center )...........................................................................68 PART II – DB2 Express-C 数据库管......................................................................................71 第 6章 – DB2体系结构...........................................................................................................73 6.1 DB2进程模型...............................................................................................................73 6.2 DB2内存模型...............................................................................................................74 6.3 DB2存储模型...............................................................................................................75 6.3.1数据页和扩展数据块..............................................................................................75 6.3.2缓冲池...................................................................................................................76 6.3.3表空间...................................................................................................................77 第 7章 – DB2 客户端的连接....................................................................................................81 7.1 DB2 目录......................................................................................................................81 7.2 配置助手(Configuration Assistant )..........................................................................82 7.2.1服务器端的安装要求..............................................................................................82 7.2.2 Setup required at the client 客户端的安装要求......................................................84 7.2.3建立客户端与服务器端概要文件............................................................................87 实验 #6 使用配置助手........................................................................................................90 第 8章 – 数据库对象...............................................................................................................93 8.1 模式..............................................................................................................................93 8.2表.................................................................................................................................93 8.2.1数据类型................................................................................................................93 8.2.2标识列...................................................................................................................96 8.2.3序列对象................................................................................................................96 8.2.4系统目录表............................................................................................................97 8.2.5已声明临时表.........................................................................................................97 实验 #7 创建一个数据表.....................................................................................................99 8.3视图............................................................................................................................101 8.4索引............................................................................................................................101 8.4.1 Design Advisor ....................................................................................................101 8.5参照完整性.................................................................................................................102 第 9章 – 数据迁移工具..........................................................................................................105 9.1 导出(EXPORT)工具...............................................................................................106 9.2 导入(IMPORT)工具................................................................................................106 9.3 使用 LOAD来导入......................................................................................................107 9.4 db2move 工具...........................................................................................................108 9.5 db2look 工具..............................................................................................................109 实验 #8 导出EXPRESS数据库的DDL............................................................................111 第 10章 – 数据库安全...........................................................................................................115 10.1 认证..........................................................................................................................116 10.2 授权..........................................................................................................................116 10.3 DBADM权限............................................................................................................118 10.4 PUBLIC 组...............................................................................................................119 10.5 GRANT和REVOKE语句 ........................................................................................119 10.6 查看授权和特权........................................................................................................119 10.7 关于组特权...............................................................................................................121 实验 #9 授予和撤销用户的权限........................................................................................122 第 11章 – 备份和恢复...........................................................................................................125 11.1 数据库的日志记录....................................................................................................125 11.2 日志的类型...............................................................................................................126 11.3 日志记录的类型.......................................................................................................126 11.3.1 循环日志记录 ....................................................................................................126 11.3.2 档案日志记录和日志保留...................................................................................127 11.4 从控制中心进行数据库日志记录...............................................................................127 11.5 日志记录的参数........................................................................................................129 11.6 数据库备份...............................................................................................................129 实验 #10 – 安排一个备份计划..........................................................................................131 11.7 数据库恢复...............................................................................................................133 11.7.1 恢复类型............................................................................................................133 11.7.2 数据库恢复........................................................................................................133 11.8 其他关于备份和恢复的操作......................................................................................134 第 12章 – 维护任务...............................................................................................................135 12.1 重组(REORG)、运行统计(RUNSTATS)、重绑定(REBIND).......................135 12.1.1重组(REORG)命令........................................................................................135 12.1.2运行统计(RUNSTATS)命令 ..........................................................................136 12.1.3 绑定/重新绑定.................................................................................................136 12.1.4 在控制中心执行维护工作...................................................................................137 12.2 维护方式...................................................................................................................139 实验#11 – 配置自动维护.................................................................................................141 第 13章 – 并行与锁定...........................................................................................................143 13.1 事务(Transactions)..............................................................................................143 13.2 并行(Concurrency) ..............................................................................................143 13.3无并行控制导致的问题 .............................................................................................144 13.3.1丢失更新(Lost update)..................................................................................145 13.3.2未落实的读(Uncommitted read)....................................................................145 13.3.3不可重复读(Non-repeatable read).................................................................146 13.3.4幻象(Phantom read)......................................................................................146 13.4隔离级别(Isolation Levels)...................................................................................147 13.4.1未落实的读........................................................................................................147 13.4.2游标稳定性........................................................................................................147 13.4.3读稳定性............................................................................................................148 13.4.4可重复读............................................................................................................148 13.4.5隔离级别对比.....................................................................................................148 13.4.6设定隔离级别.....................................................................................................149 13.5锁定升级...................................................................................................................150 13.6锁定监视...................................................................................................................151 13.7锁定等待...................................................................................................................151 13.8死锁的引发与侦测.....................................................................................................152 13.9并行与锁定的最佳实践:..........................................................................................153 PART III – DB2 Express-C应用程序开发............................................................................155 第 14章 –SQL PL 存储过程..................................................................................................157 14.1 IBM数据工作室(Data Studio)..............................................................................158 14.1.2在Data Studio中创建一个存储过程..................................................................159 14.2 SQL PL 存储过程基础..............................................................................................161 14.2.1存储过程的结构.................................................................................................161 14.2.2 可选的存储过程属性..........................................................................................162 14.2.3参数...................................................................................................................162 14.2.4 SQL PL存储过程中的注释................................................................................163 14.2.5 复合语句............................................................................................................163 14.2.6 变量声明............................................................................................................163 14.2.7 赋值语句............................................................................................................164 14.3 游标..........................................................................................................................164 14.4 流控制......................................................................................................................164 14.5 调用存储过程 ...........................................................................................................165 14.6 错误和情况处理器....................................................................................................166 14.7 动态SQL..................................................................................................................168 第 15章 – 直接插入 SQL 过程语言、触发器、用户定义函数(UDF)..................................169 15.1直接插入SQL PL .....................................................................................................169 15.2 触发器(Trigger)....................................................................................................170 15.2.1 触发器的类型 ....................................................................................................170 实验 #12 从控制中心创建一个触发器...............................................................................174 15.3 用户定义函数 (UDF).................................................................................................177 15.3.1 标量函数(Scalar function).............................................................................177 15.3.2 表函数(Table function)..................................................................................178 实验 #13 使用IBM Data Studio创建用户定义函数(UDF)............................................179 第 16章 – DB2 pureXML......................................................................................................181 16.1 在数据库中使用XML................................................................................................181 16.2 XML数据库..............................................................................................................182 16.2.1 启用 XML的数据库............................................................................................182 16.2.2 原生 XML数据库...............................................................................................182 16.3 DB2中的XML..........................................................................................................183 16.3.1 pureXML 技术优势............................................................................................184 16.3.2 XPath基础........................................................................................................185 16.3.3 XQuery的定义..................................................................................................188 16.3.4 插入 XML文档...................................................................................................189 16.3.5 查询 XML数据...................................................................................................191 16.3.6 使用 SQL/XML执行联合操作............................................................................196 16.3.7 使用 XQuery执行联合操作................................................................................196 16.3.8 更新与删除操作.................................................................................................197 16.3.9 XML 索引.........................................................................................................198 实验 #14 - SQL/XML 和 XQuery.......................................................................................200 第 17章 – 使用 Java、PHP和 Ruby进行数据库应用开发...................................................201 17.1 Java应用程序开发...................................................................................................201 17.1.1 JDBC类型 2驱动程序.......................................................................................201 17.1.2 JDBC类型 4驱动程序.......................................................................................202 17.2 PHP应用程序开发...................................................................................................203 17.2.1 DB2为PHP提供的连接选项.............................................................................203 17.2.2 Zend Core for IBM.............................................................................................204 17.3 Ruby on Rails应用程序开发.....................................................................................206 17.3.1 Startup Toolkit for DB2 on Rails ........................................................................206 附录 A — 排除故障...............................................................................................................207 A.1 查找错误代码的更多信息...........................................................................................207 A.2 SQLCODE与SQLSTATE .........................................................................................208 A.3 DB2 管理通知日志.....................................................................................................208 A.4 db2diag.log................................................................................................................209 A.5 CLI追踪.....................................................................................................................209 A.6 DB2缺陷与补丁.........................................................................................................209 参考资源 ...............................................................................................................................210 网站..................................................................................................................................210 书籍..................................................................................................................................211
资源简介 第 1章 – DB2 Express-C是什么?..........................................................................................13 1.1免费开发、部署和分发… 无限制!...............................................................................13 1.2用户帮助和技术支持.....................................................................................................14 1.3 DB2服务器..................................................................................................................14 1.4 DB2客户端和驱动........................................................................................................14 1.5 应用程序开发的自由性.................................................................................................15 1.6 DB2 版本号与 DB2 版本分类........................................................................................16 1.7升级到其它的 DB2版本................................................................................................16 1.8 DB2 Express-C的维护.................................................................................................16 1.9相关免费软件................................................................................................................17 1.9.1 IBM数据工作室(Data Studio)...........................................................................17 1.9.2 DB2 Net Search Extender .....................................................................................17 1.9.3 Starter Toolkit for DB2 on Rails.............................................................................17 1.9.4 Web 2.0 Starter Toolkit for DB2 ............................................................................17 1.9.5 WebSphere Application Server – Community Edition............................................18 第 2章 – DB2相关特性和产品................................................................................................19 2.1 DB2 Express-C订购中包含的功能...............................................................................22 2.1.1 Fix packs补丁包...................................................................................................22 2.1.2高可用性灾难恢复(HADR) ................................................................................22 2.1.3数据复制(Data Replication)...............................................................................22 2.2 DB2 Express-C所不具备的功能...................................................................................23 2.2.1数据库分区............................................................................................................23 2.2.2连接集中器(Connection Concentrator ).............................................................23 2.2.3 Geodetic Extender ................................................................................................23 2.2.4工作负载管理(Workload Management, WLM) .......................................................24 2.3 DB2相关收费产品........................................................................................................24 2.3.1 DB2连接(DB2 Connect)...................................................................................24 2.3.2 WebSphere Federation Server..............................................................................24 2.3.3 WebSphere Replication Server .............................................................................25 第 3章 – 安装 DB2..................................................................................................................27 3.1安装前提条件................................................................................................................27 3.2操作系统中的安装权限.................................................................................................27 3.3安装向导.......................................................................................................................27 3.4自动安装.......................................................................................................................31 实验 #1 安装DB2 Express-C,创建 SAMPLE数据库........................................................32 第 4章 – DB2的应用环境.......................................................................................................35 实验 #2 - 创建一个新的数据库............................................................................................43 4.1 DB2配置......................................................................................................................44 4.1.1 环境变量................................................................................................................44 4.1.2 数据库管理器配置文件(dbm cfg) ...........................................................................44 4.1.3 数据库配置文件(db cfg)....................................................................................46 4.1.4 DB2 概要文件注册表.............................................................................................47 4.2 DB2管理服务器...........................................................................................................48 实验 #3 – 实例、数据库和配置管理....................................................................................49 第 5章 – DB2工具..................................................................................................................51 5.1控制中心(Control Center).........................................................................................52 5.2命令编辑器(Command Editor) .................................................................................55 5.3 SQL帮助向导(SQL Assist Wizard ).........................................................................57 5.4 显示SQL按钮..............................................................................................................58 实验 #4 使用脚本填充EXPRESS数据库...........................................................................59 5.5 脚本..............................................................................................................................60 5.5.1 SQL脚本...............................................................................................................60 5.5.2操作系统(shell)脚本..........................................................................................61 实验 #5 为EXPRESS数据库创建一个安装脚本.................................................................62 5.6任务中心(Task Center )...........................................................................................65 5.6.1工具目录(Tools Catalog)数据库........................................................................65 5.7 日志(Journal)...........................................................................................................66 5.8运行状况监视器(Health Monitor ).............................................................................67 5.8.1运行状况中心(Health Center )...........................................................................68 PART II – DB2 Express-C 数据库管......................................................................................71 第 6章 – DB2体系结构...........................................................................................................73 6.1 DB2进程模型...............................................................................................................73 6.2 DB2内存模型...............................................................................................................74 6.3 DB2存储模型...............................................................................................................75 6.3.1数据页和扩展数据块..............................................................................................75 6.3.2缓冲池...................................................................................................................76 6.3.3表空间...................................................................................................................77 第 7章 – DB2 客户端的连接....................................................................................................81 7.1 DB2 目录......................................................................................................................81 7.2 配置助手(Configuration Assistant )..........................................................................82 7.2.1服务器端的安装要求..............................................................................................82 7.2.2 Setup required at the client 客户端的安装要求......................................................84 7.2.3建立客户端与服务器端概要文件............................................................................87 实验 #6 使用配置助手........................................................................................................90 第 8章 – 数据库对象...............................................................................................................93 8.1 模式..............................................................................................................................93 8.2表.................................................................................................................................93 8.2.1数据类型................................................................................................................93 8.2.2标识列...................................................................................................................96 8.2.3序列对象................................................................................................................96 8.2.4系统目录表............................................................................................................97 8.2.5已声明临时表.........................................................................................................97 实验 #7 创建一个数据表.....................................................................................................99 8.3视图............................................................................................................................101 8.4索引............................................................................................................................101 8.4.1 Design Advisor ....................................................................................................101 8.5参照完整性.................................................................................................................102 第 9章 – 数据迁移工具..........................................................................................................105 9.1 导出(EXPORT)工具...............................................................................................106 9.2 导入(IMPORT)工具................................................................................................106 9.3 使用 LOAD来导入......................................................................................................107 9.4 db2move 工具...........................................................................................................108 9.5 db2look 工具..............................................................................................................109 实验 #8 导出EXPRESS数据库的DDL............................................................................111 第 10章 – 数据库安全...........................................................................................................115 10.1 认证..........................................................................................................................116 10.2 授权..........................................................................................................................116 10.3 DBADM权限............................................................................................................118 10.4 PUBLIC 组...............................................................................................................119 10.5 GRANT和REVOKE语句 ........................................................................................119 10.6 查看授权和特权........................................................................................................119 10.7 关于组特权...............................................................................................................121 实验 #9 授予和撤销用户的权限........................................................................................122 第 11章 – 备份和恢复...........................................................................................................125 11.1 数据库的日志记录....................................................................................................125 11.2 日志的类型...............................................................................................................126 11.3 日志记录的类型.......................................................................................................126 11.3.1 循环日志记录 ....................................................................................................126 11.3.2 档案日志记录和日志保留...................................................................................127 11.4 从控制中心进行数据库日志记录...............................................................................127 11.5 日志记录的参数........................................................................................................129 11.6 数据库备份...............................................................................................................129 实验 #10 – 安排一个备份计划..........................................................................................131 11.7 数据库恢复...............................................................................................................133 11.7.1 恢复类型............................................................................................................133 11.7.2 数据库恢复........................................................................................................133 11.8 其他关于备份和恢复的操作......................................................................................134 第 12章 – 维护任务...............................................................................................................135 12.1 重组(REORG)、运行统计(RUNSTATS)、重绑定(REBIND).......................135 12.1.1重组(REORG)命令........................................................................................135 12.1.2运行统计(RUNSTATS)命令 ..........................................................................136 12.1.3 绑定/重新绑定.................................................................................................136 12.1.4 在控制中心执行维护工作...................................................................................137 12.2 维护方式...................................................................................................................139 实验#11 – 配置自动维护.................................................................................................141 第 13章 – 并行与锁定...........................................................................................................143 13.1 事务(Transactions)..............................................................................................143 13.2 并行(Concurrency) ..............................................................................................143 13.3无并行控制导致的问题 .............................................................................................144 13.3.1丢失更新(Lost update)..................................................................................145 13.3.2未落实的读(Uncommitted read)....................................................................145 13.3.3不可重复读(Non-repeatable read).................................................................146 13.3.4幻象(Phantom read)......................................................................................146 13.4隔离级别(Isolation Levels)...................................................................................147 13.4.1未落实的读........................................................................................................147 13.4.2游标稳定性........................................................................................................147 13.4.3读稳定性............................................................................................................148 13.4.4可重复读............................................................................................................148 13.4.5隔离级别对比.....................................................................................................148 13.4.6设定隔离级别.....................................................................................................149 13.5锁定升级...................................................................................................................150 13.6锁定监视...................................................................................................................151 13.7锁定等待...................................................................................................................151 13.8死锁的引发与侦测.....................................................................................................152 13.9并行与锁定的最佳实践:..........................................................................................153 PART III – DB2 Express-C应用程序开发............................................................................155 第 14章 –SQL PL 存储过程..................................................................................................157 14.1 IBM数据工作室(Data Studio)..............................................................................158 14.1.2在Data Studio中创建一个存储过程..................................................................159 14.2 SQL PL 存储过程基础..............................................................................................161 14.2.1存储过程的结构.................................................................................................161 14.2.2 可选的存储过程属性..........................................................................................162 14.2.3参数...................................................................................................................162 14.2.4 SQL PL存储过程中的注释................................................................................163 14.2.5 复合语句............................................................................................................163 14.2.6 变量声明............................................................................................................163 14.2.7 赋值语句............................................................................................................164 14.3 游标..........................................................................................................................164 14.4 流控制......................................................................................................................164 14.5 调用存储过程 ...........................................................................................................165 14.6 错误和情况处理器....................................................................................................166 14.7 动态SQL..................................................................................................................168 第 15章 – 直接插入 SQL 过程语言、触发器、用户定义函数(UDF)..................................169 15.1直接插入SQL PL .....................................................................................................169 15.2 触发器(Trigger)....................................................................................................170 15.2.1 触发器的类型 ....................................................................................................170 实验 #12 从控制中心创建一个触发器...............................................................................174 15.3 用户定义函数 (UDF).................................................................................................177 15.3.1 标量函数(Scalar function).............................................................................177 15.3.2 表函数(Table function)..................................................................................178 实验 #13 使用IBM Data Studio创建用户定义函数(UDF)............................................179 第 16章 – DB2 pureXML......................................................................................................181 16.1 在数据库中使用XML................................................................................................181 16.2 XML数据库..............................................................................................................182 16.2.1 启用 XML的数据库............................................................................................182 16.2.2 原生 XML数据库...............................................................................................182 16.3 DB2中的XML..........................................................................................................183 16.3.1 pureXML 技术优势............................................................................................184 16.3.2 XPath基础........................................................................................................185 16.3.3 XQuery的定义..................................................................................................188 16.3.4 插入 XML文档...................................................................................................189 16.3.5 查询 XML数据...................................................................................................191 16.3.6 使用 SQL/XML执行联合操作............................................................................196 16.3.7 使用 XQuery执行联合操作................................................................................196 16.3.8 更新与删除操作.................................................................................................197 16.3.9 XML 索引.........................................................................................................198 实验 #14 - SQL/XML 和 XQuery.......................................................................................200 第 17章 – 使用 Java、PHP和 Ruby进行数据库应用开发...................................................201 17.1 Java应用程序开发...................................................................................................201 17.1.1 JDBC类型 2驱动程序.......................................................................................201 17.1.2 JDBC类型 4驱动程序.......................................................................................202 17.2 PHP应用程序开发...................................................................................................203 17.2.1 DB2为PHP提供的连接选项.............................................................................203 17.2.2 Zend Core for IBM.............................................................................................204 17.3 Ruby on Rails应用程序开发.....................................................................................206 17.3.1 Startup Toolkit for DB2 on Rails ........................................................................206 附录 A — 排除故障...............................................................................................................207 A.1 查找错误代码的更多信息...........................................................................................207 A.2 SQLCODE与SQLSTATE .........................................................................................208 A.3 DB2 管理通知日志.....................................................................................................208 A.4 db2diag.log................................................................................................................209 A.5 CLI追踪.....................................................................................................................209 A.6 DB2缺陷与补丁.........................................................................................................209 参考资源 ...............................................................................................................................210 网站..................................................................................................................................210 书籍..................................................................................................................................211

5,889

社区成员

发帖
与我相关
我的任务
社区描述
IBM DB2 是美国IBM公司开发的一套关系型数据库管理系统,它主要的运行环境为UNIX(包括IBM自家的AIX)、Linux、IBM i(旧称OS/400)、z/OS,以及Windows服务器版本
社区管理员
  • DB2
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧