ENV_GET_INSTANCE_CODE_LEVELS table function - Return architectural level

The ENV_GET_INSTANCE_CODE_LEVELS table function returns the code level for the instance, members, and cluster caching facilities.

Authorization

One of the following authorizations is required:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

In a non-restrictive database, SELECT privilege is granted to PUBLIC when the routine is automatically created.

Syntax

The schema is SYSPROC.

Information returned

Table 1. Information returned by the ENV_GET_INSTANCE_CODE_LEVELS routine
Column name	Data type	Description
RECORD_TYPE	VARCHAR(10)	The record type, which specifies if rolling updates information is specific to an instance, cluster caching facility, or member.
ID	SMALLINT	Host as represented in db2nodes.cfg (0 for the INSTANCE level record).
ARCHITECTURE_LEVEL_DISK	VARCHAR(50)	Architecture level value stored on disk.
ARCHITECTURE_LEVEL_MEMORY	VARCHAR(50)	Architecture level value stored in memory.
SECTION_LEVEL_DISK	VARCHAR(50)	Section level stored on disk.
SECTION_LEVEL_MEMORY	VARCHAR(50)	Section level stored in memory.
CODE_LEVEL_DISK	VARCHAR(50)	Code level stored on disk.
CODE_LEVEL_MEMORY	VARCHAR(50)	Code level stored in memory.
STATE	VARCHAR(60)	Rolling Updates state: NONE NOT COMPUTED OFFLINE UPDATE INITIATED ONLINE UPDATE INITIATED
LAST_UPDATED	TIMESTAMP	Timestamp indicating when this member was last updated.
ARCHITECTURE_LEVEL_DISK_PACKED	VARCHAR(8) FOR BIT DATA	Packed format of the architecture level stored on disk.
ARCHITECTURE_LEVEL_MEMORY_PACKED	VARCHAR(8) FOR BIT DATA	Packed format of the architecture level stored in memory.
SECTION_LEVEL_DISK_PACKED	VARCHAR(8) FOR BIT DATA	Packed format of the section level stored on disk.
SECTION_LEVEL_MEMORY_PACKED	VARCHAR(8) FOR BIT DATA	Packed format of the section level stored in memory.
CODE_LEVEL_DISK_PACKED	VARCHAR(8) FOR BIT DATA	Packed format of the code level stored on disk.
CODE_LEVEL_MEMORY_PACKED	VARCHAR(8) FOR BIT DATA	Packed format of the code level stored in memory.

Example

Simulation of an online rolling update from Version 10.5 Fix Pack 1 to Fix Pack 2 for an instance named "INST1". It is assumed that Fix Pack 2 introduced a new architecture level but the section level remained unchanged since Version 10.5.

The instance starts in a homogeneous state. All members are identical.

SELECT RECORD_TYPE, ID, SUBSTR(ARCHITECTURE_LEVEL_DISK,1,28) 
   AS ARCHITECTURE_LEVEL, SUBSTR(SECTION_LEVEL_DISK,1,26) 
   AS SECTION_LEVEL, SUBSTR(CODE_LEVEL_DISK,1, 28) 
   AS CODE_LEVEL, LAST_UPDATED 
   FROM TABLE (SYSPROC.ENV_GET_INSTANCE_CODE_LEVELS())

This query returns the following output:

RECORD_TYPE ID     ARCHITECTURE_LEVEL           SECTION_LEVEL              ...
----------- ------ ---------------------------- -------------------------- ...
INSTANCE         - V:10 R:5 M:0 F:1 I:0 SB:0    -                          ...
MEMBER           0 V:10 R:5 M:0 F:1 I:0 SB:0    V:10 R:5 M:0 F:0 I:0 SB:0  ...
MEMBER           1 V:10 R:5 M:0 F:1 I:0 SB:0    V:10 R:5 M:0 F:0 1:0 SB:0  ...
MEMBER           2 V:10 R:5 M:0 F:1 I:0 SB:0    V:10 R:5 M:0 F:0 I:0 SB:0  ...
CF             128 V:10 R:5 M:0 F:1 I:0 SB:0    -                          ...
CF             129 V:10 R:5 M:0 F:1 I:0 SB:0    -                          ...

Query output (continued):

...CODE_LEVEL                    LAST_UPDATED
...----------------------------- --------------------------
...V:10 R:5 M:0 F:1 I:0 SB:0    2012-10-06-19.12.00.000000  
...V:10 R:5 M:0 F:1 I:0 SB:0    2012-10-06-19.12.00.000000
...V:10 R:5 M:0 F:1 I:0 SB:0    2012-10-06-19.12.00.000000
...V:10 R:5 M:0 F:1 I:0 SB:0    2012-10-06-19.12.00.000000
...V:10 R:5 M:0 F:1 I:0 SB:0    2012-10-06-19.12.00.000000
...V:10 R:5 M:0 F:1 I:0 SB:0    2012-10-06-19.12.00.000000

     6 record(s) selected.

The user updates two cluster caching facilities (CFs) using the following command on both CFs:

./installFixPack -p FP2_install_path INST1

Query output:

