The PLAN_TABLE in DB2 contains details of the access paths used by the optimizer from the EXPLAIN statement. This table gives much information about the PLAN having DB2 SQL statements.ACCESSTYPE is one of the columns of PLAN_TABLE which gives details regarding the access type. The values which can be present in this column and its interpretation is given below.Column valueInterpretationIIndexed accessRTablespace scanMMultiple index scanNIndex access present in predicateThe INDEXONLY column can take the value as ‘Y’ which indicates that the optimizer just needs to read indexspace in order to fetch the data for the query.

A sequence is used to generate the unique ID automatically. We can define a column in the DB2 table as type SEQUENCE. This will enable DB2 to automatically generate a unique ID for every new row inserted in the table. The auto ID generated by the DB2 remains unique throughout the database.For example, the practical implementation of SEQUENCE comes in case there are separate ORDERS tables assigned for each region North, East, West and South, then we can declare ORDER_ID as SEQUENCE. This will always generate a unique value for ORDER_ID irrespective of the ORDER table assigned for North, East, ... Read More

The subquery is a nested query. When this subquery is executed only once and the result of this subquery is used to extract the data in the main query, then this type of subquery is known as UNCORRELATED subquery. On the other hand, when a subquery refers to the main query for each execution, then the subquery is known as CORRELATED subquery.For example, if we want to extract all the orders from the ORDERS table which have ORDER_TOTAL more than overall average, then we can use the below UNCORRELATED subquery.ExampleSELECT ORDER_ID FROM ORDERS WHERE ORDER_TOTAL > (SELECT AVG(ORDER_TOTAL) FROM ORDERS)There ... Read More

Problem: How to explain INNER JOIN and OUTER JOIN with the help of an example on ORDERS and TRANSACTION DB2 table.SolutionThe JOIN is used to combine data from one or more tables in DB2. There are two main types of JOIN — INNER JOIN and OUTER JOIN. The basic difference between them is, INNER JOIN is an intersection of two or more tables while outer join is union of two or more tables. Basically, INNER JOIN is used to combine the data from multiple tables using equal column value and on the other hand, in case of OUTER JOIN, if ... Read More

We can revert all the changes done on a COBOL-DB2 program using the ROLLBACK statement. However, ROLLBACK is only applicable until COMMIT has not been issued. If we have used a COMMIT statement, then ROLLBACK will revert all the changes made in DB2 tables after the last COMMIT point.For example, after the commit statement, we execute an UPDATE statement to modify the ORDER_PAID column of ORDERS table. After that if we fire ROLLBACK then the UPDATE on the ORDERS table will be reverted.ExampleEXEC SQL COMMIT END-EXEC EXEC SQL UPDATE ORDERS    SET ORDERS_PAID = ‘YES’ WHERE ORDER_DATE = :WS-CURRENT-DATE ... Read More

Both JOIN and UNION are used to combine the data from one or more tables. In case of JOIN, the additional data appears in column while in case of UNION additional data appears in rows.For example, JOINSuppose we have two DB2 tables, ORDERS and TRANSACTIONS. We have to extract TRANSACTION_ID for each ORDER_ID, then we will use INNER JOIN as below:ExampleSELECT ORDER_ID, TRANSACTION_ID    FROM ORDERS INNER JOIN TRANSACTIONS ON    ORDERS.TRANSACTION_ID = TRANSACTIONS.TRANSACTION_IDThis query will result in 2 columns. One column will be from ORDERS table i.e., ORDER_ID and other column will be from TRANSACTIONS table i.e. TRANSACTION_ID.UNIONSWe have ... Read More

The purpose of VALUE function in DB2 is to check for NULL values and it can be used in place of NULL indicator or COALESCE function. The VALUE function replaces the column value with the given argument if it contains a NULL value.For example, if we have an ORDER table and we have to extract ORDER_ID and ORDER_DESCRIPTION from this table. The ORDER_DECRIPTION column can have NULL values.If this is the case, we have to replace ORDER_DESCRIPTION with SPACES, then we can use the below query:ExampleEXEC SQL    SELECT ORDER_ID, VALUE(ORDER_DESCRIPTION, ‘ ‘)    INTO :ORDER-ID, :ORDER-DESCRIPTION    FROM ORDERS ... Read More

For this, use application.properties −spring.datasource.username=yourMySQLUserName spring.datasource.password=yourMySQLPassword spring.datasource.url=jdbc:mysql://localhost:3306/yoruDatabaseName spring.datasource.driver-class-name=com.mysql.cj.jdbc.DriverTo understand the above syntax, let us create a table −mysql> create table demo71 −> ( −> id int, −> name varchar(20) −> ); Query OK, 0 rows affected (3.81 sec)Insert some records into the table with the help of insert command −mysql> insert into demo71 values(100, 'John'); Query OK, 1 row affected (0.13 sec) mysql> insert into demo71 values(101, 'David'); Query OK, 1 row affected (0.49 sec) mysql> insert into demo71 values(102, 'Bob'); Query OK, 1 row affected (0.15 sec)Display records from the table using select statement −mysql> select *from ... Read More

The difference between AND, OR is that AND evaluates both conditions must be true for the overall condition to be true. The OR evaluates one condition must be true for the overall condition to be true.Let us create a table −mysql> create table demo70 −> ( −> id int not null auto_increment primary key, −> name varchar(20), −> age int −> ); Query OK, 0 rows affected (0.67 sec)Insert some records into the table with the help of insert command −mysql> insert into demo70(name, age) values('John', 23); Query OK, 1 row affected (0.18 sec) mysql> insert into demo70(name, age) ... Read More

For this, use UPDATE command along with SUBSTRING_INDEX(). Let us first create a table −mysql> create table demo69 −> ( −> name varchar(40) −> ); Query OK, 0 rows affected (5.04 sec)Insert some records into the table with the help of insert command −mysql> insert into demo69 values('John/Smith'); Query OK, 1 row affected (0.83 sec) mysql> insert into demo69 values('David/Miller'); Query OK, 1 row affected (0.23 sec) mysql> insert into demo69 values('Chris/Brown'); Query OK, 1 row affected (0.40 sec) mysql> insert into demo69 values('Carol/Taylor'); Query OK, 1 row affected (0.36 sec)Display records from the table using select ... Read More