Thursday, October 27, 2011

Types of Tables in DB2


Types of tables


DB2 databases store data in tables. In addition to tables used to store persistent data, there are also tables that are used for presenting results, summary tables and temporary tables; multidimensional clustering tables offer specific advantages in a warehouse environment, whereas partitioned tables let you spread data across more than one database partition.

Base tables
These types of tables hold persistent data. There are different kinds of base tables, including

Regular tables
Regular tables with indexes are the "general purpose" table choice.

Multidimensional clustering (MDC) tables
These types of tables are implemented as tables that are physically clustered on more than one key, or dimension, at the same time. MDC tables are used in data warehousing and large database environments. Clustering indexes on regular tables support single-dimensional clustering of data. MDC tables provide the benefits of data clustering across more than one dimension. MDC tables provide guaranteed clustering within the composite dimensions. By contrast, although you can have a clustered index with regular tables, clustering in this case is attempted by the database manager, but not guaranteed and it typically degrades over time. MDC tables can coexist with partitioned tables and can themselves be partitioned tables.

Range-clustered tables (RCT)
These types of tables are implemented as sequential clusters of data that provide fast, direct access. Each record in the table has a predetermined record ID (RID) which is an internal identifier used to locate a record in a table. RCT tables are used where the data is tightly clustered across one or more columns in the table. The largest and smallest values in the columns define the range of possible values. You use these columns to access records in the table; this is the most optimal method of utilizing the predetermined record identifier (RID) aspect of RCT tables.

Temporary tables
These types of tables are used as temporary work tables for a variety of database operations. Declared temporary tables (DGTTs) do not appear in the system catalog, which makes them not persistent for use by, and not able to be shared with other applications. When the application using this table terminates or disconnects from the database, any data in the table is deleted and the table is dropped. By contrast, created temporary tables (CGTTs) do appear in the system catalog and are not required to be defined in every session where they are used. As a result, they are persistent and able to be shared with other applications across different connections.
Neither type of temporary table supports
User-defined reference or user-defined structured type columns
LONG VARCHAR columns
In addition XML columns cannot be used in created temporary tables.

Materialized query tables
These types of tables are defined by a query that is also used to determine the data in the table. Materialized query tables can be used to improve the performance of queries. If the database manager determines that a portion of a query can be resolved using a summary table, the database manager can rewrite the query to use the summary table. This decision is based on database configuration settings, such as the CURRENT REFRESH AGE and the CURRENT QUERY OPTIMIZATION special registers. A summary table is a specialized type of materialized query table.
You can create all of the preceding types of tables using the CREATE TABLE statement.

Depending on what your data is going to look like, you might find one table type offers specific capabilities that can optimize storage and query performance. For example, if you have data records that will be loosely clustered (not monotonically increasing), consider using a regular table and indexes. If you have data records that will have duplicate (but not unique) values in the key, you should not use a range-clustered table. Also, if you cannot afford to preallocate a fixed amount of storage on disk for the range-clustered tables you might want, you should not use this type of table. If you have data that has the potential for being clustered along multiple dimensions, such as a table tracking retail sales by geographic region, division and supplier, a multidimensional clustering table might suit your purposes.

In addition to the various table types described above, you also have options for such characteristics as partitioning, which can improve performance for tasks such as rolling in table data. Partitioned tables can also hold much more information than a regular, nonpartitioned table. You can also exploit capabilities such as compression, which can help you significantly reduce your data storage costs.

Automatic revalidation in DB2:


Automatic revalidation is a mechanism whereby database objects that a have been invalidated (for example, after a DROP statement) undergo revalidation automatically.

The following list shows the data definition language (DDL) statements for which automatic revalidation is currently supported:
ALTER MODULE DROP FUNCTION
ALTER MODULE DROP PROCEDURE
ALTER MODULE DROP TYPE
ALTER MODULE DROP VARIABLE
ALTER NICKNAME (altering the local name or the local type)
ALTER TABLE ALTER COLUMN
ALTER TABLE DROP COLUMN
ALTER TABLE RENAME COLUMN
CREATE OR REPLACE ALIAS
CREATE OR REPLACE FUNCTION
CREATE OR REPLACE NICKNAME
CREATE OR REPLACE PROCEDURE
CREATE OR REPLACE SEQUENCE
CREATE OR REPLACE TRIGGER
CREATE OR REPLACE VARIABLE
CREATE OR REPLACE VIEW
DROP FUNCTION
DROP NICKNAME
DROP PROCEDURE
DROP SEQUENCE
DROP TABLE
DROP TRIGGER
DROP TYPE
DROP VARIABLE
DROP VIEW
RENAME TABLE

Reason code "2". SQLSTATE=40001

SQL0911N The current transaction has been rolled back because of a deadlock or timeout



Main reason for this error will be deadlock time out properties. To view this property value issue the following command:


Display Database Manager Configuration Settings:
db2 get dbm cfg


Display Database level Configuration Settings
db2 get db cfg for <DatabaseName>


If dead lock time out value is -1 then change to either 60 or 120 sec based on your DB transaction.


update db cfg for <Databasename> using LOCKTIMEOUT n


n - number of sec.




