What is the panel command to display all DB2 database components?

Published on 01-Dec-2020 09:31:04
Problem: Give and explain the panel command to display all the components of DB2 database DSNDB01 along with their status.SolutionA DB2 is a relational database which can be visualized to have both physical as well as logical structures. The physical structure contains components like log files, bufferpools, control files etc. ... Read More

Explain the concept of integrity in DB2 along with types of integrity

Published on 01-Dec-2020 09:23:06
Problem: What is INTEGRITY in a DB2? Explain DOMAIN, ENTITY and REFERENTIAL integrity with the help of an example in ORDERS table.SolutionThe integrity refers to the accuracy, consistency and correctness of data present in the DB2 database. Data integrity is imposed during the database design to make sure that data ... Read More

Write a DB2 query to find out all the duplicate INVOICE_ID in ORDERS DB2 table?

Published on 01-Dec-2020 09:20:46
The duplicate INVOICE_ID in ORDERS DB2 table can be found by using a combination of GROUP BY with HAVING clause and COUNT function. The GROUP BY will enable us to group the result invoice wise and COUNT function will enable us to count the number of occurrences in each group. ... Read More

Example of SQL query describing COUNT function and GROUP BY

Published on 01-Dec-2020 09:19:33
Problem: Write a query on TRANSACTIONS DB2 table to list down the number of orders (ORDER_ID) assigned to a particular transaction (TRANSACTION_ID).SolutionWe can use the below query to find out the number of orders assigned to a particular transaction id on TRANSACTIONS DB2 table.ExampleSELECT TRANSACTION_ID, COUNT(ORDER_ID) FROM TRANSACTIONS    GROUP ... Read More

What is -551 error code in DB2? How will you resolve it?

Published on 01-Dec-2020 09:18:22
When we get -551 in the SQLCODE then there is some privilege level issue. It signifies that the user does not have access to the database/tablespace/view/table that he is trying to access. As per the IBM documentation -551 SQLCODE states that.Example-551 auth-id DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION ... Read More

What are QMF and SPUFI? What is the difference between them for accessing DB2 tables?

Published on 01-Dec-2020 09:17:07
Both QMF and SPUFI are tools built to access DB2 databases in a mainframe environment. Using these tools, we can SELECT, UPDATE and DELETE the data from the DB2 database. QMF stands for Query Management Facility and SPUFI stands for SQL Processor Using File Input.Below are the differences between QMF ... Read More

What is the purpose of OPTIMIZE FOR ROWS in DB2 SQLs? How is it useful?

Published on 01-Dec-2020 09:15:48
The OPTIMIZE FOR N ROWS is a DB2 clause which we can add in the query to give priority for the retrieval of the first few rows only. This clause will enable the optimizer to choose the access path that minimizes the response time for fetching first few rows.The OPTIMIZE ... Read More

How will you create a new TRIGGER on the ORDERS DB2 table? Give the syntax of TRIGGER

Published on 01-Dec-2020 09:14:29
The TRIGGERS are the event driven database programs which are triggered automatically by the database. The TRIGGERS are created using the CREATE TRIGGER statement.For example, we want to create a TRIGGER which will update ORDER_COMMISION column of the ORDERS table to 5% of the ORDER_TOTAL value after every new record ... Read More

What are TRIGGERS in DB2? What is the difference between TRIGGERS and STORED PROCEDURES?

Published on 01-Dec-2020 09:13:15
The TRIGGERS are database programs which are triggered automatically by DBMS in response to any modification done on the specified table. A TRIGGER can be associated with only a single table and they cannot be skipped if the desired event occurs.The TRIGGERS are like STORED PROCEDURES in the sense, both ... Read More

How can a COBOL-DB2 program call a STORED PROCEDURE?

Published on 01-Dec-2020 09:11:26
A STORED PROCEDURE generally contains the SQLs which are often used in one or more programs. The main advantage of STORED PROCEDURE is that it reduces the data traffic between the COBOL and DB2 as the STORED PROCEDURES resides in DB2.A COBOL-DB2 program can call a STORED PROCEDURE using a ... Read More