Proper Care and Feeding of your Ingres MDB

Proper Care and Feeding of your Ingres MDB - Recommendations for General MDB Maintenance - Read the notes on the foils! - Revised 07/24/2006

Overview - Well thought-out MDB installation is only the beginning - Regular Maintenance Tasks - Optimization and Tuning Tips - Relocating Files - Troubleshooting - Backup/Recovery recommendations - List of Useful Commands © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Before You Begin © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names,

Things you should do - Install Ingres on a disk with enough room available (a minimum of 100 GB free space) – if possible, create Ingres Transaction Log(s) on separate disk(s) - Checkpoint, journal, and dump MUST be on a separate disk - Maintain the MDB often – optimizedb and ckpdb - Manage System Tables - Plan for MDB Backup/Recovery - Use appropriate MDB Settings – MEDIUM and LARGE © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Logging Considerations - Ingres allows both the primary and dual transaction log (optional) files to exist on multiple devices (up to 16). Ingres improves logging system performance by allowing it to write to multiple disk devices. - When configuring multiple log partitions, keep the following in mind: - All log partitions must be the same size - The primary and dual transaction logs must use the same number of partitions. - Minimum recommended transaction log size is 3 GB. © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Logging Considerations - The Ingres log is a primary limiting factor for scalability - Log tuning is the first place to go if you cannot change a slow update query - Logs MUST be on safe DASD (protected by RAID) - If you lose the log you cannot recover the database © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Transaction Log Location and Size - Some products may not give you a chance to specify where Ingres files should be stored – i. e. NSM r 11 - You may need to increase the Transaction Log size depending upon data volume. In some cases 2 – 3 GB may be more appropriate - Here is what should be done in order to configure Transaction Logs - Shutdown all products using the MDB as well as Ingres - Launch CBF from a Command Prompt, highlight Transaction Log and hit Shift+F 1 © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Transaction Log Location and Size - Hit Shift+F 4 (Destroy) and select Yes – destroy Transaction log © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Transaction Log Location and Size - If you want to create the Transaction Log on a different disk, hit Shift+F 3 (Delete), followed by Shift+F 1 (Insert), and enter the new location © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Transaction Log Location and Size - Now that a new location was picked, hit Shift+F 1 (Create) and enter the new size of the transaction log in MB © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Supplied MDB Settings - Medium - Default MDB settings - Supports Windows XP Pro – 4 GB Virtual Memory required - Greatly improves Ingres performance by tuning key parameters dbms. *. connect_limit 500 dbms. *. qef_hash_mem 49152000 dbms. *. qef_sort_mem 8192000 dbms. *. qef_sorthash_memory 675840000 dbms. private. *. p 8 k. dmf_cache_size 15000 dbms. private. *. p 8 k. dmf_group_count 4500 dbms. private. *. p 16 k. dmf_cache_size 3000 dbms. private. *. p 16 k. dmf_group_count 0 dbms. private. *. p 32 k. dmf_cache_size 2500 dbms. private. *. p 32 k. dmf_group_count 0 dbms. *. log_writer 10 dbms. *. max_tuple_length 0 dbms. *. default_page_size 8192 dbms. *. active_limit 256 dbms. *. system_readlock shared gcc. *. inbound_limit 500 gcc. *. outbound_limit 500 rcp. log. archiver_interval 1 ingstart. *. rmcmd 1 gcn. session_limit 64 rcp. lock. list_limit 2048 rcp. lock. per_tx_limit 3000 rcp. log. database_limit 10 rcp. log. tx_limit 100 rcp. dmf_cache_size 8 k 200 dbms. *. qef_qep_mem 25600 dbms. *. rep_qman_threads 0 dbms. *. system_maxlocks 500 dbms. *. opf_memory 36700160 © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Supplied MDB Settings - Large - setupmdb -II_MDB_SIZE=LARGE - Takes full advantage of Server class hardware: dbms. *. connect_limit 1000 rcp. lock. per_tx_limit 3000 dbms. *. qef_hash_mem 65536000 dbms. *. qef_sort_mem 8192000 dbms. *. qef_sorthash_memory 768000000 dbms. private. *. dmf_cache_size 15000 dbms. private. *. dmf_group_count 1500 dbms. private. *. dmf_memory 55296000 dbms. private. *. p 16 k. dmf_cache_size 5500 dbms. private. *. p 16 k. dmf_group_count 0 dbms. private. *. p 16 k. dmf_memory 90112000 dbms. private. *. p 32 k. dmf_cache_size 5500 dbms. private. *. p 32 k. dmf_group_count 0 dbms. private. *. p 32 k. dmf_memory 180224000 dbms. private. *. p 4 k. dmf_cache_size 15000 dbms. private. *. p 4 k. dmf_group_count 1000 dbms. private. *. p 4 k. dmf_memory 94208000 dbms. private. *. p 8 k. dmf_cache_size 25000 dbms. private. *. p 8 k. dmf_group_count 5500 dbms. private. *. p 8 k. dmf_memory 565248000 dbms. *. log_writer 10 dbms. *. max_tuple_length 0 dbms. *. default_page_size 8192 dbms. *. active_limit 1000 dbms. *. opf_memory 36700160 gcc. *. inbound_limit 500 gcc. *. outbound_limit 500 rcp. log. archiver_interval 1 ingstart. *. rmcmd 1 gcn. session_limit 64 rcp. lock. list_limit 2048 rcp. log. database_limit 10 rcp. log. tx_limit 100 rcp. dmf_cache_size 8 k 200 dbms. *. qef_qep_mem 25600 dbms. *. rep_qman_threads 0 dbms. *. system_maxlocks 500 dbms. *. system_readlock shared © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