Creating a simple DB2 Trigger

When Trigger?

           When a specified SQL operation i.e. a DELETE, INSERT, or UPDATE occurs on a table, a trigger can be activated that defines a set of actions. Triggers, unlike referential and check constraints, can even be used to update other tables.

Triggers can be fired before or after an INSERT, DELETE, or UPDATE of a table

Creating Trigger:


Figure 1.




Figure 2.


Figure 3.


Figure 4.


Figure 5. Specify the column name


Monday, October 17, 2011

DB2 UDB trace facility


When using DB2 UDB you might on occasion encounter an error message that directs you to "get a trace and call IBM Support", "[turn] on trace [and] examine the trace record", or to "contact your technical [support] representative with the following information: problem description, SQLCODE, SQLCA contents (if possible), and trace file (if possible)". Or, when you report a problem to IBM Support, you might be asked to perform a trace to capture detailed information about your environment.
DB2 traces can be especially useful when analyzing recurring and reproducible problems, and greatly facilitate the support representative's job of problem determination. But exactly what is a trace, and how do you take one? This article introduces you to the DB2 trace facility and the db2trc command, and shows you (by way of actual examples) how to capture trace information, should you ever need to do so.
A DB2 trace is essentially a log of control flow information (functions and associated parameter values) that is captured while the trace facility is on. Traces are very useful to DB2 technical support representatives who are trying to diagnose a problem that may be difficult to solve with only the information that is returned in error messages.
Tracing can be restricted to a particular component (such as buffer pool services). This action is known as masking. Masking a trace helps to minimize the amount of information that is captured. When taking a trace, try to use the smallest scenario possible to reproduce your problem; otherwise, the amount of data that is captured might be prohibitive.

An important note about db2trc

Enabling the DB2 trace facility can impact your system's performance. The degree of performance degradation is dependent on how many resources are being used to gather information for the trace. Avoid taking traces in a production environment, and use the trace facility when directed to do so by a DB2 technical support representative.
Note that trace information is not always helpful when diagnosing an error; for example, it might not capture the error condition if:
  • The trace buffer has not been set large enough to hold a complete set of relevant trace events
  • The trace does not encompass the re-created error situation
The DB2 trace facility is controlled by means of the db2trccommand, which is used to start and stop a trace, and to format captured trace information.
You can use the db2trc command to capture trace information and to format this information into readable text. Issue thedb2trc command at an operating-system command prompt or from a shell script. Although you have the option of tracing to a file (instead of to memory), tracing to a file is slower; nevertheless, this may be your only option if the problem being re-created prevents you from "dumping" the trace (from memory) to a file. The idea is to dump the trace immediately after the problem occurs, then to turn tracing off and, finally, to format the event data either chronologically or by process or thread.
The syntax of the db2trc command is shown in Figure 1. The command options can be grouped into two broad categories: collection and parsing.
  • Collection options include turning a trace on or off; specifying the trace buffer size; specifying or changing trace options; dumping a trace; and clearing the trace buffer.
  • Parsing options include sorting the formatted trace records chronologically or by process or thread.
Trace options include the ability to specify a mask to capture trace records that satisfy specified criteria (for example, only records that are associated with a particular function or component); the ability to trace only specified process and thread combinations; and the ability to request (relative) timestamp data.
You can trace a DB2 instance or a DB2 Administration Server (DAS) instance; tracing a DB2 instance is the default behavior. To do this, you don't have to specify the db2 clause. The examples in this article pertain to tracing the default DB2 instance only.
Note that the -u option is a handy way to access command line help for most of the options on the db2trc command.

Figure 1. Syntax diagram showing the principal options of the db2trc command
db2trc syntax
An excellent way to understand the main options on the db2trc command is to try them. We will explore a very simple example using the SAMPLE database that comes with DB2 UDB. First, we will turn the trace facility on, specifying an 8-MB trace buffer. We will be tracing into shared memory (rather than to a file), using the -i option, which stops the trace as soon as the buffer is full. (The -l option, on the other hand, preserves the last trace records, because the buffer is allowed to wrap, or overwrite existing records. This is the default behavior.) The buffer size can be specified in bytes or megabytes. To specify megabytes, append M(or m) to the value.
We will also specify a trace mask to limit the number of trace records that will be captured. In this case, we will trace only the buffer pool services component. This can be done in one of two ways. We can simply specify the -m option followed by the component name enclosed by double quotation marks (-m "buffer pool services"), or we can specify the component number (in this case, 2) within the full mask syntax. A trace mask has five elements that are delimited by periods:
  • Type. This element refers to the trace record type, and includes entry (1), exit (2), data (3), error (4), and fatal error (5).
  • Product. DB2 is product 3.
  • Component. This element refers to a component name; for example, buffer pool services (2). The component's short name can also be specified; for example, sqlb.
  • Function. This element refers to a function name; for example, sqlbpfDbheapSize (404).
  • Function category. This element specifies whether the function is an internal function (fnc, 0), component external interface (cei, 2), or external API (api, 3).
