I used the following code to create a dynamic select code to reselect from another table. It worked in version 5.2.1 of developer Studio, but no longer works in version 7.1. For some reason it can no longer find the dynamically created program. I changed the connect string to match the new connection requirements for DB2 (Udb on AIX). The rest is the same. The error I get back is [FOC227] THE FOCEXEC PROCEDURE CANNOT BE FOUND: TBLCOLS1FOCEXEC.

-DEFAULT &prf_fnkey=246
-DEFAULT &prf_null='N'
-DEFAULT &dt_flag='N'
-DEFAULT &prf_column='CL53_ORGANIZATION'
-DEFAULT &prf_colval=964
-DEFAULT &prf_prname='test'
-DEFAULT &prf_date=1234
ENGINE DB2 SET DEFAULT_CONNECTION infadev
SQL DB2 PREPARE SQLOUT FOR
SELECT iv1.DYN_SELECT FROM
[select CASE WHEN T1.source_field_number =1
THEN 'SELECT '||T1.source_field_name||','
WHEN T1.source_field_number<T4.MAXID
THEN T1.source_field_name||','
WHEN T1.source_field_number=T4.MAXID
THEN T1.source_field_name||' FROM lndadmin.'||T1.source_name
END AS DYN_SELECT,
t1.source_field_number,
T2.PRFL_RUN_KEY,
T4.MAXID
from infarep.rep_all_source_flds t1,
PROFILER.DPR_PRFL_RUN_DTLS T2,
PROFILER.DPR_PRFL_OJ_FN T3,
[SELECT
CASE WHEN SOURCE_FIELD_NUMBER <8 THEN 7
ELSE
SOURCE_FIELD_NUMBER END AS MAXID,
T2.PRFL_RUN_KEY,
t3.prfl_ver_key,
t3.function_key,
SOURCE_NAME1,
MASTER_COLNAME_1
FROM infarep.rep_all_source_flds T1,PROFHIST.DPR_PRFL_RUN_SUCCESS T2,
[select
function_key,
prfl_ver_key,
source_name1,
source_name2,
substr[colname1,1,locate[',',colname1]-1] as master_colname_1 ,
substr[colname1,locate[',',colname1]+1 ,length[colname1]-locate[',',colname1]] as detail_colname_1
from PROFHIST.dpr_prfl_oj_fn] T3
WHERE T1.SUBJECT_AREA=T2.PRFL_FOLDER_NAME AND
T2.PRFL_VER_KEY=T3.PRFL_VER_KEY AND
T1.source_name=T3.source_NAME1 AND
T1.source_field_NAME =T3.MASTER_COLNAME_1] T4
WHERE T1.SUBJECT_AREA=T2.PRFL_FOLDER_NAME AND
T2.PRFL_VER_KEY=T3.PRFL_VER_KEY AND
T1.source_name=T3.SOURCE_NAME1 AND
T2.PRFL_RUN_KEY=196 and
t3.function_key=246 and
t1.source_field_number <=T4.MAXID and
T2.PRFL_RUN_KEY=T4.PRFL_RUN_KEY and
t2.prfl_ver_key=t4.prfl_ver_key and
t3.function_key=t4.function_key
ORDER BY T1.source_field_number] iv1;
END
TABLE FILE SQLOUT
PRINT *
ON TABLE SAVE AS TBLCOLS1
END
-RUN
ENGINE DB2 SET DEFAULT_CONNECTION LNDDEV
SQL DB2 PREPARE SQLOUT FOR
-INCLUDE TBLCOLS1
WHERE CL53_ORGANIZATION=964;
END
TABLE FILE SQLOUT
PRINT *
ON TABLE SUBHEAD
"Informatica Profile: &prf_prname"
"Profile Date: &prf_date"
"Invalid Values for Column: &prf_column"
HEADING
""
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT EXL2K
-*ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
-INCLUDE reptsty2.sty
END

I can't say for sure (I don't have 7.1 yet), but this may be due to "code tightening".

As far as I know, for dynamically created fexes, you need to allocate the file with a .fex suffix. Depending on your server platform, a FILEDEF or DYNAM ALLOC would be necessary:

FILEDEF TBLCOLS1 DISK TBLCOLS1.fex
-RUN

