The Load tool enables DB2 to organize various suspend problem solutions

To speed up the loading of data, many DBAs prefer to use the load tool for data migration and loading, but the load utility brings us some problems as well, and today focuses on the solution when we encounter problems when we use the Load tool again.
As we all know, the load process is divided into 4 stages: Load/build/delete/index copy. The load phase is a format that parser the source files into the physical data store, directly into the page, not through the DB2 engine, and the load phase checks the table definition, and the data that violates the definition is not loaded into the table. The build phase suggests that the index (if a loaded table has an index) checks for uniqueness constraints, and that data that violates uniqueness is deleted during the delete phase. The index copy phase copies the index data from the specified temporary tablespace to the original tablespace, and the index copy is only adapted to the Allow Read access scenario. The 4 phases of load will be recorded in the messages file. and DB2 load if not add nonrecoverable may cause the table space to become backup pending and so on.
This article mainly describes the COPY no/copy yes/nonrecoverable option provided by the DB2 LOAD command, and how to use the registry variables associated with it.
Because the load minimizes log records, the database requires archived log features when restoring online backups, and for this database load operation, the table is using Rollforward after the load operation is not performed. When the command rolls forward to the archive log, it is placed in an unhealthy state due to a missing log, and DB2 provides the following options for the LOAD command:
· COPY NO (default)
· COPY YES
· Nonreverable
1. Make a full backup of the database:
Start with a full backup of the SAMPLE database that has the roll forward recovery model enabled:
E:\TEST>DB2 Backup DB Sample
Backup succeeded. The time stamp for this backup image is: 20051230174105
At this point, we see that a sample.0 subdirectory is generated in the current directory, indicating a full database backup. Examples of these phenomena are described below:
2. Backup pending on Copy no:
At the end of the LOAD operation, the table space in which the table is located is placed in the backup pending state, where the tables can be SELECT, but not the UPDATE and DELETE operations. In order for the table state to return to normal, you must manually perform a backup command on its tablespace to remove the suspend state. Because this option is the default option, if not specified in the LOAD command, the default is to use this option, such as:
E:\TEST>DB2 Connect to sample
E:\TEST>DB2 load from Staff.del to del INSERT into staff
E:\TEST>DB2 List tablespaces
Table Space id = 2
Name = USERSPACE1
Type = System Admin Space
Content = Any data
Status = 0x0020
Detailed Explanation:
Backup pending:
E:\TEST>DB2 Select COUNT (*) from staff
1
-----------
70
1 records have been selected.
E:\TEST>DB2 Update staff set id=335 where id=340
db21034e This command is treated as an SQL statement because it is not a valid command line Processor command. During SQL processing, it returns:
The sql0290n does not allow access to the table space. sqlstate=55039
After you manually perform a backup operation on the USERSPACE1 tablespace, the table space status will be normal and the attempt to update the operation will succeed:
E:\TEST>DB2 Backup DB sample tablespace (Userspace1)
Backup succeeded. The time stamp for this backup image is: 20051230184841
After the command completes, you can see a subdirectory of the sample.3 in the current directory, indicating that a tablespace-level backup is generated.
E:\TEST>DB2 Connect to sample
E:\TEST>DB2 List tablespaces
Table Space id = 2
Name = USERSPACE1
Type = System Admin Space
Content = Any data
Status = 0x0000
Detailed Explanation:
Normal
3. About the restoration of COPY no pending:
and the resulting backup of the tablespace can be used to restore the tablespace state to normal when the database is reset by a roll forward operation, and restore the LOAD operation to modify the table. If the current rolling database exceeds the load time point, the tablespace will be reset to the pending state:
E:\TEST>DB2 Restore DB sample taken at 20051230174105
The db20000i RESTORE DATABASE command completed successfully.
E:\TEST>DB2 Rollforward DB sample to end of logs and stop
SQL1271W database "SAMPLE" has been restored, but one or more tablespaces are offline on node "0"
E:\TEST>DB2 Connect to sample
E:\TEST>DB2 List tablespaces
Table Space id = 2
Name = USERSPACE1
Type = System Admin Space
Content = Any data
Status = 0x0100
Detailed Explanation:
Undo Suspend
At this point, you can use this tablespace-level backup for recovery operations:
E:\TEST>DB2 Restore DB Sample tablespace (Userspace1) taken at 20051230184841
The db20000i RESTORE DATABASE command completed successfully.
E:\TEST>DB2 Connect to sample
E:\TEST>DB2 List tablespaces
Table Space id = 2
Name = USERSPACE1
Type = System Admin Space
Content = Any data
Status = 0x0080
Detailed Explanation:
Roll forward Suspend:
E:\TEST>DB2 Rollforward DB sample to end of logs and stop tablespace (USERSPACE1)
Roll forward status
Enter Database alias = sample
The number of nodes has returned to the status = 1
Node Number = 0
Roll forward status = not pending
The next log file to read =
Processed log file =-
Last implemented Transaction = 2005-12-30-10.47.10.000000
Db20000i rollforward Command completed successfully.
E:\TEST>DB2 Connect to sample
E:\TEST>DB2 List tablespaces
Table Space id = 2
Name = USERSPACE1
Type = System Admin Space
Content = Any data
Status = 0x0000
Detailed Explanation:
Normal
E:\TEST>DB2 Update staff set id=335 where id=340
Db20000i SQL Command completed successfully.
The visible tablespace status is normal and the table can perform an update operation.
4. About COPY YES:
At the end of the load operation, DB2 automatically makes a backup operation of the table space in which the table is located, so that when the load is finished, the table space in which the table resides is no longer in the backup suspend state, but in the normal state. However, because of the backup operation, this LOAD operation will take longer than is not backed up. Such as:
E:\TEST>DB2 Connect to sample
E:\TEST>DB2 load from Staff.del of Del inserts into staff copy yes.
E:\TEST>DB2 List tablespaces
Table Space id = 2
Name = USERSPACE1
Type = System Admin Space
Content = Any data
Status = 0x0000
Detailed Explanation:
Normal
E:\TEST>DB2 Select COUNT (*) from staff
1
-----------
105
1 records have been selected.
At this point, you can see a subdirectory of the sample.4 in the current directory, indicating that a backup generated by the load operation is generated, and that the backup will be used to re-create the database modification for the load operation when the database rolls forward recovery operation. Such as:
E:\TEST>DB2 Restore DB sample taken at 20051230174105
The db20000i RESTORE DATABASE command completed successfully.
E:\TEST>DB2 Rollforward DB sample to end of logs and stop
Roll forward status
Enter Database alias = sample
The number of nodes has returned to the status = 1
Node Number = 0
Roll forward status = not pending
The next log file to read =
The processed log file = S0000002. log-s0000003. LOG
Last implemented Transaction = 2005-12-30-11.48.26.000000
Db20000i rollforward Command completed successfully.
E:\TEST>DB2 Connect to sample
E:\TEST>DB2 List tablespaces
Table Space id = 2
Name = USERSPACE1
Type = System Admin Space
Content = Any data
Status = 0x0000
Detailed Explanation:
Normal
E:\TEST>DB2 Select COUNT (*) from staff
1
-----------
105
1 records have been selected.
This indicates that the backup under Sample.4 was used for roll forward recovery operations, and the record that the LOAD operation inserted into the database was recreated.
5. About nonrecoverable:
This option flags the LOAD operation as not recoverable, that is, the database cannot be recovered through subsequent roll forward operations. When the LOAD operation is complete, the database is neither in the backup pending state nor does it produce any backups.
E:\TEST>DB2 Connect to sample
E:\TEST>DB2 load from Staff.del to del INSERT into staff nonrecoverable
E:\TEST>DB2 List tablespaces
Table Space id = 2
Name = USERSPACE1
Type = System Admin Space
Content = Any data
Status = 0x0000
Detailed Explanation:
Normal
Although the state of the tablespaces and tables is normal after the load, the roll-forward operation skips the processing of the load transaction and marks the load's table as invalid without any action on the table if a roll forward command is required to recover the database in the future. Such as:
E:\TEST>DB2 Restore DB sample taken at 20051230174105
The db20000i RESTORE DATABASE command completed successfully.
E:\TEST>DB2 Rollforward DB sample to end of logs and stop
Roll forward status
Enter Database alias = sample
The number of nodes has returned to the status = 1
Node Number = 0
Roll forward status = not pending
The next log file to read =
The processed log file = S0000002. log-s0000003. LOG
Last implemented Transaction = 2005-12-30-12.19.55.000000
Db20000i rollforward Command completed successfully.
E:\>DB2 Connect to sample
E:\TEST>DB2 List tablespaces
Table Space id = 2
Name = USERSPACE1
Type = System Admin Space
Content = Any data
Status = 0x0000
Detailed Explanation:
Normal
E:\>DB2 SELECT * FROM staff
ID NAME DEPT JOB YEARS SALARY COMM
------ --------- ------ ----- ------ --------- ---------
sql1477n Cannot Access table "Liwenli. STAFF ". sqlstate=55019
This indicates that the table is not operational, and that the table is restored only if the table is deleted, rebuilt, or a full or tablespace backup of the database after the LOAD operation Point is used.
5. Introduction to registry variable Db2_load_copy_no_override:
Additionally, DB2 provides a registry variable: Db2_load_copy_no_override, which sets the default option COPY of LOAD to nonrecoverable or copy YES. Examples of the use of specific methods are:
To set the method to COPY YES:
E:\test>db2set db2_load_copy_no_override= "COPY YES to E:\TEST"
E:\TEST>DB2 Terminate
E:\test>db2set
Db2_load_copy_no_override=copy YES to E:\TEST
E:\TEST>DB2 load from Staff.del to del INSERT into staff
sql27966w db2_load_copy_no_override Registry Variable value "copy YES to E:\TEST" overrides the copy NO parameter specified in LOAD.
Method set to Nonrecoverable:
E:\test>db2set db2_load_copy_no_override=nonrecoverable
E:\TEST>DB2 Terminate
E:\test>db2set
Db2_load_copy_no_override=nonrecoverable
E:\TEST>DB2 load from Staff.del to del INSERT into staff
The sql27966w db2_load_copy_no_override registry variable Value "Nonrecoverable" overrides the COPY NO parameter specified in LOAD.
With the details and examples of the COPY no,copy Yes and nonrecoverable parameters for LOAD and DB2 registry variables Db2_load_copy_no_override, we have mastered their functionality and usage. Because the load operation has little logging characteristics, you should be aware of selecting the appropriate option from the database where roll-forward recovery is enabled to ensure the availability of the table where the load operation is performed.




