REXX - SQL/DB2

[Hamilton, Robert]

We get a -1 return from

ADDRESS DSNREXX 'CONNECT' 'DSNT'

Per the SQL manual. Do we need a PLAN or maybe something allocated to get
this to work?

We are working from the Application Programming and SQL Guide and running
DB2 v. 6.

Bob Hamilton

----------------------------------------------------------------------
For TSO-REXX subscribe / signoff / archive access instructions,
send email to LIST...@VM.MARIST.EDU with the message: INFO TSO-REXX

[Marcel Harleman]

Bob,

DSNREXX needs a plan DSNREXX in the connected DB2 subsystem. You can run job DSNTIJRX from the SDSNSAMP library if you haven't done this already.

Plus: do you check if the DSNREXX environment is available as follows?

address TSO 'SUBCOM DSNREXX'
if RC then
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
address DSNREXX 'CONNECT DSNT'

HTH

Marcel.

[Bob Bridges]

I take it you've already added DSNREXX to the address list? In my DB2INIT
subroutine I do it like this:

'SUBCOM DSNREXX'
if rc<>0 then call rxsubcom 'ADD','DSNREXX','DSNREXX'

I think until you do that, at most installations DSNREXX isn't available.

If that isn't the problem, I can only suppose that the subsystem DSNT
isn't currently available:

address DSNREXX 'CONNECT' ssid
if rc<>0 then call abend 'Subsystem' ssid 'unavailable (rc='rc')'

---
Bob Bridges, robert...@discoverfinancial.com, 224 405-0811
rhb...@attglobal.net, 847 520-1684 xt 243

/* The ideal universe for us is one very much like the universe we
inhabit. And I would guess that this is not really much of a coincidence.
-Carl Sagan, from "Broca's Brain" */

"Hamilton, Robert" <rob...@UTDALLAS.EDU>
2004-11-10 09:13

To: TSO-...@VM.MARIST.EDU
cc:
Subject: REXX - SQL/DB2

We get a -1 return from

ADDRESS DSNREXX 'CONNECT' 'DSNT'

per the SQL manual. Do we need a PLAN or maybe something allocated to get

this to work? We are working from the Application Programming and SQL
Guide and running DB2 v. 6.

----------------------------------------------------------------------

[Hamilton, Robert L]

Well, we got that solved . Now have a problem with the PREPARE !

bobh
-----Original Message-----
From: Rexx Language Association [mailto:REX...@lists.psu.edu] On Behalf
Of joe picc
Sent: Wednesday, November 10, 2004 10:14 AM
To: REX...@LISTS.PSU.EDU
Subject: Re: REXX - SQL/DB2

Bob,
I looked at some of my examples for mainframe and I execute this command
first: Address TSO "SUBCOM DSNREXX" followed by ADDRESS command.

Thanks.
John

--- Bob Hamilton <rob...@UTDALLAS.EDU> wrote:

> We get a -1 return from
>
> ADDRESS DSNREXX 'CONNECT' 'DSNT'
>
>
>

> Per the SQL manual. Do we need a PLAN or maybe something allocated to

> get this to work?
>
>
>
> We are working from the Application Programming and SQL Guide and
> running
> DB2 v. 6.
>
>
>

> Bob Hamilton
>
>

__________________________________
Do you Yahoo!?
Check out the new Yahoo! Front Page.
www.yahoo.com

[Hamilton, Robert L]

I've got it working down to the PREPARE step; but no further and I'm on
another project for 2-3 days. . .

bobh

________________________________

From: Rexx Language Association [mailto:REX...@lists.psu.edu] On Behalf
Of Marc Vincent Irvin
Sent: Thursday, November 11, 2004 3:30 AM
To: REX...@LISTS.PSU.EDU
Subject: Re: REXX - SQL/DB2

Has anyone packaged the SQL into a kiddy script "ALA" out of the box. I
use DB2 a lot, and would like to access it from batch jobs using REXX.
All the research I've done in the past have required assembling programs
and such.That is forbidden, at shops that don't want to support things
after a developer moves on.

Bob Hamilton wrote:

We get a -1 return from

ADDRESS DSNREXX 'CONNECT' 'DSNT'

Per the SQL manual. Do we need a PLAN or maybe something allocated to
get this to work?

We are working from the Application Programming and SQL Guide and
running DB2 v. 6.

Bob Hamilton

[Hilton, Tina]

You need to make sure you have the rexx feature installed (plan DSNREXX and
packages DSNREXCS, DSNREXRR, DSNREXRS, DSNREXUR, DSNREXX), you must have
SDSNLOAD in your steplib (although that would cause your RXSUBCOM statement
to fail -- you did include the RXSUBCOM command, right?), and you must be on
the same lpar as the subsystem you're trying to connect to. Are all those
true? If so, post your code and trace output so we can see what's happening.
You can also search the archives here and on db2-l since the rexx interface
has been discussed quite a bit.

Tina

[Marcel Harleman]

Hi,

>Has anyone packaged the SQL into a kiddy script "ALA" out of the box. I
>use DB2 a lot, and would like to access it from batch jobs using REXX.
>All the research I've done in the past have required assembling programs
>and such.That is forbidden, at shops that don't want to support things
>after a developer moves on.

I've copied an example of how I have set things up for several DSNREXX
invocations. There are of course a few issues with the coding and to
name one or two:

- the example assumes the whole resultset fits into virtual storage.
If you want to process row by row or subset by subset you'll have to
adjust the coding to your liking.