MDB Configuration - Supplied configurations have many tradeoffs – they were designed to support all products as we do not know which products are to be installed - One can do custom product specific configs with much better single product performance - NSM is the only product needing huge numbers of connections and a high locks per transaction value - You can reclaim much of the virtual storage when NSM is not present – this can be used for other purposes © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

MDB Custom Settings – NO NSM! - If the MDB is not being used by NSM, the parameters below can be custom set to the following values: gcn. session_limit 16 rcp. lock. per_tx_limit 750 dbms. *. active_limit 1000 dbms. private. *. p 4 k. dmf_cache_size 20000 dbms. private. *. p 4 k. dmf_group_count 1500 dbms. private. *. p 8 k. dmf_cache_size 55000 dbms. private. *. p 8 k. dmf_group_count 8500 dbms. private. *. p 16 k. dmf_cache_size 6500 dbms. private. *. p 32 k. dmf_cache_size 6500 - The values above increase performance across the board © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

MDB Custom Settings – Startup Count - Multiple CPUs – allow parallel transactions © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

MDB Custom Settings – Startup Count - Multiple CPUs – multiple transactions © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

MDB Custom Settings – Startup Count - Multiple NICs – default for MEDIUM/LARGE © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

CBF – Configuration By Forms - Ingres parameters can be configured via CBF Configuration By Forms - Example: configuring dbms. private. *. p 8 k. dmf_cache_size - Open a Command Prompt Window and type in cbf. © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

CBF – Configuration By Forms - Highlight DBMS Server using the down arrow key and then press Shift+F 1 (Configure) © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

CBF – Configuration By Forms - Press Shift+F 3 to access Configure DBMS Caches - Highlight DMF Cache 8 K using the down arrow key and then press Shift+F 1 (Configure) © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

CBF – Configuration By Forms - Press Shift+F 1 (Configure) to access Configure DBMS Cache Definition for 8 K Buffers © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

CBF – Configuration By Forms - The parameter we want to configure is a derived one. So, press Shift+F 2 © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

CBF – Configuration By Forms - Then, highlight dmf_cache_size and press Shift+F 1 (Edit) - Enter the new value: 55000 - Press Enter © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