DB2 LOAD Tool usage Tips collection

In the day-to-day use of the DB2 database, it is important to move the data, so the LOAD tool is often used at this time. The DB2 load utility is very powerful and has outstanding advantages in many ways compared to other tools, which makes the load utility an irreplaceable role in DB2 data movement, especially in situations where large-scale data processing is needed. Here are some tips to help you use the LOAD tool in conjunction with my hands-on experience.

options that affect LOAD performance

One of the great advantages of the load tool compared to other data mobility tools is the ability to deliver superior performance, mainly because of the data page-level processing that load takes to load data, bypassing multiple processing levels of the database management system, which can greatly improve performance. In addition to this feature of the load utility itself, we can further improve its performance by reasonably setting some of the options for load. The following lists some of the options that affect the LOAD performance and their reasonable settings.

Cpu_parallelism N

This option specifies that one load uses n CPUs concurrently to handle load. When the load processing data is large and the operating system load is not high, this parameter can be used to specify multiple CPU concurrency in the execution of the table in the process of parsing, transformation, formatting and other content to improve efficiency. If more than one load tool is started at the same time, be aware that all load tools specify this parameter is best not to exceed the total number of logical CPUs in the operating system (the CPU specified here is lcpu-, the logical CPU).

If this option is not set, DB2 automatically allocates the number of CPUs based on the number of CPUs in the current operating system.