Each element can consist of a comma-delimited list, a hyphenated range, or a single entry. An asterisk can be used to match anything. Values can be specified by their names or corresponding numbers. Setting a mask to "*.*.*.*.*" is equivalent to not specifying a mask. It is recommended that you enclose the trace mask with double quotation marks to avoid possible misinterpretation of the asterisk character by the operating-system shell.
Component and function names and their associated numbers are listed in several internal header files, which you will not be able to see. If you need to find some names or numbers to set up a particular mask, it is recommended that you look in a sample of formatted trace output. (More about that later.)
The last option that we will specify is the -t option, which will direct the trace facility to capture relative timestamps associated with each trace record.
We are now ready to start the trace. We will issue the db2trc command, start the default DB2 instance, clear the trace buffer, connect to the SAMPLE database, write the contents of the trace buffer to a file named dmp, and then turn tracing off, as shown inListing 1.

Listing 1. Turning on the trace facility
db2trc on -i 8m -m "*.*.2.*.*" -t
db2start
db2trc clear
db2 connect to sample
db2trc dump dmp
db2trc off

There are some important things that you should note about the trace buffer:
  • When you specify a buffer size, the value must be a power of 2. For example, if you request a 7-MB buffer, the value is rounded down to 4 megabytes; if you request an 8000000-byte buffer, the value is rounded down to 4194304 bytes.
  • The minimum buffer size is 1 megabyte. The default buffer size is 8 megabytes on Windows® operating systems, and 4 megabytes on UNIX®-based systems. The default and minimum trace buffer sizes depend on the version of DB2, and the maximum trace buffer size depends on the platform.
  • For performance reasons, the trace facility does not allow you to change the trace buffer size dynamically. As long as there is a single process attached to the shared trace buffer, you will not be able to change the buffer size. The best way to specify a different buffer size is to issue the db2trc command before starting the instance (that is, before issuing thedb2start command).
  • If you are tracing to shared memory, the size of the trace buffer controls the size of the dump file. If you are tracing to a file, the size of the dump file is limited only by the free space and maximum allowable file size on the system.
The trace that we have just dumped from memory to a file named dmp can be parsed or formated into readable text using one or both of the parsing options on the db2trc command. Listing 2 shows an example of each parsing request, including the summary information that is returned to standard output. In our example, the trace is small enough that truncation does not occur. Listing 2 also shows the sizes of the various output files. As expected, the dump file (dmp) is approximately 8 MB in size, corresponding to the trace buffer size that was specified on the db2trc command.

Listing 2. Parsing the trace
db2trc format dmp fmt

Trace truncated                   : NO
Trace wrapped                     : NO
Total number of trace records     : 2846
Number of trace records formatted : 2846

db2trc flow dmp flw

Total number of trace records     : 2846
Trace truncated                   : NO
Trace wrapped                     : NO
Number of trace records formatted : 2771 (pid: 23494 tid 1 node: 0)
Number of trace records formatted : 15 (pid: 19834 tid 1 node: 0)
Number of trace records formatted : 15 (pid: 28780 tid 1 node: 0)
Number of trace records formatted : 15 (pid: 28396 tid 1 node: 0)
Number of trace records formatted : 30 (pid: 26332 tid 1 node: 0)

ls -l
...
-rw-r-----   1 melnyk   staff       8393432 Aug 20 15:43 dmp
-rw-r-----   1 melnyk   staff        121489 Aug 20 15:44 flw
-rw-r-----   1 melnyk   staff        541845 Aug 20 15:44 fmt
...

The format option creates a plain text trace output file in which the trace records appear in chronological order. An example of such output in shown in Listing 3. Each record represents a trace point with its own identifying number. Trace point numbers appear consecutively in a chronologically formatted output file. If you examine the first record (trace point 1), you'll see that it contains the trace record type ("entry"), the product ("DB2 UDB"), the component ("buffer pool services"), the function ("sqlbGetTotalBufferPoolMemSize"), and the function category ("cei"). You will recall that this information maps to elements that can be used to define a mask. The numeric equivalents of these elements also appears in the trace record; "(1.3.2.537.2)", for example. If a trace point also happens to be a probe point (an explicitly identified precise location within a function), the probe number is also included in this string (see trace point 4, for example).
The formatted trace record also identifies the process ("23494"), the thread ("1"), the companion process, if applicable ("-1"), and the node ("0"). Because we specified the -t option on the db2trc command, relative timestamps are also included in the output. A relative timestamp shows the time that has elapsed since the beginning of the trace. In this case, the entire trace covers a period of 361,858,476 nanoseconds, or just over one-third of a second.
As shown in the syntax diagram (Figure 1), the format option can be used to parse only those trace points that are associated with one or more specific processes; for example: db2trc format dmp fmt1 -p 19834.

Listing 3. Formatted trace records in chronological order. This partial output shows the first six and the last trace point in the file.
1 entry DB2 UDB buffer pool services sqlbGetTotalBufferPoolMemSize cei (1.3.2.537.2)
 pid 23494 tid 1 cpid -1 node 0 sec 0 nsec 0

2 entry DB2 UDB buffer pool services sqlbpfDbheapSize fnc (1.3.2.404.0)
 pid 23494 tid 1 cpid -1 node 0 sec 0 nsec 5659

