Mainframe JCL, COBOL ISPF DB2 CICS: IBM

Ø DB2 was developed by IBM

Ø DB2 is a specific type of database which contains some advantages than DBMS

Ø DB2 is completely a RDBMS(Relational Data Base Management System)

Ø DB2 is also known as Data Base2

Ø In DB2 we can provide security in column level, table level for the data in a table

Ø All the records can be stored in the form a table

Ø Table is a collection of one or more rows and columns

Ø In DB2 we have two types of objects. They are

` 1. Physical Objects – database, Table Space(TS), synonyms, alias etc.,

2. Logical Objects – views, buffer space, index space

INTERACTION WITH DB2

Ø In DB2, to interact with DB2 subsystem we mainly have

SPUFI – SQL Processing Using File as Input

SQL – Structured Query Language

QMF – Query Management Facility

Ø We use SPUFI to communicate with DB2 through SQL

SQL

Ø By using SQL we can create tables, indexes, views, synonyms, alias

Ø Using SQL we can delete the table data

Ø We can alter the table

Ø We can fetch the data of a table

Ø DB2 does not store any data in it and mainly used for management purpose

Ø DB2 is also used to organize the data

Ø Table Space is actual storage of DB2 tables

Ø Table Space is collection of LINEAR DATA SETS (LDS)

Ø The max storage available in Table Space is 64GB

Ø The structure of actual data stored in data base is as follows

LDS

Table

Space

Ø In Table Space we have mainly 3 types. They are…….

1. Simple Table Space

2. Segmented Table Space

3. Partitioned Table Space

4. Universal Table Space

5. Large Object Table Space

6. EA enabled Table Space and Index Space

7. XML Table Space

Simple table spaces
A simple table space is neither partitioned nor segmented. Although you cannot create simple table spaces, DB2 can still use existing simple table spaces

Segmented (non-universal) table spaces
A table space that is segmented is useful for storing more than one table, especially relatively small tables. The pages hold segments, and each segment holds records from only one table.

Partitioned (non-universal) table spaces
A table space that is partitioned stores a single table. DB2 divides the table space into partitions.

Universal table spaces
You can combine the benefits of segmented space management with partitioned table space organization by using universal table spaces. Auniversal table space is a combination of partitioned and segmented table space schemes.

Large object table spaces
Large object (LOB) table spaces (also known as auxiliary table spaces) hold large object data, such as graphics, video, or large text strings. If your data does not fit entirely within a data page, you can define one or more columns as LOB columns.

EA-enabled table spaces and index spaces
You can enable partitioned table spaces for extended addressability (EA), a function of DFSMS. The term for table spaces and index spaces that are enabled for extended addressability is EA-enabled.

XML table spaces
An XML table space stores an XML table.

DB2 DATATYPES

In DB2 we have different data types as follows. They are

1. SMALLINT - 2 Bytes

2. INT - 4 Bytes

The max value of INT is 9 characters only numeric values are allowed

3. CHAR(n) – 1 Byte

It accepts only the character data and max length of a character is 1 Byte

4. VARCHAR(n)

Ø It is a variable character.

Ø The max length of VARCHAR is 4096. It contains two fields ‘length and text’

5. FLOAT(n)

Ø FLOAT is a single precision floating point number

Ø Value ranges from ‘1 – 21’

Ø Default is ‘21’

6. DOUBLE

Ø It is a double precision floating pont number values ranges from ’22 – 53’

Ø Default is ‘53’

7. NUMERIC(P,S)

Ø Using NUMERIC data type we can represent the numeric value

Ø It is also used for separation of data

Ø Numeric(5,2)

INPUT – 25000 OUTPUT – 25,000

8. DECIMAL(P,S)

Ø It is mainly used for Numeric Values to calculate the data with the fraction value

EX - 35125

DECIMAL(5,2) – 351.25

NOTE

The max value of P is ‘31’

TIME

Used to include the time value HH:MM:SS

DATE

Date is a packed decimal format YYYY-MM-DD

TIME STAMP

It is a combination of time and date the format is as follows

YYYY-MM-DD HH:MM:SS nnnnnn

`Max characters are 26

While working with SQL we have some predefined commands included in the languages. They are

