Embedded Usage

When using the Modify statement in an application, the DBMS Server returns the number of rows modified in the SQLCA's sqlerrd(3) field. If the statement does not modify any rows, the DBMS Server sets the SQLCA's sqlcode to 100.

The preprocessor does not verify the syntax of the with_clause. The values in the with_clause options can be specified using host language variables. Any other parameters cannot be specified using host language variables.

Permissions

You must own the table or have the security privilege and connect as the owner.

Locking

The Modify statement requires an exclusive table lock. Other sessions, even those using readlock=nolock, cannot access the table until the transaction containing the modify statement is committed.

Two exceptions are the modify to table_debug variant, which takes a shared table lock, and the concurrent_updates option, which takes only a brief exclusive lock at the end of the modify operation.

Related Statements

Copy

Create Index

Create Location

Create Table

Examples: Modify

The following are Modify statement examples:

1. Modify the employee table to an indexed sequential storage structure with eno as the keyed column.

   modify employee to isam on eno;

   If eno is the first column of the employee table, the same result can be achieved by:

   modify employee to isam;

2. Redo the isam structure on the employee, but request a 60% occupancy on all primary pages.

   modify employee to reconstruct
           with fillfactor = 60;

3. Modify the job table to compressed hash storage structure with jid and salary as keyed columns.

   modify job to hash on jid, salary
           with compression;

4. Perform the same modify, but also request 75% occupancy on all primary pages, a minimum of 7 primary pages and a maximum of 43 primary pages.

   modify job to hash on jid, salary
           with compression, fillfactor = 75,
           minpages = 7, maxpages = 43;

5. Perform the same modify again but only request a minimum of 16 primary pages.

   modify job to hash on jid, salary
           with compression, minpages = 16;

6. Modify the dept table to a heap storage structure and move it to a new location.

   modify dept to heap with location=(area4);

7. Modify the dept table to a heap again, but have rows sorted on the dno column and have any duplicate rows removed.

   modify dept to heapsort on dno;

8. Modify the employee table in ascending order by ename, descending order by age, and have any duplicate rows removed.

   modify employee to heapsort on ename asc,
           age desc;

9. Modify the employee table to btree on ename so that data pages are 50% full and index pages are initially 40% full.

   modify employee to btree on ename
   with fillfactor = 50, leaffill = 40;

10. Modify a table to btree with data compression, no key compression. This is the format used by the (obsolete) cbtree storage structure.

    modify table1 to btree
            with compression=(nokey, data);

11. Modify an index to btree using key compression.

    modify index1 to btree with compression=(key);

12. Modify an index so it is retained when its base table is modified. Its current table structure is unchanged.

    modify empidx to reconstruct with persistence;

13. Modify a table, specifying the number of pages to be initially allocated to it and the number of pages by which it is extended when it requires more space.

    modify inventory to btree
            with allocation = 10000, extend = 1000;

14. Modify an index to have uniqueness checked after the completion of an update statement.

    modify empidx to btree unique on empid
            with unique_scope = statement;

15. Move all physical partitions of the table in the Create Table Example 17 that contain 2001 and earlier ship-dates to the history_loc location.

    MODIFY lineitems PARTITION p1 TO REORGANIZE

    WITH LOCATION = (history_loc);

16. Remove partitioning from a table.

    modify lineitems to reconstruct with nopartition;