DB2 Programming Tips

1. Use bounds to reduce size of results table
- Example:
Condition: List all employees with last name beginning with 'A'
Bad solution:
SELECT COL1, COL2, ETC.
FROM TABLE
WHERE NAME > 'A'
-this will return all rows where name is greater than 'A' including those beginning with 'B', 'C', etc.
Good solution:
SELECT COL1, COL2, ETC.
FROM TABLE
WHERE NAME BETWEEN 'A' AND 'B'
-the column NAME is not one character in this case

2. Select only those columns needed for your process
-selecting more that needed, will cause DB2 to process longer rows in DSNDB07 when sorts are needed, may cause more I/Os when not all of the data can be placed in its buffers
-Example:
Condition: List all employees for department 0973 and manager code is 01
Bad solution:
SELECT COL1, COL2, DEPT, MANAGER_CODE, ETC.
FROM TABLE
WHERE DEPT = '0973'
AND MANAGER_CODE = '01'
-its not necessary to select DEPT and MANAGER_CODE since their values are already known
Good solution:
SELECT COL1, COL2, ETC.
FROM TABLE
WHERE DEPT = '0973'
AND MANAGER_CODE = '01'

3. Do not select any columns for existence checking
-Example:
Condition: Is there any employees in department 9999
Bad solution:
SELECT COUNT(*)
FROM TABLE
WHERE DEPT = '9999'
-this will cause DB2 to do a tablespace scan because the COUNT function is a Stage 2 predicate
SELECT COL1
FROM TABLE
WHERE DEPT = '9999'
-this may use a index, if there is one on DEPT, but you are returning the value of COL1 for each and every occurrence where DEPT is equal to '9999'
Good solution:
SELECT 1
FROM TABLE
WHERE DEPT = '9999'
-no real values is passed to your program
-you would only need to check SQLCODE for value +0

4. Avoid unnecessary cursors
When 90% of the time only one row is returned, select first
-Example:
Condition: List name of alternate physician for Dr. ?????
In most cases Dr. ???? will have one alternate
Bad solution:
DECLARE PHYCUR CURSOR FOR
SELECT NAME
FROM ALTERNATE_TABLE
WHERE PRIM_PHY = :host_variable

OPEN PHYCUR

loop
FETCH PHYCUR
INTO :i/o area
if more goto loop
else
CLOSE PHYCUR
Good solution:
DECLARE PHYCUR CURSOR FOR
SELECT NAME
FROM ALTERNATE_TABLE
WHERE PRIM_PHY = :host_variable

SELECT NAME
FROM ALTERNATE_TABLE
WHERE PRIM_PHY = :host_variable
IF SQLCODE = +0
process row
ELSE
IF SQLCODE = -811
OPEN PHYCUR
goto loop
ELSE
error routine.

loop
FETCH PHYCUR
INTO :i/o area

if more goto loop
else
CLOSE PHYCUR

5. Avoid unnecessary selects
repetitive SELECTs on small tables
-Example:
Condition: STATE code table has 50 entries, one for each state Determine which state each employee resides in. There are 500 employees in the file
Bad solution:
loop
READ EMPLOYEE file
AT END
exit
SELECT STATE
FROM TABLE
WHERE STATE_CODE = :host_variable
goto loop
-this solution will process 500 random reads against the STATE table which has only 50 entries
Good solution:
DECLARE STATE_CUR CURSOR FOR
SELECT STATE
FROM TABLE
OPEN STATE_CUR
loop
FETCH STATE_CUR
INTO :i/o area
IF SQLCODE = +0
store i/o area into w/s table
goto loop
ELSE
exit
empl-loop
READ EMPLOYEE file
AT END
exit
look up state in w/s table
goto empl-loop
-this solution will ONLY process 50 sequential read against STATE table

6. SELECTs before UPDATEs or DELETEs
its not necessary to SELECT row prior to UPDATEs or DELETEs
-SQLCODE will contain a +100 if no UPDATEs or DELETEs were process
-SQLERRD(3) contains the number of rows affected by updates and deletes
SELECTs to determine UPDATE vs INSERT
SQLCODE -803 will be returned for duplicate on inserts
SQLCODE +100 will be returned for no rows updated
Example:
Condition: Add row if none exists for input data, otherwise update row with input data
Bad solution:
SELECT COL1, COL2, COL3, etc.
FROM TABLE
WHERE COL1 = :host_variable
IF SQLCODE = +0
UPDATE TABLE
SET COL2 = :host_variable
WHERE COL1 = :host_variable
ELSE
IF SQLCODE = +100
INSERT INTO TABLE
VALUES(....)
ELSE
error routine.
Good solutions:
INSERT INTO TABLE
VALUES(....)
IF SQLCODE = -803
UPDATE TABLE
SET COL2 = :host_variable
WHERE COL1 = :host_variable
ELSE
error routine

--or--
UPDATE TABLE
SET COL2 = :host_variable
WHERE COL1 = :host_variable
IF SQLCODE = +100
INSERT INTO TABLE
VALUES(....)
ELSE
error routine.

7. Use of variable length columns
-when loading or inserting rows with variable length columns, be sure to update the length field, otherwise it will use the maximum length, which would defeat its whole purpose
Example:
VARCHAR columns are defined as:
01 identifier_1
49 identifier_2 PIC S9(n) COMP. (n is between 1 and 4)
49 identifier_3 PIC X(??).
the actual column length of identifier_3 needs to be placed in identifier_2
-when updating rows with variable length columns, if the VARCHAR column changes in length, the length of the VARCHAR identifier needs to be updated also, otherwise it will take the old length and use it, which means your column could be truncated or padded with spaces depending on your true new length

8. Turn off logging on loads and reorgs
-this will eliminate the need for DB2 to create UNDO/REDO records, therefore reducing the amount of CPU and elapsed time to do the process
-after either a load or reorg an image copy needs to be done
-to turn off logging add the parameter LOG NO to your standard load/reorg control cards