DB2 Frequently Asked Questions

Q15. Is there a way determine the day of the week from a date?

A15. Yes, with the following query a result of 0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday,

4 = Thursday, 5 = Friday, and 6 = Saturday.

SELECT DAYS(CURRENT DATE) - (DAYS (CURRENT DATE) / 7 ) * 7

FROM SYSIBM.SYSDUMMY1

or with use of the CASE statement

SELECT CURRENT DATE,

CASE

WHEN (DAYS(CURRENT DATE) - (DAYS(CURRENT DATE) / 7 ) * 7) = 0

THEN 'SUNDAY'

WHEN (DAYS(CURRENT DATE) - (DAYS(CURRENT DATE) / 7 ) * 7) = 1

THEN 'MONDAY'

WHEN (DAYS(CURRENT DATE) - (DAYS(CURRENT DATE) / 7 ) * 7) = 2

THEN 'TUESDAY'

WHEN (DAYS(CURRENT DATE) - (DAYS(CURRENT DATE) / 7 ) * 7) = 3

THEN 'WEDNESDAY'

WHEN (DAYS(CURRENT DATE) - (DAYS(CURRENT DATE) / 7 ) * 7) = 4

THEN 'THURSDAY'

WHEN (DAYS(CURRENT DATE) - (DAYS(CURRENT DATE) / 7 ) * 7) = 5

THEN 'FRIDAY'

WHEN (DAYS(CURRENT DATE) - (DAYS(CURRENT DATE) / 7 ) * 7) = 6

THEN 'SATURDAY'

END AS DAY

FROM SYSIBM.SYSDUMMY1

You may substitute any host variable for current date.

Q14. How do I retrieve the 10 largest values from a table?

A14. The following query retrieves the 10 largest account balance

SELECT ACCT_BAL, CUST_NAME

FROM DBAADMIN.TCO88001 A

WHERE 10 > (SELECT COUNT(*)

FROM DBAADMIN.TCO88001 B

WHERE A.ACCT_BAL < B.ACCT_BAL)

ORDER BY ACCT_BAL DESC

In the above example the sub-query (SELECT COUNT(*)...) ranks the ACCT_BAL.

This same method can be used to find the most current row for an individual.

Example:

The EMPLOYEE_LOCATION table contains a row for each location an employee is assigned to.

SELECT LOCATION_EFFECTIVE_DATE, PLANT_NUMBER FROM table_name

WHERE SOC_SEC_NO = '001010001'

AND 1 > (SELECT COUNT(*) FROM table_name B

WHERE A.LOCATION_EFFECTIVE_DATE < B.LOCATION_EFFECTIVE_DATE

AND SOC_SEC_NO = '001010001')

Result table would contain:

LOCATION_EFFECTIVE_DATE: 1999-01-10

PLANT_NUMBER: 0002

Q13. I have a 8 character column which contains a 3 character process number followed by a 5

character item number. Is there a way for me to select only on the 5 character item number?

A13. Yes, use the SUBSTR function - SUBSTR(column name, starting position, number of position).

Example:

In your situation: column SUPPLY_ITEM is CHAR(08) of which first 3 position is process

number followed by 5 position item number.

00112345

00122222

00244444

SELECT SUBSTR(SUPPLY_ITEM, 4 , 5) FROM table name

The above SQL would return: 12345, 22222, and 44444

Q12. I have columns that have null values. Is there a way for me to reference the columns without

having to check the indicator to determine whether there is a true value?

A12. Yes. With the use of COALESCE/VALUE function DB2 will return the column value if it is

not null, otherwise it will return a default value that you specify. COALESCE and VALUE

are interchangeable.

Example:

Column D_CHANGE is defined as a DB2 date that is nullable

Column C_STATUS is defined as 3 character and is nullable

Table values:

SELECT COALESCE(D_CHANGE, DATE('9999-12-31')) FROM table_name

The above SQL will return 3 rows: 2000-03-24, 9999-12-31, and 2000-01-20

SELECT VALUE(C_STATUS, 'N/A') FROM table_name

The above SQL will return 3 rows: BBB, AAA, and N/A

Q11. I have a table that contains historical data. I need to only the most current information per

employee. How do I do this? The table is as follows:

N_EMPLOYEE CHAR(10)

N_DEPT CHAR(04)

D_EFFECTIVE DATE

A_SALARY DEC(9,2)

M_LAST CHAR(20)

M_FIRST CHAR(20)

M_MID_INITIAL CHAR(01)

M_ADDRESS_1 CHAR(30)

M_ADDRESS_2 CHAR(30)

M_CITY CHAR(30)

N_STATE CHAR(02)

C_ZIP CHAR(10)

A11. To obtain the most current information by employee, you need first to determine each

employee's most current effective date. The following query does this:

