IBM Support

Steps to determine whether APAR IV53366 is affecting your DB2 databases

Flashes (Alerts)


Abstract

If you are not using column-organized tables, no further action is required.

If you are currently using column-organized tables with CHAR or GRAPHIC columns, it is important that you perform the following steps for every DB2® instance running on DB2 Versions 10.5, 10.5.0.1 (Fix Pack 1), and 10.5.0.2 (Fix Pack 2). If your database was created and used on Fix Pack 3 or later only, you do not need to apply these guidelines. These steps include determining whether any tables are affected by APAR IV53366, correcting any identified tables, and avoiding future issues while on Fix Pack 2 or earlier.

Content

Important: If you are using the GA level of DB2 Version 10.5, be sure to install Fix Pack 1 or Fix Pack 2 prior to applying the guidelines that are described in this document. You must complete these steps prior to applying DB2 Version 10.5.0.3 (Fix Pack 3) or later.

  1. Download the db2checkCOL utility from https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?source=DB2COLT105. Copy the db2checkCOL.tar.gz file from the <cdroot>/server/ directory into your sqllib/bin directory. To extract the db2checkCOL utility, run the following commands:
      • gunzip db2checkCOL.tar.gz
      • tar -xf db2checkCOL.tar
  2. Run the db2checkCOL utility against each of your databases. For complete details on how to use this utility, see db2checkCOL - Check database for APAR IV53366 in this technote. This information supersedes any previous version or the README file that refers to db2checkCOL version 1.0.
    • If db2checkCOL determines that APAR IV53366 does not affect any of your databases, and those databases contain column-organized tables, it is recommended that you apply Fix Pack 3 (or later) immediately. As long as you are running a pre-Fix Pack 3 data server, you must follow the instructions in How to load column-organized tables in DB2 Versions 10.5, 10.5.0.1 (Fix Pack 1), and 10.5.0.2 (Fix Pack 2). If, after running db2checkCOL, you continue to run a pre-Fix Pack 3 data server for an extended period of time, it is recommended that you rerun db2checkCOL just prior to applying Fix Pack 3.
    • If db2checkCOL determines that APAR IV53366 does affect one or more of your databases, you can proceed in one of two ways, which are fully described in separate sections below:
        • Option A: Export, then apply Fix Pack 3 and reload
          1. Export the data from all affected tables in DB2 Versions 10.5, 10.5.0.1 (Fix Pack 1), or 10.5.0.2 (Fix Pack 2).
          2. Correct the tables.
          3. Apply Fix Pack 3.
          4. Reload the tables.
        • Option B: Correct affected tables in Fix Pack 1 or 2, then apply Fix Pack 3
          1. Correct all affected tables in DB2 Versions 10.5.0.1 (Fix Pack 1) or 10.5.0.2 (Fix Pack 2).
          2. Apply Fix Pack 3.
      Use Option A unless any of the following statements is true:
    • You are unable to apply Fix Pack 3 immediately.
    • You do not have the space to store the export files for all affected tables while applying Fix Pack 3.
    • It is a violation of your company’s policy to store data outside of a database (that is, in export files).

After you have corrected your tables and db2checkCOL no longer identifies any affected tables, consider creating a new database backup image (or a set of table space backup images) to serve as a new baseline for future recovery operations.

Important: Any backup images of affected table spaces that were created prior to fixing affected tables should not be used for recovery purposes.

Option A: Export, then apply Fix Pack 3 and reload

With this option, you complete some steps on DB2 Versions 10.5.0.1 (Fix Pack 1) or 10.5.0.2 (Fix Pack 2) before applying Fix Pack 3, and some steps after applying Fix Pack 3. Ensure that you complete the necessary steps on Fix Pack 1 or Fix Pack 2 for all databases in your instance. After that, use the db2checkCOL utility to verify that your databases are no longer affected by APAR IV53366. Then apply Fix Pack 3 and complete the remaining steps.

