DSNTEP2 is a sample DB2 program that allows you to dynamically execute SQL statements in batch.

//STEP01 EXEC PGM=IKJEFT01

//STEPLIB DD DSN=D0977.DB2.DBT1.DSNLOAD,DISP=SHR

//DBRMLIB DD DISP=SHR,DSN=D0977.DSN610.SDSNSAMP

//SYSTSPRT DD SYSOUT=*

//SYSPRINT DD SYSOUT=*

//SYSUDUMP DD SYSOUT=*

//SYSTSIN DD *

DSN SYSTEM(DBT1)

RUN PROGRAM(DSNTEP2) PLAN(DSNTEP61) -

LIB('D0977.DB2.DBT1.RUNLIB')

END

//*

//SYSIN DD *

SELECT CUST_NBR, CR_GRP

FROM DBAADMIN.TCO88002

WHERE A_BAL > 1000.00;

SELECT AVG(BAL_AMOUNT) FROM DBAADMIN.TCO88002;

PAGE 1

***INPUT STATEMENT:

SELECT CUST_NBR, CR_GRP

FROM DBAADMIN.TCO88002

WHERE BAL_AMOUNT > 1000.00;

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

| CUST_NBR | CR_GRP |

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

1_| 1030856 | 79 |

2_| 1076111 | 79 |

3_| 1255482 | 79 |

4_| 1397113 | 79 |

5_| 1401952 | 79 |

6_| 1425506 | 79 |

7_| 1458644 | 79 |

8_| 1469497 | 43 |

9_| 1495795 | 79 |

10_| 1522938 | 46 |

11_| 1734854 | 79 |

12_| 1851676 | 46 |

PAGE 1

***INPUT STATEMENT:

SELECT AVG(BAL_AMOUNT) FROM DBAADMIN.TCO88002;

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

| |

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

1_| 4504.25495335 |

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

SUCCESSFUL RETRIEVAL OF 1 ROW(S)

Note:

1. Each SQL statement should end with a semi-colon (;)

You may change the terminator to something other that then semi-colon (see other control

Statements below)

2. You may execute more than one SQL statement
3. Output report is limited to 133 characters per page

If report spans more that 133 characters, it will place a column that does not fit onto a second page.

4. Use -- (two hyphens) for comments

Examples:

--Average Balance Report

--SELECT AVG(BAL_AMOUNT) FROM DBAADMIN.TCO88002;

5. DSNTEP2 only reads the first 72 character of SYSIN

1. Statement terminator

--#SET TERMINATOR value

example: --#SET TERMINATOR +

SELECT CUST_NBR, CR_GRP FROM DBAADMIN.TCO88002+

2. Limit number of rows fetched

--#SET ROWS_FETCH nn

example: --#SET ROWS_FETCH 10

SELECT CUST_NBR, CR_GRP FROM DBAADMIN.TCO88002;

3. Limit number of rows sent to output

PAGE 1

--#SET TERMINATOR +

--#SET ROWS_FETCH 10

--#SET ROWS_OUT 05

***INPUT STATEMENT:

SELECT CUST_NBR, CR_GRP

FROM DBAADMIN.TCO88002

WHERE BAL_AMOUNT > 1000.00+

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

| CUST_NBR | CR_GRP |

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

1_| 1030856 | 79 |

2_| 1076111 | 79 |

3_| 1255482 | 79 |

4_| 1397113 | 79 |

5_| 1401952 | 79 |

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

SUCCESSFUL RETRIEVAL OF 10 ROW(S)

PAGE 1

1. Since ROWS_OUT was set to 5, only the first 5 rows are sent to the output. But you will notice that