SQL/MP add partition first key primary?

[Memmedaga Memmedov]

hi,
I want to ask a question about SQL/MP add partition. I have 'payment' table and it is nearly %97 full of record.
I want to add partition using 'date' column, is it possible?
Because I know that if you use 'FIRST KEY' command, the name of primary key first column has to be same.
Do you think this below command works? Otherwise, I will copy data, purge table, create table primary key 'date' firstly and then copy datas.

alter table payment
add partition payment2
catalog xxxxx
extent (xx, xx) maxextents xxx
FIRST KEY (20201231); (YYYYMMDD-31 Dec 2020)


create table payment
(
card_number
, date
, branch_id
, sequence_number
, reserve_field
, PRIMARY KEY
(
card_number
, date
, branch_id
, sequence_number
)
)

[wbreidbach]

Post by Memmedaga Memmedov
hi,
I want to ask a question about SQL/MP add partition. I have 'payment' table and it is nearly %97 full of record.
I want to add partition using 'date' column, is it possible?
Because I know that if you use 'FIRST KEY' command, the name of primary key first column has to be same.
Do you think this below command works? Otherwise, I will copy data, purge table, create table primary key 'date' firstly and then copy datas.
alter table payment
add partition payment2
catalog xxxxx
extent (xx, xx) maxextents xxx
FIRST KEY (20201231); (YYYYMMDD-31 Dec 2020)
create table payment
(
card_number
, date
, branch_id
, sequence_number
, reserve_field
, PRIMARY KEY
(
card_number
, date
, branch_id
, sequence_number
)
)

Hi,
partitioning is based on the primary key starting with the first field of the primary key, in your case the card_number. if the table is nearly full, it might be a good idea to check for free space within the table with fup info <table>, stat or with sqlci fileinfo <table>,stat;. In case there is a lot of slack (> 15%) you could do a reload of the table, maybe that helps.
Another possibility might be increasing the maxextents, but from experience SQLCI might allow increasing the number of maxextents not regarding the limits of the filesystem.
Those are the only ways to solve the problem without invalidating the programs.
If you create an additional partition based on the card_number you need to specify "with data movement" within the alter table statement, otherwise the data will not be moved to the new partition.
Another way is to move the table to another disk and increase the size during that move: alter table payment partonly move to ....

[Memmedaga Memmedov]

Thank for your reply.
I have already done "fup reolad payment, slack 0" also increased maxextents to max. value before.
Adding partition with card_number didn't make me think. So I want to change order of primary key for new table.
May be new big table again full of record 2-3 years later and this time I will be adding partition for date column.
IS IT POSSIBLE use LOAD command for copy datas from payment to paymentNEW? They will have same column only primary key order change. OR another way instead of write this. (insert into paymentNEW select * from payment;)

