Insight and perspectives

 A transaction is a sequence of one or more SQL statements grouped together as a single unit, typically within an application process. The commands used to complete or end the transaction are as follows:  COMMIT ROLLBACK Transaction logging is a process to keep track of changes made to a database (by one or more application connections or transactions) as they are made. These changes are recorded in the transaction logs for replay purposes. The following section describes the database configuration parameters related to transaction logging:  logbufsz: This parameter specifies the amount of memory to use as a buffer for log records before writing these records to disk. The default value is 256 pages, however the Db2 configuration advisor will generally set the size to 2,151 pages (4 K page size). logfilsiz: This parameter specifies the size of each primary and secondary log file. The size of these log files limits the number of log records that can be written b...

  The REBIND Utility: This utility command can be used to recreate a package stored in the database without the need for a bind file. The basic command syntax for REBIND is this:     REBIND <PACKAGE> [PackageName] <VERSION [VersionID]> <APREUSE [YES | NO]>     <RESOLVE [ANY | CONSERVATIVE]>     <REOPT [NONE | ONCE | ALWAYS] > Consider the following:  PackageName: Identifies the name to assign to the package to rebind VersionID: Identifies a specific version of the package to be rebound If you want to REBIND the package DEMO.PACKAGE to the database, you can use the following command: REBIND PACKAGE demo.package; If you create any new indexes in a table that the DEMO.PACKAGE relies on, you should rebind the package to make use of the newly created indexes.

  The RUNSTATS Utility:  This utility command can use be used to collect table and index statistics to build efficient access plans. The basic command syntax for RUNSTATS is:      RUNSTATS ON TABLE [TableName]     USE PROFILE     <UTIL_IMPACT_PRIORITY [Priority]> You could also use this version of the command:      RUNSTATS ON TABLE [TableName] FOR     <<SAMPLED> DETAILED>     [INDEXES | INDEX]     [[IndexNames] | ALL]     <EXCLUDING XML COLUMNS>     <ALLOW READ ACCESS | ALLOW WRITE ACCESS>     <SET PROFILE NONE | SET PROFILE <ONLY> | UPDATE PROFILE         <ONLY>>     <UTIL_IMPACT_PRIORITY [Priority]> You could also use this version ...

The REORG Utility: This utility command can be used to re-organize tables and indexes either in online or in offline mode to improve storage efficiency and reduce the I/O access cost. This utility can also be used to implement various compression algorithms for tables and indexes. The basic command syntax for REORG is this:   REORG TABLE [TableName] <INDEX [IndexName]> <ALLOW READ ACCESS | ALLOW NO ACCESS> <USE [TmpTSName]> <INDEXSCAN> <LONGLOBDATA <USE [LongTSName]>> <KEEPDICTIONARY | RESETDICTIONARY> <RECLAIM EXTENTS [ALLOW [WRITE | READ | NO] ACCESS]> Or   REORG TABLE [TableName] <INDEX [IndexName]> INPLACE [ALLOW READ ACCESS | ALLOW NO ACCESS] [<TRUNCATE | NOTRUNCATE TABLE> | <CLEANUP OVERFLOWS>] [START | RESUME] | [STOP | PAUSE]   Or   REORG TABLE [TableName] <INDEX [IndexName]> INPLACE [STOP | PAUSE]   Or   REORG [INDEXE...

  The REORGCHK utility: This utility command can use be used to suggest tables and indexes that would benefit from reorganization. The basic command syntax for REORGCHK is this:     REORGCHK     <UPDATE STATISTICS | CURRENT STATISTICS>     <ON TABLE USER |         ON SCHEMA [SchemaName] |         ON TABLE [USER | SYSTEM | ALL | [TableName]> Consider the following:  SchemaName: Identifies the name to assign to a schema whose objects are to be analyzed to determine whether they must be re-organized TableName: Identifies the name to assign to a specific table to analyze, to determine whether it must be re-organized.

A complete load operation consists of five distinct phases; they are as follows: Analyze phase Load phase Build phase Delete phase Index copy phase Analyze phase:  This phase of the Load process is only utilized when a column-organized table is being loaded and the column compression dictionaries need to be built. This happens during a LOAD REPLACE operation, a LOAD REPLACE RESETDICTIONARY operation, a LOAD REPLACE RESETDICTIONARYONLY operation, or a LOAD INSERT operation (if the column-organized table is empty). For column-organized tables, this phase is followed by the load, build, and delete phases. The following diagram showcases the steps involved in the analyze phase. The analyze phase is invoked if you are loading data for the first time into a column-organized BLU acceleration table using LOAD INSERT, or running a LOAD REPLACE with RESETDICTIONARY or RESETDICTIONARYONLY clause. Load phase:  During this phase, data is loaded into the table, and index keys and table stat...