Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
A static SQL is hardcoded in COBOL-DB2 program and the SQL query cannot change during the program execution. We can only change the value of the host variables. In the case of DYNAMIC SQL, we can change the columns, tables and predicates in the COBOL-DB2 program in run time.For example, based on the current date, we can update ORDERS or ORDERS_HIST table. This query can be built using DYNAMIC SQL which includes ORDERS table or ORDER_HIST table.The main advantage of DYNAMIC SQL is its flexibility. We can add columns or change tables/predicates as per our business logic. On the other ... Read More
The QBLOCK_TYPE column of the PLAN_TABLE mainly gives the information about the type of query. It can have the value as CORSUB which indicates that the query is Correlated subquery and NCOSUB indicates that the query is Non-correlated subquery.The JOIN_TYPE column indicates the type of join used in the query. This column can take the value as per the below table.Column valueInterpretationBLANKINNER JOIN/NO JOINFFULL OUTER JOINLLEFT OUTER JOINRRIGHT OUTER JOIN
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
This example demonstrates how to define a MIN and MAX value for EditText in Android using KotlinStep 1 − Create a new project in Android Studio, go to File ⇒ New Project and fill all required details to create a new project.Step 2 − Add the following code to res/layout/activity_main.xml. Step 3 − Add the following code to src/MainActivity.ktimport android.os.Bundle import android.text.InputFilter import android.text.Spanned import android.widget.EditText import android.widget.TextView import androidx.appcompat.app.AppCompatActivity import kotlinx.android.synthetic.main.activity_main.* class MainActivity : AppCompatActivity() { lateinit var textView: TextView override fun onCreate(savedInstanceState: Bundle?) { super.onCreate(savedInstanceState) ... Read More