Complete the following steps before applying Fix Pack 3:
  1. If the db2checkCOL utility identifies one or more affected tables in your database, choose one of the following actions for each table:
    • If you no longer need the table, drop it.

      • DROP TABLE yourtable
    • If you do not need the data in the table (because it is a temporary table, a staging table, and so on), delete all rows and existing dictionaries from the table by running the following LOAD command. Do not use the TRUNCATE statement.

      • LOAD FROM /dev/null OF DEL REPLACE RESETDICTIONARY INTO yourtable [COPY NO | NONRECOVERABLE];
    • If you do need the data, export the table data to a file by using the EXPORT command. Then either drop the existing table and create a new target table for the load operation or use the LOAD command that is described in the previous bullet to delete all rows and dictionaries from your existing table (yourtable).

      • Important: Do not reload any of the affected tables until after applying Fix Pack 3.
  2. After completing one of these actions for each affected table in the database, rerun the db2checkCOL utility to verify that it no longer identifies any affected tables.
  3. Repeat Steps 1 and 2 for all databases in the instance.
  4. Apply Fix Pack 3 (or later).
    Important: Do not apply Fix Pack 3 to a DB2 instance until db2checkCOL has been run against all of the databases on that instance and no affected tables have been found.

After applying Fix Pack 3, load the exported data back into your tables. If assistance is needed at any time, contact IBM Support.

Option B: Correct affected tables in Fix Pack 1 or 2, then apply Fix Pack 3

1. If the db2checkCOL utility identifies one or more affected tables in your database, you can choose one of the following actions for each table:
  • If you no longer need the table, drop it.

    • DROP TABLE yourtable
  • If you do not need the data in the table (because it is a temporary table, a staging table, and so on), delete all rows and existing dictionaries from the table by running the following LOAD command. Do not use the TRUNCATE statement.

    • LOAD FROM /dev/null OF DEL REPLACE RESETDICTIONARY INTO yourtable [COPY NO | NONRECOVERABLE];
  • If you do need the data in the table, reload the table. First, export the table data to a file by using the EXPORT command. Then either drop the existing table and create a new target table for the load operation or use the LOAD command that is described in the previous bullet to delete all rows and dictionaries from your existing table (yourtable). When loading the table, be sure to follow the instructions in How to load column-organized tables in DB2 Versions 10.5, 10.5.0.1 (Fix Pack 1), and 10.5.0.2 (Fix Pack 2). If assistance is needed, contact IBM Support.

2. Apply Fix Pack 3 as soon as possible, but as long as you are running Fix Pack 2 (or earlier), be sure to take the following steps:
    1. Ensure that future load operations against your tables are carried out according to the instructions in How to load column-organized tables in DB2 Versions 10.5, 10.5.0.1 (Fix Pack 1), and 10.5.0.2 (Fix Pack 2).
    2. Rerun the db2checkCOL utility if any of the following statements is true:
      • You restored or transported one or more table spaces that were not checked previously.
      • You have a table space flagged as "not checked" by the tool, and you performed actions to move it to an accessible state.
      • You have a table flagged as "not checked" by the tool, and you performed actions to move it to an accessible state.
      • You are unsure whether you correctly followed Steps 1 or 2.1.
    3. If you continue to run a pre-Fix Pack 3 data server for an extended period of time, it is recommended that you rerun db2checkCOL just prior to applying Fix Pack 3.

How to load column-organized tables in DB2 Versions 10.5, 10.5.0.1 (Fix Pack 1), and 10.5.0.2 (Fix Pack 2)

Follow these steps when loading column-organized tables that have CHAR or GRAPHIC columns of length greater than 2 in DB2 Versions 10.5, 10.5.0.1 (Fix Pack 1), and 10.5.0.2 (Fix Pack 2).

The term initial load operation, as used in this document, refers to any one of the following operations:

  • The first load operation into a newly created empty table
  • A load operation into a table that contains any number of rows that were added through insert, ingest, or import operations, but never through a load operation
  • A load replace operation without the KEEPDICTIONARY option

To load a table, follow these steps:
  1. If you are performing an initial load operation, do not load more than 500,000 rows, and ensure that the util_heap_sz database configuration parameter is set to at least 1,000,000 pages for each 100,000 rows to be loaded.
  2. During the initial load of the table, do not run other load operations (or other DB2 utilities that consume utility heap memory) concurrently.
  3. (Optional) You can rerun the db2checkCOL utility at any point to verify that these instructions have been followed correctly.

