Ingres mdb housekeeping
Download
1 / 24

Ingres MDB Housekeeping - PowerPoint PPT Presentation


  • 701 Views
  • Updated On :

Ingres MDB Housekeeping. Script for General MDB Housekeeping Revised April 2 2006. Mission. This presentation explains the standard Ingres housekeeping operations packaged in the housekeeping script supplied with the MDB.

Related searches for Ingres MDB Housekeeping

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Ingres MDB Housekeeping' - Roberta


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Ingres mdb housekeeping l.jpg

Ingres MDB Housekeeping

Script for General MDB Housekeeping

Revised April 2 2006


Mission l.jpg
Mission

  • This presentation explains the standard Ingres housekeeping operations packaged in the housekeeping script supplied with the MDB.

  • You are expected to configure and adapt this script (at a minimum you should incorporate your preferred backup tool).

  • It is best practices to run an Event Agent on the MDB machine. This script will use it if present!


Overview l.jpg
Overview

  • Performance is paramount to all mission critical applications. In order to help keeping Ingres performing at its best, we put together a script that helps performing housekeeping tasks such as:

    • USERMOD - useful utility for maintaining user tables. Improves the performance of query processing

    • OPTIMIZEDB - generates statistics that are used by the Ingres Query Optimizer to select an efficient query processing strategy

    • CKPDB - checkpoints a database

      • Then moves CHECKPOINT, DUMP and JOURNAL files not needed to recover the MDB from the current (i.e. last) CHECKPOINT sequence

  • Windows only at the moment


Optimization and backup l.jpg

Optimization and Backup

© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.


Optimization and backup5 l.jpg
Optimization and Backup

  • As you saw in Proper Care and Feeding of your MDB, Optimization and Backup are two very important aspects

  • The first step here is to optimize the MDB and then checkpoint it:

    • usermod -umdbadmin -online mdb

    • optimizedb -zk -zw -umdbadmin mdb

    • ckpdb -umdbadmin mdb


Finding mdb locations l.jpg

Finding MDB Locations

© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.


Mdb locations l.jpg
MDB Locations

  • It is fundamental to find CHECKPOINT, DUMP, and JOURNAL locations in order to keep files needed to recover from the LAST checkpoint sequence online

  • We parse the output (refer to Proper Care and Feeding of your MDB for examples) of infodb mdb to find those locations. So, you will see commands like the ones below in the script:

    • infodb mdb | findstr \ckp > .\II_TEMP.TXT

    • for /F "tokens=3,4,5,6,7,8*" %%i in ('type .\II_TEMP.TXT') do set II_CHECKPOINT=%%i %%j %%k %%l %%m %%n

    • @echo Checkpoint files for the MDB are located at: %II_CHECKPOINT%


Mdb locations8 l.jpg
MDB Locations

  • As we find out locations, we create OFFLINE folders for the files that are not needed ONLINE – i.e. files that can be moved to OFFLINE media. Example:

    • @echo Creating OFFLINE folder for CHECKPOINT files...

    • for /F "tokens=1 delims=\" %%i in ('echo %II_CHECKPOINT%') do set II_DELIM=%%i

    • if not exist %II_DELIM%\OFFLINE\CKP\DEFAULT\MDB md %II_DELIM%\OFFLINE\CKP\DEFAULT\MDB

    • set CKP_OFFLINE=%II_DELIM%\OFFLINE\CKP\DEFAULT\MDB


Finding current checkpoint sequence l.jpg

Finding Current CHECKPOINT Sequence

© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.


Finding current checkpoint sequence10 l.jpg
Finding Current CHECKPOINT Sequence

  • Once we are done with locations, we proceed to find which CHECKPOINT sequence is the last (i.e. current) one

  • We find out which sequence is the last one by parsing the output of infodb mdb:

    • for /F "tokens=4" %%i in ('findstr /i /c:"Checkpoint sequence" .\II_TEMP.TXT') do set CUR_CKP_SEQ=%%i

  • Now that we know what the current CHECKPOINT sequence is, all others can be moved to the OFFLINE folder


Journal history for current checkpoint sequence l.jpg

JOURNAL History for Current CHECKPOINT Sequence

© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.


Journal history for current checkpoint sequence12 l.jpg
JOURNAL History for Current CHECKPOINT Sequence

  • Now that we know which CHECKPOINT sequence is the current (last) one, we will find which JOURNAL files are needed to recover from that CHECKPOINT sequence

  • First, we find out the LAST JOURNAL needed:

    • for /F "tokens=8" %%i in ('findstr /i /c:"Journal sequence" .\II_TEMP.TXT') do set LAST_JNL_SEQ=%%i


Journal history for current checkpoint sequence13 l.jpg
JOURNAL History for Current CHECKPOINT Sequence

  • Now that we know what JOURNAL file is the last one for the current CHECKPOINT sequence, we will find which file is the first one:

    • for /F "tokens=7" %%i in ('findstr /i /c:"%LAST_JNL_SEQ% 1" .\II_TEMP.TXT') do set FIRST_JNL_SEQ=%%i

  • Now that we know what JOURNAL files are needed to recover from the current CHECKPOINT sequence, all other files can be moved to the OFFLINE folder – i.e. all files except those in the FIRST_JNL_SEQ – LAST_JNL_SEQ range


