DDL and Catalog Changes
The information about your database objects are stored in the Db2 catalog as we explained in our past blog. You manipulate your database objects using the DDL (Data Definition Language):
- If you need to create a table for instance, you use the CREATE TABLE DDL,
- if you want to alter its definition, you use ALTER TABLE DDL,
- and if you want to remove it, DROP DDL is the statement;
and similarly with other objects.
Have you ever wondered what happens under the hood when you execute DDL? In fact, after you execute a DDL statement, Db2 runs certain DML operations into the catalog. Db2 will perform inserts, updates, or deletes to the catalog tables.
Normally, this is hidden from a user, but you can see these operations by running a log analysis tool, or even just DSN1LOGP the output of which is however rather difficult to read.
Understanding the sequence of DML operations that Db2 executes when applying a DDL, can help you to understand what information is being recorded in the catalog. Let's take a look at a simple DDL statement and activities that happen under the hood.
Assume, we just issue the following DDL.
CREATE TABLE MY_DUMMY_TABLE (COLUMN1 VARCHAR(20));
This statement will create a dummy table in an implicit tablespace of an implicit database. The table will have just a single varchar column.
I ran my log analysis tool, and below are some highlights that I extracted from the log as a consequence of the prior DDL.
- The very first action Db2 did was taking a sequence number for an implicit database. You can see the following update to the SYSSEQUENCES table for a sequence called SYSIBM.DSNSEQ_IMPLICITDB. The assigned value for my implicit database will be 8981, the database becomes DSN08981.
UPDATE "SYSIBM".SYSSEQUENCES
SET MAXASSIGNEDVAL = 8981
WHERE "SCHEMA" = 'SYSIBM'
AND "NAME" = 'DSNSEQ_IMPLICITDB'
;
- Db2 then creates an implicit tablespace called MYRDUMMY in the database DSN08981. From the insert below into SYSTABLESPACE, you can see that my tablespace type is 'G', which means partition by growth tablespace, and that it was created by Db2 12 (RELCREATED='Q')
INSERT INTO "SYSIBM".SYSTABLESPACE
( "NAME" , CREATOR , DBNAME , DBID , "OBID" , "PSID" , BPOOL ,
PARTITIONS , LOCKRULE , PGSIZE , ERASERULE , STATUS ,
IMPLICIT , NTABLES , NACTIVE , DSETPASS , CLOSERULE , SPACE ,
IBMREQD , ROOTNAME , ROOTCREATOR , SEGSIZE , CREATEDBY ,
STATSTIME , "LOCKMAX" , "TYPE" , CREATEDTS , ALTEREDTS ,
ENCODING_SCHEME , SBCS_CCSID , DBCS_CCSID , MIXED_CCSID ,
MAXROWS , LOCKPART , LOG , NACTIVEF , "DSSIZE" ,
OLDEST_VERSION , CURRENT_VERSION , AVGROWLEN , SPACEF ,
MAXPARTITIONS , CREATORTYPE , INSTANCE , "CLONE" ,
RELCREATED , MEMBER_CLUSTER , ORGANIZATIONTYPE , HASHSPACE ,
HASHDATAPAGES , PAGENUM , INSERTALG , PQTY , STORTYPE ,
STORNAME , VCATNAME , FREEPAGE , PCTFREE , COMPRESS ,
GBPCACHE , TRACKMOD , SECQTYI , PCTFREE_UPD ,
PCTFREE_UPD_CALC , COMPRESSRATIO )
VALUES
( 'MYRDUMMY' , ‘MYUSER' , 'DSN08981' , 11072 , 0 , 0 ,
'BP2 ' , 1 , 'R' , 4 , 'N' , 'T' , 'Y' , 0 , 0 ,
' ' , 'Y' , 0 , 'H' , '' , ' ' , 32 ,
‘MYUSER' , '0001-01-01-00.00.00.000000' , -1 , 'G' ,
'2024-02-28-05.14.36.041552' , '2024-02-28-05.14.36.041552' ,
'E' , 1027 , 4396 , 5035 , 255 , ' ' , 'Y' , -0.1E+01 ,
4194304 , 0 , 0 , -1 , -0.1E+01 , 256 , ' ' , 1 , 'N' , 'Q' ,
' ' , ' ' , 0 , 0 , 'A' , 0 , -1 , 'I' , 'SYSDEFLT' ,
'DSNDTGP' , 0 , 5 , ' ' , ' ' , ' ' , -1 , 0 , -1 , -1 )
;
- As we learned my tablespace is PBG. Here I see an insert into the SYSTABLEPART catalog table that records physical information about my first partition. For instance, you can see that the DSSIZE used was 4GB.
INSERT INTO "SYSIBM".SYSTABLEPART
( "PARTITION" , TSNAME , DBNAME , IXNAME , IXCREATOR , PQTY ,
SQTY , STORTYPE , STORNAME , VCATNAME , CARD , FARINDREF ,
NEARINDREF , PERCACTIVE , PERCDROP , IBMREQD , LIMITKEY ,
FREEPAGE , PCTFREE , CHECKFLAG , CHECKRID , SPACE ,
COMPRESS , PAGESAVE , STATSTIME , GBPCACHE , CHECKRID5B ,
TRACKMOD , EPOCH , SECQTYI , CARDF , IPREFIX , ALTEREDTS ,
SPACEF , DSNUM , EXTENTS , LOGICAL_PART , LIMITKEY_INTERNAL ,
OLDEST_VERSION , CREATEDTS , AVGROWLEN , FORMAT ,
REORG_LR_TS , RELCREATED , HASHSPACE , HASHDATAPAGES ,
RBA_FORMAT , PCTFREE_UPD , PCTFREE_UPD_CALC , "TYPE" ,
PAGENUM , BPOOL , PGSIZE , "DSSIZE" , MEMBER_CLUSTER ,
COMPRESSRATIO )
VALUES
( 1 , 'MYRDUMMY' , 'DSN08981' , '' , '' , -1 , -1 , 'I' ,
'SYSDEFLT' , 'DSNDTGP' , -1 , -1 , -1 , -1 , -1 , 'N' , '' ,
0 , 5 , ' ' , X'40404040' , -1 , ' ' , 0 ,
'0001-01-01-00.00.00.000000' , ' ' , X'4040404040' , ' ' , 0 ,
-1 , -0.1E+01 , 'I' , '0001-01-01-00.00.00.000000' ,
-0.1E+01 , -1 , -1 , 1 , X'' , 0 ,
'2024-02-28-05.14.36.041552' , -1 , 'R' ,
'0001-01-01-00.00.00.000000' , 'Q' , 0 , 0 , 'U' , 0 , -1 ,
'G' , 'A' , 'BP2 ' , 4 , 4194304 , ' ' , -1 )
;
- After we finished with the tablespace, Db2 finally inserts the table information into the SYSTABLES catalog table. You can see the name of the table, its tablespace, database, and so on. Please be aware of the TYPE='T', which means this is a table. Views are also recorded in SYSTABLES. Other useful information is the number of columns (COLCOUNT = 1), and number of children (CHILDREN), parents (PARENTS), if the table is part of the referential integrity. In my case both are zeros.
INSERT INTO "SYSIBM".SYSTABLES
( "NAME" , CREATOR , "TYPE" , DBNAME , TSNAME , DBID , "OBID" ,
COLCOUNT , EDPROC , VALPROC , CLUSTERTYPE , CLUSTERRID ,
CARD , NPAGES , PCTPAGES , IBMREQD , REMARKS , PARENTS ,
CHILDREN , KEYCOLUMNS , RECLENGTH , STATUS , KEYOBID ,
"LABEL" , CHECKFLAG , CHECKRID , AUDITING , CREATEDBY ,
LOCATION , TBCREATOR , TBNAME , CREATEDTS , ALTEREDTS ,
DATACAPTURE , RBA1 , RBA2 , PCTROWCOMP , STATSTIME , CHECKS ,
CARDF , CHECKRID5B , ENCODING_SCHEME , TABLESTATUS ,
NPAGESF , SPACEF , AVGROWLEN , RELCREATED , NUM_DEP_MQTS ,
VERSION , PARTKEYCOLNUM , SPLIT_ROWS , SECURITY_LABEL ,
OWNER , APPEND , OWNERTYPE , CONTROL , VERSIONING_SCHEMA ,
VERSIONING_TABLE , HASHKEYCOLUMNS , ARCHIVING_SCHEMA ,
ARCHIVING_TABLE , STATS_FEEDBACK , REGENERATETS )
VALUES
( 'MY_DUMMY_TABLE' , ‘MYUSER' , 'T' , 'DSN08981' , 'MYRDUMMY' ,
11072 , 0 , 1 , '' , '' , ' ' , 0 , -1 , -1 , -1 , ' ' , '' ,
0 , 0 , 0 , 0 , ' ' , 0 , '' , ' ' , X'40404040' , ' ' ,
‘MYUSER' , '' , '' , '' , '2024-02-28-05.14.36.041552' ,
'2024-02-28-05.14.36.041552' , ' ' , X'00000000000000000000' ,
X'00000000000000000000' , -1 , '0001-01-01-00.00.00.000000' ,
0 , -0.1E+01 , X'4040404040' , 'E' , '' , -0.1E+01 ,
-0.1E+01 , -1 , 'Q' , 0 , 0 , 0 , ' ' , ' ' , ‘MYUSER' ,
'N' , ' ' , ' ' , '' , '' , 0 , '' , '' , 'Y' ,
'2024-02-28-05.14.36.041552530273' )
;
- My table has just one column so there is a single insert into the SYSCOLUMNS table for that column. From the insert you can see the name, type, whether it is nullable and more.
INSERT INTO "SYSIBM".SYSCOLUMNS
( "NAME" , TBNAME , TBCREATOR , COLNO , COLTYPE , LENGTH ,
SCALE , "NULLS" , COLCARD , HIGH2KEY , LOW2KEY , UPDATES ,
IBMREQD , REMARKS , "DEFAULT" , KEYSEQ , FOREIGNKEY ,
FLDPROC , "LABEL" , STATSTIME , DEFAULTVALUE , COLCARDF ,
COLSTATUS , LENGTH2 , DATATYPEID , SOURCETYPEID ,
TYPESCHEMA , TYPENAME , CREATEDTS , STATS_FORMAT ,
PARTKEY_COLSEQ , PARTKEY_ORDERING , ALTEREDTS , "CCSID" ,
HIDDEN , RELCREATED , CONTROL_ID , XML_TYPEMOD_ID , "PERIOD" ,
GENERATED_ATTR , HASHKEY_COLSEQ , ENCODING_SCHEME )
VALUES
( 'COLUMN1' , 'MY_DUMMY_TABLE' , ‘MYUSER' , 1 , 'VARCHAR ' ,
20 , 0 , 'Y' , -1 , X'' , X'' , 'Y' , 'N' , '' , 'Y' , 0 ,
'M' , 'N' , '' , '0001-01-01-00.00.00.000000' , '' ,
-0.1E+01 , ' ' , 0 , 448 , 0 , 'SYSIBM' , 'VARCHAR' ,
'2024-02-28-05.14.36.041552' , ' ' , 0 , ' ' ,
'2024-02-28-05.14.36.041552' , 5035 , 'N' , 'Q' , 0 , 0 ,
' ' , ' ' , 0 , 'E' )
;
- Internally, Db2 uses internal IDs to identify the objects. In this update to SYSTABLESPACE you will see the object ID for the tablespace of 4 and page set ID of 5. Db2 will use these ids internally, such as when it records operations to the log. DB2 will not refer to my objects via their names.
UPDATE "SYSIBM".SYSTABLESPACE
SET "OBID" = 4
, "PSID" = 5
WHERE DBNAME = 'DSN08981'
AND "NAME" = 'MYRDUMMY'
;
- Similarly, Db2 assigns the object id of 6 to my dummy table as you can see in this update to SYSTABLES catalog table
UPDATE "SYSIBM".SYSTABLES
SET "OBID" = 6
, IBMREQD = 'H'
, RECLENGTH = 31
, RBA1 = X'00DEB7EB1A6558463000'
, RBA2 = X'00DEB7EB1A6558463000'
WHERE CREATOR = ‘MYUSER'
AND "NAME" = 'MY_DUMMY_TABLE'
;
- Now my tablespace is marked as available (STATUS=1) with 1 table and is ready to use.
UPDATE "SYSIBM".SYSTABLESPACE
SET STATUS = 'A'
, NTABLES = 1
WHERE DBNAME = 'DSN08981'
AND "NAME" = 'MYRDUMMY'
;
- And finally, don't forget about the authorizations. The SYSTABAUTH table has been inserted with a row that shows that my userid has several privileges with the GRANT option. That is because I am the owner of the object. I posses the following privileges: ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, UNLOAD.
INSERT INTO "SYSIBM".SYSTABAUTH
( GRANTOR , GRANTEE , GRANTEETYPE , DBNAME , SCREATOR ,
STNAME , TCREATOR , TTNAME , AUTHHOWGOT , TIMESTAMP ,
DATEGRANTED , TIMEGRANTED , UPDATECOLS , ALTERAUTH ,
DELETEAUTH , INDEXAUTH , INSERTAUTH , SELECTAUTH ,
UPDATEAUTH , IBMREQD , GRANTEELOCATION , LOCATION , "COLLID" ,
CONTOKEN , CAPTUREAUTH , REFERENCESAUTH , REFCOLS ,
GRANTEDTS , TRIGGERAUTH , GRANTORTYPE , SYS_START , SYS_END ,
TRANS_START , UNLOADAUTH )
VALUES
( ‘MYUSER' , ‘MYUSER' , ' ' , '' , ‘MYUSER' ,
'MY_DUMMY_TABLE' , ‘MYUSER' , 'MY_DUMMY_TABLE' , ' ' ,
X'C5D6E6D2D9E6F7F4F7F3D7E3' , '240228' , '05143611' , ' ' ,
'G' , 'G' , 'G' , 'G' , 'G' , 'G' , 'N' , '' , '' , '' ,
X'4040404040404040' , ' ' , 'G' , ' ' ,
'2024-02-28-05.14.36.113409' , 'G' , ' ' , DEFAULT , DEFAULT ,
DEFAULT , 'G' )
;
This was just a very simplistic example, but we have seen that a single DDL CREATE TABLE statement involves several DML operations into the Db2 catalog tables. In this cases particularly - SYSSEQUENCES, SYSTABLESPACE, SYSTABLEPART, SYSTABLES, SYSCOLUMNS, and SYSTABAUTH.
Hopefully, I raised your attention and now you are eager to run your own DDL and observe what happens under the hood. Exercises like this helped me to understand what is going on when I execute a DDL. It can also help you in recovery scenarios to see what all gets involved in case you need to recover from accidental drops for instance.