AS MAX_EFF_DATE

FROM TCO88001

GROUP BY N_EMPLOYEE

Since we will need to reference the maximum effective date for each employee in the

the rest of the information per employee, we will need to join the table data with the

results of the above query.

SELECT A.N_EMPLOYEE, A.N_DEPT, A.D_EFFECTIVE, A_SALARY, A.M_LAST,

A.M_FIRST, A.M_MID_INITIAL, A.ADDRESS_1, A.ADDRESS_2,

A.M_CITY, A.N_STATE, A.C_ZIP

FROM TCO88001 A,

(SELECT N_EMPLOYEE, MAX(D_EFFECTIVE)

AS MAX_EFF_DATE

FROM TCO88001

GROUP BY N_EMPLOYEE) B

WHERE A.N_EMPLOYEE = B.N_EMPLOYEE

AND A.D_EFFECTIVE = MAX_EFF_DATE

You will notice that the above query listed each column in the table in the SELECT. A

SELECT * was not used, because it would produce a result set containing all the columns

from the table followed by N_EMPLOYEE and D_EFFECTIVE from the joined result set.

10. How can I update part of a primary key? DB2 won't let me use the

UPDATE tablename SET col1 = 'value1' WHERE col1 = 'value2'

A10. An update statement that affects a unique constraint (primary key or other unique indexed

columns) cannot be used to update more than one row of the table. It will return SQL code -534.

To update multiple rows with a unique constraint:

1) Select only the columns of the unique contraint where it meets the condition for your update

2) Update the column in the unique constraint qualifying each column from step #1

Example:

TABLE1 contains the following columns

N_DOC CHAR(03) key1

D_DOC DATE key2

N_SEQ_DOC DEC(03) key3

N_SEQ DEC(03) key4

Other columns

Program needs to change D_DOC from 2000-01-24 to 1999-12-31

There are multiple rows with D-DOC with the value of 2000-01-24

QuikJob:

OPTION SEQCHK=NO,SQLSYSN=DBT1,SPIE=NO,SUBSPIE=NO,LIST=YES

EQU WST-AREA WST00-00

EQU N-DOC (3)

EQU D-DOC (10)

EQU N-SEQ-DOC (03)-P

EQU N-SEQ (03)-P

EQU SQLCODE (3)-P

EXEC SQL

WHENEVER SQLERROR GOTO 800

END-EXEC

EXEC SQL

WHENEVER SQLWARNING GOTO 800

END-EXEC

EXEC SQL

DECLARE CUR1 CURSOR FOR

SELECT N_DOC, D_DOC, N_SEQ_DOC, N_SEQ

FROM DBAADMIN.TCO88010

WHERE D_DOC = '2000-01-24'

END-EXEC

EXEC SQL

OPEN CUR1

END-EXEC

EXEC SQL

WHENEVER NOT FOUND GOTO 990

END-EXEC

100 EXEC SQL

FETCH CUR1

INTO :N-DOC, :D-DOC, :N-SEQ-DOC, :N-SEQ

END-EXEC

EXEC SQL

UPDATE DBAADMIN.TCO88010

SET D_DOC = '1999-12-31'

WHERE N_DOC = :N-DOC

AND D_DOC = '2000-01-24'

AND N_SEQ_DOC = :N-SEQ-DOC

AND N_SEQ = :N-SEQ

END-EXEC

GOTO 100

800 MOVE @VAL-SQL-CODE TO SQLCODE

MOVE C'SQL CODE = ' TO PRT1

MOVE SQLCODE TO PRT15 0C

PRINT

ABEND 0008

990 GOTO EOJ

Q9. Can an non-DB2 program call a DB2 subroutine?

A9. Yes, just compile the program as a normal batch program and execute the

program using DB2 execution JCL.

Example:

DB2 subroutine: L16001 with plan L16001 is in library SYS1.LEMVS.SUBLIB

Main non DB2 program: L88001 is in library SYS1.LOADLIB

Execution JCL:

//STEP01 EXEC DB2BPROD

//DB2SYSIN DD *

DSN SYSTEM(DBP3)

RUN PROGRAM(L88001) PLAN(L16001) -

LIB('SYS1.LOADLIB')

END

Note: Steplib contains: SYS1.DB2.DBP3.DSNLOAD

SYS1.LEMVS.LINKLIB

SYS1.LEMVS.SUBLIB

Q8. How do I determine how many rows there are by a type? (i.e. how many employee are there by

department)

A8. Use COUNT(*) with GROUP BY

Example: SELECT COUNT(*), N_DEPT FROM DBAADMIN.TCO88001 GROUP BY N_DEPT

Q7. Can a DB2 subroutine be used by both batch and CICS?

A7. Yes, be sure the subroutine is compile with the NODYNAM option and linkedit with DSNELI.

