In this section we will take up those topics and commands of the Data Definition and Data Manipulation Languages, which are not discussed in the previous chapter.
Data Definition Language
As we have seen in the previous chapter the main data definition language commands are:
In this section we will consider the two commands related to INDEXes that is creating them and deleting them. The Data definition commands related to VIEWS will be discussed in the section on views.
Like base tables, indexes are created and dropped using the SQL DDLs. These are the only SQL statements that refer to indexes. The data manipulation Languages does not include any references to the indexes.
This is because the decision as to use a particular index in responding to a particular SQL query is made not by the user but by DB2. The general form of the CREATE INDEX statement is:
The optional 'other parameters' have to do with physical storage matters as in the CREATE TABLE. Each 'order' specification is either ASC(ascending)or DSC(descending), ASC being the default. The left to right sequence of naming columns in the CREATE INDEX statement corresponds to major-to-minor ordering in the usual way.
For example, the statement 'CREATE INDEX X ON T (P, Q DSC, R)' creates and index called X on the base table T in which entries are ordered by the ascending R-value within descending Q-value within ascending R-value. The columns P, Q and R need not be contiguous, nor need they all be of the same data type, nor need they be all fixed length or varying length.
Once created the index is automatically managed by the data manager to reflect the updates on the base table, till the index is dropped.
The 'UNIQUE' option in the CREATE INDEX statement specifies that no two rows in the indexed base table will be allowed to take the same value for the indexed column or column combination at the same time, or in other words no duplicates will be allowed. DB2 will reject any attempt to introduce a duplicate value.
An attempt to create a UNIQUE index on a non-empty table, which already violates the uniqueness, will fail. Two Nulls are considered to be equal to each other for unique indexing purposes. Thus if a given column has nulls allowed, that is NOT NULL is not specified, a UNIQUE index on that column will permit at the most one null to appear at any given time.
Indexes like the base tables can be created and dropped at any time. Any number of indexes can be built on a single base table.
Indexes can be dropped explicitly using the DROP INDEX command. Whenever the base table is dropped the indexes for that table is automatically dropped. The syntax of the DROP INDEX command is: DROP INDEX index-name. The index is destroyed or removed form the catalog. If an existing package depends on the dropped index, DB2 will do the automatic recompilation of the package the next time it is invoked.
Data Manipulation Language
As seen in the previous chapter, the 4 major DML statements are SELECT, INSERT, UPDATE and DELETE. We will use the suppler and parts database. The database has three tables a suppler table, a parts table and a shipment table as follows:
We will start with the simple queries that we have seen in the previous chapter. The query 'get the supplier numbers and status for suppliers in Paris' will be expressed in SQL as follows:
Here one important thing that is to be noted is that, the result of the query is another table, a table derived from the given tables in the database. In other words, the user of a relational system is always operating in the simple tabular framework. This fact, that the result of a query is another table is referred to as the 'closure' property of the relational systems.
The closure property means that since the result of a SELECT operation is another table, it is possible to apply another SELECT operation on that result. It also means that the SELECT operations can be nested. The syntax of the SELECT statement with most of the options is given below:
The significance of each of the options will become clear as we proceed with the examples. For getting all the supplier numbers from the shipment table the SQL statement will be something like this:
And the result will be as follows. Notice the duplication of the supplier numbers in the result. DB2 does not eliminate the duplicates from the result of a SELECT statement unless the user explicitly requests for that using the key word DISTINCT.
Even though in this example DISTINCT is used to eliminate the duplicate scalar values distinct can be used to eliminate 'duplicate rows' also. The alternative to DISTINCT is ALL, which is also the default. Retrieval of Computed Values
The SQL statements can be used for retrieving the computed values without any problems. For example consider the following query 'For all parts get the weight of the part in grams (the weight given in the table being in pounds)'. This is achieved by the following SQL statement:
The SELECT statement can also include general scalar expressions, involving scalar operators such as '+' or '-' and scalar function such as SUBSTR, instead of or as well as column names. But only the column names in the SELECT statement will give rise to name columns in the result as ca be seen from the above example. Retrieval Using * '*' is used to get all the columns of a particular table. For example the SQL, SELECT * FROM S' will give an entire copy of the table S. The star or '*' is the shorthand for the list of all column names in the table(s), in the left-to-right order in which the columns appear in the table(s). Thus the SELECT statements
The '*' notation is a very useful mechanism in the case of interactive queries but potentially dangerous in the case of embedded SQLs, because the meaning of '*' may change if the SELECT statement is recompiled and some definitional change has occurred in the interim.
Consider the query 'Get the supplier numbers and names of the suppliers in Paris with status >20.
The SQL will be something like this:
The conditional expression following the WHERE clause can consist of a simple comparison or it can consist of multiple comparisons and other kinds of conditional expressions all combined together using AND, OR and NOT and parentheses if required to indicate a desired order of evaluation.
Retrieval with Ordering
For getting the result in a particular order we use the ORDER BY clause. Consider the query ' Get all the supplier details in the descending order of status'. The SQL statement will be as follows:
One important thing to remember here is that, the column(s) on which the ORDER BY is specified should be part of the result table. It is possible to identify columns in the ORDER BY clause by column number instead of column name, that is by the ordinal, left-to-right, position of the column in question within the result table.
This feature makes it possible to order the result on the basis of a column, which does not have a name as in the example shown below:
SELECT using BETWEEN
BETWEEN can be used to get those items that fall within a range. For example consider the query' Get all the details of the parts whose weight is in the range of 14 and 17 both inclusive'. The SQL will be:
The same result can be obtained by using two individual comparisons connected together using an AND. The following SQL will also get the same result as above:
Another interesting point is that we can specify NOT BETWEEN. For example the query
SELECT using IN
Get the part details whose weights are 12 and 17.
Like between IN is also shorthand and the same results can be achieved by using by the following
Another similarity between IN and BETWEEN is that here also we can specify NOT IN.
Retrieval using LIKE
Like is a very powerful clause and also very useful. For example if you want to get all the part names beginning with 'C use like as follows:
In general the LIKE clause takes the form scalar-expression LIKE literal [ESCAPE character], where the scalar expression represents the value of the string. In the literal the '-' character stands for any single character, '%' stands for any sequence of n characters and all other characters stand for themselves. The following are some of the examples of the usage of LIKE:
If the ESCAPE clause and a character is specified it means that, the special interpretation given to the literal characters '-' and '*%' can be disabled. For example in the last example the backslash character 'V has been specified as the ESCAPE character, which means that the special interpretation given to '-' and '%' can be disabled by preceding such characters with a backslash. NOT LIKE is also available. For example, CITY NOT LIKE '%E%' will evaluate to true if CITY does not contain an 'E'.
Operations using NULLS
For retrieving rows where some of the columns have been defined as NULLs there is a special comparison operator of the form IS [NOT] NULL.
For example suppose the STATUS of supplier SI is Null. So in order to get the supplier details we use the following SQL:
It is important to note that the syntax is ' STATUS IS NULL' and not 'STATUS = NULL' which is illegal. This is because nothing, not even null itself is considered equal to null. Another point is that it is illegal to include NULL in the SELECT clause like SELECT NULL.
IBM Mainframe Related Interview Questions
|IBM Lotus Notes Interview Questions||IBM-CICS Interview Questions|
|COBOL Interview Questions||Linux Interview Questions|
|IBM-JCL Interview Questions||IBM Mainframe Interview Questions|
|IBM AIX Interview Questions||IBM WAS Administration Interview Questions|
|IBM Lotus Domino Interview Questions||IBM Integration Bus Interview Questions|
|Mainframe DB2 Interview Questions||Unix Production Support Interview Questions|
Ibm Mainframe Tutorial
Introduction To Software Development
Introduction To Ibm Mainframes
Tso And Ispf
Jes2, ]es3 And Sms
Introduction To Job Control Language (jcl)
The Job Statement
The Exec Statement
The Job And Exec Statements
The Dd Statement
Procedures And Symbolic Parameters
Generation Data Groups (gdg), Compile/link-edit And Run Jcls
Access Method Services (ams)
Additional Vsam Commands
Introduction To Rexx
Overview Of Rexx
Introduction To Cics
Exception Handling In Cics
Developing A Cics Application
Cics Programming Techniques
Basic Mapping Support (bms)
Transient Data Control
Temporary Storage Control
Interval And Task Control
Cics Application Design
Recovery And Restart
System Security And Intersystem Communication
Cics Debugging Facilities And Techniques
Bms Map Definition Macros And Copylib Members
Cics Response And Abend Codes
Data, Information And Information Processing
Introduction To Database Management Systems
Introduction To Relational Database Management Systems
Database Architecture And Data Modeling
Overview Of Db2
Structured Query Language (sql)
Data Security And Access
Db2 Application Development
Qmf And Db2i
Db2 Performance Monitoring, Utilities And Recovery/restart
Overview Of Information Management System (ims)
Introduction To Vs Cobol Ii
Overview Of Application Development In Vs Cobol Ii
Overview Of The Cobol Program
Sorting And Merging Files
Coding Cobol Programs That Run Under Cics. Ims, Db2 And Ispf
Compiling The Program
Link-editing The Program
Executing The Program
Improving Program Performance
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.