3 entry DB2 UDB buffer pool services sqlbGetBPConfig cei (1.3.2.310.2)
 pid 23494 tid 1 cpid -1 node 0 sec 0 nsec 10319

4 data DB2 UDB buffer pool services sqlbGetBPConfig cei (3.3.2.310.2.0)
 pid 23494 tid 1 cpid -1 node 0 sec 0 nsec 11629 probe 0
 bytes 12

 Data1  (PD_TYPE_HEXDUMP,4) Hexdump:
 0000 0000                                  ....


5 exit DB2 UDB buffer pool services sqlbGetBPConfig cei (2.3.2.310.2)
 pid 23494 tid 1 cpid -1 node 0 sec 0 nsec 19329
 rc = 0

6 exit DB2 UDB buffer pool services sqlbpfDbheapSize fnc (2.3.2.404.0)
 pid 23494 tid 1 cpid -1 node 0 sec 0 nsec 22358
 rc = 0x000448BC = 280764

...

2846 exit DB2 UDB buffer pool services sqlbCheckBPFConsistancy fnc (2.3.2.273.0)
 pid 23494 tid 1 cpid -1 node 0 sec 0 nsec 361858476
 rc = 0

The flow option creates a plain text trace output file in which the trace records are ordered chronologically by process; it creates what is essentially a control flow diagram. An example of such output in shown in Listing 4. Information contained in this output includes process, thread, and node number, as well as trace point numbers, function names, trace record types and, in some cases, probe numbers. A nice feature is that each new function entry is indented for clarity.
Once again, as shown in the syntax diagram (Figure 1), the flow option can also be used to parse only those trace points that are associated with one or more specific processes; for example: db2trc flow dmp flw1 -p 19834.

Listing 4. Part of a control flow diagram
...

pid = 19834 tid = 1 node = 0

846         sqlbPFPrefetcherEntryPoint entry
847         sqlbPFPrefetcherEntryPoint data [probe 0]
848         | sqlbPFInitPrefetcher entry
849         | sqlbPFInitPrefetcher data [probe 0]
850         | | sqlbConnectToSharedMemory entry
851         | | sqlbConnectToSharedMemory exit
852         | | sqlbInitEDU entry
853         | | | sqlbGetBPConfig entry
854         | | | sqlbGetBPConfig data [probe 0]
855         | | | sqlbGetBPConfig exit
856         | | | SqlbFileTbl::SqlbFileTbl entry
857         | | | SqlbFileTbl::SqlbFileTbl exit
858         | | sqlbInitEDU exit
859         | sqlbPFInitPrefetcher exit
860         | sqlbpfRemoveFromQ entry

...

The info option on the db2trc command returns a handy summary of the trace parameters that are associated with a particular dump file. Using our dump file named dmp, the command db2trc info dmp returns:

Listing 5. Information returned by the "info" option on the db2trc command
Marker                  :  @TRACE@
Trace version           :      6.0
Platform                :      AIX
Build level             :  s040811
maxBufferSize           : 8388608 bytes (8 MB)
auxBufferSize           : 6291456 bytes (6 MB)
allocationCount         : 2
DB2TRCD pid             : 0
Trace destination       : <shared memory buffer>
crash                   : disabled
crash runtime passno    : 0
numSuspended            : 0

Buffer size             : 8388608 bytes (8 MB)
Allow buffer to wrap    : no
Mask                    : *.*.2.*.*
Timestamps              : enabled
PID.TID mask            : all
Fixed data mask #1      : all
Fixed data mask #2      : all
Max system errors       : infinite
Treat this rc as sys err: none

Some of the other options that are available on the db2trc command (and that are shown in Figure 1) include:
  • -perfcount. The performance counter is a collection option that records the total number of times each function is called; its associated -t option specifies that the total amount of time spent in each function is also to be captured. The performance counter also supports the -m option, which allows you to specify a mask.
  • perffmt. The performance trace formatter is a parsing option that formats a dump file containing performance counter data into readable text (see Listing 6).
  • change. This collection option lets you change the trace options that are in effect.
  • -resume. This collection option lets you resume execution of a suspended process. You cannot resume if -suspendwas not enabled (see below).
  • dump -q. The -q (quiet mode) option disables printing of the message "Trace has been dumped to file..." to standard output.
  • stop. This collection option stops tracing on demand; all processes suspend tracing, but the contents of the trace buffer are preserved so that they can be dumped later. This action is in contrast to the off option, which disables the trace facility altogether.
  • -c cpid. This trace option requests that only the specified companion process be traced.
  • -rc return-code. This trace option requests that the specified value (which must be a signed integer) be treated as a system error.
  • -e max-sys-errors. This trace option requests that tracing be stopped once the specified number of system errors has occurred.
  • -crash point. This trace option requests that a process be crashed at a specified trace point. The trace point can be specified as follows: "product-name.component-short-name.function-name.entry | exit | probe". For example: "DB2.SQLE.sqleFirstConnect.entry". The probe element represents any trace point in the function other than an entry or an exit trace point. DB2 UDB Version 8.2 introduces the -debug option as a synonym for -crash.
    Note: Use the -crash option with care, and only when directed to do so by a DB2 technical support representative.
  • -passno i. This trace option requests that a process be crashed after the crash point has executed i times.
  • -sleep s. This trace option requests that a process sleep for s seconds instead of crashing.
  • -signum n. This trace option requests that a process raise signal n instead of crashing.
  • -pause. This trace option requests that a process pause until signalled instead of crashing.
  • -suspend. This trace option requests that a process suspend execution instead of crashing.
  • -softcrash. This trace option requests that a process interrupt the agent instead of crashing.

