SQL Codes › SQL Return Codes -1 through -999 › -561 - TTM TOO SMALL, SEE ERROR ACTION

-561 - TTM TOO SMALL, SEE ERROR ACTION

Explanation

You issued an ALTER TABLE statement that involves an operation which requires:

1. Copying the contents of the table to the Temporary Table Manager (TTM),
2. Changing the definition of the table, and then
3. Copying the reformatted rows back to the table.

However, a check is made before this operation begins to see if enough space is available in the TTM. If the answer is No, this message is generated.

The SQLSTATE that equates to this SQL return code is 57S05.

User Response

You can make the TTM larger by doing a dynamic extend.

Dynamic extensions of the TTM can be done with no loss of access to any other task areas or TTM usage. You need not close the TTM. Support is provided when the TTM is defined as either DASD or VIRTUAL. When the TTM is DASD, define the dynamic extend or on-demand dynamic extend for the TTM as for any other DASD data set. When the TTM is VIRTUAL, use the dynamic extend or on-demand dynamic extend as for any other VIRTUAL database area.

To specify dynamic extend at MUF startup, use MUF startup options. For more information, see the CA Datacom/DB Database and System Administration Guide.

To change dynamic extend settings for VIRTUAL TTMs after the MUF has started, use the VIRTUAL_DYNAMIC_EXTEND console-like command. For more information, see the CA Datacom/DB Database and System Administration Guide.

To use on-demand dynamic extend, use the DYNAMIC_EXTEND console-like command. For more information, see the CA Datacom/DB Database and System Administration Guide.

TTM_BLKS_MAX_USE is a new column that lists the highest number of blocks used. This column is found in the SQL_STATUS (SQS) table. For more information, see the CA Datacom/DB System Tables Reference Guide.

Using ALTER TABLE on large tables is not recommended because of the following issues:

• The time it takes
• The risk of losing data if the LXX file becomes full and checkpoints and the request cannot complete for any reason, such as the data area or TTM becoming full.

To avoid these issues

Follow these steps:

1. Copy the rows to an external file with the DBUTLTY function EXTRACT.
2. Use DBUTLTY LOAD with FORMAT=NONE to delete all rows.
3. Execute ALTER TABLE to change the definition of the table. Since the table is empty, this executes quickly with no rollback concerns.
4. Convert the rows yourself and produce an EXTRACT file valid for the new format..
5. Load the converted EXTRACT file back with DBUTLTY LOAD or REPLACE.