1. DATA DEFINITION LANGUAGE (DDL)

2. DATA MANIPULATION LANGUAGE (DML)

3. DATA CONTROL LANGUAGE (DCL)

4. TRANSACTION CONTROL LANGUAGE (TCL)

1. DATA DEFINITION LANGUAGE (DDL)

In DDL we have following commands

a. CREATE

b. ALTER

c. DROP

a. CREATE

By using CREATE we can create

I. TABLE

II. VIEW

III. ALIAS

IV. SYNONYM

V. INDEX

SYNTAX – CREATE A TABLE

CREATE TABLE TABLENAME(COLUMN1 DATATYPE,COLUMN2 DATATYPE………………….);

CREATE TABLE TABLE1(ENO INT,ENAME CHAR(20),ESAL DECIMAL(7,2));

Ø By using CREATE we can create VIEWS as virtual tables for the tables

Ø By using VIEWS we can store any column data as a virtual table

SYNTAX

CREATE VIEW VIEWNAME AS SELECT COLUMN1,COLUMN2,COLUMN3………..FROM TABLE1

CREATE VIEW V1 AS SELECT ENO,ENAME FROM TABLE1;

ALIAS

ALIAS is like a duplicate name for the base table, it is sharable to all the users

SYNTAX

CREATE ALIAS ALIASNAME FOR TABLENAME;

CREATE ALIAS A1 FOR TABLE1;

SYNONYM

It is also a duplicate name for the table. Synonym can be accessed by the specific user

SYNTAX

CREATE SYNONYM NAME OF THE SYNONYM FOR USER ID.TABLENAME;

CREATE SYNONYM S1 FOR MATE02.TABLE1;

Once you drop the table, SYNONYM table will also get deleted

Referential Integrity in DB2

The Foreign Key values of dependent table should have a matching primary key values in a parent table is called ” Referential Integrity”.

Ø Whenever an application programmer wants to maintain the data uniquely in the specified column we can create PRIMARY KEY for that column

Ø PRIMARY KEY does not allow the duplications

Ø Only one PRIMARY KEY for one table

Ø We cant alter the PRIMARY KEY

Ø FOREIGN KEY is related key for the PRIMARY KEY through which you can connect to the PRIMARY KEY to maintain the records efficiently in a table

Ø While creating a PRIMARY KEY ‘NOTNULL’ does not allow Null values in specific column

CREATE TABLE TABLE2(ENO INT NOTNULL PRIMARYKEY,ENAME CHAR(20),ESAL DECIMAL(7,2),EMOBILE INT);