CBF – Configuration By Forms - Keep pressing F 10 until you get the following prompt: Save changes to DBMS Server Parameters? - Highlight Yes and then press Enter - Once you get back to the Configuration By Forms screen, press F 6 to exit © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Ingres Configuration Manager - Ingres parameters can also be configured via Configuration Manager UI - Example: configuring dbms. private. *. p 8 k. dmf_group_count - Launch the Ingres Configuration Manager utility in the Ingres [ EI ] program group © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Ingres Configuration Manager - On the left panel expand the DBMS Servers tree and select (default) – all DBMS parameters will be displayed on the right panel © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Ingres Configuration Manager - The parameter we want to configure is a derived Cache definition for 8 K Buffers. So, click on the Cache tab on the right panel, highlight DMF Cache 8 K, and then click on the Derived tab © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Ingres Configuration Manager - Highlight dmf_group_count for 8 K buffers and double-click on its value or Edit Value button. Then, type in the new value (8500 in this case) and press Enter - Click on Close when you are done © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

MDB Custom Settings –Navigating CBF and Ingres Configuration Manager - gcn. session_limit - Select Name Server – scroll down, highlight and edit session_limit - rcp. lock. per_tx_limit - Select Locking System – highlight and edit per_tx_limit - dbms. *. active_limit - Select DBMS Server – Derived tab/screen – highlight and edit active_limit - dbms. private. *. p 4 k. dmf_cache_size - Select DBMS Server – Cache tab/screen – highlight DMF Cache 4 K – Derived tab/screen – highlight and edit dmf_cache_size - dbms. private. *. p 4 k. dmf_group_count - Select DBMS Server – Cache tab/screen – highlight DMF Cache 4 K – Derived tab/screen – highlight and edit dmf_group_count © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

MDB Custom Settings –Navigating CBF and Ingres Configuration Manager - dbms. private. *. p 8 k. dmf_cache_size - Select DBMS Server – Cache tab/screen – highlight DMF Cache 8 K – Derived tab/screen – highlight and edit dmf_cache_size - dbms. private. *. p 8 k. dmf_group_count - Select DBMS Server – Cache tab/screen – highlight DMF Cache 8 K – Derived tab/screen – highlight and edit dmf_group_count - dbms. private. *. p 16 k. dmf_cache_size - Select DBMS Server – Cache tab/screen – highlight DMF Cache 16 K – Derived tab/screen – highlight and edit dmf_cache_size - dbms. private. *. p 32 k. dmf_cache_size - Select DBMS Server – Cache tab/screen – highlight DMF Cache 32 K – Derived tab/screen – highlight and edit dmf_cache_size © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Suggested Regular Maintenance © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names,

Maintenance and Performance Tuning - MDB must be regularly maintained to produce good performance - Rebuild indexes, regenerate stats to reflect reality – usermod, optimizedb, ckpdb commands. - If you suddenly experience a performance issue or run out of disk space – think DB Maintenance! - You WILL run out of disk space if you forget to do maintenance © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Optimizedb - daily - Can be run while db is active - Should be run whenever table is rebuilt or has major changes. Recommended – daily! - Syntax: optimizedb -zk -zw -umdbadmin mdb - If you add a lot of data, then run optimizedb before doing extensive queries – so, think about optimizedb after Argis reconciliation or a large Desktop Management replication finishes or after a large NSM discovery or after importing data into Service Desk (can make 3 orders of magnitude impact on performance) © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Checkpoint - daily - Takes place online (while DB is in use) and is transparent to users - ckpdb command creates new checkpoint sequence, then copies log records of any DB changes that occurred during checkpoint procedure to dump file. - Permission required: System administrator, DBA, or an Ingres user with operator privilege. - Syntax: ckpdb -umdbadmin mdb - IMPORTANT: Free disk space must be monitored closely! Checkpoint and journal files will build up on the disk. However, always keep previous versions of checkpoint, dump, and journal files on tape! We will discuss that in greater detail later – Backup/Recovery Recommendations. © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

