The 7 system catalog tables will be updated while accessing data from remote systems:
- SYSIBM.LOCATIONS
- SYSIBM.LUNAMES
- SYSIBM.IPNAMES
- SYSIBM.MODESELECT
- SYSIBM.USERNAMES
- SYSIBM.LULIST
- SYSIBM.LUMODES
Buffer pools in DB2
Buffer pools are database objects used to cache database data pages in memory. If an object’s data page is placed in a buffer pool, physical I/O access to disks will be avoided. Buffer pools can be assigned to cache only a particular table space’s data. This assignment takes place within the table space definition.
Buffer pools are areas of virtual storage that temporarily store pages of table spaces or indexes. When a program accesses a row of a table, DB2 places the page containing that row in a buffer. When a program changes a row of a table, DB2 must write the data in the buffer back to disk (eventually) — normally either at a checkpoint or a write threshold. A write threshold is set either as a vertical threshold at the page set level or as a horizontal threshold at the buffer pool level.
Storage for buffer pools is in real memory above the 2 GB bar.
Up to 80 virtual buffer pools are available, allowing for the following breakdown:
50 – 4K page buffer pools (BP0–BP49)
10 – 32K page buffer pools (BP32K– BP32K9)
10 – 8K page buffer pools
10 – 16K page buffer pools
Related Posts
RID Pool in DB2
- The Row Identifier (RID) pool is used for storing and sorting RIDs for operations such as list prefetch, multiple index access, hybrid joins, and enforcing unique keys while updating multiple rows. The optimizer looks at the RID pool for prefetch and RID use.
- The full use of the RID pool is possible for any single user at runtime. Runtime operations can result in a table space scan if insufficient space is available in the RID. For example, if you want to retrieve 10,000 rows from a 100,000,000-row table and no RID pool is available, a scan of 100,000,000 rows would occur, at any time and without external notification. The optimizer assumes physical I/O will be less with a large pool.
The RID pool size is set with an installation parameter (16 K to 100,000 MB). The pool is created at startup time, but no space is allocated until RID storage is actually needed.
Sort Pool in DB2
- At startup, DB2 allocates a sort pool in the private area of the DBM1 address space. DB2 uses a special sorting technique called a tournament sort.
- During the sorting processes, it’s not uncommon for this algorithm to produce logical work files called runs, which are intermediate sets of ordered data. If the sort pool is large enough, DB2 completes the sort in that area.
- More often than not, however, the sort cannot be completed in the sort pool, and the runs are moved into the DB2 work files that are used to perform sorts. These runs are later merged to complete the sort.
- When the DB2 work files are used to hold the pages that make up the sort runs, you may experience performance degradation if the pages are externalized to the physical work files because they will have to be read back in later to complete the sort.
A DSNZPARM defines the sort pool size, which is currently 240 K to 128 MB with a 2 MB default.
Most referred DSN commands in DB2
ABEND- Causes the DSN session to terminate with a X’04E’
BIND – Builds an application package or plan
DB2 – Executes a DB2 command
DCLGEN -Produces declarations for tables or views
DSN – Starts a DSN session
END – Ends a DSN session
FREE -Deletes an application package or plan
REBIND – Updates an application package or plan
REBIND TRIGGER PACKAGE – Updates an application trigger package
RUN – Executes an application program
SPUFI – Executes the SQL Processing Using File Input facility
* – Comment
Error during load or Unload – how to remove lock on the table
-TERM UTILITY – Terminates execution of utility
-STOP PROCEDURE – Stops the acceptance of SQL CALL statements for stored procedures
Srinimf 