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,
Prod: WebFOCUS v7.1.4 OS: Windows Outputs: Excel, HTML, Pdf
December 08, 2008, 03:46 AM
harsha.g
Can you paste the error message?
Version: WebFOCUS 7.6.6 O.S. WebFOCUS is mounted on Unix Output Report: HTML, Excel, CSV, PDF and TXT
December 08, 2008, 03:50 AM
Narayana
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.
Prod: WebFOCUS v7.1.4 OS: Windows Outputs: Excel, HTML, Pdf
December 08, 2008, 09:09 AM
GinnyJakes
I don't see an error. No report to distribute makes sense in this context.
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 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 MamidennaThis message has been edited. Last edited by: BlueZone,
------------------------------------------------------------------------------------------------- Blue Cross & Blue Shield of MS WF.76-10 on (WS2003 + WebSphere) / EDA on z/OS + DB2 + MS-SQL MRE, BID, Dev. Studio, Self-Service apps & a dash of fun !!
December 09, 2008, 12:25 PM
Darin Lee
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.
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
December 11, 2008, 02:16 AM
Narayana
Thanks to everyone!
Removing PREPARE SQLOUT FOR worked out perfectly.
Prod: WebFOCUS v7.1.4 OS: Windows Outputs: Excel, HTML, Pdf