Transcript Copy PPT - Aetna - db2commerce.com

Altering a Data Type
Two Different Methods
03.15.13
http://db2commerce.com
CONFIDENTIAL; © 2012 ROSETTA. All rights reserved.
Problem
Table with data type of DOUBLE in it’s primary key must only have
integer data types
Column name
Data type
schema
SHIPMODE_ID
JURSTGROUP_ID
SYSIBM INTEGER
SYSIBM INTEGER
4
4
0
0
No
No
Child
Child
MONTO
SYSIBM
20
5
Yes
No
TAMANIO
SYSIBM DOUBLE
8
0
No
No
DESCRIPTION
SYSIBM
VARCHAR
50
0
Yes
No
DIASADICIONALES
SYSIBM
INTEGER
4
0
Yes
No
FIELD1
SYSIBM
INTEGER
4
0
Yes
No
FIELD2
SYSIBM
VARCHAR
254
0
Yes
No
FIELD3
SYSIBM
DOUBLE
8
0
Yes
No
OPTCOUNTER
SYSIBM
SMALLINT
2
0
Yes
No
Data type name
DECIMAL
Column
Length Scale
Nulls
FK?
2
Method Choices
ALTER TABLE … ALTER
COLUMN … SET DATA
TYPE…
ADMIN_MOVE_TABLE
3
Planning
Issues:
• Data type of a primary key column cannot be changed using ALTER TABLE
• Reorg is required after a this particular data type alteration using ALTER
TABLE
• ADMIN_MOVE_TABLE cannot handle foreign keys
Plan #1:
1.
2.
3.
4.
5.
6.
Ensure users are not accessing the table
Drop primary key
Alter data type
Reorg table
Add primary key
Do runstats on the table
4
Plan #1 Execution
$ db2 "alter table DBATEST.XCONFIGSHPCHARGE drop PRIMARY KEY"
DB20000I The SQL command completed successfully.
$ db2 "alter table DBATEST.XCONFIGSHPCHARGE alter TAMANIO set data type
INTEGER"
DB20000I The SQL command completed successfully.
$ db2 "select substr(TABSCHEMA,1,8) as tabschema, substr(TABNAME,1,18) as
tabname, AVAILABLE, REORG_PENDING, NUM_REORG_REC_ALTERS from
sysibmadm.admintabinfo where tabschema='DBATEST' and
tabname='XCONFIGSHPCHARGE' with ur"
TABSCHEMA TABNAME
AVAILABLE REORG_PENDING NUM_REORG_REC_ALTERS
--------- ------------------ --------- ------------- -------------------DBATEST
XCONFIGSHPCHARGE
Y
Y
1
1 record(s) selected.
5
Plan #1 Execution
$ db2 "reorg table DBATEST.XCONFIGSHPCHARGE"
DB20000I The REORG command completed successfully.
$ db2 "select substr(TABSCHEMA,1,8) as tabschema, substr(TABNAME,1,18) as
tabname, AVAILABLE, REORG_PENDING, NUM_REORG_REC_ALTERS from
sysibmadm.admintabinfo where tabschema='DBATEST' and
tabname='XCONFIGSHPCHARGE' with ur"
TABSCHEMA TABNAME
AVAILABLE REORG_PENDING NUM_REORG_REC_ALTERS
--------- ------------------ --------- ------------- -------------------DBATEST
XCONFIGSHPCHARGE
Y
N
0
1 record(s) selected.
$ db2 "ALTER TABLE DBATEST.XCONFIGSHPCHARGE ADD CONSTRAINT PK_XCONFIGSHPCHAR
PRIMARY KEY (SHIPMODE_ID, JURSTGROUP_ID, TAMANIO)"
DB20000I The SQL command completed successfully.
$ db2 runstats on table DBATEST.XCONFIGSHPCHARGE with distribution and
detailed indexes all
DB20000I The RUNSTATS command completed successfully.
6
Planning
Issues:
• Data type of a primary key column cannot be changed using ALTER TABLE
• Reorg is required after a this particular data type alteration using ALTER
TABLE
• ADMIN_MOVE_TABLE cannot handle foreign keys
Plan #2:
1.
2.
3.
4.
Ensure users are not accessing the table
Drop foreign keys
Use ADMIN_MOVE_TABLE to change the column data type
Add foreign keys
7
Plan #2 Execution
$ db2 "alter table dbatest.XCONFIGSHPCHARGE drop FOREIGN KEY
FK_CFGSHPC_JRSGRP drop FOREIGN KEY FK_CFGSHPC_SHPMDE"
DB20000I The SQL command completed successfully.
$ db2 "call ADMIN_MOVE_TABLE(
'DBATEST',
'XCONFIGSHPCHARGE',
'','','','','','',
'SHIPMODE_ID INTEGER NOT NULL,
JURSTGROUP_ID INTEGER NOT NULL,
MONTO DECIMAL(20,5),
TAMANIO INTEGER NOT NULL,
DESCRIPTION VARCHAR(50),
DIASADICIONALES INTEGER,
FIELD1 INTEGER,
FIELD2 VARCHAR(254),
FIELD3 DOUBLE,
OPTCOUNTER SMALLINT',
'',
'MOVE')"
8
ADMIN_MOVE_TABLE Output
Result set 1
-------------KEY
-------------------------------AUTHID
CLEANUP_END
CLEANUP_START
COPY_END
COPY_OPTS
COPY_START
COPY_TOTAL_ROWS
INDEXNAME
INDEXSCHEMA
INDEX_CREATION_TOTAL_TIME
INIT_END
INIT_START
PAR_COLDEF
REPLAY_END
REPLAY_START
REPLAY_TOTAL_ROWS
REPLAY_TOTAL_TIME
STATUS
SWAP_END
SWAP_RETRIES
SWAP_START
VERSION
VALUE
---------------------------------------------------DB2INST1
2013-03-09-18.00.39.229239
2013-03-09-18.00.39.178340
2013-03-09-18.00.38.982713
OVER_INDEX,ARRAY_INSERT,NON_CLUSTER
2013-03-09-18.00.38.856133
825
PK_XCONFIGSHPCHAR
DBATEST
0
2013-03-09-18.00.38.828100
2013-03-09-18.00.38.638682
SHIPMODE_ID INTEGER NOT NULL,
JURSTGROUP_ID INTEGER NOT NULL,
MONTO DECIMAL(20,5),
TAMANIO INTEGER NOT NULL, DESCRIPTION VARCHAR
2013-03-09-18.00.39.109882
2013-03-09-18.00.38.983153
0
0
COMPLETE
2013-03-09-18.00.39.170664
0
2013-03-09-18.00.39.115338
09.07.0006
22 record(s) selected.
Return Status = 0
9
Plan #2 Execution
$ db2 "ALTER TABLE DBATEST.XCONFIGSHPCHARGE ADD CONSTRAINT
FK_CFGSHPC_JRSGRP FOREIGN KEY (JURSTGROUP_ID) REFERENCES
WSCOMUSR.JURSTGROUP (JURSTGROUP_ID) ON DELETE CASCADE ON UPDATE NO
ACTION ENFORCED ENABLE QUERY OPTIMIZATION“
DB20000I
The SQL command completed successfully.
$ db2 "ALTER TABLE DBATEST.XCONFIGSHPCHARGE ADD CONSTRAINT
FK_CFGSHPC_SHPMDE FOREIGN KEY (SHIPMODE_ID) REFERENCES
WSCOMUSR.SHIPMODE (SHIPMODE_ID) ON DELETE CASCADE ON UPDATE NO
ACTION ENFORCED ENABLE QUERY OPTIMIZATION“
DB20000I
The SQL command completed successfully.
$ db2 runstats on table DBATEST.XCONFIGSHPCHARGE with distribution
and detailed indexes all
DB20000I
The RUNSTATS command completed successfully.
10
Table After Change
Column name
Data type
schema
SHIPMODE_ID
JURSTGROUP_ID
SYSIBM INTEGER
SYSIBM INTEGER
4
4
0
0
No
No
Child
Child
MONTO
SYSIBM
20
5
Yes
No
TAMANIO
SYSIBM INTEGER
4
0
No
No
DESCRIPTION
SYSIBM
VARCHAR
50
0
Yes
No
DIASADICIONALES
SYSIBM
INTEGER
4
0
Yes
No
FIELD1
SYSIBM
INTEGER
4
0
Yes
No
FIELD2
SYSIBM
VARCHAR
254
0
Yes
No
FIELD3
SYSIBM
DOUBLE
8
0
Yes
No
OPTCOUNTER
SYSIBM
SMALLINT
2
0
Yes
No
Data type name
DECIMAL
Column
Length Scale
Nulls
FK?
11
Comparing Outages
Ensure
users are
not
accessing
the table
ALTER TABLE … ALTER
COLUMN … SET DATA
TYPE…
ADMIN_MOVE_TABLE
Alter data
type
Drop
primary key
Add
primary key
Reorg table
Do runstats
on the
table
Use
ADMIN_MOVE_TA
BLE to change the
column data type
Ensure users are
not accessing the
table
Drop foreign keys
Add foreign keys
12
How to Decide Which Method is Better
If column being changed were not in primary key, perhaps plan #1
would be better
If the table had no foreign keys, maybe plan #2 would be better
Change
Complexity
Change
Duration
Outage
Time
End Result
Risk
Back-Out
Plan
13
Vote for Ember!
Blog: http://db2commerce.com
Twitter: @ember_crooks
LinkedIn: http://www.linkedin.com/pub/ember-crooks/8/a9b/25a/
E-mail: [email protected]
Useful links on this topic:
DB2 Info Center entry on ALTER TABLE:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.lu
w.sql.ref.doc/doc/r0000888.html
DB2 Info Center entry on ADMIN_MOVE_TABLE:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.lu
w.sql.rtn.doc/doc/r0055069.html
14