DB2 SYSPROC.ADMIN_CMD Stored Procedure Output
Fin
I am calling the SYSPROC.ADMIN_CMD command to LOAD data into table. The command appears to be working fine, but I need to be able to read the output from the SP to get the number of rows read, skipped, loaded, rejected, deleted, committed etc which is the output returned by the SP.
I have tried all manner of ways trying to retrieve the data but it simply will not work for whatever reason. Can some kind soul please advise how I get classic oorexx 4.1.0 to read a dynamic result set from DB2 Express-C 9.7.4 the SYSPROC.ADMIN_CMD stored procedure ?
I am calling the SP via :
call SQLEXEC 'EXECUTE IMMEDIATE :v.sql'
Many thanks, Fin
Swifty
wrote:
>I need to be able to read the output from the SP
The standard mechanism is to place the code containing the:
call SQLEXEC 'EXECUTE IMMEDIATE :v.sql'
... in one REXX program, lets call that "prog1" and then have a second
program which executes "prog1" and captures the output thus:
/* Prog2 */
Temp = 'temp.file'
'prog1 >' temp
Do while lines(temp) > 0
Line = linein(temp)
Say line /* or parse it to see if it contains what you want */
...
End
If you want to keep it all in one program, you could do something like
this in "prog1"
/* Prog1 */
Password = 'aHjKeQiIuYyP'
If arg(1) = password then do
Temp = 'temp.file'
'prog1 >' temp
/* Do stuff here */
Exit
End
...
call SQLEXEC 'EXECUTE IMMEDIATE :v.sql'
...
--
Steve Swift
http://www.swiftys.org.uk/swifty.html
http://www.ringers.org.uk
Fin
The response is only 1 line of several fields and an sqlcode of 0 and I was hoping to read such via similar logic to declaring a cursor, and fetching the response into variables. Via mainframe there appears to be a way but I can find nothing for Windows XP.
There must be a simple elegant solution to this no ?
LesK
Redirect the output to the stack? The code would depend on
which Rexx you're using.
--
Les (Change Arabic to Roman to email me)
Fin
I have no idea how to redirect output to the stack. Got any examples ?
When I call the statement from db2 clp this is the output I get, then I call it from rexx all I can get is the sqlcode 0. When I check the results, the rows have loaded no problem, but I need to be able to capture the row counts and number of failures.
It just seems to me that it should be a simple thing, but I just cannot figure it out. SP's like this have to open a cursor to be able to write output, why can't we fetch that cursor into variables ?
--------------------
db2 => CALL SYSPROC.ADMIN_CMD('LOAD FROM G:\TEST.csv OF DEL METHOD P (1
,2,3,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19) INSERT INTO DB2ADMIN.TEMP_TABLE (F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18) DATA BUFFER 4096 INDEXING MODE REBUILD')
Result set 1
--------------
ROWS_READ ROWS_SKIPPED ROWS_LOADED ROWS_REJECTED
ROWS_DELETED ROWS_COMMITTED ROWS_PARTITIONED NUM_AGENTIN
FO_ENTRIES MSG_RETRIEVAL MSG_REMOVAL
-------------------- -------------------- -------------------- ---------------
----- -------------------- -------------------- -------------------- -----------
---------- ---------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------- ------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------------------
11 0 10
1 0 11 -
1 record(s) selected.
Return Status = 0
SQL3107W There is at least one warning message in the message file.
db2 =>
LesK
One of the other sql/rexxperts might be able to provide more
details, but what I've learned is something like:
'somecommand | rxqueue'
do while queued()
pull data
/* process the data */
end
Fin
Desperate for help on this.
A.D. Fundum
>> 1 0 11
-
> One of the other sql/rexxperts might be able to provide
> more details, but what I've learned is something like:
> 'somecommand | rxqueue'
> do while queued()
> pull data
> /* process the data */
> end
The OP is unreadable due to the line length, but SysTextScreenRead()
may be another option. But yours'll be better.
--
Swifty
wrote:
>Redirect the output to the stack
This is normally achieved with something like:
command '|RXQUEUE'
Unfortunately, this sets the RC variable to the return code from
RXQUEUE, which is always zero (unless something vile happens to
RXQUEUE).
Redirection to disc sets RC to the return code from the command:
command '>' file
I certainly agree with not wanting to fiddle around with files though.
To this end, I wrote a function which issues the command, and returns
the output in a stem variable, like this:
L. = 'Command'(command)
As well as the standard stem variables, it returns the RC in L.0rc and
if L.0rc is not zero, then it sets L.0cmd to the actual command
executed.
The function contains all the code to handle the temporary file, and
to erase it after it's been read. It keep my programs neat, and if
ever I discover a bug in the command trapping, I know that have to fix
it in only one place.
Jeremy Nicoll - news posts
> I have tried all manner of ways ...
Apart from rexx answers that people have provided, it might be useful to
explore how (or if) the equivalent could be done using, say, Visual Basic.
That might give someone an idea how to do it in rexx; alternatively you
might be able to use a VB stub to call the DB2 thing and trap the reply and
then pass that, somehow, to rexx.
--
Jeremy C B Nicoll - my opinions are my own.
Email sent to my from-address will be deleted. Instead, please reply
to newsre...@wingsandbeaks.org.uk replacing "aaa" by "284".
Fin
It just seems illogical that this is not seemingly supported in a native format of some description.
Shmuel Metz
at 09:08 AM, Swifty <steve....@gmail.com> said:
>This is normally achieved with something like:
>
> command '|RXQUEUE'
ANSI Rexx added some options to address for capturing command output.
--
Shmuel (Seymour J.) Metz, SysProg and JOAT <http://patriot.net/~shmuel>
Unsolicited bulk E-mail subject to legal action. I reserve the
right to publicly post or ridicule any abusive E-mail. Reply to
domain Patriot dot net user shmuel+news to contact me. Do not
reply to spam...@library.lspace.org
LesK
> In <feea27t91apm74c8e...@4ax.com>, on 07/19/2011
> at 09:08 AM, Swifty <steve....@gmail.com> said:
>
>> This is normally achieved with something like:
>>
>> command '|RXQUEUE'
>
> ANSI Rexx added some options to address for capturing command output.
>
Swifty
wrote:
>It just seems illogical that this is not seemingly supported in a native format of some description.
If you're in an environment where the REXX/SQL function package
applies, then you can use that to issue an SQL statement, and get the
results back in a stem.
I've built a wrapper around the REXX/SQL functions so it becomes even
more "rexxish":
R. = 'Select * from table'
If R.0RC <> 0 then call error
Do Row = 1 to R.0
Do C = 1 to words(R.0columns)
Col = word(r.0columns,c)
Say 'Row:' row 'Column' col':' R.col.row
End
End
You have to adopt a different approach if the table contains gigabytes
of data, as the stem variable use all your memory.
Shmuel Metz
07/20/2011
at 01:06 PM, LesK <5mr...@tampabay.rr.com> said:
>He's running ooRexx, which isn't ANSI compliant.
I knew that the original IBM OREXX wasn't ANSI compliant, but had
assumed that that would have been one of the first enhancements to the
open source version. Thanks.
Swifty
wrote:
>R. = 'Select * from table'
Well what a stupid mistake... try this:
R. = 'SQL'('Select * from table')
Fin
The sql in this case is something in the order of :
CALL SYSPROC.ADMIN_CMD('LOAD FROM BLAH_FILE.CSV OF DEL INTO MISC_TABLE')
The results of such, returns a single row of data with several fields being rows_read, rows_committed etc etc. I need to read those fields and the problem is Rexx will not do so in a fashion I was hoping for, or expecting, or at the very least, that I know how to do.
RXQUEUE does not seem to work either.
rony
If everything goes wrong you may want to look into BSF4ooRexx (cf.
<https://sourceforge.net/projects/bsf4oorexx/>). This is an external Rexx function library with
supporting ooRexx code that allows you to use all of Java as if it was ooRexx.
What this means is as follows: every funtionality that is available in form of Java classes can be
used by Rexx programs in a very easy manner due to the "camouflaging ooRexx support".
As Java is practically installed on any computer (Windows, Linux, MacOSX) supplying a huge library
of Java classes (known as "JRE", Java Runtime Environment), there is probably no functionality that
is not covered by Java (classes). BSF4ooRexx allows you to fully exploit this infrastructure for
your own purposes.
There are examples in the BSF4ooRexx package that demonstrate how easy it is to use JDBC to get
access to SQL. After having installed BSF4ooRexx look for "samples/ReneJansen/jdbc.jrexx".
So you would end up to program in Rexx, but if you need functionality from Java you can incorporate
that into your Rexx programs via BSF4ooRexx using your Rexx skills (no need to learn/know Java, just
to be able to read their HTML-documentation). And you would be able to use all of Java without
strong typing or exact casing ("camouflaging support").
---rony