Table partitioning

Rijit · Active User Joined: 15 Apr 2010 Posts: 168 Location: Pune

Posted: Mon Jan 23, 2012 11:29 pm Post subject: Process of table partition

I am working on a task where I have to re-partition a huge table based on business need because going forward they are going to use the new field COL2 more frequently in the queries and to optimize the process.

Currently the table is partitioned on the key field of the table COL1. Now this partition will be dropped and the table has to be partitioned based on a new COL2 of the table. But as per my design, no index has been declared using COL2 in the table and neither COL2 is part of the primary key of the table. I have a couple of questions in mind:

1. Is it wise to create index using COL2 also along with the partitioning stuff. ( Currently COL2 has no index defined on it and is empty) and let COL1 remain as the primary key as many programs are already using COL1.

2. For any re-partitioning activity like this I assume first we have to unload the table into a flat file, make necessary edits in it to add COL2 information. Then drop the table and recreate the table with defined partition and then load the data into the table. Or is it the reverse way, we edit the data first in the table and then re-partition it with specified ranges of COL2?

3. How much impact can it cause on the programs which have queries running based on the COL1 field of the table ( The field on which currently the table is partitioned)

This is basically a design related challenge and any useful advise would be appreciated.

gylbharat · Posted: Tue Jan 24, 2012 12:15 pm

Hi,

I think you should consult your DBA.

1. I think you can partition the table based on COL1 and create a NPI for COL2.

2. yes, You should unload the data in the flat file.
what do you mean by

Rijit · Active User Joined: 15 Apr 2010 Posts: 168 Location: Pune

Thank you for your inputs. Further in this. I am not adding any new field to the table here. I am going to populate value in COL2 for each row in the table and repartition the whole table based on the value range in COL2.

According to my understanding since no new field is added to the table so DCLGEN won't be changed right. Only the logic for the programs which inswert into the table need to be changed I guess. If the DCLGEN is not changed then is there a need to compile and bind all the programs which use this table.

Secondly for a query how we can detrmine if that query is using partition elimination process form the explain for that query?

guptae · Posted: Wed Jan 25, 2012 2:02 pm

Hello There,

enrico-sorichetti · Posted: Wed Jan 25, 2012 2:12 pm

terminolgy is important in IT ...
DCLGEN is the process ( well... it' s really the DB2 command ) which is used
to create the COBOL/PL1/ASM/... DECLARES for a table AMEN

any other use is just plain wrong

publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/dsncrk13/3.11?ACTION=MATCHES&REQUEST=DCLGEN&TYPE=FUZZY&SHELF=DSNSHKA3.bks&DT=20081204091348&CASE=&searchTopic=TOPIC&searchText=TEXT&searchIndex=INDEX&rank=RANK&ScrollTOP=FIRSTHIT#FIRSTHIT

Rijit · Active User Joined: 15 Apr 2010 Posts: 168 Location: Pune

Thanks Ekta for enlightening me. A very valid point made. As we are changing the partitions in the table so the optimized access path for the queries may change using this field. Ideally it would be a good idea to recompile and bind all the impacted programs after the table partitions right.

Rijit · Active User Joined: 15 Apr 2010 Posts: 168 Location: Pune

dbzTHEdinosauer · Posted: Fri Jan 27, 2012 1:59 am

look at the output of the bind process,
it will tell you what the hlq for the explain.

and, changeman can be changed.....................

Rijit · Active User Joined: 15 Apr 2010 Posts: 168 Location: Pune

Rijit · Active User Joined: 15 Apr 2010 Posts: 168 Location: Pune

Only what i could find is below

BIND PACKAGE(********) MEMBER(********) -
OWNER(DB2DBA) QUALIFIER(ACT00 ) CURRENTDATA(NO ) -
VALIDATE(BIND) EXPLAIN(YES) ACTION(REPLACE) -
SQLERROR(NOPACKAGE) ISOLATION(CS) RELEASE(COMMIT ) -
DEGREE(1) FLAG(I);

I wanna see the explain for this program. It can bee seen that the explain option is Yes in the bind card. So there should be some place where I can see this explain right

dbzTHEdinosauer · Posted: Fri Jan 27, 2012 9:26 pm

when you do a select .... from ?.plan....
and you leave the ? blank,
you are going to access the plantable for the default qualifier./schema for your select.

do a list of tables at your site and find the correct schema/qualifier for the plantable that the bind from changeman populates.

i would guess it is act00 but i know nothing (just like you) about your site set-up.

since you are too lazy to read and inform yourself,
bite the bullet,
declare your ignorance
and ask a dba what plantable you should be querying.

if you are too stubborn to do that,
build your own plantable (via spufi)
and populate it with an explain of your sql.

in the appendix of every sql reference
it describes how you can build your own plantable.

Rijit · Active User Joined: 15 Apr 2010 Posts: 168 Location: Pune

Hi Dick, I appreciate a detailed explanation like this. Thank you. I understood the point. I will have to touch base with the DBA in my team and soon will get the default schema which is used in my site. Will keep you posted