tag:blogger.com,1999:blog-1347137045980388492.comments2023-05-21T03:07:57.328-07:00db2 performanceSameerhttp://www.blogger.com/profile/04380280581835257131noreply@blogger.comBlogger87125tag:blogger.com,1999:blog-1347137045980388492.post-47093754220457425542014-10-17T09:20:34.511-07:002014-10-17T09:20:34.511-07:00You can use pagenated queries.
Or you can try use...You can use pagenated queries.<br /><br />Or you can try use partitioned tables. It will become easier to move them around from live to archive tables!Sameerhttps://www.blogger.com/profile/04380280581835257131noreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-56313103668255690762014-10-16T20:52:18.912-07:002014-10-16T20:52:18.912-07:00Hi
Is there a way where I can use a commit count ...Hi <br />Is there a way where I can use a commit count when using a merge statement. I have to update/insert around 40 lakhs records per day into an archive table. Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-31790684236065986242014-09-04T04:45:40.719-07:002014-09-04T04:45:40.719-07:00Nice Blog Thanks For Sharing with me and This blog...Nice Blog Thanks For Sharing with me and This blog will tell you everything about the <a href="http://giilgit.blogspot.in/" rel="nofollow">you just improvise</a> .Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-19574076482880394422014-06-11T00:30:14.745-07:002014-06-11T00:30:14.745-07:00interesting and informative, keep posting, thank y...interesting and informative, keep posting, thank you very much!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-81855146761106144612013-12-30T18:44:03.825-08:002013-12-30T18:44:03.825-08:00- Indexing is one way.
- Reorg and runstats are t...- Indexing is one way. <br />- Reorg and runstats are two other options. <br />- Reorg by the index in where clause.Sameerhttps://www.blogger.com/profile/04380280581835257131noreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-178006347866253182013-12-30T09:11:56.616-08:002013-12-30T09:11:56.616-08:00Hi Sam,
I have a query with just SELECT fields fr...Hi Sam,<br /><br />I have a query with just SELECT fields from Table where condition.<br /><br />Is there a way to optimize this query?<br /><br />i can't use FIRST FETCH ROW option as the number of records cannot be determined.Anonymoushttps://www.blogger.com/profile/18047073269630440329noreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-90025710383189557332013-12-19T23:31:22.056-08:002013-12-19T23:31:22.056-08:00consider using atleast two disks, one for data and...consider using atleast two disks, one for data and other transaction logs. I will suggest that even though you have only one disk, distribute data and indexes on different tablespaces. It gives you two benefits:<br />1) Tomorrow if you can add more disks, you can easily move these tablespaces<br />2) You can use different pagezize and bufferpools for each of themSameerhttps://www.blogger.com/profile/04380280581835257131noreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-22537908857552098182013-12-19T03:53:17.186-08:002013-12-19T03:53:17.186-08:00only a raid5 five disk
only a raid5 five disk<br />Anonymoushttps://www.blogger.com/profile/04255214901705478149noreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-38904085525157718632013-12-11T18:53:50.606-08:002013-12-11T18:53:50.606-08:00Its better to keep them in different tablespaces (...Its better to keep them in different tablespaces (specially if you have multiple disks)Sameerhttps://www.blogger.com/profile/04380280581835257131noreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-50850653417078934842013-12-11T04:50:28.559-08:002013-12-11T04:50:28.559-08:00all the tables and indexes in USERSPACE1 .
is corr...all the tables and indexes in USERSPACE1 .<br />is correct? or is better various tablespaces?Anonymoushttps://www.blogger.com/profile/04255214901705478149noreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-53486573669423442612013-10-26T08:42:37.976-07:002013-10-26T08:42:37.976-07:00VARCHAR_ARR is a type I had created as an array. C...VARCHAR_ARR is a type I had created as an array. Check this one for details:<br /><br />http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0050497.htmlSameerhttps://www.blogger.com/profile/04380280581835257131noreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-9791429091932457702013-10-25T10:24:06.003-07:002013-10-25T10:24:06.003-07:00Hi I was trying to do a similar drop indexes/recre...Hi I was trying to do a similar drop indexes/recreate indexes procedure. I get an error on line number 8 in your example with wasadmin.varchar_arr .... any chance you could point me in the direction of getting this to work?<br /><br />Thanks<br />Anonymoushttps://www.blogger.com/profile/01544856718804158410noreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-22564403903552956382013-07-29T19:08:29.277-07:002013-07-29T19:08:29.277-07:00Hi Nagesh,
Thanks for your comment. I have now st...Hi Nagesh,<br /><br />Thanks for your comment. I have now started blogging on PostgreSQL database. Do visit my blogs on http://pgpen.blogspot.comSameerhttps://www.blogger.com/profile/04380280581835257131noreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-29810524941152919442013-07-24T21:03:08.169-07:002013-07-24T21:03:08.169-07:00Hi sameer !!
Thanks for the superb post,, please d...Hi sameer !!<br />Thanks for the superb post,, please do put up more n more !! <br /><br />i'm waiting for <br />how to decipher the explain plan step by step?<br />shall wait for the blog...<br /><br />Thanks and regards<br />NageshAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-7717334783535898942013-05-28T06:41:29.381-07:002013-05-28T06:41:29.381-07:00Hey Saurabh, thanks for the correction. You are co...Hey Saurabh, thanks for the correction. You are correct. I missed a DESC in the over clause.<br /><br />dense_rank and rank functions gives a rank to the data set as per the order by clause. Further you can partition on the ranking. e.g if you have a table which stores student_name, subjectcode, score then you can do<br /><br />select student_name, subjectcode, dense_rank()over(order by score partition by subjectcode) from my_table;<br /><br /><br />this will give rank 1 to highest scorer of each subject.<br /><br /><br />it is more or less similar to row_number, but row_number will give serial two different serial numbers to two person who have same score. <br /><br />e.g. if Anita and Rita have 90 in History, and Amar has 80 they will be "row_numbered" as <br /><br />Rita history 1 <br />Anita history 2<br />Amar history 3<br /><br /><br />when instead of row_number you use rank() they will be "ranked" as<br /><br />Rita history 1 <br />Anita history 1<br />Amar history 3<br /><br />notice that rank did a justice with both the toppers :)<br />But poor amar, though actually his score is 2nd highest got 3rd rank cause 1 rank was taken up by 2 people. This usually helps in getting points table for leagues (e.g. in Cricket of football leagues, top two teams with same points and 3rd team with 2nd highest point will be awarded).<br /><br /><br />Now to be a little more fair with Amar you may use dense_rank (which is also required when using 2nd max salary and even in case of athletics, where if two people cross the line at exact same fraction of second, both should get gold). so we "dense_ranked" to give some more credit to Amar for his score and we got<br /><br /><br /><br />Rita history 1 <br />Anita history 1<br />Amar history 2<br /><br />I hope it is clear now. If you use partition by you may get something like this<br /><br />Name SubjectCode Score Rank <br />Amar HIST 85 2<br />Rita HIST 90 1<br />Anita HIST 90 1<br />Rita ENG 90 3<br />Anita ENG 92 2<br />Amar ENG 95 1<br /><br /><br />Sameerhttps://www.blogger.com/profile/04380280581835257131noreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-83568760545893670492013-05-28T05:36:45.883-07:002013-05-28T05:36:45.883-07:00I think query to find 2nd max salary should be
se...I think query to find 2nd max salary should be <br />select sal as secnd_max_sal from (select dense_rank() over(order by sal DESC) rnk,sal from emp)tmp_rslt wher tmp_rslt.rnk=2<br /><br />Also I would like to know what exactly does dense_rank() mean...is it giving serial numbers (1,2...N) to the columns ordered in over(...) clause. And in the result produced is rank=10 for a row means there are 9 rows which satisfy condition in over(..) clause?SAURABH'Shttps://www.blogger.com/profile/08230437803156315324noreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-87579411444501008922013-05-08T02:09:20.158-07:002013-05-08T02:09:20.158-07:00well I am no more a db2 guy... But from my general...well I am no more a db2 guy... But from my general database experience I can suggest you to create an index (temporarily) on the table for the column which is used in where clause. If that too is not working then <br /><br />Option1) <br />export data in flat files with the where clause as filter<br /><br />truncate the table<br /><br />load/import the data back from flat files<br /><br />Option2)<br />Do the deletion in chunks. Use a windowing function and row enumerator. Deleted only 100K records at a time, do it till delete returns with 0 records.<br /><br />Sameerhttps://www.blogger.com/profile/04380280581835257131noreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-26965765691771959052013-05-07T22:49:12.784-07:002013-05-07T22:49:12.784-07:00What is the best way to delete records with 'w...What is the best way to delete records with 'where clause'. I have a requirement where I need to delete about 1M records from a table which has 2.5M records. I am struggling to do so as DB2 simply hangs after some time.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-21915693077836241852012-08-29T09:56:56.510-07:002012-08-29T09:56:56.510-07:00And could really use a DBA opinion in these situat...<i>And could really use a DBA opinion in these situations.</i><br /><br />I have always advocated involving a DBA right from requirement phase... What you can get from forums and blogs can never be as good as having a dba involved in your development... :P<br /><br />But if you are stuck somewhere you can ping me on my mailID sameer.kasi200x@gmail.com or you can ping me on twitter...<br />Sameerhttps://www.blogger.com/profile/04380280581835257131noreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-15895601478291093422012-08-29T09:45:17.205-07:002012-08-29T09:45:17.205-07:00Hi Sam,
Thanks for your reply.Actually I gave it a...Hi Sam,<br />Thanks for your reply.Actually I gave it a thought.It seems I had gotten the whole concept of merge incorrectly.But now after trying out different options with merge I'm a bit comfortable with the ways to use it.<br /><br />Anyways are there other means to contact you.Cause many times it so happens that Im stuck with some db2 related issue and just cant find a way out.And could really use a DBA opinion in these situations.<br /><br />Thanks<br />PratikPratikhttps://www.blogger.com/profile/07497230893756322701noreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-40947597556705361862012-08-26T03:45:43.771-07:002012-08-26T03:45:43.771-07:00Hi Jack,
That is the way to do it. There is nothi...Hi Jack,<br /><br />That is the way to do it. There is nothing wrong in what you are doing:<br /><br />set current explain mode evaluate indexes;<br />sql statement;<br />db2exfmt -1 -d DATABASE -o explain.out<br /><br />But as you are trying to see if a dropped index is affecting a plan or not, in that case it will be important to see if the index was used at all for the queries. Have you confirmed that first? If it was not at all used, dropping it (actually/virtually) will not affect your plan.<br /><br />BTW optim or for that matter any plan analysis tool can not (rather should not) be generating planing of its own, they all depend on db2 optimizer and if there is a way they can simulate something (virtual indexes) then there must be a stub in db2 somewhere which is letting them do this. Sure, with optim or data studio your work is much more easier but it should work even without those (as I have explained in this blog).<br /><br />Can you please send me/post the values (may in form if insert statement) for the existing index (from syscat.index) and advise_index table.<br /><br /><br />Regards<br />SameerSameerhttps://www.blogger.com/profile/04380280581835257131noreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-74733539633975453182012-08-24T13:35:38.586-07:002012-08-24T13:35:38.586-07:00I still cannot get it to work.
I have tried:
set...I still cannot get it to work.<br /><br />I have tried:<br /><br />set current explain mode evaluate indexes;<br />sql statement;<br />db2exfmt -1 -d DATABASE -o explain.out<br /><br />set current explain mode recommend indexes;<br />sql statement;<br />db2exfmt -1 -d DATABASE -o explain.out<br /><br />set current explain mode explain;<br />sql statement;<br />db2exfmt -1 -d DATABASE -o explain.out<br /><br />And they all look the same.<br /><br />Can you point me to some official documentation? I have found very little about this feature. You are the only place I have found that says it works via command line.<br /><br />When I asked an IBM rep about it they said I had to get Optim Query Workload Tuner to enable it. But I'm sure that was only to enable it in Data Studio.Jacknoreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-50902469785582451482012-08-23T21:49:21.098-07:002012-08-23T21:49:21.098-07:00If I understood your comment correctly then, I gue...If I understood your comment correctly then, I guess your are trying combination of EXPLAIN MODE (?). See the moment you say:<br /><br />set current explain mode recommend indexes;<br />set current explain mode explain;<br /><br />second statement will set explain mode to explain (which will override or subdue the effect of first statement).<br /><br />Just set it once and set it to evaluate indexes:<br /><br />set current explain mode evaluate indexes;<br /><br />It should help. And I have seen usage of ADVISE_INDEX being documented for evaluating virtual indexes even in DB2 8.2, so version (9.5) should not be an issue.<br />Sameerhttps://www.blogger.com/profile/04380280581835257131noreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-15951414981154374872012-08-23T17:37:32.356-07:002012-08-23T17:37:32.356-07:00This is what I am doing:
update ADVISE_INDEX set ...This is what I am doing:<br /><br />update ADVISE_INDEX set EXISTS='Y' where NAME='VIRTUAL_INDEX_NAME';<br />update ADVISE_INDEX set USE_INDEX='N' where NAME='VIRTUAL_INDEX_NAME';<br /><br />set current explain mode recommend indexes;<br />set current explain mode explain;<br /><br />sql statement;<br /><br />db2exfmt -1 -d DATABASE -o explain.out<br /><br />set current explain mode no;<br /><br />I have tried different permutations of 'current explain mode evaluate indexes', 'current explain mode recommend indexes', and 'current explain mode explain'. I have tried with true indexes and virtual indexes.<br />But it still does not recognize that the indexes are 'virtually dropped' when I look at explain.out.<br /><br />I have db2 9.5fp4 if that makes a difference.<br /><br />What am I doing wrong?Jacknoreply@blogger.comtag:blogger.com,1999:blog-1347137045980388492.post-32892517734394929972012-08-23T08:00:49.253-07:002012-08-23T08:00:49.253-07:00Pratik,
I did not quite get the usage of SELECT *...Pratik,<br /><br />I did not quite get the usage of SELECT * from FINAL TABLE. I am not aware of this syntax (i.e. usage of select in then clause) for merge statement.<br /><br />Probably if you can explain me what you intend to do, I can help.Sameerhttps://www.blogger.com/profile/04380280581835257131noreply@blogger.com