MVS DB 的一些基本操作
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- asm的一些基本操作ASM
- Determining whether an object requires reorganization MVS DB2ObjectUIDB2
- Java IO 一些基本操作Java
- MySQL對錶和庫的一些基本操作MySql
- 並查集的一些基本概念以及基本操作(初始化,合併,查詢等操作)並查集
- Go 操作 Redis 的基本操作GoRedis
- 非常詳細地Hive的基本操作和一些注意事項Hive
- 對資料庫中列的一些基本的操作的SQL命令（轉）資料庫SQL
- Docker的基本操作Docker
- MySQL的基本操作MySql
- git的基本操作Git
- 模組的基本操作
- 棧的基本操作
- webdriver的基本操作Web
- hash的基本操作
- db2資料庫的操作以及一些常見問題解決DB2資料庫
- Golang 如何操作DB2的？GolangDB2
- DB2一些SQL的用法DB2SQL
- 一些db2的資料DB2
- 活動（Activity）的基本操作
- JS — 物件的基本操作JS物件
- react的基本操作(1)React
- Hive表的基本操作Hive
- Vim命令的基本操作
- Numpy的基本操作（五）
- Hbase shell的基本操作
- git的基本操作（一）Git
- Docker映象的基本操作Docker
- 佇列的基本操作佇列
- ThinkPHP 的CURD 基本操作PHP
- DriveInfo類的基本操作
- FileInfo類的基本操作
- File類的基本操作
- Dataload的基本操作
- 陣列的基本操作陣列
- DB2 用到的基本SQL語句DB2SQL
- webpack 基本操作Web
- Git基本操作Git