usermod - Usermod modifies the user defined tables only to their currently defined storage structure - Recreates any secondary indexes that are currently defined - Fixes overflow pages in a table – improves query processing performance - should be used on a regular (daily suggested) basis when: - Tables are in overflow - Need to reclaim space - Before optimizedb/sysmod - Can be run online where access to table is mostly permitted except at end when usermod takes exclusive access to table. - Syntax: usermod -umdbadmin -online mdb © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Monitor DB Location Size - For 32 -bit OS (W 2 K, Red. Hat ES 3. 0), need to monitor size of files in the DB location. If > 2 GB need to : - Relocate table(file) from one location to another. E. g. , modify employee to relocate With oldlocation = (loc 1, loc 2, loc 3) newlocation = (loc 4, loc 5, loc 6) - Reorganize table(file) across multiple locations. E. g. , modify employee to reorganize with location = (loc 1, loc 2, loc 3) © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

sysmod - sysmod reorganizes the system catalogs for DB to predetermined storage structures - If you create the MDB and have no (or limited) data in it and then use it or bulk load data -> run sysmod - If spread of data changes - > run sysmod - Sysmod requires exclusive access to the DB – and is not for regular live/online maintenance - Syntax: sysmod mdb © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

sysmod - You need a current backup (or two!) before running sysmod - The total time cost can be very high - Should not be done except on a carefully scheduled basis © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Optimization © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks

Statistics - Affect speed of query processing - Generating statistics for DB means effectively optimizing the DB - Run periodically on keys or index columns - Complete and accurate statistics result in greater and more effective query execution strategies - Inaccurate statistics can lead to wrong query execution plans to be chosen by optimizer and, therefore, to dramatic performance reduction © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Statistics - Requires disk space because temporary tables are being created - Need to run this when significant change in column’s value - Stored in system catalogs (iistats and iihistograms) - Used by query optimizer to select an efficient query processing strategy - Syntax: optimizedb <options> dbname/tablenames(s) - Example 1: optimizedb -zv mdb –rai_fddef_prop - Example 2: optimizedb -zk mdb –rai_fddef_prop –rai_fsdef_prop -ahwuuid © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

System Catalogs - Also require maintenance as they are tables themselves and subject to data changes - sysmod will run the appropriate modify commands against the catalogs - sysmod takes an exclusive mode against the DB - Can be done on DB as well as individual tables © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

System Catalogs - symod requires exclusive access to DB (must be run while DB is “quiet”) - Can make this command wait till DB becomes free - Run whenever system catalogs are changes - Syntax: sysmod dbname/tablename(s) - Example 1: sysmod empdata - Example 2: sysmod -w empdata iirelation iiattribute © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Optimization Recommendations - Ingres uses a Cost Based Query Optimizer to develop query execution plans – QEPs. - Optimizing the DB affects speed of query processing. More complete and accurate statistics -> more efficient query execution strategies - > faster system performance. - Without statistics, Query Optimizer can only guess what the data looks like, and “assumptions” are usually not valid for application SQL queries. - In VDBA, use Optimize Database dialog to generate database statistics for DB that is currently selected in Database Object Manager window. - At the command line, use optimizedb command: - Syntax: optimizedb -zk -zw -umdbadmin mdb © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Frequency - How often should the MDB be optimized? - The simple answer is: often! Daily. - During roll-out it is expected that tables are getting populated. - Tables will keep growing – although values in some columns will change very little, others will change significantly. - optimizedb can be executed via shell scripts, batch files, etc. - Optimizing a database generally requires disk space, because temporary tables are created © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Modify System Storage Tables - sysmod command modifies system tables (catalogs) of a database to predetermined storage structures. - sysmod should be run after optimizing the MDB. - System tables are modified to the most appropriate storage structure for accelerating query processing. - sysmod operation requires exclusive access to DB as well as DBA or system administrator privileges. - Syntax: symod mdb © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Relocate Database Files © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names,

