DSNTEP2 & DSNTEP4...

DSNTEP2-:It is a PL\1 source program which provides the functionality
of executing the sql statements from jcl in mainframe.
the sql can be executed when run under the plan DSNTEP2

DSNTEP2 DOES NOT ALLOW MUTILPLE FETCHING OF ROWS.

DSNTEP4 -: All the functions are same as of dsntep2.
it also allows the multiple fetching.



MULTIPLE FETCH -: Specify the number of rows that the
SQL is going to fetch at one select.
The default fetch amount for DSNTEP4 is 100 rows, but you can
specify from 1 to 32676 rows.

TABLESPACES IN DB2.....A Review

DB2 stores data in table spaces composed of one or many VSAM datasets.

The six type of Table Spaces that DB2 supports are-:

• SIMPLE
• SEGMENTED
• PARTITIONED
• LARGE OBJECTS
• UNIVERSAL
• XML 

Simple Table spaces

1.     Fairly Outdated 

2.     One or many tables in single tablespace

3.     Rows from different tables usually interleaves on same page so locking usually locks rows from other tables also.

4.     Creation not supported in DB2 9.1 but existing tables are supported

Segmented Table Spaces

1.     Organizes pages of the TS into pages

2.     Each segment contains rows of only one table

3.     Size is limited to 64 GB

Number of pages	SEGSIZE
28 or less	4 to 28
28 to 128	32
128 or more	64

4.     Problem of locking rows of other table (Simple Tablespace) is resolved

5.     If a mass delete is performed segment pages are available for immediate reuse.(no need to run reorg)

6.     Mass deletes are much faster

7.     COPY utility doesn’t have to copy empty pages

8.     Eg-:

Create tablespace DEVTS in DEVDB

Using stogroup DSG910

          Priqty 1000

Secqty 50

Segsize 4

Locksize table

Bufferpool bp32

Close no

Ccsid ebcidic

9.  Once set, the SEGSIZE parameter cannot be altered.

DB2 VERSUS ORACLE

Oracle’s Larry Ellison threw down the gauntlet recently when he made claims about the superiority of Oracle over DB2. IBM has, of course, responded. Below are the Oracle claims and the IBM rebuttals. Enjoy!

ORACLE: "We blew the doors off of IBM. We crushed them." [Referring to TPC-C benchmark results] In a machine that took up less than 10% the floor space, of IBM's record setting computer. We ran faster, we ran a lot faster: using a tiny fraction of the floor space, a tiny fraction of the power, cost less."

IBM: Until late last year, DB2 enjoyed a massive 49% lead over Oracle. With Oracle's most recent result, they have taken the lead by 25% (and by the way, they used more than six times as many CPU cores to do it). We are confident that DB2 will retain its lead this year. Also, remember that DB2 has dominated TPC-C performance leadership over the past seven years, with almost twice as many days of leadership as Oracle Database.

Check the link below and clear your queries-:

http://mainframeupdate.blogspot.com/2010/02/oracle-versus-ibm-and-db2.html

DB2 ON Z/OS

z/OS is the flagship IBM mainframe operating system. z/OS is the robust zSeries

mainframe operating system designed to meet the demanding QoS requirements

for On Demand Business. Here are some highlights:

1) It provides a highly secure, scalable, high-performance base for on demand

applications.

2) It can simplify IT infrastructure by allowing the integration of applications in a

single z/OS image.

Evolution of DB2

In the early 1950s, the disk drive was developed in IBM San Jose, California. In

the late 1960s, as large amounts of data were stored on disk, the research focus

turned to data management systems.

                   At that time, network and hierarchical data management systems were in use.

CODASYL and Information Management System (IMS) were state of the art

technologies for automated banking, accounting, and order processing.

Respected IBM Fellow Dr. E.F. (Ted) Codd worked on a new kind of mathematical

notation, but it took a while for it to receive the attention it deserved. Programs

that would have been five pages long if represented in CODASYL were written by

Codd in single lines. These would be queries such as “Find the employees who

earn more than their managers.”

                From Codd’s paper, a team in IBM research started building System R as part of

a program of research in the relational model of data. The objective of this

system was to handle ad hoc queries (one time) and “canned” queries (executed

many times). For the relational model to be accepted, it had to be proven by an

industrial-strength implementation. That was the goal of the System R project

begun at IBM San Jose Research in 1973.

               Codd believed that computer users should be able to work at a more natural

language level. They should not be concerned about the details of where or how

the data was stored.

At the IBM laboratory, other concepts emerged:

When querying a set of rows, lock the set.

Instead of authorizing columns of a table, make it a view of the columns and

authorize that.

Transactions, like logical units, should be all or nothing.

The research went on until Db2 was the best database to work on Mainframes making billions of transactions per second with ease.