1. 主机DB2 的 catalog
MVS DB2 Catalog
db2 catalog tcpip node MVS1M remote STFMVS1M.POK.IBM.COM server 5000 SYSTEM DB2MVS OSTYPE OS390
db2 catalog DCS db DD1H as USIBMVRDD1H
db2 catalog db DD1H as DD1H at node MVS1M AUTHENTICATION DCS
之后给就可以像开放平台那样操作数据库了。
2. 第三方工具连接MVS DB
-dis ddf
DSNL080I -DBEP DSNLTDDF DISPLAY DDF REPORT FOLLOWS:
DSNL081I STATUS=STARTD
DSNL082I LOCATION LUNAME GENERICLU
DSNL083I BBIRDBEU AUIBMQA1.QLFDB21 -NONE
DSNL084I TCPPORT=446 SECPORT=0 RESPORT=5020 IPNAME=-NONE
DSNL085I IPADDR=::19.10.30.17
DSNL086I SQL DOMAIN=bbir.abc.hppp.com
DSNL105I CURRENT DDF OPTIONS ARE:
DSNL106I PKGREL = COMMIT
DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE
***
连接的URL 就是这样
jdbc:db2://19.10.30.17:5020/BBIRDBEU
jdbc:db2://IP:RESPORT/LOCATION
3. 在主机上建DB 和 Table 的过程
3.1 为DB 建存储空间:
SET CURRENT SQLID='DB2ADM'; -- 由于执行JCL 的是其他的账户,这里需要切换一下
CREATE STOGROUP ABCG001
VOLUMES("*")
VCAT DBEE ;
COMMIT;
3.2 建DB
CREATE DATABASE TONYTEST STOGROUP ABCG001 BUFFERPOOL BP0;
3.3 建表空间
CREATE TABLESPACE TS## IN TONYTEST
USING STOGROUP ABCG001 PRIQTY 20 SECQTY 20 ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0 CLOSE NO COMPRESS YES;
COMMIT;
3.4 CREATE TABLE TONY.TBL01
(ACCTNO CHAR(8) NOT NULL,
COMMENTS CHAR(50) ,
PRIMARY KEY(ACCTNO))
IN TONYTEST.ABCG001;
4. 在MVS DB2 上建一个包含LOB 和CLOB 的表
--TABLE SPACE DEFINITION FOR EMP_PHOTO_RESUME
CREATE TABLESPACE DSN8S91B
IN DSN8D91L
USING STOGROUP DSN8G910
PRIQTY 20
SECQTY 20
ERASE NO
LOCKSIZE PAGE
LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC;
--TABLE DEFINITION FOR EMP_PHOTO_RESUME
CREATE TABLE DSN8910.EMP_PHOTO_RESUME
(EMPNO CHAR( 06 ) NOT NULL,
EMP_ROWID ROWID NOT NULL GENERATED ALWAYS,
PSEG_PHOTO BLOB( 500K ),
BMP_PHOTO BLOB( 100K ),
RESUME CLOB( 5K ),
IN DSN8D91L.DSN8S91B
CCSID EBCDIC;
--LOB TABLE SPACES, AUX TABLES, AND INDEXES FOR EMP_PHOTO_RESUME
CREATE LOB TABLESPACE DSN8S91L
IN DSN8D91L
LOG NO;
CREATE AUX TABLE DSN8910.AUX_PSEG_PHOTO
IN DSN8D91L.DSN8S91L
STORES DSN8910.EMP_PHOTO_RESUME
COLUMN PSEG_PHOTO;
CREATE UNIQUE INDEX DSN8910.XAUX_PSEG_PHOTO
ON DSN8910.AUX_PSEG_PHOTO;
CREATE LOB TABLESPACE DSN8S91M
IN DSN8D91L
LOG NO;
CREATE AUX TABLE DSN8910.AUX_BMP_PHOTO
IN DSN8D91L.DSN8S91M
STORES DSN8910.EMP_PHOTO_RESUME
COLUMN BMP_PHOTO;
CREATE UNIQUE INDEX DSN8910.XAUX_BMP_PHOTO
ON DSN8910.AUX_BMP_PHOTO;
CREATE LOB TABLESPACE DSN8S91N
IN DSN8D91L
LOG NO;
CREATE AUX TABLE DSN8910.AUX_EMP_RESUME
IN DSN8D91L.DSN8S91N
STORES DSN8910.EMP_PHOTO_RESUME
COLUMN RESUME;
CREATE UNIQUE INDEX DSN8910.XAUX
body { margin: 0 0 0 0; padding:0 0 0 0 } td,div { font-family:Segoe UI;font-size:9pt;vertical-align:top } /* Copyright IBM Corp. 2012 All Rights Reserved. */ body { margin: 0 0 0 0; padding:0 0 0 0; overflow:hidden; } .transcript { background-color:#d2d2d2; } .messageBlock { padding-left:10px; padding-right:10px; margin-bottom:3px } .message { padding-left:20px; margin-left:95px; word-wrap:break-word; white-space:-moz-pre-wrap; _white-space:pre; white-space:pre-wrap;} .messageCont { padding-left:20px; margin-left:95px; word-wrap:break-word; white-space:-moz-pre-wrap; _white-space:pre;white-space:pre-wrap;} .other { font-size:11px;color:#39577a;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .myself { font-size:11px;color:#da8103;font-style:normal;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont { font-size:8px;text-align:right; color:#39577a;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .myselfCont { font-size:8px;text-align:right; color:#da8103;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .system { font-size:11px; word-wrap:break-word;color:#da8103;font-style:normal;font-weight:normal; white-space:-moz-pre-wrap; _white-space:pre;white-space:pre-wrap; } .showTimestamp { padding-left:20px; font-size:11px; float:right; color:#999999;font-style:normal;font-weight:normal; } .other1 { font-size:11px; color:#ac2000;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont1 { font-size:8px;text-align:right; color:#ac2000;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .other2 { font-size:11px; color:#3c9fa8;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont2 { font-size:8px;text-align:right; color:#3c9fa8;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .other3 { font-size:11px; color:#e25614;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont3 { font-size:8px;text-align:right; color:#e25614;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .other4 { font-size:11px; color:#0b6ac8;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont4 { font-size:8px;text-align:right; color:#0b6ac8;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .other5 { font-size:11px; color:#b23290;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont5 { font-size:8px;text-align:right; color:#b23290;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .other6 { font-size:11px; color:#02e7c7;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont6 { font-size:8px;text-align:right; color:#02e7c7;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .other7 { font-size:11px; color:#5b3284;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont7 { font-size:8px;text-align:right; color:#5b3284;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .highlight { background-color:#bed6f8; } .datestamp { padding-right:0px; font-size:11px; cursor:default; margin-bottom:1px; background-color:#c0c0c0; width:100%; float:left; text-align:right; color:#ffffff; font-weight:bold; font-style:italic; } #chatAlert { float:left; border-bottom:1px solid #E8D091; padding:6px; width:100%; color:#A5754C; } #chatAlertImage { float:left; } #chatAlertText { float:left; margin-left:6px; } #chatAlertClose { float:right; margin-right:10px; padding-right:6px; margin-top:0px; } #chatAlertText a { color:#A5754C; } #chatAlertText a:hover { color:#A5754C; text-decoration:none; } .tsDisplay { display:block }.dsDisplay { display:block }
MVS DB2 Catalog
db2 catalog tcpip node MVS1M remote STFMVS1M.POK.IBM.COM server 5000 SYSTEM DB2MVS OSTYPE OS390
db2 catalog DCS db DD1H as USIBMVRDD1H
db2 catalog db DD1H as DD1H at node MVS1M AUTHENTICATION DCS
之后给就可以像开放平台那样操作数据库了。
2. 第三方工具连接MVS DB
-dis ddf
DSNL080I -DBEP DSNLTDDF DISPLAY DDF REPORT FOLLOWS:
DSNL081I STATUS=STARTD
DSNL082I LOCATION LUNAME GENERICLU
DSNL083I BBIRDBEU AUIBMQA1.QLFDB21 -NONE
DSNL084I TCPPORT=446 SECPORT=0 RESPORT=5020 IPNAME=-NONE
DSNL085I IPADDR=::19.10.30.17
DSNL086I SQL DOMAIN=bbir.abc.hppp.com
DSNL105I CURRENT DDF OPTIONS ARE:
DSNL106I PKGREL = COMMIT
DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE
***
连接的URL 就是这样
jdbc:db2://19.10.30.17:5020/BBIRDBEU
jdbc:db2://IP:RESPORT/LOCATION
3. 在主机上建DB 和 Table 的过程
3.1 为DB 建存储空间:
SET CURRENT SQLID='DB2ADM'; -- 由于执行JCL 的是其他的账户,这里需要切换一下
CREATE STOGROUP ABCG001
VOLUMES("*")
VCAT DBEE ;
COMMIT;
3.2 建DB
CREATE DATABASE TONYTEST STOGROUP ABCG001 BUFFERPOOL BP0;
3.3 建表空间
CREATE TABLESPACE TS## IN TONYTEST
USING STOGROUP ABCG001 PRIQTY 20 SECQTY 20 ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0 CLOSE NO COMPRESS YES;
COMMIT;
3.4 CREATE TABLE TONY.TBL01
(ACCTNO CHAR(8) NOT NULL,
COMMENTS CHAR(50) ,
PRIMARY KEY(ACCTNO))
IN TONYTEST.ABCG001;
4. 在MVS DB2 上建一个包含LOB 和CLOB 的表
--TABLE SPACE DEFINITION FOR EMP_PHOTO_RESUME
CREATE TABLESPACE DSN8S91B
IN DSN8D91L
USING STOGROUP DSN8G910
PRIQTY 20
SECQTY 20
ERASE NO
LOCKSIZE PAGE
LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC;
--TABLE DEFINITION FOR EMP_PHOTO_RESUME
CREATE TABLE DSN8910.EMP_PHOTO_RESUME
(EMPNO CHAR( 06 ) NOT NULL,
EMP_ROWID ROWID NOT NULL GENERATED ALWAYS,
PSEG_PHOTO BLOB( 500K ),
BMP_PHOTO BLOB( 100K ),
RESUME CLOB( 5K ),
IN DSN8D91L.DSN8S91B
CCSID EBCDIC;
--LOB TABLE SPACES, AUX TABLES, AND INDEXES FOR EMP_PHOTO_RESUME
CREATE LOB TABLESPACE DSN8S91L
IN DSN8D91L
LOG NO;
CREATE AUX TABLE DSN8910.AUX_PSEG_PHOTO
IN DSN8D91L.DSN8S91L
STORES DSN8910.EMP_PHOTO_RESUME
COLUMN PSEG_PHOTO;
CREATE UNIQUE INDEX DSN8910.XAUX_PSEG_PHOTO
ON DSN8910.AUX_PSEG_PHOTO;
CREATE LOB TABLESPACE DSN8S91M
IN DSN8D91L
LOG NO;
CREATE AUX TABLE DSN8910.AUX_BMP_PHOTO
IN DSN8D91L.DSN8S91M
STORES DSN8910.EMP_PHOTO_RESUME
COLUMN BMP_PHOTO;
CREATE UNIQUE INDEX DSN8910.XAUX_BMP_PHOTO
ON DSN8910.AUX_BMP_PHOTO;
CREATE LOB TABLESPACE DSN8S91N
IN DSN8D91L
LOG NO;
CREATE AUX TABLE DSN8910.AUX_EMP_RESUME
IN DSN8D91L.DSN8S91N
STORES DSN8910.EMP_PHOTO_RESUME
COLUMN RESUME;
CREATE UNIQUE INDEX DSN8910.XAUX
body { margin: 0 0 0 0; padding:0 0 0 0 } td,div { font-family:Segoe UI;font-size:9pt;vertical-align:top } /* Copyright IBM Corp. 2012 All Rights Reserved. */ body { margin: 0 0 0 0; padding:0 0 0 0; overflow:hidden; } .transcript { background-color:#d2d2d2; } .messageBlock { padding-left:10px; padding-right:10px; margin-bottom:3px } .message { padding-left:20px; margin-left:95px; word-wrap:break-word; white-space:-moz-pre-wrap; _white-space:pre; white-space:pre-wrap;} .messageCont { padding-left:20px; margin-left:95px; word-wrap:break-word; white-space:-moz-pre-wrap; _white-space:pre;white-space:pre-wrap;} .other { font-size:11px;color:#39577a;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .myself { font-size:11px;color:#da8103;font-style:normal;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont { font-size:8px;text-align:right; color:#39577a;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .myselfCont { font-size:8px;text-align:right; color:#da8103;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .system { font-size:11px; word-wrap:break-word;color:#da8103;font-style:normal;font-weight:normal; white-space:-moz-pre-wrap; _white-space:pre;white-space:pre-wrap; } .showTimestamp { padding-left:20px; font-size:11px; float:right; color:#999999;font-style:normal;font-weight:normal; } .other1 { font-size:11px; color:#ac2000;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont1 { font-size:8px;text-align:right; color:#ac2000;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .other2 { font-size:11px; color:#3c9fa8;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont2 { font-size:8px;text-align:right; color:#3c9fa8;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .other3 { font-size:11px; color:#e25614;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont3 { font-size:8px;text-align:right; color:#e25614;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .other4 { font-size:11px; color:#0b6ac8;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont4 { font-size:8px;text-align:right; color:#0b6ac8;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .other5 { font-size:11px; color:#b23290;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont5 { font-size:8px;text-align:right; color:#b23290;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .other6 { font-size:11px; color:#02e7c7;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont6 { font-size:8px;text-align:right; color:#02e7c7;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .other7 { font-size:11px; color:#5b3284;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont7 { font-size:8px;text-align:right; color:#5b3284;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .highlight { background-color:#bed6f8; } .datestamp { padding-right:0px; font-size:11px; cursor:default; margin-bottom:1px; background-color:#c0c0c0; width:100%; float:left; text-align:right; color:#ffffff; font-weight:bold; font-style:italic; } #chatAlert { float:left; border-bottom:1px solid #E8D091; padding:6px; width:100%; color:#A5754C; } #chatAlertImage { float:left; } #chatAlertText { float:left; margin-left:6px; } #chatAlertClose { float:right; margin-right:10px; padding-right:6px; margin-top:0px; } #chatAlertText a { color:#A5754C; } #chatAlertText a:hover { color:#A5754C; text-decoration:none; } .tsDisplay { display:block }.dsDisplay { display:block }
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6126/viewspace-1214325/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/6126/viewspace-1214325/