DB2之备忘录--index&load篇

好久不看专业书籍，今日在读DB2 administrator  guide的时候发现很多知识还是存在盲点，因此记录以备忘，内容可能比较简单，仅供参考

索引

DB2 indexes are also used to cluster data, to partition tables, to provide access paths to data, and to order retrieved
data without a sort.

是否在创建索引的时候，建立索引：

parameter  of defer  for  create index

If the table that you are indexing is empty, DB2 creates the index. However, DB2
does not actually create index entries until the table is loaded or rows are inserted.
If the table is not empty, you can choose to have DB2 build the index when the
CREATE INDEX statement is executed. Alternatively, you can defer the index build
until later. Optimally, you should create the indexes on a table before loading the
table. However, if your table already has data, choosing the DEFER option is
preferred; you can build the index later by using the REBUILD INDEX utility.

是否在创建索引的时候分配空间

parameter  of  define    for  space allocation ：
Deferred allocation of index space data sets
When you execute a CREATE INDEX statement with the USING STOGROUP
clause, DB2 generally defines the necessary VSAM data sets for the index space. In
some cases, however, you might want to define an index without immediately
allocating the data sets for the index space.
Example: You might be installing a software program that requires creation of
many indexes, but your company might not need some of those indexes. You
might prefer not to allocate data sets for indexes that you do not plan to use.
To defer the physical allocation of DB2-managed data sets, use the DEFINE NO
clause of the CREATE INDEX statement. When you specify the DEFINE NO
clause, DB2 defines the index but defers the allocation of data sets. The DB2
catalog table contains a record of the created index and an indication that the data
sets are not yet allocated. DB2 allocates the data sets for the index space as needed
when rows are inserted into the table on which the index is defined.
Index versions
DB2 uses index versions to maximize data availability. Index versions enable DB2
to keep track of schema changes and provides users with access to data in altered
columns that are contained in indexes.

load utility:
How the LOAD utility loads DB2 tables
Use the LOAD utility to load one or more persistent tables of a table space, or one
or more partitions of a table space. The LOAD utility operates on a table space, so
you must have authority for all tables in the table space when you run LOAD.
The LOAD utility 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.
Additionally, you can use the LOAD utility to do the following:
v Compress data and build a compression dictionary
v Convert data between compatible data types and between encoding schemes
v Load multiple tables in a single table space

Default values for columns
When you load a table and do not supply a value for one or more of the columns,
the action DB2 takes depends on the circumstances.
v If the column is not a ROWID or identity column, DB2 loads the default value
of the column, which is specified by the DEFAULT clause of the CREATE or
ALTER TABLE statement.
Replacement or addition of data
You can use LOAD REPLACE to replace data in a single-table table space or in a
multiple-table table space. You can replace all the data in a table space (using the
REPLACE option), or you can load new records into a table space without
destroying the rows that are already there (using the RESUME option).
COPY-pending status
The LOAD utility places a table space in the COPY-pending state if you load with
LOG NO, which you might do to save space in the log. Immediately after that
operation, DB2 cannot recover the table space. However, you can recover the table
space by loading it again. Prepare for recovery, and remove the restriction, by
making a full image copy using SHRLEVEL REFERENCE. (If you end the COPY
job before it is finished, the table space is still in COPY-pending status.)
When you use REORG or LOAD REPLACE with the COPYDDN keyword, a full
image copy data set (SHRLEVEL REF) is created during the execution of the
REORG or LOAD utility. This full image copy is known as an inline copy. The table
space is not left in COPY-pending state regardless of which LOG option is
specified for the utility.
The inline copy is valid only if you replace the entire table space or partition. If
you request an inline copy by specifying COPYDDN in a LOAD utility statement,
an error message is issued, and the LOAD terminates if you specify LOAD
RESUME YES or LOAD RESUME NO without REPLACE.
REBUILD-pending status
The LOAD utility places all the index spaces for a table space in the
REBUILD-pending status if you end the job (by using -TERM UTILITY) before it
completes the INDEXVAL phase. It places the table space itself in
RECOVER-pending status if you end the job before it completes the RELOAD
phase.
Implications of using an INSERT statement to load tables
If you plan to use the INSERT statement to load tables, you should consider some
of the implications.
v If you are inserting a large number of rows, you can use the LOAD utility.
Alternatively, use multiple INSERT statements with predicates that isolate the
data that is to be loaded, and then commit after each insert operation.
v When a table, whose indexes are already defined, is populated by using the
INSERT statement, both the FREEPAGE and the PCTFREE parameters are
ignored. FREEPAGE and PCTFREE are in effect only during a LOAD or REORG
operation.
v Set the NOT LOGGED attribute for table spaces when large volumes of data are
being inserted with parallel INSERT processes. If the data in the table space is
lost or damaged, it can be reinserted from its original source.
v You can load a value for a ROWID column with an INSERT and fullselect only
if the ROWID column is defined as GENERATED BY DEFAULT. If you have a
table with a column that is defined as ROWID GENERATED ALWAYS, you can
propagate non-ROWID columns from a table with the same definition.

ref: 

db2 白皮书