Displaying Page Content (Non-Partitioned Tables and Indexes)
In the world of databases, the page is the basic unit of data storage. The page structure of GBase 8s database is crucial for optimizing data access and improving query efficiency. This article will take you deep into the page structure of GBase 8s, exploring the inner world of non-partitioned tables and indexes.
1. Methods to Display Pages
1.1 Using the oncheck Command
To display pages based on chunk number and page offset:
To display pages based on partition number and logical page number (these concepts will be introduced in subsequent chapters):
Note:
chunk_number starts from 1, while page_offset and logical_page_number start from 0.
1.2 How to Get partition_number?
If there is no partition, get it from systables (or sysmaster:systabnames).
For partitioned tables, query sysfragments.
Use oncheck -pt to find the partition_number of all fragments.
Note:
oncheck can recognize both decimal and hexadecimal numbers.
oncheck can display most pages, but for logical log pages, use onlog.
1.3 Locating partition numbers
The partition number (partnum) uniquely identifies a specific tblspace.
A non-partitioned table has only one data tblspace, thus only one partition number.
For each dbspace fragment, a partitioned table has a tblspace (and a partnum).
For a detailed introduction to partition numbers, refer to subsequent articles.
For non-partitioned tables, use systables:
Or use:
select partnum, hex(partnum) from systabnames where tabname = ‘table_name’;
For partitioned tables, use sysfragments:
where t.tabid = f.tabid and tabname = “table_name”;
The easiest way to get partnums:
1.4 Displaying the Page Containing a Specific Row
First, find the rowid of the concerned row:
Then display the page:
2. Displaying Pages – Non-Partitioned Tables and Indexes
2.1 Preparing Metadata
create database test1 with log;
create table t1(c1 int, c2 char(100), c3 varchar(100)) in datadbs1;
create index i1 on t1(c1) in datadbs1;
2.2 Preparing Data
Data generation script:
def main():
fp = open(“data.unl“, “w“)
for i in range(0, 1000):
fp.write(“%d|%08dxxxxxxxxxxxxxxxxxxx|%08dxxxxxxxxxxxxxxxxxxxxxxxxxxn“ % (i, i, i))
fp.close()
if __name__ == ‘__main__‘:
main()
Generate data and load it:
dbaccess test1 –
load from ‘data.unl’ insert into t1;
2.3 Finding Pages Based on Physical Address
Get the physical address:
Data part:
Logical Page Physical Page Size Physical Pages
0 4:57 32 32
32 4:93 96 96
Index part:
Logical Page Physical Page Size Physical Pages
0 4:53 4 4
4 4:89 4 4
8 4:189 8 8
Display the data part:
(57 is a bitmap page and needs to be skipped)
addr stamp chksum nslots flag type frptr frcnt next prev
4:58 287029 610f 13 1 DATA 1844 148 0 0
slot ptr len flag
1 24 140 0
2 164 140 0
3 304 140 0
4 444 140 0
5 584 140 0
6 724 140 0
7 864 140 0
8 1004 140 0
9 1144 140 0
10 1284 140 0
11 1424 140 0
12 1564 140 0
13 1704 140 0
slot 1:
0: 0 0 0 0 30 30 30 30 30 30 30 30 78 78 78 78 ….00000000xxxx
16: 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 20 xxxxxxxxxxxxxxx
32: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
48: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
64: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
80: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
96: 20 20 20 20 20 20 20 20 0 22 30 30 30 30 30 30 .”000000
112: 30 30 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00xxxxxxxxxxxxxx
128: 78 78 78 78 78 78 78 78 78 78 78 78 xxxxxxxxxxxx….
Display the index part:
addr stamp chksum nslots flag type frptr frcnt next prev
4:54 289313 6a17 7 70 BTREE 76 1940 0 0
slot ptr len flag
1 24 8 0
2 36 8 0
3 44 8 0
4 52 8 0
5 60 8 0
6 68 8 0
7 32 4 0
slot 1:
0: 80 0 0 99 0 0 0 3 …………….
slot 2:
0: 80 0 1 33 0 0 0 2 …3…………
slot 3:
0: 80 0 1 cd 0 0 0 4 …M…………
slot 4:
0: 80 0 2 67 0 0 0 5 …g…………
slot 5:
0: 80 0 3 1 0 0 0 6 …………….
slot 6:
0: 80 0 3 9b 0 0 0 7 …………….
slot 7:
0: 0 0 0 8 …………….
2.4 Based on Logical Address 1
Retrieve partnum
Data Partition
Partition partnum 4194306
Index Partition
Partition partnum 4194307
Display Data Partition
addr stamp chksum nslots flag type frptr frcnt next prev
4:58 287029 610f 13 1 DATA 1844 148 0 0
slot ptr len flag
1 24 140 0
2 164 140 0
3 304 140 0
4 444 140 0
5 584 140 0
6 724 140 0
7 864 140 0
8 1004 140 0
9 1144 140 0
10 1284 140 0
11 1424 140 0
12 1564 140 0
13 1704 140 0
slot 1:
0: 0 0 0 0 30 30 30 30 30 30 30 30 78 78 78 78 ….00000000xxxx
16: 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 20 xxxxxxxxxxxxxxx
32: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
48: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
64: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
80: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
96: 20 20 20 20 20 20 20 20 0 22 30 30 30 30 30 30 .“000000
112: 30 30 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00xxxxxxxxxxxxxx
128: 78 78 78 78 78 78 78 78 78 78 78 78 xxxxxxxxxxxx….
Display Index Partition
addr stamp chksum nslots flag type frptr frcnt next prev
4:54 289313 6a17 7 70 BTREE 76 1940 0 0
slot ptr len flag
1 24 8 0
2 36 8 0
3 44 8 0
4 52 8 0
5 60 8 0
6 68 8 0
7 32 4 0
slot 1:
0: 80 0 0 99 0 0 0 3 ……..
slot 2:
0: 80 0 1 33 0 0 0 2 …3….
slot 3:
0: 80 0 1 cd 0 0 0 4 …M….
slot 4:
0: 80 0 2 67 0 0 0 5 …g….
slot 5:
0: 80 0 3 1 0 0 0 6 …….
slot 6:
0: 80 0 3 9b 0 0 0 7 ……..
slot 7:
0: 0 0 0 8 ……..
2.5 Based on Logical Address 2
Retrieve Data Partition partnum
Database selected.
> select partnum from systables where tabname = ‘t1’;
partnum
4194389
1 row(s) retrieved.
Retrieve Index Partition partnum
partn
4194390
1 row(s) retrieved.
Once the partnums are retrieved, the method of displaying pages is the same as the previous section.
2.6 Based on rowid
Retrieve rowid:
Database selected.
> select c1, rowid from t1 where c1 = 0;
c1 rowid
0 257
1 row(s) retrieved.
Display:
oncheck -pp test1:t1 257
addr stamp chksum nslots flag type frptr frcnt next prev
4:58 287029 610f 13 1 DATA 1844 148 0 0
slot ptr len flag
1 24 140 0
2 164 140 0
3 304 140 0
4 444 140 0
5 584 140 0
6 724 140 0
7 864 140 0
8 1004 140 0
9 1144 140 0
10 1284 140 0
11 1424 140 0
12 1564 140 0
13 1704 140 0
slot 1:
0: 0 0 0 0 30 30 30 30 30 30 30 30 78 78 78 78 ….00000000xxxx
16: 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 20 xxxxxxxxxxxxxxx
32: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
48: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
64: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
80: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
96: 20 20 20 20 20 20 20 20 0 22 30 30 30 30 30 30 .“000000
112: 30 30 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00xxxxxxxxxxxxxx
128: 78 78 78 78 78 78 78 78 78 78 78 78 xxxxxxxxxxxx….
2.7 Directly Reading a Chunk
Objective: To find the content of a data page from the chunk file, using the physical address (4 2337) as an example.
You need to force a checkpoint to ensure the data is written to disk.
Determine the file corresponding to chunk 4.
On–Line — Up 00:22:55 — 17346180 Kbytes
Dbspaces
address number flags fchunk nchunks pgsize flags owner name
4545c028 1 0x70001 1 1 2048 N BA gbasedbt rootdbs
47ddc7f0 2 0x1070001 2 1 2048 N PBA gbasedbt plog
47eb8dd0 3 0x60001 3 1 2048 N BA gbasedbt llog
47966dd0 4 0x60001 4 1 2048 N BA gbasedbt datadbs1
47d90560 5 0x60001 5 1 2048 N BA gbasedbt datadbs2
47d29dd0 6 0x60001 6 1 2048 N BA gbasedbt datadbs3
47d255b0 7 0x60001 7 1 8192 N BA gbasedbt data8dbs1
47b22da0 8 0x60001 8 1 8192 N BA gbasedbt data8dbs2
47d94520 9 0x60001 9 1 8192 N BA gbasedbt data8dbs3
4847fd78 10 0x42001 10 1 8192 N TBA gbasedbt tmpdbspace
486f5560 11 0x68001 11 1 2048 N SBA gbasedbt sbspace1
479fe5b0 12 0x4a001 12 1 2048 N UBA gbasedbt tmpsbspace
12 active, 2047 maximum
Chunks
address chunk/dbs offset size free bpages flags pathname
4545c258 1 1 0 78848 65818 PO–B— /gbase8s/3.3.0_2X4_1/ids/storage/rootdbs
47d9a028 2 2 0 32768 0 PO–BE– /gbase8s/3.3.0_2X4_1/ids/storage/ol_gbasedbt1210_20240718181816_plog_p_1
47bb8028 3 3 0 32768 15 PO–BE– /gbase8s/3.3.0_2X4_1/ids/storage/ol_gbasedbt1210_20240718181816_llog_p_1
47bc2028 4 4 0 32768 32571 PO–BE– /gbase8s/3.3.0_2X4_1/ids/storage/ol_gbasedbt1210_20240718181816_datadbs1_p_1
47d92028 5 5 0 32768 32715 PO–BE– /gbase8s/3.3.0_2X4_1/ids/storage/ol_gbasedbt1210_20240718181816_datadbs2_p_1
47c99028 6 6 0 32768 32715 PO–BE– /gbase8s/3.3.0_2X4_1/ids/storage/ol_gbasedbt1210_20240718181816_datadbs3_p_1
48722028 7 7 0 8192 8139 PO–BE– /gbase8s/3.3.0_2X4_1/ids/storage/ol_gbasedbt1210_20240718181816_data8dbs1_p_1
47ad5028 8 8 0 8192 8139 PO–BE– /gbase8s/3.3.0_2X4_1/ids/storage/ol_gbasedbt1210_20240718181816_data8dbs2_p_1
47e4f028 9 9 0 8192 8139 PO–BE– /gbase8s/3.3.0_2X4_1/ids/storage/ol_gbasedbt1210_20240718181816_data8dbs3_p_1
486f4028 10 10 0 8192 8139 PO–BE– /gbase8s/3.3.0_2X4_1/ids/storage/ol_gbasedbt1210_20240718181816_tmpdbspace_p_1
47eee028 11 11 0 32768 23802 23802 POSB— /gbase8s/3.3.0_2X4_1/ids/storage/ol_gbasedbt1210_20240718181816_sbspace1_p_1
Metadata 1745 8466 1745
47bd3028 12 12 0 32768 23802 23802 POSB— /gbase8s/3.3.0_2X4_1/ids/storage/ol_gbasedbt1210_20240718181816_tmpsbspace_p_1
Metadata 1745 8466 1745
12 active, 32766 maximum
NOTE: The values in the “size” and “free” columns for DBspace chunks are
displayed in terms of “pgsize” of the DBspace to which they belong.
Read 2048 bytes from the physical file.
View the read content.
%!xxd
The highlighted part is the beginning of the first three lines, starting from integers 0, 1, 2.
Appendix
Page Header Data Format
The data in the page header is a mix of hexadecimal and decimal values. The definitions are provided here to avoid any ambiguity.
DEC:DEC DEC HEX DEC HEX CHAR DEC DEC HEX HEX
For example:
addr stamp chksum nslots flag type frptr frcnt next prev
4:2337 6362036 1af0 13 801 DATA 1844 148 0 0
The type column is translated from pg_flags. Refer to the notes for the range.
Slot Data Format
At the start of the page, there is a list of all slots.
1 24 140 0
2 164 140 0
slot is the slot number.
ptr is the offset within the page where the slot’s data starts.
len is the length of the data that the slot points to.
flag is 2 if the data pointed to by the slot has a forward pointer (indicating the data is continued on another page because it couldn’t fit on this one); otherwise, it is 0.
Remember, a slot is not a row but a container. A slot can contain a row, part of a row (in the case of row splits across pages), or other structures. The slot table entries consist of the length and position on the page.
Through this exploration, we have gained a deeper understanding of the page structure in GBase 8s. From physical addresses to logical addresses, from data pages to index pages, each part carries critical information for the database’s operation. We hope this article helps you better grasp the internal mechanisms of GBase 8s, enhancing your database management efficiency and effectiveness.