DB2
(Database 2)
History
IBM’s first database is IMS. This is
a hierarchical database. IBM introduces its second base on relational concepts
in 1980s and it is called as Database 2 (DB2).
Advantage of DBMS over File Management System
1.Increased Independency
If
a new field is added to a file, the layout of the file should be changed in all
the programs with new field, though the programs do not use this field. But if
a new column is added to a table, it does not need any program change, as long
as the programs do not need this field. Thus programs using Databases are
developed independent of physical structure.
2.Less Redundancy
In
file system, the same data is stored in multiple files to meet the requirements
of different applications. In DB2, we can store the common information in one
table and make all the applications to share it. By means of centralized
control and storing the fact at the right place and only once, data redundancy
is reduced.
3.Increased Concurrency and
Integrity
Concurrency
allows more than one task to share a resource concurrently. Integrity is
defined as validity or correctness of data. In file system, if a file is
allocated with OLD disposition, then no other program can use it. DB2 allows
more than one task to use the same table in update mode. At the same time, integrity
of data is ensured by its sophisticated locking strategy.
4.Improved Security
In
file system, all the records in the file are subjected to a specific access
right. The access cannot be controlled at field level or set level. But in DB2,
we can restrict access on column level and set level.
Types of Database
Hierarchical Relation between entities is established
using parent-child relationship-Inverted Tree Structure-This is the first
logical model of DBMS. Data stored in the form of SEGMENTS. Example: IMS
Network Any entity can be associated with any other entity. So
distinguishing between parent and child is not possible. This is a linked
structure model. Data stored in
RECORD and different record types are linked by SETS. Example: IDMS
Relational In mathematical terms, relation is TABLE.
Data is stored in the form of tables consists of Tuples (rows) and attributes
(columns).
Example: DB2
DB2 Objects
There are two types of elements or
objects in DB2.
System Objects: objects that are
controlled and used by DB2.
Example: DB2 Directory, DB2 catalog,
Active & Archive logs, Boot Strap Dataset (BSDS), Buffer Pools, Catalog
visibility Database and Locks.
Data Objects: Objects that are created and used by the
users.
Example: Tables, Indexes, Views,
Table spaces, Databases, Storage Groups.
DB2 Object-Storage Group
Storage
group is a collection of direct access volumes, all of the same type.
DB2 objects are allocated in Storage
group. A storage group can have maximum 133 volumes.
DB2 Object-Table Space
Table Spaces
are the physical space where the tables are stored. There are three types of
table spaces.
Simple Table Space:
More than one
table is stored in the table space and single page can contain rows from all
the tables.
Segmented Table Space:
Segment is
logically contiguous set of n pages where n is defined by SEGSIZE parameter of
TABLESPACE definition. Tables are stored in one or more segments. Mass delete
and sequential access are faster in Segmented type table space. Reorganization
will restore the table in its clustered order. Lock table command locks only
the table and not the table space.
Partitioned Table Space:
Only one
table can be stored. 1-64 partitions are possible in table space. NUMPART of
TABLESPACE definition decides the partitions. It is partitioned with value
ranges for single or combination of columns and these columns cannot be
updated. Individual partitions can be independently recovered and reorganized.
Different partitions can be stored in different groups.
DB2 Object-Database
It
is not a physical object. It is a logical grouping consists of Table spaces,
Index spaces, Views, Tables etc.
Whenever you create an object you have to explicitly say which Database it
belongs to or DB2 implicitly assigns the object to the right database.
In
the creation of table, we should say in which database and in which table
Space we are housing the table
(explicitly). In creating an index, we mention only the table name. DB2 decides
the database from the base table name.
It
can also be defined as a unit of start and stop. There can be a maximum of
65,279 databases in a DB2 Subsystem.
DB2 Object - Table
Table
is a structure consists of number of columns and rows. It is created
using the DDL CREATE TABLE.
Physical
hierarchy:
ROWID
is a pointer to data record in the page.
It
is a 4byte field. (3 bytes for page and 1 byte for line in the page)
Page
consists of 1 to 127 data records. It is the unit of I-O.
Table
space consists of pages and it is stored in STOGROUP.
Database
consists of one or more table space and other related objects.
DB2 Object- Index
Generally,
Index is used to access a record quickly. There are two types of
Indexes: Clustering and
Non-Clustering. If there is a Clustered index is available for a table, then
the data rows are stored on the same page where the other records with similar
index keys are already stored on. There can be only one cluster index for a
table. Refer the diagram in the next page for better understanding of clustered
and non-clustered indexes.
Understanding: Root Page, Leaf Page and Non-Leaf page
Referring to
VSAM basics, sequence set has direct pointers to control interval and last
level indexes have pointers to sequence set and higher-level indexes have
pointers to lower level indexes and there is one root index from where the
B-tree starts.
Similarly Leaf-pages contain full keys
with row-ids that points to data page. (Row id has page number and line
number). Non-leaf pages have pointers to leaf pages or lower level non-leaf
pages and the root page has pointer to first level non-leaf pages.
View of clustered index – Diagram
2 Object-Index Space
Unlike
table spaces, Index spaces are automatically created when indexes are
created.
Indexes are stored in index spaces.
DB2 Object - Alias & Synonym
Alias
and Synonym are alternate name for a table. The main differences
between them are listed below:
SYNONYM
|
ALIAS
|
Private object. Only the user who
created it, can access it.
|
Global object. Accessible by
anyone.
|
Used in local environment to hide
the high level qualifier.
|
Used in distributed environment to
hide the location qualifier.
|
When the base table is dropped,
associated synonyms are automatically dropped.
|
When base table is dropped,
aliases are not dropped.
|
SYSADM authority is not needed.
|
To create an alias, we need SYSADM
authority or CREATEALIAS privilege.
|
DB2 Object-View
Views
provide an alternative way of looking at the data in one or more tables.
A
view is a named specification of a result table. For retrieval, all views can
be used
like
base tables. Maximum 15 base tables can be used in a view.
Advantages of view
1.Data
security: Views allows to set-up
different security levels for the same base
Table.
Sensitive columns can be secured from the unauthorized Ids.
2.It
can be used to present additional information like derived columns.
3.It
can be used to hide complex queries. Developer can create a view that results
from
a complex join on multiple tables. But the user can simply query on this view
as
if it is a separate base table, without knowing the complexity behind the
building.
4.It
can be used for domain support. Domain identifies a valid range of values that
a
column
can contain. This is achieved in VIEW using WITH CHECK OPTION.
Read Only Views
Views
on which INSERT, UPDATE and DELETE operations cannot be carried
out, are called non-updateable views
or read only views.
Characteristics of Updateable views
1.It
should be derived from one base table and should not contain derived columns.
2.Views
should not contain GROUP BY, HAVING and DISTINCT clauses at the
outermost
level.
3.Views
should not be defined over read-only views.
4.View
should include all the NOT NULL columns of the base table.
DB2 Object- Catalog
It
is a data dictionary for DB2, supporting and maintaining data about the
DB2 environment. It consists of 54
tables in the system database DSNDB06.
The
data in DB2, about the data in the tables are updated when RUNSTATS utility
runs.
The information in the DB2 catalog table can be accessed using SQL.
DB2 Object- Directory
This
is second data dictionary of DB2 and used for storing detailed, technical
information
about the aspects of DB2’s operation. It is for DB2 internal use only.
It
cannot be accessed using SQL statements. It is stored in the system database
DSNDB01.
DB2 Object- Active and Archive Logs
DB2
records all the data changes and significant events in active logs as and
when they occur. In case of failure,
it uses this data to recover the lost information.
When active log is full, DB2 copies
the content of active log to a DASD or magnetic tape data set called archive
log.
DB2 Object- Boot Strap Dataset
It
stores the inventory of all-active and all-archive logs. During installation of
DB2, two BSDS are created and kept
in different volumes. BSDS datasets are VSAM KSDS.
DB2 Object-Buffer Pools
Buffer
Pools are virtual storage area, which DB2 uses for its temporary
purpose. There are fifty 4K buffer
pools and ten 32K buffer pools in DB2.
DB2
Default buffers are BP0, BP1, BP2 and BP32.
Program Preparation
SQL
(Structured Query Language) is the language that is used to
Retrieve
/ update / delete DB2 data. SQL statements are embedded into COBOL
Program
within the scope of ‘EXEC SQL and
END-EXEC.’
DB2
Program is first feed to DB2 Pre compiler that extracts the DB2
statements into DBRM and replace the
source program DB2 statements with COBOL CALL statements. This modified source
is passed to COBOL compiler and then link editor to generate load module.
During pre compilation, time stamp token is placed on modified source and DBRM.
On
the other side, DBRM undergoes binding process and DB2 Optimizes chooses the
best access path for the extracted SQL statements and store it in PLAN.
Plan and Package
Binding
process can happen in two stages, BIND PACKAGE and BIND PLAN.
One
DBRM is created for one program. If the main program calls n number of
Sub-programs,
then there will be n DBRMS in addition to main program DBRM.
These
n + 1 DBRM can be directly feed to BIND PLAN to produce a single
PLAN or create m number of
intermediate packages each formed by one or more DBRM. This m numbers of
packages are then feed to BIND PLAN step that produces PLAN. Package is not
executable but Plan is executable. To run a DB2 Program, Plan
is mandatory.
Advantages
of Package:
1.When there is a change in sub
program, it is enough to recompile
the
subprogram and create the PACKAGE for that sub program. There is no need
for
BIND PLAN.
2.Lck options and various bind
options can be controlled at sub-program
level.
3.It avoids the cost of large bind.
4.It reduces Rebound time.
5.Versioning: Same package can be
bounded with multiple programs with
different
collection IDs.
Execution of DB2 Program
DB2
Program is executed using terminal monitor program IKJEFT01.
It can be executed using IKJEFT1A or
IKJEFT1B also. They are alternate entry points of IKJEFT01.DB2 region name,
program to run, DB2 plan and PARM (if needed) are provided in the SYSTSIN card
of IKJEFT01.
//STEP
EXEC PGM=IKJEFT01
//SYSPRNT
DD SYSOUT=*
//SYSTSIN
DD *
DSN SYSTEM(DST2) == > In
DB2 test region DST2
RUN PROGRAM(pgmname) PLAN(planname) == >
Execute the program
LIB(‘loadlibrary) == > whose load module is here!
END
/*
Program Preparation in Detail
* Time Stamp token is passed from
pre-compilation stage to Load module and Plan.
When
first time the plan is accessed, the time stamp of plan is verified against the
time stamp of load module. If they match, then table will be accessed using the
access path in PLAN and proceed further. If they don’t match, then the program
abnormally ends with SQLCODE of –818. It means the load module and plan are out
of sync. It usually occurs if we modify the program and pre-compile, compile
and link edit but did not bind the plan with latest DBRM produced in
pre-compilation stage.
There is a modification in COBOL
part. There is no change in any of the DB2 statements in the program. Is
binding necessary?
Yes. It is necessary. For COBOL changes, we have to compile and link
edit.
For compilation we need pre-compiled
source. So new time stamp is transferred to your load module from the
pre-compilation stage. If we don’t BIND it, timestamp mismatch is obvious and
the program will abnormally end.
Can this
time stamp check be avoided?
If the program pre-compiled with LEVEL
option, then there won’t be time stamp verification. But this option is not
generally recommended.
Bind Card
BIND PACKAGE
sub command is used to bind a DBRM to a package and BIND PLAN is used to bind a
DBRM to a plan or group of packages to a PLAN.
Sample Bind Card:
//BIND EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN
BIND PLAN(EMPPLAN)
MEMBER(EMPDBRM)
VALIDATE(BIND)
ISOLATION(CS)
RELEASE(COMMIT)
OWNER(SMSXL86)
LIB(‘SMSXL86.DB2.DBRMLIB’)
/*
Bind Parameter – MEMBER and LIBRARY
DBRM to be
bound is given in MEMBER and the partitioned dataset containing the DBRM is
given in LIB. During BIND Package, if an existing package is used to create the
new package then, COPY should be mentioned instead of MEMBER and LIBRARY.
COPY(collection-id.package-id)
COPYVER(version-id) – version of the
copy to be used. Default is empty if omitted.
Bind Parameter – PKLIST
PKLIST is a
BIND parameter of BIND PLAN. Packages to be connected with PLAN are named
here. PKLIST (COLL1.*) bound all the
packages with collection ID COLL1 to the PLAN.
Package Naming – Collection-ID and
Versioning
Packages have
three qualifiers. Location ID is used in distributed environment. Collection ID
is used for grouping of packages.
Naming Syntax: Location-id.
Collection-id. Package-name
VERSION. With packages, the pre-compiler
option VERSION can be used to bind multiple versions of the programs into
package.
PROG-------- > Pre-compile -----------à BIND Package --------àCOLL1.PROG.TEST
With
VERSION(TEST)
-------- > Pre-compile
-----------à BIND Package --------àCOLL1.PROG.PROD
With
VERSION(PROD)
Bind Parameter – ACTION
Package or
Plan can be an addition or replacement. Default is replacement.
REPLACE will replace an existing package
with the same name, location and collection. REPLVER(version-id) will replace a
specific version of an existing package.
ADD – Add the new package to SYSIBM.SYSPACKAGE.
Syntax: ACTION(ADD|REPLACE)
Bind
Parameter – Isolation Level
The isolation level parameter of the
BIND command describes to what extent a program bound to this package can be
isolated from the effects of other programs running. This determines the
duration of the page lock.
CURSOR
STABILITY (CS)– As the cursor moves from the record in one page to the record
in next page, the lock over the first page is released (provided the record is
not updated). It avoids concurrent updates or deletion of row that is currently
processing. It provides WRITE integrity.
REPEATABLE
READ (RR) – All the locks acquired are retained until commit point. Prefer this
option when your application has to retrieve the same rows several times and
cannot tolerate different data each time. It provides READ and WRITE integrity.
UNCOMMITTED
READ (UR) – It is also known as DIRTY READ. It can be applied only for
retrieval SQL. There are no locks during READ and so it may read the data that
is not committed. Highly dangerous and use it when concurrency is your only
requirement. It finds its great use in statistical calculation of the large
table and data-warehousing environment.
Bind Parameter – ACQUIRE and RELEASE
ACQUIRE(USE)
and RELEASE(COMMIT) – DB2 imposes table or table space lock when it executes an
SQL statement that references a table in the table space and it release the
acquired lock on COMMIT or ROLLBACK. This is the default option and provides
greater concurrency.
ACQUIRE(USE)
and RELEASE(DEALLOCATE) – Locks table and table spaces on use and releases when
the plan terminates.
ACQUIRE(ALLOCATE)
and RELEASE(DEALLOCATE) – When DB2 allocates the program thread, it imposes
lock over all the tables and table spaces used by the program. This option
avoids deadlocks by locking your source at the beginning but it reduces
concurrency.
ACQUIRE(ALLOCATE) and
RELEASE(COMMIT) is NOT ALLOWED. This increases the frequency of deadlocks.
Bind Parameter – SQLERROR
It says
whether to create the package in case of SQL errors.
SQLERROR (NOPACKAGE) – Default - If
there is any SQL error, package will not be created.
SQLERROR (CONTINUE) – Create the Package
even if SQL errors are encountered.
Bind Parameter – VALIDATE
It controls
the handling of ‘object not found’ and ‘not authorized’ errors.
Default is VALIDATE (RUN) – If all
objects are found and all privileges are held, then don’t check once again at
execution time. If there is any ‘privilege issue’ or ‘object not found’ error,
then produce WARNING messages, bind the package and check authorization and
existence at execution time.
VALIDATE (BIND) – If there is any
privilege or object-not-found issue, then produce ERROR message and create the
package only if SQLERROR(CONTINUE) is coded in bind card. The statement in error
will not be executable.
BIND Parameter – EXPLAIN
EXPLAIN(YES)
loads the access path selected by the optimizer in PLAN_TABLE. EXPLAIN(NO) is
default and it wont load any such details in PLAN_TABLE.
BIND Parameter – QUALIFIER
Qualifiers
are added at the bind time to the objects used in the program.
It is suggested NOT to use any
qualifiers in the program and use this parameter during BIND so that the
program can be easily promoted from one region to next region without any
change in program.
REBINDING
When SQL
statements are not changed but a new index is added or RUNSTATS is run, then it
is advisable to do a REBIND for the best possible access path. REBIND PLAN is cheaper than BIND with ACTION
(REPLACE).
SYSTSIN CARD should be
REBIND PLAN(PLAN-NAME)
VALIDATE(BIND)
…..
Host Variables
DB2 is separate subsystem. As DB2 data are stored in external address
space, you cannot directly modify or read from your program. During read, the
DB2 values are retrieved into your working storage variables. During update,
the DB2 columns are updated using your working storage variables. These working
storage variables used for retrieving or updating DB2 data should be of same
size of DB2 columns. They are called as host variables as they are defined in
the host language (COBOL). Host variables are prefixed with colon ( : ) in the
embedded SQL.
DCLGEN (Declaration Generator)
DCLGEN is a DB2 utility. If we
provide table details, it will generate DB2 Structure and host variable
structure of the table. It can also generate NULL indicators for the NULL
columns. This output is stored in a PDS member. These members are included in
the program using the INCLUDE . INCLUDE is a pre-compiler statement and so it
should be coded within the scope of EXEC SQL and END-EXEC.
DCL generated
host variable names are same as DB2 column names.
As underscore is not a valid character
for variable names in COBOL, it is replaced by hyphen in host variable
generation of DCLGEN. Prefix and suffix can be added to all the variables while
creating the DCLGEN copybook.
Host
variables can be declared in working storage section. But they cannot be stored
in copybooks as other file layouts.
INCLUDE is
expanded during pre-compilation time but COPY is expanded during compilation
and so declaration of host variable in copybook will produce errors during
pre-compilation.
DECLARE
TABLE, that is table structure of DCLGEN is NOT really needed for
pre-compilation. But if it is used, then any misspelled table name, column
names are trapped in pre-compilation stage itself.
DB2
need not be up during pre-compilation. As pre-compiler just extracts the DB2
statements and produce DBRM, it does not need DB2 connection. But DB2 region
should be up for binding as the optimizer builds access path in this stage from
catalog information.
DCLGEN Panel (Option 2 of DB2I)
_______________________________________________________________________________
| |
| DSNEDP01 DCLGEN SSID: DSN |
| ===>
|
| |
| Enter table name for which
declarations are required: |
| 1 SOURCE TABLE NAME ===> DSN8410.EMPTABLE
(Unqualified table name) |
|
2 TABLE OWNER ===>
(Optional) |
| 3 AT LOCATION ..... ===> (Optional) |
|
|
| Enter destination data
set: (Can be sequential or
partitioned) |
| 4 DATA SET NAME ... ===> ‘SMSXL86.DB2.INCLUDE(EMPTABLE)’ |
| 5 DATA SET PASSWORD ===> (If password protected) |
|
|
| Enter options as desired:
|
| 6 ACTION .......... ===> ADD
(ADD new or REPLACE old declaration) |
| 7 COLUMN LABEL .... ===> NO
(Enter YES for column label) |
| 8 STRUCTURE NAME .. ===> (Optional) |
| 9 FIELD NAME PREFIX ===> (Optional) |
| 10 DELIMIT DBCS .... ===> YES
(Enter YES to delimit DBCS identifiers) |
| 11 COLUMN SUFFIX ... ===> NO
(Enter YES to append column name) |
| 12 INDICATOR VARS .. ===> NO
(Enter YES for indicator variables) |
|
|
|
|
| PRESS: ENTER to process END to exit HELP for more information |
|
|
|__________________________________________________________________________________|
DSNE905I EXECUTION COMPLETE, MEMBER EMPTABLE
ADDED
DB2 Data Types and equivalent host
variables.
DB2 Column
|
Bytes
|
COBOL PIC Clause
|
Bytes
|
SMALLINT
|
2
|
PIC S9(04) COMP
|
2
|
INTEGER
|
4
|
PIC S9(09) COMP
|
4
|
DECIMAL (p, q)
(P should be less than 32)
|
Int
(P/2)
|
PIC S9(p-q)V9(q)
|
Integer
((P+Q)/2 +1)
|
DATE
|
4
|
PIC X(10)
|
8
|
TIME
|
3
|
PIC X(08)
|
6
|
TIMESTAMP
|
10
|
PIC X(26)
yyyy-mm-dd-hh.mm.ss.nnnnnn
|
26
|
CHAR(n) (n=1 to 254)
|
N
|
PIC X(n)
|
N
|
VARCHAR(n) (n=0-4046)
|
N
|
01 WS-COLUMN.
49 WS-COLUMN-LENGTH PIC S9(04) COMP
49 WS-COLUMN –TEXT PIC X(n)
|
N+2
|
Limit of Date: Jan 1st 1
ad – Dec 31st 9999 AD
Limit of Time: 000000 – 240000
Limit of Time Stamp:
00010101000000000000 – 00001231240000000000
SQLCA
SQLCA is SQL
Communication area. It is a DB2 copybook that should be included in all the DB2
programs. The fields declared in the copybook are updated for every DB2 query.
The Length of SQLCA is 136. The most important field in this copybook is
SQLCODE. The success or failure of DB2 query can be checked in this field.
Value of 0 in SQLCODE indicates the
successful operation.
Positive value in SQLCODE indicates the
completed operation with some exception
Negative value in SQLCODE indicates the
unsuccessful operation.
So it is
common programming practice that the SQLCODE of every query must be verified
for valid values after the execution of the query. If the SQLCODE value is not
acceptable, then the control is transferred to ABEND routine that would end the
program with proper error messages.
SQLERRD (3)
contains the number of rows affected by a DB2 INSERT/DELETE operation. If the
operation is DELETE, this contains number of rows deleted after the execution
of the query.
WHENEVER statement
WHENEVER is a
error-trapping statement that directs the processing to continue or branch to
an error handling routine based on the SQLCODE returned for the statement. When
it processed, it applies to all the subsequent SQL statements issued by the
application program.
EXEC SQL
WHENEVER
condition action
END-EXEC.
Conditions can be NOT FOUND, SQLWARNING
or SQLERROR.
Action can be either CONTINUE or GO TO.
NOT FOUND is TRUE if SQLCODE is 100.
SQLWARNING is TRUE if SQLCODE is greater
than zero but not equal to 100
SQLERROR is TRUE if SQLCODE is less than
zero.
It is almost
always safe to code specific SQLCODE checks after each SQL statement and
process accordingly. Avoid using WHENEVER.
DSNTIAR – SQLCA Formatter
DSNTIAR is an
IBM supplied program, which would display the error messages in a formatted
way. Most of the installations call this program in their error routine.
CALL
‘DSNTIAR’ USING SQLCA, ERROR-MESSAGE, ERROR-TEXT-LEN.
01 ERROR-MESSAGE.
05 ERROR-LEN PIC S9(04) COMP VALUE +1320.
05 ERROR-TEXT PIC X(132) OCCURS 10 TIMES.
77 ERROR-TEXT-LEN PIC S9(09) COMP
VALUE +72.
Components of DB2
DB2
internal structure is very complex and contains large number of components. But
from a high-level point of view, DB2 can be considered to have four major
components.
System Services component: Supports
system operation, operator communication, logging and similar function.
Locking services component: Provides
the necessary controls for managing concurrent access to data.
Database services component:
Supports the definition, retrieval and update of user and system data.
Distributed data facility component:
Provides DB2’s distributed database support.
Before get into SQL, let us briefly
see the sub- components of Database services component.
Database services component
It
has five sub-components in it. We have already discussed the functions of two
components – Pre-compiler and Optimizer (Bind)
Runtime supervisor:
It
is resident in the main memory when the application program is executing. Its
job is to oversee that execution. When the program requests some database operation
to be performed, control first goes to the runtime supervisor, which uses the
control information in the application plan to request the appropriate on the
part of the Data Manager.
Data Manager (DM):
It
performs all the normal access method functions – search, retrieval, update
etc. It invokes other system components in order to perform detail functions
such as locking, logging etc.
Buffer Manager (BM):
It
is responsible for transferring the data between external storage and virtual
memory. It uses sophisticated techniques such as ‘read-ahead-buffering’ and
‘lock-aside-buffering’ to get the best performance out of the buffer pools
under its control to minimize the amount of physical i/o actually performed.
Catalog and Directory are also part
of Database services component only.
SQL
SQL is fourth
generation language. The definition of fourth language is ‘Tell the need.
System will get it done for you ‘. There is no need to instruct the method to
get it.
For example,
I want list of employees working currently in Malaysia. If the data is in file,
then I have to declare, define, and Open the file. Then I should read all the
records into my working storage one by one and if their location is Malaysia, I
display them. Finally I close the file.
If the data
is in DB2, I would simply write a query (SELECT * FROM table where
location=’Malaysia’’) and DB2 optimizer do everything for me in identifying the
best access path. No worries of DECLARE, DEFINE, OPEN, READ and OMIT stuffs.
That’s the power of fourth generation language and that makes the programmer
life easy!!
SQL has three kinds of statements.
DDL–Data Definition Language
Statements CREATE ALTER DROP
DML–Data Manipulation Language
Statements SELECT INSERT UPDATE DELETE
DCL–Data Control Language
Statements GRANT REVOKE
DDL-CREATE
This
statement is used to create DB2 objects.
General Syntax CREATE
object object-name parameters
Table creation Sample CREATE TABLE table-name
(Column definitions,
Primary Key
definition,
Foreign Key
definition,
Alternate Key
definition,
LIKE
table-name /View-name,
IN
DATABASE-NAME.TABLESPACE-NAME)
DDL-Column Definition
Define all
the columns using the syntax:
Column-name Data-type Length (NULL/NOT
NULL/ NOT NULL WITH DEFAULT)
Data-types are already explained in
DCLGEN section.
Ø
Column-name can be of maximum
length 30. It should start with an alphabet and it can contain numbers and
underscore.
Ø
NULL means UNKNOWN, if the
value is not supplied during an insertion of a row, then NULL will be inserted
into this column.
Ø
NOT NULL means, value for this
column should be mentioned when inserting a row in the table. NOT NULL with
DEFAULT means, if the value for this column is not supplied during an
insertion, then based on type of the column default values will be moved into
the table. Default values are listed in the below table.
Data- Type
|
Default Value
|
CHAR
|
Spaces
|
VARCHAR
|
Empty String (String of length 0)
|
DATE, TIME, TIMESTAMP
|
Current (DATE/TIME/TIMESTAMP)
|
INTEGER SMALLINT DECIMAL
|
Zero
|
No comments:
Post a Comment