Listing 6. Turning on the performance counter and formatting the captured data. Sample output shows the first five formatted records.
db2 connect reset
db2stop

db2trc on -i 8m -perfcount -t -m "*.*.2.*.*"

db2start
db2trc clear
db2 connect to sample

db2trc dump dmp1
db2trc off

db2trc perffmt dmp1 pfmt


13 (0 sec, 28978 nanosec)  sqlbReducePagesPinned
8 (0 sec, 114614 nanosec)  sqlbLoadContainerList
1 (0 sec, 6309 nanosec)  sqlbAllocateBuckets
1 (0 sec, 28228 nanosec)  sqlbpfCreateQ
4 (0 sec, 195840 nanosec)  sqlbConnectToSharedMemory
...

This article introduces you to the basics of DB2 tracing, and shows you, by way of working examples, how to use the db2trccommand if you are ever requested to capture trace information by a DB2 technical support representative. You can try some of these examples yourself in a test (not production!) environment to acquire a better understanding and appreciation of the DB2 trace facility.

Unlocking the secrets surrounding the locking behavior of the DB2 UDB data movement utilities

Summary:  The DB2 Universal Database data movement utilities -- export, import, and load -- support various levels of concurrency, which is primarily enforced through the placement of different types of locks on resources such as database tables or rows. This article describes the locking behaviour of these utilities and shows you, through working examples, how to use tools such as the DB2 UDB snapshot monitor to view and acquire a better understanding of this behavior.

To prevent uncontrolled data access, and provide concurrency control, the IBM® DB2® Universal DatabaseTM (DB2 UDB) database manager places locks on database resources such as table rows or entire tables. A lock associates the resource with an application, called the lock owner, to control how other applications can access the same resource. In short, locking is the means by which data integrity can be maintained when two or more application processes simultaneously request access to the same data.
Locks prevent uncommitted changes made by one application process from being accessed by other processes. The locks are released when the process ends or when a commit operation releases locks that were acquired during a unit of work (UOW). A UOW is a recoverable sequence of operations within an application process; it ends as a result of a commit operation, a rollback operation, or the end of the application process.
The principal DB2 UDB data movement utilities are export, import, and load. These utilities can be used to move data between databases. The export utility copies data from database tables to files, and those files can then be used to import or load the data into another database table.

Isolation levels

The isolation level that is associated with an application process defines the degree to which that process is isolated from other concurrently executing application processes. The isolation level is specified as an attribute of the package that the application process uses. A package is a database object that is created during execution of the BIND, PRECOMPILE, or REBIND command; it contains output from the SQL compiler and is used to execute SQL statements. An appropriate isolation level can be specified when you precompile (prepare) an application, or when you bind an application to a database.
  • When an application is precompiled, embedded SQL statements are replaced with source code that can be submitted to the SQL compiler.
  • When an application is bound to a database, output from the SQL compiler is converted into a package.
DB2 UDB supports the following isolation levels:
  • Repeatable read (RR)ensures that any table row read during a unit of work (UOW) is not changed by other application processes until the UOW completes. Similarly, any row changed by another application process cannot be read until it is committed by that application process. An application process running at the RR level is completely isolated from the effects of concurrent application processes.
  • Read stability (RS) is similar to RR. However, an application process running at the RS level is not completely isolated from the effects of concurrent application processes. If such an application process issues the same query more than once, it might see changed data or new ("phantom") rows that have been added by other application processes.
  • Cursor stability (CS) also ensures that any row changed by another application process cannot be read until it is committed by that application process. However, the CS isolation level only ensures that the current row of every updatable cursor is not changed by other application processes; rows that were read during a UOW can be changed by other application processes.
  • Uncommitted read (UR) allows, for certain operations, any row read during a UOW to be changed by other application processes, and any row changed by another application process to be read, even if that change has not been committed. For other operations, UR is similar to CS.