DATA BUFFER buffersize

Use this option to specify the maximum number of data caches that the LOAD tool can use, in units of 4k. We can imagine that the greater the amount of data being processed and the more data caches we allocate to the load, the better the performance of the load will be if we do not exceed the idle physical memory of the operating system. However, this value setting is limited by the parameter UTIL_HEAP_SZ of the database. Since there are multiple tools for using UTIL_HEAP_SZ, it is generally recommended that the data cache for a LOAD assignment not exceed the UTIL_HEAP_SZ 50%, and in actual settings, it is best to determine according to UTIL_HEAP_SZ's actual situation. This can be judged by observing the size of the tool heap used in the database snapshot and the high water level size.

In addition, this option is not simple to set the larger the better, because the data cache when the maximum size is set, even if the increase in its sizes will not improve performance, because the bottleneck of performance is not cached. So you need to do a lot of testing in the actual production to find a value that's best for you.

Disk_parallelism N

Use this option to specify that the LOAD tool can improve performance with the ability to perform concurrent I/O to multiple containers in a tablespace. Make the appropriate settings based on the number of containers in the table space.

Modified by Anyorder

This file modifier to improve performance by using the preset sorting results in the input file. If you enter a file that is derived from data that is used in the export tool to use a certain sort predicate, performance can be increased (several times to dozens of times times). And this file modifier can be used for various input file formats.

