db2数据库操作项

1， 建表CREATE DATABASE database-name [IN dbspace-name] [WITH LOG]
2， 删库 DROP DATABASE database-name
3， 格式： DATABASE database-name
4，说明： 选择可访问的库作为当前库。
5，关闭库： CLOSE DATABASE
6，建表格式： CREATE [TEMP] TABLE table-name ( column-name datatype [NOT NULL],…)
7，删除表格式： DROP TABLE table-name
8，SELECT [DISTINCT] select-list FROM tablename [,…]
select-list表的选项 tablename表名


SELECT fname, lname FROM customer WHERE state <> "CA" ORDER BY lname;

SELECT fname, lname, age FROM customer WHERE age BETWEEN 20 and 30;

SELECT max(age), min(age) FROM customer

9.删除列表项格式： DELETE FROM table-name [WHERE condition]

10，插入列表入操作
格式： INSERT INTO table-name [(column-list)]
{VALUES (value-list) | SELECT-statement}

11，修改列表 UPDATE table-name
SET {column-name = expression [,...]| {(col-list) | *} = (expr-list)}
[WHERE condition]
修改还没有遇到



13， EXEC SQL DECLARE GLOBAL TEMPORARY TABLE tmplstb1 LIKE jkdzt NOT LOGGED;


/*---------- 装载数据前准备 ----------*/
EXEC SQL DELETE FROM jkdzt WHERE 1=1;
if( (SQLOK!=SQLCODE) && (SQLNOTFOUND!=SQLCODE) ) {
DISPLAY_INT("\n 装载数据前准备 error0 ! SQLCODE=",SQLCODE);
goto err;
}

EXEC SQL DELETE FROM jkdzd
WHERE trdt=:intrdt
OR trdt<:tpcldt;// ?????
if( (SQLOK!=SQLCODE) && (SQLNOTFOUND!=SQLCODE) ) {
DISPLAY_INT("\n 装载数据前准备 error1 ! SQLCODE=",SQLCODE);
goto err;
}

EXEC SQL UPDATE jkdzd SET ertp='0'//更新数据
WHERE trdt=:yesterday AND pmtx='T';
if( (SQLOK!=SQLCODE) && (SQLNOTFOUND!=SQLCODE) ) {
DISPLAY_INT("\n 装载数据前准备 error2 ! SQLCODE=",SQLCODE);
goto err;
}

EXEC SQL DELETE FROM jkdzp
WHERE trdt=:intrdt;
if( (SQLOK!=SQLCODE) && (SQLNOTFOUND!=SQLCODE) ) {
DISPLAY_INT("\n 装载数据前准备 error3 ! SQLCODE=",SQLCODE);
goto err;
}

/*---------- 创建临时表: 金卡对帐冲正记录临时表 ----------*/
EXEC SQL DECLARE GLOBAL TEMPORARY TABLE tmplstb1 LIKE jkdzt NOT LOGGED;
if( SQLOK != SQLCODE ) {
DISPLAY_INT("\n 创建临时表出错 ! SQLCODE=",SQLCODE);
goto err;
}

/*---------- 创建临时表: 主机对帐撤消记录临时表 ----------*/
EXEC SQL DECLARE GLOBAL TEMPORARY TABLE tmplstb2 LIKE jkdzt NOT LOGGED;
if( SQLOK != SQLCODE ) {
DISPLAY_INT("\n 创建临时表出错 ! SQLCODE=",SQLCODE);
goto err;
}

/*---------- 创建临时表: 对帐差错临时表 ----------*/
EXEC SQL DECLARE GLOBAL TEMPORARY TABLE tmperr LIKE jkdzt NOT LOGGED;
if( SQLOK != SQLCODE ) {
DISPLAY_INT("\n 创建临时表出错 ! SQLCODE=",SQLCODE);
goto err;
}




14， EXEC SQL DECLARE

bkcba_cursor CURSOR FOR
SELECT * FROM bkcba
WHERE cbtrdt=:intrdt
AND cbtrcd IN ('4321','4322','4342') /*4342中部分冲正插bkcba的,全部冲不插*/
AND cbwkzz='' /* 去掉外卡 */
AND cbstcd=:STCDA
FOR READ ONLY;
EXEC SQL OPEN bkcba_cursor;
if(SQLOK!=SQLCODE) {
DISPLAY_INT("\n open cursor bkcba_cursor error!",SQLCODE);
goto err;
}

