DB2/400数据库编程

$1 Interfaces to UDB/400
1. Traditional system interface: 用CL命令创建数据库对象，DFU、SEU都可以修改数据
2. SQL：strsql、strqm、runsqlstm工具
使用runsqlstm工具执行一个sql脚本步骤：

3. iSeries Navigator
4. Query for iSeries: strqry或者wrkqry
如：查询表tbtest数据－runqry *N lyf1/tbtest

$2 Database Concept
1. Database files
A database file is one of several types of the system object type *FILE kept in the system.
2. Source file
A source file contains uncompiled programming code and input data needed to create some types of objects. A source file can contain source statements for such items as high-level language programs and data description specifications. A source file can be a source physical file, diskette file, tape file, or inline data file.
3. Physical file
A physical file is a database file that stores application data. Physical files contain one record format and one or more members. From the perspective of the SQL interface, physical files are identical to tables.
4. Logical file
A logical file is a database file that logically represents one or more physical files. From the perspective of the
SQL interface, logical files are identical to views and indexes.
5. Member
Members are different sets of data, each with the same format, within one database file. As a general rule, database files have only one member, the one created when the file is created. If a file contains more than one member, each member serves as a subset of the data in the file.
6. Record
A record is a group of related data within a file. From the perspective of the SQL interface, records are identical to rows.
命名规则：
数据库文件名、记录格式名、字段名长度只能小于等于10，如果使用CL命令创建数据库文件，长度超过10无法创建，如果使用SQL接口可以创建，但实际的数据库文件被重命名为长度为10的名字，在sql中使用两个名字都可以访问数据。

$3. 创建和定义数据库文件
共4个途径
1、IDDU
You can create a database file by using Interactive Data Definition Utility (IDDU)。
步骤：
－－创建一个Dictionary（就是在一个lib）
－－在Dictionary中创建field定义
－－根据field创建Record Format
－－根据RecordF创建数据库文件
2、OS/400 CL
首先用CRTPF、CRTLF、CRTSRCPF命令创建好数据库文件，然后用SEU或DFU描述文件中的数据规范（DDS）。
3、SQL
You can create and describe a database file (table) by using Structured Query Language (SQL) statements.
4、iSeries Navigator

$4. 创建源文件（Source File）
1、Why source files are used
如果在创建对象时，命令不能提供足够的信息，就需要创建源文件。
例如：创建一个CL程序，必须有source statement源文件，创建逻辑文件，必须有一个含DDS信息的源文件。
一般情况，如果依赖源文件创建一个对象，源文件中的成员名称和所要创建的对象名称相同。
创建下面对象，源文件是必须的:
* High-level language programs
* Control language programs
* Logical files
* Intersystem communications function (ICF) files
* Commands
创建下面对象，源文件可能被用到，但不是必须的:
* Physical files
* Display files
* Printer files
* Translate tables
2、Creating a source file using CRTSRCPF
CRTSRCPF FILE(QGPL/lyfsource) TEXT(’Source file’)
3、IBM-supplied source files
系统为每种类型对象创建提供了缺省的源文件，如CRTCLPGM命令使用QCLSRC文件名作为缺省值。

$4. 管理数据库文件
1、拷贝文件CPYF
1）将tbfrom表中的数据追加到tbto表格中
CPYF FROMFILE(LYF1/TBFROM) TOFILE(LYF2/TBTO) MBROPT(*ADD)
MBROPT提供三种模式：*ADD，*REPLACE，*UPDADD，并且提供from…to的记录拷贝功能
2）复制一份tbfrom表格并且含数据
CPYF FROMFILE(LYF1/TBFROM) TOFILE(LYF2/TBFROM2) CRTFILE(*YES)
2、移动文件MOVOBJ
MOVOBJ OBJ(LYF1/TBFROM) OBJTYPE(*FILE) TOLIB(LYF2)
3、删除文件
DLTF (LYF1/TBFROM)
4、查看文件属性
#查看数据库文件大小
wrkobj obj(LYF2/TBFROM2)
Storage information:
Size . . . . . . . . . . . . . . . : 53248
Offline size . . . . . . . . . . . : 0

