如何通过IKJEFT01 Utility运行动态SQL? [英] How to run dymanic SQL through IKJEFT01 Utility?

问题描述

在执行IKJEFT01时如何在SQL查询中传递参数?示例:

How can we pass parameters in SQL query while executing IKJEFT01? Example:

//UNLOAD    EXEC PGM=IKJEFT01
//SYSTSPRT  DD SYSOUT=*
//SYSTSIN   DD *  DSN SYSTEM(DB2X) RETRY(120)
RUN PROGRAM(DSNTIAUL)
PLAN(DSNTIAUL) -
PARM('SQL')
END
//SYSPRINT  DD SYSOUT=*
//SYSUDUMP  DD SYSOUT=D
//SYSREC00  DD DSN=FCSTN.AK.XXXXXX,
//          DISP=(NEW,CATLG,DELETE),
//SYSPUNCH  DD SYSOUT=*                                           
//SYSIN     DD *
SELECT * FROM ABC.DEF WHERE XYZ='999'   
/*

我不想在sql查询中将值赋予为"999"，而是要通过变量传递值. 如何做到这一点?请帮助...！

Instead of giving value in sql query as '999' i want to pass the value through a variable. How this can be done? Plz help...!

推荐答案

在执行UNLOAD步骤之前，有一个单独的步骤将SQL的可变行写入临时文件.然后将该临时文件与SQL不变的行连接起来.

Have a separate step, prior to your UNLOAD step, that writes the variable line(s) of SQL to a temporary file. Then concatenate that temporary file with the unchanging lines of SQL.

徒手...

//SETVAR   EXEC PGM=PRM2FILE,PARM='WHERE XYZ=''&VAL'''
//SYSPRINT DD  SYSOUT=*
//OUTPUT01 DD  DISP=(NEW,PASS,DELETE),
//             LRECL=80,
//             AVGREC=U,
//             RECFM=FB,
//             SPACE=(80,(1,1),RLSE)
//*
//UNLOAD    EXEC PGM=IKJEFT01
//SYSTSPRT  DD SYSOUT=*
//SYSTSIN   DD *  DSN SYSTEM(DB2X) RETRY(120)
RUN PROGRAM(DSNTIAUL)
PLAN(DSNTIAUL) -
PARM('SQL')
END
//SYSPRINT  DD SYSOUT=*
//SYSUDUMP  DD SYSOUT=D
//SYSREC00  DD DSN=FCSTN.AK.XXXXXX,
//          DISP=(NEW,CATLG,DELETE),
//SYSPUNCH  DD SYSOUT=*                                           
//SYSIN     DD *
SELECT * FROM ABC.DEF
//          DD  DISP=(OLD,PASS),DSN=*.SETVAR.OUTPUT01
//*

......其中PRM2FILE是您编写的程序，用于获取parm字段中的内容并将其写入OUTPUT01 DD.

...where PRM2FILE is a program you write to take whatever is in the parm field and write it to the OUTPUT01 DD.

为了达到您要实现的目标，我们做了很多事情.

We do this a lot to accomplish the same goal you are trying to achieve.

这篇关于如何通过IKJEFT01 Utility运行动态SQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！