1 / 179

大型主机平台系统应用开发基础

大型主机平台系统应用开发基础. 教育部－ IBM 精品课程. 单位：大连理工大学 作者：陆坤，李凤岐，姜厚云. 第九章 嵌入式数据库编程基础. 9.1 大型机数据库对象介绍 9.2 嵌入式 SQL 编程基础 9.3 程序准备 9.4 游标的使用 9.5 动态 SQL 基础 9.6 数据库的权限和锁的机制. 第一节大型主机数据库对象介绍. IBM 数据库历史 主机数据库中的对象 ; 创建存储组 , 数据库 , 表空间 , 表 , 试图 , 同义词 , 别名索引等对象 ; 数据在数据库中的存储方式； How to use SPUFI.

ailani
Download Presentation

大型主机平台系统应用开发基础

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 大型主机平台系统应用开发基础 教育部－IBM精品课程 单位：大连理工大学 作者：陆坤，李凤岐，姜厚云

  2. 第九章 嵌入式数据库编程基础 • 9.1 大型机数据库对象介绍 • 9.2 嵌入式SQL编程基础 • 9.3 程序准备 • 9.4 游标的使用 • 9.5 动态SQL基础 • 9.6 数据库的权限和锁的机制

  3. 第一节大型主机数据库对象介绍 • IBM数据库历史 • 主机数据库中的对象; • 创建存储组,数据库,表空间,表,试图,同义词,别名索引等对象; • 数据在数据库中的存储方式； • How to use SPUFI

  4. IBM DB2发展史 • 四十年的理论研究应用产品，IBM公司在数据库管理系统的研究和发展中作出了巨大的贡献。 • 70年代之前，层次型数据库占主导地位(IBM IMS); • 1970年,IBM研究中心E.F. Codd博士提出了关系数据库模型,紧接着IBM研究中心发明了第一个关系数据库管理系统SYSTEM R 和SQL语言. • 80年代，基于SQL的关系数据库逐渐成为驻留，IBM的关系数据库DB2住在了大型机上的数据库应用。 • 1992年IBM将DB2推向开放平台； • 1997年IBM发布DB2 UDBV5，市场急剧扩大； • 1999年IBM发布DB2V6，占据市场领导地位； • 2000年IBM发布DB2V7.1—电子商务数据库； • 2001年IBM发布DB2V7.2—电子商务数据库增强版； • 2002年IBM发布DB2V8.1—智能数据库； • 2007年IBM发布DB2V9 — 第一个混合型数据库；

  5. IBM引导信息管理走向新的高度

  6. IBM在关系数据库技术领域的领导地位

  7. DB2的先进性和广泛性 • 近40年关系数据库经验 • 超过260项数据库专利技术 • Fortune 100, 100%使用DB2 • Fortune 500,80%使用DB2 • 超过1,000,000张使用许可证 • 超过60,000,000最终用户 • 超过450,000公司使用DB2 • 全世界70％的企业数据存储在DB2中

  8. DB2:增长最快的数据库

  9. DB2-平台延伸最好的数据库(1/3)

  10. DB2-平台延伸最好的数据库(2/3)

  11. DB2-平台延伸最好的数据库(3/3)

  12. DB2 Objects DB2 Objects

  13. Sequence of defining objects

  14. Interfaces to define object SQL Interfaces - Command line or file input interfaces - SPUFI – SQL Processor Using File Input.(重点） Tool on z/OS for entering SQL via file input - QMF – Query Management Facility. z/OS or workstation tool to allow interactive SQL entry and processing. - CLP – Command Line Processor. It is used to dynamically execute SQL requests and/or DB2 commands.

  15. DB2 Naming Rules (1/2)

  16. DB2 Naming Rules(2/2) • DB and STG name must be unique in DB2 System • All object names begin with A-Z,#,$; following chars: A-Z,#,$,0-9; • Name length: DB,TBS <= 8 others: <=128

  17. DB2 Storage Group

  18. Volumes • Are used in order • Not dedicated to STG • Non-DB2 DS can be allocated on it • One Can be assigned to different STG • MAX 133 volumes in a STG • All volumes in a STG must the same type

  19. DB2 Database

  20. DB2 Database • CREATE DATABASE DB1 STOGROUP SG1default STG BUFFERPOOL BP1 dflt table bp INDEXBP BP2 dflt index bp CCSID UNICODE; dflt code • DSNDB04 is the default DB and is created when DB2 is installed.

  21. DB2 Table Space • What is a Table Space? • DB2 storage Structure • Contains data rows for one or more tables • Resides in a page set of one or more VSAM LDS • Created in a database using SQL • Three types of Table space: • Simple Table Space • Segmented Table Space • Partitioned Table Space

  22. Simple Table Space • Seldom used!

  23. Segmented Table Space

  24. SMPL and SEGMT Table Space

  25. Partitioned(Table-Controlled) Table Space

  26. Table-Controlled Partitioning Example(1/2)

  27. Table-Controlled Partitioning Example(2/2)

  28. Create Table-column attribute TABLESPACE DATABASE

  29. Default Attribute(1/2)

  30. Default Attribute(2/2) • DATA TYPE SYSTE DEFAULT ------------------------------------------- CHARACTER SPACES NUMERIC 0s VARCHAR ZERO LENGHTH DATE CURRENT DATA TIME CURRENT TIME TIMESTAMP CURRENT TIMESTAMP

  31. CREATE TABLE- LIKE • CHECK CONSTRAINS ARE NOT COPYED • PR ,UNIQ,FK ARE NOT COPYED

  32. SYSTABLES Catalog Information

  33. Table Check Constraint Consideration

  34. -DISPLAY DATABASE Command • -DIS DB (DSNDB06) ,CHKP

  35. DB2 SYNONYM • Refer to a local table or view • Refer to another owner’s tab or view as if it were yours. • It can be thought of as a ‘private’ pointer to a table or view. • A synonym can only be referenced by its owner

  36. DB2 SYNONYM - Example

  37. DB2 ALIAS • It is a pointer to a table or view • It likes a virtual table • It can be referenced by using a qualified name. • The table or view could be located on a remote site.

  38. DB2 ALIAS - Example

  39. Naming Conversation for DS

  40. How to use SPUFI （SQL Processing Using File Input) • Environment • Mainframe IP Address:如：218.25.163.36 • DB2 subsystem:DB8G • TSO USERID • Each student has his own TSO Logon USERID. Use the USERID you are assigned to logon. • User datasets used in the practice • ST***.SPFUI.INPUT(SQL1) • ST***.SPFUI.OUTPUT Note: (1) These datasets are pre-created for each USER. (2) Later we will see that these two datasets are used as SPUFI input and SPUFI output. And each user should use his own datasets. So when you take your practice,change *** to your USERID suffix.

  41. How to use SPFUI • Practice Steps (1)Connect to z/OS (2) Invoke DB2 panel (3) Set SSID parameter that tell SPUFI which DB2 subsystem you’ll operate on. (4) Use SPUFI to manage DB2 data • Set SPUFI Parameters • Define SQL input file to SPUFI • Define SQL output file to SPUFI • Enter edit panel and edit your queries ( you can have DDL, DML,DCL here) • Run test queries through SPFUI and view results (5) Use SPUFI to run your own queries (6) Summary

  42. How to use SPFUI • Invoke DB2 panel • After you have enter ISPF. Type m.11.1 and type “enter key”, you will go to DB2 panel.

  43. How to use SPFUI • Set target DB2 subsystem to SPUFI • Picture shows the DB2 panel, choose “D”, the panel to set db2 subsystem will appear. Current set DB2 subsystem that SPUFI will operate on Form here, you can go to the panel which set DB2 subsystem that SPUFI will operate on

  44. How to use SPFUI • Set target DB2 subsystem to SPUFI (Continue) • The picture below show how you set target DB2 subsystem. In our testing environment it’s DB8G. Type DB8G and enter. Type DB8G here to set the target DB2 subsystem as DB8G

  45. How to use SPUFI • Invoke SPUFI Enter 1 to invoke SPUFI Target DB2 subsystem: DB8G

  46. How to use SPUFI • Input/output datasets • SPUFI uses datasets to hold it’s input/output. Before use, you must first created these two datasets. In practice, please use “ST***.SPUFI.INPUT(SQL1)” and “ST***.SPUFI.OUT” which have been created Input Dataset containing the SQL Where the destination output will go

  47. How to use SPUFI • Edit SQL • You can edit the query from edit panel just like editing a normal datasets. And you can also save all your changes • Run SQL • Edit panel  F3Ctrl

  48. How to use SPUFI • View results • After you run SPUFI, result page will appear as below picture • Enter “F3” to go back to edit panel

  49. How to use SPUFI • Run your own queries • Run your own DDL • Sample: Create table test(c1 int, c2 int); • Run your own DML • Sample: insert into test values(1, 1); • Sample: select * from test; • Run your own DCL • Grant select on test to public; • Summary

  50. 第二节 嵌入式编程基础 • Using Delimiters • Use Host variables • Use appropriate techniques to process null data • SQLCA

More Related