CREATE TABLE DEPT(ENUM INT,DEPTNO CHAR(10),DEPTNAME CHAR(25),FOREIGN KEY(ENO REFERENCE TABLE2 ON DELETE DELETE RULES:

On Delete Cascade:

If we delete the rows in a parent table then the related rows of dependent table will be deleted automatically.

On Delete Set Null:

If we delete the rows of parent table then the foreign key values of the related rows in dependent table will set to NULL.

On Deleted Restrict:

Ø If we delete rows of parent table which are having matching foreign key values in dependent table then we will get SQL error code as -532.

Ø In order to overcome from this error we can delete the rows from dependent table first and then delete the rows from parent table.

Ø If we won’t mention any of these by default ON DELETE RESTRICT will be substituted.

NOTE

We can perform all the operations on child table

NOTE2

Ø After creation of Parent table you have to create unique Index for the table

Ø We can create Unique Index for any other table other than Primary Key

CREATE UNIQUE INDEX I ON TABLE2(ENAME);

CASE STUDY

We can generate the default values for the specific column by using an identity

CREATE TABLE TABLE3(ENO INT NOTNULL GENERATED ALWAYS AS IDENTITY(START WITH 1001,INCREMENT BY 1);

2. ALTER

By using ALTER command we can add any no of columns to the table

SYNTAX

ALTER TABLE TABLENAME ADD COLUMNNAME DATATYPE;

At a time we can add only one column to the table

ALTER TABLE TABLE2 ADD ADDRESS CHAR(15);

3. DROP

By using DROP we can drop the entire structure of the table along with the data so that you can create one more table with same name

SYNTAX

DROP TABLE TABLENAME

DROP TABLE TABLE2;

2. DATA MANIPULATION LANGUAGE (DML)

In DML we have the following commands

a. INSERT

b. SELECT

c. DELETE

d. UPDATE

a. INSERT

Used to INSERT the values into the table

SYNTAX

1. INSERT INTO TABLENAME(COLUMN1,COLUMN2,………) VALUES(VALUE1,VALUE2,………….);

2. INSERT INTO TABLENAME VALUES(VALUE1,VALUE2,……….,VALUEn);

INSERT INTO TABLE2 (ENUM) VALUE(1005);

INSERT INTO TABLE2 VALUES(1003,’PAWAN’,25000,8801965434);

b. DELETE

Used to delete the number of rows in the table

SYNTAX

DELETE FROM TABLENAME WHERE CONDITION;

DELETE FROM TABLE2 WHERE ENUM=1005;

c. UPDATE

Used to update the column data by taking any column reference

SYNTAX

1. UPDATE TABLENAME SET COLUMNNAME=VALUE

2. UPDATE TABLENAME SET COLUMNNAME=VALUE WHERE CONDITION;

1. UPDATE TABLE2 SET ENAME=’PAWAN’ WHERE ENUM=1001;

2. UPDATE TABLE2 SET ESAL=25000; (Update Entire Column)

d. SELECT

By using SELECT we can retrieve data from the table and based on condition we can select particular rows from the column

SYNTAX

1. SELECT * FROM TABLENAME;

EX – SELECT * FROM TABLE2;

2. SELECT COLUMNNAME FROM TABLENAME;

EX – SELECT FROM TABLE2;

3. SELECT COLUMNNAME FROM TABLENAME WHERE CONDITION;

EX – SELECT ENUM FROM TABLE2 WHERE ESAL<25000;

4. RETRIEVING THE NULL VALUES FROM THE TABLE

EX – SELECT ENUM FROM TABLE2 WHERE ESAL IS NULL;

NULL = Predefined Word

SELECT USING RELATIONAL OPERATORS

The RELATIONAL OPERATORS are AND,OR,NOT

EX1. SELECT * FROM TABLE2 WHERE ENUM=1001 AND ESAL=15000;

EX2. SELECT * FROM TABLE2 WHERE ENUM=1002 OR ESAL>25000;

EX3. SELECT * FROM TABLE2 WHERE NOT ESAL=15000;

EX4. SELECT * FROM TABLE2 WHERE ESAL IS NOTNULL;

PATTERN MATCHING

Ø By using like function we can retrieve the records based on condition

Ø This is also used for retrieving the data also for filtering the data

Ø By using 2 operators we can perform pattern matching as follows

1. _ (Under Score)

2. % - for all

EX1 – SELECT * FROM TABLE2 WHERE ENAME LIKE “_A”;

It displays all the names whose second character is ‘A’

EX2 – SELECT * FROM TABLE2 WHERE ENAME LIKE “A%”;

It displays all the names which contains at least one ‘A’ in the name field

SQL BUILTIN FUNCTIONS

These are predefined in the SQL, divided into two types. They are…..

1. AGGREGATE FUNCTIONS (OR) COLUMN FUNCTIONS

2. SCALAR FUNCTIONS

1. COLUMN FUNCTIONS

We can perform all the operations only on the columns and also these are used to perform aggregate operations

a. MIN – SELECT MINIMUM(ESAL) FROM TABLE2;

b. MAX – SELECT MAX(ESAL) FROM TABLE2;

c. AVG – SELECT AVG(ESAL) FROM TABLE2;

d. COUNT

Used to count the no of rows in the table

EX – SELECT COUNT(ESAL) FROM TABLE2;

e. COUNT(DISTINCT)

By using distinct we can eliminate the duplications in the column

EX – SELECT COUNT(DISTINCT ESAL) FROM TABLE2;

f. SUM

Used to find the total and also applicable for numeric data

EX – SELECT SUM(ESAL) FROM TABLE2;

2. SCALAR FUNCTIONS

A scalar function is applied to single value rather than a set of values.

a. CHAR

This function returns the character representation of date, time, timestamp or decimal.

Syntax CHAR(expression [,ISO] [,USA] [,EUR] [,JIS] [,LOCAL] )

Ex SQL Queries Let us assume TEMP is table contains one record.

SELECT CHAR(CURRENT_DATE) FROM TEMP

Result : 2005-12-17

SELECT CHAR(CURRENT_DATE,USA) FROM TEMP

Result : 12/17/2005

SELECT CHAR(CURRENT_TIME,USA) FROM TEMP

Result : 02:12 PM

SELECT CHAR(CURRENT_TIME) FROM TEMP

Result : 14.12.43

b. DATE

Ø The DATE function returns a date. if the argument is null, the result is the null value.

Ø If the argument is a timestamp, the result is the date part of the timestamp.

Ø If the argument is a date, the result is that date.

Ø If the argument is a number, the result is the date that is n1 days after

January 1, 0001, where n is the integral part of the number.

Example SQL Query :

SELECT DATE(CURRENT_TIMESTAMP) FROM TEMP

Result : 2004-11-17

c. DAY

This function returns the day part of the argument. If the argument is null, the result is the null value.

Example : Assume that DATE1 and DATE2 are DATE columns in the same

table. Assume also that for a given row in this table, DATE1 and DATE2 represent

respectively the dates 15 January 2000 and 31 December 1999. Then, for the given row:

DAY(DATE1 DATE2) returns the value 15.

d. DAYS

This function returns the integer representation of given date. This function caluculate the number of days from January 1, 0001 to the argument date. If the argument is null, the result is the null value.

Example SQL Query :

SELECT DAYS('20031121') FROM TEMP

Result : 731540

e. DECIMAL

This function returns decimal represenation of given value.

Syntax DECIMAL(expression [,integer1] [,integer2] )

The data type of the result is DECIMAL(p,s), where p and s are the second and third arguments. ( integer1, integer2 ). expression can be character string or a number.

Represent the average salary of the employees in EMPLOYEE2 table as an 10digit decimal number with two of these digits to the right of the decimal point.

Example SQL Query :

SELECT DECIMAL(AVG(SALARY),8,2) FROM EMPLOYEE

f. DIGITS

The DIGITS function returns a character string representation of its argument. The argument must be an integer or a decimal number. if the argument is null, the result is the null value.

Example : Assume that BALANCE has the data type DECIMAL(6,2), and that one of its values is 6.28. Then, for this value:

SELECT DIGITS(BALANCE) FROM BANK

Return value will be '000628'.

The result is a string of length six (the precision of the column) with leading zeros padding the string out to this length. Neither sign nor decimal point appear in the result.

g. FLOAT HEX HOUR INTEGER

FLOAT – The FLOAT function returns a floating point representation of its argument. If argument is null, the result is null value

HEX – The HEX function returns hexadecimal representation of its argument if the argument is null, the result is the null value

HOUR – This function is to get hour part from time or timestamp. If the argument is null, the result is the null value

INTEGER – This function returns an integer representation of given value. If the argument is null, the result is null value if the argument is null, the result is the null value.

h. LENGTH

LENGTH function returns the length of the given argument. if the argument is null, the result is the null value.

Example : Assume that EMPNAME is a VARCHAR(14) column that contains 'RAMESH' for employee 200. The following SQL query:

SELECT LENGTH(EMPNAME) FROM EMPLOYEE2 WHERE EMPNO = '200'

This query returns value 6

i. MICROSECOND

The MICROSECOND function returns the microsecond part of its argument. The argument must be a timestamp or timestamp duration. The result of the function is a large integer. if the argument is null, the result is the null value.

j. MINUTE

The MINUTE function returns the minute part of its argument. The argument must be a time, timestamp, time duration, or timestamp duration. The result of the function is a large integer. if the argument is null, the result is the null value.

k. MONTH

The MONTH function returns the month part of its argument. The argument must be a date, timestamp, date duration, or timestamp duration. The result of the function is a large integer. If the argument is null, the result is the null value.

Example : Select all rows in the table EMPLOYEE2 for employees who were born in May: SQL Query :

SELECT FROM EMPLOYEE2 WHERE MONTH(BIRTHDATE) = 5;

l. SECOND

SECOND function returns the second part of time or timestamp. If argument is null

Result will be null.

Example : If APPRECEIVED timestamp is 2004102111.09.20.000012'

SECOND(APPRECEIVED) will give result 20.

m. SUBSTR

SUBSTR function returns the part of given string. If the argument is null, function returns null value.

Syntax SUBSTR(string,start [,length])

Here string is the input string, start is the starting position of substring in the given string.

length is length of the substring. If this parameter is omitted the result will be number of characters starting from position specified in "start" parameter till the end of the string.

Example : Let us assume EMPNAME contains "RAMESH" , we wan to get first three characters from the EMPNAME.

SUBSTR(EMPNAME,1,3) returns the first 3 characters which is "RAM"

TIME TIMESTAMP

The TIME function returns a time derived from its argument. The argument must be a timestamp, a time, or a valid string representation of a time. If the argument is null, the result is the null value.

The TIMESTAMP function returns a timestamp derived from its argument or arguments. The rules for the arguments depend on whether the second argument is specified.

If both arguments are specified, the first argument must be a date or a valid string representation of a date and the second argument must be a time or a valid string representation of a time.

Syntax TIMESTAMP(expression [,expression])

n. VALUE

VALUE function returns the value of given value. COALESCE can be used as a synonym for VALUE.

Syntax VALUE(expression [, expression ] )

If argument1 is null, This function returns argument2 value.

Argument1, Argument2 must be of same type.

Example : Assume that MARK1 and MARK2 are columns in table STUDENT, and nulls are allowed in MARK1 column. Select all

the rows in STUDENT for which MARK1 + MARK2 > 120, assuming a value of 0 for MARK1 when MARK1 is null.

SQL Query :

SELECT * FROM STUDENT WHERE VALUE(MARK1,0) + MARK2 > 100;

o. VARGRAPHIC

The VARGRAPHIC function returns a graphic string representation of a character string. if the argument is null, the result is the null value.

p. YEAR

The year function returns the year part of its argument. If the argument is null, result will be null value.

Example : Get the all employees who are born in 1977

SQL Query :

SELECT * FROM EMPLOYEE WHERE YEAR(BIRTH_DATE) = 1977;

q. UPPER

Used to view the data in uppercase

EX – SELECT ENAME,UPPER(ENAME) FROM TABLE2;

r. LOWER

Used to view the data in lower case

EX – SELECT ENAME, LOWER(ENAME) FROM TABLE2;

s. CONCATE

Ø Used to concate or combine two or more columns

We may use the operator ‘ ‘ to combine

SELECT ENUM ENAME FROM TABLE3;

2. SELECT CONCAT(ENUM,ENAME) FROM TABLE3;

NOTE – To fetch all the tables from your User ID we use a SQL query

SELECT * FROM SYSIBM.SYSTABLES WHERE CREATOR=’USERID’;

UNION AND UNIONALL

Ø UNION will eliminate the duplicate values in the table

Ø Whereas UNIONALL will not eliminate the duplications

Ø It performs critical calculation while performing UNION and UNIONALL i.e., all the column names and data types should be same

EX1 – SELECT * FROM TABLE1 UNION SELECT *FROM TABLE2;

EX2 – SELECT * FROM TABLE1 UNIONALL SELECT * FROM TABLE2;

JOINS

Ø Join is used to combine two or more tables to get the complete information about the entities.

Ø Join can combine the table which has common columns in the tables.

Ø If no matching column(s) is there then SELECT Statement is fine enough to get the information from more than one table.

Ø The results of join have the associated rows from one table with another table(s).

Ø Join is intermediate table result from more than one table based on matching column(s) or attribute(s).

Ø Join will get more columns (complete information) about the particular entity (column).

Ø If the specified value matches in both tables, the rows will be matched and combined.

Ø DB2 supports the joins.

There are two types of Joins based on how the tables are joined. They are…….

1. INNER JOIN

2. OUTER JOIN

1. INNER JOIN

Ø INNER JOIN will join the columns which satisfies the condition from the tables provided in JOIN.

Ø In other words, INNER JOIN will combine the row from left table to right table for the matching column(s) rows

CASE STUDY

Let us say, A and B table are joining. A has the matching row for a specific value in a matching columns and B should have the row with the matching value. Then only the row will appear in result table.

Let us say, A and B table are joining. A has the matching row for specific value in matching columns. But B has no row with the matching value. Then the row will be dropped in the result table.

SYNTAX

SELECT TABLE1-COLUMN-NAMES,TABLE2-COLUMN-NAMES FROM TABLE1-NAME INNERJOIN TABLE2-NAME ON JOINING-COLUMN(S) WHERE SEARCH-CONDITION(S);

2. OUTER JOIN

Ø OUTER JOIN will join all the columns from left table and right table based on the condition.

Ø If the matching rows missed in the other table, the columns will be filled with NULLs of missed table in result table

CASE STUDY

Let us say, A and B table are joining. A has the matching row for the specific value in the matching columns and B also should have the row with the matching value. Then only the row will appear in the result table

Let us say, A and B table are joining. A has the matching row for the specific value in the matching columns. But B has no row with the matching value. Still the row is joined in OUTER JOIN, but the columns of B will be filled up with NULL values in the result table

Let us say, A and B table are joining. A has no matching row for a specific value in a matching columns. But B has row with the matching value. Still the row is joined in OUTER JOIN, but the columns of A will be filled up with NULL values in the result table

In other words, it will match all the rows from both tables even though other table has no matching row in it

This is also called as NON EQUI JOIN

This is also well known as NON FULL OUTER JOIN

SYNTAX

SELECT A-COLUMNS-LIST,B-COLUMNS-LIST FROM TABLE-A OUTERJOIN TABLE-B WHERE CONDITION ON JOINING COLUMN(S);

In the above case, it will retrieve all the matched and unmatched rows from Table-A and Table-B. In other words, OUTER JOIN will retrieve all the rows from two tables

OUTER JOIN is divided into two types based on how tables joined. They are…….

a. LEFT OUTER JOIN

b. RIGHT OUTER JOIN

a. LEFT OUTER JOIN

LEFT OUTER JOIN will retrieve all the rows from the tables which are provided before LEFT OUTER JOIN keyword and only matched rows from the tables provide after the keyword

CASE STUDY

Let us say, A and B table are joining. A has the matching row for a specific value in a matching columns. But B have no row with the matching value. Still the row is joined in OUTER JOIN, but the columns of B will be filled up with NULL values in the result table

SYNTAX

SELECT COLUMNS-LIST FROM TABLE-A WHERE CONDITION LEFT OUTER JOIN SELECT COLUMS-LIST FROM TABLE-B WHERE CONDITION ON JOINING COLUMN(S);

b. RIGHT OUTER JOIN

RIGHT OUTER JOIN will retrieves matched rows from the tables which are provided before RIGHT OUTER JOIN keyword and all the rows from the table provide after the keyword

CASE STUDY

SYNTAX

SELECT A-COLUMNS-LIST,B-COLUMNS-LIST FROM TABLE-A RIGHT OUTER JOIN TABLE-B ON JOINING COLUMN(S) WHERE CONDITION;

SUB QUERIES

Ø In SUB QUERIES concept 1^st inner query will be executed and then the outer query will be executed

Ø In SUB QUERIES we may check two or more conditions within one SELECT Statement

1. SELECT MAX(ESAL) FROM TABLE2 WHERE ESAL<(SELECT MAX(ESAL) FROM TABLE2);

2. SELECT MIN(ESAL) FROM TABLE2 WHERE ESAL>(SELECT MIN(ESAL) FROM TABLE2);

We can write max 15 queries

CORRELATED SUB QUERIES

Ø In correlated sub queries for every value in the inner query, outer query will execute the number of times

Ø By using correlated sub queries concept we can find any max or min value from the table

SELECT SAL FROM EMPA WHERE N=(SELECT COUNT(*) FROM EMP B WHERE A.SAL<=B>SAL);

GROUPBY

Ø GROUPBY is a clause used to group the rows.

Ø GROUPBY clause is mainly used for grouping the data based on any one aggregate function.

Ø Grouping can be done with similar values or based on certain condition. The columns functions should not apply to the columns provided in GROUPBY.

Ø The column used in GROUPBY should exist in SELECT columns list. Otherwise, the user will get BIND Error.

Ø GROUPBY clause can be used on more than one column.

Ø If Grouping column is non nullable column, then all rows which have null values will become a group.

Ø Without having any aggregate function we cant use GROUPBY clause.

Ø While grouping the data, the comparing column names data types should be same

SYNTAX

GROUPBY COLUMN1,COLUMN2……… etc.,

SELECT SUM(ESAL) FROM TABLE2 GROUPBY DEPTNUM;

Where ESAL and DEPTNUM data types should be same

HAVING

Ø HAVING will replace ‘WHERE’ clause while working with GROPBY clause.

Ø HAVING is used to specify the condition for the GROUPBY clause.

Ø HAVING is optional even though GROUPBY existed. But GROUPBY is mandatory to use HAVING.

Ø More than one condition can be used in HAVING

SYNTAX

HAVING CONDITION1 CONDITION2………etc.,

SELECT SUM(ESAL) FROM TABLE2 GROUPBY DEPTNUM HAVING ESAL>20000;

ORDERBY

Ø ORDERBY clause is used to SORT the data which is retrieved based on the data of specified columns.

Ø In ORDERBY clause any one column reference is required

Ø One or more columns can be used in ORDERBY clause.

Ø The columns specified in ORDERBY should be existed in SELECT.

Ø SORTING can be done in two ways. They are…….

1. ASCENDING(ASC): Ascending Order which is DEFAULT

2. DESCENDING(DESC): Descending Order

SYNTAX

ORDERBY COLUMN1 DESC.COLUMN2 ASC,………etc.,

SELECT ENUM,ENAME FROM TABLE2 ORDERBY ESAL;

DATA CONTROL LANGUAGE (DCL)

By using DCL commands, we can protect the data from unauthorized access. We have mainly three commands in it. They are………..

1. GRANT

2. REVOKE

3. LOCK

1. GRANT

Ø It is used to pass the permissions to the particular User ID or to the public

Ø Once you given an access another users can access the data of table

SYNTAX

GRANT OPERATION ON OBJECT-NAME TO WHOM;

If you coded operation ALL any user or specific user can do all the remaining operations

GRANT ALL ON TABLE2 TO PUBLIC;

(OR)

GRANT ALL ON TABLE2 TO MATE09;

2. REVOKE

By using REVOKE we can cancel the permission given by GRANT. We can restrict the access on specific object

REVOKE ALL ON TABLE2 FROM PUBLIC;

3. LOCK

By using LOCK we can protect the data from unauthorized user. We have mainly two types. They are……

1. SHARED LOCK

2. EXCLUSIVE LOCK

In SHARED LOCK when user reading or updating the data from a table all the users can read. But in case of EXCLUSIVE LOCK no other users can access the data from the table

SHARED – FOR ALL

EXCLUSIVE – FOR SPECIFIC USER

SYNTAX

LOCK TABLE TABLE-NAME ON <LOCK> MODE;

1. LOCK TABLE TABLE2 ON SHARED MODE;

2. LOCK TABLE TABLE2 ON EXCLUSIVE MODE;

TRANSACTIONAL CONTROL LANGUAGE (TCL)

This is mainly used to know the status of any SQL statement. We have mainly two commands. They are……

1. COMMIT

2. ROLLBACK

COMMIT specifies successful execution of SQL statements

ROLLBACK specifies unsuccessful execution so that re-execution of SQL statement is required

EMBEDDED SQL

Ø EMBEDDED SQL represents usage of standard SQL statements in a high level language like COBOL

Ø In EMBEDDED SQL completely tells about the usage of DB2 SQL in COBOL

Ø In EMBEDDED SQL we have mainly two types. They are…….

1. STATIC SQL

2. DYNAMIC SQL

While working with application program we have different components which are used to communicate with DB2

SQLCA (SQL COMMUNICATION AREA)

Ø It is mandatory for every COBOL+DB2 program

Ø SQLCA will give the feedback of SQL statement execution to the application program

Ø It is like a copybook which includes all necessary information

HOST VARIABLE

Ø Used to store the data of a table

Ø It is a hosting variable because these variables are used to receive the data from a table or inserting data into table etc

Host variable is identified with prefix ‘:’

Mainframe JCL, COBOL ISPF DB2 CICS

IBM - DB2

No comments:

Post a Comment