- the arrays are build up using the interpret command. As a result the
array-names are exactly the same as the column-names of the
SQL-statement. And as an example of how to do this for a column that
represents a function of one kind or the other I also gave an SQL
statement.

Hope this helps.

/* Rexx - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */

DB2 = "DSN1"

Stmt = "SELECT PDSNAME, NAME, VERSION, HEX(CONTOKEN) AS CONTOKEN"
Stmt = Stmt" FROM SYSIBM.SYSPACKAGE"
Stmt = Stmt" WHERE COLLID = '"Collid"' ORDER By 2, 3, 1"

call CreateThread

call DeclareCursor_C1
call Prepare_C1
call Describe_C1
call Open_C1
do until SQLcode <> 0
call Fetch_C1
end
call Close_C1
call TerminateThread

return

/* = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = */
/* */
/* = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = */

CreateThread:

"SUBCOM DSNREXX"
if RC then

S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
address DSNREXX "CONNECT" DB2
If SQLCODE <> 0 then
do
say "Error connecting to "DB2", SQLcode = "SQLCODE
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
exit(12)
end
return

TerminateThread:
address DSNREXX "DISCONNECT"
If SQLCODE <> 0 then
do
say "Error terminating connection from "DB2", SQLcode = "SQLCODE
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
exit(12)
end
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
return

/* = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = */
/* */
/* = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = */

DeclareCursor_C1:
address DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"
if SQLcode <> 0 then
do
say "Error during DECLARE C1, SQLcode = "SQLCODE
say " "
say SQLERRMC
say " "
call TerminateThread
exit(12)
end
return

Prepare_C1:
address DSNREXX "EXECSQL PREPARE S1 FROM :STMT"
if SQLcode <> 0 then
do
say "Error during PREPARE, SQLcode = "SQLCODE
say " "
say Stmt
say " "
say SQLERRMC
say " "
call TerminateThread
exit(12)
end
return

Describe_C1:
address DSNREXX "EXECSQL DESCRIBE S1 INTO :OUTSQLDA"
if SQLcode <> 0 then
do
say "Error during DESCRIBE, SQLcode = "SQLCODE
say " "
say OUTSQLDA
say " "
call TerminateThread
exit(12)
end
return

Open_C1:
address DSNREXX "EXECSQL OPEN C1"
if SQLcode <> 0 then
do
say "Error during OPEN C1, SQLcode = "SQLCODE
say " "
say SQLERRMC
say " "
call TerminateThread
exit(12)
end
return

Fetch_C1:
address DSNREXX "EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
if SQLCODE = 0 then
do
Aantal = Aantal + 1
do I = 1 to OUTSQLDA.SQLD
H = "C1_"OUTSQLDA.I.SQLNAME"."Aantal" = '"OUTSQLDA.I.SQLDATA"'"
interpret H
end
end
else if SQLcode <> 100 then
do
say "Error during FETCH C1, SQLcode = "SQLCODE
say " "
say SQLERRMC
say " "
call TerminateThread
exit(12)
end
return

Close_C1:
address DSNREXX "EXECSQL CLOSE C1"
if SQLcode <> 0 then
do
say "Error during CLOSE C1, SQLcode = "SQLCODE
say " "
say SQLERRMC
say " "
call TerminateThread
exit(12)
end
return

[Bob Bridges]

Well, sort of. I've managed to get it down to three subroutines:

if db2init()<>0 then call abend 'DB2 is unavailable just now'

DB2INIT is an external function that establishes DSNREXX, figures out what
LPAR it's in and connects to the appropriate DB2 subsystem. I include a
statement like the above in every REXX that needs to do a SELECT.

if dosql(1,<SELECT statement>)<>0 then call abend 'SELECT statement
failed'

DOSQL is another external function that declares the cursor, prepares the
SELECT statement and opens the cursor. I suppose it would work with other
statement types than SELECT, but that's all I ever do in REXX. I can have
multiple DOSQL calls, and can have more than one open at once; the first
argument (1 in this case) specifies the cursor number.

do forever
if sqlcmd('FETCH C1 USING DESCRIPTOR :fch')=100 then leave
/* process fch.1.sqldata, fch.2.sqldata etc */
end
call sqlcmd 'CLOSE C1'

To fetch the data I use SQLCMD, and it must be a local routine not
external for two reasons: 1) the fields are fetched into REXX variables,
which cannot be passed back from external routines, and 2) external REXX
calls are even slower than fetches. Basically all my SQLCMD does is "address DSNREXX 'EXECSQL' cmd" and then check various return codes to decide whether to print off a
bunch of error messages and abend.

So it's packaged, but not exactly neatly. Any interest in that?

---
Bob Bridges, robert...@discoverfinancial.com, 224 405-0811
rhb...@attglobal.net, 847 520-1684 xt 243

/* From "Basic Flying Rules": Try to stay in the middle of the air. Do
not go near the edges of it. The edges of the air can be recognized by
the appearance of ground, buildings, sea, trees and interstellar space. It
is much more difficult to fly there. */

________________________________

From: Rexx Language Association On Behalf Of Marc Vincent Irvin
Sent: Thursday, November 11, 2004 3:30 AM

Has anyone packaged the SQL into a kiddy script "ALA" out of the box. I
use DB2 a lot, and would like to access it from batch jobs using REXX.
All the research I've done in the past have required assembling programs
and such.That is forbidden, at shops that don't want to support things
after a developer moves on.

----------------------------------------------------------------------