Information Systems:DB2 for i Database - Revision history

Norwinu: Norwinu moved page Information Systems:DB2 (Data Base) to Information Systems:DB2 for i Database

2016-07-20T16:29:05Z

Norwinu moved page Information Systems:DB2 (Data Base) to Information Systems:DB2 for i Database

Norwinu at 16:27, 20 July 2016

2016-07-20T16:27:12Z

New page

Data files are grouped together into libraries (like folders on PC’s, but only with a single level). These data libraries are -

'''Production''' '''Development'''
ASW UP1480BFVA UP1480BFPL
Extension UWDASWPRDD UWDASWVOLD
Web (web orders, InfoNet) WEBPRDD WEBDEVD

There are two types of data files; physical and logical. A physical file contains the actual data records. A logical file is not data, but an index to the data.

Logical files are defined to allow different programs to access the data in different ways; or to optimise SQL (see Programming / SQL – Structure Query Language for an explanation of this).

For example, SROITR (Inventory Transactions) is a physical file. Records are accessed in the sequence in which they were written. Some of the logical files, or indexes, are SRBITR (keyed by inventory event code, order number, line number), SR1ITR (item, warehouse, date, time, text, batch, quantity), SR2ITR (item, date, time, text, batch, quantity), SR12ITR (accounting period, item, warehouse), and SRYITR (warehouse, inventory event code, date, handler).

In ASW, the first two characters indicate the system (in this example 'Stock Room'). An ‘O’ for the third character means it is a physical file; ‘B’ or a number means logical file; other letters working backwards from Z are logical files we have built. The rest of the name indicates the type of data. In the extensions, the last character(s) indicate the type; P for physical, or L and a number for logical.

To find out if a particular file is physical or logical, and it logical what the physical file is, key in DSPFD SR2ITR and press enter.
Notice 'Type of file' 10 lines down, and 'Files accessed by logical file' in about the middle.

