SQL default index location

Products

• IDMS

Issue/Introduction

When an SQL table is created, a default index is also created for the table. If no other location mode is made available to the table by either creating a CALC key for it (CALC) or making it the referencing table of a clustered constraint (VIA), then by default, the location mode of the table will be VIA the default index.

This article describes how to view the physical location of the table's rows and the SR8s in the default index.

Environment

Release : All supported releases
Component: SQL Option

Resolution

To determine what pages each row of the table is on, issue a SELECT and include the ROWID.

SELECT ROWID, * FROM SQL.T1;
*+
*+                ROWID      C1
*+                -----      --
*+  X'03D4FD0100000008'       1
*+  X'03D4FD0300000008'       2
*+  X'03D4FD0400000008'       3
*+  X'03D4FD0500000008'       4
*+  X'03D4FD0600000008'       5
*+  X'03D4FD0700000008'       6
*+
*+ 6 rows processed

The dbkey is the first four bytes of the ROWID. Note that in the above display, every row is on page x'03D4FD', or 251,133 in decimal.

To determine where the internal system records of the default index are, run a PRINT INDEX. To do this, first issue the following SELECT to determine the TABLEID of the table:

SELECT TABLEID FROM SYSTEM.TABLE WHERE NAME = 'T1' AND SCHEMA='SQL';
*+
*+ TABLEID
*+ -------
*+    1025
*+
*+ 1 row processed

To confirm this above number, display the index.

DISPLAY INDEX ROWID01025 ON SQL.T1;
*+ Status = 0        SQLSTATE = 00000
*+   CREATE UNIQUE INDEX ROWID01025
*+       ON SQL.T1
*+       DATE CREATED 2022-12-12-09.09.14.523249
*+       UNCOMPRESSED
*+       IN SQLWORK.SQL-AREA-1
*+       INDEX BLOCK CONTAINS 10 KEYS DISPLACEMENT IS 2 PAGES
*+       CLUSTERED
*+       INDEX ID 2
*+       ;

Then print the index:

PRINT INDEX ROWID01025 SEGMENT SQLWORK TABLE SQL.T1;
*+ Status = 0        SQLSTATE = 00000
*+ SET=ROWID01025
*+      OWNER=SR7              PAGE GROUP=0     RECORDS PER PAGE=255
*+       ODBK=03D4FD02 SR8 N03D4FF01 SR8 P03D4FF01 ASC CUSH=4   DBK TKL=3
*+     MEMBER=T1               PAGE GROUP=0     RECORDS PER PAGE=255
*+ L0   03D4FF01  NUME=6                 U=FFFFFFFF N=03D4FD02 P=03D4FD02
*+       RECL=60    SPA=4176
*+               03D4FD01  03D4FD03  03D4FD04  03D4FD05  03D4FD06  03D4FD07
*+ TOTAL SR8=1

The above display shows that the dbkey of the SR7 is x'03D4FD02', so it is on the same page as the rows.

Also there is one SR8, and its dbkey is x'03D4FF01', so it is on page 251,135.