Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Accessing a DB2 Table through REXX

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> CLIST & REXX
View previous topic :: :: View next topic  
Author Message
manishram1

New User


Joined: 23 Mar 2006
Posts: 34

PostPosted: Wed Nov 22, 2006 10:45 am    Post subject: Accessing a DB2 Table through REXX
Reply with quote

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
View user's profile Send private message

ofer71

Global Moderator


Joined: 27 Dec 2005
Posts: 2360
Location: Israel

PostPosted: Wed Nov 22, 2006 11:46 am    Post subject:
Reply with quote

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
View user's profile Send private message
UmeySan

Active Member


Joined: 22 Aug 2006
Posts: 757
Location: Germany

PostPosted: Mon Nov 27, 2006 7:00 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Huzefa

New User


Joined: 05 Dec 2006
Posts: 85
Location: Bangalore

PostPosted: Mon Feb 05, 2007 12:01 pm    Post subject:
Reply with quote

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
View user's profile Send private message
ofer71

Global Moderator


Joined: 27 Dec 2005
Posts: 2360
Location: Israel

PostPosted: Mon Feb 05, 2007 2:18 pm    Post subject:
Reply with quote

Make sure your DBA's followed this step while installing DB2.

O.
Back to top
View user's profile Send private message
Steve Coalbran

New User


Joined: 01 Feb 2007
Posts: 85
Location: Stockholm, Sweden

PostPosted: Mon Feb 05, 2007 3:42 pm    Post subject: Re: Accessing a DB2 Table through REXX
Reply with quote

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
View user's profile Send private message
Huzefa

New User


Joined: 05 Dec 2006
Posts: 85
Location: Bangalore

PostPosted: Wed Feb 07, 2007 9:45 am    Post subject:
Reply with quote

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
View user's profile Send private message
Steve Coalbran

New User


Joined: 01 Feb 2007
Posts: 85
Location: Stockholm, Sweden

PostPosted: Sun Feb 11, 2007 7:17 pm    Post subject:
Reply with quote

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 icon_smile.gif
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 -> CLIST & REXX All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to write Rexx program to size and... sreejeshcs CLIST & REXX 14 Thu Oct 12, 2017 7:26 am
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
This topic is locked: you cannot edit posts or make replies. Rexx to execute spool commands upendrasri CLIST & REXX 5 Wed Oct 04, 2017 6:54 am
No new posts Accessing CICS tran with map from JCL... navdeepaggarwal CICS 5 Tue Oct 03, 2017 6:15 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us