Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

REXX-DB2 with SQLDA

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

Active User


Joined: 17 Nov 2009
Posts: 118
Location: India

PostPosted: Mon Dec 31, 2012 11:35 am    Post subject: REXX-DB2 with SQLDA
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

Moderator


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

PostPosted: Mon Dec 31, 2012 12:57 pm    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Dec 31, 2012 2:59 pm    Post subject: Reply to: REXX-DB2 with SQLDA
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 ...
http://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: 118
Location: India

PostPosted: Mon Dec 31, 2012 5:29 pm    Post subject:
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

Moderator


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

PostPosted: Mon Dec 31, 2012 5:30 pm    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Dec 31, 2012 5:53 pm    Post subject: Reply to: REXX-DB2 with SQLDA
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

Moderator


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

PostPosted: Mon Dec 31, 2012 6:13 pm    Post subject:
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: 118
Location: India

PostPosted: Wed Jan 02, 2013 3:28 pm    Post subject:
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    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 What is the command to check MODE of ... rohanthengal CLIST & REXX 6 Fri Nov 18, 2016 1:48 pm
This topic is locked: you cannot edit posts or make replies. Rexx to create GDG by scanning JJ result krish.deepu CLIST & REXX 5 Tue Oct 25, 2016 5:32 pm
No new posts Replace repeated JCL with looping REXX prino CLIST & REXX 3 Thu Oct 13, 2016 5:30 am
This topic is locked: you cannot edit posts or make replies. Need to code a Rexx PGM sundaram.naveen CLIST & REXX 18 Thu Oct 06, 2016 6:45 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us