ECAP下db2基本操作
启动db2数据库su - db2inst1db2start
下拉框表、授权表、菜单表
select * from t_option where OPTION_SET in ('PJtype','PJstat')export to t_option.txt.20100906 of del select * from t_optionexport to t_authorization.txt.zq_e.20100906 of del select * from t_authorization where TRANSCODE like '%ZQ_E%'export to t_child_busi_tran.txt.zq_e.20100906 of del select * from t_child_busi_tran where TRAN_CODE like '%ZQ_E%'export to t_teller_busi.txt.all.20100906 of del select * from t_teller_busi
查询柜员
select * from t_teller where brch_no1 = '06221' and teller_level >10机构柜员信息查询select * from t_brhm where branch_no like '141%'select * from t_teller where brch_no1 = '08593'export to t_teller.txt.20100901 of del select * from t_teller where brch_no1 = '08593'
批量执行sql
db2 connect to ecapdb2 -tvf test.sql
select * from t_child_busi_tran where TRAN_CODE like 'JM_F12_9140%';select * from t_child_busi_tran where TRAN_CODE like 'JM_F11_9140%';
基本操作
db2 connect to ecapdb查看表结构db2 describe table t_authorizationselect * from t_authorization"Column name schema Data type name Length Scale s------------------------------- --------- ------------------- ---------- ----- -TRANSCODE SYSIBM VARCHAR 20 0SUBTRANSCODE SYSIBM VARCHAR 20 0AUTHPOINT SYSIBM INTEGER 4 0AUTHGRADE SYSIBM VARCHAR 8 0AUTHTYPE SYSIBM VARCHAR 8 0PROMPTMSG SYSIBM VARCHAR 500 0AUTHDESCRIP SYSIBM VARCHAR 500 0select * from t_authorization where TRANSCODE = 'GM_F6_91114200'select * from t_authorization where TRANSCODE = '_B2_000045'insert into t_authorization values('GM_F6_91114100','',4,'','302','','')insert into t_authorization values('GM_F6_91114100','',1,'12','100','','')delete from t_authorization where TRANSCODE = 'GM_F6_91114100'db2 commit
db2导表操作
db2 export to t_teller.txt of del select * from t_teller where brch_no1='04446'EXPORT TO myfile.del OF DELMESSAGES msg.out SELECT staff.name, staff.dept, org.location FROM org, staff WHERE org.deptnumb = staff.dept;db2 import from t_teller.txt of del insert into t_teller
db2导出、导入表数据
db2 "export to t_option.txt of del select * from t_option"db2 "import from t_option.txt of del insert into t_option"
修改柜员和授权信息
select TELLER_NO,TELLER_LEVEL from T_TELLER where BRCH_NO1 = '20102'update T_TELLER set TELLER_LEVEL = 7 where TELLER_NO = '0005050'select TELLER_NO,TELLER_LEVEL from T_TELLER where BRCH_NO1 = '20402' and TELLER_NO = '0005050'update t_authorization set AUTHTYPE = '301' where TRANSCODE = '_QY_F3_92607100'update t_authorization set AUTHTYPE = '301' where TRANSCODE = '_QY_F3_92607200'update t_authorization set AUTHTYPE = '100' where TRANSCODE = '_QY_F3_92607100' and AUTHPOINT = 1update t_authorization set AUTHTYPE = '100' where TRANSCODE = '_QY_F3_92607200' and AUTHPOINT = 1
修改下拉框信息
//刷新下拉框 http://108.0.108.221:9080/TxRunAction.do?TranName=clearCache select * from t_option where OPTION_SET = 'VoucherType10'select * from t_option where OPTION_SET = 'aprvsign'delete from t_optionselect * from t_option where OPTION_SET = 'TCchaType'db2 describe table t_optioninsert into t_option values('TCchaType','2','佛山交换','')insert into t_option values('TCchaType','6','香港交换','')insert into t_option values('TCchaType','2','佛山交换','')update t_option set OPTION_REMARK = '全国支票交换' where OPTION_REMARK = '3-全国支票交换'update t_option set OPTION_REMARK = '广州交换' where OPTION_REMARK = '1-广州交换'update t_option set OPTION_REMARK = '深圳交换' where OPTION_REMARK = '2-深圳交换'
增加导航菜单
导航菜单更新后刷新菜单生效http://104.0.40.111:9080/TxRunAction.do?TranName=clearCacheselect * from t_child_busi_transelect * from t_teller_busiexport to t_child_busi_tran.20100710 of del select * from t_child_busi_tranexport to t_teller_busi.20100710 of del select * from t_teller_busidescribe table t_child_busi_trandescribe table t_teller_busiselect * from t_child_busi_tran where TRAN_CODE like '%0010%'select * from t_child_busi_tran where TRAN_CODE = '_B1_000010'delete from t_child_busi_tran where TRAN_CODE = 'TS_F2_907020'insert into t_child_busi_tran values('代理保险',0,'TS_F2_907020','缴费','G02',2,'0',-1,0,0,'1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111','1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111')insert into t_child_busi_tran values('代理保险',0,'TS_F2_907020','缴费','G03',2,'0',-1,0,0,'1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111','1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111')insert into t_child_busi_tran values('存取开销类',0,'_B1_000010','有折现金存款','G03',2,'0',-1,0,0,'1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111','1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111')数据类型 列列名 模式 数据类型名称 长 小数位 NULL------------------------------- --------- ------------------- ---------- ----- ------CHILD_BUSI_NAME SYSIBM VARCHAR 50 0 否 子业务名称CHILD_BUSI_ID SYSIBM BIGINT 8 0 否TRAN_CODE SYSIBM CHARACTER 50 0 否 交易码TRAN_NAME SYSIBM CHARACTER 50 0 否 交易名称TELLER_BUSI_NO SYSIBM VARCHAR 20 0 否 所属业务IDTRAN_SEQ SYSIBM BIGINT 8 0 否 交易序号TRAN_TYPE SYSIBM CHARACTER 1 0 是 交易类型F_AUTHLEVEL SYSIBM INTEGER 4 0 否IS_FINATRAN SYSIBM INTEGER 4 0 否IS_CORRTRAN SYSIBM INTEGER 4 0 否AUTH_GROUP SYSIBM VARCHAR 200 0 是 柜组AUTH_BRANCH SYSIBM VARCHAR 200 0 是列名 模式 数据类型名称 长 小数位 NULL------------------------------- --------- ------------------- ---------- ----- ------TELLER_BUSI_NO SYSIBM INTEGER 4 0 否TELLER_BUSI_NAME SYSIBM VARCHAR 40 0 否TELLER_BUSI_STAT SYSIBM CHARACTER 1 0 否TELLER_BUSI_DETAIL1 SYSIBM VARCHAR 1024 0 是TELLER_BUSI_DETAIL2 SYSIBM VARCHAR 1024 0 是TELLER_BUSI_DETAIL3 SYSIBM VARCHAR 1024 0 是TIME_SIGN SYSIBM BIGINT 8 0 否STR_RESERVE1 SYSIBM VARCHAR 255 0 是STR_RESERVE2 SYSIBM VARCHAR 255 0 是DAC SYSIBM CHARACTER 16 0 是update t_teller_busi set TELLER_BUSI_NAME = '禅城特色支付业务' where TELLER_BUSI_DETAIL1 = 'G16'update t_teller_busi set TELLER_BUSI_NAME = '禅城中间业务' where TELLER_BUSI_DETAIL1 = 'G15'
页:
[1]