IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

REXX-DB2 with SQLDA


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Susanta

Active User


Joined: 17 Nov 2009
Posts: 126
Location: India

PostPosted: Mon Dec 31, 2012 11:35 am
Reply with quote

Hi

I am trying to write a db2 sql cursor to get data from db2 table using rexx.

Code:
ADDRESS TSO "SUBCOM DSNREXX"             
IF RC THEN                               
DO                                       
  S_RC=RXSUBCOM('ADD','DSNREXX','DSNREXX')
END                                       
ADDRESS DSNREXX                           
"CONNECT xxxx"                           
IF SQLCODE <> 0 THEN                     
DO                                       
  SAY 'UNABLE TO CONNECT xxxx'           
END                                       
p='SELECT C1,C2 FROM TBL1 WHERE  C3 = ?'

mysqlda.sqld=1                                         
mysqlda.1.sqltype=496                                 
/* rec1 is coming from a i/p file which is not shown*/
mysqlda.1.sqldata=substr(rec1.1,2,10)                 
mysqlda.1.sqlind=0                                     

"EXECSQL PREPARE S1 using descriptor :mysqlda FROM :P "
       
"EXECSQL DECLARE CURSOR C1 FOR S1"                     

"EXECSQL OPEN C1 "           
                                                       
"EXECSQL FETCH C1 using DESCRIPTOR :OUTSQLDA"         

If SQLCODE = 0 Then Do
  say OUTSQLDA.1.SQLDATA
  say OUTSQLDA.2.SQLDATA
end

"EXECSQL CLOSE C1 "

Here c2 and c3 are of datatype smallint.and c1 is character.
Its giving some error, I know that there is syntax error. Could you please help.

Thanks
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Mon Dec 31, 2012 12:57 pm
Reply with quote

Can you please show us the output with trace on?

Is your DB2 SUBSYSTEM xxxx???
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Mon Dec 31, 2012 2:59 pm
Reply with quote

Quote:
Its giving some error, I know that there is syntax error. Could you please help.


unfortunately it 's monday and psychic day is wednesday

so You have the alternative to ...
wait a couple of days
or ...
post a more intelligent description of the error You received

for a very good handler of rexx db2 errors see ...
www.ibmmainframes.com/viewtopic.php?t=59952&highlight=idug
Back to top
View user's profile Send private message
Susanta

Active User


Joined: 17 Nov 2009
Posts: 126
Location: India

PostPosted: Mon Dec 31, 2012 5:29 pm
Reply with quote

Hi Pandora

xxxx is just a place holder, the actual is different.
i normally replace sensitive data before posting with some dummy names.
Thanks
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Mon Dec 31, 2012 5:30 pm
Reply with quote

Hi,

Dont you see you just gave a half answer?
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Mon Dec 31, 2012 5:53 pm
Reply with quote

apart lame excuses You still did not post anything useful icon_evil.gif

nowhere the name of the DB2 subsystem is regarded as sensitive information

and .... we are not IT dentists
getting info should not be as hard as pulling a tooth icon_cool.gif
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Mon Dec 31, 2012 6:13 pm
Reply with quote

Quote:
getting info should not be as hard as pulling a tooth


Getting info is as hard as making TS spit his tooth himself icon_eek.gif
Back to top
View user's profile Send private message
Susanta

Active User


Joined: 17 Nov 2009
Posts: 126
Location: India

PostPosted: Wed Jan 02, 2013 3:28 pm
Reply with quote

Hi All

I am not confident in using sqlda , i belive we need sqlda when there is a smallint/ integer type db2 column we use in the query. At the moment I dont have enough time to learn further about sqlda. which i will try to learn in future.

I have found a workaround to avoid use of sqlda for this scenario.
I have used scaler funtion CHAR to convert the c2 and c3 to char and the below code is working now.

Code:
ADDRESS TSO "SUBCOM DSNREXX"             
IF RC THEN                               
DO                                       
  S_RC=RXSUBCOM('ADD','DSNREXX','DSNREXX')
END                                       
ADDRESS DSNREXX                           
"CONNECT xxxx"                           
IF SQLCODE <> 0 THEN                     
DO                                       
  SAY 'UNABLE TO CONNECT xxxx'           
END                                       

col3=substr(rec1.1,2,10)                 

p='SELECT C1, char(C2) FROM TBL1 WHERE  char(C3) = ''col3'''

"EXECSQL PREPARE S1 FROM :P "
"EXECSQL DECLARE c1 CURSOR FOR S1"                     
"EXECSQL OPEN C1 "           
"EXECSQL FETCH C1 into :col1 , :col2 "         

If SQLCODE = 0 Then Do
  say col1
  say col2
end

"EXECSQL CLOSE C1 "



Thanks all
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Running REXX through JOB CLIST & REXX 13
No new posts Error to read log with rexx CLIST & REXX 11
No new posts isfline didnt work in rexx at z/OS ve... CLIST & REXX 7
No new posts run rexx code with jcl CLIST & REXX 15
No new posts Execute secondary panel of sdsf with ... CLIST & REXX 1
Search our Forums:

Back to Top