Display Spooled File
File . . . . . : QPDSPFD Page/Line 1/1 Control . . . . . Columns 1 - 130 Find . . . . . .
*...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+....0....+....1....+....2.... 3/26/15 Display File Description
DSPFD Command Input
File . . . . . . . . . . . . . . . . . . . : FILE SR2ITR
Library . . . . . . . . . . . . . . . . . : *LIBL
Type of information . . . . . . . . . . . . : TYPE *ALL
File attributes . . . . . . . . . . . . . . : FILEATR *ALL
System . . . . . . . . . . . . . . . . . . : SYSTEM *LCL
File Description Header
File . . . . . . . . . . . . . . . . . . . : FILE SR2ITR
Library . . . . . . . . . . . . . . . . . . : UP1480BFPL
'''Type of file . . . . . . . . . . . . . . . : Logical'''
File type . . . . . . . . . . . . . . . . . : FILETYPE *DATA
Auxiliary storage pool ID . . . . . . . . . : 00001
Data Base File Attributes
Externally described file . . . . . . . . . : Yes
File level identifier . . . . . . . . . . . : 1050129085518
Creation date . . . . . . . . . . . . . . . : 01/29/05
Text 'description' . . . . . . . . . . . . : TEXT Item/date/time/
stockroom/text/batch/qty
Distributed file . . . . . . . . . . . . . : No
Partitioned SQL Table . . . . . . . . . . . : No
DBCS capable . . . . . . . . . . . . . . . : No
Maximum members . . . . . . . . . . . . . . : MAXMBRS 1
Number of triggers . . . . . . . . . . . . : 0
Number of members . . . . . . . . . . . . . : 1
Access path maintenance . . . . . . . . . . : MAINT *IMMED
Access path recovery . . . . . . . . . . . : RECOVER *NO
Force keyed access path . . . . . . . . . . : FRCACCPTH *NO
Preferred storage unit . . . . . . . . . . : UNIT *ANY
Record format selector program . . . . . . : FMTSLR *NONE
Records to force a write . . . . . . . . . : FRCRATIO *NONE
Maximum file wait time . . . . . . . . . . : WAITFILE *IMMED
Maximum record wait time . . . . . . . . . : WAITRCD 60
With check option . . . . . . . . . . . . . : NONE
Allow read operation . . . . . . . . . . . : Yes
Allow write operation . . . . . . . . . . . : Yes
Allow update operation . . . . . . . . . . : ALWUPD *YES
Allow delete operation . . . . . . . . . . : ALWDLT *YES
Record format level check . . . . . . . . . : LVLCHK *YES
Access path . . . . . . . . . . . . . . . . : Keyed
Access path size . . . . . . . . . . . . . : ACCPTHSIZ *MAX1TB
Access path logical page size . . . . . . . : PAGESIZE *KEYLEN
Maximum key length . . . . . . . . . . . . : 98
Maximum record length . . . . . . . . . . . : 208
Access Path Description
Access path maintenance . . . . . . . . . . : MAINT *IMMED
Unique key values required . . . . . . . . : UNIQUE No
Key order . . . . . . . . . . . . . . . . . : Not specified
Select/omit specified . . . . . . . . . . . : No
Access path journaled . . . . . . . . . . . : No
Access path . . . . . . . . . . . . . . . . : Keyed
Number of key fields . . . . . . . . . . . : 7
Record format . . . . . . . . . . . . . . . : ITR
Key field . . . . . . . . . . . . . . . . : ITPRDC
Sequence . . . . . . . . . . . . . . . : Ascending
Sign specified . . . . . . . . . . . . : UNSIGNED
Zone/digit specified . . . . . . . . . : *NONE
Alternative collating sequence . . . . : No
Key field . . . . . . . . . . . . . . . . : ITDATE
Sequence . . . . . . . . . . . . . . . : Ascending
Sign specified . . . . . . . . . . . . : SIGNED
Zone/digit specified . . . . . . . . . : *NONE
Alternative collating sequence . . . . : No
Key field . . . . . . . . . . . . . . . . : ITTIME
Sequence . . . . . . . . . . . . . . . : Ascending
Sign specified . . . . . . . . . . . . : SIGNED
Zone/digit specified . . . . . . . . . : *NONE
Alternative collating sequence . . . . : No
Key field . . . . . . . . . . . . . . . . : ITSROM
Sequence . . . . . . . . . . . . . . . : Ascending
Sign specified . . . . . . . . . . . . : UNSIGNED
Zone/digit specified . . . . . . . . . : *NONE
Alternative collating sequence . . . . : No
Key field . . . . . . . . . . . . . . . . : ITTEXT
Sequence . . . . . . . . . . . . . . . : Ascending
Sign specified . . . . . . . . . . . . : UNSIGNED
Zone/digit specified . . . . . . . . . : *NONE
Alternative collating sequence . . . . : No
Key field . . . . . . . . . . . . . . . . : ITBATC
Sequence . . . . . . . . . . . . . . . : Ascending
Sign specified . . . . . . . . . . . . : UNSIGNED
Zone/digit specified . . . . . . . . . : *NONE
Alternative collating sequence . . . . : No
Key field . . . . . . . . . . . . . . . . : ITQTY
Sequence . . . . . . . . . . . . . . . : Ascending
Sign specified . . . . . . . . . . . . : SIGNED
Zone/digit specified . . . . . . . . . : *NONE
Alternative collating sequence . . . . : No
'''Files accessed by logical file PFILE
File Library LF Format
SROITR UP1480BFPL ITR'''
Sort Sequence . . . . . . . . . . . . . . . : SRTSEQ *HEX
Language identifier . . . . . . . . . . . . : LANGID ENU
Member Description
Member . . . . . . . . . . . . . . . . . . : MBR SR2ITR
Member level identifier . . . . . . . . . : 1050129085518
Member creation date . . . . . . . . . . : 01/29/05
Text 'description' . . . . . . . . . . . : TEXT Item/date/time/
stockroom/text/batch/qty
Expiration date for member . . . . . . . : EXPDATE *NONE
Access path maintenance . . . . . . . . . : MAINT *IMMED
Access path recovery . . . . . . . . . . : RECOVER *NO
Preferred storage unit . . . . . . . . . : UNIT *ANY
Record format selector program . . . . . : FMTSLR *NONE
Records to force a write . . . . . . . . : FRCRATIO *NONE
Share open data path . . . . . . . . . . : SHARE *YES
Access Path Activity Statistics . . . . . :
Access path logical reads . . . . . . . :
Access path physical reads . . . . . . :
Index size . . . . . . . . . . . . . . : 3315630080
Access path valid . . . . . . . . . . . : Yes
Implicit access path sharing . . . . . : Yes
Access path journaled . . . . . . . . : Yes
Number of unique partial key values . . :
Key field 1 . . . . . . . . . . . . . : 66959
Key fields 1 - 2 . . . . . . . . . . : 11624772
Key fields 1 - 3 . . . . . . . . . . : 23585801
Key fields 1 - 4 . . . . . . . . . . : 23742851
File owning access path . . . . . . . . : UP1480BFPL/SR18ITR
Member . . . . . . . . . . . . . . . . : SR18ITR
Shared access path attributes
Maintenance . . . . . . . . . . . . . : *IMMED
Access path recovery . . . . . . . . : *NO
Force keyed access path . . . . . . . : *NO
Keys must be unique . . . . . . . . . : No
Last change date/time . . . . . . . . . . : 01/17/15 15:29:30
Last save date/time . . . . . . . . . . . : 01/17/15 01:06:20
Last restore date/time . . . . . . . . . : 01/17/15 14:49:33
Last used date . . . . . . . . . . . . . : 02/12/15
Days used count . . . . . . . . . . . . . : 1
Reset date . . . . . . . . . . . . . . :
Number of data members . . . . . . . . . : 1
Based on file . . . . . . . . . . . . . . : SROITR
Library . . . . . . . . . . . . . . . . : UP1480BFPL
Member . . . . . . . . . . . . . . . . : SROITR
Logical file format . . . . . . . . . . : ITR
Number of index entries . . . . . . . . : 23756196
Record Format List
Record Format Level
Format Fields Length Identifier
ITR 36 208 3CDDB9F4542D1
Text . . . . . . . . . . . . . . . . . . . :
Total number of formats . . . . . . . . . . : 1
Total number of fields . . . . . . . . . . . : 36
Total record length . . . . . . . . . . . . : 208
Member List
Source Creation Last Change
Member Size Type Date Date Time
SR2ITR 3315630080 01/29/05 01/17/15 15:29:30
Text: Item/date/time/stockroom/text/batch/quantity
Total number of members . . . . . . . . . : 1
Total number of members not available . . : 0
Total of member sizes . . . . . . . . . . : 3315630080

