Re: SQL QAQQINI Error - Unable to retrieve query options file.

The query options file is not needed.  But SQL checks for it and will use 
it if a copy in QUSRSYS exists.  That message is just a warning.  If you 
want to create one you can use CRTDUPOBJ of the one in QSYS but there are 
a lot of options in that one you may not want.  So don't just do it for 
the sole reason to get rid of the "unable to retrieve query options file" 
message.
The real problem is the next message, "FR47P457A already exists.".  You 
are trying to run this multiple times from the same job.  Now, either DROP 
TABLE before executing your DECLARE ... or come up with some other method 
of dealing with this.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





fkany@xxxxxxxxxxxxxxxxxx 
Sent by: midrange-l-bounces+rob=dekko.com@xxxxxxxxxxxx
04/08/2005 09:49 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
MIDRANGE-L@xxxxxxxxxxxx
cc

Subject
SQL QAQQINI Error - Unable to retrieve query options file.







I'm trying to use 'DECLARE GLOBAL TEMPORARY TABLE'.
In debug, I found no data in the FR47P457A file after the EXECUTE SQLCMD 
line.

I checked the job log and here are the messages:
================================================
PREPARE of statement SQLCMD completed.
Unable to retrieve query options file.
FR47P457A in QTEMP type *FILE already exists.
=======================================================

I pressed F1 on 'Unable to retrieve query options file' and here is what I 
found:
=================================================================
Message ID . . . . . . :            CPI433A                 Severity . . . 
. . . . :            00
Message type . . . . . :            Information
Date sent  . . . . . . :   04/08/05      Time sent  . . . . . . : 09:27:19

Message . . . . :   Unable to retrieve query options file.
Cause . . . . . :   Unable to retrieve the query options from member 
QAQQINI
  in file QAQQINI in library QUSRSYS for reason code 2.  The reason codes 
and
  their meanings follow:
    1 - Library QUSRSYS was not found.
    2 - File QAQQINI in library QUSRSYS was not found.
=========================================================

QAQQINI exists in QSYS and not in QUSRSYS.  Why is QAQQINI needed, what 
does it do?  Would it hurt anything to copy the QAQQINI object to QUSRSYS?

Here is the SQLRPGLE code:
==========================
 /FREE
   SQLSTRING = 'DECLARE GLOBAL TEMPORARY TABLE FR47P457A AS '        +
               '(SELECT FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, ' +
                       'FHPUDT, FHSCD, FHCCD, FHCCT, FHCZIP '        +
                  'FROM FRL00144 WHERE FHDDAT >= ' + %CHAR(FDATE)    +
                                 ' AND FHDDAT <= ' + %CHAR(TDATE)    +
                                 ') WITH DATA';
 /END-FREE

      *
      * Prepare SQL Command String
C/EXEC SQL
C+     PREPARE SQLCMD FROM :SQLSTRING
C/END-EXEC
      *
      * Execute SQL Command String
C/EXEC SQL
C+     EXECUTE SQLCMD USING :FDATE, :TDATE
C/END-EXEC
      *
==============================

I appreciate all your help.  Thanks.

Frank


-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.