- 博客(20)
- 资源 (5)
- 收藏
- 关注
RSS订阅原创 db2数据去重复
delete from (select s_orgcode,s_payeeacct,row_number() over(partition by s_orgcode,s_payeeacct) as row_num from TS_SPECACCTINFO) a where row_num > 1
2018-06-28 11:54:12
1786
原创 db2创建序列sequence
----创建序列drop sequence TMIS_TR_BDGINCOME_NORM_SEQ;CREATE SEQUENCE TMIS_TR_BDGINCOME_NORM_SEQ AS BIGINT START WITH 1 INCREMENT BY 1 NO MAXVALUE CACHE 1000 CYCLE ORDER;drop sequence TMIS_TR_BDGINCOME_TR...
2018-06-28 11:53:28
8353
原创 db2 清空数据表
import from NUL of del replace into TR_DAY_TAX ;import from NUL of del replace into TR_DAY_TAX1;import from NUL of del replace into TR_DAY_TAX2;
2018-06-28 11:53:09
940
原创 db2 -设置修改数据库的基本信息
update db cfg for TMISMGDB using DFT_QUERYOPT 3;---设置修改数据库 TMISMGDB的基本信息update db cfg for TMISMGDB using MAXAPPLS 120;update db cfg for TMISMGDB using PCKCACHESZ 16384;update db cfg for TMISMGDB using...
2018-06-28 11:52:54
3255
原创 db2 substr-current的使用
select case bigsmall.bankno when '102' then '工商银行'when '103' then '农业银行'when '104' then '中国银行'when '105' then '建设银行'when '301' then '交通银行'when '302' then '中信银行'when '303' then '光大银行'when '304' then '华...
2018-06-28 11:52:41
752
原创 db2 exists的使用
/*==============================================================*//* Database name: 国债兑付业务管理系统 *//* DBMS name: Sybase AS Enterprise 11.x *//*...
2018-06-28 11:52:26
6974
原创 db2 数据备份bat 命令
@rem 设置Server和实例@set Server_example=BAILW-91E8DA5A6\SQLEXPRESS@rem 设置数据库名称@set dbName=savingbonds@rem 设置数据库用户@set dbUser=sa@rem 设置数据库密码@set dbPassword=tmisusr@rem 设置文件路径@set filePath=D:\common\dbbase\...
2018-06-28 11:52:06
648
原创 db2 创建索引index
drop index IX_TPP_AHEADCASH_PROFITRATIO1;create index IX_TPP_AHEADCASH_PROFITRATIO1 on TPP_AHEADCASH_PROFITRATIO(I_REFDEBTSRLNO ASC,S_AHEADDRAWPERIODCODE ASC) disallow reverse scans PctFree 10 MinPctU...
2018-06-28 11:36:38
8239
原创 db2创建视图view
drop view VW_TCBS_TB_ACCT_BALANCE;--==============================================================-- View：VW_TCBS_TB_ACCT_BALANCE--==============================================================create ...
2018-06-28 11:35:38
7255
原创 db2 基础操作
---清空cls----查看数据版本select * from sysibm.sysversions---重启数据Restart db twcs177---激活数据库 Activate db twcs177---停止数据库Deactivate db twcs177---数据库链接名db2 catalog tcpip mode mynode remote 10.2.2.177 server 620...
2018-06-25 19:17:55
370
原创 db2存储过程WHILE-DO的使用
SET SCHEMA TMISUSR ;SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TMISUSR";CREATE PROCEDURE "TMISUSR"."PRC_BBUS_GATHER_TRE" (IN "PI_I_DATE" INTEGER, IN "PI
2018-06-25 19:16:45
4460
原创 db2存储过程CASE-WHEN的使用
SET SCHEMA TMISUSR ;SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TMISUSR";CREATE PROCEDURE "TMISUSR"."PRC_TAX_TOPORG_RANK" (IN "PI_D_DATEINIT" DATE, IN "P
2018-06-25 19:15:57
2382
原创 db2存储过程if的使用
SET SCHEMA TMISUSR ;SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TMISUSR";CREATE PROCEDURE "TMISUSR"."PRC_BBUS_GATHER_ORGUSR" (IN "PI_I_DATE" INTEGER, IN "
2018-06-25 19:14:52
6657
原创 db2存储过程insert数据
此示例是存储过程简单的插入数据，如有更新、合并，需要自己根据项目需求实现。SET SCHEMA TMISUSR ;SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TMISUSR";CREATE PROCEDURE "TMISUSR"."PRO_ADD_LOG" (IN "FISK_CODE
2018-06-25 19:13:32
3438
原创 存储过程多游标的使用
多游标使用，条件不满足时游标可以的返回用户不同的结果。SET SCHEMA TMISUSR ;SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TMISUSR";CREATE PROCEDURE "TMISUSR"."PRC_BBUS_GATHER_BUS" (IN "PI_I_DATE&q
2018-06-25 19:10:03
1308
原创 db2存储过程游标的使用
存储过程取结果集用到数据游标下面示例，是个简单的游标使用，具体需要自己扩展SET SCHEMA TMISUSR ;SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TMISUSR";CREATE PROCEDURE "TMISUSR"."PRO_SELECT_DEBT" (IN "PI_DE
2018-06-25 19:07:36
5341
原创 DB2 自定义函数（2）
SET SCHEMA TMISUSR;SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TMISUSR";CREATE FUNCTION "TMISUSR"."F_DEBTGETTRELIST" ("PI_I_YEAR" INTEGER, "PI_S_TREAREA&q
2018-06-25 19:03:09
1143
原创 DB2自定义函数获取月第一天
SET SCHEMA TMISUSR;SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TMISUSR";CREATE FUNCTION "TMISUSR"."F_GETSTARTDAY" ("PI_S_RPTTYPE" CHARACTER(1), "PI_D_DATE
2018-06-25 19:02:17
946
原创 动态卸载分区表数据
此存储过程实现动态卸载分区表数据。SET SCHEMA TMISUSR ;SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TMISUSR";CREATE PROCEDURE "TMISUSR"."PRC_BATCH_DETACH_DATA" (IN "PI_D_ACCT" DAT
2018-06-21 17:30:15
273
原创 数据库之间数同步
有些业务需要不同数据库的数据进行同步。下面的存储过程实现数据同步SET SCHEMA TMISUSR ;SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TMISUSR";CREATE PROCEDURE "TMISUSR"."PRC_BATCH_PAYACTOR_MOVE" (OUT "P
2018-06-21 17:28:49
170
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人