15,
SELECT * FROM pmrta
WHERE rtcyno='01'
AND (rtefdt,rtirtp,rtirkd,rtperd) in////////////////////////////////在哪些字段
(SELECT MAX(rtefdt),rtirtp,rtirkd,rtperd FROM pmrta WHERE rtcyno='01'
AND rtirtp=:ca2831I.irtp
AND rtirkd='0'
group by rtirtp,rtirkd,rtperd)
AND rtstcd=:STCDA;

16, EXEC SQL DECLARE pmrta_txcur CURSOR FOR//////////////////声明游标
SELECT * FROM pmrta
WHERE rtcyno='01'
AND rtirtp='51'
AND rtirkd='0'
AND rtefdt<=:ca2967I.cldt
ORDER BY rtefdt DESC
FOR READ ONLY WITH UR;
EXEC SQL OPEN pmrta_txcur;
if( SQLOK != SQLCODE ) {
DISPLAY_INT("\n open pmrta_txcur error ! SQLCODE=",SQLCODE);
goto end;


17. v_init_tzinf(&stTzinf);
EXEC SQL SELECT * INTO :stTzinf :idts FROM tzinf///////////////////查询
WHERE tzbrno=:tz6502I.brno
AND tzsbno=:tz6502I.sbno
AND tztrdt=:tz6502I.trdd
AND tzjydm='6500'
AND tzjyls=:tz6502I.tzsq
AND tzstcd=:STCDA;

之间的区别

EXEC SQL DECLARE cur CURSOR for//这是申请游标
SELECT * FROM ddmsa
WHERE msstcd='C'
FETCH FIRST 10 ROWS ONLY;



18 /*----------插入调帐登记表------------*/ ///////////////////插入
EXEC SQL INSERT INTO tzinf VALUES(:stTzinf);
if (SQLOK!=SQLCODE) {
DISPLAY_INT("\n 插入调帐登记表出错! CODE=",SQLCODE);
error.ID=ERROR_ERR;
sprintf(error.MSG,"插入调帐帐登记表出错!");
goto end;
}




19。 vi操作符 mkdir新建文件夹

20.显示当前进程 ps
21。移动文件mv 文件名 路径

22tar cvf myfiles.tar.gz files/ //打包，压缩文件
c表示建立归档
z表示归档进行压缩
f用于指定建立的归档文件名

tar xvf myfiles.tar.gz -C restore/ 解压缩文件
x表示回复归档


23.
EXEC CICS ADDRESS EIB(dfheiptr) RESP(respCode);
if (respCode != DFHRESP(NORMAL)) { //还是不会
fprintf(stderr, "Error occurred addressing commarea, rc = %d\n", respCode);

EXEC CICS RETURN;
}


strcpy(tranidname,dfheiptr->eibtrnid);
TASKID = sgetl((char*)dfheiptr->eibtaskn);


EXEC CICS ADDRESS COMMAREA(commArea) RESP(respCode);
if (respCode != DFHRESP(NORMAL)) {
fprintf(stderr, "Error occurred addressing commarea, rc = %d\n", respCode);
EXEC CICS RETURN;

24. EXEC SQL SELECT tltlcl INTO :aa :idt FROM pmtla
WHERE tlbrno=:tz6501I.brno AND tlsbno=:tz6501I.sbno
AND tltrtl=:tz6501I.trtl AND tlstcd=:STCDA;
if (SQLCODE){
if (SQLNOTFOUND==SQLCODE) {
error.ID=CLIENT_TRTL_ERR;
goto end;
}
error.ID=SQL_SEL_ERR;
sprintf(error.MSG,"%s%d","pmlba",SQLCODE);
}



25.加载数据：
1、 以默认分隔符加载,默认为“,”号
db2 "import from btpoper.txt of del insert into btpoper"
2、 以指定分隔符“|”加载
db2 "import from btpoper.txt of del modified by coldel| insert into btpoper"



delete from pmpaa where patacd='71';

insert into pmpaa (patacd,pache2,pastcd) values('71','养老','A');



卸载一个表中全部数据
db2 "export to btpoper.txt of del select * from btpoper"
db2 "export to btpoper.txt of del modified by coldel| select * from btpoper"

26.EXEC SQL FETCH dgpar_upcur INTO :tpzixh :idt,:tpzsqt :idt,:tphdqt :idt;
if ( SQLOK != SQLCODE && SQLCODE != 100 ) {
DISPLAY_INT("\n 取子帐号最大序号出错, code=",SQLCODE);
error.ID = SQL_OPN_ERR;
sprintf(error.MSG,"%s.code=%d","取子帐号最大序号出错",SQLCODE);
EXEC SQL CLOSE dgpar_upcur;
goto end;
}

27.tphdqt1 = tphdqt+1;
EXEC SQL UPDATE dgpar
SET pazixh=:tpzixh,
pazsqt=:tpzsqt1,
pahdqt=:tphdqt1
WHERE CURRENT OF dgpar_upcur;

28. EXEC SQL SELECT count(*) INTO :tpcunt :idt
FROM dgmsa
WHERE mszhmc = :dg0900I.zhmc;
if ( SQLCODE ) {
error.ID = ERROR_ERR;
strcpy(error.MSG,"校验子帐户名称是否重复时出错!");
goto end;
}

mstrtm=:tptrtm,
msmttl=:dg0902I.trtl
WHERE CURRENT OF dgmsa_upcur;

31. EXEC SQL DECLARE dgpar_upcur CURSOR FOR
SELECT pahdqt
FROM dgpar
WHERE pahzzh = :tphzzh
AND pastcd = :STCDA
FOR UPDATE;


32.EXEC SQL SELECT * INTO :stZfpzb :idts//只能

在程序中写，因为有变量。
FROM zfpzb
WHERE pzcfkd=:dg0903I.cfkd
AND pzgzn1=:dg0903I.gzn1
AND pzcflx=:dg0903I.cflx
AND pziqno=:dg0903I.iqno
AND pzacno=:dg0903I.acno
AND pzstcd='A';

33.EXEC SQL SELECT * INTO :stDdmsa :idt
FROM ddmsa
WHERE msacno =:tpacno
AND msstcd in ('A','F','P')
WITH UR;

EXEC SQL SELECT lbbrno,lbsbno,lbsbnm,lbrlnm,lbtelp,lbzpcd,lbaddr,lbexno,lbmdno
INTO :prbrno :idt,:prsbno :idt,:prsbnm :idt,:prrlnm :idt,:prtelp :idt,:przpcd :idt,:praddr :idt,:tpexno :idt,:tpmdno :idt
FROM pmlba
WHERE lbbrno = :dg0904I.brno
AND lbsbno = :dg0904I.sbno;


34.EXEC SQL SELECT MAX(ddshxh) INTO :tpshxh :idt
FROM dzdjy
WHERE ddozfs = :stDzdjy.ddozfs
AND ddzzfs = :stDzdjy.ddzzfs
AND ddzdl1 = :stDzdjy.ddzdl1
AND ddzddt = :stDzdjy.ddzddt;

35.sprintf(tmpSql,,dg0934I.qrbr,dg0934I.qrsb,tpozfs);

SELECT * FROM dzdjy WHERE ddbrno LIKE '%s' AND ddsbno//匹配select * from tzinf where sbno like '%1'"尾号为1的sbno
LIKE '%s' AND ddozfs LIKE '%s'

sprintf(tmpSql,"SELECT * FROM dzdjy WHERE ddbrno LIKE '%s' AND ddsbno LIKE '%s' AND ddozfs LIKE '%s'","aaa","bbb","ccc")

SELECT * FROM dzdjy WHERE ddbrno LIKE '%s'

SELECT * FROM dzdjy WHERE ddbrno LIKE 'aaa' AND ddsbno LIKE 'bbb' AND ddsbno LIKE 'ccc'

36.
EXEC SQL PREPARE infsql FROM :tmpSql;
EXEC SQL DECLARE dzdjy_cur CURSOR FOR infsql;

37.EXEC SQL DECLARE ddmsa_cur CURSOR FOR
SELECT msbrno,mssbno,msacno,mslabl,msacbl,msname,msitcd,msblde,msfzam,msstcd,msabfg,msrlac,mscuno,msdzcn
FROM ddmsa
WHERE msacno LIKE :tmpacno
/*AND (msstcd IN ('A','F','P') OR
(msladt > :utimo_end AND msstcd='C' AND msbrno||mssbno<>'05124'))*//*本月有发生额销户打对帐单*/
/*AND msabfg<>'2'*/
/*AND msbrno='02' AND mssbno = '116' */
AND msstcd IN ('A','F','P')
ORDER BY msbrno,mssbno,msacno;


38. EXEC SQL SELECT * FROM aacta
WHERE cttrdt = :tptrdt
AND ctacno LIKE :tpjhxd
AND ctstcd = :STCDA;


39.
EXEC SQL INSERT INTO dgdsa
( dszizh, dstrcd, dstrdt, dstrtm,
dscdfg, dstram, dsacbl, dsamal,
dssrno, dstrtl, dstrno, dsctfg,
dspano, dscfk1, dsiqno, dsstcd,
dsgzn1, dscflx, dshzsq )
VALUES
( :stDgdsa.dszizh, :stDgdsa.dstrcd, :stDgdsa.dstrdt, :stDgdsa.dstrtm,
:stDgdsa.dscdfg, :stDgdsa.dstram, :stDgdsa.dsacbl, :stDgdsa.dsamal,
:stDgdsa.dssrno, :stDgdsa.dstrtl, :stDgdsa.dstrno, :stDgdsa.dsctfg,
:stDgdsa.dspano, :stDgdsa.dscfk1, :stDgdsa.dsiqno, :stDgdsa.dsstcd,
:stDgdsa.dsgzn1, :stDgdsa.dscflx, :stDgdsa.dshzsq );



40.strcpy(DynQueryStr,"SELECT * "); //在oc8520中有源代码
strcat(DynQueryStr," FROM dzdcs");
strcat(DynQueryStr," WHERE ddcbbs like \'");
strcat(DynQuer

yStr,oc8520I.cbbs);催收标识
strcat(DynQueryStr,"\' ");
strcat(DynQueryStr," AND ddbrno like \'");行号
strcat(DynQueryStr,oc8520I.qrbr);
strcat(DynQueryStr,"\' ");


41. EXEC SQL ROLLBACK WORK;
EXEC SQL CONNECT RESET;

42
EXEC SQL FETCH pmlba_cur INTO :prbrno :idt,:prsbno :idt,:prsbnm :idt,:prrlnm :idt,:prtelp :idt,:przpcd :idt,:praddr
:idt;
+322 if(SQLOK!=SQLCODE){
+323 if(SQLNOTFOUND==SQLCODE) break;
+324 DISPLAY_INT("\nfetch pmlba_cur err,CODE=",SQLCODE);
+325 goto err;
+326 }

43 AND (mslabl<>0.00 OR msamal<>0.00 OR msmdic<>0.00 OR msmdal<>0.00)
+483 SELECT msacno,msname,mslabl,msopdt,mscldt,msdzcn,msabfg,msrlac,mscuno
+484 FROM ddmsa
+485 WHERE msbrno=:prbrno
+486 AND mssbno=:prsbno
+487 AND msabfg<>'2'
+488 AND ((msitcd not in ('812','813','249')) OR (msitcd in ('812','813','249') AND msckfg='1'))
+489 AND msitcd not in ('268','252','253','254','255','256','278','281')
+490 AND substr(msthfg,4,1)!= '1'
+491 AND (mslabl<>0.00 OR msamal<>0.00 OR msmdic<>0.00 OR msmdal<>0.00)
+492 AND msstcd in ('A','F','P')
+493 AND substr(msdzcn,2,1)>=:tpdzpl
+494 ORDER BY msacno FOR READ ONLY;
+495 EXEC SQL OPEN ddmsa_cur;
44

/* 修改cvlxa记录*/
EXEC SQL UPDATE cvlxa

SET lxamot=:cv0232I.amot,
lxysdt=:cv0232I.ysdt,
lxspjg=:cv0232I.sljg
WHERE lxsybh=:cv0232I.sybh
AND lxljlx=:cv0232I.ljlx
AND lxcyno=:cv0232I.cyno
AND lxpmlx=:cv0232I.pmlx
AND lxstcd=:STCDA;



45 EXEC SQL DECLARE ddmsa_cur CURSOR FOR
SELECT msacno,msname,mslabl,msopdt,mscldt,msdzcn,msabfg,msrlac,mscuno
FROM ddmsa
WHERE msbrno=:prbrno
AND mssbno=:prsbno
AND msabfg<>'2'
AND ((msitcd not in ('812','813','249')) OR (msitcd in ('812','813','249') AND msckfg='1'))
AND msitcd not in ('268','252','253','254','255','256','278','281')
AND substr(msthfg,4,1)!= '1'
AND (mslabl<>0.00 OR msamal<>0.00 OR msmdic<>0.00 OR msmdal<>0.00)
AND msstcd in ('A','F','P')
AND substr(msdzcn,2,1)>=:tpdzpl
ORDER BY msacno FOR READ ONLY;
EXEC SQL OPEN ddmsa_cur;
if(SQLOK != SQLCODE){
DISPLAY_INT("\nopen ddmsa_cur err,CODE=",SQLCODE);
goto err;


46

EXEC SQL SELECT * INTO :stDzdjg :idts
FROM dzdjg
WHERE djbrsb = :stDzdif.dibrno || :stDzdif.disbno
AND djstcd = 'A'; 字符串连接

47
EXEC SQL DECLARE dddsa_cur1 CURSOR FOR
SELECT dscdfg,dstram,dsacbl
FROM dddsa
WHERE dsacno = :tprlac
AND dstrdt >= :dg0941I.stdt
AND dsstcd = :STCDA
ORDER BY dsserl;
/*------------ 打开游标 ---------*/
EXEC SQL OPEN dddsa_cur1;
if (SQLOK!=SQLCODE) {
DISPLAY_STR("\n Error:Can not open dddsa_cur1");
error.ID = SQL_OPN_ERR;
sprintf(error.MSG,"%s,code=%d", "dddsa_cur1",SQLCODE);
EXEC SQL CLOSE dddsa_cur1;
goto end;
}

deccvint(0,&abacbl);
deccvint(0,&ttacbl);
deccvint(0,&tttram);
strcpy(ttcdfg," ");

EXEC SQL FETCH dddsa_cur1 INTO :ttcdfg :idt,:tttram :idt,:ttacbl :idt;
A：grouping sets：用来在单个sql中形成多级分组。
例：select company_id,node_id,count(customer_id) from customer group by grouping sets(company_id,node_id)

B：rollup：可以在单个数据库操作中形成多个分组。
例：select company_id,node_id,count(customer_id) from customer group by rollup(company_id,node_id)
注：rollup操作不是可交换的操作，指定用户组的顺序是很重要的。

C：cube： 生成分组表中分组的所有组合。
例：select company_id,node_id,count(customer_id) from customer grou

p by cube(company_id,node_id)

D：over：移动函数可以帮助实现移动的数据分析
Select date,avg(qty) over(order by date rows between 1 preceding and 1 following) as values from sale




A：grouping sets：用来在单个sql中形成多级分组。
例：select company_id,node_id,count(customer_id) from customer group by grouping sets(company_id,node_id)

B：rollup：可以在单个数据库操作中形成多个分组。
例：select company_id,node_id,count(customer_id) from customer group by rollup(company_id,node_id)
注：rollup操作不是可交换的操作，指定用户组的顺序是很重要的。
会针对前面这个变量来作各种情况的group by ,后一个变量则只是其中一种情况


C：cube： 生成分组表中分组的所有组合。
例：select company_id,node_id,count(customer_id) from customer group by cube(company_id,node_id)
－－这个再计算cube的时候很方便，直接能给出所有组合情况的group by
并且结合coalesce(）就能达到想要的cube中间数据，^_^
这个要常用，节省重复工作


D：over：移动函数可以帮助实现移动的数据分析
Select date,avg(qty) over(order by date rows between 1 preceding and 1 following) as values from sale



47. EXEC SQL UPDATE dzdcs SET ddcldt=:tptrdt,ddcltl=:dz0129I.trtl,ddktdt=:tptrdt,ddstcd='C' WHERE CURRENT OF dzdcs_scrcur;
if(SQLOK != SQLCODE) {
DISPLAY_INT("update dzdcs error!CODE =",SQLCODE);
error.ID=SQL_UPD_ERR;
sprintf(error.MSG,"%s,CODE=%d","dzdcs",SQLCODE);
goto end;
}

48. /*取挂牌利率*/
EXEC SQL DECLARE pmrta_cur CURSOR FOR
SELECT rtefdt, rtinrt, rtymfg
FROM pmrta
WHERE rtcyno = '01' AND rtirtp = '11' AND rtperd = 'D00' AND rtirkd = '0'
ORDER BY rtefdt DESC;