Database Tuning overview – Buffer Tuning

As part of the Performance Tuning series – Performance Stack , this section looks at Buffer Managment tuning. The purpose of the series is a general approach to Performance Tuning – independent of database server platform. A large proportion of DBAs support more than one type of database server and a methodology allows them to apply to all platforms. Memory usage Measure and assign memory space for IO and memory data structures e.g procedure cache Ensure the buffer space is sufficient. To low buffer space for DBMS memory requirements causes a low hit rate. A low hit rate is where... Read more →

Catalog the database and rename database

Use the uncatalog \ catalog of a database to rename a database. An alternative to the db2relocatedb method. If this is an option,uncatalog the db and then catalog it with a new alias: $ db2 create db mydb $ db2 uncatalog db mydb $ db2 catalog db mydb as a_new_name The database name name remains the same . The name used with the CONNECT statement changes. The application can use the new alias to connect. Related Posts Rename a DB2 database Author: Jack Vamvas(http://www.dba-db2.com) Read more →

Yum clean all

The /var folder on a Red Hat Linux server was reported as only 1% space left. The DB2 binaries and data\ log files were on their own drive space. Normally , I search for the Linux largest files and assess whether they require pruning. Occasionally I use the command : yum clean all Firstly, what is yum ? From the yum wiki . “Yum is an automatic updater and package installer/remover for rpm systems. It automatically computes dependencies and figures out what things should occur to install packages. It makes it easier to maintain groups of machines without having to... Read more →

DB2 - ADMIN_DROP_SCHEMA to drop a schema

I use ADMIN_DROP_SCHEMA to drop a schema and its objects. The main advantage of using the procedure , instead of writing a customised script is there could be routines such as stored procedures or UDFs with cross dependencies and require to be dropped in a certain order. An example is CALL SYSPROC.ADMIN_DROP_SCHEMA ( schema_name, NULL, 'error_table_schema','error_table_name') ---Use this sql to view the errors: SELECT * FROM error_table_schema.error_table_name Certain objects are not dropped using the ADMIN_DROP_SCHEMA procedure. Check the DB2 documentation for details Read more →

Database tuning and Operating System Tuning

As part of the Performance Tuning series – Performance Stack , this section looks at Operating System tuning. The purpose of the series is a general approach to Performance Tuning – independent of database server platform. A large proportion of DBAs support more than one type of database server and a methodology allows them to apply to all platforms. There are various configurations per Operating System. For example , on Windows setting the start up service account to Lock Pages in Memory , assists in alleviating paging for SQL Server. Or check OLTP checklist for DB2 configurations. DB2 and Threads... Read more →