DEV Community

Cong Li
Cong Li

Posted on

Detailed Explanation of the Page Structure in GBase 8s Database (2)

#database

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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:

select partnum, hex(partnum) from systables where tabname = 'table_name';
Enter fullscreen mode Exit fullscreen mode

Or use:

database sysmaster;
select partnum, hex(partnum) from systabnames where tabname = 'table_name';
Enter fullscreen mode Exit fullscreen mode

For partitioned tables, use sysfragments:

select partn, hex(partn) from systables t, sysfragments f
where t.tabid = f.tabid and tabname = "table_name";
Enter fullscreen mode Exit fullscreen mode

The easiest way to get partnums:

oncheck -pt database_name:table_name
Enter fullscreen mode Exit fullscreen mode

1.4 Displaying the Page Containing a Specific Row

First, find the rowid of the concerned row:

select *, rowid from your_table where __condition__;
Enter fullscreen mode Exit fullscreen mode

Then display the page:

oncheck -pp database:table_name rowid
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

Generate data and load it:

./generate_data.py
dbaccess test1 -
load from 'data.unl' insert into t1;
Enter fullscreen mode Exit fullscreen mode

2.3 Finding Pages Based on Physical Address

Get the physical address:

oncheck -pt test1:t1
Enter fullscreen mode Exit fullscreen mode

Data part:

Extents                       
Logical Page  Physical Page  Size Physical Pages
0             4:57           32   32
32            4:93           96   96
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  • Display the data part: 
oncheck -pP 4 58
Enter fullscreen mode Exit fullscreen mode

(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....
Enter fullscreen mode Exit fullscreen mode
  • Display the index part: 
oncheck -pP 4 54
Enter fullscreen mode Exit fullscreen mode 
[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                                        ................
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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....
Enter fullscreen mode Exit fullscreen mode

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                 ........
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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....
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  • 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.
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode
  • View the read content. 
vi /tmp/1.txt
%!xxd
Enter fullscreen mode Exit fullscreen mode

The highlighted part is the beginning of the first three lines, starting from integers 0, 1, 2.

Image description

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  • 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.

Top comments (0)

Read next

eugene-zimin profile image

Database per Service as a Design Pattern

Eugene Zimin -

vyan profile image

Commonly Used Databases with Node.js and Express

Vishal Yadav -

domfive profile image

MySQL vs Cassandra: Everything You Need to Know

Dom | Five.Co -

karenpayneoregon profile image

Creating mocked data for EF Core using Bogus and more

Karen Payne -