#查看数据库文件的逻辑属性，如：约束、触发器
dspfd file(lyf1/tb2007) type(*cst/*trg/*all)
如果没有约束和触发器，就不会显示这些信息。

#查看数据库文件的物理属性，如：物理存储、字符集、权限、操作统计
wrkf file(lyf2/tbfrom2) #查看数据库文件的物理属性，如：物理存储、字符集、权限、操作统计
查看下面属性
DBCS capable . . . . . . . . . . . . . . . : No
Maximum members . . . . . . . . . . . . . . : MAXMBRS *NOMAX
Number of constraints . . . . . . . . . . . : 0
Number of triggers . . . . . . . . . . . . : 0
Number of members . . . . . . . . . . . . . : 2
Member size . . . . . . . . . . . . . . . . : SIZE *NOMAX
Allocate storage . . . . . . . . . . . . . : ALLOCATE *NO
Contiguous storage . . . . . . . . . . . . : CONTIG *NO
Preferred storage unit . . . . . . . . . . : UNIT *ANY
Records to force a write . . . . . . . . . : FRCRATIO *NONE
Maximum file wait time . . . . . . . . . . : WAITFILE *IMMED
Maximum record wait time . . . . . . . . . : WAITRCD 60
Max % deleted records allowed . . . . . . . : DLTPCT *NONE
Reuse deleted records . . . . . . . . . . . : REUSEDLT *YES
字符集：
Coded character set identifier . . . . . . : CCSID 1141
操作权限：
Allow read operation . . . . . . . . . . . : Yes
Allow write operation . . . . . . . . . . . : Yes
Allow update operation . . . . . . . . . . : ALWUPD *YES
Allow delete operation . . . . . . . . . . : ALWDLT *YES
RF检查选项：
Record format level check . . . . . . . . . : LVLCHK *YES
访问路径：
Access path . . . . . . . . . . . . . . . . : Arrival
Access path size . . . . . . . . . . . . . : ACCPTHSIZ *MAX1TB
Maximum record length . . . . . . . . . . . : 12
File is currently journaled . . . . . . . . : No
Access Path Description
Access path . . . . . . . . . . . . . . . . : Arrival
Sort Sequence . . . . . . . . . . . . . . . : SRTSEQ *HEX
Language identifier . . . . . . . . . . . . : LANGID ENG
Member Description
Member . . . . . . . . . . . . . . . . . . : MBR TBFROM
Member level identifier . . . . . . . . . : 1070724085102
Member creation date . . . . . . . . . . : 24.07.07
Text ‘description’ . . . . . . . . . . . : TEXT
Expiration date for member . . . . . . . : EXPDATE *NONE
Member size . . . . . . . . . . . . . . . : SIZE *NOMAX
Current number of records . . . . . . . . : 4
Number of deleted records . . . . . . . . : 1
Allocate storage . . . . . . . . . . . . : ALLOCATE *NO
Contiguous storage . . . . . . . . . . . : CONTIG *NO
Preferred storage unit . . . . . . . . . : UNIT *ANY
Records to force a write . . . . . . . . : FRCRATIO *NONE
Share open data path . . . . . . . . . . : SHARE *NO
Max % deleted records allowed . . . . . . : DLTPCT *NONE
数据操作统计信息：
Data Space Activity Statistics . . . . . :
Data space size in bytes . . . . . . . : 12288
Physical file open accesses . . . . . . : 8
Physical file close accesses . . . . . : 7
Write operations . . . . . . . . . . . : 5
Update operations . . . . . . . . . . . :
Delete operations . . . . . . . . . . . : 1
Logical Reads . . . . . . . . . . . . . : 21
Physical Reads . . . . . . . . . . . . : 2
Clear operations . . . . . . . . . . . :
Data space copy operations . . . . . . :
Reorganize operations . . . . . . . . . :
Access paths builds/rebuilds . . . . . :
Records rejected by key selection . . . :
Records rejected by non-key selection . : 4
Records rejected by group-by selection. :
Access Path Activity Statistics . . . . . :
Access path logical reads . . . . . . . :
Access path physical reads . . . . . . :
Implicit access path sharing . . . . . : No
Last change date/time . . . . . . . . . . : 24.07.07 10:16:28
Last save date/time . . . . . . . . . . . :
Last restore date/time . . . . . . . . . :
Last used date . . . . . . . . . . . . . : 24.07.07
Days used count . . . . . . . . . . . . . : 1
Reset date . . . . . . . . . . . . . . :
Member . . . . . . . . . . . . . . . . . . : MBR FEB
Member level identifier . . . . . . . . . : 1070724095738
Member creation date . . . . . . . . . . : 24.07.07
Text ‘description’ . . . . . . . . . . . : TEXT
Expiration date for member . . . . . . . : EXPDATE *NONE
Member size . . . . . . . . . . . . . . . : SIZE *NOMAX
Current number of records . . . . . . . . : 0
Number of deleted records . . . . . . . . : 0
Allocate storage . . . . . . . . . . . . : ALLOCATE *NO
Contiguous storage . . . . . . . . . . . : CONTIG *NO
Preferred storage unit . . . . . . . . . : UNIT *ANY
Records to force a write . . . . . . . . : FRCRATIO *NONE
Share open data path . . . . . . . . . . : SHARE *NO
Max % deleted records allowed . . . . . . : DLTPCT *NONE
Data Space Activity Statistics . . . . . :
Data space size in bytes . . . . . . . : 12288
Physical file open accesses . . . . . . : 1
Physical file close accesses . . . . . : 1
Write operations . . . . . . . . . . . :
Update operations . . . . . . . . . . . :
Delete operations . . . . . . . . . . . :
Logical Reads . . . . . . . . . . . . . :
Physical Reads . . . . . . . . . . . . :
Clear operations . . . . . . . . . . . :
Data space copy operations . . . . . . :
Reorganize operations . . . . . . . . . :
Access paths builds/rebuilds . . . . . :
Records rejected by key selection . . . :
Records rejected by non-key selection . :
Records rejected by group-by selection. :
Access Path Activity Statistics . . . . . :
Access path logical reads . . . . . . . :
Access path physical reads . . . . . . :
Implicit access path sharing . . . . . : No
Last change date/time . . . . . . . . . . : 24.07.07 09:57:38
Last save date/time . . . . . . . . . . . :
Last restore date/time . . . . . . . . . :
Last used date . . . . . . . . . . . . . : 24.07.07
Days used count . . . . . . . . . . . . . : 1
Reset date . . . . . . . . . . . . . . :
Record Format List
Record Format Level
Format Fields Length Identifier
TBFROM 1 12 2DA374E705DE7
Text . . . . . . . . . . . . . . . . . . . :
Total number of formats . . . . . . . . . . : 1
Total number of fields . . . . . . . . . . . : 1
Total record length . . . . . . . . . . . . : 12
Member List
Source Creation Last Change Deleted
Member Size Type Date Date Time Records Records
FEB 12288 24.07.07 24.07.07 09:57:38 0 0
Text:
TBFROM 12288 24.07.07 24.07.07 10:16:28 4 1
Text:
Total number of members . . . . . . . . . : 2
Total number of members not available . . : 0
Total records . . . . . . . . . . . . . . : 4
Total deleted records . . . . . . . . . . : 1
Total of member sizes . . . . . . . . . . : 24576

$5. 管理数据库成员
1、增加成员
使用CRTPF、CRTLF缺省增加一个与文件名相同的成员，当然指定MBR参数为*NONE时就不产生成员，CRTSRCPF命令缺省不创建成员；
如果PF或LF已创建好，使用ADDPFM、ADDLFM添加成员：
CHGPF FILE(LYF2/TBFROM2) MAXMBRS(*NOMAX) ＃修改成员数量不受限制
ADDPFM FILE(LYF2/TBFROM2) MBR(JAN)
2、修改成员属性
使用CHGPFM、CHGLFM命令修改，SRCTYPE, EXPDATE (the member’s expiration date), SHARE (whether the member can be shared within a job), and TEXT (the text description of the member)属性。
成员的最大大小需要用CHGPF、CHGLF修改
3、修改成员名称
RNMM FILE(LYF2/TBFROM2) MBR(JAN) NEWMBR(FEB)
4、删除成员
rmvm FILE(LYF2/TBFROM2) MBR(FEB)
5、清除成员中的数据
clrPFM FILE(LYF2/TBFROM2) MBR(FEB)
6、使用RGZPFM命令重组物理文件（也可以在sql中使用reorg table命令）
如果文件到达序列组织数据，通过使用关键词序列重组物理文件，使数据物理位置按照key序列存放；
重整物理文件，可以压缩被删除的记录，它的相对记录号会重新编制
RGZPFM file(lyf2/tbfrom2)
7、显示成员中的记录
DSPPFM FILE(LYF2/TBFROM2) MBR(FEB)

$6. 查看文件属性
1、显示数据库文件特性（表格属性）
dspfd file(lyf1/tbfrom)
wrkf file(lyf1/tbfrom)
2、显示字段描述（表格字段定义）
dspffd file(lyf1/tbfrom)
3、显示文件的依赖关系
dspdbr file(lyf1/tbfrom)
4、显示系统交叉引用文件
The system manages eight database files that contain:
* Basic database file attribute information (QSYS/QADBXREF)
* Cross-reference information (QSYS/QADBFDEP) about all the database files on the system (except those database files that are in the QTEMP library)
* Database file field information (QSYS/QADBIFLD)
* Database file key field information (QSYS/QADBKFLD)
* Referential constraint file information (QSYS/QADBFCST)
* Referential constraint field information (QSYS/QADBCCST)
* SQL package information (QSYS/QADBPKG)
* Remote database directory information (QSYS/QADBXRDBD)

$7. 使用源文件
1、显示一个对象（假设由源文件创建的）的源文件，使用detail选项即可
DSPOBJD OBJ(LYF1/VW001) OBJTYPE(*ALL) DETAIL(*SERVICE)

$1.1 基本操作
Starting interactive SQL：STRSQL
Starting Query Manager：STRQM
(1)INSERT数据
如果定义DataLink列缺省值，可以用函数DLVALUE('’,'URL’,'’).
如果想是插入数据为追加模式，使用CHGPF命令修改表格文件的Reuse Deleted Records属性为*NO.
插入数据的顺序不能保证检索的缺省顺序.
如果指定COMMIT(*ALL), COMMIT(*CS), COMMIT(*CHG), or COMMIT(*RR)（交换SQL会话的属性），insert语句执行失败，已经插入的数据将回滚
如果指定COMMIT(*NONE)，insert语句执行失败，已经插入的数据将提交
NOTES：
修改SQL会话的属性：在Enter SQL Statements画面点击F13 Services，选择修改会话属性菜单即可，在该窗口可以修改select输出方式、提交控制方式、日期格式及分隔符、时间格式及分隔符、数据预取功 能、哪种命名规范、排序方式和语言ID
如果命名规范采用SQL，查询语句写法selec * from lyf1.tbtest
如果命名规范采用SYS，查询语句写法selec * from lyf1/tbtest
insert into lyf1/tb2007(c1,c2) values(1,’a')在strsql下执行失败,原因是数值之间用逗号隔开后还需加一个空格，否则在STRSQL下执行报错，如果数据都是字符串或 数字，不需要加，如果数据类型是混合的，数据需要用逗号和空格隔开。

(2)UPDATE数据
如果指定COMMIT(*ALL), COMMIT(*CS), COMMIT(*CHG), or COMMIT(*RR)（交换SQL会话的属性），update语句执行失败，已经修改的数据将回滚
如果指定COMMIT(*NONE)，update语句执行失败，已经修改的数据将提交
特殊写法：
UPDATE EMPLOYEE SET (WORKDEPT, PHONENO, JOB)= (’D11’, ’7213’, ’DESIGNER’) WHERE EMPNO = ’000270’ #几个字段同时改
UPDATE CL_SCHED SET ROW =(SELECT * FROM MYCOPY WHERE CL_SCHED.CLASS_CODE = MYCOPY.CLASS_CODE) #块修改

(3)DELETE数据
如果指定COMMIT(*ALL), COMMIT(*CS), COMMIT(*CHG), or COMMIT(*RR)（交换SQL会话的属性），delete语句执行失败，已经删除的数据将回滚
如果指定COMMIT(*NONE)，delete语句执行失败，已经删除的数据将提交
使用DELETE语句批量删除的时候，建议使用系统命令终止日志记录：ENDJRNPF

(4)SELECT数据
db2/400中没有sysibm.sysdummy1对象，如果要查询某个特定寄存器或常量或函数，只需要找任意一张表格查询即可
select 1+1 from tbtest
select user from tbtest
select CURRENT_DATE from tbtest
select CURRENT_SERVER from tbtest
数据库字典表放在QSYS2库中，譬如：
select table_name from qsys2/systables(syscolumns)
select table_name from schema_name/systables(syscolumns)

(5)创建别名
如果表文件有多个成员，为了访问某个特定的成员，可以为这个成员建立别名
CREATE ALIAS lyf1.MBR2_tbtest FOR lyf1.tbtest(MBR2)

$1.2 SQL中的特定寄存器：
CURRENT DATE＝CURRENT_DATE：The current date.
CURRENT TIME＝CURRENT_TIME：The current time.
CURRENT TIMESTAMP＝CURRENT_TIMESTAMP：The current date and time in timestamp format.
CURRENT TIMEZONE＝CURRENT_TIMEZONE：A duration of time that links local time to Universal Coordinated Time (UTC) using the formula:local time - CURRENT TIMEZONE = UTC It is taken from the system value QUTCOFFSET.
CURRENT SERVER＝CURRENT_SERVER：The name of the relational database as contained in the relational database directory table in the relational database directory.
USER： The run-time authorization identifier (user profile) of the job.
CURRENT PATH＝CURRENT_PATH＝CURRENT FUNCTION PATH：The SQL path used to resolve unqualified data type names, procedure names, and function names.