Hi,

I have a table in DB2 with the following structure.

FIELDNAME ALIAS FORMAT
--------- ----- ------
RPT_TYPE_C E01 A10
PRCS_TS E02 HYYMDm
RPT_DESC_T E03 A200
RPT_REC_CT_Q E04 I11
RPT_URL_T E05 A255
UPDT_TS E06 HYYMDm
UPDT_USER_I E07 A8

I have to insert records into this table; where i am using SQL pass-through in the fex file. I have to run this fex file using Report Caster; but the Report Caster job is getting executed with errors/warnings.

please find below the code i am using in SQL pass-through:

ENGINE DB2 SET DEFAULT_CONNECTION DB21
SQL DB2 PREPARE SQLOUT FOR
INSERT INTO DB2PROD.DC_EXCP_RPT_S (RPT_TYPE_C,PRCS_TS,RPT_DESC_T,RPT_REC_CT_Q,RPT_URL_T,UPDT_TS,UPDT_USER_I) VALUES
('DFDLCR04','2008-12-08 00:26:04.781000','For a specified Food RDC displays FDC Vendors which are not set up as SDA Vendors',130,'\\nicsrv10\devsuply\DFD_REPORTS_INTG\DFD_ROLLOUT\WFReports\DFDLCR04.XLS','2008-12-08 00:26:04.781000','JDBCWEBF');
END

Any kind of help in acheiving this code getting executed successfully is very much appreciated.

Thanks in Advance,

This message has been edited. Last edited by: Narayana,

Can you paste the error message?

Please find the message below given by Report Caster:

BTP1010 Starting worker thread
BTP1020 Starting task: insert_summ
BTP1020 Task type: EDA RPC
BTP1020 Procedure name: dfdlakecity/rpt_summ1
BTP1020 Connecting to server BIMCT01V with static execution id
BTP1020 Executing focexec.
BTP1020 ENGINE DB2 SET DEFAULT_CONNECTION DB21
BTP1020 SQL DB2 PREPARE SQLOUT FOR
BTP1020 -*SELECT * FROM DB2PROD.DC_EXCP_RPT_S
BTP1020 -* INSERT INTO DB2PROD.DC_EXCP_RPT_S (RPT_TYPE_C,PRCS_TS,RPT_DESC_T,RPT_REC_CT_Q,RPT_URL_T,UPDT_TS,UPDT_USER_I) VALUES
BTP1020 INSERT INTO DB2PROD.DC_EXCP_RPT_S VALUES ( DFDLCR04 , 2008-12-08 00:26:04.781000 , Vendors ,130, \\nicsrv10\devsuply\DFD_REPORTS_INTG\DFD_ROLLOUT
BTP1020 -* INSERT INTO DB2PROD.DC_EXCP_RPT_S (RPT_REC_CT_Q) VALUES (121)
BTP1020 -*COMMIT;
BTP1020 END
BTP1020 SET DISTRIBUTE=OFF
BTP1020 Task finished.
BTP1010 No report to distribute.

I don't see an error. No report to distribute makes sense in this context.

No record is being inserted into the table.

The other notification mail says as below:

Schedule ID: S13o0ptor513, Job Description: insert_summ Completed with errors/warnings No report to distribute.

There was no report to distribute; i want to insert the record into the DB2 table, which is not happening.

If you run the focexec outside of RC, does it insert a record?

quote:

ENGINE DB2 SET DEFAULT_CONNECTION DB21
SQL DB2 PREPARE SQLOUT FOR
INSERT INTO DB2PROD.DC_EXCP_RPT_S (RPT_TYPE_C,PRCS_TS,RPT_DESC_T,RPT_REC_CT_Q,RPT_URL_T,UPDT_TS,UPDT_USER_I) VALUES
('DFDLCR04','2008-12-08 00:26:04.781000','For a specified Food RDC displays FDC Vendors which are not set up as SDA Vendors',130,'\\nicsrv10\devsuply\DFD_REPORTS_INTG\DFD_ROLLOUT\WFReports\DFDLCR04.XLS','2008-12-08 00:26:04.781000','JDBCWEBF');
END

Why are you using PREPARE SQLOUT FOR ???

See if this works:

ENGINE DB2 SET DEFAULT_CONNECTION DB21
SQL DB2
INSERT INTO DB2PROD.DC_EXCP_RPT_S (RPT_TYPE_C,PRCS_TS,RPT_DESC_T,RPT_REC_CT_Q,RPT_URL_T,UPDT_TS,UPDT_USER_I) VALUES
('DFDLCR04','2008-12-08 00:26:04.781000','For a specified Food RDC displays FDC Vendors which are not set up as SDA Vendors',130,'\\nicsrv10\devsuply\DFD_REPORTS_INTG\DFD_ROLLOUT\WFReports\DFDLCR04.XLS','2008-12-08 00:26:04.781000','JDBCWEBF');
END

Tom

Tom is right. I do not exactly know the internals of using the "PREPARE SQLOUT FOR" phrase, but it limits the SQL to a Fetch only.

The Insert should work just fine in this format -
SQL DB2
INSERT INTO TBL1
VALUES (A,B,C)
;
TABLE
END

Sandeep Mamidenna

This message has been edited. Last edited by: BlueZone,

Another consideration - when you execute outside of RC, it is using your current credentials. When it is run in RC, it appears that you are using a static execution ID which may or may not have write access to your database.

In any case, there is no resulting report for an SQL INSERT (number of rows updated is not a report) so you will get the "No report to distribute" message. That is not an error. If you are not getting db stats returned try setting PASSRECS=ON in your server profile for the DB2 adapter.

Thanks to everyone!

Removing PREPARE SQLOUT FOR worked out perfectly.