• About Dangerous DBA
  • Table of Contents
Dangerous DBA A blog for those DBA's who live on the edge

Category Archives: Reorganise Index

Automated DB2 Reorganisation, Runstats and Rebinds – Version 2

December 11, 2011 8:56 pm / 1 Comment / dangerousDBA

A while back I did the first version of this code (can be found here). Over time I have been running this code on our production servers, it started out by working fine but sometimes it would over run and interfere with the morning batch, so a different solution was needed. In a previous article I discussed if it was better to let the included automated DB2 functionality take care of the maintenance of tables etc, or to create your own process that uses included stored procedures to identify the tables that need reorganising.

So this new version of the script will only work between certain times and only do offline reorganisations, but is still possible to just reorganise a single partition of a range partitioned table. The reason for the time restriction is to take a leaf from the included automated scripts having an offline maintenance window, and to stop the scripts that I have created before overrunning into the morning batch. The previous version of the reorganisation script attempted to be to “clever” and do an online reorg of non partitioned tables and an offline reorg of the partitions of the range partitioned tables. The problem with this is that capturing when the online reorgs have finished (as they are asynchronous), so that the table can have it statistics run so that it is not identified again by the SYSPROC.REORGCHK_TB_STATS stored procedure. Equally another issue is that you would have to reorganise the index’s on the tables that you have on-line reorganised as they would not have been done, where as an offline reorganisation also does the indexes at the same time.

So I made the decision to do all the reorganisations offline, followed by a runstats and a rebind. The main controlling stored procedure looks like:

CREATE PROCEDURE DB_MAIN.RUN_ALL_AUTOMATED_MAINTENANCE(IN MAINT_SCHEMA VARCHAR(255), IN REORG_FINISH_TIME TIME, IN RUNSTATS_FINISH_TIME TIME, IN DAY_TO_REMOVE INTEGER)
LANGUAGE SQL
BEGIN
 ----------------------------------------------------------------------------
 ----------------------------------------------------------------------------
 --This procedure is the wrapper for all the rest to tidy it up a little bit.
 --It will only run the reorgs tille the time specified, then will just finish the one
 --that it is on once the time has expired.
 --Similar thing for the runstats so that it does not impact on the running of the
 --morning loads.
 --Rebind the procedures so that they get new packages based on the updated statistics
 --from the reorg and runstats.
 --All Reorg done off line as this is what DB2 does.
 --MAINT_SCHEMA = The schema you wish to be looked at
 --REORG_FINISH_TIME = The time you wish the reorgs to run until
 --RUNSTATS_FINISH_TIME = The time you wish runstats to run till
 --DAY_TO_REMOVE = The number of day back you wish staging tables to be emptied from
 ----------------------------------------------------------------------------
 ----------------------------------------------------------------------------

 ----------------------------------------------------------------------------
 ----------------------------------------------------------------------------
 --Reorg the tables
 CALL DB_MAIN.RUN_AUTOMATED_TABLE_REORG(MAINT_SCHEMA, REORG_FINISH_TIME, DAY_TO_REMOVE);
----------------------------------------------------------------------------
 ----------------------------------------------------------------------------
 --Runstat the tables that have been reorged
 CALL DB_MAIN.RUN_AUTOMATED_TABLE_RUNSTATS(MAINT_SCHEMA, RUNSTATS_FINISH_TIME,DAY_TO_REMOVE);
----------------------------------------------------------------------------
 ----------------------------------------------------------------------------
 --Rebind the stored procedures to take advantage of the potentially new plans
 CALL DB_MAIN.RUN_AUTOMATED_REBIND_PROCEDURES(MAINT_SCHEMA);

END

This is now a three stage operation, the first two stages have time limits and so they will carry out new operations until this time limit is breached. What you have to realise here is that if the end time is 18:00:00 then it will start work right up until 17:59:59, this means if it picks up a particularly large reorganisation task at this last second then it will run till it has finished.

Some of the code especially the runstats stuff is quite a lot like the previous version just with a change for the time. As I cant upload a single .zip file as apparently it will be a security risk, and apparently a .sql file is also a risk please find a number of .doc files a the bottom of the article. Please just change the file extension and then you will be able to access them. I would very interested in having feedback from anyone who uses this code to see how you get on with it.

DISCLAIMER: As stated at the top of the blog use this code in your production systems at your own peril. I have tested and know it works on my systems, please test and check it works on yours properly as reorganising tables can potentially dangerous.

FILES WITH CODE IN:

OverallRunnerStoredProcedure

ReorganiseTablesStoredProcedures

ReorganiseTableTables

