Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
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
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
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
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
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
We can find the duplicate TRANSACTION_ID in the ORDERS DB2 table using the below query:ExampleSELECT TRANSACTION_ID, COUNT(*) AS TRANSACTION_COUNT FROM ORDER GROUP BY TRANSACTION_ID HAVING COUNT(*) > 1The purpose of COUNT(*) is to count the number of rows. We will group the result based on the TRANSACTION_ID using GROUP BY function and to display the duplicate transaction ids, we will place a predicate using HAVING statement for COUNT(*) greater than one.For example, consider the below TRANSACTIONS DB2 table:TRANSACTION_IDTRANSACTION_STATUSIRN22345PAIDIRN22345PAIDIRN22345PAIDIRN56902PAIDIRN99781UNPAIDIRN56902PAIDThe query will give the below result:TRANSACTION_IDTRANSACTION_COUNTIRN223453IRN569022IRN997811Read More
Problem: Write a SQL query to count the number of orders which were placed today from the ORDERS DB2 table. (The date should not be hardcoded)SolutionWe can find the count of orders which are placed today using the below DB2 query:ExampleSELECT COUNT(ORDER_ID) AS ORDER_COUNT FROM ORDERS WHERE ORDER_DATE = CURRENT DATEIn this query, we have used the COUNT COLUMN function which will count the total number of ORDER_ID (primary key). In the WHERE clause, we will use the predicate for the ORDER_DATE column. The CURRENT DATE is a DB2 inbuilt function which will return the current system date.For example, if ... Read More
This example demonstrates how to use static variables in Android using Kotlin.Step 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.util.Log import android.widget.Toast import androidx.appcompat.app.AppCompatActivity class MainActivity : AppCompatActivity() { private val tag = "I'm a Static Variable" override fun onCreate(savedInstanceState: Bundle?) { super.onCreate(savedInstanceState) setContentView(R.layout.activity_main) title = "KotlinApp" ... Read More
This example demonstrates how to send a variable from Activity to Fragment in Android using Kotlin.Step 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.widget.Button import android.widget.EditText import androidx.appcompat.app.AppCompatActivity import androidx.fragment.app.FragmentManager import androidx.fragment.app.FragmentTransaction class MainActivity : AppCompatActivity() { lateinit var editText: EditText lateinit var button: Button override fun onCreate(savedInstanceState: Bundle?) { ... Read More
This example demonstrates how to Change color of Button in Android when Clicked using Kotlin.Step 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.graphics.Color import android.os.Bundle import android.widget.Button import androidx.appcompat.app.AppCompatActivity class MainActivity : AppCompatActivity() { override fun onCreate(savedInstanceState: Bundle?) { super.onCreate(savedInstanceState) setContentView(R.layout.activity_main) title = "KotlinApp" val button: Button ... Read More