This shows that SR2ITR is a logical file that is for the physical file SROITR.

[[Category:Database Query]]
[[Category: IBM i]]

Sheilav at 19:29, 19 October 2015

2015-10-19T19:29:29Z

Show changes

Sheilav: Created page with "Data files are grouped together into libraries (like folders on PC’s, but only with a single level). These data libraries are - '''P..."

2015-10-19T19:07:39Z

Created page with "Data files are grouped together into libraries (like folders on PC’s, but only with a single level). These data libraries are - '''P..."

New page

Data files are grouped together into libraries (like folders on PC’s, but only with a single level). These data libraries are -

'''Production''' '''Development'''
ASW UP1480BFVA UP1480BFPL
Extension UWDASWPRDD UWDASWVOLD
Web (web orders, InfoNet) WEBPRDD WEBDEVD

There are two types of data files; physical and logical. A physical file contains the actual data records. A logical file is not data, but an index to the data.

Logical files are defined to allow different programs to access the data in different ways; or to optimise SQL (see Programming / SQL – Structure Query Language for an explanation of this).

For example, SROITR (Inventory Transactions) is a physical file. Records are accessed in the sequence in which they were written. Some of the logical files, or indexes, are SRBITR (keyed by inventory event code, order number, line number), SR1ITR (item, warehouse, date, time, text, batch, quantity), SR2ITR (item, date, time, text, batch, quantity), SR12ITR (accounting period, item, warehouse), and SRYITR (warehouse, inventory event code, date, handler).

In ASW, the first two characters indicate the system (in this example 'Stock Room'). An ‘O’ for the third character means it is a physical file; ‘B’ or a number means logical file; other letters working backwards from Z are logical files we have built. The rest of the name indicates the type of data. In the extensions, the last character(s) indicate the type; P for physical, or L and a number for logical.

To find out if a particular file is physical or logical, and it logical what the physical file is, key in DSPFD SR2ITR and press enter.
Notice 'Type of file' 10 lines down, and 'Files accessed by logical file' in about the middle.

