Oracle and DB2

■ Florida State
University
■ Administrative
Information Systems
■ University Data
Services
■ Penny Bowman
■ February, 2002

10/18/08 1
 Differences Between
Oracle and DB2
■ Since FSU/AIS is now using both DB2 databases
and Oracle databases in the production solutions,
you may be assigned to write SQL code that must
access them both
■ Be aware that there are differences between the
two databases that may affect your code or the
ability to port what you have written
■ This presentation is not a comprehensive list of
differences, only a brief comparison and contrast
■ For more in depth knowledge, you should study
the two systems and consult with DBA
10/18/08 2
 Differences Between
Oracle and DB2
■ Platforms
■ Catalog / Data Dictionary
■ Tools
■ Datatypes
■ Functions
■ Combining Data from Multiple Tables
■ Explain Plan Tables
■ Other Differences

10/18/08 3
 DB2 Platforms in AIS
■ NWRDC 390 mainframe
– DB2 Version 6
– Test Region – DB2T
– Production Region – DSN

■ UDB for Data Warehouse – local server

– UDB Version 7.2
– New Client tools
– System will soon be ready

10/18/08 4
 Oracle Platforms in AIS
■ Development Server - UNIX
– [Link] ([Link])
– Oracle [Link].0
■ Staging Server - NT
– Athena ([Link])
– Oracle [Link].0
■ Production – UNIX
– [Link]
– Oracle [Link].0
■ COLD Server – NT
– [Link] ([Link])
– Oracle [Link].0

10/18/08 5
 DB2 Catalog
■ Contains information about all the objects in DB2
■ SYSIBM.SYSDUMMY1(one row system table)
■ WorkBench refreshed nightly from the DB2
Catalog
■ SELECT access to PUBLIC has been granted for
the DB2 catalog tables on NWRDC. By default,
only SYSADM or SYSCTRL authority
automatically has the privilege of retrieving data
from catalog tables.

10/18/08 6
 DB2 Catalog
■ No easy predefined views provided with DB2.
This is the select statement to query the catalog to
list the tables in your group that you can access:
SELECT DISTINCT
TCREATOR,TTNAME FROM
[Link]
WHERE GRANTEE='FSDBA' AND
GRANTEETYPE= ' ';

10/18/08 7
 Oracle Data Dictionary
■ Contains information about objects in Oracle
■ Dual (one row system table)
■ DESCRIBE command

■ This is the select statement to query the catalog to

list the tables you can access:
SELECT OWNER, TABLE_NAME FROM
ALL_TABLES ORDER BY 2;

10/18/08 8
 Oracle Data Dictionary
■ Prefix for Views for DBAs: DBA_
■ Prefix for Views for Non DBA users :
USER_ and ALL_
■ Views for All Users:
Session_Privs and Session_Roles
■ Views for DBAs:
Privilege related views
V$ Performance Views

10/18/08 9
 Data Dictionary Views for Users
Use the DESCribe command to
examine the table structure of
each View before you SELECT
from the View. This is a ■ USER_INDEXES
sampling of the available ■ USER_IND_COLUMNS
views: ■ USER_OBJECTS
■ USER_RESOURCE_LIMITS
■ USER_ROLE_PRIVS
■ USER_CATALOG ■ USER_TABLES
■ USER_COL_COMMENTS ■ USER_TABLESPACES
■ USER_COL_PRIVS ■ USER_TAB_COLUMNS
■ USER_CONSTRAINTS
■ USER_TAB_COMMENTS
■ USER_CONS_COLUMNS
■ USER_USERS
■ USER_VIEWS
■ USER_DEPENDENCIES

10/18/08 10
 DB2 Mainframe Tools on
NWRDC
■ SPUFI - to develop and test SQL
■ WorkBench – primarily used to:
– analyze DB2 database objects
– capture DDL to create or alter tables
– review indexes & referential integrity between tables
■ R-Tools – used to browse and edit tables, can specify column
values and dynamically join tables
■ Insight – primarily used to
– Monitor Active Threads
– Analyze Thread History – cost of DB2
– Explain SQL statements

10/18/08 11
 Oracle Client Tools
■ SQL Plus Worksheet (to develop and test SQL)
■ DBA Studio - multiple database tools in one
application. Used to administer:
– Instances, including startup, shutdown, and
initialization.
– Schemas, including tables, indexes, and Oracle8
objects.
– Security, including user accounts, roles, and privileges.
– Storage, including tablespaces, datafiles, and rollback
segments.

10/18/08 12
 Differences Between
Oracle and DB2
■ This section includes the data types and
functions for each database
■ First – the DB2 data types and functions are
discussed
■ Followed by the Oracle datatypes and
functions

10/18/08 13
 DB2 Datatypes
■ String Types: ■ String subtypes
■ CHARACTER(size) applicable to CHAR,
■ VARCHAR(size) (2 byte VARCHAR, and
overhead) CLOB:
■ GRAPHIC(size) – BIT
■ VARGRAPHIC(size) – SBCS
■ BLOB(n) – MIXED
■ CLOB(n) ■ ROWID
■ DBCLOB(n)

10/18/08 14
 DB2 Datatypes
Numeric Types:
■ SMALLINT (range of -32768 to +32767)
■ INTEGER or INT (range -2147483648 to
+2147483647)
■ REAL or FLOAT(n) Single precision floating-
point numbers. n must be in the range 1 through
21. If you omit n, the column has double
precision.

10/18/08 15
 DB2 Datatypes