Modified by FastParse

This file modifier improves performance by lowering the check for input data, and if you enter data that does not differ from the structure or type of the target table, you can improve performance by setting this option. This file modifier can only be used in ASC or DEL format files.

Modified by Norowwarnings

You can use this file modifier to improve performance if you expect a large number of warning to occur during the LOAD process.

Savecount

This option lowers the load on the set of consistent recovery points, which can be used to improve performance in the case of large data volumes, and is set appropriately based on the amount of data processed. If the data you want to load is 10 million rows, you can set this value to 10000. This file modifier cannot be used in conjunction with the Anrorder file modifier.

USER tablespace

Use this option to develop a system temporary tablespace to improve the performance of your data build when a large number of indexes need to be built in a table that requires LOAD, and the table or index itself is not a large table space.


use cursors (cursor) to improve LOAD performance

The load tool can be a cursor (CUROSR) When loading data, except that it can be a IXF, ASC, and del type of file. If you need to save the data from one table to another, and the corresponding columns are of the same type, then we can use the cursor to move the data. The way that cursors can improve the efficiency of data loading is because the data does not need to be saved to disk in a file way. Instead, it reaches the destination datasheet directly from the source datasheet, which saves two I/O processes (the process of exporting data to the disk and reading data files from disk when data is loaded), Thus greatly improving the efficiency of data processing.
Examples are as follows:

Create the SQL file Test.sql, which reads as follows:
Listing 1. SQL Statement--test.sql

DECLARE CURSOR mycur for SELECT * from Tab1name;
Load from Mycur of cursor inserts into Tab2name;

Note: The SQL statement in the cursor declaration determines its content autonomously according to the needs of the application, and the option of the load can be determined independently according to various requirements.

Execute the following command in the DB2 environment:
Listing 2. Execute SQL statement

DB2 Connect to DBName
DB2? TVF Test.sql


Conversion of code pages

Moving data between different databases can often cause code page inconsistencies, which need to be handled separately according to different circumstances. If the file that holds the data is in ASC or del format, you need to use the file modifier: codepage, which specifies the value of the code page of the source database for the data.
Listing 3. Sample 1--del File

LOAD from Test ' del modified by codepage=819 inserts into TabName

If the file that holds the data is in the IXF format, no options are required for the load tool, and the conversion of the code page is completed automatically during data loading. If the load cannot succeed if the data format changes in the project that executes the code page conversion, the Forcein file modifier is used, and the example is as follows:
Listing 4. Sample 2--IXF File

LOAD from Test ' IXF modified by Forcein inserts into TabName

Note: You can avoid this problem by choosing to use the IXF format and specifying the Forcein file modifier when the data loaded is in exactly the same table structure as some non-null characters or characters are truncated.


Location of error rows

Under the DB2 V8 and DB2 V9.1 environment, it is important to find fault rows and fault columns in the source database to analyze the cause of the failure due to the fact that some of the columns of some rows may not conform to the target table type or format when the load load data is executed, causing them to be truncated or rejected. However, the error message that is output in this failure scenario may be as follows:
The form 5. Execution error message

sql3125w because the data is longer than the target database column, the first "f4-6" Row "5"
The character data in the column is truncated.
The sql3110n utility has finished processing. Read the "13" line from the input file.
SQL3519W starts to mount the consistency point. Enter the number of records = "13".
sql3520w "Mount consistent point" succeeded.
sql3515w in Time "2009-01-04 11:18:53.301919", the utility has completed "LOAD".
There is at least one warning message in the sql3107w message file.

Where the failed row is identified as "f4-6," which indicates that the data on line 6th of the CPU with ID 4 fails. Then through this information we can not find the fault line from the source file, we can only through the total line number identification to find the fault line, so in this case only through the single CPU processing to find Barrier line.

This requires that the Load tool's cpu_parallelism option be set to 1, which guarantees that the load will always apply to only one CPU in the process of loading data, then the CPU portion of the line ID is always "F0" and the line portion is the total number of rows.

In DB2 V9.5, the sql3227w message is also exported to help locate the total number of rows to avoid the above problems, as shown below.
The form 6. Execution Error Details

sql3125w because the data is longer than the target database column, the first "f4-6" Row "5"
The character data in the column is truncated.
The sql3227w record mark "f4-6" refers to the user record number "16".
The sql3110n utility has finished processing. Read the "53" line from the input file.
SQL3519W starts to mount the consistency point. Enter the number of records = "53".
sql3520w "Mount consistent point" succeeded.
sql3515w in Time "2009-01-04 11:18:53.301919", the utility has completed "LOAD".
There is at least one warning message in the sql3107w message file.