TABLE FILE SQLOUT
PRINT *
ON TABLE SAVE AS TBLCOLS1
END
-RUN

ENGINE DB2 SET DEFAULT_CONNECTION LNDDEV
-RUN

SQL DB2 PREPARE SQLOUT FOR
-INCLUDE TBLCOLS1
WHERE CL53_ORGANIZATION=964;
END
-RUN

Interestingly enough, I executed enough of my code to make sure it created the save file, which it did successfully. I also implmented some of the code enhancements you suggested but still got the same error.

-DEFAULT &prf_fnkey=246
-DEFAULT &prf_null='N'
-DEFAULT &dt_flag='N'
-DEFAULT &prf_column='CL53_ORGANIZATION'
-DEFAULT &prf_colval=964
-DEFAULT &prf_prname='test'
-DEFAULT &prf_date=1234
FILEDEF TBLCOLS1 DISK TBLCOLS1.fex
-RUN
ENGINE DB2 SET DEFAULT_CONNECTION infadev
SQL DB2 PREPARE SQLOUT FOR
SELECT iv1.DYN_SELECT FROM
[select CASE WHEN T1.source_field_number =1
THEN 'SELECT '||T1.source_field_name||','
WHEN T1.source_field_number<T4.MAXID
THEN T1.source_field_name||','
WHEN T1.source_field_number=T4.MAXID
THEN T1.source_field_name||' FROM lndadmin.'||T1.source_name
END AS DYN_SELECT,
t1.source_field_number,
T2.PRFL_RUN_KEY,
T4.MAXID
from infarep.rep_all_source_flds t1,
PROFILER.DPR_PRFL_RUN_DTLS T2,
PROFILER.DPR_PRFL_OJ_FN T3,
[SELECT
CASE WHEN SOURCE_FIELD_NUMBER <8 THEN 7
ELSE
SOURCE_FIELD_NUMBER END AS MAXID,
T2.PRFL_RUN_KEY,
t3.prfl_ver_key,
t3.function_key,
SOURCE_NAME1,
MASTER_COLNAME_1
FROM infarep.rep_all_source_flds T1,PROFHIST.DPR_PRFL_RUN_SUCCESS T2,
[select
function_key,
prfl_ver_key,
source_name1,
source_name2,
substr[colname1,1,locate[',',colname1]-1] as master_colname_1 ,
substr[colname1,locate[',',colname1]+1 ,length[colname1]-locate[',',colname1]] as detail_colname_1
from PROFHIST.dpr_prfl_oj_fn] T3
WHERE T1.SUBJECT_AREA=T2.PRFL_FOLDER_NAME AND
T2.PRFL_VER_KEY=T3.PRFL_VER_KEY AND
T1.source_name=T3.source_NAME1 AND
T1.source_field_NAME =T3.MASTER_COLNAME_1] T4
WHERE T1.SUBJECT_AREA=T2.PRFL_FOLDER_NAME AND
T2.PRFL_VER_KEY=T3.PRFL_VER_KEY AND
T1.source_name=T3.SOURCE_NAME1 AND
T2.PRFL_RUN_KEY=196 and
t3.function_key=246 and
t1.source_field_number <=T4.MAXID and
T2.PRFL_RUN_KEY=T4.PRFL_RUN_KEY and
t2.prfl_ver_key=t4.prfl_ver_key and
t3.function_key=t4.function_key
ORDER BY T1.source_field_number] iv1;
END
TABLE FILE SQLOUT
PRINT *
ON TABLE SAVE AS TBLCOLS1
END
-RUN
ENGINE DB2 SET DEFAULT_CONNECTION LNDDEV
-RUN
SQL DB2 PREPARE SQLOUT FOR
-*INCLUDE /app/ibi_dev/ibi/srv52/wfs/edatemp/tblcols3.fex
-INCLUDE TBLCOLS1
WHERE CL53_ORGANIZATION=964;
END
-RUN
TABLE FILE SQLOUT
PRINT *
ON TABLE SUBHEAD
"Informatica Profile: &prf_prname"
"Profile Date: &prf_date"
"Invalid Values for Column: &prf_column"
HEADING
""
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT EXL2K
-*ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
-INCLUDE reptsty2.sty
END