Display Spooled File
File . . . . . : QPDSPFD Page/Line 1/1
Control . . . . . Columns 1 - 130
Find . . . . . .
*...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+....0....+....1....+....2....+....3
3/26/15 Display File Description
DSPFD Command Input
File . . . . . . . . . . . . . . . . . . . : FILE SR2ITR
Library . . . . . . . . . . . . . . . . . : *LIBL
Type of information . . . . . . . . . . . . : TYPE *ALL
File attributes . . . . . . . . . . . . . . : FILEATR *ALL
System . . . . . . . . . . . . . . . . . . : SYSTEM *LCL
File Description Header
File . . . . . . . . . . . . . . . . . . . : FILE SR2ITR
Library . . . . . . . . . . . . . . . . . . : UP1480BFPL
'''Type of file . . . . . . . . . . . . . . . : Logical'''
File type . . . . . . . . . . . . . . . . . : FILETYPE *DATA
Auxiliary storage pool ID . . . . . . . . . : 00001
Data Base File Attributes
Externally described file . . . . . . . . . : Yes
File level identifier . . . . . . . . . . . : 1050129085518
Creation date . . . . . . . . . . . . . . . : 01/29/05
Text 'description' . . . . . . . . . . . . : TEXT Item/date/time/
stockroom/text/batch/qty
Distributed file . . . . . . . . . . . . . : No
Partitioned SQL Table . . . . . . . . . . . : No
DBCS capable . . . . . . . . . . . . . . . : No
Maximum members . . . . . . . . . . . . . . : MAXMBRS 1
Number of triggers . . . . . . . . . . . . : 0
Number of members . . . . . . . . . . . . . : 1
Access path maintenance . . . . . . . . . . : MAINT *IMMED
Access path recovery . . . . . . . . . . . : RECOVER *NO
Force keyed access path . . . . . . . . . . : FRCACCPTH *NO
Preferred storage unit . . . . . . . . . . : UNIT *ANY
Record format selector program . . . . . . : FMTSLR *NONE
Records to force a write . . . . . . . . . : FRCRATIO *NONE
Maximum file wait time . . . . . . . . . . : WAITFILE *IMMED
Maximum record wait time . . . . . . . . . : WAITRCD 60
With check option . . . . . . . . . . . . . : NONE
Allow read operation . . . . . . . . . . . : Yes
Allow write operation . . . . . . . . . . . : Yes
Allow update operation . . . . . . . . . . : ALWUPD *YES
Allow delete operation . . . . . . . . . . : ALWDLT *YES
Record format level check . . . . . . . . . : LVLCHK *YES
Access path . . . . . . . . . . . . . . . . : Keyed
Access path size . . . . . . . . . . . . . : ACCPTHSIZ *MAX1TB
Access path logical page size . . . . . . . : PAGESIZE *KEYLEN
Maximum key length . . . . . . . . . . . . : 98
Maximum record length . . . . . . . . . . . : 208
Access Path Description
Access path maintenance . . . . . . . . . . : MAINT *IMMED
Unique key values required . . . . . . . . : UNIQUE No
Key order . . . . . . . . . . . . . . . . . : Not specified
Select/omit specified . . . . . . . . . . . : No
Access path journaled . . . . . . . . . . . : No
Access path . . . . . . . . . . . . . . . . : Keyed
Number of key fields . . . . . . . . . . . : 7
Record format . . . . . . . . . . . . . . . : ITR
Key field . . . . . . . . . . . . . . . . : ITPRDC
Sequence . . . . . . . . . . . . . . . : Ascending
Sign specified . . . . . . . . . . . . : UNSIGNED
Zone/digit specified . . . . . . . . . : *NONE
Alternative collating sequence . . . . : No
Key field . . . . . . . . . . . . . . . . : ITDATE
Sequence . . . . . . . . . . . . . . . : Ascending
Sign specified . . . . . . . . . . . . : SIGNED
Zone/digit specified . . . . . . . . . : *NONE
Alternative collating sequence . . . . : No
Key field . . . . . . . . . . . . . . . . : ITTIME
Sequence . . . . . . . . . . . . . . . : Ascending
Sign specified . . . . . . . . . . . . : SIGNED
Zone/digit specified . . . . . . . . . : *NONE
Alternative collating sequence . . . . : No
Key field . . . . . . . . . . . . . . . . : ITSROM
Sequence . . . . . . . . . . . . . . . : Ascending
Sign specified . . . . . . . . . . . . : UNSIGNED
Zone/digit specified . . . . . . . . . : *NONE
Alternative collating sequence . . . . : No
Key field . . . . . . . . . . . . . . . . : ITTEXT
Sequence . . . . . . . . . . . . . . . : Ascending
Sign specified . . . . . . . . . . . . : UNSIGNED
Zone/digit specified . . . . . . . . . : *NONE
Alternative collating sequence . . . . : No
Key field . . . . . . . . . . . . . . . . : ITBATC
Sequence . . . . . . . . . . . . . . . : Ascending
Sign specified . . . . . . . . . . . . : UNSIGNED
Zone/digit specified . . . . . . . . . : *NONE
Alternative collating sequence . . . . : No
Key field . . . . . . . . . . . . . . . . : ITQTY
Sequence . . . . . . . . . . . . . . . : Ascending
Sign specified . . . . . . . . . . . . : SIGNED
Zone/digit specified . . . . . . . . . : *NONE
Alternative collating sequence . . . . : No
'''Files accessed by logical file PFILE
File Library LF Format
SROITR UP1480BFPL ITR'''
Sort Sequence . . . . . . . . . . . . . . . : SRTSEQ *HEX
Language identifier . . . . . . . . . . . . : LANGID ENU
Member Description
Member . . . . . . . . . . . . . . . . . . : MBR SR2ITR
Member level identifier . . . . . . . . . : 1050129085518
Member creation date . . . . . . . . . . : 01/29/05
Text 'description' . . . . . . . . . . . : TEXT Item/date/time/
stockroom/text/batch/qty
Expiration date for member . . . . . . . : EXPDATE *NONE
Access path maintenance . . . . . . . . . : MAINT *IMMED
Access path recovery . . . . . . . . . . : RECOVER *NO
Preferred storage unit . . . . . . . . . : UNIT *ANY
Record format selector program . . . . . : FMTSLR *NONE
Records to force a write . . . . . . . . : FRCRATIO *NONE
Share open data path . . . . . . . . . . : SHARE *YES
Access Path Activity Statistics . . . . . :
Access path logical reads . . . . . . . :
Access path physical reads . . . . . . :
Index size . . . . . . . . . . . . . . : 3315630080
Access path valid . . . . . . . . . . . : Yes
Implicit access path sharing . . . . . : Yes
Access path journaled . . . . . . . . : Yes
Number of unique partial key values . . :
Key field 1 . . . . . . . . . . . . . : 66959
Key fields 1 - 2 . . . . . . . . . . : 11624772
Key fields 1 - 3 . . . . . . . . . . : 23585801
Key fields 1 - 4 . . . . . . . . . . : 23742851
File owning access path . . . . . . . . : UP1480BFPL/SR18ITR
Member . . . . . . . . . . . . . . . . : SR18ITR
Shared access path attributes
Maintenance . . . . . . . . . . . . . : *IMMED
Access path recovery . . . . . . . . : *NO
Force keyed access path . . . . . . . : *NO
Keys must be unique . . . . . . . . . : No
Last change date/time . . . . . . . . . . : 01/17/15 15:29:30
Last save date/time . . . . . . . . . . . : 01/17/15 01:06:20
Last restore date/time . . . . . . . . . : 01/17/15 14:49:33
Last used date . . . . . . . . . . . . . : 02/12/15
Days used count . . . . . . . . . . . . . : 1
Reset date . . . . . . . . . . . . . . :
Number of data members . . . . . . . . . : 1
Based on file . . . . . . . . . . . . . . : SROITR
Library . . . . . . . . . . . . . . . . : UP1480BFPL
Member . . . . . . . . . . . . . . . . : SROITR
Logical file format . . . . . . . . . . : ITR
Number of index entries . . . . . . . . : 23756196
Record Format List
Record Format Level
Format Fields Length Identifier
ITR 36 208 3CDDB9F4542D1
Text . . . . . . . . . . . . . . . . . . . :
Total number of formats . . . . . . . . . . : 1
Total number of fields . . . . . . . . . . . : 36
Total record length . . . . . . . . . . . . : 208
Member List
Source Creation Last Change
Member Size Type Date Date Time
SR2ITR 3315630080 01/29/05 01/17/15 15:29:30
Text: Item/date/time/stockroom/text/batch/quantity
Total number of members . . . . . . . . . : 1
Total number of members not available . . : 0
Total of member sizes . . . . . . . . . . : 3315630080

This shows that SR2ITR is a logical file that is for the physical file SROITR.

← Older revision	Revision as of 16:29, 20 July 2016
(No difference)