IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Table partitioning


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Mon Jan 23, 2012 11:32 pm
Reply with quote

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.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Tue Jan 24, 2012 12:15 pm
Reply with quote

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
Quote:
make necessary edits in it to add COL2 information.

?

3. yes, it may impact as all the associated packages must be rebound.
its better to do this in test region and run the explain reports to check if there any access path impact.
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Tue Jan 24, 2012 8:02 pm
Reply with quote

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?
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India

PostPosted: Wed Jan 25, 2012 2:02 pm
Reply with quote

Hello There,

Quote:
If the DCLGEN is not changed then is there a need to compile and bind all the programs which use this table.


Binding is not only for table changes ( dclgen changes), Its also for access path if you defind it at compile time
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Wed Jan 25, 2012 2:12 pm
Reply with quote

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
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Fri Jan 27, 2012 12:40 am
Reply with quote

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.
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Fri Jan 27, 2012 12:50 am
Reply with quote

enrico-sorichetti wrote:
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

Yeah you are right. And let me thank you for the link. I revisited the theory part to understand the concepts better.

I am interested to know that if my program has a select query in it using some column suppose COL1 of the table and the table is partioned based on COL1. So how can I check if that query is using partition elimination in its access path? In my project we have changeman and bind and compile is taken care by changeman. I was trying to access the plan table but could not find the plan table created with MYUSERID.PLAN_TABLE. icon_confused.gif
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jan 27, 2012 1:59 am
Reply with quote

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

and, changeman can be changed.....................
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Fri Jan 27, 2012 7:48 pm
Reply with quote

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

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


I looked at the changeman compilation log. Did a f 'Plan' did not get anything.
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Fri Jan 27, 2012 7:59 pm
Reply with quote

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
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jan 27, 2012 9:26 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Sat Jan 28, 2012 4:13 pm
Reply with quote

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 icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Unload and Load ISPF Table TSO/ISPF 4
No new posts ISPF Table to add a new column TSO/ISPF 1
No new posts Inserting into table while open selec... DB2 1
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
Search our Forums:

Back to Top