DECLARE GLOBAL TEMPORARY TABLE gives you a handy option to create a table in QTEMP lib with some handy options.

  1. As DECLARE GLOBAL TEMPORARY TABLE creates a table in QTEMP lib so
  • Other sessions cannot share it.
  • It does not appear in the system catalog.
  • When you end your session, the table rows are deleted, and the table is dropped.

2. Basic syntax of DECLARE GLOBAL TEMPORARY TABLE is almost the same as CREATE TABLE statement.

  • For example, same as CREATE TABLE statement, You LIKE or AS clause can be used.

3. They can store data with minimal infrastructure and…


SELECT Statement

SQL SELECT Statement is a form of SQL QUERY which gives access to a result set(Temporary table). We can use SQL SELECT to look inside the table or create a new table based on the result set of queries.

There are 3 components of SQL SELECT:

  1. SubSelect
  2. FullSelect
  3. Select-Statement

SubSelect

SubSelect is most basic form of SQL SELECT. Just simple select statement with minimum options.Here is how IBM describes SubSelect:>>-select-clause--from-clause--+--------------+----------------->
'-where-clause-'

>--+-----------------+--+---------------+----------------------->
'-group-by-clause-' '-having-clause-'

This is a how a SubSelect looks like:

select [all|distinct] expression {, expression}
from tablename [corr_name] {, tablename [corr_name]}
[where search_condition]
[group by column {, column}]
[having search_condition]

Structured Query Language aka SQL is the industry standard to talk to Relation database management systems (RDBMS). RDBMS and SQL are like an alias to each other. To perform any action on RDBMS you need SQL.

All the basic concepts of SQL are the same for every RDBMS like SQLServer, DB2, MYSQL, Oracle, etc. But every database provides some special SQL, specifically for that database. Here will talk only for DB2/400.

Let’s Get to Point.

SQL statements are categorized as follows:

  1. Data Control Language (DCL) statements: To handle data security. For example, GRANT and REVOKE statements.
  2. Data Definition Language (DDL)…

When processing a CURSOR, DB2 SQL gives the option to update or delete data in the base table based on CURSOR's current position in the result table. This is called POSITIONED UPDATE or POSITIONED DELETE.

Let say there is a table EXAMPLETABLE like

CREATE TABLE EXAMPLETABLE (id numeric, name char(10), processedFlag char(1));

You need to write some processing logic for each record of this table that is not processed yet(means processedFlag <> ‘Y’), and when processing for a record is completed, processedFlag should be updated to ‘Y’.

Solution 1: Without using POSITIONED UPDATE

DECLARE CURSOR

DECLARE @C1 CURSOR for 
(Select id,name from EXAMPLETABLE where processedFlag <>'Y')

The result of a SELECT Statement which returns more than one row, is easy to view on an SQL interface like STRSQL. But How can we handle this result set pragmatically in Embedded SQL or SQL/PL code?

The answer is SQL CURSOR.

CURSOR provides a simple way to access Result Set returned by SELECT Statement. You can think of CURSOR as a pointer to one row in a ResultSet containing multiple rows. So at a time cursor can reference only one row, but It can move to other rows of ResultSet as required.

To use CURSOR, there are 4 control…


GLOBAL VARIABLES

  1. GLOBAL VARIABLES are database objects to save a single value at a time(like DTAARA object)
  2. They can be accessed and modified using SQL statements.
  3. Their definition (only definition, not actual value) is stored in the database catalogs.
  4. The reason for this is that global variables have a session scope.
  5. This means that every session can use the GLOBAL VARIABLES that exist in the catalogs, but each session has its own private value that it can manipulate and use.
  6. No session can access the GLOBAL VARIABLE’s value of another session.
  7. CREATE VARIABLE statement is used to create new GLOBAL VARIABLES.
  8. These…

We can use a very simple correlated query to find the nth highest or lowest record in a table.

Here is the query to find 2nd highest salary from the…


There are two ways to write SQL: Static SQL, which gets the job done in most cases, and the second one is Dynamic SQL for the rest of the complex stuff.

Static SQL

  1. In a static SQL statement, most of the statement is hard-coded like table names and Column names. This part of the statement can not be changed at runtime.
  2. The only information that can be specified at runtime values in the WHERE clause of the SQL statement using host variables.
  3. The access plan for a static SQL is generated as soon as code is compiled.
  4. Once an access…

Materialized Query Table(MQT)

  1. A materialized query table is a table whose structure and definition are based on the result of a given query.
  2. Materialized query table gets its data from the same query. And MQT actually contains that data.
  3. That query may contain more than 1 table.

Syntax for MQT

CREATE TABLE mqt-name AS (select-statement) mqt-options

for example

CREATE TABLE TRANS_REPORT AS (SELECT YEAR AS SYEAR, MONTH AS SMONTH, DAY AS SDAY, SUM(AMOUNT) AS SSUM FROM TRANS                             
GROUP BY YEAR, MONTH, DAY )
DATA INITIALLY DEFERRED
REFRESH DEFERRED
MAINTAINED BY USER

MQT Options details

REFRESH TABLE

How MQT will get data when created…


There are multiple ways to Delete duplicate data from A Table using SQL Query. Here we are going to talk about using Correlated subqueries to Delete duplicate data.

Let say we have an EMPLOYEE table as follows.

In the above EMPLOYEE table, multiple records are duplicates. To remove all duplicate records, we can write a very simple correlated subquery.

Delete from EMPLOYEE E1 
where RRN(E1) > (Select MIN(RRN(E2)) from EMPLOYEE E2 where E2.id = E1.id)

To delete duplicate records, we need to find a unique value for each record of the table, so we are using SQL RRN() function to…

Sumit goyal

A programmer.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store