RECORD_TYPE ID     ARCHITECTURE_LEVEL           SECTION_LEVEL               ...
----------- ------ ---------------------------- --------------------------- ...
INSTANCE         - V:10 R:5 M:0 F:1 I:0 SB:0    -                           ...
MEMBER           0 V:10 R:5 M:0 F:1 I:0 SB:0    V:10 R:5 M:0 F:0 I:0 SB:0   ...
MEMBER           1 V:10 R:5 M:0 F:1 I:0 SB:0    V:10 R:5 M:0 F:0 I:0 SB:0   ...
MEMBER           2 V:10 R:5 M:0 F:1 I:0 SB:0    V:10 R:5 M:0 F:0 I:0 SB:0   ...
CF             128 V:10 R:5 M:0 F:2 I:0 SB:0    -                           ...
CF             129 V:10 R:5 M:0 F:2 I:0 SB:0    -                           ...

Query output (continued):

... CODE_LEVEL                  LAST_UPDATED
... --------------------------- --------------
... V:10 R:5 M:0 F:1 I:0 SB:0   2012-10-06-19.12.00.000000
... V:10 R:5 M:0 F:1 I:0 SB:0   2012-10-06-19.12.00.000000
... V:10 R:5 M:0 F:1 I:0 SB:0   2012-10-06-19.12.00.000000
... V:10 R:5 M:0 F:1 I:0 SB:0   2012-10-06-19.12.00.000000
... V:10 R:5 M:0 F:2 I:0 SB:0   2012-10-06-19.23.44.000000
... V:10 R:5 M:0 F:2 I:0 SB:0   2012-10-06-19.22.49.000000

      6 record(s) selected.

The three members are subsequently moved to Fix Pack 2 using the same installFixPack command:

./installFixPack -p FP2_install_path INST1

Query output:

RECORD_TYPE ID     ARCHITECTURE_LEVEL           SECTION_LEVEL               ...
----------- ------ ---------------------------- --------------------------- ...
INSTANCE         - V:10 R:5 M:0 F:1 I:0 SB:0    -                           ...
MEMBER           0 V:10 R:5 M:0 F:2 I:0 SB:0    V:10 R:5 M:0 F:0 I:0 SB:0   ...
MEMBER           1 V:10 R:5 M:0 F:2 I:0 SB:0    V:10 R:5 M:0 F:0 I:0 SB:0   ...
MEMBER           2 V:10 R:5 M:0 F:2 I:0 SB:0    V:10 R:5 M:0 F:0 I:0 SB:0   ...
CF             128 V:10 R:5 M:0 F:2 I:0 SB:0    -                           ...
CF             129 V:10 R:5 M:0 F:2 I:0 SB:0    -                           ...

Query output (continued):

... CODE_LEVEL                  LAST_UPDATED
... --------------------------- --------------
... V:10 R:5 M:0 F:1 I:0 SB:0   2012-10-06-19.12.00.000000  
... V:10 R:5 M:0 F:2 I:0 SB:0   2012-10-06-19.29.39.000000
... V:10 R:5 M:0 F:2 I:0 SB:0   2012-10-06-19.30.34.000000
... V:10 R:5 M:0 F:2 I:0 SB:0   2012-10-06-19.31.25.000000
... V:10 R:5 M:0 F:2 I:0 SB:0   2012-10-06-19.23.44.000000
... V:10 R:5 M:0 F:2 I:0 SB:0   2012-10-06-19.22.49.000000

      6 record(s) selected.

The cluster is then committed at the new level of code, using the following command:

./installFixPack -commit_new_level

Query output:

RECORD_TYPE ID     ARCHITECTURE_LEVEL           SECTION_LEVEL               ...
----------- ------ ---------------------------- --------------------------- ...
INSTANCE         - V:10 R:5 M:0 F:2 I:0 SB:0    -                           ...
MEMBER           0 V:10 R:5 M:0 F:2 I:0 SB:0    V:10 R:5 M:0 F:0 I:0 SB:0   ...
MEMBER           1 V:10 R:5 M:0 F:2 I:0 SB:0    V:10 R:5 M:0 F:0 I:0 SB:0   ...
MEMBER           2 V:10 R:5 M:0 F:2 I:0 SB:0    V:10 R:5 M:0 F:0 I:0 SB:0   ...
CF             128 V:10 R:5 M:0 F:2 I:0 SB:0    -                           ...
CF             129 V:10 R:5 M:0 F:2 I:0 SB:0    -                           ...

Query output (continued):

... CODE_LEVEL                  LAST_UPDATED
... --------------------------- --------------
... V:10 R:5 M:0 F:2 I:0 SB:0   2012-10-06-19.36.40.000000
... V:10 R:5 M:0 F:2 I:0 SB:0   2012-10-06-19.29.39.000000
... V:10 R:5 M:0 F:2 I:0 SB:0   2012-10-06-19.30.34.000000
... V:10 R:5 M:0 F:2 I:0 SB:0   2012-10-06-19.31.25.000000
... V:10 R:5 M:0 F:2 I:0 SB:0   2012-10-06-19.23.44.000000
... V:10 R:5 M:0 F:2 I:0 SB:0   2012-10-06-19.22.49.000000

      6 record(s) selected.