Moving journal files to the offline folder l.jpg
Moving JOURNAL Files to the OFFLINE Folder

  • We use the XCOPY command to “move” JOURNAL files to the OFFLINE folder

  • First, we put the EXCLUDE LIST together – i.e. files that won’t be “moved” (all files except those in the FIRST – LAST JOURNAL range):

    • for /L %%i in (%FIRST_JNL_SEQ%,1,%LAST_JNL_SEQ%) do dir /b *%%i.jnl >> .\II_TEMP.TXT

  • Then, we use the exclude list in conjunction with the XCOPY command to copy files to the OFFLINE folder:

    • for /F "tokens=1 delims=\" %%i in ('echo %II_JOURNAL%') do set II_DELIM=%%i

    • xcopy *.JNL %II_DELIM%\OFFLINE\JNL\DEFAULT\MDB /i /exclude:.\II_TEMP.TXT /y


Moving journal files to the offline folder15 l.jpg
Moving JOURNAL Files to the OFFLINE Folder

  • We say we use the XCOPY command to “move” JOURNAL files to the OFFLINE folder because now that XCOPY was used to COPY files to that folder, files can be deleted from the JOURNAL location:

    • for /F "tokens=1* delims=%II_DELIM%" %%i in ('xcopy *.JNL %II_DELIM%\OFFLINE\JNL\DEFAULT\MDB /i /exclude:.\II_TEMP.TXT /y /l ^| find ":"') do del /F /Q %%i


Dump history for current checkpoint sequence l.jpg

DUMP History for Current CHECKPOINT Sequence

© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.


Dump history for current checkpoint sequence17 l.jpg
DUMP History for Current CHECKPOINT Sequence

  • The next steps are similar to the ones used to find FIRST and LAST JOURNALS for the current CHECKPOINT sequence

  • First, we determine the LAST DUMP needed:

    • for /F "tokens=8" %%i in ('findstr /i /c:"Dump sequence" .\II_TEMP.TXT') do set LAST_DMP_SEQ=%%i


Dump history for current checkpoint sequence18 l.jpg
DUMP History for Current CHECKPOINT Sequence

  • Now that we know what DUMP file is the last one for the current CHECKPOINT sequence, we will find out what file is the first one:

    • for /F "tokens=7" %%i in ('findstr /i /c:"%LAST_DMP_SEQ% 1" .\II_TEMP.TXT') do set FIRST_DMP_SEQ=%%i

  • Now that we know the JOURNAL files needed to recover from the current CHECKPOINT sequence, all other files can be moved to the OFFLINE folder – i.e. all files except in the FIRST_DMP_SEQ – LAST_DMP_SEQ range


Moving dump files to the offline folder l.jpg
Moving DUMP Files to the OFFLINE Folder

  • We use the XCOPY command to “move” DUMP files to the OFFLINE folder

  • First, we put the EXCLUDE LIST together – i.e. files that won’t be “moved” (all files except those in the FIRST – LAST DUMP range):

    • for /L %%i in (%FIRST_DMP_SEQ%,1,%LAST_DMP_SEQ%) do dir /b *%%i.DMP >> .\II_TEMP.TXT

    • for /L %%i in (%FIRST_DMP_SEQ%,1,%LAST_DMP_SEQ%) do dir /b *%%i*.LST >> .\II_TEMP.TXT

    • for /L %%i in (%CUR_CKP_SEQ%,1,%CUR_CKP_SEQ%) do dir /b *%%i.DMP >> .\II_TEMP.TXT

    • for /L %%i in (%CUR_CKP_SEQ%,1,%CUR_CKP_SEQ%) do dir /b *%%i*.LST >> .\II_TEMP.TXT

    • dir /b *.CNF >> .\II_TEMP.TXT

    • dir /b *.TXT >> .\II_TEMP.TXT


Moving dump files to the offline folder20 l.jpg
Moving DUMP Files to the OFFLINE Folder

  • Then, we use the exclude list in conjunction with the XCOPY command to copy files to the OFFLINE folder:

    • xcopy *.* %II_DELIM%\OFFLINE\DMP\DEFAULT\MDB /i /exclude:.\II_TEMP.TXT /y

  • We say we use the XCOPY command to “move” DUMP files to the OFFLINE folder because now that XCOPY was used to COPY files to that folder, files can be deleted from the DUMP location:

    • for /F "tokens=1* delims=%II_DELIM%" %%i in ('xcopy *.* %II_DELIM%\OFFLINE\DMP\DEFAULT\MDB /i /exclude:.\II_TEMP.TXT /y /l ^| find ":"') do del /F /Q %%i


Moving checkpoint files to the offline folder l.jpg

Moving CHECKPOINT Files to the OFFLINE Folder

© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.


Moving checkpoint files to the offline folder22 l.jpg
Moving CHECKPOINT Files to the OFFLINE Folder

  • Now that we know what the current CHECKPOINT sequence is, all other files can be moved to the OFFLINE folder

  • Put the XCOPY exclude list together for CHECKPOINT files:

    • dir /b *%CUR_CKP_SEQ%*.CKP >> .\II_TEMP.TXT

    • dir /b *.TXT >> .\II_TEMP.TXT

  • We use the XCOPY command to “move” CHECKPOINT files to the OFFLINE folder:

    • for /F "tokens=1 delims=\" %%i in ('echo %II_CHECKPOINT%') do set II_DELIM=%%i

    • xcopy . %II_DELIM%\OFFLINE\CKP\DEFAULT\MDB /i /exclude:.\II_TEMP.TXT /y /s


Moving journal files to the offline folder23 l.jpg
Moving JOURNAL Files to the OFFLINE Folder

  • We say we use the XCOPY command to “move” CHECKPOINT files to the OFFLINE folder because now that XCOPY was used to COPY files to that folder, files can be deleted from the CHECKPOINT location:

    • for /F "tokens=1*" %%i in ('xcopy . %II_DELIM%\OFFLINE\CKP\DEFAULT\MDB /i /exclude:.\II_TEMP.TXT /y /s /l ^| find ".\"') do del /F /Q %%i