Prior to Version 8, the DB2 UDB load utility needed exclusive access to the table spaces that were associated with the target table. That is no longer the case. As of Version 8, the load utility places locks on the table itself, and you can enjoy concurrent access to other table objects in the same table spaces while a data load operation is running. In fact, the load utility, which is much faster than the import utility, can still take quite a long time to run when huge amounts (gigabytes or more) of data are being loaded. In circumstances such as this, concurrent access to pre-existing data in target tables of load operations is very important.
This article outlines the locking behavior of the DB2 UDB data movement utilities, and provides an overview of how concurrent access to data is managed while these utilities are running.
The DB2 UDB export utility writes data from a database table to one or more files that are stored outside of the database. The export utility uses SQL to read the data from the table. The files to which the export utility writes data can be in one of several standard file formats, most commonly PC/IXF or DEL. The format of a PC/IXF file is a version of an adaptation of the Integration Exchange Format (IXF) data interchange architecture. A delimited ASCII (DEL) file is a stream of ASCII characters; rows in the data stream are separated by row delimiters, and column values within a row are separated by column delimiters. For more information about these file formats, see Exploit file type modifiers that are built into the DB2 data movement utilities. Besides specifying the format of the data that is to be written to the output file, you must also provide the name of that file, and an SQL SELECT statement to specify the data that is to be exported. That is all the information that is required to initiate a basic DB2 export operation. Figure 1 shows a partial syntax diagram for the EXPORT command that includes only the required parameters.
A DB2 export operation requires a database connection. Other applications that want to access data in the same table concurrently do not need to disconnect from the database, and are free to pursue read or write operations in that table. That is because the export utility is bound with the cursor stability (CS) isolation level (for a description of the DB2 isolation levels, see the sidebar). During an export operation, row-level locking is applied to the exporting table, which means that only one lock is held at a time. That lock is the row lock that is associated with the row being exported. When the isolation level is cursor stability, the lock is released as the cursor moves from row to row.

Figure 1. Partial syntax of the DB2 EXPORT command
EXPORT command
We can demonstrate this quite easily, using the following scenario. We will use a delimited ASCII data file named staffdata.del. This file contains a substantial amount of data (22767 records), which is necessary for this type of exercise. Such a file can quickly be assembled by pasting many copies of exported data from the STAFF table (in the SAMPLE database that comes with DB2 UDB) into a text file, and then overwriting the ID (SMALLINT) column data with sequential numbers that have been generated in a spreadsheet. This ensures unique values in the ID column.
Suppose we were to create a new table, NEWSTAFF, using the table definition for the STAFF table. After connecting to the SAMPLE database, we create the NEWSTAFF table by issuing the following SQL statement: create table newstaff like staff. Using the DB2 IMPORT command, we then populate this new table with data contained in the large DEL file: import from staffdata.del of del modified by chardel"" coldel, decpt. insert into newstaff.
We are now ready to export the data from the NEWSTAFF table, and to simultaneously capture a database lock snapshot. The DB2 UDB snapshot monitor lets you capture information about the database and connected applications at the time the snapshot is taken. You can capture a snapshot from the command line processor (CLP) interface, from SQL table functions, or by using the snapshot monitor administrative application programming interface (API) functions in an application, such as a C or a C++ application. There are a number of snapshot request types, and each type returns its own set of monitor data. For example, a lock snapshot captures and returns database lock information.
We will execute two CLP scripts, one from each of two DB2 command windows (sessions). The first script will be referred to as Application 1, and the second script will be referred to as Application 2. Application 1 will execute the export operation and Application 2 will get a lock snapshot while Application 1 is running.
An example of the kind of output you can expect from this type of exercise is shown in Listing 1. The lock snapshot includes database summary information, application information and, of course, the lock information. Information is captured about every lock that was held at the time that the snapshot was taken. The one of interest here is the intent share (IS) lock held by the NEWSTAFF table. With this type of lock, concurrent applications can read from or write to the locked table.

Listing 1. A database lock snapshot reveals that IS locking is used during a data export operation
Application 1:

connect to sample

export to newstaffdata.del of del select * from newstaff

SQL3104N  The Export utility is beginning to export data to file "newstaffdata.del".
SQL3105N  The Export utility has finished exporting "22767" rows.

Number of rows exported: 22767

connect reset



Application 2:

connect to sample

get snapshot for locks on sample

            Database Lock Snapshot

...

List of Locks
...
 Lock Name                   = 0x0002000D000000000000000054
 Lock Attributes             = 0x00000000
 Release Flags               = 0x00000001
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 13
 Object Type                 = Table
 Tablespace Name             = USERSPACE1
 Table Schema                = MELNYK
 Table Name                  = NEWSTAFF
 Mode                        = IS

connect reset

The DB2 UDB import utility writes data from an input file to a database table or updatable view. The import utility uses the SQL INSERT statement (or the SQL UPDATE statement) to write data to the target table. The file from which the import utility reads data can be in one of several standard file formats, most commonly PC/IXF, DEL, or ASC. A non-delimited ASCII (ASC) file is a stream of ASCII characters; rows in the data stream are separated by row delimiters, and each column within a row is defined by a beginning and ending location. For more information about these file formats, see Exploit file type modifiers that are built into the DB2 data movement utilities. Besides specifying the format of the data that is to be read from the input file, you must also provide the name of that file, the target table name, and the mode under which the import utility is to execute. That is all the information that is required to initiate a basic DB2 import operation. Figure 2 shows a partial syntax diagram for the IMPORT command that includes the required parameters.

Figure 2. Partial syntax of the DB2 IMPORT command
IMPORT command
The main DB2 import modes include:
  • INSERT adds the input data to the table without changing any existing data.
  • INSERT_UPDATE adds new input data to the table, or updates existing rows with input data that has matching primary key values.
  • REPLACE deletes all existing data from the table (by truncating the data object) and then inserts the input data.
  • REPLACE_CREATE deletes all data from an existing table and then inserts the input data. If the table does not exist, this mode creates the table prior to inserting the input data. This mode is only valid with PC/IXF input files.