Types of Files in an Ingres Database - Data – User tables, indexes, and system catalogs - Checkpoint – Static copy of the entire database - Journal – Dynamic records of changes made to the journaled tables after the last checkpoint. Take regular checkpoints to minimize recovery time and avoid wasting disk space with journal files - Dump – Records of changes to the database that occurred during the dump process. Used to recover databases checkpointed online - Work – Used for system work, such as sorting and creating temporary tables © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Why Relocate? - If data, work, checkpoint, dump and journal files are all currently located on the same disk, relocating them to separate disks will: - Improve fault tolerance and aid recovery from hardware failure (i. e. , disk with data files). - Potentially improve performance - Gain disk space - HIGHLY RECOMMENDED! - IMPORTANT! Checkpoint, journal, and dump files can use only one location each. © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Default Locations - Default Ingres locations are: - ii_checkpoint (checkpoint) - ii_database (data) - ii_dump (dump) - ii_journal (journal) - ii_work (work) - They are all set to the following: - Windows: C: Program FilesCAIngres [EI] - UNIXLinux: /opt/CA/Ingres. EI © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Change Defaults - Windows - To change default locations under Windows edit the following entries in the sample. rsp file: - iidatabasedir="C: INGRESNEWAREA" - iicheckpointdir="D: INGRESAREA" - iijournaldir="D: INGRESAREA" - iidumpdir="D: INGRESAREA" - iiworkdir="C: INGRESNEWAREA © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Change Defaults – UNIX/Linux - Under Linux/Unix, first create a response file and include the following entries in it: - II_DATABASE=<Directory> II_CHECKPOINT=<Directory> II_JOURNAL=<Directory> II_DUMP=<Directory> II_WORK=<Directory> - The directory must exist prior to installing Ingres! Mask: 700. The directory structure must be owned by the user that owns the installation. ingres by default. - Export II_RESPONSE_FILE. Example: - export II_RESPONSE_FILE=/ingres/install. rsp © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Guidelines for Locations - Locations must exist prior to relocation operations - Locations must be specified with appropriate usage type: Journal, Checkpoint, Dump, Data, Work - Existing files are moved to new locations. New files are created there. - Location areas are created using host OS facilities - Each area must have a specific subdirectory structure that parallels that of the corresponding default location: - MD C: INGRESNEWAREAINGRESDATA (or CKP, JNL, DMP, WORK) - MD C: INGRESNEWAREAINGRESDATADEFAULT - New areas are then used in the Create Location dialog (VDBA) or in create location, alter location, drop location SQL statements. © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Create Location Dialog © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names,

Create Location Statement © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names,

Relocating Database Files - Checkpoint - Once new locations are created, use the relocatedb command to relocate DB files. For example: - relocatedb mdb -new_ckp_location=newlocation_checkpoint - This command does the following: - Performs an update to the iidbdb - Updates checkpoint location in the configuration file. - Copies checkpoint files from old checkpoint location (ii_checkpoint) to new checkpoint location (newlocation_checkpoint) - Deletes checkpoint files from old checkpoint location © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Relocating Database Files - Journal Example: - relocatedb mdb -new_jnl_location=newlocation_journal - This command does the following: - Performs an update to the iidbdb - Updates journal location in the configuration file - Copies journal files from old journal location (ii_journal) to new journal location (newlocation_journal) - Deletes journal files from old journal location © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Relocating Database Files - Dump Example: - relocatedb mdb -new_dump_location=newlocation_dump - This command does the following: - Performs an update to the iidbdb - Updates dump location in the configuration file - Copies dump files from old dump location (ii_dump) to new dump location (newlocation_dump) - Deletes dump files from old dump location © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Relocating Database Files - Work - Before relocating work files, we must extend the database to the new work location. For example: - extenddb -lnewlocation_work mdb -Uwork - Once that’s done, tell Ingres that the new work location is the default for the database. For example: - relocatedb mdb -new_work_location=newlocation_work - Next, use the unextenddb command to unextend a database location. In this case, we will unextend the location that used to be the default - ii_work. For example: - unextenddb -lii_work mdb -Uwork - IMPORTANT: After unextending a database location, you should checkpoint the database. Previous checkpoints cannot be used because they reference a location that is no longer accessible to the database! © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Relocating Database Files - Data Relocating data files is possible but not recommended! - Best approach - > plan ahead so that relocating data files is never needed! - If you must relocate data files, first use the relocateddb command to copy the entire MDB. For example: - relocatedb mdb -new_database=newmdb -location=ii_database -new_location=newlocation_data - This command not only copies the entire MDB Database, but will also relocate data files. The default data location for newmdb will be newlocation_data - Checkpoint, dump, journal, and work locations were already relocated. So, the default locations for newmdb are: newlocation_checkpoint, newlocation_dump, newlocation_journal, newlocation_work, newlocation_data - Now that all Database files are relocated, destroy the original MDB. For example: - destroydb -umdbadmin mdb - Finally, copy newmdb back to mdb, and then destroy it: - relocatedb newmdb -newdatabase=mdb - Destroydb -umdbadmin newmdb - Note: Checkpoint relocated MDB immediately as there are no checkpoints at this point! © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Troubleshooting Recommendations © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services

