Quickly loading a large standard table

You can change a large, existing standard table into a nonlogging table and then load the table.

To quickly load a large, existing standard table:

  1. Drop indexes, referential constraints, and unique constraints.
  2. Change the table to nonlogging.

    The following sample SQL statement changes a STANDARD table to nonlogging:

    ALTER TABLE largetab TYPE(RAW);
  3. Load the table using a load utility such as dbexport or the High-Performance Loader (HPL). For more information about dbexport and dbload, see the IBM® Informix® Migration Guide. For more information about HPL, see the IBM Informix High-Performance Loader User's Guide.
  4. Perform a level-0 backup of the nonlogging table. You must make a level-0 backup of any nonlogging table that has been modified before you convert it to STANDARD type. The level-0 backup provides a starting point from which to restore the data.
  5. Change the nonlogging table to a logging table before you use it in a transaction. The following sample SQL statement changes a raw table to a standard table:
    ALTER TABLE largetab TYPE(STANDARD);
    Warning: Do not use nonlogging tables within a transaction where multiple users can modify the data. If you need to use a nonlogging table within a transaction, either set Repeatable Read isolation level or lock the table in exclusive mode to prevent concurrency problems.

    For more information about standard tables, see the previous section, Advantages of logging tables.

  6. Re-create indexes, referential constraints, and unique constraints.

Copyright© 2019 HCL Technologies Limited