Numeric Types Continued:
■ FLOAT, FLOAT(n), or DOUBLE PRECISION,
or DOUBLE (packed decimal numbers with
precision p and scale s.
■ The precision p, which is the total number of digits including the
digits following the decimal point, must be greater than 0 and less than
32.
■ The scale s, which is the number of digits in the fractional part of the
number, must be greater than or equal to 0 and less than or equal to
the precision. s can be omitted; its default is 0. If s is omitted, p can
also be omitted; its default is 5.
■ The maximum range is 1 - 10**31 to 10**31 - 1.

10/18/08 16
 DB2 Identity

■ Identity is a type of column in a table

■ DB2 automatically calculates the value on an INSERT or
UPDATE
■ A table can have only one identity column. numeric type
with a scale of zero An identity column is implicitly NOT
NULL.,
■ Identify column values increment if the initial value is
positive, or decrement if the initial value is negative. The
default is 1.

10/18/08 17
 DB2 DATE / Functions
DATE – YYYY-MM-DD ➨DAYOFMONTH

TIME – [Link] ➨DAYOFWEEK

TIMESTAMP – ➨DAYOFYEAR

0001-01-01-00.00.00.000000 to –DAYS

9999-12-31-24.00.00.000000 –HOUR

Default date format is  ➨JULIAN_DAY

YYYY-MM-DD –MICROSECOND
–MIDNIGHT_ SECONDS
–MINUTE
Date Functions
–MONTH
■CHAR
➨QUARTER
■DATE
–SECOND
■DAY

10/18/08 18
 DB2 Version 6 Functions
➨ ABS or ABSVAL ➨ CEIL or CEILING
➨ ACOS ■ CHAR
➨ ASIN ➨ CLOB
➨ ATAN ■ COALESCE
➨ ATANH ➨ CONCAT
➨ ATAN2 ➨ COS
■ AVG ➨ COSH
➨ BLOB

10/18/08 19
 DB2 Version 6 Functions
■ COUNT ■ DECIMAL or DEC
➨ COUNT_BIG ➨ DEGREES
■ DATE ■ DIGITS
■ DAY ➨ DOUBLE or
➨ DAYOFMONTH DOUBLE-
➨ DAYOFWEEK PRECISIION
➨ DAYOFYEAR ➨ EXP
■ DAYS ■ FLOAT
➨ DBCLOB ➨ FLOOR
10/18/08 20
 DB2 Version 6 Functions
➨ GRAPHIC ➨ LEFT
■ HEX ■ LENGTH
■ HOUR ➨ LN
➨ IFNULL ➨ LOCATE
➨ INSERT ➨ LOG
■ INTEGER or INT ➨ LOG10
➨ JULIAN_DAY ➨ LTRIM
➨ LCASE or LOWER ■ MAX

10/18/08 21
 DB2 Version 6 Functions
■ MICROSECOND ➨ POSSTR
■ MIDNIGHT_ ➨ POWER
SECONDS ➨ QUARTER
■ MIN ➨ RADIANS
■ MINUTE ➨ RAISE_ERROR
➨ MOD ➨ RAND
■ MONTH ➨ REAL
■ NULLIF ➨ REPEAT

10/18/08 22
 DB2 Version 6 Functions
➨ REPLACE ➨ SINH
➨ RIGHT ➨ SMALLINT
➨ ROUND ➨ SPACE
➨ ROWID ➨ SQRT
➨ RTRIM ➨ STDDEV
■ SECOND ■ STRIP
➨ SIGN ■ SUBSTR
➨ SIN ■ SUM

10/18/08 23
 DB2 Version 6 Functions
➨ TAN ➨ UCASE OR UPPER
➨ TANH ■ VALUE
■ TIME ➨ VARCHAR
■ TIMESTAMP ➨ VARCHAR_
➨ TIMESTAMP_ FORMAT
FORMAT ■ VARGRAPHIC
➨ TRANSLATE ➨ VARIANCE OR VAR
➨ TRUNCATE or ➨ WEEK
TRUNC ■ YEAR
10/18/08 24
 DB2 Date Type
Date/time values in DB2 are stored in a special internal format. When you
load or retrieve data, DB2 can convert to or from any of the valid
formats.
■ DATE
– A date is a three-part value representing a year, month,
and day in the range 0001-01-01 to 9999-12-31
YYYYMMDD

■ YYYY corresponds to a year value from 0000 to 9999

■ MM corresponds to a month value from 1 to 12
■ DD corresponds to a day value from 1 to 31

10/18/08 25
 DB2 Time Datatype
■ TIME
– A time is a three-part value representing a time of day
in hours, minutes, and seconds, in the range 00.00.00 to
24.00.00.
– HHMMSS

■ HH corresponds to an hour value from 00 to 24

■ MM corresponds to a minute value from 0 to 59
■ SS corresponds to a second value from 0 to 59
–

10/18/08 26
 DB2 Timestamp Datatype

■ TIMESTAMP
– A timestamp is a seven-part value representing a date and time by
year, month, day, hour, minute, second, and microsecond, in the
range 0001-01-01-00.00.00.000000 to 9999-12-31-
24.00.00.000000.
– YYYYMMDDHHMMSSMICROS
■ YYYYMMDD corresponds to YEAR, MONTH and DAY
■ HHMMSS corresponds to HOUR, MINUTE and SECOND
■ MICROS corresponds to microseconds

10/18/08 27
 DB2 Date, Time, Timestamp
Data Types
■ The combination of using DATE and TIME requires 7 bytes, and
using them in combination can save space over using TIMESTAMP.
To calculate a duration using DATE and TIME columns, two
subtractions must occur; one for the DATE column and one for the
TIME column.

■ Use the DB2 TIMESTAMP data type when the date and time are
always needed together, but rarely needed alone. TIMESTAMP
provides greater time accuracy, down to the microsecond level,
requires 10 bytes. Use this when greater precision is important.
Subtracting one TIMESTAMP field from another results in a
TIMESTAMP duration.

10/18/08 28
 DB2 Date / Time Functions
■ CHAR ■ MICROSECOND
■ DATE ■ MIDNIGHT_
■ DAY SECONDS
➨ DAYOFMONTH ■ MINUTE
➨ DAYOFWEEK ■ MONTH
➨ DAYOFYEAR ➨ QUARTER
■ DAYS ■ SECOND
■ HOUR
➨ JULIAN_DAY

10/18/08 29
 DB2 Version 6 Functions
■ TIME
■ TIMESTAMP
➨ TIMESTAMP_
FORMAT
➨ WEEK
■ YEAR

10/18/08 30
 DB2 CHAR Function to Format
Date and Time

■ The CHAR function returns a character representation of a

date, a time, a timestamp or a decimal number.

■ CHAR function allows you to easily convert date and time

columns from the system format to one of several
international standard formats.

CHAR( field, {ISO|USA|EUR|JIS} )

10/18/08 31
 DB2 CHAR Function to Format
Date and Time
■ IF field is a date - The result is a date character string
in the specified format (ISO is the
default,USA,EUR,JIS)
■ IF field is a time -The result is a time character string
in the specified format (ISO is the
default,USA,EUR,JIS)
■ IF field is a timestamp - The result is a timestamp
character string, no format can be specified.
■ IF field is a decimal number - The result is a decimal
character string (including sign), no format can be
specified.

10/18/08 32
 DB2 CHAR Function Examples
SELECT STUDENT_ID,
CHAR(BIRTHDATE,ISO) AS ISODATE,
CHAR(BIRTHDATE,USA) AS USADATE,
CHAR(BIRTHDATE,EUR) AS EURDATE,
CHAR(BIRTHDATE,JIS) AS JISDATE
FROM ...

■ STUDENT_ID ISODATE USADATE EURDATE JISDATE

■ 001505664 1977-06-12 06/12/1977 12.06.1977 1977-06-12

10/18/08 33
 DB2 Current Date / Time
Functions
■ These functions return current information from DB2:
■ CURRENT DATE
■ CURRENT TIME
■ CURRENT TIMESTAMP
■ If you want to only manipulate the current date or time,
and you are not selecting other columns from a table, you
may issue a SELECT statement using the system provided
one row table:
– SELECT CURRENT DATE, CURRENT TIME
FROM SYSIBM.SYSDUMMY1;
10/18/08 34
 DB2 CURRENT TIME
Functions
SELECT STUDENT_ID,
CHAR(CURRENT_TIME,ISO) AS ISOTIME,
CHAR(CURRENT_TIME,USA) AS USATIME,
CHAR(CURRENT_TIME,EUR) AS EURTIME,
CHAR(CURRENT_TIME,JIS) AS JISTIME
FROM FSDBA.STUDENT_MASTER
WHERE STUDENT_ID = '001505664’ ;
---------+---------+---------+---------+---------+

STUDENT_ID ISOTIME USATIME EURTIME JISTIME

---------+---------+---------+---------+---------+

001505664 09.53.55 09:53 AM 09.53.55 09:53:55

10/18/08 35
 DB2 CURRENT TIMESTAMP
Functions
SELECT STUDENT_ID,
CHAR(CURRENT_TIMESTAMP) AS CHARTIMESTAMP,
CHAR(245.54 - 3448.98) AS CHARDECIMAL
FROM
FSDBA.STUDENT_MASTER
WHERE
STUDENT_ID = '001505664' ;
---------+---------+---------+---------+---------+--

STUDENT_ID CHARTIMESTAMP CHARDECIMAL

---------+---------+---------+---------+---------+--

001505664 1998-02-03-09.58.38.904056 -03203.44

10/18/08 36
 DB2 DATE Function
– The DATE function returns an internal (binary)
representation of a date, a timestamp, an integer or a
character string. The result of subtracting one DATE
value from another, as in the expression HIREDATE -
BIRTHDATE, is a date duration.
– IF field is a date or more likely, a date calculation
– The result is the internal representation of that date.
– IF field is a timestamp
– The result is the internal representation of the date
portion of the timestamp.
10/18/08 37
 DB2 DATE Function
– IF field is an integer n
– The result is the internal representation of the date n-1
days from 01/01/0001.
– IF field is a character string representing a Julian date
('1998030')
– The result is the internal representation of the
equivalent date.

10/18/08 38
 DB2 DATE Function Examples
SELECT
DATE(CURRENT_DATE + 30 DAYS) AS DATEOFDATE,
DATE(CURRENT_TIMESTAMP) AS DATEOFTIMESTAMP,
DATE(730000) AS DATEOFINTEGER,
DATE('2000001') AS DATEOFCHAR
….

DATEOFDATE DATEOFTIMESTAMP DATEOFINTEGER DATEOFCHAR

1998-03-05 1998-02-03 1999-09-03 2000-01-01

10/18/08 39
 DB2 DAY
– The DAY function returns the day of the month (1-31)
representation of a date or timestamp, or the number of
days in a date or timestamp duration.
– IF field is a date - the result is the day of the month for
that date.
– IF field is a timestamp - the result is the day of the
month for that timestamp.
– IF field is a date duration (date calculation) - the result
is the number of days in the period (-99 to 99).
– IF field is a timestamp duration - the result is the
number of days in the period (-99 to 99).
10/18/08 40
 DB2 DAY Function Examples
SELECT CURRENT_DATE, DAY(BIRTHDATE) AS DAYOFDATE,

DAY(CURRENT_TIMESTAMP) AS DAYOFTIMESTAMP,
DAY(CURRENT_DATE - DATE('1997-12-31'))
AS DAYOFDATEDUR

FROM ….
CURRENT DATE DAYOFDATE DAYOFTIMESTAMP DAYOFDATEDUR

1998-02-03 12 3 3

Notice that, in the date duration calculation, only the DAYS portion
of the dates are taken into consideration. A duration calculation
for timestamps would be similar. Notice also that the example
includes a FUNCTION (DATE) within a FUNCTION (DAY).

10/18/08 41
 DB2 DAYOFMONTH( )
The DAYOFMONTH function returns the day part of its
argument.
The function is similar to the DAY function, except
DAYOFMONTH does not support a date, or timestamp
duration as an argument.
The argument must be a date, a timestamp, or a valid
character string, representation of a date or timestamp.
The result of the function is a large integer between 1 and
31, which represents the day part of the value. If the
argument can be null, the result can be null; if the
argument is null, the result is the null value.
10/18/08 42
 DB2 DAYOFMONTH( )
Example: Set the INTEGER variable DAYVAR to the
day of the month on which employee 140 in sample
table [Link] was hired.,
SELECT DAYOFMONTH(HIREDATE)
INTO :DAYVAR
FROM [Link]
WHERE EMPNO = '000140';

10/18/08 43
 DB2 DAYOFWEEK( )
The DAYOFWEEK function returns an integer in the
range of 1 to 7 that represents the day of the week.
The argument must be a date, a timestamp, or a valid
character string, representation of a date or timestamp.
The result of the function is a large integer. If the
argument can be null, the result can be null; if the
argument is null, the result is the null value.

10/18/08 44
 DB2 DAYOFWEEK( )
Example: Using sample table [Link], set the integer host
variable DAY_OF_WEEK to the day of the week that Christine
Haas (EMPNO = '000010') was hired (HIREDATE).

SELECT DAYOFWEEK(HIREDATE)
INTO :DAY_OF_WEEK
FROM [Link]
WHERE EMPNO = '000010';

The result is that DAY_OF_WEEK is set 6, which represents Friday.

10/18/08 45
 DB2 DAYOFWEEK( )
Example : The following query returns four values: 1, 2, 1, and 2.,

SELECT
DAYOFWEEK(CAST('10/11/1998',AS DATE)),
DAYOFWEEK(TIMESTAMP('10/12/1998','01.02')),
DAYOFWEEK(CAST(CAST('10/11/1998', AS DATE) AS
CHAR(20))),
DAYOFWEEK(CAST(TIMESTAMP('10/12/1998‘ ,'01.02') AS
CHAR(20))),
FROM SYSIBM.SYSDUMMY1;

10/18/08 46
 DB2 DAYOFYEAR( )
The DAYOFYEAR function returns an integer in the
range of 1 to 366 that represents the day of the year
where 1 is January 1.
The argument must be a date, a timestamp, or a valid
character string, representation of a date or timestamp.
The result of the function is a large integer. If the
argument can be null, the result can be null; if the
argument is null, the result is the null value.

10/18/08 47
 DB2 DAYOFYEAR( )
Example: Using sample table [Link], set the integer host
variable AVG_DAY_OF_YEAR to the average of the day of the
year on which employees were hired (HIREDATE):

SELECT AVG(DAYOFYEAR(HIREDATE)),

INTO :AVG_DAY_OF_YEAR

FROM [Link];

The result is that AVG_DAY_OF_YEAR is set to 202.

10/18/08 48
 DB2 DAYS
The DAYS function returns an integer representation of a
date, a timestamp or the character string equivalent of a
date.
– IF field is a date -the result is the integer number of
days plus 1 since 01/01/0001.
– IF field is a timestamp - the result is the integer number
of days plus 1 since 01/01/0001.
– IF field is a character string equivalent of a date - the
result is the integer number of days plus 1 since
01/01/0001.

10/18/08 49
 DB2 DAYS Function Examples
SELECT CURRENT_DATE,
DAYS(CURRENT_DATE) AS DAYSOFDATE,
DAYS(CURRENT_TIMESTAMP) AS DAYSOFTIMESTAMP,
DAYS('1997-12-31') AS DAYSOFCHAR
FROM ...
CURRENT DATE DAYSOFDATE DAYSOFTIMESTAMP DAYSOFCHAR

1998-02-03 729423 729423 729389

Notice that the DAYS FUNCTION returns the number of days since
01/01/0001 PLUS 1! This coincides with the operation of the DATE
FUNCTION which returns a date based on the number n -1 days since
01/01/0001. We will often use these two functions together when making
date calculations.
10/18/08 50
 DB2 DAY and DAYS Functions
Since the DAY function returns the day portion of a DB2 date or
timestamp and the DAYS function converts a DB2 date or timestamp
into an integer value representing one more than the number of days
since January 1, 0001,
You can calculate the 'day of the week' using these functions where the
returned values of 0 thru 6 represent Sunday through Saturday.

SELECT CURRENT DATE AS CUR_DATE,

DAY(CURRENT DATE) AS CUR_DAY,
DAYS(CURRENT DATE) AS CUR_DAYS,
DAYS(CURRENT DATE) - (DAYS(CURRENT DATE) / 7 ) * 7
AS DAY_OF_WEEK FROM FSDBA.ONE_ROW_TABLE;

CUR_DATE CUR_DAY CUR_DAYS DAY_OF_WEEK

10/18/08 1999-06-07 7 729912 1 51
 DB2 HOUR
The HOUR function returns an integer representing the hour part of
the field.
The argument must be a date, a timestamp, or a valid character string,
representation of a date or timestamp.
The result of the function is a large integer. If the argument can be
null, the result can be null; if the argument is null, the result is the
null value.
If the argument is a time, timestamp, or character string
representation of either, the result is the hour part of the value,
which is an integer between 0 and 24.
If the argument is a time duration or timestamp duration, the result is
the hour part of the value which is an integer between -99 and
+99. A nonzero result has the same sign as the argument.
10/18/08 52
 DB2 HOUR
Example: Assume that a table named CLASSES contains a row for
each scheduled class. Also assume that the class starting times are
in a TIME column named STARTTM.
Select those rows in CLASSES that represent classes that start after
the noon hour.

SELECT *
FROM CLASSES
WHERE HOUR(STARTTM) > 12;

10/18/08 53
 DB2 JULIAN_DAY
The JULIAN_DAY function returns an integer
representing the number of days from January 1, 4712
B.C. (the start of the Julian date calendar) to the date
specified in the argument.
The argument must be a date, a timestamp, or a valid
character string, representation of a date or timestamp.
The result of the function is a large integer. If the
argument can be null, the result can be null; if the
argument is null, the result is the null value.

10/18/08 54
 DB2 JULIAN_DAY
Example 1: Using sample table [Link], set the
integer host variable JDAY to the Julian day of the day
that Christine Haas (EMPNO = ‘000010’) was
employed (HIREDATE = ‘1965-01-01’).
SELECT JULIAN_DAY(HIREDATE)
INTO :JDAY
FROM [Link]
WHERE EMPNO = ‘000010’;
The result is that JDAY is set to 2438762.

10/18/08 55
 DB2 JULIAN_DAY
Example 2: Set integer host variable JDAY to the Julian
day for January 1, 1998.
SELECT JULIAN_DAY(‘1998-01-01’)
INTO :JDAY
FROM SYSIBM.SYSDUMMY1;

The result is that JDAY is set to 2450815.

10/18/08 56
 DB2 MICROSECOND
The MICROSECOND function returns the microsecond part of its
argument.
The argument must be a date, a timestamp, or a valid character string,
representation of a date or timestamp.
The result of the function is a large integer. If the argument can be
null, the result can be null; if the argument is null, the result is the
null value.
If the argument is a timestamp or character string representation of a
timestamp, the result is the microsecond part of the value which is
an integer between 0 and 999999.
If the argument is a duration, the result is the microsecond part of the
value which is an integer between -999999 and 999999. A nonzero
result has the same sign as the argument.
10/18/08 57
 DB2 MICROSECOND
Example : Assume that table TABLEX contains a
TIMESTAMP column named TSTMPCOL and a
SMALLINT column named INTCOL. Select the
microseconds part of the TSTMPCOL column of the
rows where the INTCOL value is 1234:

SELECT MICROSECOND(TSTMPCOL)
FROM TABLEX
WHERE INTCOL = 1234;

10/18/08 58
 DB2 MIDNIGHT_SECONDS
The MIDNIGHT_SECONDS function returns an integer
value in the range of 0 to 86400 that represents the
number of seconds between midnight and the time
specified by the argument.
The argument must be a date, a timestamp, or a valid
character string, representation of a date or timestamp.
The result of the function is a large integer. If the
argument can be null, the result can be null; if the
argument is null, the result is the null value.

10/18/08 59
 DB2 MIDNIGHT_SECONDS
Example : Find the number of seconds between midnight and
00:01:00, and midnight and 13:10:10. Assume that host variable
XTIME1 has a value of ’00:01:00’, and that XTIME2 has a value
of ’13:10:10’.
SELECT MIDNIGHT_SECONDS(:XTIME1),
MIDNIGHT_SECONDS(:XTIME1)
FROM SYSIBM.SYSDUMMY1;
This example returns 60 and 47410. Because there are 60 seconds in
a minute and 3600 seconds in an hour, 00:01:00 is 60 seconds
after midnight ((60 * 1) +0), and 13:10:10 is 47410 seconds
((3600 * 13) + (60 * 10) + 10).

10/18/08 60
 DB2 MIDNIGHT_SECONDS
Example : Find the number of seconds between midnight
and 24:00:00, and midnight and 00:00:00.
SELECT MIDNIGHT_SECONDS(’24:00:00’),
MIDNIGHT_SECONDS(’00:00:00’)
FROM SYSIBM.SYSDUMMY1;
This example returns 86400 and 0. Although these two
values represent the same point in time, different values
are returned.

10/18/08 61
 DB2 MINUTE
The MINUTE function returns the minute part of its argument.
The argument must be a time, timestamp, time duration, timestamp
duration, or a valid character string representation of a time or
timestamp.
The result of the function is a large integer. If the argument can be
null, the result can be null; if the argument is null, the result is the
null value.
If the argument is a time, timestamp, or character string
representation of either, the result is the minute part of the value
which is an integer between 0 and 59.
If the argument is a time duration or timestamp duration, the result is
the minute part of the value which is an integer between -99 and
99. A nonzero result has the same sign as the argument.
10/18/08 62
 DB2 MINUTE
Example: Assume that a table named CLASSES contains
one row for each scheduled class. Assume also that the
class starting times are in the TIME column named
STARTTM. Using these assumptions, select those rows
in CLASSES that represent classes that start on the
hour.

SELECT * FROM CLASSES

WHERE MINUTE(STARTTM) = 0;

10/18/08 63
 DB2 MONTH
The MONTH function returns the month part of its argument.
The argument must be a date, timestamp, date duration, timestamp
duration, or a valid character string representation of a date or
timestamp.
The result of the function is a large integer. If the argument can be
null, the result can be null; if the argument is null, the result is the
null value.
If the argument is a date, timestamp, or character string
representation of either, the result is the month part of the value
which is an integer between 1 and 12.
If the argument is a date duration or timestamp duration, the result is
the month part of the value, which is an integer between -99 and
99. A nonzero result has the same sign as the argument.
10/18/08 64
 DB2 MONTH
Example: Select all rows in the sample table
[Link] for employees who were born in May:

SELECT * FROM [Link]

WHERE MONTH(BIRTHDATE) = 5;

10/18/08 65
 DB2 QUARTER
The QUARTER function returns an integer in the range
of 1 to 4 that represents the quarter of the year in which
the date occurs. For example, the function returns a 1
for any dates in January, February, or March.
The argument must be a date, timestamp, or a valid
character string representation of a date or timestamp.
The result of the function is a large integer. If the
argument can be null, the result can be null; if the
argument is null, the result is the null value.

10/18/08 66
 DB2 QUARTER
Example: The following function returns 3 because
August is in the third quarter of the year.

SELECT QUARTER(‘1996-08-25’)
FROM SYSIBM.SYSDUMMY1;

10/18/08 67
 DB2 QUARTER
Using the sample table [Link] table, set the
integer host variable QUART to the quarter of the year
in which activity number 70 for project ‘AD3111’
occurred. Activity completion dates are recorded in
column ACENDATE.
SELECT QUARTER(ACENDATE)
INTO :QUART
FROM [Link]
WHERE PROJNO = ‘AD3111’ AND ACTNO = 70;
QUART is set to 4.
10/18/08 68
 DB2 SECOND
The SECOND function returns the seconds part of its argument.
The argument must be a time, timestamp, time duration, timestamp
duration, or a valid character string representation of a time or
timestamp.
The result of the function is a large integer. If the argument can be
null, the result can be null; if the argument is null, the result is the
null value.
If the argument is a time, timestamp, or character string
representation of either, the result is the seconds part of the value,
which is an integer between 0 and 59.
If the argument is a time duration or timestamp duration, the result is
the seconds part of the value, which is an integer between -99 and
99. A nonzero result has the same sign as the argument.
10/18/08 69
 DB2 SECOND
Example: Assume that RECEIVED is a TIMESTAMP
column and that one of its values is the internal
equivalent of ‘1988-12-25-17.12.30.000000’. Then, for
this value:
SECOND(RECEIVED)
Returns the value 30.

10/18/08 70
 DB2 TIME
The TIME function returns a time derived from its argument.
The argument must be a time, timestamp, or a valid character string
representation of a time or timestamp.
The result of the function is a time. If the argument can be null, the
result can be null; if the argument is null, the result is the null
value.
If the argument is a time, the result is that time.
If the argument is a timestamp, the result is the time part of the
timestamp.
If the argument is a character string, the result is the time or time part
of the timestamp represented by the character string.

10/18/08 71
 DB2 TIME
Example: Assume that a table names CLASSES contains one row for
each scheduled class. Assume also that the class starting times are
in the TIME column named STARTTM. Using these assumptions,
select those rows in CLASSES that represent classes that start at
1:30 P.M.
SELECT *
FROM CLASSES
WHERE TIME(STARTTM) = ’13:30:00’;

10/18/08 72
 DB2 TIMESTAMP
The TIMESTAMP function returns a timestamp derived from its
argument or arguments.
If only one argument is specified, it must be a timestamp, a valid
string representation of a timestamp, a character string of length 8,
or a character string of length 14.
A character string of length 14 must be a string of digits that
represents a valid date and time in the form yyyymmddhhmmss
where yyyy is the year, mm is the month, dd is the day, hh is the
hour, mm is the minute, and ss is the seconds.
If only one argument is specified and it is a timestamp, the result is
that timestamp. If only one argument is specified and it is a
character string, the result is the timestamp represented by that
character string. The timestamp represented by a string of length
14 has a microsecond part of zero.
10/18/08 73
 DB2 TIMESTAMP
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.

If both arguments are specified, the result is a timestamp with the

date specified by the first argument and the time specified by the
second argument. The microsecond part of the timestamp is zero.

If the argument can be null, the result can be null; if the argument is
null, the result is the null value.

10/18/08 74
 DB2 TIMESTAMP
Example: Assume that table TABLEX contains a DATE column
named DATECOL and a TIME column named TIMECOL. For
some row in the table, assume that DATECOL represents 25
December 1988 and TIMECOL represents 17 hours, 12 minutes,
and 30 seconds after midnight. Then, for this row:

TIMESTAMP(DATECOL, TIMECOL)

Returns the value ‘1988-12-25-17.12.30.000000’.

10/18/08 75
 DB2 TIMESTAMP_FORMAT
The TIMESTAMP_FORMAT function returns a timestamp.
string-expression - An expression that returns any type of character string,
except a CLOB, with a maximum length that is not greater than 255
bytes. Leading and trailing blanks are removed from the string and the
resulting substring is interpreted as a timestamp using the format
specified by format-string.
Format-string - An expression that returns a character string constant with
a maximum length that is not greater than 255 bytes. Format-sring
contains a template of how string-expression is to be interpreted as a
timestamp value. Leading and trailing blanks are removed from the
sting, and the resulting substring must be a valid template for a
timestamp. The only valid format for the function is:
■ ‘YYYY-MM-DD HH24:MI:SS’

10/18/08 76
 DB2 TIMESTAMP_FORMAT
The TIMESTAMP_FORMAT function returns a timestamp.
– ‘YYYY-MM-DD HH24:MI:SS’
– Where:
– YYYY 4-digit year
– MM Month (01-12, January = 01)
– DD Day of month (01-31)
– HH24 Hour of day (00-24, when the value is 24,
the minutes and seconds must be 0).
– MI Minutes (00-59)

10/18/08
– SS Seconds (00-59) 77
 DB2 TIMESTAMP_FORMAT
The result of the function is a timestamp. If the argument can be null, the
result can be null; if the argument is null, the result is the null value.
Example: Set the character variable TVAR to the value of CREATEDTS
from [Link] if it is equal to one second before the
beginning of the year 2000 ('1999-12-31 23:59:59'). The character
string should be interpreted in the only format that can be specified for
the function.
SELECT VARCHAR_FORMAT(CREATEDTS,
'YYYY-MM-DD HH24:MI:SS')
INTO :TVAR FROM [Link]
WHERE CREATEDTS = TIMESTAMP_FORMAT
('1999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS');
10/18/08 78
 DB2 WEEK
The WEEK function returns an integer in the range of 1 to
54 that represents the week of the year. The week starts
with Sunday.
The argument must be a date, a timestamp or a valid
character string representation of a date or timestamp.
The result of the function is a large integer. If the
argument can be null, the result can be null; if the
argument is null, the result is the null value.

10/18/08 79
 DB2 WEEK
Using sample table [Link], set the integer host
variable WEEK to the week of the year that project
‘AD2100’ ended.
SELECT WEEK(PRENDATE)
INTO :WEEK
FROM [Link]
WHERE PROJNO = ‘AD2100’;
The result of that :WEEK is set to 6.

10/18/08 80
 DB2 YEAR
The YEAR function returns the year part of its argument.
The argument must be a date, timestamp, date duration, timestamp
duration, or a valid character string representation of a date or
timestamp.
The result of the function is a large integer. If the argument can be
null, the result can be null; if the argument is null, the result is the
null value.
If the argument is a date, a timestamp, or character string
representation of either, the result is the year part of the value,
which is an integer between 1 and 9999.
If the argument is a date duration or timestamp duration, the result is
the year part of the value, which is an integer between -9999 and
9999. A nonzero result has the same sign as the argument.
10/18/08 81
 DB2 YEAR
Example: From the table [Link] select all rows
for employees who were born in 1941.

SELECT *
FROM [Link]
WHERE YEAR(BIRTHDATE) = 1941;

10/18/08 82
 DB2 Review
Any questions about the DB2 Data types and
Functions reviewed in this section?

Let’s now discuss the Oracle datatypes and

function.

10/18/08 83
 Oracle Datatypes
■ DATE ■ NUMBER(p,s)
■ CHAR(size) max 2000 ■ LONG
characters ■ RAW(size)
■ NCHAR(size) max is ■ LONG RAW
2000 ■ BLOB (up to 4 gigabytes)
■ NVARCHAR2(size) max ■ CLOB (up to 4 gigabytes)
is 4000 bytes
■ NCLOB (up to 4
■ VARCHAR2(size) max gigabytes)
length is 4000 bytes
■ BFILE (references
■ VARCHAR max length is external file up to 4
4000 bytes gigabytes)
■ ROWID ■ MLSLABEL
10/18/08 84
 Oracle Number Datatypes
■ NUMBER(p,s) – stores zero, positive and negative fixed
and floating-point numbers.
– P is precision – the total number of digits 1 to 38
– S is the scale – the number of digits to the right of the decimal
point –84 to 127 -if a value exceeds the scale, Oracle rounds it
■ NUMBER (floating-point number for a decimal
precision of 38 – no scale specified)
■ FLOAT (decimal precision 38 or binary precision 126)
■ FLOAT(b) (binary precision b that can range from1 to
126)

10/18/08 85
 Oracle Datatypes
■ LONG (store variable length character strings up to 2
gigabytes. - Only one LONG type can be in a table.
LONG columns cannot be indexed.)
■ RAW(size) – This type is a variable-length datatype like
the VARCHAR2 character datatype
■ LONG RAW – Used to store graphics, sound,
documents, or arrays of binary data.
You can index RAW data, but not LONG RAW data.

10/18/08 86
 Oracle Pseudocolumns

An Oracle pseudocolumn behaves like a table column, but is not

actually stored in the table.

You can select from pseudocolumns, but you cannot insert, update,
or delete their values.

- NEXTVAL and CURRVAL

- LEVEL
- ROWID
- ROWNUM

10/18/08 87
 Oracle Sequences
A SEQUENCE is a schema object that can generate unique sequential
values. These values are often used for primary and unique keys.

Use these pseudocolumns in SQL statements to refer to sequences:

– CURRVAL (returns the current value of a sequence)
– NEXTVAL (increments the sequence and returns the next value)

You must qualify CURRVAL and NEXTVAL with the name of the
sequence: [Link] [Link]

The following statement increments the ZSEQ sequence and returns the
new value: SELECT [Link] FROM dual;
The following statement selects the current value of ZSEQ, but does not
change it’s value: SELECT [Link] from dual;
10/18/08 88
 Oracle LEVEL Datatype
■ The pseudocolumn LEVEL is used for hierarchical queries in
Oracle.
■ The LEVEL returns 1 for a root node, 2 for a child of a root,
and so on.
■ A root node is the highest node within an inverted tree. A
child node is any nonroot node. A parent node is any node
that has children. A leaf node is any node without children.
■ Consult the Oracle manual for more information about coding
hierarchical queries.

10/18/08 89
 Oracle ROWNUM
■ The pseudocolumn ROWNUM returns a number indicating
the order in which Oracle selects the row from a table or a set
of joined rows.
■ The first row selected has a ROWNUM of 1, the second 1,
and so on.
■ You can use ROWNUM to limit the number of rows returned
by a query:
SELECT * FROM table WHERE ROWNUM < 10;
But – you can’t use the > because the conditions testing for ROWNUM
values greater than a positive integer are always false: this query
returns no rows:
SELECT * FROM table WHERE ROWNUM > 10;

10/18/08 90
 Oracle Number Functions
Number functions accept numeric input and return numeric values.
ABS(n) MOD(m,n)
ACOS(n) POWER(m,n)
ASIN(n) ROUND(n[,m])
ATAN(n) SIGN(n)
ATAN2(n, m)
SIN(n)
CEIL(n)
SINH(n)
COS(n)
COSH(n) SQRT(n)
EXP(n) TAN(n)
FLOOOR(n) TANH(n)
LN(n) TRUNC(n[,m])
LOG(m,n)
10/18/08 91
 Oracle Character Functions
■ Character functions accept character input and return
either character or numeric values.

■ This first group of functions accept character input and

return character values.

■ The second group of functions accept character input and

return numeric values.

10/18/08 92
 Oracle Character Functions –
First Set
CHR(n) NLS_UPPER (n [, nls_sort])
CONCAT(char1, char2) REPLACE (char,
INITCAP(char) search_string [,
replacement_string])
LOWER(char)
RPAD (char1, n[,char2])
LPAD(char,n[,char2])
RTRIM (char [,set])
LTRIM(char[,set])
NLS_INITCAP (n [, SOUNDEX(char)
nls_sort]) SUBSTR(char, m[,n])
NLS_LOWER (n [, SUBSTRB(char, m[,n])
nls_sort]) TRANSLATE(char,from,to)
UPPER(char)

10/18/08 93
 Oracle Character Functions –
Second Set
ASCII(char)
INSTR(char1, char2 [,n[,m]])
INSTRB(char1, char2 [,n[,m]])
LENGTH(char)
LENGTHB(char)
NLSSORT(char [, nls_sort])

10/18/08 94
 Oracle DATE / Functions
Date type includes timestamp YYYYMMDDHHMMSS
Default date format is  DD-MON-YY
Date Functions
- Day
- Month
- Year
- To_Date
- T0_Char
- Add_Months
- Months_Between
■ Subtracting one date from another date returns the number of days
between them. The default time is 12:00:00AM(midnight).
10/18/08 95
 Oracle Date Functions

■ Date functions operate on values of the DATE

datatype.

■ All date functions return a value of DATE datatype,

except the MONTHS_BETWEEN function, which
returns a number.

10/18/08 96
 Oracle Date Functions

ADD_MONTHS(d,n)
LAST_DAY(d)
MONTHS_BETWEEN(d,e)
NEW_TIME(d,a,b)
NEXT_DAY(d,char)
ROUND (d [,fmt])
SYSDATE
TRUNC(d [,fmt])

10/18/08 97
 Oracle Date Truncation and
Rounding
ROUND(d[,fmt])
TRUNC(d[,fmt])
MONTH or MON or MM or
RM
Format model for fmt in
ROUND and TRUNC: WW or IW
CC or SCC W
YYYY or SYYYY DDD or DD or J
YYY or YY or Y DAY or DY or D
Y,YYY or YEAR or SYEAR HH or HH12 or HH24
Q MI

10/18/08 98
 Oracle Conversion Functions
CHARTOROWID(char) TO_DATE (char [, fmt [,
CONVERT( char, dest_char_set ‘nlsparams’ ] ])
[,source_char_set ]) TO_MULTI_BYTE(char)
HEXTORAW(char) TO_NUMBER (char [, fmt [,
RAWTOHEX(raw) ‘nlsparams’ ] ])
ROWIDTOCHAR(rowid) TO_SINGLE_BYTE(char)
TO_CHAR, date conversion
TRANSLATE USING
TO_CHAR(d [, fmt [,
‘nlsparams’] ]) TRANSLATE(text USING
TO_CHAR, number conversion {CHAR_CS | NCHAR_CS })
TO_CHAR(n [, fmt [,
‘nlsparams’] ])

10/18/08 99
 Oracle Date Format Models for
fmt of TO_CHAR and TO_DATE
SCC or CC WW or W
YYYY or SYYYY IW
YYY or YY or Y DDD or DD or D
IYYY DAY
IYY or IY or I DY
Y,YYY J
SYEAR or YEAR AM or PM
RR A.M. or P.M.
BC or AD HH or HH12
B.C. or A.D. HH24
Q MI
MM SS or SSSSS
RM -/ , . ; : (punctuation)
MONTH “…text..” (string)
MON
10/18/08 100
 Oracle Date Format
Prefixes and Suffixes
Prefix: Date Format Case Control:
FM (fill mode)
Uppercase:
FX (format exact)
DAY, DY, MONTH, MON,
Suffix:
YEAR, AM, PM, A.M., A.m.,
TH (ordinal number – 4th) P.M., P.m.
SP (spelled out number - FOUR)
Initial Caps:
SPTH and THSP (spelled out
ordinal number – FOURTH) Day, Dy, Month, Mon, Year, Am,
Pm
Note: When prefixes and suffixes are Lowercase:
added to a date format, the case day, dy, month, mon, year, am,
(upper, initial, or lower) is pm
determined by the format element,
not by the prefix or suffix. ‘ddTH’
produces “04th” not “04TH”

10/18/08 101
 Oracle Number Formats for fmt
of TO_CHAR
9 L
0 C
$ ,
B .
MI V
S EEEE
PR RN or rn
D DATE
G

10/18/08 102
 Oracle SYSDATE Function
– The SYSDATE function returns the current date
and time from Oracle.
– If you want to only manipulate the CURRENT
DATE, and you are not selecting other columns
from a table, you may issue a SELECT statement
using the Oracle one row table:
SELECT SYSDATE FROM DUAL;

Note: This is the “DB2 way” 

SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1

10/18/08 103
 Oracle Conversion Functions
Conversion functions convert a value from one
datatype to another.

Generally, the form of the function names follows

the convention datatype TO datatype

The first datatype is the input datatype; the last

datatype is the output datatype.

10/18/08 104
 Oracle Other Single-Row
Functions
DUMP (expr [, display_format [, start_position [,
length ]]])
EMPTY_[B | C]LOB()
BFILENAME (‘directory’, ‘filename’)
GREATEST (expr [, expr] …)
LEAST (expr [, expr] …)
NLS_CHARSET_DECL_LEN(bytecnt, csid)

10/18/08 105
 Oracle Other Single-Row
Functions
NLS_CHARSET_ID(text)
NLS_CHARSET_NAME(n)
NVL (expr1, expr2)
UID
USER
USERENV (‘option’) (option can be ENTRYID,
SESSIONID, TERMINAL, LANGUAGE,
LABEL)
VSIZE (expr)
10/18/08 106
 Oracle Group Functions
Default is to include all the candidate rows, but using
DISTINCT causes the group function to use only the distinct
values of the argument expression
AVG ([DISTINCT | ALL] n)
COUNT(* | [DISTINCT | ALL] expr )
MAX([DISTINCT | ALL] expr)
MIN ([ DISTINCT | ALL] expr)
STDDEV([DISTINCT | ALL] n)
SUM([DISTINCT | ALL] n)
VARIANCE ([DISTINCT | ALL] n)

* Note: more on grouping data later

10/18/08 107
 Oracle Review
Any questions about the Oracle Data types and
Functions reviewed in this section?

Let’s now discuss the way you combine data

from multiple tables in Oracle and DB2.

10/18/08 108
 Combining Tables
Oracle DB2
-Join -Inner join

-Outer join (+) -Left Outer Join

-Right Outer Join
-Union, Union All -Full Outer Join

-Intersect, Minus -Union, Union All

10/18/08 109
 JOIN Differences
Oracle DB2
-Join, Outer join (+) -InnerJoin, Left Outer Join,
Right Outer Join, Full Outer Join
SELECT A.STUDENT_ID, SELECT A.STUDENT_ID,
B.EFFECTIVE_DATE , [Link] B.EFFECTIVE_DATE , [Link]
FROM
FROM FSDBA.STUDENT_MASTER
FSDBA.STUDENT_MASTER A,
AS A LEFT OUTER JOIN
FSDBA.STUDENT_HOLDS B
FSDBA.STUDENT_HOLDS AS B
WHERE
ON
A.STUDENT_ID =
B.STUDENT_ID(+) ; A.STUDENT_ID = B.STUDENT_ID ;

10/18/08 110
 Oracle Intersect & Minus
List the product and price only if it appears in both the queries -
25 rows.
SELECT product_id, max(list_price) from PRICE group by
product_id INTERSECT SELECT product_id,
max(actual_price) from ITEM group by product_id;

Report the products in the first query of prices that are not in the
second query - 6 rows
SELECT product_id, max(list_price) from PRICE group by
product_id MINUS SELECT product_id, max(actual_price)
from ITEM group by product_id;
10/18/08 111
 DB2 CASE – SQL Logic
SELECT A.COURSE_DEPT_NUMBER, B.COURSE_NUMBER,
B.COURSE_IND, B.SECTION_NUMBER, A.COURSE_TITLE,
CASE COURSE_DEPT_NUMBER
WHEN '4206' THEN 'ART‘
WHEN '4207' THEN 'ART
HISTORY‘ WHEN '4208' THEN 'ART
EDUCATION END
FROM FSDBA.COURSE_MASTER AS A
INNER JOIN FSDBA.CURRENT_COURSES AS
ON A.COURSE_NUMBER = B.COURSE_NUMBER AND
A.COURSE_IND = B.COURSE_IND
WHERE YEAR = '1998' AND TERM = '1‘ AND
COURSE_DEPT_NUMBER IN ('4206', '4207', '4208');
10/18/08 112
 Tuning Considerations -
Differences
Oracle DB2
■ When joining tables, enter the ■ When joining tables, enter the join
most restrictive predicates before predicates first – preferably in the
the join predicates in the WHERE ON clause - then enter most
clause restrictive predicates first in the
WHERE clause
■DB2 has filter factors on predicate
types that help determine the cost of
a query
■Create indexes for frequently used
ORDER BY or GROUP BY or
equality predicates

10/18/08 113
 Optimizer Differences
Oracle – local server DB2 – NWRDC $$$
-Major changes with 8i -Major changes with Version 6
-Analyze (with compute or - Runstats
estimate) -New tables and enhancements to
- Rule-based - Optimizer_mode = Optimize for n rows (assumes all
rule rows mode)
-Cost-based - Optimizer_mode =
all_rows, first_rows, or choose
(default) -Optimizer
hints
Optimizer hints -WHEN_OPTIMIZE (R, B, blank)
-Or as hints in individual queries 
-OPTHINT='hint-id'
/*+all_rows*/ /*+first_rows*/

10/18/08 114
 DB2 Explain Table
Explain process - generates an Explain Report
(51 columns in the DB2 Plan Table)

CREATE TABLE FSUDBA.PLAN_TABLE

(QUERYNO INTEGER NOT NULL,
QBLOCKNO SMALLINT NOT NULL,
APPLNAME CHAR (8) FOR SBCS DATA NOT NULL,
PROGNAME CHAR (8) FOR SBCS DATA NOT NULL,
PLANNO SMALLINT NOT NULL,
METHOD SMALLINT NOT NULL,
CREATOR CHAR (8) FOR SBCS DATA NOT NULL,
TNAME CHAR (18) FOR SBCS DATA NOT NULL,
TABNO SMALLINT NOT NULL,

10/18/08 115
 DB2 Explain Table
ACCESSTYPE CHAR (2) FOR SBCS DATA NOT NULL,
MATCHCOLS SMALLINT NOT NULL,
ACCESSCREATOR CHAR (8) FOR SBCS DATA NOT NULL,
ACCESSNAME CHAR (18) FOR SBCS DATA NOT NULL,
INDEXONLY CHAR (1) FOR SBCS DATA NOT NULL,
SORTN_UNIQ CHAR (1) FOR SBCS DATA NOT NULL,
SORTN_JOIN CHAR (1) FOR SBCS DATA NOT NULL,
SORTN_ORDERBY CHAR (1) FOR SBCS DATA NOT NULL,
SORTN_JOIN CHAR(1) FOR SBCS DATA NOT NULL,
SORTN_ORDERBY CHAR(1) FOR SBCS DATA NOT NULL,
SORTN_GROUPBY CHAR(1) FOR SBCS DATA NOT NULL,
SORTC_UNIQ CHAR(1) FOR SBCS DATA NOT NULL,

10/18/08 116
 DB2 Explain Table
SORTC_JOIN CHAR(1) FOR SBCS DATA NOT NULL,
SORTC_ORDERBY CHAR(1) FOR SBCS DATA NOT NULL,
SORTC_GROUPBY CHAR(1) FOR SBCS DATA NOT NULL,
TSLOCKMODE CHAR (3) FOR SBCS DATA NOT NULL,
TIMESTAMP CHAR (16) FOR SBCS DATA NOT NULL,
REMARKS VARCHAR (254) FOR SBCS DATA NOT NULL,
PREFETCH CHAR(1) FOR SBCS DATA NOT NULL
WITH DEFAULT,
COLUMN_FN_EVAL CHAR(1) FOR SBCS DATA NOT NULL
WITH DEFAULT,

10/18/08 117
 DB2 Explain Table
MIXOPSEQ SMALLINT NOT NULL WITH DEFAULT,
VERSION VARCHAR (64) FOR SBCS DATA NOT NULL WITH DEFAULT,
COLLID CHAR (18) FOR SBCS DATA NOT NULL WITH DEFAULT,
ACCESS_DEGREE SMALLINT DEFAULT NULL,
ACCESS_PGROUP_ID SMALLINT DEFAULT NULL,
JOIN_DEGREE SMALLINT DEFAULT NULL,
JOIN_PGROUP_ID SMALLINT DEFAULT NULL,
SORTC_PGROUP_ID SMALLINT DEFAULT NULL,
SORTN_PGROUP_ID SMALLINT DEFAULT NULL,

10/18/08 118
 DB2 Explain Table
PARALLELISM_MODE CHAR(1) FOR SBCS DATA DEFAULT NULL,
MERGE_JOIN_COLS SMALLINT DEFAULT NULL,
CORRELATION_NAME CHAR(18) FOR SBCS DATA DEFAULT NULL,
PAGE_RANGE CHAR (1) FOR SBCS DATA NOT NULL
WITH DEFAULT,
JOIN_TYPE CHAR (1) FOR SBCS DATA NOT NULL WITH DEFAULT,
GROUP_MEMBER CHAR(8) FOR SBCS DATA NOT NULL
WITH DEFAULT,
IBM_SERVICE_DATA VARCHAR (254) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WHEN_OPTIMIZE CHAR(1) FOR SBCS DATA NOT NULL
WITH DEFAULT,
QBLOCK_TYPE CHAR(6) FOR SBCS DATA NOT NULL
WITH DEFAULT,

10/18/08 119
 DB2 Explain Table
BIND_TIME TIMESTAMP NOT NULL WITH DEFAULT,
OPTHINT CHAR(8) FOR SBCS DATA NOT NULL
WITH DEFAULT,
HINT_USED CHAR(8) FOR SBCS DATA NOT NULL
WITH DEFAULT,
PRIMARY_ACCESSTYPE CHAR(1) FOR SBCS DATA NOT NULL
WITH DEFAULT)
IN DATABASE FSZDEVDB;

10/18/08 120
 DB2 Explain Report

---------+---------+---------+---------+---------+---------+---------+----
QUERY BLOCK PLAN METH TNAME TABNO
-----+-------- -----+-------- -+---------+---------+---------+---------+---- --
00587 01 01 00 WWW_STUDENT_LOG 01

TYPE MC ANAME
---------+-------------+----
I 02 IXWSL04
--------+---------+---------+---------+---------+--
IO SNU SNJ SNO SNG SCU SCJ SCO SCG PF
--------+---------+---------+---------+---------+--
Y N N N N N N N N

10/18/08 121
 Oracle Explain Plan Table
Explain Plan generates a Query Plan. We haven’t had to be concerned with
the cost of Oracle queries, so we don’t run EXPLAINs on every Oracle
sql satement, as we do with DB2. Explain plan table (24 columns) can be
created with the [Link] script.

create table PLAN_TABLE (

statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(30),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_instance numeric,
10/18/08 122
 Oracle Explain Plan Table
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),

10/18/08 123
 Oracle Explain Plan Table
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30));

10/18/08 124