By default, the import utility is bound with the read stability (RS) isolation level (for a description of the DB2 isolation levels, see the sidebar). A DB2 import operation requires a database connection. Other applications that want to access data in the same table concurrently do not need to disconnect from the database; such applications can have both read and write access to the target table if the ALLOW WRITE ACCESS clause has been specified on the IMPORT command. On the other hand, ALLOW NO ACCESS (the default) prevents concurrent applications from accessing target table data. The import utility accomplishes this by requesting an exclusive (X) lock on the table at the beginning of an ALLOW NO ACCESS import operation. Listing 2 shows part of a lock snapshot taken during such an import operation into a table named NEWSTAFF2, which was created to be like the NEWSTAFF table. The listing does not show information about locks that are not specific to an import operation. Some of these are locks held by system catalog objects, or are so-called internal variation or plan locks that are more generally related to insert or update operations.

Listing 2. A database lock snapshot reveals that X locking is used during an ALLOW NO ACCESS data import operation
Application 1:

connect to sample

import from newstaffdata.del of del modified by chardel"" coldel, decpt.
  insert into newstaff2
  
SQL3109N  The utility is beginning to load data from file "newstaffdata.del".
SQL3110N  The utility has completed processing.
          "22767" rows were read from the input file.

SQL3221W  ...Begin COMMIT WORK. Input Record Count = "22767".
SQL3222W  ...COMMIT of any database changes was successful.

SQL3149N  "22767" rows were processed from the input file.
          "22767" rows were successfully inserted into the table.
              "0" rows were rejected.

Number of rows read         = 22767
Number of rows skipped      = 0
Number of rows inserted     = 22767
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 22767

connect reset



Application 2:

connect to sample

get snapshot for locks on sample

            Database Lock Snapshot

...

List of Locks
...

 Lock Name                   = 0x0002000E000000000000000054
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 255
 Hold Count                  = 0
 Lock Object Name            = 14
 Object Type                 = Table
 Tablespace Name             = USERSPACE1
 Table Schema                = MELNYK
 Table Name                  = NEWSTAFF2
 Mode                        = X

select lock_mode, lock_object_type, substr(table_name, 1, 16) as table_name
  from table(sysproc.snapshot_lock('SAMPLE',-1)) as lockinfo
  where lock_object_type in (1,2,4,5,6,11,18,19)

connect reset

Listing 2 also shows a sample query against the table that is returned by the SNAPSHOT_LOCK table function, an SQL administrative routine that provides convenient access to snapshot monitor information. The information in Table 1 is based on sample output from such a query. Lock modes and lock object types are defined in the sqlmon.h include file, which will help you to decipher the numerical equivalents that the table function returns.
Lockg ModeLock Object TypeTable Name
Next-key Share (NS)RowSYSTABLES
Intent Share (IS)TableSYSTABLES
Intent Share (IS)TableSYSCOLUMNS
Exclusive (X)TableNEWSTAFF2
In the case of an ALLOW WRITE ACCESS import operation (valid only in conjunction with the INSERT or the INSERT_UPDATE import mode), the import utility requests an intent exclusive (IX) lock on the target table when the first row of data is ready to be inserted. When a table holds an IX lock, the lock owner and concurrent applications can read and update data in that table.The import utility periodically commits inserted data (see Listing 3). Because the acquired lock does not persist across transaction boundaries, the import utility loses its IX table lock and must request another lock after each commit. If a concurrently connected application happens to be holding an incompatible row lock, the import utility pauses until that application commits or rolls back its transaction.

Listing 3. A database lock snapshot reveals that IX locking is used during an ALLOW WRITE ACCESS (INSERT_UPDATE) data import operation
Application 1:

connect to sample
create table newstaff like staff
alter table newstaff add primary key (id)

import from staff_data.del of del modified by chardel"" coldel, decpt.
  insert into newstaff
...
Number of rows committed    = 35

import from newstaffdata.del of del modified by chardel"" coldel, decpt.
  allow write access insert_update into newstaff

SQL3109N  The utility is beginning to load data from file "newstaffdata.del".
SQL3186W  Data was not loaded into the database, because the log was full or
the lock space was exhausted.  SQLCODE "-912" was returned.  A commit will be
attempted and the operation will continue if the commit is successful.
SQL0912N  The maximum number of lock requests has been reached for the
database.  SQLSTATE=57011
SQL3221W  ...Begin COMMIT WORK. Input Record Count = "1020".
SQL3222W  ...COMMIT of any database changes was successful.
...
SQL3186W
SQL0912N
SQL3221W  ...Begin COMMIT WORK. Input Record Count = "22356".
SQL3222W  ...COMMIT of any database changes was successful.

SQL3110N  The utility has completed processing.  "22767" rows were read from
the input file.
SQL3221W  ...Begin COMMIT WORK. Input Record Count = "22767".
SQL3222W  ...COMMIT of any database changes was successful.
...
Number of rows committed    = 22767

connect reset



Application 2:

connect to sample

select lock_mode, lock_object_type, substr(table_name, 1, 16) as table_name
  from table(sysproc.snapshot_lock('SAMPLE',-1)) as lockinfo
  where lock_object_type in (1,2,4,5,6,11,18,19)