processing that cannot be executed in parallel with the query

You cannot normally perform any access to the target table of the load tool while load is being executed. However, there are special circumstances as follows.

If the query against the target table is executed first, the LOAD tool is always unable to execute, regardless of the option specified. The LOAD tool does not execute until the query for the target table finishes.
If the query against the target table is executed after the load tool is first executed and the query is executed while the load tool is running, the query statement can be executed as soon as the load tool specifies the Allow Read access option.

So the load utility is not a good tool, and the best way to use the Import tool instead of the load tool is to start the parallel query at any time without accurately determining the order in which the load tool and query statements are executed, and also to execute the query concurrently.


observe the LOAD state being executed

When one or more LOAD tools are executing, the command "list utilities" can be implemented if you need to observe the implementation of these tools in detail. Examples are below.
Listing 7. LOAD Command Execution status

>DB2 List Utilities Show Detail
Identification = 5
Type = LOAD
Database name = SAMPLE
Sub-area code = 0
Description = OFFLINE LOAD DEL AUTOMATIC indexing INSERT COPY NO YUAN. T1
Start time = 2009-01-05 10:44:57.203583
Status = Execute
Call type = user
Progress monitoring: Phase Number = 1 Description = SETUP Total Work = 0 bytes Completed work = 0 bytes
Start time = 2009-01-05 10:44:57.203592
Stage number [Current] = 2 Description = LOAD Total Work = 2174631 rows
Completed work = 1106547 rows start time = 2009-01-05 10:44:57.248663

There are only two stages in the output above, and there will be a third stage-build phase if the target table has an index. Evaluate the remaining time of the LOAD tool being executed primarily by comparing the size and proportions of the total work and completed work by two values.


using LOAD to observe the state of a table

We know that observing the state of a database can be done through snapshots or list DB commands, and the State of the table space can be viewed through the snapshot or list tablespace commands, while the state of the watch table usually only knows that the snapshot can reflect some of the situations accessed by the table, and when the table is not available, There is no List command to use when you need to get the table's fault status.

So in this case, we can use the LOAD QUERY tool to implement it. Examples are as follows:
Listing 8. Observe the state of a table

C:\Program files\ibm\sqllib\bin>db2 "SELECT * from T1"

Deptnumb deptname MANAGER Division LOCATION
-------- -------------- ------- ---------- -------------
SQL0668N is not allowed on table "YUAN. T1 "To perform the action, the reason code is" 3 ". sqlstate=57016
C:\Program files\ibm\sqllib\bin>db2 load query table t1
SQL3501W the table space in which the table resides will not be placed in the backup suspend state because the database is disabled for forward recovery.
The sql3109n utility is starting to load data from the file "E:\t1.out".
sql3500w in the time "2009-01-05 10:52:56.661277", the utility starts "LOAD".
SQL3519W starts to mount the consistency point. Enter the number of records = "0".
sql3520w "Mount consistent point" succeeded.
sql3005n processing was interrupted.
The sql3532i load utility is currently in the "load" phase.
Number of rows read = 0
Skip rows = 0
Load Number = 0
Number of rejected rows = 0
Number of rows deleted = 0
Number of implementation lines = 0
Warning Number = 0
Table Status: Mount suspend

From this, we can judge that the current table cannot be accessed because it is in the "Mount suspend" state, then we can use the Load tool to restart or terminate (terminate) the failure to access the table.

use LOAD to quickly clear a table

I need to empty the data in a table when I don't need it. And we can choose a variety of ways to do this work:

Delete data in table: Delete from tablename
Do not log deletes the data in the table: ALTER TABLE tabname activate not logged initially;delete from TableName
Modify table does not log way: ALTER TABLE tabname activate not logged with empty table
Load Replace mode: Load from Empfile of Del to into TabName

Note: File Empfile is empty

"1" Way of usability is very poor, will consume a lot of time and log space, generally not used. Unless you need to delete the data selectively.

The "2" approach can be used, but it is too cumbersome, especially to note that two statements must be executed in the same transaction.

The "3" approach is a good way to quickly empty a datasheet.

The "4" approach is also a good way to quickly empty the datasheet, and for "3", the process is more manageable because the load supports fault control options such as restart and terminate.

So it is recommended to use the "4" method to quickly clear a table.