ReorganiseTableViews

RunstatsTableTables

RunstatsTableViews

RunstatsTableStoredProcedures

RebindSchemaStoredProcedure

Posted in: DB2, DB2 Administration, DB2 built in tables, DB2 built in Views, DB2 Built-in Stored Procedures, DB2 Maintenance, IBM, Rebind Stored Procedure, Reorg Index, Reorg Table, Reorganise Index, Runstats, SYSIBM.SYSDATAPARTITIONS, SYSIBM.SYSTABLES, SYSIBMADM.SNAPUTIL, SYSPROC.ADMIN_CMD, SYSPROC.ADMIN_CMD, SYSPROC.REBIND_ROUTINE_PACKAGE, SYSPROC.REORGCHK_IX_STATS, SYSPROC.REORGCHK_TB_STATS

IDUG – EMEA – 16th – Day Three

November 16, 2011 11:04 pm / Leave a Comment / dangerousDBA

Today was another long day, but was ended by an excellent dinner put on by IBM to thank its customers, with ostrich leg and proper sushi so now we know where all out licensing fee goes!! The talks that I attended did not teach me as much as I had hoped, but I did learn something in each of them though so not a total waste of time.

A DBA’s guide to using TSA – Fredric Engelen

This covered the basics of HADR and then went on to cover how you set up the TSA to take over the HADR, and did not cover the TSM that I hoped it would that I will be implementing soon at Holiday extras. Learn’t:

  1. db2rfpen – Will let force the rollforward of the primary database.

Managing DB2 Performance in an Heterogeneous environment – Jim Wankowski

This covered the differences and similarities between DB2 LUW and DB2 z/OS. Although it was informative I feel the title was not correct for the session and should have been different. I learnt:

  1. When a Sort happens on a VARCHAR column then the column is expanded to its full length – I may ask this question to Scott Hayes when I do his Rocket Science Seminar on Friday

Deep Dive into DB2 LUW offline table and index reorg – Saeid Mohseni

This session was very good, if you are a frequent reader of my blog then you will know that I am trying to get a straight answer to my questions on Reorganisation and Runstats in DB2 and so I got confirmed and learnt:

  1. DB2 reorgs need the current runstats on the table to be correct to give the correct results for the reorganisation identifying stored procedure.
  2. You can parallel run a reorg on a partitioned table index as long as the first, and subsequent runnings do not allow reads.

Data Warehousing – SIG

This was a little disappointing as it did not have an agenda so was unstructured, and I would have liked to have had a little more information on how it was going to be run. It was informative and if any one has heard of “Data Vaulting” then there is a lady from the Netherlands that would really like to know.

Back to the fifties . . . . . 50 fabulous ways for forecasting failures, flaws and finding flubber – Alexander Kopac

This was an excellent talk and there is a lot to try out when I get back home and enough work to keep us going for week probably. The presenter dressed up as a wizard and the bits of SQL he has given in the slides will hopefully make the DB2 team at HX wizards.One main thing to remember is:

  1. KISS – Keep It Simple Stupid

Useful but widely unknown DB2 Functions – Michael Tiefenbacher

Second talk from this guy and if I did not already know, used or have blogged about all the things that he presented this would have been an extreamly useful and I really should have read the Agenda better before getting in there.
And to the final talk of the day:

DB2 LUW Index design, best practice and case studies – Scott Hayes

This was a very good talk and used in conjunction with Alexander’s information I think will build a framework for reviewing indexes and designs at HX. I learnt that:
  1. I need to read up on CLUSTERED indexes
  2. Single column indexes are not good, even though it is the recommended by IBM
  3. You need a good problem statement to come up with a good solution – Can be applied to everything in life.
Tomorrow is the last day of the conference and so it finishes pretty early and so I might get some sight seeing done in the afternoon, but before that I plan on attending:

Thursday, November 17, 2011

08:30 AM – 09:30 AM
Session 15
1899:I/O, I/O, it’s off to Disk I go – I/O Optimization, Elimination, & SSD (Aquarius)
09:45 AM – 10:45 AM
Session 16
2194:Database I/O in the Brave New World (Aquarius)
11:15 AM – 12:15 PM
Session 17
1892:Understanding and Tuning Page Cleaning in DB2 (Aquarius)
12:30 PM – 01:30 PM
Thursday DB2 Panel
So have a good night and see you all in the morning.
Posted in: Data types, DB2, DB2 Administration, DB2 Ecosystem, DB2 Maintenance, EMEA, IBM, IDUG, Reorg Index, Reorg Table, Reorganise Index, Varchar

