To use FTP to perform SQL queries, bind a plan that allows FTP to invoke the package EZAFTPMQ in collection EZAFTPMQ, and grant execution privileges for that plan to PUBLIC. You can specify the name of the plan using the DB2PLAN keyword in FTP.DATA, or the default is EZAFTPMQ. This FTP facility performs only SELECT operations on the DB2® tables; it does not perform UPDATE, INSERT, or DELETE operations.
Requirement: If secondary authorization
for SQL queries is required, you must modify the DSN3SATH sample exit
shipped by DB2. The exit returns
the primary AUTHID for requests that originate from the FTP server.
The following sample job is provided in the FTOEBIND member of the SEZAINST data set; you can use it to enable the FTP server and client to perform SQL queries.
//FTPSETUP JOB FTPSETUP,
// CLASS=A,
// NOTIFY=&SYSUID
//**************************************************************
//*
//* File name: tcpip.SEZAINST(FTOEBIND)
//* SMP/E distribution name: EZAFTPAB
//*
//* Licensed Materials - Property of IBM
//* This product contains "Restricted Materials of IBM"
//* 5647-A01 Copyright IBM Corp. 1997, 2011
//* All rights reserved.
//* US Government Users Restricted Rights -
//* Use, duplication or disclosure restricted by GSA ADP Schedule
//* Contract with IBM Corp.
//* See IBM Copyright Instructions.
//*
//* This JCL binds a plan that allows the application to ability the
//* invoke the package EZAFTPMQ in collection EZAFTPMQ.
//* The JCL allows execution of the plan EZAFTPMQ by PUBLIC.
//*
//* The FTP server and client use this plan. (See
//* Usage note #7)
//*
//*
//* Usage notes:
//*
//* 1. You must execute this job from a user ID that has
//* the authority to bind the EZAFTPMQ plan.
//*
//* 2. Change the STEPLIB DD statement in the FTPBIND and
//* FTPGRANT steps to reflect the DB2 DSNLOAD data set.
//*
//* 3. Change the DB2 sybsystem name in the FTPBIND and
//* FTPGRANT steps from SYSTEM(xxx) to the
//* installation defined DB2 subsystem name.
//*
//* 4. Change the library parameter in the FTPBIND step from
//* TCPIP.SEZADBRM to the installation defined TCPIP
//* SEZADBRM library.
//*
//* 5. Change the plan name in the FTPGRANT step from
//* DSNTIAYY to reflect the plan associated with the
//* program DSNTIAD.
//*
//* 6. Change the library parameter in the FTPGRANT step
//* from xxxxxx.RUNLIB.LOAD to reflect the library
//* where the DSNTIAD program resides.
//*
//* 7. You can bind the DBRM to a package and a plan name
//* other than EZAFTPMQ by changing the plan specified
//* in the FTPBIND and FTPGRANT steps.
//*
//**************************************************************
//FTPBIND EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN=xxxxxx.DSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(xxx)
BIND CURRENTDATA(NO) -
EXPLAIN(NO) -
ISOLATION(CS) -
LIBRARY('TCPIP.SEZADBRM') -
MEMBER(EZAFTPMQ) -
PACKAGE(EZAFTPMQ) -
RELEASE(COMMIT) -
VALIDATE(RUN)
BIND ACQUIRE(USE) -
ACTION(REPLACE) -
CACHESIZE(1024) -
CURRENTDATA(NO) -
EXPLAIN(NO) -
ISOLATION(CS) -
NODEFER(PREPARE) -
PKLIST(EZAFTPMQ.EZAFTPMQ) -
PLAN(EZAFTPMQ) -
RELEASE(COMMIT) -
VALIDATE(RUN) -
RETAIN
END
//*
//FTPGRANT EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN=xxxxxx.DSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(xxx)
RUN PROGRAM(DSNTIAD) -
PLAN(DSNTIAYY) -
LIBRARY('xxxxxx.RUNLIB.LOAD')
END
//SYSIN DD *
GRANT EXECUTE ON PLAN EZAFTPMQ TO PUBLIC;
//*