This content originally appeared on DEV Community and was authored by Cong Li
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:
oncheck -pP chunk_number page_offset
To display pages based on partition number and logical page number (these concepts will be introduced in subsequent chapters):
oncheck -pp partition_number logical_page_number
Note:
-
chunk_numberstarts from 1, whilepage_offsetandlogical_page_numberstart from 0.
1.2 How to Get partition_number?
- If there is no partition, get it from
systables(orsysmaster:systabnames). - For partitioned tables, query
sysfragments. - Use
oncheck -ptto find thepartition_numberof all fragments.
Note:
-
oncheckcan recognize both decimal and hexadecimal numbers. -
oncheckcan display most pages, but for logical log pages, useonlog.
1.3 Locating partition numbers
- The
partition number(partnum) uniquely identifies a specifictblspace. - A non-partitioned table has only one data
tblspace, thus only onepartition number. - For each
dbspacefragment, a partitioned table has atblspace(and apartnum).
For a detailed introduction to partition numbers, refer to subsequent articles.
For non-partitioned tables, use systables:
select partnum, hex(partnum) from systables where tabname = 'table_name';
Or use:
database sysmaster;
select partnum, hex(partnum) from systabnames where tabname = 'table_name';
For partitioned tables, use sysfragments:
select partn, hex(partn) from systables t, sysfragments f
where t.tabid = f.tabid and tabname = "table_name";
The easiest way to get partnums:
oncheck -pt database_name:table_name
1.4 Displaying the Page Containing a Specific Row
First, find the rowid of the concerned row:
select *, rowid from your_table where __condition__;
Then display the page:
oncheck -pp database:table_name rowid
2. Displaying Pages - Non-Partitioned Tables and Indexes
2.1 Preparing Metadata
drop database if exists test1;
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:
#!/usr/bin/python
def main():
fp = open("data.unl", "w")
for i in range(0, 1000):
fp.write("%d|%08dxxxxxxxxxxxxxxxxxxx|%08dxxxxxxxxxxxxxxxxxxxxxxxxxx\n" % (i, i, i))
fp.close()
if __name__ == '__main__':
main()
Generate data and load it:
./generate_data.py
dbaccess test1 -
load from 'data.unl' insert into t1;
2.3 Finding Pages Based on Physical Address
Get the physical address:
oncheck -pt test1:t1
Data part:
Extents
Logical Page Physical Page Size Physical Pages
0 4:57 32 32
32 4:93 96 96
Index part:
Extents
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:
oncheck -pP 4 58
(57 is a bitmap page and needs to be skipped)
[root@gbasehdr202 test]# oncheck -pP 4 58
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:
oncheck -pP 4 54
[root@gbasehdr202 test]# oncheck -pP 4 54
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
[root@centos7_2 test]# oncheck -pt test1:t1
Data Partition
Partition partnum 4194306
Index Partition
Partition partnum 4194307
Display Data Partition
[root@gbasehdr202 test]# oncheck -pp 4194306 1
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
[root@gbasehdr202 test]# oncheck -pp 4194307 1
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
[root@centos7_2 test]# dbaccess test1 -
Database selected.
> select partnum from systables where tabname = 't1';
partnum
4194389
1 row(s) retrieved.
Retrieve Index Partition partnum
> select f.partn from sysfragments f, systables t where f.tabid = t.tabid and t.tabname='t1';
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:
[root@centos7_2 test]# dbaccess test1 -
Database selected.
> select c1, rowid from t1 where c1 = 0;
c1 rowid
0 257
1 row(s) retrieved.
Display:
oncheck -pp test1:t1 257
[root@gbasehdr202 test]# 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.
onmode -c
- Determine the file corresponding to chunk 4.
[root@gbasehdr202 test]# onstat -d
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.
dd if=/gbase8s/3.3.0_2X4_1/ids/storage/ol_gbasedbt1210_20240718181816_datadbs1_p_1 of=/tmp/1.txt bs=2048 count=1 skip=58
- View the read content.
vi /tmp/1.txt
%!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.
addr stamp chksum nslots flag type frptr frcnt next prev
DEC:DEC DEC HEX DEC HEX CHAR DEC DEC HEX HEX
For example:
[root@centos7_2 test]# oncheck -pP 4 2337
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.
slot ptr len flag
1 24 140 0
2 164 140 0
-
slotis the slot number. -
ptris the offset within the page where the slot's data starts. -
lenis the length of the data that the slot points to. -
flagis 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.
This content originally appeared on DEV Community and was authored by Cong Li
Print
Share
Comment
Cite
Upload
Translate
Updates
There are no updates yet.
Click the Upload button above to add an update.
APA
MLA
Cong Li | Sciencx (2024-07-24T09:14:35+00:00) Detailed Explanation of the Page Structure in GBase 8s Database (2). Retrieved from https://www.scien.cx/2024/07/24/detailed-explanation-of-the-page-structure-in-gbase-8s-database-2/
" » Detailed Explanation of the Page Structure in GBase 8s Database (2)." Cong Li | Sciencx - Wednesday July 24, 2024, https://www.scien.cx/2024/07/24/detailed-explanation-of-the-page-structure-in-gbase-8s-database-2/
HARVARDCong Li | Sciencx Wednesday July 24, 2024 » Detailed Explanation of the Page Structure in GBase 8s Database (2)., viewed ,<https://www.scien.cx/2024/07/24/detailed-explanation-of-the-page-structure-in-gbase-8s-database-2/>
VANCOUVERCong Li | Sciencx - » Detailed Explanation of the Page Structure in GBase 8s Database (2). [Internet]. [Accessed ]. Available from: https://www.scien.cx/2024/07/24/detailed-explanation-of-the-page-structure-in-gbase-8s-database-2/
CHICAGO" » Detailed Explanation of the Page Structure in GBase 8s Database (2)." Cong Li | Sciencx - Accessed . https://www.scien.cx/2024/07/24/detailed-explanation-of-the-page-structure-in-gbase-8s-database-2/
IEEE" » Detailed Explanation of the Page Structure in GBase 8s Database (2)." Cong Li | Sciencx [Online]. Available: https://www.scien.cx/2024/07/24/detailed-explanation-of-the-page-structure-in-gbase-8s-database-2/. [Accessed: ]
rf:citation » Detailed Explanation of the Page Structure in GBase 8s Database (2) | Cong Li | Sciencx | https://www.scien.cx/2024/07/24/detailed-explanation-of-the-page-structure-in-gbase-8s-database-2/ |
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.