Folks,

I've been accessing a Lotus Notes Domino data source using the Lotus Notes adapter supplied by IBI and native FOCUS commands. However the response is painfully slow - over a minute to retrieve 7000 small records.

So I thought I might try SQL passthrough as an experiment. SQL is new to me and although I've played with it a bit in the past, it hasn't been within the WebFOCUS sphere.

Here is what I have so far and the error I get. I don't even know if this is possible, but I though I would give it a shot anyway:

SQL SQLJDBC PREPARE SQLOUT FOR
SELECT * FROM SALESACTIVITY
;
TABLE FILE SQLOUT
PRINT *
BY COMPANYCODE
ON TABLE HOLD AS HOLD1
END
-RUN

With this I get:

(FOC1400) SQLCODE IS 33554456 (HEX: 02000018) XOPEN: 94200
: (33554456) [94200]
L (FOC1405) SQL PREPARE ERROR.

This message has been edited. Last edited by: George Patton,

One thing that you should be careful in FEX procedures with SQLOUT-s are the ";" they always cause some errors.

Also I your code needs additional END what closes SQLOUT.

  
SQL SQLJDBC PREPARE SQLOUT FOR
SELECT * FROM SALESACTIVITY
END
TABLE FILE SQLOUT
PRINT *
BY COMPANYCODE
ON TABLE HOLD AS HOLD1
END
-RUN

Another thing that you might need to include if you have multiple database connections that make use of the SQLJDBC adapter (is that even a separate adapter?), is to specify which database to use, eg.:

ENGINE SQLJDBC SET DEFAULT_CONNECTION &LOTUSDOMINOCONNECTORNAME

I'm interested in seeing how your experiment progresses, as some of our users have shown some interest to report on information from our Lotus Domino (and Notes) server.

Thanks for the suggestions, but I don't think they are going to work - and maybe, as of today, they aren't necessary.

I don't know what is different - prior to right now the NotesJDBC adapter did work, but it was just very slow - and it still is with some queries. But with others it is OK, speedwise. I'm mystified.

For example:

-* Lotus Notes Database
TABLE FILE QUOTEITEMS_LN
COUNT UNID
SUM PQTY
BY CCODE
END

-* Time to process and display 6283 records: 1 minute 5 secoonds

But:

-SET &TODAY=&YYMD;
-*SET &STARTDATE=AYMD(&TODAY, -7, 'I8YYMD');

DEFINE FILE SALESACTIVITY
ACTIVE_SINCE/YYMD=DATECVT(&STARTDATE, 'I8YYMD', 'YYMD');
CODELEN/I1=LENV(COMPANYCODE, 'I1');
COMPCODE2/A12=IF CODELEN EQ 3 THEN '   ' | COMPANYCODE | ' Rep:' ELSE
             IF CODELEN EQ 4 THEN '  ' | COMPANYCODE | ' Rep:' ELSE
			 IF CODELEN EQ 5 THEN ' ' | COMPANYCODE | ' Rep:' ELSE COMPANYCODE | ' Rep:';
BLANKLINE/A1=' ';
FIRSTNAME/A10=GETTOK(DOCAUTHOR, 30,  1, ' ', 10, FIRSTNAME);
NEXTNAME/A10=GETTOK(DOCAUTHOR,  30,  2, ' ', 10, NEXTNAME);
LASTNAME/A10=GETTOK(DOCAUTHOR,  30, -1, ' ', 10, LASTNAME);
FIRSTINITIAL/A1=EDIT(FIRSTNAME, '9$$$$$$$$$');
NEXTINITIAL/A1=EDIT(NEXTNAME, '9$$$$$$$$$');
LASTINITIAL/A1=EDIT(LASTNAME, '9$$$$$$$$$');
SHORTNAME/A3=IF NEXTNAME EQ LASTNAME THEN FIRSTINITIAL | LASTINITIAL ELSE FIRSTINITIAL | NEXTINITIAL | LASTINITIAL;
END

TABLE FILE SALESACTIVITY
SUM
     BLANKLINE AS '' OVER
     SALESACTIVITY.SALESACTIVITY.BODY AS '' OVER
     BLANKLINE AS ''
BY  SALESACTIVITY.SALESACTIVITY.SHORTNAME NOPRINT
BY  SALESACTIVITY.SALESACTIVITY.COMPANYCODE NOPRINT
BY  SALESACTIVITY.SALESACTIVITY.ACTDATE NOPRINT
BY  SALESACTIVITY.SALESACTIVITY.ACTIVITYTYPE NOPRINT

ON SALESACTIVITY.SALESACTIVITY.SHORTNAME SUBHEAD
"Sales Activity report for <SALESACTIVITY.SALESACTIVITY.DOCAUTHOR "
"Activity since <ACTIVE_SINCE "
"Report Date <+0>&DATEtrMDYY "

ON SALESACTIVITY.SALESACTIVITY.ACTDATE SUBHEAD
"<SALESACTIVITY.SALESACTIVITY.COMPANYCODE                  <SALESACTIVITY.SALESACTIVITY.COMPANYNAME  "
"<ACTDATE                  Type: <ACTIVITYTYPE "
"                               Subject: <SUBJECT "
"                        Chemo Rep: <OURREPNAME"
"                        <COMPCODE2 <COMPANYREPNAME "
ON TABLE SUBFOOT
" "
"This report runs from the file &FOCFEXNAME "
WHERE SALESACTIVITY.SALESACTIVITY.ACTDATE GE &STARTDATE
AND SALESACTIVITY.SALESACTIVITY.ACTDATE LE &ENDDATE;
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL07 BYTOC
ON TABLE SET STYLE *
-* STYLE STATEMENTS
END

-* Time to process and display 10730 spreadsheet lines with an average of 5 lines of text in each (I'd guess at least 1000 times the data transfer of the first query above): 25 seconds

So a more complex query against a much bigger database runs way faster than a very simple query against a smaller database.

I've had a case opened for some time now and no useful suggestions have emanated from New York.

Here is some (maybe) useful reading for java access to Domino databases:

http://www.ibm.com/developerwo...ccess_pt1/index.html