Query Optimization Issue:
Assume that a query is executing by a application developer or a client. The query has all the indexes on the columns which are required and performed well upto certain time, suddenly got complaints about the same query that it is not performing well taking too much time to retrieve. Please leave your valuable comments how you people can resolve this issue.
...
9/27/2016
9/25/2016
Share views to resolve the below reorg issue
Loading....
Difference between Online Reorg and Offline Reorg in DB2
Online reorg vs Offline reorg:
Offline or Classical Reorg:
So many people think that Offline reorg quiesces the database, so no applications are able to connect or access the tables. But while performing offline reorg applications can access tables, but they have limited access. Read access only at the time of SORT and BUILD phase...
9/22/2016
What are the different phases of Online & Offline Reorg in DB2 ?
Different Phases of Reorg:
Basically Reorg sorts the data using index scan or table scan and moves the records from one page to a previous page which has some free space due to deletion of rows from a table to reclaim the space.
There are "TWO" types of reorgs:
==> Offline reorg or Classic reorg
==> Online reorg or Inplace reorg
Each reorg type consists of different phases of executi...
9/08/2016
What are the DB2 LUW DBA daily activities?
DB2 DBA Daily Activities:
Daily Activities:
==> Verify all the instances and databases are running.
==> Check the backup status of the databases.
==> Check the archiving of database logs.
==> Verify the pruning of database backups and archival logs.
==> Check the status of Reorg/Runstats jobs if you have enabled.
==> Check the db2 diagnostic logs and notification logs.
==> Check the size/status of each mount database server is usi...
9/06/2016
How to take data Online Backup in DB2?
Data Backups in DB2
Offline Backup:
Offline backup means we can take the data when there are no connections from the applications to the database. No need to configure any database configuration parameter such as LOGARCHMETH1, LOGARCHMETH2 etc....
9/02/2016
How to replicate instance configuration parameters in DB2?
db2cfexp – Connectivity Confg Export tool
command
It'll
export the database manager and all the instance level configuration, registry
variables, node details, database details including remote databases and
protocol information details into an external file....
8/29/2016
DB2 Important Commands
Commands for DB2 DBA
...
8/25/2016
How to setup logical data partitioning in DB2?
How to setup Logical Data Partition Step by Step:
What is Data Partitioning:
Present days databases and data warehouses become larger, this is somewhat complicated to maintain growth of the database. To avoid this and to manage the growth of the data IBM introduce the concept called "Database Partitioning Feature", "Table Partitioning" and Multi Dimensional Clustering".
In this article we are going look at "Database Partitioning Feature...
8/13/2016
Data concurrency in DB2 deep dive
DB2 concurrency basics
In a
multi-user database environment, transactions are usually executed
simultaneously. Each transaction has the potential to interfere with any other
transaction that is running. Without any appropriate locking mechanism, the
following undesirable effects may occur:
Lost
updates: Two
applications, A and B, might both read the same row from the database, and both
calculate new values for one of its columns based on the data these
applications read. If A updates the row with its new value and B then also
updates the...
7/04/2016
Overview of LOCK ISSUES in DB2
In this post we are going to discuss about three main lock issues:
> Lock Escalations
> Lock Waits
> DeadLocks
Lock Escalations:
How many of you know that locks will also use memory? Yes they uses a a certain amount of memory and CPU. Sometimes there may be many row level locks present on a single table. So it may lead to more usage of memory and CPU. This this kind of scenarios DB2 automatically acquire a table level lock by replacing all the row level...
5/18/2016
How to Identify the version and service level of DB2 product?
To know the DB2 product version and service level we can use "db2level".
The output of the "db2level" command will be as below.
The combination of the four informational tokens uniquely identify the precise service level of your DB2 instance. This information is essential when contacting IBM Software Support for assistance.
For JDBC or SQLJ applications, if you are using the IBM DB2 Driver for SQLJ and JDBC, you can determine the level of the...
5/01/2016
Troubleshooting tools for Performance Tuning: Analyzing db2diag log files using db2diag tool
Primary log files are for the use of database and administration notification log are for system administrators. The db2diag log files are for the use of IBM Software Support for troubleshooting.
Even administration notification log messages are also logged to the db2diag log files.
The db2diag tool serves to filter and format the volume of information available in the db2diag log files. Filtering db2diag log file records can reduce the time required to locate the records needed when troubleshooting problem...
3/13/2016
Troubleshooting tools for Performance Tuning: DB2DART
Overview of the db2dart tool
The main tool that is used to verify the database architecture and the objects that are belonging to a database. The contents of database control files will be displayed to extract data from tables.
db2dart will create a report file with a default name <databaseName>. RPT. The report file will be created in the current directory for a single-partition database. If it is a multiple-partition database file will be created under the diagnostic directory. The subdirectory is called DART<dbpartition...
3/06/2016
Checking archive log files with the db2cklog tool
Checking archive log files with the db2cklog tool
Checking archive log files with the db2cklog toolChecking your archive log files ensures that known good log files are available in case a rollforward recovery becomes necessary and that the recovery operation does not fail because of a problem with a log file. The db2cklog tool reads either single log files or a range of numbered log files and performs checks on the internal validity of the files. Log files that pass validation without any error messages or warnings are known good files and you...
2/29/2016
Establishing a performance tuning strategy
The Design Advisor
The DB2 Design Advisor is a tool that can help you significantly improve your workload performance. The task of selecting which indexes, materialized query tables (MQTs), clustering dimensions, or database partitions to create for a complex workload can be daunting. The Design Advisor identifies all of the objects that are needed to improve the performance of your workload.
Given a set of SQL statements in a workload, the Design Advisor generates recommendations fo...
2/17/2016
Self-tuning memory in partitioned database environments
There are few factors to determine the self tuning feature will tune the system appropriately in partitioned database environment.
When self-tuning memory is enabled for partitioned databases, a single database partition is designated as the tuning partition, and all memory tuning decisions are based on the memory and workload characteristics of that database partition.
After tuning decisions on that partition are made, the memory adjustments are distributed to the other database partitions to ensure that all database partitions maintain similar...
2/12/2016
How Resource utilization will be done in DB2
Memory allocation
Memory allocation and deallocation occurs at various times. Memory might be allocated to a particular memory area when a specific event occurs (for example, when an application connects), or it might be reallocated in response to a configuration change.
Below figure shows the different memory areas that the database manager allocates for various uses and the configuration parameters that enable you to control the size of these memory areas. Note that in a partitioned database environment, each database partition has its...
2/07/2016
Performance monitoring tools and methodology
Operational monitoring of system performance
Operational monitoring refers to collecting key system performance metrics at periodic intervals over time. This information gives you critical data to refine that initial configuration to be more tailored to your requirements, and also prepares you to address new problems that might appear on their own or following software upgrades, increases in data or user volumes, or new application deployments.
Operational monitoring considerations
A DB2 database (“DB2”) system provides some excellent sources...
1/31/2016
Performance tuning tools and methodology
Benchmark testing:
Benchmark testing
is a normal part of the application development life cycle. It is a team effort
that involves both application developers and database administrators(DBAs).
Benchmark testing
is performed against a system to determine current performance and can be used
to improve application performance. If the application code has been written as
efficiently as possible, additional performance gains might be realized by tuning
database and database manager configuration parameters.
Different types of
benchmark tests...
1/11/2016
File Permimission Requirements for the Instance and Database Directories
The DB2 database system requires that your instance and database directories have a minimum level of permissions.
Note: When the instance and database directories are created by the DB2 database manager, the permissions are accurate and should not be changed.
The minimum permissions of the instance directory and the NODE000x/sqldbdir directory on UNIX and Linux machines must be: u=rwx and go=rx. The meaning of the letters is explained in the following table:
Character Represents:
u User (owner)
g Group
o Other users
r Read
w ...
1/04/2016
Security considerations when installing and using the DB2 database manager
Important to the DB2 administrator from the moment the product was installed are Security Considerations.
A user ID, a group name, and a password are required to complete the installation of DB2 database manager. If you install a GUI-based DB2 database manager that only creates default values for different user IDs and the group. Depending on the OS platform different defaults will be created.
On UNIX and Linux operating systems, if you choose to create a DB2 instance in the instance setup window, the DB2 database install program creates,...
1/02/2016
Authentication & Authorization process in DB2
Authentication:
The process that verifies user identity at OS level is known as "authentication". The security product can be part of the OS or a separate product.
To authenticate a user security facility requires two items:
1. User ID
2. Password
The user ID identifies the user to the security facility. By supplying the correct password, information known only to the user and the security facility, the user's identity (corresponding to the user ID) is verified.
What will happen after being authenticated:
1. The user must be identified to...
1/01/2016
DB2 Database Security Model
The DB2 database system data and functions have two modes of security controls
1. Authentication (Outside the DB2 database system)
2. Authorization (Managed by database manager)
Authentication:
1. The process that verifies user identity at OS level is known as "authentication".
2. In this the authentication of user completed by a secure facility outside the DB2 database system using an authentication...