Quickly copy a table with data in DB2

Keep a copy before changing the data

Sometimes you need to keep the current values before investigating a data problem or making a controlled correction. A dated copy in the database makes those values easy to query afterward.

In DB2 for Linux, UNIX and Windows (LUW), use CREATE TABLE ... AS (SELECT ...) WITH DATA to create a table from a query and populate it in one statement:

CREATE TABLE ASSET_CB_20261104 AS (
    SELECT
        ASSETNUM,
        SITEID,
        DESCRIPTION,
        STATUS
    FROM MAXIMO.ASSET
    WHERE LENGTH(CUSTOMFIELD) > 12
) WITH DATA;

This creates a new table containing four columns from the ASSET rows whose CUSTOMFIELD value satisfies the length condition. It does not update the source table.

It is a useful working copy of selected data. It is not a replacement for a recoverable database backup: the copy lives in the same database and does not reproduce the complete source table definition or its dependencies.

What the statement does

CUSTOMFIELD is a placeholder for a character column in your table. Replace it with a column that exists in your database and use a predicate that matches the records you intend to retain. It can appear in the filter without appearing in the copied columns.

That last detail matters: this copy keeps ASSETNUM, SITEID, DESCRIPTION and STATUS, but not CUSTOMFIELD itself. If CUSTOMFIELD is the value you plan to correct, include it in the SELECT list too.

Make the destination schema explicit

The source is qualified as MAXIMO.ASSET, but the original destination name is unqualified. DB2 resolves an unqualified table name using the current schema. Check it with:

VALUES CURRENT SCHEMA;

To place the copy explicitly in MAXIMO, use:

CREATE TABLE MAXIMO.ASSET_CB_20261104 AS (
    SELECT
        ASSETNUM,
        SITEID,
        DESCRIPTION,
        STATUS,
        CUSTOMFIELD
    FROM MAXIMO.ASSET
    WHERE LENGTH(CUSTOMFIELD) > 12
) WITH DATA;

Use an approved working schema instead if that is how your database is managed. The executing account needs permission to read the source and create the destination, with suitable table-space access and capacity. An existing table with the same name will cause creation to fail; this is not an overwrite operation.

Preview the rows first

Run the SELECT independently to check the condition and the values you are about to retain:

SELECT
    ASSETNUM,
    SITEID,
    DESCRIPTION,
    STATUS,
    CUSTOMFIELD,
    LENGTH(CUSTOMFIELD) AS VALUE_LENGTH
FROM MAXIMO.ASSET
WHERE LENGTH(CUSTOMFIELD) > 12;

DB2's LENGTH includes trailing blanks. For a fixed-length CHAR column, padding can therefore affect this filter. If trailing spaces should be ignored, use LENGTH(RTRIM(CUSTOMFIELD)) > 12 consistently in the preview and copy queries. Null values do not satisfy either condition. For multibyte text, also check the function's string-unit rules rather than assuming the result always counts visible characters. See IBM's LENGTH reference.

Copy every column or the whole table

To retain all normally selected columns for the matching rows:

CREATE TABLE MAXIMO.ASSET_CB_FILTERED_20261104 AS (
    SELECT *
    FROM MAXIMO.ASSET
    WHERE LENGTH(CUSTOMFIELD) > 12
) WITH DATA;

To copy the whole table, remove the filter:

CREATE TABLE MAXIMO.ASSET_CB_FULL_20261104 AS (
    SELECT *
    FROM MAXIMO.ASSET
) WITH DATA;

SELECT * omits implicitly hidden columns. Name those columns explicitly if they must be retained; IBM documents this behavior under hidden columns.

A full ASSET copy can require substantial storage, transaction-log capacity and time. Source reads can also encounter locks or affect concurrent work depending on isolation and database configuration. For an investigation involving only a few attributes, an explicit column list and a focused predicate are easier to review.

Verify the copy

After creating the five-column filtered copy above, inspect its row count and contents:

SELECT COUNT(*) AS COPIED_ROWS
FROM MAXIMO.ASSET_CB_20261104;
 
SELECT
    ASSETNUM,
    SITEID,
    DESCRIPTION,
    STATUS,
    CUSTOMFIELD
FROM MAXIMO.ASSET_CB_20261104
FETCH FIRST 20 ROWS ONLY;

These queries assume the version that includes CUSTOMFIELD. Omit that column when inspecting the original four-column example.

You can compare the copied count with the matching source count, but separately executed queries can see different data when Maximo is still updating records. A matching count alone does not prove that every copied value is correct. If an exact baseline matters, coordinate application activity and transaction isolation with the DBA.

Check your SQL client's auto-commit setting. When auto-commit is disabled, commit the successful copy before relying on it from another connection. A later rollback of that transaction would discard the uncommitted work.

What this backup does not preserve

A query-based copy retains the selected result values in a new table. Do not assume it also duplicates primary and foreign keys, indexes, triggers, grants, identity generation, defaults, or physical storage settings. The CREATE TABLE statement has separate rules and options for inherited column attributes; review them if structural fidelity matters. See IBM's CREATE TABLE reference.

The copied data does not stay synchronized with the source. It also does not include related Maximo records, long descriptions held elsewhere, attachments or other tables unless those are copied separately.

An ad hoc table created through SQL is not automatically a Maximo business object. It is available for database queries, not automatically through applications, object structures or Database Configuration.

Keep a note of what was copied, when, why and how it can be matched back to source records. ASSETNUM and SITEID provide useful business identifiers here; consider retaining ASSETUID as well when you need a stable database identifier. Do not treat the copy as a generic restore script: writing values back to Maximo requires a separate plan for keys, concurrent changes, related records and business rules.

Once the investigation and any retention period are complete, remove the working copy through your normal database cleanup process. Until then, protect it according to the data it contains.