Implicit / Explicit objects – why SET CURRENT RULES makes a difference
All of us have an opinion about IMPLICITLY created objects in a Db2 subsystem – it’s like a love/hate relationship. The ability for Db2 to automatically create the “needed” objects has been around for a while and this capability definitely has some pro’s and con’s depending on the specific organization and requirements.
Most Db2 sites have a naming convention in place for various reasons. For example, a table name must start with “T”, a tablespace name must start with “S”, the second byte of a database name has to reflect the environment like test, system-test, production etc.
This is one reason why many Db2 shops like to enforce a naming convention – when things get hot and chaotic, it’s really convenient to identify the object type when browsing through the Db2 address spaces, reading log output, etc. Another convenient component is the ability to do wildcarding for utilities, predicates when querying the Db2 catalog, etc.
So why is it convenient to use IMPLICITLY created objects ? Well, when using EXPLICITLY created objects, there can be many objects to create until Db2 accepts the object structure to be “complete”. For a unique constraint, you need a supporting index. For a table, you need a tablespace and database. When the table has a LOB column, you need all the AUX objects and this gets even more complicated when the table is a PBR (Partition By Range). So let’s have a look at a “relatively simple” table with one LOB column and 6 partitions:
For this table, you need to:
- Create DATABASE in case it doesn’t exist already.
- Create TABLESPACE with all the needed attributes – and in this case 6 partitions.
- Create TABLE specifying the partition LIMITKEYS.
- Create 6 AUX tables (one for each partition), since this is a PBR table with 6 partitions and a LOB column.
- Create an AUX tablespace for each AUX table.
- Create 6 associated indexes for the LOB object.
So for ONE table in ONE tablespace having ONE LOB-column, you need THREE objects per partition – a total of EIGHTEEN additional objects, and let’s admit it – it’s easy to lose control getting your object to be in a complete and operational status. This is where IMPLICIT objects can become very handy, simply let Db2 do the job. As already mentioned, it comes for a price since you don’t have full control of the object names and you might have a challenge using wildcarding. On a side note, this is where LISTDEF processing can assist by using INCLUDE parameter.
If you settle with exploiting IMPLICIT objects, there’s one register you need to be aware of, and it has two options – each behaving very differently:
SET CURRENT RULES = ‘STD’
SET CURRENT RULES = ‘DB2’
The default value is ‘DB2’, but you probably want to use ‘STD’ since the behavior is very different – depending on ONE important “minor” component, whether or not the base tablespace for the table is explicitly created. The CURRENT RULES diagram below has been copied for the IBM Db2 documentation for your reference.
|
Statement
|
What is affected
|
Details in topic
|
|---|---|---|
| ALTER TABLE | Enforcement of check constraints added.
Default value of the delete rule for referential constraints. Whether Db2 creates LOB table spaces, auxiliary tables, and indexes on auxiliary tables for added LOB columns. Whether Db2 creates an index for an added ROWID column that is defined with GENERATED BY DEFAULT. |
ALTER TABLE |
| CREATE TABLE | Default value of the delete rule for referential constraints.
Whether Db2 creates LOB table spaces, auxiliary tables, and indexes on auxiliary tables for LOB columns if the table is explicitly created. Whether Db2 creates an index for a ROWID column that is defined with GENERATED BY DEFAULT if the table is explicitly created. |
CREATE TABLE |
| GRANT | Granting privileges to yourself. | GRANT |
| REVOKE | Revoking privileges from authorization IDs | REVOKE |
First, only a CREATE TABLE statement is executed without using SET CURRENT RULES, so Db2 is using the value ‘DB2’.
|
CREATE TABLE IDUG19_PBRLOB_EXPLICIT (COUNTRY VARCHAR(50) ,CITY VARCHAR(30) ,ZIPCODE CHARACTER(10) ,RESIDENTS INTEGER ,LOBDATA CLOB(2M) INLINE LENGTH 0 ,LOB_ROWID ROWID NOT NULL GENERATED ALWAYS ) LOGGED DSSIZE 4G BUFFERPOOL BP2 NOT VOLATILE CARDINALITY PARTITION BY RANGE (COUNTRY NULLS LAST ASC ) ( PARTITION 1 ENDING ('G') INCLUSIVE , PARTITION 2 ENDING ('J' ) INCLUSIVE , PARTITION 3 ENDING ('M') INCLUSIVE , PARTITION 4 ENDING ('ST') INCLUSIVE , PARTITION 5 ENDING ('T') INCLUSIVE , PARTITION 6 ENDING ('ZZZ' ) INCLUSIVE ) |
Note that the IN DATABASE.TABLESPACE clause isn’t used so Db2 will create an IMPLICIT database (in this case DSB05572 was “assigned”). The IMPLICIT tablespace created by Db2 was assigned the name HSBCIMP, which in this specific case is the same name as the table – potentially violating your naming convention, but all 18 needed AUX objects, as well as the tablespace and database were created implicitly.
Now, let’s try to create the tablespace EXPLICITLY as HSBCIMP2 in the EXPLICIT database HSBCDB. The CREATE TABLE statement is identical to the previous case, except that the IN HSBCDB.HSBCIMP2 clause is specified so Db2 won’t create an implicit tablespace. In the first case we will use SET CURRENT RULES = ‘DB2’ (or we could eliminate this statement since it’s the default. Here’s the objects residing in Db2 – only the tablespace and table.
So basically the table is in an incomplete status since all the AUX objects are missing and you have your day planned for you.
If we instead of using SET CURRENT RULES = ‘DB2” decide to use SET CURRENT RULES = ‘STD’, it’s a complete different environment (note that the tablespace created EXPLICITLY in this use case is HSBCIMP3 and the tablename is also HSBCIMP3):
The conclusion is, if you create the tablespace explicitly, the use of SET CURRENT RULES really matters – and you have to make the choice based on your naming convention and requirements.