VISION:Results Interface to DB2 - The Dynamics of Retrieving DB2 Data

Products

• Vision:Results

Issue/Introduction

Running a VISION: Results job in DB2 Dynamic Mode using the DB2 Call Attach Facility.

Resolution

Here's an example of how to run a job in DB2 Dynamic Mode using the DB2 Call Attach Facility. The shaded areas depict the embedded SQL.

//JOBNAME  JOB ...YOUR JOB INFORMATION...
//*
//* THIS JOB RUNS WITH THE DB2 CALL ATTACH FACILITY
//*
//DYLPROC PROC SYSDEV='*',                  * SYSTEM O/P SPOOLING
//        DSKDEV=SYSDA,                     * SYSTEM DISK GENERIC
//        DB2LOAD='IBM.DB2.DSNLOAD',        * IBM DB2 LOAD LIBRARY
//        DYLLOAD='YOUR.RESULTS.LOAD',      * RESULTS LOAD LIBRARY
//        DYNLOAD='INTERFACE.DYNAMIC.LOAD', * INTERFACE DYNAMIC LOAD
//        NEWCNTL='YOUR.SOURCE.LIBRARY',    * RESULTS SOURCE/INFO LIB
//        DB2PARM='AUTHID',                 * SSID, PLAN, AUTHID, LOCID
//        SRTLIB='SYS1.SORTLIB',            * SORT LIBRARY
//        SRTUNIT=SYSDA,                    * SORT WORK DISK UNIT
//        DYLPROG=''                        * RESULTS SOURCE MEMBER
//*
//*********************************************************************
//*
//STEP1    EXEC PGM=DYL280,REGION=4M,COND=(0,NE)
//STEPLIB  DD DSN=&DYNLOAD.,DISP=SHR   <-- INTERFACE DYNAMIC LIBRARY
//         DD DSN=&DYLLOAD.,DISP=SHR   <-- RESULTS LOAD LIBRARY
//         DD DSN=&DB2LOAD.,DISP=SHR   <-- DB2 LOADLIB
//SYSPRINT DD SYSOUT=&SYSDEV
//SYS280R  DD SYSOUT=&SYSDEV
//SORTLIB  DD DSN=&SRTLIB,DISP=SHR
//SORTWK01 DD UNIT=&SRTUNIT,SPACE=(CYL,(2,1))
//SORTWK02 DD UNIT=&SRTUNIT,SPACE=(CYL,(2,1))
//SORTWK03 DD UNIT=&SRTUNIT,SPACE=(CYL,(2,1))
//DYLDBUG  DD SYSOUT=&SYSDEV
//DSNTRACE DD SYSOUT=&SYSDEV
//ABNLIGNR DD DUMMY
//SYSUDUMP DD SYSOUT=&SYSDEV
//SYS004   DD UNIT=&DSKDEV.,SPACE=(TRK,(5,5))
//DB2PARMS DD DSN=&NEWCNTL.(&DB2PARM),DISP=SHR    <-- DB2 SSID, PLAN,
//*
//SYSIN    DD DSN=&NEWCNTL.(&DYLPROG),DISP=SHR
//         PEND
 
//*
//SALSAMP EXEC DYLPROC,DYLPROG=SALSAMP
//DB2PARMS DD *
DB2A     DYLDB2   DSN8610
/*
//SYSIN    DD *
REPORT 78 WIDE
;
; PROGRAM: SALARY COMPARISON
;
; DESCRIPTION: SINGLE TABLE DB2 EXTRACTION REPORT FROM
; DSN8610.EMP USING STANDARD EMBEDDED SQL
; AND HOST VARIABLES FOR SELECTION VALUES.
FILE XFILE DUMMY
WORKAREA
EMPNO 6 CH
FNAME 12 CH
LNAME 15 CH
SALARY 5 PD 2 E
SELSAL 5 PD 2 E VALUE 20000
ON ONE

EXEC SQL DECLARE EMPCSR CURSOR FOR SELECT EMPNO, FIRSTNME, LASTNAME, SALARY FROM DSN8610.EMP WHERE SALARY > :SELSAL ORDER BY LASTNAME ENDEXEC EXEC SQL OPEN EMPCSR ENDEXEC

ENDONE

EXEC SQL FETCH EMPCSR INTO :EMPNO, :FNAME, :LNAME, :SALARY ENDEXEC

IF DYLSQLSTAT EQ 'Y'
 THEN LIST EMPNO, FNAME, LNAME, SALARY
ELSE

EXEC SQL         CLOSE EMPCSR         ENDEXEC

 STOP
ENDIF
T1 'VISION:INTERFACE FOR DB2 - SALARY COMPARISON' WITH 2 AFTER
T1+1 DYLDATE
T1+70 DYLETIME
//