DB2 Automated Maintenance Vs Automated Maintenance Scripts

September 11, 2011 8:43 am / Leave a Comment / dangerousDBA

I am the first to admit when I am wrong and accept the consequences but IBM some times do not make it easy to work out what you are meant to be doing and answers of  “it depends” are not entirely helpful, equally I understand that I know what I know about DB2 and I am more than willing to learn. Those of you that have come to my blog before have probably seen the articles that I have done before on stored procedures that you could automate to Reorganise tables and indexes, Runstats on the reorganised tables and then finally rebind the stored procedures. These recently have started to overrun and affect production systems that they were not meant too, so a new way of working needs to be found, therefore we are back to the automated maintenance found in the DB2 ESE product itself or editing the scripts, but from my research automated maintenance does not really do things in the right “order”.

The automated maintenance provided with DB2 from what I understand allows two time periods online window and an offline window, and in essence three different types of work method “do something” or “tell someone who’s bothered” or “do something and tell someone who’s bothered”. In the online window you can carry out runstats and activities that can be carried out on tables without talking them offline. In the offline window DB2 will carry out regorgs of tables in an offline classic reorg. At no point will it will it rebind the stored procedures and in no way are these joined up e.g. if a table is reorganised it will then have the runstats done on it unless DB2 formulas behind the scenes decide too. Where as the scripts and stored procedures I created will do everything in order, but is it needed.

I was listening to the excellent free webinar (live) from DBI on “DB2 LUW Vital Statistics – What you need to know” (replay download at the bottom) and listening to guest John Hornibrook explain how and what you can set in DB2 to gather statistics was elightening and I learned so all good. Having been researching the automated maintenance I thought of a question “Do you need to runstats after a table / index reorg?”, the host thought that he knew the answer, but I think John threw him a little bit of a curve ball by responding with (something like) “well the data has not changed but the locations and distribution on disk have changed” (would have liked to get the exact quote but no sound on replay I downloaded!), well I was even more confused. I would have loved to have submitted a follow up question but they drew it to a close in short order after that. My next question would have been “Will a table be marked for runstats after it has been reorganised?”.

So on the theme of that question I thought IBM developer works might know and if did have some very useful information on it Automatic table maintenance in DB2, Part 1 and Automatic table maintenance in DB2, Part 2. These articles are very good and explain how automatic table maintenance works, but equally left me with questions. A line in the Part 2:

“If you reorganize the table and do not update the table statistics by issuing a RUNSTATS command, the statistics will still indicate that the table contains a high percentage of overflow rows, and REORGCHK will continue to recommend that the table be reorganized”

But in Part 1 on runstats there is a list of decisions DB2 will make as to wether it needs to runstats:

  1. Check if the table has been accessed by the current workload.
  2. Check if table has statistics. If statistics hare never been collected for this table, issue RUNSTATS on the table. No further checks performed.
  3. Check whether UDI counter is greater than 10% of the rows. If not, no action on the table.
  4. Check whether UDI counter is greater than 50% of the rows, issue RUNSTATS on the table if UDI counter is greater than 50% of the rows.
  5. Check if the table is due for evaluation. No further action performed if the table is not due for evaluation. An internal table is used to track if tables are due for evaluation.
  6. RUNSTATS if the table is small.
  7. if table is large (more than 4000 pages), sample the table to decide whether or not to perform RUNSTATS.
So this seems that a table might not get runstat’ed if it did not fall into these criteria and then it would keep being targeted for reorganisation. Another thing that intrigued me was that:

“All scheduled reorganizations (and other automatic maintenance operations, like automatic runstats) are maintained in a queue. When the corresponding maintenance window begins, reorganizations are performed one after another until the end of the window”

So if your tables are large or your window when your tables can not be accessed is short then not a lot of work will be done. It is not multi threaded like the stored procedures that I wrote, but it does have one advantage that the reorganisation phase is to a window, something that is not built into my scripts. Equally the stored procedures have their disadvantages as the reorganisation is IO heavy and the runstats is CPU heavy, so if you have multiples of these things going off all could be at different stages and become quite a load on the server.

I think that the solution is that automatic maintenance is useful just to keep your runstats ticking over during the week because as explained by John this automation is very “light” and also can be set to evaluate before a query is run, but for reorganisation I think I am going to write a new version of the scripts and stored procedures that I blogged about before and build in time windows that work will be carried out under because it is a more joined up way of doing things and also will include the rebind which is essential for DB2 knowing the best execution plan for stored procedures.

I would love to know your experience with automatic maintenance or other methods of keeping your reorganisations and runstats up to date so please feel free to comment on this posting.

