I wrote a simple fex to execute me some SQL on our DB2/400. Executing basic queries works fine, but as soon as I attempt to format them I'm getting strange errors...

What works is this:

APP HOLD SQLREPORTS

ENGINE DB2 SET DEFAULT_CONNECTION SQL400
SQL
&QUERY
END
-RUN

But if, in an attempt to format the output, I change that so that the output is passed on to an SQLOUT table:

APP HOLD SQLREPORTS

ENGINE DB2 SET DEFAULT_CONNECTION SQL400
SQL DB2 PREPARE SQLOUT FOR
&QUERY
END
-RUN

TABLE FILE SQLOUT
PRINT *
END
-RUN

I get this:

 APP HOLD SQLREPORTS
 ENGINE DB2 SET DEFAULT_CONNECTION SQL400
 SQL DB2 PREPARE SQLOUT FOR
 SELECT * FROM caldat8 WHERE CALYEA = 11 AND CALMNT = 1;
 END
 -RUN
 (FOC1261) PHYSICAL INTERFACE NOT FOUND FOR SUFFIX DB2. MODULE NAME : RRSET
 (FOC1262) THE FOLLOWING NATIVE ERROR MESSAGE(S) CAPTURED :
 (FOC1260) SearchPath: failed to find C:\ibi\DEVSTU~1\srv76\home\bin\rrset.dll
 (FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF)
 (FOC1488) SQL INTERFACE HAS NOT BEEN INSTALLED OR COULD NOT BE INITIALIZED
 : Physical interface not found for SUFFIX=DB2     . Module name: RRSET
 (FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF)
 (FOC1488) SQL INTERFACE HAS NOT BEEN INSTALLED OR COULD NOT BE INITIALIZED
 : Physical interface not found for SUFFIX=DB2     . Module name: RRSET
 (FOC1479) ERROR CONNECTING TO SQL DATABASE
 TABLE FILE SQLOUT
 PRINT *
 END
 -RUN
 (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
 BYPASSING TO END OF COMMAND

Can anyone explain what is going on here and how to fix this?

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

Since you do not specify the engine ("SQL SELECT..."), the first SQL request actually uses the WebFOCUS metadata, which appears to work.

You specify the SQL engine in the second ("SQL DB2 SELECT..."), and it appears your DevStudio reporting server is not configured correctly (though I have not seen messages like these).

Even though you set the default connection, you also must specify the engine type in your SQL statement. An alternative to SQLOUT is TABLE ON TABLE..., something like this:

ENGINE DB2 SET DEFAULT_CONNECTION &CONNECTION_NAME

SQL DB2
SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES
ORDER BY TABSCHEMA, TABNAME
;
TABLE ON TABLE HOLD AS HOLD01
END
-RUN

Thanks for that Francis!

I found that I can execute my queries from the command console, provided I select the server with the DB2 connector as the location to run the queryfrom (I also just noticed a spelling error in the contents of that dropdown, where it says "WebFOCUS enviroments").

That kind of throws a stick in the wheels for creating an easy to access query tool from within WebFOCUS...

If you can run the SQL from the command console of the reporting server console, you should be able to run it from a fex on that same server. You can access the fexes in the application directories directly from the reporting server console - try running it from the Procedures link.

quote:

Originally posted by Francis Mariani:
If you can run the SQL from the command console of the reporting server console, you should be able to run it from a fex on that same server.

That's exactly what I was trying to circumvent.
A fex to execute arbitrary SQL statements does not belong on a production server IMHO...

I'm trying to have this SQL query "tool" on my local development environment, but it would appear that's impossible then.

I guess I should look for a 3rd party DB2/400 query tool with good copy/paste support (should be able to handle different line-ending styles, for example).

quote:

A fex to execute arbitrary SQL statements does not belong on a production server IMHO...

Wrong. Quite often a business problem is solved better with SQL passthru statements than with WebFOCUS code.