Found 150 Articles for DB2

Problem: How will the COBOL-DB2 program behave when there is a mismatch between the host variable and number of columns in the SELECT statement?SolutionIn case there is a mismatch in the number of columns and number of host variables, the query will fail. For example, if we have used the below query in a COBOL-DB2 program which processes the ORDERS DB2 table.ExampleEXEC SQL    SELECT ORDER_ID,       ORDER_AMOUNT,       ORDER_DATE,       ORDER_STATUS    INTO :WS-ORDER-ID,       :WS-ORDER-AMOUNT,       :WS-ORDER-DATE,    FROM ORDERS WHERE ORDER_DATE = ‘2020-09-15’ END-EXECThere is a mismatch ... Read More

Problem: What is the purpose of the "FOR UPDATE OF" clause in a cursor? What will happen if we fire an UPDATE statement without using this clause in a COBOL-DB2 program?SolutionThe “FOR UPDATE OF” clause is given in the cursor declaration and it is used when we want to update the table. All the columns which need to be updated should be given in the cursor declaration.The “FOR UPDATE OF” clause will place the exclusive lock on all the qualifying rows once the cursor is open. We can also update the table without using “FOR UPDATE CLAUSE” but in that ... Read More

Problem: Is it possible to update a CURSOR in which we have used JOIN on 2 tables ORDERS and TRANSACTIONS? Why or why not? How can we proceed to UPDATE any of these tables?SolutionWhenever we use JOIN in a cursor on two or more tables (ORDERS and TRANSACTIONS in this case) a temporary table is generated in the virtual memory. However, since this is a temporary table we can fetch data from this table but it is not possible to update this table.If we want to update any of the tables used in the JOIN then we have to declare ... Read More

Problem: The DB2 program PROGA was changed and compiled, but it was binded after 3 days. What will happen if we execute this program on the 4th day?SolutionThe pre-compiler inserts the current timestamp in the modified source code and in DBRM. In case of modified source code, this timestamp is passed on to the load module and in case of DBRM the timestamp is passed on to the package. At the time of program execution, the timestamp of load module and package is compared. This comparison takes place to ensure that the correct version of package and load module is ... Read More

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. On the other logical structure contains components like tablespace, indexspace, tables, views, packages, plans, etc.The components of physical structure have restricted access and only DBAs can access those files. However, it is possible for us to view components of logical structure using any of the below 2 options.Using DB2 administration ... Read More

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 residing in the database remains complete, accurate and reliable.There are three types of integrity described in below figure.Domain integrityIt ensures that column data in the DB2 table adhere to the permissible set of values. For example, in the ORDERS table, the domain integrity on the ORDER_DATE column ensures that data ... Read More

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. We can use predicate in HAVING clause to filter the results for groups having count greater than one.Below is the SQL query which we can use to find duplicate invoices in ORDERS DB2 table.ExampleSELECT INVOICE_ID FROM ORDERS    GROUP BY INVOICE_ID    HAVING COUNT(INVOICE_ID) > 1For example, if we have ... Read More

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 operation ON OBJECT object-nameThere are DCL (Data control language) statements which are used by DBAs in order to control the access on DB2 objects. We can raise a request with DBA to provide access to the particular object for which user is getting -551 SQLCODE.Following DCL statement will give access ... Read More

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 and SPUFI.SPUFI is a free tool which comes with mainframe DB2 installation, while QMF is a separate licensed toolQMF is an interactive tool which can be used for reporting and formatting purposes, but in case of SPUFI, we need to write the SQL queries in the mainframe dataset and the ... Read More

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 FOR N ROWS clause is not effective on SELECT DISTINCT and COUNT function because DB2 will need the entire qualifying rows in order to fetch the DISTINCT rows or COUNT the number of rows. The OPTIMIZE FOR N ROWS clause gives DB2 a better opportunity to establish the access path.The ... Read More