Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
SQLCODE 518 while executing DB2 module.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 166
Location: Coimbatore

PostPosted: Wed Feb 07, 2018 10:46 am    Post subject: SQLCODE 518 while executing DB2 module.
Reply with quote

Hi,

I am trying to build the sql's dynamically via my program and trying to execute but I am getting 518 sqlcode. Could you please help me resolve this.

Details are as follows:
Working storage declaration:
Code:
****************************************************************
*                 SQL CURSORS AND STATEMENTS                     
****************************************************************
*                                                               
       EXEC SQL                                                 
           DECLARE STMT-SELECT STATEMENT                         
       END-EXEC.                                                 
                                                                 
       EXEC SQL                                                 
           DECLARE C1 CURSOR                                     
           FOR STMT-SELECT                                       
       END-EXEC.                                                 


Building the sql:
Code:

INITIALIZE WS-DAILY-SELECT.                         
STRING                                             
 'SELECT  XS_SHIP_NO'           DELIMITED BY SIZE   
        ',XS_VESSEL_NAME'       DELIMITED BY SIZE   
        ',XS_CAPACITY'          DELIMITED BY SIZE   
        ',XS_VESSEL_TYPE'       DELIMITED BY SIZE   
        ',XS_SERVICE_CD'        DELIMITED BY SIZE   
        ',XS_SERVICE_NAME'      DELIMITED BY SIZE   
        ' FROM VEXPORT.TSHIP'   DELIMITED BY SIZE   
        ' WHERE'                DELIMITED BY SIZE   
 INTO WS-DAILY-SELECT                               
 END-STRING.                                       
                                                   
INITIALIZE WS-WHERE-CLAUSE-01                       
STRING                                             
'(XS_SHIP_NO >=:WS-SHIP-NUM'   DELIMITED BY SIZE   
' OR XS_SHIP_NO LIKE ('          DELIMITED BY SIZE 
''''                            DELIMITED BY SIZE   
WS-PERCENT                      DELIMITED BY SIZE   
''''                            DELIMITED BY SIZE   
'|| RTRIM(:WS-SHIP-NUM) ||'     DELIMITED BY SIZE   
''''                            DELIMITED BY SIZE   
WS-PERCENT                      DELIMITED BY SIZE   
'''))'                          DELIMITED BY SIZE   
' AND (XS_VESSEL_NAME LIKE ('    DELIMITED BY SIZE 
''''                            DELIMITED BY SIZE   
WS-PERCENT                      DELIMITED BY SIZE   
''''                            DELIMITED BY SIZE   
'|| RTRIM(:WS-SHIP-NAME) ||'    DELIMITED BY SIZE   
''''                            DELIMITED BY SIZE   
WS-PERCENT                      DELIMITED BY SIZE   
'''))'                          DELIMITED BY SIZE   
' AND (XS_SERVICE_CD LIKE'       DELIMITED BY SIZE 
'(RTRIM(:WS-SHIP-LINE-CODE) ||' DELIMITED BY SIZE   
''''                            DELIMITED BY SIZE   
WS-PERCENT                      DELIMITED BY SIZE   
'''))'                          DELIMITED BY SIZE   
  INTO WS-WHERE-CLAUSE-01   
  END-STRING.               
                             

INITIALIZE WS-ORDER-CLAUSE-01                     
STRING                                           
    'ORDER BY XS_SERVICE_CD' DELIMITED BY SIZE   
   INTO WS-ORDER-CLAUSE-01                       
END-STRING.                                       


Preparing the sql to execute it:
Code:


Working storage declaration:
01  WS-STMTBUF.                                               
    49  WS-STMTLEN                   PIC S9(04) COMP VALUE +0.
    49  WS-STMTTEXT                  PIC X(2500) VALUE SPACES.

Actual piece of code where the prepare happens.
MOVE 2500                        TO WS-STMTLEN   
                                                 
EXEC SQL                                         
    PREPARE STMT-SELECT                           
    FROM :WS-STMTBUF                             
END-EXEC                                         
                                                 
 DISPLAY 'SQLCODE:' SQLCODE                       
                                                 
IF  SQLCODE = +0                                 
OR  SQLCODE = +100                               
    NEXT SENTENCE                                 
ELSE                                             
    MOVE 'BAD SQLCODE IN PREPARE STMT-SELECT'     
      TO VD2140I-PGMMSG                           
END-IF.                                           


After execution:
1. I am able to get the query which I built inside. Could see it in displays I gave. Was unable to copy everything, hence pasted what I could.
Code:

