UNION and UNION ALL at DB2

Union at DB2 used to join 2 or more resultset from a query. For example if we want to see list of staff who work at IT department and FA department.

This is the syntax
select column1, column2
from table_name
where….
UNION
select column1, column2
from table_name
where….

Each column1 and column2 must be match with other resultset, or it will generate an error.

This is an simple example how to using UNION in Stored Procedure

CREATE PROCEDURE SP_PAKE_UNION ( )
DYNAMIC RESULT SETS 1
————————————————————————
– SQL Stored Procedure
————————————————————————
P1: BEGIN
– Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
select a.firstnme, b.deptname
FROM EMPLOYEE a, DEPT b
where a.workdept = b.deptno
and a.workdept =’E21′
union
select a.firstnme, b.deptname
FROM EMPLOYEE a, DEPT b
where a.workdept = b.deptno
and a.workdept =’A00′;
– Cursor left open for client application
OPEN cursor1;
END P1

That Store Procedure will generate list of staff who work at 'A00' and 'E21' department.

UNION is differ from UNION ALL. UNION just list the DISTINCT output. It will remove any record which is redundant. If you want to see all output, just change UNION with UNION ALL

Best regards,

Deny Sutani

Crime Fighting Computer Systems and Databases

As crime globalizes, so does crime fighting. Mobsters, serial killers, and terrorists cross state lines and borders effortlessly, making use of the latest advances in mass media, public transportation, telecommunications, and computer networks. The police - there are 16,000 law enforcement agencies in the Unites States alone - is never very far behind.

Quotes from the official Web pages of some of these databases:

National Center for the Analysis of Violent Crime (NCAVC)

Its mission is to combine investigative and operational support functions, research, and training in order to provide assistance, without charge, to federal, state, local, and foreign law enforcement agencies investigating unusual or repetitive violent crimes. The NCAVC also provides support through expertise and consultation in non-violent matters such as national security, corruption, and white-collar crime investigations.

It comprises the Behavioral Analysis Unit (BAU), Child Abduction and Serial Murder Investigative Resources Center (CASMIRC), and Violent Criminal Apprehension Program (VICAP).

VICAP is a nationwide data information center designed to collect, collate, and analyze crimes of violence - specifically murder. It collates and analyzes the significant characteristics of all murders, and other violent offenses.

Homicide Investigation Tracking System (HITS)

A program within the Washington state's Attorney General's Office that tracks and investigates homicides and rapes.

Violent Crime Linkage System (ViCLAS)

Canada-wide computer system that assists specially trained investigators to identify serial crimes and criminals by focusing on the linkages that exist among crimes by the same offender. This system was developed by the RCMP (Royal Canadian Mounted Police) in the early 1990s.

UTAP, stands for The Utah Criminal Tracking and Analysis Project

Gathers experts from forensic science, crime scene analysis, psychiatry and other fields to screen unsolved cases for local law enforcement agencies.

International Criminal Police Organization (ICPO) - Interpol's DNA Gateway

Provides for the transfer of profile data between two or more countries and for the comparison of profiles that conform to Interpol standards in a centralized database. Investigators can access the database via their Interpol National Central Bureau (NCB) using Interpol's secure global police communications system, I-24/7.

Interpol's I-24/7

Global communication system to connect its member countries and provide them with user-friendly access to police information. Using this system, Interpol National Central Bureaus (NCBs) can search and cross-check data in a matter of seconds, with direct and immediate access to databases containing critical information (ASF Nominal database of international criminals, electronic notices, stolen motor vehicles, stolen/lost/counterfeit travel and ID documents, stolen works of art, payment cards, fingerprints and photographs, a terrorism watch list, a DNA database, disaster victim identification, international weapons tracking and trafficking in human beings-related information, etc).

Interpol Fingerprints

Provides information on the development and implementation of fingerprinting systems for the general public and international law enforcement entities.

Europol (European Union's criminal intelligence agency) Computer System (TECS)

Member States can directly input data into the information system in compliance with their national procedures, and Europol can directly input data supplied by non EU Member States and third bodies. Also provides analyses and indexing services.