Renaming primary index in DB2 for z/OS
Some time back, I got similar request and my first response was to drop and recreate the primary index with new name. But it is not as simple as it sounds. It compels me to open DB2 manual and revisit DB2 basics. So, I thought it a good idea to share the facts with this blog entry.
What is primary index?
• Primary index is an unique index which enforces the uniqueness of the primary key.
• Primary index is always created on NOT NULL column(s).
• There is always one primary key constraint associated with primary index.
• UNIQUERULE column of SYSIBM.SYSINDEXES table is equal to ‘P’ for primary index. You can use this fact to indentify unique index.
• If you directly try to DROP primary index, you will get SQL CODE -669 with error message
“THE OBJECT CANNOT BE EXPLICITLY DROPPED.”
• To create primary index on table which don’t have any primary index, you will have to first create unique index on required NOT NULL column(s) and then alter table to add primary constraint. If you directly alter the table to add primary constraint without creating an unique index on that column, you will get SQLCODE -625 with error message :-
“TABLE table-name DOES NOT HAVE AN INDEX TO ENFORCE THE UNIQUENESS OF THE PRIMARY OR UNIQUE KEY”
Steps to rename primary index (should be followed in exact sequence):-
1. Note down the column(s) on which the existing index is created. You can query catalog table SYSIBM.SYSKEYS to get the columns name and its sequence.
2. Now find out the primary key constraint name of existing primary key. You can query catalog table SYSIBM.SYSTABCONST for the primary index.
3. Now alter table to drop primary key constraint.
ALTER TABLE table-owner.table-name DROP CONSTRAINT constraint-name;
COMMIT;
4. Now drop the existing primary index.
DROP INDEX index-owner.index-name;
COMMIT;
5. Then create unique index on the same column(s) and same sequence on which previous index was created with new name.
CREATE UNIQUE INDEX index-owner.index-name
ON table-owner.table-name (col1, col2);
Note: - Refer SQL Reference manual for complete syntax of CREATE UNIQUE INDEX.
6. Finally alter table to add primary key constraint with name same as of previous constraint.
ALTER TABLE table-owner.table-name ADD CONSTARINT constraint-name
PRIMARY KEY (col1, col2);
COMMIT;
So, by following above steps you can easily rename existing primary index or create new primary index. You can follow similar steps to change the column(s) in the primary index . I hope this will be useful to guys firefighting with primary index. Please feel free to add your comments or suggestions.
Some time back, I got similar request and my first response was to drop and recreate the primary index with new name. But it is not as simple as it sounds. It compels me to open DB2 manual and revisit DB2 basics. So, I thought it a good idea to share the facts with this blog entry.
What is primary index?
• Primary index is an unique index which enforces the uniqueness of the primary key.
• Primary index is always created on NOT NULL column(s).
• There is always one primary key constraint associated with primary index.
• UNIQUERULE column of SYSIBM.SYSINDEXES table is equal to ‘P’ for primary index. You can use this fact to indentify unique index.
• If you directly try to DROP primary index, you will get SQL CODE -669 with error message
“THE OBJECT CANNOT BE EXPLICITLY DROPPED.”
• To create primary index on table which don’t have any primary index, you will have to first create unique index on required NOT NULL column(s) and then alter table to add primary constraint. If you directly alter the table to add primary constraint without creating an unique index on that column, you will get SQLCODE -625 with error message :-
“TABLE table-name DOES NOT HAVE AN INDEX TO ENFORCE THE UNIQUENESS OF THE PRIMARY OR UNIQUE KEY”
Steps to rename primary index (should be followed in exact sequence):-
1. Note down the column(s) on which the existing index is created. You can query catalog table SYSIBM.SYSKEYS to get the columns name and its sequence.
2. Now find out the primary key constraint name of existing primary key. You can query catalog table SYSIBM.SYSTABCONST for the primary index.
3. Now alter table to drop primary key constraint.
ALTER TABLE table-owner.table-name DROP CONSTRAINT constraint-name;
COMMIT;
4. Now drop the existing primary index.
DROP INDEX index-owner.index-name;
COMMIT;
5. Then create unique index on the same column(s) and same sequence on which previous index was created with new name.
CREATE UNIQUE INDEX index-owner.index-name
ON table-owner.table-name (col1, col2);
Note: - Refer SQL Reference manual for complete syntax of CREATE UNIQUE INDEX.
6. Finally alter table to add primary key constraint with name same as of previous constraint.
ALTER TABLE table-owner.table-name ADD CONSTARINT constraint-name
PRIMARY KEY (col1, col2);
COMMIT;
So, by following above steps you can easily rename existing primary index or create new primary index. You can follow similar steps to change the column(s) in the primary index . I hope this will be useful to guys firefighting with primary index. Please feel free to add your comments or suggestions.
0 comments:
Post a Comment