|
View previous topic :: View next topic
|
| Author |
Message |
diwa_thilak
Active User
.jpg)
Joined: 13 Jul 2006
Posts: 205
Location: At my desk
|
|
|
|
Hi Friends,
I am planning to retrieve a single row from a table (DB2) using REXX utility.
Please throw some light about connecting DB2 tables using rexx and retrieving a data(row) from the table. |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10886
Location: italy
|
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
You have to declare a cursor and fetch the row from the table, even if you know there will never be more than one row.
The format is:
To load DB2 and connect:
| Code: |
Address TSO "SUBCOM DSNREXX"
If RC <> 0 Then Do
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
If S_RC <> 0 Then Do
Say 'Problem Loading DB2 environment'
Exit
End
End
Address DSNREXX "CONNECT "DB2Name |
To fetch the row:
| Code: |
SQLSTMT = "SELECT ... FROM ... WHERE ..."
Address DSNREXX
"EXECSQL DECLARE C7 CURSOR FOR S7"
"EXECSQL PREPARE S7 FROM :SQLSTMT"
"EXECSQL OPEN C7"
"EXECSQL FETCH C7 INTO :fld1, :fld2 ..."
LastSQL = SQLCODE
"EXECSQL CLOSE C7"
Address |
|
|
| Back to top |
|
|
diwa_thilak
Active User
Joined: 13 Jul 2006
Posts: 205
Location: At my desk
|
|
|
|
Thanks Marso,
I could able to connect to the DB2 successfully. But i could not able to retrieve the records.
I need to dynamically send values to the query.
SQLSTMT = "SELECT NUM_VAL_I FROM DSN.TABNAME WHERE ID_I = 045"
Also let me know whether :SQLSTMT , :FLD1 variables are accepted in REXX.
How to dynamically send values to the query ? |
|
| Back to top |
|
|
diwa_thilak
Active User
Joined: 13 Jul 2006
Posts: 205
Location: At my desk
|
|
|
|
Hi All,
Yeah :SQLSTMT and : FLD1 are valid statements in REXX.
But when i execute my below query i am getting SQLCODE as -981.
| Quote: |
-981 THE SQL STATEMENT FAILED BECAUSE THE RRSAF CONNECTION IS NOT IN A STATE THAT ALLOWS SQL OPERATIONS, REASON reason-code. |
That means i could not able to connect to DB2. How to rebind and connect to DB2 ? |
|
| Back to top |
|
|
ofer71
Global Moderator
Joined: 27 Dec 2005
Posts: 2358
Location: Israel
|
|
|
|
The instructions regarding binding REXX to DB2 are well described in the fine manual.
O. |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
Send values to the query like usual:
| Code: |
MyVal = 45
SQLSTMT = "SELECT NUM_VAL_I FROM DSN.TABNAME WHERE ID_I = "MyVal |
If the variable is alphabetic, add quotes:
| Code: |
MyVal = "diwa"
SQLSTMT = "SELECT NUM_VAL_I FROM DSN.TABNAME WHERE ID_I = '"MyVal"'" |
About -981, try to get the reason-code by displaying SQLERRMC:
| Code: |
If SQLCODE <> 0 Then Do
Say "SQLCODE = "SQLCODE
Say "SQLERRMC = "SQLERRMC
End |
|
|
| Back to top |
|
|
diwa_thilak
Active User
Joined: 13 Jul 2006
Posts: 205
Location: At my desk
|
|
|
|
Hi All,
I found the bug, The connection identifier to connect to the DB2 tables needs to be specified while connecting to the tables.
I missed to specify.
Thanks All Buddies (e.s, Marso, Ofer). |
|
| Back to top |
|
|
lanand_hps
New User
Joined: 05 Dec 2007
Posts: 82
Location: chennai
|
|
|
|
Hi All,
I have used similiar code in my REXX routine.
It is throwing a return code of 1 for open cursor.
My code :
SQLSTMT="SELECT CIM FROM DB2TEST.MASTER"
ADDRESS DSNREXX
"EXECSQL DECLARE C7 CURSOR FOR S7"
"EXECSQL PREPARE S7 FROM :SQLSTMT"
"EXECSQL OPEN C7"
"EXECSQL FETCH C7 INTO :FLD1"
I'm able to connect to DB2 subsystem.
Please comment. |
|
| Back to top |
|
|
ofer71
Global Moderator
Joined: 27 Dec 2005
Posts: 2358
Location: Israel
|
|
|
|
Take a look at Marso's post above.
O. |
|
| Back to top |
|
|
lanand_hps
New User
Joined: 05 Dec 2007
Posts: 82
Location: chennai
|
|
|
|
It is working with return code 1 (warning?).
Is it possible to connect to two subsystems in one rexx routine?
ADDRESS DSNREXX "CONNECT " DSN1
ADDRESS DSNREXX
"EXECSQL DECLARE C7 CURSOR FOR S7"
"EXECSQL PREPARE S7 FROM :SQLSTMT"
"EXECSQL OPEN C7"
"EXECSQL FETCH C7 INTO :FLD1
ADDRESS DSNREXX "CONNECT " DSN2
ADDRESS DSNREXX
"EXECSQL DECLARE C7 CURSOR FOR S7"
"EXECSQL PREPARE S7 FROM :SQLSTMT"
"EXECSQL OPEN C7"
"EXECSQL FETCH C7 INTO :FLD1
Data is present in DSN1 and not in DSN2.
I'm getting sqlcode(for fetch) 0 for both regions which is wrong.
Output of 2nd region is same as 1st.
Should i do anything different? |
|
| Back to top |
|
|
ofer71
Global Moderator
Joined: 27 Dec 2005
Posts: 2358
Location: Israel
|
|
|
|
Return code 1 for RXSUBCOM is not a warning. Read the manual.
O. |
|
| Back to top |
|
|
lanand_hps
New User
Joined: 05 Dec 2007
Posts: 82
Location: chennai
|
|
|
|
Ok. But can i have answer for my doubts?
Is it possible to connect to two subsystems in one rexx routine? |
|
| Back to top |
|
|
saiprasadh
Active User
Joined: 20 Sep 2006
Posts: 154
Location: US
|
|
|
|
Hi,
Ya you can, But you have to disconnect the first Sub-System
For Ex: You have 2 sub systems Prod & Test
1) Connect to prod
2) Execute the query
3) Disconnect from prod
4) Connect to test
5) Execute the query
6) Disconnect from prod
Thanks
Sai |
|
| Back to top |
|
|
|
|
