Monday, September 28, 2009

Online LOAD

Same line online REORG, when you use SHRLEVEL CHANGE in the LOAD it is called 'Online LOAD'.
Online LOAD allows user transactions to run while data is being loaded. Online load resume acts much like SQL INSERT statements, and includes logging, page or row locking, index building, duplicate key, triggers, and referential constraint checking.

SHRLEVEL CHANGE:
Specifies that applications can concurrently read and write the table space or partition it is loading.
If you specify SHRLEVEL CHANGE, you can not specify the following parameters: INCURSOR, RESUME NO, REPLACE, KEEPDICTIONARY, LOG NO, ENFORCE NO, and SORTKEYS, STATISTICS, COPYDDN, RECOVERYDDN, MAPDDN, PREFORMAT, REUSE, and PART integer REPLACE. For a partition-directed LOAD, if you specify SHRLEVEL CHANGE, then only RESUME YES can be specified or inherited from the LOAD statement.
LOAD SHRLEVEL CHANGE does not perform the SORT, BUILD, SORTBLD, INDEXVAL, ENFORCE, or REPORT phases, and the compatibility/concurrency considerations are different.

Online LOAD is not compatible with Online REORG SHRLEVEL REFERENCE.

The regular LOAD uses SHRLEVEL NONE: Specifies that applications have no concurrent access to the table space or partition and the default is NONE.

Sunday, September 27, 2009

Online REORG

In simple terms, when you use SHRLEVEL CHANGE in the REORG utility, it is called 'Online REORG'.


First, let's see what was prior to online REORG:
  1. UNLOAD the data
  2. Redifine the tablespace
  3. RELOAD the data
  4. REBUILD the indexes

During UNLOAD, other users can read the tablespace. They can not update the tablespace. During other steps neither read nor updates are allowed. Typically, it's long an outage to the tablespace.




Online REORG:


Online reorg works by reloading the reorged tablespace and rebuilding the indexes into different datasets (shadow tablespaces and indexes). It then switches the original datasets and the new ones at the end of the reorg - the idea is that the objects are only unavailable for this short time at the end, not during the reload and rebuild phases which can be protracted for large objects. Online reorg comes in two flavours - one which allows applications to read and update during the reorg, and one which allows only reading.

It is determined by the keyword SHRLEVEL. SHRLEVEL has 3 options:

  • SHRLEVEL NONE - Traditional
  • REORGSHRLEVEL CHANGE - 'Online' REORG - Allows reads & Writes
  • SHRLEVEL REFERENCE - 'Online' REORG - Allows only reads

Be careful when choosing SHRLEVEL REFERENCE as it's an outage on the tablespace.

Pending Status - DB2

COPY pending status - COPY
How to reset?: Take image copy.


REBUILD pending – RBDP
How to reset?: REBUILD, RECOVER, LOAD REPLACE, REPAIR SET INDEX with NORBDPEND, Start ACCESS FORCE, REORG INDEX SORTDATA


RECOVER pending – RECP
How to reset?: For TS: Run RECOVER or LOAD REPLACE, REPAIR SET TABLESPACE with NORCVRPEND
-For index: REBUILD INDEX, RECOVER INDEX or REORG INDEX SORTDATA, REPAIR SET INDEX with NORCVRPEND
-For both: START ACCESS FORCE (data will not be fixed)


REORG pending – REORP
How to reset?: Run LOAD REPLACE, Run REORG with SHRLEVEL NONE,


REFRESH pending – REFP
Whenever DB2 puts an object in REFP status, it also put that object in Recover pending (RECP) status as well.
How to reset?: Run RECOVER and LOAD REPLACE.


Group BP RECOVER pending- GRECP
How to reset?: Recover the object, or use START DATABASE


Restart pending: RESTP
The restart pending status is set on if an object has back-out work pending at the end of DB2 restart TS, IX
How to reset?: Objects in the RESTP status remain unavailable until back-out work is complete, or until restart is canceled and a conditional restart or cold start is performed in its place.

Plan and Package




In Simple terms, PLAN is executable, but package is not.


A package contains control structures that DB2 uses when it runs SQL statements. Packages are produced during program preparation. You can think of the control structures as the bound or operational form of SQL statements. All control structures in a package are derived from the SQL statements that are embedded in a single source program.


An application plan relates an application process to a local instance of DB2, specifies processing options, and contains one or both of the following elements:



  • A list of package names

  • The bound form of SQL statements


Some info related to PLAN and PACKAGE:
* Regardless of what the plan contains, you must bind a plan before the applicationcan run.
* You cannot bind or rebind a package or a plan while it is running. However, you can bind a different version of a package that is running.
* Input to binding the plan can include DBRMs only, a package list only, or a combination of the two.


Advantages of PACKAGEs: (Ref: DB2 Application Programming and SQL Guide)


1. Ease of maintenance: When you use packages, you do not need to bind the entire plan again when you change one SQL statement. You need to bind only the package associated with the changed SQL statement.




2. Incremental development of your program: Binding packages into package collections allows you to add packages to an existing application plan without having to bind the entire plan again. A collection is a group of associated packages. If you include a collection name in the package list when you bind a plan, any package in the collection becomes available to the plan. The collection can even be empty when you first bind the plan. Later, you can add packages to the collection, and drop or replace existing packages, without binding the plan again.
(Read the above, it will tell you what is COLLECTION).

3. Versioning: Maintaining several versions of a plan without using packages requires a separate plan for each version, and therefore separate plan names and RUN commands. Isolating separate versions of a program into packages requires only one plan and helps to simplify program migration and fallback. For example, you can maintain separate development, test, and production levels of a program by binding each level of the program as a separate version of a package, all within a single plan.

4. Flexibility in using bind options: The options of BIND PLAN apply to all DBRMs bound directly to the plan. The options of BIND PACKAGE apply only to the single DBRM bound to that package. The package options need not all be the same as the plan options, and they need not be the same as the options for other packages used by the same plan.

5. Flexibility in using name qualifiers: You can use a bind option to name a qualifier for the unqualified object names in SQL statements in a plan or package. By using packages, you can use different qualifiers for SQL statements in different parts of your application. By rebinding, you can redirect your SQL statements, for example, from a test table to a production table.