Found 151 Articles for DB2

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

Updated on 01-Dec-2020 04:44:50
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 insertion in ORDERS table.ExampleCREATE TRIGGER ORDERCOMMUPD AFTER INSERT ON ORDERS FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE ORDERS SET ORDER_COMMISION=(5*ORDER_TOTAL)/100;Using the above statement, we have created an AFTER trigger which will be triggered automatically after any new row is inserted in the ORDERS table. Similarly, we can have BEFORE ... Read More

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

Updated on 01-Dec-2020 04:43:41
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 are pieces of code which are directly managed by DB2. However, TRIGGERS are event driven and executed automatically once the desired event occurs (INSERT/UPDATE/DELETE) on the specified table.The STORED PROCEDURES on the other hand needs to be called explicitly in the application program. Another difference between them is, TRIGGERS are ... Read More

How can a COBOL-DB2 program call a STORED PROCEDURE? Give an example.

Updated on 01-Dec-2020 04:41:51
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 CALL statement and we can have nested STORED PROCEDURE upto 16 levels. For example, if we have STORED PROCEDURE with a name ORDERSTAT, then we can call it in our COBOL-DB2 program using the below command:ExampleEXEC SQL    CALL ORDERSTAT (:WS-ORDER-ID, :WS-ORDER-STATUS) END-EXECIn order to create a DB2 procedure, we ... Read More

Impact of database downtime on the COBOL-DB2 program

Updated on 01-Dec-2020 04:40:37
Problem: What will be the result if a COBOL-DB2 program tries to query a DB2 table, but the database in which table is residing is down?SolutionWhen we try to access any table using a COBOL-DB2 program and the DB2 database in which that table is residing is down then the COBOL-DB2 program will abend. In this case, the SQLCODE of SQLCA field will have the value as -904.As per the IBM documentation SQLCODE -904 states.“Unavailable resource. The database or tablespace is not available for use”There are several methods which we can use in order to find the current status of ... Read More

Investigation of root cause and resource responsible for the deadlock in DB2

Updated on 30-Nov-2020 09:41:18
Problem: A COBOL-DB2 program failed due to deadlock. How will you find the resource due to which the program failed?SolutionA DEADLOCK condition occurs when two or more applications are stuck, waiting for each other to release the locks on the resources needed by them. A detailed information and logs can be found in the DB2 system job DSNZMSTR job. The DSNZ is the name of the installed DB2 sub-system and it can vary from installation to installation. The SYSOUT of this job continues to display the DB2 level system logs. The logs related to the deadlock are also available in ... Read More

Explain the concept of LOCK PROMOTION with the help of an example

Updated on 30-Nov-2020 09:40:03
A DB2 LOCK PROMOTION is defined as the process of acquiring more restrictive locks on a particular resource. DB2 uses LOCK PROMOTION for the concurrent processes which are trying to access the same DB2 resource. Basically, there are three types of locks.Shared lock(S)The concurrent processes can place a shared lock on a resource (DB2 table, row, page, etc) but cannot update the data. In order to update the data, concurrent processes have to promote their lock to UPDATE.Update lock(U)The concurrent process can read the data but cannot update it. Update lock indicates that the process is ready to update the ... Read More

Implementation and purpose of direct index look-up

Updated on 30-Nov-2020 09:38:27
The direct index look-up is chosen by the DB2 optimizer when all the columns used in the predicate of the WHERE clause is part of the index.For example, if we have ORDERS DB2 table as below.ORDER_IDORDER_DATEORDER_TOTALZ2234530-10-2020342Z3341214-08-2020543Z5699019-10-2020431Z5690221-09-20206743Z9978104-11-2020443Z5611229-08-2020889In this table, there is one index which is built having columns named ORDER_ID and ORDER_DATE. For the below query, DB2 optimizer will choose direct index look-up because the columns used in the SELECT statement are also part of the index.ExampleSELECT ORDER_ID, ORDER_DATE, INVOICE_ID FROM ORDERS    WHERE ORDER_ID = ‘Z33412’ AND ORDER_DATE = ‘14-08-2020’The result of the above query will be as follows.ORDER_IDORDER_DATEZ3341214-08-2020In the ... Read More

What is the SQL query describing usage of MAX aggregate function and GROUP-BY with HAVING?

Updated on 30-Nov-2020 09:37:27
We can find the highest ORDER_TOTAL datewise from the ORDERS DB2 table using below query.ExampleSELECT ORDER_DATE, MAX(ORDER_TOTAL) FROM ORDERS GROUP BY ORDER_DATEWe will use ‘GROUP BY’ on ORDER_DATE to group the result date wise and MAX aggregate function will help us to get the maximum ORDER_TOTAL placed at that particular date.For example, if we have below ORDERS DB2 table.ORDER_IDORDER_TOTALORDER_DATEZ2234534229-07-2020Z6299854330-07-2020Z5699043128-07-2020Z56902674329-07-2020Z9978144328-07-2020Z5611288930-07-2020 Then the SQL query - SELECT ORDER_DATE, MAX(ORDER_TOTAL) FROM ORDERS GROUP BY ORDER_DATE will return the result below.ORDER_DATEORDER_TOTAL28-07-202044329-07-2020674330-07-2020889Read More

Write the DB2 SQL query to find the third highest ORDER_TOTAL in a ORDERS DB2 table

Updated on 30-Nov-2020 09:36:25
We can find the third highest ORDER_TOTAL in the ORDERS DB2 table using the below query.ExampleSELECT ORDER_ID, MIN(ORDER_TOTAL) FROM ORDERS    ORDER BY ORDER_TOTAL DESC    FETCH FIRST 3 ROWS ONLYThe ‘FETCH FIRST 3 ROWS ONLY’ clause will give only 3 rows in the output and these 3 rows will be in descending order. The first row will have the highest ORDER_TOTAL in the entire ORDERS table, second row will have the second highest ORDER_TOTAL in the entire ORDERS table and so on.The MIN aggregate function will give the least value of the ORDER_TOTAL among those 3 rows and this ... Read More

SQL query describing usage of SUM aggregate function and GROUP-BY with HAVING

Updated on 30-Nov-2020 09:35:05
Problem: Write the DB2 SQL query to give the sum of ORDER_TOTAL for the orders placed on 29th July and 30th July individually. The result should come in a single table.SolutionWe can find the sum of ORDER_TOTAL for the orders placed on 29th and 30th July individually using aggregate function SUM, GROUP BY and HAVING.For example, if we have an ORDER table as below.ORDER_IDORDER_TOTALORDER_DATEZ2234534229-07-2020Z6299854330-07-2020Z5699043112-07-2020Z56902674329-07-2020Z9978144310-07-2020Z5611288930-07-2020 Below is the query which will give the desired result.ExampleSELECT ORDER_DATE, SUM(ORDER_TOTAL) FROM ORDERS GROUP BY ORDER_DATE HAVING ORDER_DATE IN (‘29-07-2020’, ‘30-07-2020’)In this query, we have selected ORDER_DATE and ORDER_TOTAL with aggregate function SUM.The GROUP BY will ... Read More