create table paymentNEW
(
card_number
, date
, branch_id
, sequence_number
, reserve_field
, PRIMARY KEY
(
date
, card_number
, branch_id
, sequence_number
)

[wbreidbach]

Post by Memmedaga Memmedov
Thank for your reply.
I have already done "fup reolad payment, slack 0" also increased maxextents to max. value before.
Adding partition with card_number didn't make me think. So I want to change order of primary key for new table.
May be new big table again full of record 2-3 years later and this time I will be adding partition for date column.
IS IT POSSIBLE use LOAD command for copy datas from payment to paymentNEW? They will have same column only primary key order change. OR another way instead of write this. (insert into paymentNEW select * from payment;)
create table paymentNEW
(
card_number
, date
, branch_id
, sequence_number
, reserve_field
, PRIMARY KEY
(
date
, card_number
, branch_id
, sequence_number
)

Yes, load would be the preferred way. If you change the sequence of the columns you have to use the MOVEBYNAME option.
But you should be careful to change the primary key, maybe the application needs the actual sequence of fields. In addition doing partitioning by date usually isn't a good idea.
We have always used things like the cardnumber or the account number as partitioning criteria.

[Randall]

Post by wbreidbach

Post by Memmedaga Memmedov
Thank for your reply.
I have already done "fup reolad payment, slack 0" also increased maxextents to max. value before.
Adding partition with card_number didn't make me think. So I want to change order of primary key for new table.
May be new big table again full of record 2-3 years later and this time I will be adding partition for date column.
IS IT POSSIBLE use LOAD command for copy datas from payment to paymentNEW? They will have same column only primary key order change. OR another way instead of write this. (insert into paymentNEW select * from payment;)
create table paymentNEW
(
card_number
, date
, branch_id
, sequence_number
, reserve_field
, PRIMARY KEY
(
date
, card_number
, branch_id
, sequence_number
)

Yes, load would be the preferred way. If you change the sequence of the columns you have to use the MOVEBYNAME option.
But you should be careful to change the primary key, maybe the application needs the actual sequence of fields. In addition doing partitioning by date usually isn't a good idea.
We have always used things like the cardnumber or the account number as partitioning criteria.

Remember that if there is a SELECT * INFO :struct statement in the application, changing fields around can cause some pretty weird situations. The same applies to INSERT INTO with no fields, just values, particularly if dynamic SQL is used. In our shop. SELECT * or INSERT INTO with no fields is a sure fire way to fail a code review.

[Memmedaga Memmedov]

Thanks for replies.
I'll only change the order of primary key statement. Create table statement will be same. So, I think that there is no need to edit programs using select or insert. I created new table and start load command, I will see what will happen when finished.

[Bill Honaker]

Post by Memmedaga Memmedov
Thanks for replies.
I'll only change the order of primary key statement. Create table statement will be same. So, I think that there is no need to edit programs using select or insert. I created new table and start load command, I will see what will happen when finished.

I want to point out what you may already know.
You should create and load new tables with an alternatate filename, then find all programs that access the program, make duplicates of them to a work location.
Then you should SQLCOMP the duplicate programs, with EXPLAIN PLAN. You should review that and compare to the current production files.

Changing the primary keys (or indexes) can negatively impact the executaion plan for statements, since you are changing an access path.

If you find that this is the case, you can add an Index on the alternate table that is the same as the original primary key and recompile.
The resulting plan will still have slightly higher costs, both Insert statements (to insert the new index row), and on access statements to read the index row.
Alternatively, you can create the alternate table with larger extent sizes so it can be much bigger, but that's really a limited-time fix (unless older rows are
occasionally deleted from the tables).

Good luck with these, Memmedaga!

[wbreidbach]

Post by Bill Honaker

Post by Memmedaga Memmedov
Thanks for replies.
I'll only change the order of primary key statement. Create table statement will be same. So, I think that there is no need to edit programs using select or insert. I created new table and start load command, I will see what will happen when finished.

I want to point out what you may already know.
You should create and load new tables with an alternatate filename, then find all programs that access the program, make duplicates of them to a work location.
Then you should SQLCOMP the duplicate programs, with EXPLAIN PLAN. You should review that and compare to the current production files.
Changing the primary keys (or indexes) can negatively impact the executaion plan for statements, since you are changing an access path.
If you find that this is the case, you can add an Index on the alternate table that is the same as the original primary key and recompile.
The resulting plan will still have slightly higher costs, both Insert statements (to insert the new index row), and on access statements to read the index row.
Alternatively, you can create the alternate table with larger extent sizes so it can be much bigger, but that's really a limited-time fix (unless older rows are
occasionally deleted from the tables).
Good luck with these, Memmedaga!

Just one additional comment:
If an application program accesses the table using the card-number and you change the sequence of the fields as described that might result in a massive performance degredation!
You can try to heal that using an alternate index but if the card number ist the primary search criteria for the table you will still have a performance impact.
We have always avoided to do partitioniong by date because that will just delay the problems to sometime in the future. In our payment application the tables are partitioned according to a fixed criteria similar to card-number and because of that we have 32 partitions of nearly the same size. We did it years ago and we never had to change anything.
Another thing: You never told us something about the size of the table, maybe the problem can be solved by just increasing the size of the table.

[Memmedaga Memmedov]

wbreidbach, yes you are right. I have just completed the table move and compilation cobol programs. Only one of the sql select statement see that cost value incresing from 4 to 2M. (million)
I haven't added any index yet. I believe that new index will decrease the cost.
Also I doubled the size of table while creating new one. If table will be full again in 3-4 years, only add partition using date.
Also I will comment your suggesstion with my collague, may be return before table I did not drop it yet.

select * from payment where card_number=x and year,month,day>a,b,c ... sth like that..

[Memmedaga Memmedov]

Solved. Thanks.
I created an index for card-number then cost decrease 2M to 2.