Explore topic-wise InterviewSolutions in .

If you want to GENERATE a detailed report containing all of the explain data, you can execute the db2exfmt command in a sequence shown as follows:

Enable the Explain FACILITY by setting the current explain mode to EXPLAIN. 

This will configure the CURRENT EXPLAIN MODE special REGISTER so that the EXPLAIN the facility can capture the explain information without executing the SQL statement:

1. SET CURRENT EXPLAIN MODE EXPLAIN;

2. Run the actual SQL statement in the same SESSION:

     db2 -tvf employee.ddl > employee.log

3. Disable the Explain facility by setting the current explain mode to NO:

     SET CURRENT EXPLAIN MODE NO

4. Run the db2exfmt tool:

    db2exfmt -d SAMPLE -g TIC -w -1 -s % -n % -# 0 -o employee.db2exfmt.out

First, MDC enables a table to be physically clustered on more than one key, or dimension, simultaneously. As a result query performance is improved where there is a clustering of one or more specified dimensions of a table. CONSEQUENTLY not only will these QUERIES access only those PAGES having records with the correct dimension values, but these qualifying pages will ALSO be grouped into blocks, or extents.

Second, although a table with a clustering index can become unclustered over time, in most cases an MDC table is able to maintain and guarantee its clustering overall dimensions automatically and continuously.

Third, in MDC the clustering INDEXES are block-based. These indexes are much smaller than regular record-based indexes, hence they take up much less disk space and are faster to scan.

• ACTIVITIES: Records activity events that occur as the database is used.
• BUFFERPOOLS: Records a buffer pool event when the last application disconnects from the database.
• CONNECTIONS: Records a connection event when an application disconnects from the database.
• DATABASE: Records a database event when the last application disconnects from the database.
• DEADLOCKS: Records a deadlock event whenever a deadlock occurs. However, this monitor is depreciated and the LOCKING event should be used instead.
  • DEADLOCKS WITH DETAILS
  • DEADLOCKS WITH DETAILS HISTORY
  • DEADLOCKS WITH DETAILS HISTORY VALUES
• STATEMENTS: Records a STATEMENT event whenever a SQL statement finishes running.
• STATISTICS: Record statistics events that occur when USING the database.
• TABLES: Records a tabled event for each active table when the last application disconnects from the database.
• TABLESPACES: Records a tablespace event for each tablespace when the last application disconnects from the database.
• THRESHOLD VIOLATIONS: Records threshold violation events that occur when using the database.
• TRANSACTIONS: Records a transaction event whenever a transaction completes. 
• UNIT OF WORK: Records events when a unit of work completes.
• LOCKING: Records a lock event whenever a lock-related event occurs, such as lock TIMEOUT, lock waits, and deadlocks.
• PACKAGE CACHE: Records events related to the package cache statement.
• CHANGE HISTORY: Records events related to changes to the DB CFG, DBM CFG, and DB2 registry settings, track the execution of utilities such as LOAD, online backup, REORG, and RUNSTATS.

The monitoring elements in Db2 are as follows :

• Counter: This records a precise count of the NUMBER of times an activity or event has occurred in the database system. For example, the pool_col_l_reads monitor element records the number of column-organized page requests from the buffer pool for regular and LARGE tablespaces.
• Gauge: This represents the current value for a monitoring element based on how MUCH of something is happening or is used. For example, the num_locks_held monitoring element shows how many locks are being currently HELD in the database.
• Watermark: This represents the HIGHEST value for a given monitoring element. For example, the connections_top monitoring element shows the highest number of simultaneous connections to the database since the database's activation.
• Text: This represents the text value that monitoring elements report. For example, the bp_name monitoring element states the name of the buffer pool.
• Timestamp: This monitor element represents the time that an event happened. 

For example, the last_backup monitoring element lists the date and time that thelatest database backup was completed.

To CREATE and MAINTAIN an MQT is a two-step process.

First, we NEED to create an MQT based on the needs of the business logic, and then refresh the data in the MQT table.

Below is an example

• agent_stack_sz

Agent stack size CONFIGURATION parameter. This parameter DETERMINES the MEMORY that is ALLOCATED by Db2 for each agent thread stack.

• aslheapsz

Application support layer heap size configuration parameter.The application support layer heap represents a COMMUNICATION buffer between the local application and its associated agent. This buffer is allocated as shared memory by each database manager agent that is started.

• audit_buf_sz

Audit buffer size configuration parameter. This parameter specifies the size of the buffers used when you audit the Db2instance.

The four types of Db2 REGISTRY variables are:

• Db2 global-level profile registry: All server-wide environment variable settings reside in this registry. These settings are APPLICABLE to all the instances that pertain to a particular version of DB2.
• Db2 instance-level profile registry: The environment variable settings for a particular instance are stored in this registry. The VALUES defined in this profile registry override any corresponding settings in the global-level profile registry.
• Db2 instance node-level profile registry: The environment variable settings that are specific to a node in a MASSIVELY Parallel Processing (MPP) database environment are stored in this registry. The values defined in this profile registry override any corresponding settings in the global-level and instance-level profile registries.
• DB2 user-level profile registry: The environment variable settings that are specific to each user are stored in this registry, and they TAKE higher precedence over other profile registry settings.

Db2 column-organized tables ADD columnar CAPABILITIES to DB2 databases, which include data that is stored with column organization and vector processing of column data.

If the workload is entirely an analytics or OLAP workload, the recommended approach is to put as many tables as possible into a column-organized format.

The process of inserting data into column-organized tables, or updating previously inserted data, is optimized for large INSERT transactions. As a RESULT, workloads that are transactional in nature should not use column-organized tables. Traditional row-organized tables with index access are generally better suited for these types of workloads. With column-organized tables, performance is optimal if 100 or more ROWS are impacted by each insert or update transaction.

• SYSADM: for managing the INSTANCE as a whole
• SYSCTRL: for administering a database manager instance
• SYSMAINT: for maintaining databases within an instance
• SYSMON: for monitoring the instance and its databases
• SECADM: for managing SECURITY within a database
• DBADM: for administering a database
• ACCESSCTRL: for granting and revoking AUTHORITIES and privileges (except for SECADM, DBADM, ACCESSCTRL, and DATAACCESS authority SECADM authority is required to GRANT and revoke these authorities)
• DATAACCESS: for accessing data
• SQLADM: for monitoring and tuning SQL queries
• WLMADM: for managing workloads
• EXPLAIN: for doing explain query plans (EXPLAIN authority does not  give access to the data itself
• LOAD: Users with LOAD authority are allowed to perform the following tasks: Quiesce or query the state of a tablespace Perform bulk-load operations by using the LOAD command Collect catalogue statistics by using the RUNSTATS command

What is the full FORM of SPUFI?
Full form of SPUFI is SQL Processor Using File Input.

In DB2 on which LEVEL we can apply the locks?
Below are the 3 level where we can apply locks
(1)PAGE
(2)Table
(3)Table SPACE

How do we classified LOCKS in DB2?
DB2 locks can be classified based on below OPTIONS
(1)SIZE
(2)Duration
(3)Mode

What is the storage LENGTH of TIMESTAMP data type and its default format?
Storage length of TIMESTAMP data type is 10 bytes and its default format is YYYY-MM-DD:HH:MM:SS-NNNNNN