getting -140 when running DB2 query in rexx



IBM's flagship relational database management system

getting -140 when running DB2 query in rexx

Postby prachikhanna » Fri Dec 10, 2010 5:18 pm

I m getting below error while running the code provided.
SQLCODE = -104
SQLSTATE = 42601
SQLERRMC = <END-OF-STATEMENT>:<IDENTIFIER>
ERRMSG = PREPARE STATEMENT FAILED
***

Can someone please let me know the issue. I think it is beacuse of DSNLOAD but i donot know how to find where it is presnt. I have so many libraries mapped to ISPLLIB.



/* db2 starts */                                                       
subsys = DBTG                                                         
qryvar1= "SELECT LENGTH,SCALE FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = '"
qryvar2= tablnm                                                       
qryvar3= "' AND NAME ='"                                               
qryvar4= var1                                                         
qryvar5= "' FETCH FIRST ROW ONLY WITH UR"                             
SQLSTMT= '"'||qryvar1 || qryvar2 || qryvar3 || qryvar4 || qryvar5||'"'
say sqlstmt                                                           
address tso "subcom dsnrexx"                                           
if rc <> 0 then                                                       
   do                                                                 
     x_rc = rxsubcom('add','dsnrexx','dsnrexx')                       
     if x_rc <> 0 then                                                 
       do                                                             
         say 'problem loading '||subsys' environment'                 
         exit 0                                                       
       end                                                             
   end                                                                 
address dsnrexx "connect" subsys                                       
if sqlcode <> 0 then                                                   
do                                                                     
ERRMSG = 'CONNECTION FAILED'                                           
call sqlca                                                             
end                                                                   
address dsnrexx "execsql prepare s1 from :sqlstmt"                     
if sqlcode <> 0 then                                                   
do                                                                     
ERRMSG = 'PREPARE STATEMENT FAILED'                                   
call sqlca                                                             
end                                                                   
address dsnrexx "execsql declare c1 cursor for s1"                     
if sqlcode <> 0 then                                                   
do                                                                     
ERRMSG = 'DECLARE CURSOR FAILED'                                       
call sqlca                                                             
end                                                                   
address  dsnrexx "execsql open c1"                                   
if sqlcode <> 0 then                                                 
do                                                                   
ERRMSG = 'OPEN CURSOR FAILED'                                       
call sqlca                                                           
end                                                                 
address  dsnrexx "execsql fetch c1 into :length,:scale"             
if sqlcode <> 0 then                                                 
do                                                                   
ERRMSG = 'FETCH CURSOR FAILED'                                       
call sqlca                                                           
end                                                                 
address  dsnrexx "execsql close c1"                                 
if sqlcode <> 0 then                                                 
do                                                                   
ERRMSG = 'CLOSE CURSOR FAILED'                                       
call sqlca                                                           
end                                                                 
SQLCA:                                       
  SAY "SQLCODE  = " SQLCODE                   
  SAY "SQLSTATE = " SQLSTATE                 
  SAY "SQLERRMC = " SQLERRMC                 
  SAY "ERRMSG   = " ERRMSG                   
EXIT                                         
prachikhanna
 
Posts: 8
Joined: Fri Dec 10, 2010 4:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: getting -140 when running DB2 query in rexx

Postby NicC » Sat Dec 11, 2010 12:42 am

I do not see you assigning values to variables tablnm and var1.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: getting -140 when running DB2 query in rexx

Postby Akatsukami » Sat Dec 11, 2010 1:28 am

NicC wrote:I do not see you assigning values to variables tablnm and var1.

Uninitialized Rexx variables have their names as their values, so the statement would be valid, although probably incorrect.

We don't use the IBM Rexx/SQL interface here (we use REXXTOOLS, which uses a different syntax). I notice, however, that the querent is enclosing the statement to be prepared in double quotes. Is this needed for DSNREXX?
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: getting -140 when running DB2 query in rexx

Postby NicC » Sat Dec 11, 2010 10:50 am

double quotes. Is this needed for DSNREXX?


I thought I saw my execs using quotes but have just checked and they do not so this could be [part of] the problem
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: getting -140 when running DB2 query in rexx

Postby prachikhanna » Mon Dec 13, 2010 12:31 pm

I have values assigned to TBLNM and var1 variable. I just copy pasted the DB2 part of my rexx. I guess this is because of DSNLOAD lib which i have to specify.
I know the DSNLOAD lib but that is not mapped to ISPLLIB.
I tried allocate command but that is not working.
prachikhanna
 
Posts: 8
Joined: Fri Dec 10, 2010 4:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: getting -140 when running DB2 query in rexx

Postby NicC » Mon Dec 13, 2010 2:49 pm

You haven't declared your cursor - I have a note in my code saying 'always required'
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: getting -140 when running DB2 query in rexx

Postby NicC » Mon Dec 13, 2010 2:51 pm

Oh, you have but it should come BEFORE the PREPARE
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: getting -140 when running DB2 query in rexx

Postby prachikhanna » Wed Dec 15, 2010 3:34 pm

NicC wrote:Oh, you have but it should come BEFORE the PREPARE
I moved my declared cursor before prepare statement.still it fails in prepare statement.
prachikhanna
 
Posts: 8
Joined: Fri Dec 10, 2010 4:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: getting -140 when running DB2 query in rexx

Postby prachikhanna » Wed Dec 15, 2010 4:59 pm

Solved the issue. It was with the qoutes given in the query

SQLSTMT= '"'||qryvar1 || qryvar2 || qryvar3 || qryvar4 || qryvar5||'"'

I removed these qoutes and it is working.

Thanks for every1s tym.
prachikhanna
 
Posts: 8
Joined: Fri Dec 10, 2010 4:53 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post