Notes
  • These guidelines apply only to an initial load operation on a table with CHAR or GRAPHIC columns, or to a LOAD REPLACE command; they do not apply to any incremental LOAD INSERT command that loads additional data into the table.
  • Set the util_heap_sz database configuration parameter to a minimum of 1,000,000 pages for each 100,000 rows to be loaded during the initial load operation. If you also specify the keyword AUTOMATIC, the database manager can use database overflow memory if demand for the utility heap exceeds the specified number of pages, thereby resulting in better compression performance. The following example shows you how to set util_heap_sz if your initial load operation loads 500,000 rows:


    • UPDATE DB CFG USING UTIL_HEAP_SZ 5000000 AUTOMATIC;
  • If your input data file is larger than the 500,000 row limit described in Step 1 above, you can run two load operations, one that reads 500,000 (or fewer) rows and creates dictionaries, and another that loads all of the data in the file. The first of these two load operations qualifies as an initial load operation. For example:


    • LOAD FROM yourdata.del OF DEL
     ROWCOUNT 500000
     REPLACE RESETDICTIONARYONLY INTO     yourtable;

    LOAD FROM     yourdata.del OF DEL
     REPLACE KEEPDICTIONARY INTO     yourtable;


db2checkCOL - Check database for APAR IV53366

Scans a database and detects any tables that are affected by APAR IV53366.

Prerequisites
-------------

On the Linux operating system, set the LD_LIBRARY_PATH environment variable to ~/sqllib/lib64

On the AIX® operating system, set the LIBPATH environment variable to ~/sqllib/lib64

Authorization
-------------

One of the following authority levels is required to run the command:

  o SYSADM
  o SYSCTRL
  o SYSMAINT
  o SYSMON

The input user ID must hold one of the following authorizations:

  o DATAACCESS authority
  o SELECT privilege on the system catalog tables and all column-organized tables in the database

Write access to the current directory is also required. On average, 500 KB of disk space per column-organized table could be required for temporary files.

Required Connection
-------------------

This command establishes a database connection.

Command syntax
--------------

>>-db2checkCOL--database-alias--+------------------+-------><
                                '-userid--password-'  

Command parameters
------------------

database-alias
    Specifies the alias of the local database that is to be scanned.

userid
    Specifies the user ID that is to be used to connect to the database.

password
    Specifies the password for the user ID.


Usage notes
-----------

The specified database must be a local database.

The db2checkCOL utility determines whether any tables are affected by APAR IV53366.

The command returns one of the following values:

Table 1. Codes returned by the db2checkCOL command
-------------------------------------------------------------------------------
Return  Description
 code
-------------------------------------------------------------------------------
   0    No tables are affected by APAR IV53366.
-100    Contact IBM Support.
-200    One or more tables are affected by APAR IV53366. Follow the
        instructions in this technote.
-300    Returned when it is not possible to scan or process all of the
        column-organized tables in the database (for example, because a table
        space is offline, or if there is insufficient disk storage for
        temporary files). Rerun the command when the affected tables or table
        spaces are back online.
-400    Contact IBM Support.
-500    Returned when the authorization ID of the command does not hold one
        of the required authority levels. Rerun the command with a user ID that
        holds one of the required authority levels.
-600    Returned when a syntax error or other incorrect usage is encountered.
        Correct the syntax and rerun the command.
-700    Contact IBM Support.

The db2checkCOL utility is an online tool, but it is recommended that you run it during nonpeak periods. Expected run time can range from seconds to approximately an hour, depending on the number of tables and the amount of concurrent activity.


Examples
--------

The following examples show sample db2checkCOL output.


Return code 0 - No tables are affected by APAR IV53366
------------------------------------------------------

    Starting Phase 1 of 3: Query Catalog Tables

    Starting Phase 2 of 3: Loading Table Descriptors

    Starting Phase 3 of 3: Analyzing Table Descriptors


    Database was fully checked. No tables have been
    identified for further action.

    Exiting with return code 0.


Return code -200 - Some tables are affected by APAR IV53366
-----------------------------------------------------------

    Starting Phase 1 of 3: Query Catalog Tables

    Starting Phase 2 of 3: Loading Table Descriptors

    Starting Phase 3 of 3: Analyzing Table Descriptors

    ERROR: Table "BLU     "."B_CUST_BASE" is affected.
    ERROR: Table "BLU     "."B_CUST_NATL_ACCT" is affected.

    Database was fully checked. A total of 2 affected tables
    were found.

    Exiting with return code -200.


-------------------------------------------------------------

db2checkCOL version 2.0

Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.

Licensed Materials - Property of IBM
Copyright IBM Corp. 2014 All Rights Reserved.
US Government Users Restricted Rights
Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Database Objects\/Config - Database","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"}],"Version":"10.5","Edition":"Advanced Enterprise Server;Advanced Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
25 September 2022

UID

swg21663252

Page Feedback