connect reset

This example includes two import operations: one to populate the NEWSTAFF table with some data, and the other one to import more data in INSERT_UPDATE mode, which requires that a primary key be defined on the target table. Table 2, based on sample output from a query against the table that is returned by the SNAPSHOT_LOCK table function, shows that IX table locking is used during an ALLOW WRITE ACCESS data import operation.
Lockh ModeLock Object TypeTable Name
Next-key Share (NS)RowSYSTABLES
Exclusive (X)RowNEWSTAFF
Intent Share (IS)TableSYSTABLES
Intent Exclusive (IX)TableNEWSTAFF
The DB2 UDB load utility very efficiently writes data from one or more input files to a database table. The load utility also accepts input from named pipes, devices (such as tape devices), or user-defined cursors. The load utility, unlike the import utility, does not use SQL to write data to the target table; it writes formatted pages directly to the table and consequently, is much faster than the import utility. The file from which the load utility reads data can be in one of several standard file formats, most commonly PC/IXF, DEL, or ASC. For more information about these file formats.. Besides specifying the format of the data that is to be read from the input file, you must also provide the name of that file, the target table name, and the mode under which the load utility is to execute. INSERT mode adds the input data to the table without changing any existing data. REPLACE mode deletes all existing data from the table (by truncating the data object) and then inserts the input data. (There is also a RESTART mode and a TERMINATE mode, but we will not discuss them further here. For information about these modes.  That is all the information that is required to initiate a basic DB2 load operation. Figure 3 shows a partial syntax diagram for the LOAD command that includes the required parameters.

Figure 3. Partial syntax of the DB2 LOAD command
LOAD command
A DB2 load operation requires a database connection. Other applications that want to access data in the same table concurrently do not need to disconnect from the database; such applications can have read access to the target table if the ALLOW READ ACCESS clause has been specified on the LOAD command. The access is restricted to data that existed prior to the start of the load operation. On the other hand, ALLOW NO ACCESS (the default) prevents concurrent applications from accessing target table data. The load utility accomplishes this by requesting a super exclusive (Z) lock on the table and retaining this lock for the duration of the load operation (see Listing 4). When a table holds a Z lock, no concurrent application can read or update data in that table.

Listing 4. A database lock snapshot reveals that Z locking is used during an ALLOW NO ACCESS data load operation
Application 1:

connect to sample

load from newstaffdata.del of del modified by chardel"" coldel, decpt.
  insert into newstaff2

SQL3501W  The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.

SQL3109N  The utility is beginning to load data from file
"/home/melnyk/newstaffdata.del".

SQL3500W  The utility is beginning the "LOAD" phase at time "12/07/2004
17:39:18.311340".

SQL3519W  Begin Load Consistency Point. Input record count = "0".

SQL3520W  Load Consistency Point was successful.

SQL3110N  The utility has completed processing.  "22767" rows were read from
the input file.

SQL3519W  Begin Load Consistency Point. Input record count = "22767".

SQL3520W  Load Consistency Point was successful.

SQL3515W  The utility has finished the "LOAD" phase at time "12/07/2004
17:39:19.630882".

Number of rows read         = 22767
Number of rows skipped      = 0
Number of rows loaded       = 22767
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 22767

connect reset



Application 2:

connect to sample

get snapshot for locks on sample

            Database Lock Snapshot

...

List of Locks
...

 Lock Name                   = 0x0002000E000000000000000054
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 255
 Hold Count                  = 0
 Lock Object Name            = 14
 Object Type                 = Table
 Tablespace Name             = USERSPACE1
 Table Schema                = MELNYK
 Table Name                  = NEWSTAFF2
 Mode                        = Z

connect reset

In the case of an ALLOW READ ACCESS load operation, the load utility maintains a share (S) lock for the duration of the load operation, and then upgrades the S lock to a Z lock when data is being committed. The S lock allows concurrent applications that use read-only queries to access the target table, but applications attempting to write data to the table are prevented from doing so. Of course, you cannot request a REPLACE and ALLOW READ ACCESS load operation simultaneously: Because a load replace operation truncates all existing table data before loading new data, there is no pre-existing data to query!
ALLOW READ ACCESS operations are great when you have a huge amount of data to load. Such operations can take a substantial amount of time, and you will appreciate having virtually uninterrupted access to pre-existing table data while the utility is running. Moreover, if an ALLOW READ ACCESS load operation fails or is aborted, pre-existing table data can still be read.
You can use the LOCK WITH FORCE option on the LOAD command to force applications holding conflicting locks on the target table off the system; in this way, your load operation avoids risking a timeout while waiting for a lock. At the start of an ALLOW NO ACCESS load operation, the utility may force concurrent applications that are trying to read from or write to the target table. At the start of an ALLOW READ ACCESS load operation, however, the utility may force applications that are trying to write to the table, and towards the end of the load operation (when data is being committed), the utility may force applications that are trying to read from or write to the target table.
Because locks are not persistent, the load utility also uses table states to control access to tables; these states remain in effect even if a load operation aborts. For more information about table states, see Demystifying table and table space states.