In this section we will discuss the basic scalar objects and operators supported by DB2. The basic data object is the scalar value. For example, the object appearing at the intersection of a given row and a given column of a given table is a scalar value. Each such value belongs to a particular scalar data type and for each such data type there can be an associated format for writing literals of that type. Scalar objects can be operated upon by means of scalar operators. For example two numeric scalar objects can be added together using the scalar arithmetic operator '+'. DB2 provides scalar functions, which can also be regarded as scalar operators. Scalar operators and objects can be combined to form scalar expressions.
DB2 supports the following scalar data types.
The valid operators that can be used with date, time and timestamp are the infix arithmetic operators '+ and '-'. A complete list is given below:
Given below are some of the examples for the above expressions. Some of them are illegal for obvious reasons:
Date/Time arithmetic is performed in accordance with the calendar and permissible date/time values. Therefore, the expression DATE ('5/31/1977') + 1 MONTH will yield the result '6/30/1977' and not '6/31/1977'. But the expression DATE ('6/30/1977') -1 MONTH yields the result '5/30/1977' and not '5/31/1977*.
In general if we add a date duration d to some date and subtract the same duration from the result. we are not guaranteed to end up with the date we started with. Thus DATE ('5/31/1988') + 1MONTH - IMONTH does not yield '5/31/1988'. But the expression DATE ('5/31/1988')+30 DAYS-30DAYS will definitely yield'5/31/1988'.
Scalar Operators and Scalar Functions
DB2 provides a number of scalar operators and functions that can be used in the construction of scalar expressions. These operators and functions are summarized below:
DB2 supports a number of special registers. They are given below:
Nulls are special markers by which SQL systems like DB2 represents missing information. For example, when we say that the height of a Person is null, it means that, we know that such a Person exists, he/she does have a height, but we do not know what the height is. In other words we do not know the exact height that could sensibly be put in the HEIGHT slot in the row of the Person in question. Therefore, we mark it as null, and we interpret that the mark means that we do not know what the real value is. But the whole point about nulls is that they are not values and are not same as blank or a zero.
In general any column can have nulls unless the definition of that column explicitly specifies NOT NULL. If a given column is allowed to contain nulls, when a row is inserted into the table and no values are specified for that column DB2 will automatically place a null into that column. By the same token, DB2 will not allow the updating of a column for which NOT NULL has been specified with a null value.
When the option ' NOT NULL' is used 'WITH DEFAULT, it means that the column cannot contain nulls. But it is still possible to omit the values for the column during INSERT or UPDATE. If a row is inserted and no values are specified for some columns DB2 will insert one of the following default values in that column:
Two special comparison operators, IS NULL and IS NOT NULL are provided to test for the presence or absence of nulls. For example, let A=l, B='x' and 'C is null. Then C IS NULL is true, A IS NULL is false and B IS NOT NULL is true.
Even though, DB2 has given the provision of Nulls, it is -always better and safer not to use Nulls, because they can create more trouble than they are worth. Nulls when included will display strange and inconsistent behavior and are rich source of error, confusion and chaos. So it is always to specify either NOT NULL or NOT NULL WITH DEFAULT for all columns.
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.