Q. What is DB2?
DB2 is a relational database management system (RDBMS) developed by IBM. It is designed to store, manage, and retrieve large amounts of structured data efficiently. DB2 is widely used in enterprise
Environments for various applications, including transaction processing, data warehousing, and business intelligence.
DB2 provides a robust set of features and capabilities, including support for SQL (Structured Query Language), ACID (Atomicity, Consistency, Isolation, Durability) properties for data integrity, multi-platform compatibility, scalability, high availability, and security features. It offers a range of deployment options, including on-premises, cloud, and hybrid environments.
DB2 supports various operating systems, including Linux, Unix, and Windows, and offers different editions to cater to the needs of different organizations, from small businesses to large enterprises. It provides tools and utilities for database administration, performance monitoring, and development, making it easier for database professionals to manage and optimize their DB2 databases.
Q. What are the Diffrent data types available in DB2?
DB2 supports a wide range of data types to accommodate different types of data. Here are some commonly used data types in DB2:
Numeric Types:
INTEGER: Represents whole numbers.
SMALLINT: Represents small whole numbers.
DECIMAL/NUMERIC: Represents decimal numbers with specified precision and scale.
FLOAT/REAL/DOUBLE: Represents floating-point numbers.
Character Types:
CHAR: Represents fixed-length character strings.
VARCHAR: Represents variable-length character strings.
CLOB: Represents large character objects.
Binary Types:
BINARY: Represents fixed-length binary data.
VARBINARY: Represents variable-length binary data.
BLOB: Represents large binary objects.
Date and Time Types:
DATE: Represents a date.
TIME: Represents a time.
TIMESTAMP: Represents a combination of date and time.
Boolean Type:
BOOLEAN: Represents true or false values.
XML Type:
XML: Represents XML data.
LOB Types:
CLOB: Represents large character objects.
BLOB: Represents large binary objects.
Row Types:
ROW: Represents a row of data.
Q. Write the Query to find the number of rows in a DB2 table
To find the number of rows in a DB2 table, you can use the following SQL query
SELECT COUNT(*) FROM your_table_name;
Q. Write the Query to check number of users in db2 database with permission
To check the number of users in a DB2 database with permissions, you can use the following query
SELECT COUNT(*) FROM SYSIBMADM.AUTHORIZATIONIDS;
TO check specific permissions
SELECT COUNT(*) FROM SYSIBMADM.AUTHORIZATIONIDS A
JOIN SYSCAT.TABAUTH T ON A.AUTHID = T.GRANTEE
WHERE T.TABNAME = ‘your_table_name’ AND T.TABSCHEMA = ‘your_table_schema’ AND T.AUTHORITY = ‘SELECT’;
Q How to delete database in DB2 in HA DR
Connect to the primary database server: Use the appropriate command or utility to connect to the primary database server where the database resides.
Disable the database: Before deleting the database, you need to disable it. Run the following command to disable the database:
db2 => DIS DATABASE dbname REDIRECT
Replace dbname with the name of the database you want to delete.
Verify the database status: Check the status of the database to ensure that it is redirected and no longer active. Use the following command
db2 => LIST DATABASE DIRECTORY SHOW DETAIL
Look for the database you want to delete and ensure that the “State” column shows “Disabled” and the “Redirected” column shows “Yes”.
Once the database is disabled and redirected, you can proceed with deleting it. Run the following command
db2 => DROP DATABASE dbname
DB2 will prompt you to confirm the deletion. Type yes to confirm and proceed with the deletion
Are you sure you want to drop the database (y/n)?
After the deletion is complete, you can verify that the database is no longer listed
db2 => LIST DATABASE DIRECTORY