The whole series us updated by Deepthi

Topics covered

• QMF
• SPUFI
• Program preparation and execution
• DCLGEN
• LOCKS
• DB2 Utilities

DCLGEN

DCLGEN is a facility that is used to generate SQL statements that describe a table or view. These table descriptions are then used to check the validity of other SQL statements at precompile time. DCLGEN  uses table declaration to build a host language structure, which  is used by the DB2 precompiler  to verify that correct column names and data types have been specified in the SQL statement.

LOCKS

• DB2 uses locks to control concurrence and prevent lost updates.
• Locks prevent one program from accessing data that another  program has changed but not yet committed.
• DB2 acquires all locks implicitly under DB2 control.
  • Lock can be obtained  ==>

• • Implicitly – by the system
  • Explicitly – by the user

There are 2 kinds of locks :

• Shared             S
• Exclusive         X

SHARE MODE Lock :

• Is used for querying consistent data
• Prevents other users from updating data, you are reading
• Prevents other users from putting EXCLUSIVE mode lock  on the table
• Lets other users read the data
• Locks the whole table

Example  :

LOCK TABLE EMPLOYEE

IN  SHARE  MODE ;

EXCLUSIVE MODE lock :

• Is used for changing data
• Prevents other users from updating data, you’re reading
• Prevents other users from putting ANY lock on the table
• Lets other users read the data
• Does not guarantee current data
• Locks the whole table

Example :

LOCK TABLE EMPLOYEE       IN          EXCLUSIVE MODE ;

Locks can be obtained implicitly or explicitly.

Implicit lock is issued by the system itself.

DB2 provides facilities for explicit locking :

• The SQL statement LOCK TABLE
• The ISOLATION parameter on the BIND command
• The tablespace LOCKSIZE parameter
• The ACQUIRE/RELEASE parameters on the BIND command

DB2 UTILITIES

• LOAD
• IMAGECOPY
• RECOVERY
• REORG
• RUNSTATS
• CHECKS

LOAD

Use LOAD to load one or more tables of a table space. LOAD loads records    into the tables and builds or extends any indexes defined on them. If the   table space already contains data, you can choose whether you want to add   the new data to the existing data or replace the existing data. The loaded  data is processed by any edit or validation routine associated with the     table, and any field procedure associated with any column of the table.

REORG

The REORG online utility reorganizes a table space or index to improve access performance and reclaim fragmented space. In addition, the utility can reorganize a single partition of either a partitioned index or a   partitioned table space. You can specify the degree of access to your data during reorganization. If you specify REORG UNLOAD ONLY, the data is unloaded in a format that is acceptable to the LOAD utility of the same DB2 subsystem.

Output: If the table space or partition has the COMPRESS YES attributes, then the data is compressed when reloaded. If you specify the KEEPDICTIONARY option of REORG, the current dictionary is used; otherwise  a new dictionary is built.

REORG TABLESPACE can be executed on the table spaces in the DB2 catalog

database (DSNDB06) and some table spaces in the directory database

(DSNDB01).  It cannot be executed on any table space in the DSNDB07

database.

Authorization Required: To execute this utility on a user table space or

index, the privilege set of the process must include one of the following:

• REORG privilege for the database
• DBADM or DBCTRL authority for the database
• SYSCTRL authority
• SYSADM authority.

Popularity: unranked [?]

Related interview questions

• DB2 understanding : Part – I
• Interview Questions DB2 : Part – II
• Interview Questions DB2 : Part – I
• What is a DB2 bind?
• What are the levels of isolation available with DB2V4