Troubleshooting - Problems encountered can be with “host” product (e. g. , Unicenter NSM) or Ingres as an installation or the MDB - Need to determine if a problem lies within the MDB or the product that installed the MDB © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

First Steps - Identify release and gen level information for MDB isql mdb – umdbadmin - Is Ingres up and running? - On UNIX check processes in Task Manager or use ps – fuingres - On Windows, verify Visual Manager icon is fully green. If not, double click on it to see which processes are red - If extra or missing processes, stop and restart Ingres - Is MDB accessible? - Execute sql mdb -umdbadmin © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Database Information - Use infodb command to gather high level information on the db, including: - Status of db - Location of files - History of checkpoints and journals - Must be DBA or system admin to run this command - Syntax: infodb mdb <#checkpoint> © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Check Ingres Error Log Files - ERRLOG. LOG – default log for most programs - IIACP. LOG – archive error log - IIRCP. LOG – recovery error log - ERRVDBA. LOG – VDBA error log - INGSTART. LOG – Ingres startup log © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

ERRLOG. LOG - ERRLOG. LOG is the first Ingres log you want to look into whenever you have Ingres issues. Example: - Typical error messages in ERRLOG. LOG when Virtual/Shared Memory is not enough mosal 01 h: : [II_JSP , 00 f 20100]: Tue Dec 20 16: 29: 00 2005 E_CL 1030_LK_EXPAND_LIST_FAILED LK failed while trying to allocate more locks lock lists. There are 0 locks lists, allocated at system startup time, of which 0 are used. mosal 01 h: : [II_JSP , 00 f 20100]: Tue Dec 20 16: 29: 00 2005 E_DMA 011_LK_NO_LLBS No more lock list blocks are available. It could be that locking system shared memory has been exhausted, or that the configured limit (ii. *. rcp. lock. list_limit) has been exceeded. mosal 01 h: : [II_JSP , 00 f 20100]: Tue Dec 20 16: 29: 00 2005 E_DM 901 A_BAD_LOCK_CREATEError creating a lock list. mosal 01 h: : [II_JSP , 00 f 20100]: Tue Dec 20 16: 29: 00 2005 E_DM 00 D 0_LOCK_MANAGER_ERROR Error creating a server lock list. mosal 01 h: : [II_JSP , 00 f 20100]: Tue Dec 20 16: 29: 00 2005 E_DM 9411_DMF_INIT Error initializing the DMF working environment. mosal 01 h: : [II_JSP , 00 f 20100]: Tue Dec 20 16: 29: 00 2005 E_DM 1051_JSP_NO_INSTALL The installation is not currently running. Run INGSTART then try again. © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Check MDB Error Log Files - INSTALL_MDB. LOG – information about MDB creation processing. This is the log file you want to look into whenever you have issues creating the MDB. Example: - Typical error messages when MDB creation fails due to low Virtual Memory: Tue 12/20/2005 16: 29: 00. 95 Creating the mdb users E_LC 0001 GCA protocol service (GCA_REQUEST) failure. Internal service status E_GC 0025 -- Unable to connect to Name Server: Blank or incorrect Name Server ID. The Name Server may not be running. Check the status of your installation. . E_LQ 0001 Failed to connect to DBMS session. Creation of database 'mdb' abnormally terminated. Tue 12/20/2005 16: 29: 05. 52 Failed to create database mdb (errorlevel=1) © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Backup/Recovery Recommendations © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services