Posted in: DB2, DB2 Administration, DB2 built in Views, DB2 Maintenance, IBM, Rebind Stored Procedure, Reorg Index, Reorg Table, Reorganise Index, Runstats, SYSIBMADM.SNAPTAB_REORG

DB2 Table maintenance automated

February 27, 2011 9:39 am / Leave a Comment / dangerousDBA

I have done several previous posts on this subject but I would like to bring them together here, and show you the process that I run to maintain my tables when I want too, not when DB2 decides is best. So I use my four processes that I have built so far from the articles Automated DB2 Table Reorganisation, Automated DB2 Index Reorganisation, Automated DB2 Runstats and Automated DB2 Stored procedure Rebinding and write a wrapper stored procedure to encapsulate them all.

The wrapper stored procedure is fairly simple:

CREATE PROCEDURE DB_MAIN.AUTOMATED_MAINTAIN_GLOBAL_SCHEMA()
LANGUAGE SQL
BEGIN
    CALL DB_MAIN.AUTOMATED_REORG('S','GLOBAL');
    CALL DB_MAIN.AUTOMATED_REORG_INDEX('S','GLOBAL');
    CALL DB_MAIN.AUTOMATED_RUNSTATS_TABLE('S', 'GLOBAL');
    CALL DB_MAIN.REBIND_PROCEDURES ('GLOBAL');
END

This is the stored procedure that runs and keeps in line the GLOBAL schema that we have, obviously you can change GLOBAL to anything that you like. The stored procedure can then be used to automate anyway you like, the only gotcha is that the user that runs the script has to have the authority to carry out the commands. This means you can run it from a query window in the likes of Control Centre or IBM Data Studio, a bash script or a file that is run by the db2 command.

As for the schedule that we employ this is still under some testing, but due to the fact that you can never tell how long a offline table reorganisation will take and that once it has started there is no way to pause or stop it, unlike the online table reorganisations I have the 4 R’s (Reorg Table, Reorg Index, Runstats and Rebind) process running on each schema every other week. The tables that are identified for processing should only be the ones that have been identified by the inbuilt assessment stored procedures SYSPROC.REORGCHK_TB_STATS and SYSPROC.REORGCHK_IX_STATS or the tables that have been identified in table in the runstats part of the process, this should shorted the time taken to process, but I have found in testing the timings the reorganisations are greatly affected by the other processes that are going on in the instance / box at the time.

Posted in: DB2, DB2 Administration, DB2 Maintenance, IBM, Rebind Stored Procedure, Reorg Index, Reorg Table, Reorganise Index, Runstats, SYSPROC.REORGCHK_IX_STATS, SYSPROC.REORGCHK_TB_STATS

Automated DB2 Index Reorganisation

February 2, 2011 8:57 pm / 4 Comments / dangerousDBA

In my last post I typed about reorganisation of tables DB2, in this post I am going to talk about the additional code I have developed that will reorganise indexes. Again DB2 9.7 comes with an in built command for assessing the indexes on a table or the tables in a schema. As per last post I am not going to go into detail that can be found here, and again a brief overview is below:

CALL SYSPROC.REORGCHK_IX_STATS(<S or T>, <Schema Name or Table Name>)

As per the REORGCHK_TB_STATS you can access the output of stored procedure by running the following query:

SELECT *
FROM SESSION.IX_STATS

In this table there are columns for identifying the tables, indexes and partitions the indexes reside on, the ones that have been identified for reorganisation will have one or more * in the REORG column found at the end of the table. I have done a little research (probably room for more) on the tables that I have available to me at work, and it seems that  on a partitioned tables it will identify all the indexes that cover all the partitions that need reorganising. It is possible to manually write a command and reorganise  just the indexes that reside on the partitions. As what I actually need to do is reorganise automatically then I opted for a method that was basically a carbon copy of the table reorganisation methods.

Using the following SQL it is possible to identify the indexes that have been identified by stored procedure that need processed:

SELECT *
FROM SESSION.IX_STATS 
WHERE REORG LIKE '%*%'

Like in the table reorganisation code I will give you a light overview of what I have got working and include all the code at the end of the post. As per last time before you compile any of these stored procedures you will need to fool DB2 into thinking that the SESSION.IX_STATS table actually exists, to do this you will have too run the SYSPROC.REORGCHK_IX_STATS on viable criteria.