Q6. I got an SQL code of -911 (lockout). How do I determine what caused the lockout?

A6. Look for message DSNT376I on the system log by:

1. Sign on the IOF by choosing option IO on your ISPF menu

2. Enter /L for log

3. Enter I for index

4. Enter 3 for DB2 messages

5. Scroll down until you see a message like + PLAN=????? WITH nnn

followed by message + DSNILMCL RESOURCE UNAVAILABLE

(the + sign is for DBP3, # sign would be for DBT1 and _ sign for DBT2)

(????? = an DB2 plan name and nnn = an number)

example:

_ 123 * 99089 11:28 DSNT376I + PLAN=QJ#PLAN WITH 435

_ 124 99089 11:28 DSNT501I + DSNILMCL RESOURCE UNAVAILABLE 436

6. Enter S beside the line which contains + PLAN=????? WITH nnn

7. Results:

M 8000000 SDUP 99089 11:28:01.06 STC21922 00010000 DSNT376I + PLAN=QJ#PLAN WITH 435

D 435 00000000 CORRELATION-ID=J95CERT

D 435 00000000 CONNECTION-ID=DB2CALL

D 435 00000000 LUW-ID=GDCNET.A17DBP3.B2056D062315=10862

D 435 00000000 IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS PLAN=QMF311 WITH

D 435 00000000 CORRELATION-ID=X95US

D 435 00000000 CONNECTION-ID=DB2CALL

D 435 00000000 LUW-ID=GDCNET.A17DBP3.B2056C988F4F=10788

E 435 00000000 ON MEMBER DBP3

M 8000000 SDUP 99089 11:28:01.12 STC21922 00010000 DSNT501I + DSNILMCL RESOURCE UNAVAILABLE 436

D 436 00000000 CORRELATION-ID=J95CERT

D 436 00000000 CONNECTION-ID=DB2CALL

D 436 00000000 LUW-ID=*

D 436 00000000 REASON 00C9008E

D 436 00000000 TYPE 00000304

E 436 00000000 NAME DCO66001.SCO66009.X'0001F1'.X'04'

In above example:

Job J95CERT running with DB2 plan QJ#PLAN (QuikJob) was locked out by X95US using DB2

Plan QMF311 (QMF).

Job name (J95CERT) of the locked out job is found in line #2 where CORRELATION-ID=

DB2 plan of the locked out job is found in line #1 where PLAN=

User id or job name causing the lock out is found in line # 7 where CORRECLATION ID=

(if its a user id, the lock out is caused by an on-line function, i.e. QMF, FOCUS, FILE-AID)

DB2 plan causing the lock out is found in line #6 where PLAN= from messsage

ONE HOLDER OF THE RESOURCE IS PLAN=

Q5. How do I pass a PARM to an DB2 program?

A5. Add the PARMS subcommand to your RUN command

Example: DSN SYSTEM(DBP3)

RUN PROGRAM(L88001) PLAN(L88001) PARMS('TEST') -

LIB('D0973.CSDLIB')

END

Q4. How can I tell how many rows my UPDATE statement will effect without having to do a SELECT?

A4. DB2 will place in SQL-ERRD occurrence 3 for Cobol

the number of rows affected by a UPDATE or DELETE statement.

Q3. Why are DB2 dates returned in one format from a SELECT and requiring INSERTS to be

a different format?

A3. This is not true. DB2 dates may be retrieved or inserted in any of the following formats:

MM/DD/CCYY (USA format)

CCYY-MM-DD (ISO format)

MM.DD.CCYY (EUR format)

Specify what format you want by using the CHAR function in

your SELECT statement. For INSERTs, DB2 looks at your punctuation to determine

which format you are using.

For example INSERT D_DOC INTO DBAADMIN.TCO88010

VALUE ('08/25/1998') will use USA format

INSERT D_DOC INTO DBAADMIN.TCO88010

VALUE ('1998-08-25') will use ISO format

INSERT D_DOC INTO DBAADMIN.TCO88010

VALUE (25.08.1998) will use EUR format

Q2. Why are dates from the DB2 unload in one format and loads requiring a different format?

A2. This is not true. DB2 dates are unloaded using the system installation default format

of USA (i.e. MM/DD/CCYY). DB2 dates may be loaded in any of the valid format

(USA, ISO, or EUR). DB2 will determine which format you are using by looking at

your punctuation.

Q1. Dislike use of fake date to keep from having nulls

A1. Tables can be created with an default date other than current date.

Suggestion: Setup default date as 0001-01-01.

Add the following to WSSQLERR copy member.

10 NULL-DATE PIC X(10) VALUE '0001-01-01'.

Checking for null date:

IF D-DOC = NULL-DATE is no different than IF D-DOC = SPACES