Focal Point
database connection problem

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/1781087331

July 07, 2004, 09:31 PM
<Vipul>
database connection problem
we are using wf5.2.3 I am having the following problem when I try sql pass thru on the database (db2 udb). Any Insights: I have looked at knowledge base and not got any ideas.
I am appending a simple code w/o sql pass thru it is fine as a fiddle but sql pass thru' creates a havoc.


SET SQLENGINE=DB2
-*ENGINE DB2 SET DEFAULT_CONNECTION TMUPIT
ENGINE DB2 SET CONNECTION_ATTRIBUTES r_tmupi
-*SQL DB2 SET CURRENT SQLID = 'tmupi'
-*SQL DB2 SET CONNECTION_ATTRIBUTES r_tmupi/tmupi,tmupi
-*SQL DB2
select TMUPI.NAME from TMUPI.CORORG for fetch only;
TABLE FILE SQLOUT
PRINT *
ON TABLE SAVE AS BANK_NAME
END -RUN
(FOC1517) UNRECOGNIZED COMMAND select TMUPI.NAME from TMUPI.CORORG for fetch
only;
0 ERROR AT OR NEAR LINE 24 IN PROCEDURE MEMFEX FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND
-EXIT
Thanks,

Vipul

This message has been edited. Last edited by: <Mabel>,
July 07, 2004, 10:34 PM
jmdoyl1
We use DB2 8.1 and 5.2.3 as well and have been using the following ( replace variables to match your environment):

ENGINE DB2 SET DEFAULT_CONNECTION &DBNAME
ENGINE DB2
SELECT
...
;
TABLE ON TABLE HOLD AS &VIEWNAME FORMAT ALPHA
END
TABLE FILE &VIEWNAME
...
END

Jon
July 09, 2004, 02:12 PM
<Grzegorz>
Vipul,

The error points that the PASSTHROUGH is not passed to DB2 - the native DB2 SELECT statement is handled as a FOCUS request.
For DB2 I used syntax:

-* Setting the engine here if necessary:
...
-*
SQL SQL
SELECT native DB2 statement
... ;
TABLE
ON TABLE HOLD AS HOLD_FILE
END

and I have never had any problems.
I noticed some issues (but not remember details) when using syntax SQL DB2.

Regards
Grzegorz
December 02, 2004, 05:31 PM
Deepak
Can someone tell me what is the syntax for the same if instead of the DB2 data source to access a Teradata datasource?

Please explain the engine settings that need to be specified also.

Thanks in advance
December 03, 2004, 11:27 AM
HÃ¥kan
This works like a charm using SQL Server in 4.3.6 (have not tested in 5.3):

SET SQLENGINE = SQLMSS
SQL &SQLDB SET USER VismaBusiness
SQL &SQLDB SET SERVER VismaBusiness
-RUN
-*
SQL
SELECT AcNo,Nm
FROM F9999.DBO.AC ;
TABLE FILE SQLOUT
SUM Nm
BY AcNo
ON TABLE HOLD AS ACTEMP FORMAT FOCUS INDEX AcNo
END
-RUN
December 03, 2004, 01:20 PM
Tom Walker
Vipul,
I believe your code is missing the keyword "SQL" before the passthru code.

SET SQLENGINE=DB2
ENGINE DB2 SET CONNECTION_ATTRIBUTES r_tmupi

SQL
select TMUPI.NAME from TMUPI.CORORG for fetch only;