WS-STMTBUF:  SELECT  XS_SHIP_NO,XS_VESSEL_NAME,XS_CAPACITY,XS_VESSEL_TYPE,XS
IP WHERE(XS_SHIP_NO >=:WS-SHIP-NUM OR XS_SHIP_NO LIKE ('%'|| RTRIM(:WS-SHIP-
 RTRIM(:WS-SHIP-NAME) ||'%')) AND (XS_SERVICE_CD LIKE ('%'|| (RTRIM(:WS-SHIP
        ORDER BY XS_SHIP_NO                                                 


I am getting SQLCODE:000000031K. This is when I execute the prepare statement which I mentioned above.
I am getting the below error,
Code:

<H4> SQLCODE -518 Explanation                                                 
                                                                               
  -518 THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT     
                                                                               
  Explanation: One of the following conditions exists:                         
                                                                               
  * The statement named in the EXECUTE statement has not been prepared.       
                                                                               
  * The statement named in the EXECUTE statement identifies a SELECT, or       
    ASSOCIATE LOCATORS statement                                               
                                                                               
  * The statement named in the EXECUTE IMMEDIATE statement identifies a SELECT
      or ASSOCIATE LOCATORS statement.                                         
                                                                               
  System action: The statement cannot be processed.                           
                                                                               
  Programmer response: Ensure that you prepare the statement prior to EXECUTE.
  Also, ensure that the statement you prepare is not a SELECT or VALUES INTO   
  statement.                                                                   
                                                                               
  SQLSTATE: 07003                                                             



Could you please help me resolve this ?. I tried my best to resolve it, but still I am facing this issue.
Back to top
View user's profile Send private message

abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 166
Location: Coimbatore

PostPosted: Wed Feb 07, 2018 10:49 am    Post subject:
Reply with quote

After this prepare statement, I am trying to fetch the data using a cursor where I am unable to perform the fetch. Under programmer response, its mentioned that "ensure that the statement you prepare is not a SELECT", but did not get this statement. Do let me know how could I resolve this.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2019
Location: UK

PostPosted: Wed Feb 07, 2018 4:53 pm    Post subject:
Reply with quote

What? It simply says that you cannot prepare a SELECT statement or a statement that has "VALUES INTO".
Back to top
View user's profile Send private message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 166
Location: Coimbatore

PostPosted: Wed Feb 07, 2018 5:49 pm    Post subject:
Reply with quote

Yes. I dint get that statement. My query has SELECT with WHERE clause. Can I know if I cannot give my PREPARE statement just like what I have given ?. Can't I PREPARE it using SELECT statements. If so is there any other way to dynamically define the SQL's.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1904
Location: NY,USA

PostPosted: Wed Feb 07, 2018 10:59 pm    Post subject:
Reply with quote

Try declaring SELECT with "WITH HOLD" option
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 663
Location: Whitby, ON, Canada

PostPosted: Thu Feb 08, 2018 1:17 am    Post subject:
Reply with quote

You are using Dynamic SQL. One of the rules is that all SELECT statements must be coded as a CURSOR. SELECT......INTO is not allowed, even if you only expect 1 row to be returned.
Back to top
View user's profile Send private message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 166
Location: Coimbatore

PostPosted: Thu Feb 08, 2018 11:25 am    Post subject: Reply to: SQLCODE 518 while executing DB2 module.
Reply with quote

So you meant to say that I should use "DECLARE CURSOR" statements instead of "SELECT ... INTO" statements ?.
Back to top
View user's profile Send private message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 166
Location: Coimbatore

PostPosted: Thu Feb 08, 2018 1:09 pm    Post subject:
Reply with quote

I could see my existing production modules having SELECT statement and they are building the SQL'S dynamically. I do not see that they build only DECLARE CURSOR statement. Please correct me if I am wrong.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1284
Location: Israel

PostPosted: Thu Feb 08, 2018 8:47 pm    Post subject: Re: SQLCODE 518 while executing DB2 module.
Reply with quote

abdulrafi wrote:
...but I am getting 518 sqlcode...
...
I am getting SQLCODE:000000031K. This is when I execute the prepare statement which I mentioned above...

First you receive -312 for the PREPARE, and because of this you receive -518 in following statements.
This is what you have to fix:
Code:
-312                                                                         
                                                                             
 VARIABLE variable-name IS NOT DEFINED OR NOT USABLE                         
                                                                             
 Explanation                                                                 
 ~~~~~~~~~~~                                                                 
                                                                             
 A variable is referenced in the SQL statement, but it either is not defined 
 or cannot be used.                                                           
                                                                             
      variable-name                                                           
          The name of the variable that is referenced in the statement.       
                                                                             
 One of the following conditions exists:                                     
                                                                             
     The variable is not declared.                                           
                                                                             
     The attributes of the variable are inconsistent with its usage in the   
      SQL statement.                                                         
                                                                             
     The host variable was specified in a dynamic SQL statement. Host         
      variables are not valid in dynamic SQL statements.                     
     A host variable array is used in a context in which it is unsupported.   
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Error executing Java program in OSGI ... danik56 CICS 0 Thu Apr 26, 2018 12:16 am
This topic is locked: you cannot edit posts or make replies. Unavailable RECON datasets while exec... abdulrafi JCL & VSAM 10 Fri Apr 06, 2018 12:45 pm
No new posts IMS error U4038 while executing my PL... abdulrafi IMS DB/DC 2 Thu Feb 08, 2018 1:05 pm
No new posts SQLCODE=100 and SQLSTAE 02000 pshongal DB2 12 Fri Jan 19, 2018 10:14 am
No new posts Dump analysis of an assembler module monica1 PL/I & Assembler 4 Tue May 09, 2017 10:05 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us