| View previous topic :: View next topic |
| Author |
Message |
jctgf
Beginner
Joined: 05 Nov 2006
Posts: 89
Topics: 36
|
 Posted: Sun Jul 29, 2007 9:53 am Post subject: when a clustering primary key is mandatory? |
 |
|
hi,
i had a problem with the index update a few years ago when i tried to load append a extremely large partitioned table.
the table had 2 indexes: the primary key (non cluster) and a second index, a cluster and partitioned one.
at that time i was suggested to discard the second index and to turn my primary key the cluster index (and partitioned). the change solved my index update issue.
now i need to separate my clustering index from my primary key again and i fear to go thru that index update problem again.
nobody in the company remembers why the solution adopted at that time was to turn the primary key the clustering (and partitioned) index.
i wonder if someone has experienced such a situation.
thanks,
jc |
|
| Back to top |
|
 |
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Sun Jul 29, 2007 1:33 pm Post subject: |
|
|
What vsn db2 are you running?
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
jctgf
Beginner
Joined: 05 Nov 2006
Posts: 89
Topics: 36
|
| Posted: Sun Jul 29, 2007 2:26 pm Post subject: |
|
|
the problem occured under version 6.
i am not sure about the current version.
thanks,
jc |
|
| Back to top |
|
|
jctgf
Beginner
Joined: 05 Nov 2006
Posts: 89
Topics: 36
|
| Posted: Sun Jul 29, 2007 2:36 pm Post subject: |
|
|
just in case i wasn't clear enough...
what i would like to know is:
1) a partitioned index must also be a clustered one?
2) if i have an extremely large partitioned table, does my primary key also have to be partitioned?
thanks,
jc |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Sun Jul 29, 2007 5:15 pm Post subject: |
|
|
Obviously your not a dba, otherwise you would know the version of db2 that you are running. You can find it on a spufi or qmf intro screen as well as look at the jes of your db2master started tasks.
It really matters what version db2 you are running. If you are still on vsn 6, you still have the same problems. IBM only changes things in db2 with vsn releases. Vsn7 has some important improvements, vsn8 even more. vsn 9 even more.....
talk to your dba or read the release notes on the different db2 releases.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
jctgf
Beginner
Joined: 05 Nov 2006
Posts: 89
Topics: 36
|
| Posted: Mon Jul 30, 2007 6:11 am Post subject: |
|
|
hi,
no, i am not a dba. they change the version constantly and i am not sure about the current one.
i have already talked to my dba and he's having a hard time, looking thru a lot o manuals and trying to remember the reason they suggested me such a solution, a few years ago.
it seems to be a very unusual situation, otherwise he'd have provided me a prompt response. it seems to be related to the size of the table (2 billion rows). there are not many tables as large as this one in my company.
| Quote: |
If you are still on vsn 6, you still have the same problems
|
may i assume you are saying the version 6 has indeed such a problem?
thanks |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Mon Jul 30, 2007 7:12 am Post subject: |
|
|
| Quote: | | i wonder if someone has experienced such a situation. |
no, I have always worked at shops that had rather competent DBA's, or at least kept up with DB2 versions and introduced changes.
You are looking for a simple answer to a very complicated problem. 1st, the version of db2 that you are running is vitally important. Vsn 7 had a release 7.1 to address some of these large table issues. It could be that the table is designed incorrectly. I know you don't want to hear about table redesign, rebuild, etc... but that maybe the correct solution, and I realize you can't make that. The DBA's are responsible for that kind of action.
If you have to solve this problem yourself, then you need to determine what version DB2 you are running on (the first line of a BIND listing), and then look at the documentation.
With a large table you are looking at Business Intelligence and Warehousing issues which impact reorg and the BUILD2 process (indexes). Vsns 7, 7.1, 8 and 9 were forced to address those issues many because of the competition with oracle support of SAP.
You are taking on DBA activities. If you are indeed responsible, you need to find answers. You might find some info in the IBM discussions about SAP support. Also, I would look thru forums (websites) concerned with BI and Warehousing. Those people have to constantly address these issues.
You said that you started on Vsn6 and they are changing the versions constantly. Therefore you are on Vsn6+, which means you can use NPSI (Non partitioned secondary indexes) Clustered and non-clustered primary. In these later versions, parallelism during the reorg is possible (faster loads) and depending on the type of indexes you use, BUILD2 is not invoked.
Unfortunately, there is probably no quick and dirty solution to your problem. IBM constantly stresses re-design of tables with new releases.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