The other system object that I will be using is SYSIBMADM.SNAPTAB_REORG, the full IBM run-down on this view can be found here. A brief overview is that it will show the tables that are currently being reorganised. Why does this matter, well if you try to reorganise the index while the table reorganisation is still going on then it will result in errors, this mostly needs to be protected against when tables are having ONLINE reorganisations carried out on them as they are asynchronous. This view will show you what is going on reorg wise and be able to be monitored for them finishing. You can use SYSIBMADM.SNAPTAB_REORG columns REORG_STATUS and REORG_COMPLETION to assess if the index reorganisation should go ahead.

Using the SYSPROC.REORGCHK_IX_STATS stored procedure and the SESSION.IX_STATS table it is possible to identify the index’s that need reorganisation, then loop round the ones that need doing, I have used the following code to make sure that I don’t try to reorganise an index on a table that is currently being reorganised, this allows me to loop over the “busy” ones and carry on with other index reorganisation:

SELECT CASE WHEN ((REORG_STATUS = 'COMPLETED') AND (REORG_COMPLETION = 'SUCCESS')) THEN 1  --Table Reorg Success
            WHEN ((REORG_STATUS = 'COMPLETED') AND (REORG_COMPLETION = 'FAIL')) THEN 2 --Table Reorg Fail       
            ELSE 3 -- Still going
      END                                       
FROM SYSIBMADM.SNAPTAB_REORG                                       
WHERE (TABNAME, TABSCHEMA, REORG_START) IN (SELECT TABNAME,                                                                                            
                                                   TABSCHEMA,                                                                                            
                                                   MAX(REORG_START) MAX_REORG_START                                                                                   
                                            FROM SYSIBMADM.SNAPTAB_REORG                                                                                   
                                            GROUP BY TABNAME,                                                                                            
                                                     TABSCHEMA)                                            
       AND LTRIM(RTRIM(TABSCHEMA)) || '.' || LTRIM(RTRIM(TABNAME)) = REORG_TAB_SCHEMA || '.' || REORG_TAB_NAME);

I get the MAX(REORG_START) just in case there has been more than one in a day. If you download and view the code you will see how the above statement a table and a view allow me to cycle over and come back to busy indexes. I am not going to go into the code  in as much depth as the last article as it is very much the same with reusable stored procedures that can be used all together or separately.  So please download the code and take a look, and see if it can help you.

DISCLAIMER: As stated at the top of the blog use this code in your production systems at your own peril. I have tested and know it works on my systems, please test and check it works on yours properly as reorganising index’s can potentially dangerous. The file is a .doc only as that’s the only way I could get it uploaded onto wordpress, it should open fine like that, or knock the .doc off and it will open in your favourite text editor.

FILE WITH CODE IN: DB2_Automated_Reorg_Indexes_SP_V_T_DCP

Posted in: DB2, DB2 Administration, DB2 built in Views, DB2 Built-in Stored Procedures, DB2 Maintenance, IBM, Reorg Index, Reorganise Index, SYSIBMADM.SNAPTAB_REORG

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 757 other subscribers

Recent Posts

  • Self generating Simple SQL procedures – MySQL
  • Google Cloud Management – My Idea – My White Whale?
  • Position Tracker – The Stub – Pandas:
  • Position Tracker – The Stub
  • Position Tracker – In the beginning
  • Whats been going on in the world of the Dangerous DBA:
  • QCon London Day 1
  • Testing Amazon Redshift: Distribution keys and styles
  • Back to dangerous blogging
  • DB2 10.1 LUW Certification 611 notes 1 : Physical Design

Dangerous Topics

added functionality ADMIN_EST_INLINE_LENGTH Bootcamp colum convert data types DB2 db2 DB2 Administration DB2 Development db2advis db2licm Decompose XML EXPORT GCP Google IBM IBM DB2 LUW idug information centre infosphere IOT LOAD merry christmas and a happy new year Position Tracking python Recursive Query Recursive SQL Reorganisation Reorganise Reorganise Indexes Reorganise Tables Runstats sql statement Stored Procedures SYSPROC.ADMIN_CMD Time UDF User Defined Functions V9.7 V10.1 Varchar XML XML PATH XMLTABLE

DangerousDBA Links

  • DB2 for WebSphere Commerce
  • My Personal Blog

Disclaimer:

The posts here represent my personal views and not those of my employer. Any technical advice or instructions are based on my own personal knowledge and experience, and should only be followed by an expert after a careful analysis. Please test any actions before performing them in a critical or nonrecoverable environment. Any actions taken based on my experiences should be done with extreme caution. I am not responsible for any adverse results. DB2 is a trademark of IBM. I am not an employee or representative of IBM.

Advertising

© Copyright 2022 - Dangerous DBA
Infinity Theme by DesignCoral / WordPress