When to backup - Good: Run at end of a housekeeping cycle - Better: Run prior to and after housekeeping (provides a recover point in the event housekeeping fails) - Best: Run before and after housekeeping and at suitable points in between. This allows minimum time for recovery based on size of checkpoint and journal files! - Caution – checkpoint requires space similar to the full MDB - Every checkpoint that is spinning needs the full space © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Backup and Recovery Options - Checkpointing and journaling to backup a DB or selected tables (recommended approach!) - Unloading DB - Copying DB to backup particular tables or all objects you own in DB - Operating system backups to replace current or destroyed tables in DB - Roll forward of DB to recover a DB or selected tables from checkpoints and journals © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Checkpoint - Takes place online (while DB is in use) and is transparent to users - ckpdb command creates new checkpoint, then copies log records of any DB changes that occurred during checkpoint procedure to dump file. - Permission required: System administrator, DBA, or an Ingres user with operator privilege. - Syntax: ckpdb -umdbadmin mdb - IMPORTANT: In order to prevent checkpoint and journal files from using too much disk space, we recommend that checkpoint, journal, and dump files from previous checkpoint sequences are moved to tape! © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Checkpoint - How do I find out what checkpoint, journal, and dump files need to remain on disk in order to recover from the current checkpoint sequence? - Run infodb mdb and search for the following data (bold red): ----Journal information----------------------------Checkpoint sequence : 8 Journal sequence : 1671 ----Dump information------------------------------Checkpoint sequence : 8 Dump sequence : 7 ----Checkpoint History for Journal-----------------------Date Ckp_sequence First_jnl Last_jnl valid mode --------------------------------------Wed Oct 19 14: 07: 15 2005 7 1342 1 ONLINE Wed Oct 19 14: 31: 28 2005 8 1343 1671 1 ONLINE ----Checkpoint History for Dump------------------------Date Ckp_sequence First_dmp Last_dmp valid mode --------------------------------------Wed Oct 19 14: 07: 15 2005 7 6 6 1 ONLINE Wed Oct 19 14: 31: 28 2005 8 7 7 1 ONLINE ----Extent directory------------------------------Location Flags Physical_path ---------------------------------ii_journal JOURNAL C: Program FilesCAIngres [EI]ingresjnldefaultmdb ii_checkpoint CHECKPOINT C: Program FilesCAIngres [EI]ingresckpdefaultmdb ii_dump DUMP C: Program FilesCAIngres [EI]ingresdmpdefaultmdb ======================================== © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Unloading a Database - unloaddb - Must be run offline only - Creates static backup copy - Creates scripts for unloading/reloading DB in binary mode (unloaddb DBName) or in ASCII mode (unloaddb –c DBName) - After, you must run one of the created scripts (unload. ing on UNIX or unload. bat on Windows) which visits every single table in DB and copies the content to a file – should run offline to ensure table enterires are consistent in terms of referencial integrities - Once done, use system tools to copy file to tape (or whatever) w/o need to stop Ingres installation. - In case of recovery – you would loose all transactions after the snapshot. - Checkpoint is preferred option - Syntax: unloaddb mdb © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Copying a Database - copydb mdb –c -umdbadmin © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Backup Recommendations - Keep last checkpoint and associated journals and dmp files on disk - Take copy of checkpoint files, dmp directory for DB and journal files to tape (or disk) - Allow # of checkpoints in the aaaa. cnf file to grow to the maximum number and then for DB to drop the oldest © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Recovery - rollforwarddb recovers DB from last checkpoint and current journal and dump files - If target checkpoint was performed online (while DB was in use), then rollforwarddb does the following: - 1. Restores DB from checkpoint location to DB location - 2. Applies log records in dump location to DB (which returns DB to its state when checkpoint began) - 3. Applies journal records to DB - If target checkpoint was executed offline, then step #2 is omitted. - Permission required: DBA or a system administrator. - Syntax: rollforwarddb –umdbadmin mdb © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

Reference List/Cheat Sheet © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names,

Command Cheat Sheet - usermod –online –umdbadmin mdb - ckpdb –umdbadmin mdb - optimizedb –zk –zw –umdbadmiin - help mdb - Isql mdb -umdbadmin - rollforwarddb –umdbadmin mdb - sysmod mdb - unloaddb - infodb - For more info, consult Command Reference Guide from MDB installation. © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.