|
View previous topic :: View next topic
|
| Author |
Message |
manishram1
New User
Joined: 23 Mar 2006
Posts: 34
|
|
|
|
Hi Eveyone,
Is there any method to access any DB2 table field and display it in the Screen?
Appreciating your help
Regards,
Mani |
|
| Back to top |
|
 |
ofer71
Global Moderator
Joined: 27 Dec 2005
Posts: 2358
Location: Israel
|
|
|
|
IBM provides DSNREXX - a very cool interface that let you access DB2 from REXX. You can read more about it in the fine manual.
O. |
|
| Back to top |
|
|
UmeySan
Active Member
Joined: 22 Aug 2006
Posts: 771
Location: Germany
|
|
|
|
Hi there !
Little example: Displaying panel, getting something and then fetching a row of a DB2-table. Hope that makes it easy to get a little familiar with
DSNREX.
ADDRESS ISPEXEC "ADDPOP"
ADDRESS ISPEXEC "DISPLAY PANEL(UMKTOID1)"
IF RC > 0 THEN DO
RETURN
END
ADDRESS ISPEXEC "VGET (UINR,UKNR,UDB2) PROFILE"
SQLSYS = UDB2
SQLFUC = "f***"
SQLINR = 'S'!!UINR
UKTOID = " "
UKTONR = UKNR
'SUBCOM DSNREXX'
IF RC THEN
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX
SQLFUC = "CONNECT"
ADDRESS DSNREXX "CONNECT" SQLSYS
IF SQLCODE ^= 0 THEN CALL SQLERROR
SQLFUC = "DECLARE"
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"
IF SQLCODE ^= 0 THEN CALL SQLERROR
IF SQLCODE ^= 0 THEN CALL SQLERROR
SQLSTM = "SELECT DISTINCT VE.VE_ID",
" FROM "SQLINR".VE_DARL VE",
" JOIN "SQLINR".GRUNDKONTO GK",
" ON GK.KONTO_ID = VE.VE_ID",
" WHERE GK.KONTONR = "UKTONR" "
SQLFUC = "PREPARE"
ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTM"
IF SQLCODE ^= 0 THEN CALL SQLERROR
SQLFUC = "OPEN C1"
ADDRESS DSNREXX "EXECSQL OPEN C1"
IF SQLCODE ^= 0 THEN CALL SQLERROR
SQLFUC = "FETC C1"
ADDRESS DSNREXX "EXECSQL FETCH C1 INTO :UKTOID"
IF SQLCODE ^= 0 THEN CALL SQLERROR
IF SQLCODE = 0 THEN CALL SQLALLOK
SQLFUC = "CLOS C1"
ADDRESS DSNREXX "EXECSQL CLOSE C1"
IF SQLCODE ^= 0 THEN CALL SQLERROR
ADDRESS DSNREXX "EXECSQL COMMIT"
IF SQLCODE ^= 0 THEN CALL SQLERROR
ADDRESS DSNREXX "DISCONNECT"
IF SQLCODE ^= 0 THEN CALL SQLERROR
ADDRESS ISPEXEC "REMPOP"
EXIT
SQLALLOK:
ZEDSMSG = ' '
ZEDLMSG = 'KONTO-NR: '!!UKTOID
ADDRESS ISPEXEC "SETMSG MSG(ISRZ001)"
RETURN
SQLERROR:
ZEDSMSG = ' '
ZEDLMSG = 'SQL-ERROR: '!!SQLFUC!!' '!!SQLCODE
ADDRESS ISPEXEC "SETMSG MSG(ISRZ001)"
RETURN
Regards, UmeySan |
|
| Back to top |
|
|
Huzefa
New User
Joined: 05 Dec 2006
Posts: 83
Location: Bangalore
|
|
|
|
Hi umeysan,
I tried to run the rexx code u mentioned above...
When I try to execute the same it says
15 +++ S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
Error running REXXDB2, line 15: Routine not found
What is the problem |
|
| Back to top |
|
|
ofer71
Global Moderator
Joined: 27 Dec 2005
Posts: 2358
Location: Israel
|
|
|
|
Make sure your DBA's followed this step while installing DB2.
O. |
|
| Back to top |
|
|
Steve Coalbran
New User
Joined: 01 Feb 2007
Posts: 89
Location: Stockholm, Sweden
|
|
|
|
If for some reason DSNREXX is deliberately unavailable there may be an alternative.
There used to be a Tools version called RXSQL which was available before the productline version was released. RXSQL worked fine when I last used it in 1999 but the syntax is different (well, of course?!)
Sadly, I don't have access to any examples here, having moved onto DSNREXX entirely, so I can't lift any handy code snippets, but I'm sure a Google or searching this Forum can be educational?
If your DBA can get it sorted for you then you'll find it is very easy to use...
ADDRESS DSNREXX sqlstmt
Look at:
"DB2? Universal Database for z/OS Application Programming and SQL Guide Version 8 - SC18-7415-nn" in IBM Publications -
http://www.elink.ibmlink.ibm.com/pub...s/publications
If you have access to the IBM Intranet (as do I) then IBM Internal Publications -
http://ehone.ibm.com/public/applications/publications
A word of warning on high-volume processing.
I wrote a datawarehouse export using DSNREXX which worked well but q u i t e s l o w l y so I replaced it with a more classical approach, DB2 utility unload and then post-processed the file (with compiled Rexx exec) in a fraction of the time. Use the REXXC and REXXL utilities to compile and link/bind. |
|
| Back to top |
|
|
Huzefa
New User
Joined: 05 Dec 2006
Posts: 83
Location: Bangalore
|
|
|
|
Hi steve,
When I try to use RXSQL with the following code
/* REXX */
RXSQL_DB2SUBSYS = D93T
QUERY = "SELECT * FROM SYSIBM.SYSTABLES"
CALL RXSQL QUERY
CALL SQLCA
I get the error
4 +++ CALL RXSQL QUERY
Error running REXXDB21, line 4: Routine not found
*** |
|
| Back to top |
|
|
Steve Coalbran
New User
Joined: 01 Feb 2007
Posts: 89
Location: Stockholm, Sweden
|
|
|
|
| Huzefa wrote: |
...
4 +++ CALL RXSQL QUERY
Error running REXXDB21, line 4: Routine not found
*** |
Sounds like it is not installed then - well it was some years since I used it and the DBA has to explicitly install the package, I guess.
DSNREXX is the pukka way to go - probably needs a DBA to check the status  |
|
| Back to top |
|
|
|
|
