I have some problem, I need an example for a stored procedure.
Here we have an IBM mainframe with OS390, a transaction should be started by
a stored procedure which will be "called" from a java program.
So we have the way --> Java --> stored procedure --> transaction -->
cobol-program
and a string of 20 bytes which will be checked by the cobol program, this
prg should give a return-code back to java.
Is it possible to do so?
I have never seen a stored procedure, so I need someone to show me an
example, what is so special about stored procedures?
please help me quick, 'cause I don't know it myself and I don't have the
time to search the whole internet, ...
Thanks in advance
Tim Cordsen
[snip]
>I have never seen a stored procedure, so I need someone to show me an
>example, what is so special about stored procedures?
Ummmm... if you've never seen one then how will you know when somebody
shows you an example?
I suggest taht you hire a programmer to do a programmer's job.
DD
Tim Cordsen wrote:
>
> Hi @ all!
>
> I have some problem, I need an example for a stored procedure.
> Here we have an IBM mainframe with OS390, a transaction should be started by
> a stored procedure which will be "called" from a java program.
>
> So we have the way --> Java --> stored procedure --> transaction -->
> cobol-program
> and a string of 20 bytes which will be checked by the cobol program, this
> prg should give a return-code back to java.
>
A stored Procedure looks just like a program; for me they are in COBOL,
the difference is the way they are compiled and the bind statements.
The advantage is the speed of processing - my Stored Procedures are
called by a web application; updating various tables in predetermined
and complex ways.
my DB2 application is on OS/390 V 2.6 - DB2 V5; using ColdFusion for the
Web Application portion.
Susan A
Typical knee-jerk reaction from you, with the emphasis on 'jerk'.
He will know that it is a 'stored procedure' if it is a sensible
response to this enquiry, or if it mentions that it is so. Even you
should have been able to work that out.
There is no indication that he is not a programmer. I have been
programming for 30 years and have never seen (AFAIK) a stored procedure
in Cobol. If I were to write one then I would like to see an example
first.
But then perhaps you don't know what a 'stored procedure' is, or why it
might be different.
How interesting that you apply such a label to one of the classic
paradoxes of knowledge and learning. The classic formulation is:
If you already know something, why look for it?
If you do not know something, how will you know when you find it?
My apologies if the reference was a bit too obscure.
>
>He will know that it is a 'stored procedure' if it is a sensible
>response to this enquiry, or if it mentions that it is so. Even you
>should have been able to work that out.
'Sensible' is in the mind of the beholder... just like the results of
COND=(4,LT,PREVSTEP) are 'obvious', no?
>
>There is no indication that he is not a programmer.
There is no indication that he is not the King of England, either... but I
know he is not because *I* am.
>I have been
>programming for 30 years and have never seen (AFAIK) a stored procedure
>in Cobol. If I were to write one then I would like to see an example
>first.
... and you would know you had been given an example of what you sought
because it was 'sensible', right?
>
>But then perhaps you don't know what a 'stored procedure' is, or why it
>might be different.
The world is full of 'perhaps'... were I possessed of the couth,
decorum and Plain Good Sense demonstrated by your comments then perhaps
I would suggest that you you might wish to void your bowels into your
headgear and pull it over your pinnae.
DD
Just as an example (and not working as it is made of some pieces I
have left in my computer (it had a nervous breakdown) !!!), here is
what a program calling a stored procedure would look like
(including the creation of the stored procedure it self)
FF
----------------------
IDENTIFICATION DIVISION.
PROGRAM-ID. DEMO.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 LDA.
03 LDA-V2RC PIC S9(4) COMP-4.
03 FILLER PIC X(10).
03 LDA-RC PIC S9(4) COMP-4.
03 FILLER PIC X(50).
01 CURSOR-1.
03 CURS-V2RC PIC S9(4) COMP-4.
03 CURS-TYPE PIC S9(4) COMP-4.
03 CURS-ROWS-PROCESSED PIC S9(9) COMP-4.
03 CURS-OFFS PIC S9(4) COMP-4.
03 CURS-FNC PIC X.
03 FILLER PIC X.
03 CURS-RC PIC S9(4) COMP-4.
03 FILLER PIC X(50).
01 HOST-DATA-AREA PIC X(512).
01 ERR-RC PIC S9(4) COMP-4.
01 MSGBUF PIC X(500).
01 MSGBUF-L PIC S9(9) COMP-4.
01 PART-UPDATE.
03 DESCRIP OCCURS 3 TIMES PIC X(20).
03 PARTNOS OCCURS 3 TIMES PIC S9(9) COMP-4.
01 BND-VARS.
03 DESCRIP-ALEN OCCURS 3 TIMES PIC S9(4) COMP-4.
03 DESCRIP-RC OCCURS 3 TIMES PIC S9(4) COMP-4.
03 PARTNO-ALEN OCCURS 3 TIMES PIC S9(4) COMP-4.
03 PARTNO-RC OCCURS 3 TIMES PIC S9(4) COMP-4.
01 DESCRIP-CS PIC S9(9) VALUE 3 COMP-4.
01 DESCRIP-MAX PIC S9(9) VALUE 3 COMP-4.
01 DESCRIP-PH PIC X(5) VALUE ":DESC".
01 DESCRIP-PH-L PIC S9(9) VALUE 5 COMP-4.
01 DESCRIP-L PIC S9(9) VALUE 20 COMP-4.
01 PARTNO-CS PIC S9(9) VALUE 3 COMP-4.
01 PARTNO-MAX PIC S9(9) VALUE 3 COMP-4.
01 PARTNO-PH PIC X(6) VALUE ":PARTS".
01 PARTNO-PH-L PIC S9(9) VALUE 6 COMP-4.
01 USERNAME PIC X(33)
VALUE "scott/tiger".
01 USERNAME-L PIC S9(9) VALUE 31 COMP-4.
01 PASSWORD PIC X(5) VALUE "TIGER".
01 PASSWORD-L PIC S9(9) VALUE 5 COMP-4.
01 CONN PIC S9(9) VALUE 0 COMP-4.
01 CONN-L PIC S9(9) VALUE 0 COMP-4.
01 CONN-MODE PIC S9(9) VALUE 0 COMP-4.
01 VARCHAR2-TYPE PIC S9(9) VALUE 1 COMP-4.
01 INT-TYPE PIC S9(9) VALUE 3 COMP-4.
01 DESCRIP-LEN PIC S9(9) VALUE 20 COMP-4.
01 INT-L PIC S9(9) VALUE 4 COMP-4.
01 MAX-TABLE PIC S9(9) VALUE 20 COMP-4.
01 ZERO-A PIC S9(9) VALUE 0 COMP-4.
01 VERSION-7 PIC S9(9) VALUE 2 COMP-4.
01 MINUS-ONE PIC S9(9) VALUE -1 COMP-4.
01 CREATE-PKG PIC X(256) VALUE SPACES.
01 CREATE-PKG-L PIC S9(9) VALUE 256 COMP-4.
01 CREATE-PKG-BODY PIC X(256) VALUE SPACES.
* PL/SQL anonymous block, calls update_parts
01 PLS-BLOCK PIC X(100) VALUE
"BEGIN add_parts(3, :DESC, :PARTS); END;".
01 PLS-BLOCK-L PIC S9(9) VALUE 100 COMP-4.
01 ORA-ERROR PIC X.
01 RET-ERROR PIC S99999.
PROCEDURE DIVISION.
START-MAIN.
STRING
"CREATE PACKAGE update_parts AS "
"TYPE pnt IS TABLE OF NUMBER "
"INDEX BY BINARY_INTEGER; "
"TYPE pdt IS TABLE OF CHAR(20) "
"INDEX BY BINARY_INTEGER; "
"PROCEDURE add_parts (n IN INTEGER, "
"descr IN pdt, "
"partno IN pnt); "
"END update_parts; " DELIMITED BY SIZE
INTO CREATE-PKG.
STRING
"CREATE PACKAGE BODY update_parts AS "
"PROCEDURE add_parts (n IN INTEGER, "
"descr IN pdt, "
"partno IN pnt); "
"BEGIN "
"FOR i IN 1..n LOOP "
"INSERT INTO part_nos "
"VALUES (partno(i), descr(i)); "
"END LOOP; "
"END; "
"END update_parts;" DELIMITED BY SIZE
INTO CREATE-PKG-BODY
* Connect to Oracle in non-blocking mode.
* HDA must be initialized to zeros before call to OLOG.
MOVE LOW-VALUES TO HOST-DATA-AREA.
CALL "olon" USING LDA USERNAME(1:USERNAME-L) USERNAME-L.
IF RET-ERROR NOT = 0
GO TO EXIT-STOP.
* Open the cursor.
* Use parameters PASSWORD, etc. for unused parameters.
CALL "OOPEN" USING CURSOR-1, LDA, PASSWORD, PASSWORD-L,
MINUS-ONE, PASSWORD, PASSWORD-L.
IF RET-ERROR NOT = 0
GO TO EXIT-STOP.
* Parse the drop table statement.
* The statement is executed by OPARSE because
* the DEFFLG parameter is zero.
CALL "OPARSE" USING CURSOR-1, DROP-TBL, DROP-TBL-L,
ZERO-A, VERSION-7.
IF RET-ERROR NOT = 0 AND NOT = 942
GO TO EXIT-CLOSE.
* Parse as well as execute the create table statement.
CALL "OPARSE" USING CURSOR-1, CREATE-TBL, CREATE-TBL-L,
ZERO-A, VERSION-7.
IF RET-ERROR NOT = 0
GO TO EXIT-CLOSE.
* Parse the PL/SQL block.
* The statement is executed by OPARSE because
* the DEFFLG parameter is zero.
CALL "OPARSE" USING CURSOR-1, CREATE-PKG, CREATE-PKG-L,
ZERO-A, VERSION-7.
IF RET-ERROR NOT = 0
GO TO EXIT-CLOSE.
CALL "OPARSE" USING CURSOR-1, CREATE-PKG-BODY,
CREATE-PKG-L,
ZERO-A, VERSION-7.
IF RET-ERROR NOT = 0
GO TO EXIT-CLOSE.
CALL "OPARSE" USING CURSOR-1, PLS-BLOCK, PLS-BLOCK-L,
ZERO-A, VERSION-7.
IF RET-ERROR NOT = 0
GO TO EXIT-CLOSE.
* Execute the PL/SQL block, calling update_parts.
CALL "OEXEC" USING CURSOR-1.
IF RET-ERROR NOT = 0
GO TO EXIT-CLOSE.
EXIT-CLOSE.
CALL "OCLOSE" USING CURSOR-1.
CALL "OLOGOF" USING LDA.
EXIT-STOP.
STOP RUN.
We had a 650 RAM with tape doing some work at a mill. The RAM (drum)
was too small for all the code needed for the various transaction codes the
applicaltion had to handle.
Solution applied, I think of as a stored procedure. They put the code for
the
transactions on the disks with the data, so they could read the program and
data into the available memory to execute it.
Now days, I'd called a stored procedure one where I found a way to say
hello without using a persons name I could not at the moment remember.
A Knee Jerk. I wish I'd see more definitions than name calling.
Warren Simmons
Richard Plinston <rip...@Azonic.co.nz> wrote in message
news:3C6387EA...@Azonic.co.nz...
<docd...@panix.com> wrote in message news:a3ur1a$4ph$1...@panix1.panix.com...
Not obscure, merely inappropriately applied.
> >He will know that it is a 'stored procedure' if it is a sensible
> >response to this enquiry, or if it mentions that it is so. Even you
> >should have been able to work that out.
>
> 'Sensible' is in the mind of the beholder... just like the results of
> COND=(4,LT,PREVSTEP) are 'obvious', no?
I think that it is possible to eliminate 'sensible' as one of your
attributes regardless as how you behold yourself. It is not the results
that would be obvious, it is the context. Your code is not in an
appropriate context and so its function, origin or purpose are not clear
(nor why you posted it). A sample stored procedure _would_ be in
context in this thread and/or it is appropriately labeled.
> ... and you would know you had been given an example of what you sought
> because it was 'sensible', right?
No. As I have already said, because it is in an appropriate context, ie
a response to the question or a sensibly labeled example.
Of course any 'samples' from you would be viewed with a great deal of
suspicion.
> >But then perhaps you don't know what a 'stored procedure' is, or why it
> >might be different.
>
> The world is full of 'perhaps'...
I'll take that as a "no" then.
I am a programmer, but I was never in the need to know anything about stored
procedures. Now the project-manager (I am sure that this is not the right
word, but I don't know the right one) just came to me and said: "find out
how a stored procedure has to be written and how it works". So I try to find
out what it looks like and how it works.
It should only call a transaction on the mainframe and give a string back to
the calling java program, that's all.
Thanks to the persons who tried to help so far.
Yours
Tim Cordsen
"Tim Cordsen" <tim.c...@web.de> schrieb im Newsbeitrag
news:a3rgk8$d1...@doiftp1.volkswagen.de...
> In case somebody has forgotten, Mr Plinston started this thread,
>not DD. Could somebody reply to his question, and ignore side issues.
Someone already did!!:(:
FF
Well, then, it looks as though the conditional of the apology should take
a branch.
>
>> >He will know that it is a 'stored procedure' if it is a sensible
>> >response to this enquiry, or if it mentions that it is so. Even you
>> >should have been able to work that out.
>>
>> 'Sensible' is in the mind of the beholder... just like the results of
>> COND=(4,LT,PREVSTEP) are 'obvious', no?
>
>I think that it is possible to eliminate 'sensible' as one of your
>attributes regardless as how you behold yourself.
Well, Mr Plinston, you show how well you think... and so boldly, and in
public, too!
You might want to pay a bit of attention to noting your editing... no,
cancel that, you appear to have your hands *more* than full, as is,
already.
DD
Actually, Mr Styles, I believe that a Mr Cordsen started this thread... Mr
Plinston merely took it in a certain direction without indicating the
editing he'd done.
DD
>In article <3C6387EA...@Azonic.co.nz>,
>Richard Plinston <rip...@Azonic.co.nz> wrote:
>>docd...@panix.com wrote:
>>>
>>> >I have never seen a stored procedure, so I need someone to show me an
>>> >example, what is so special about stored procedures?
>>>
>>> Ummmm... if you've never seen one then how will you know when somebody
>>> shows you an example?
>>>
>>> I suggest taht you hire a programmer to do a programmer's job.
>>
>>Typical knee-jerk reaction from you, with the emphasis on 'jerk'.
>
>How interesting that you apply such a label to one of the classic
>paradoxes of knowledge and learning. The classic formulation is:
>
>If you already know something, why look for it?
>
>If you do not know something, how will you know when you find it?
>
>My apologies if the reference was a bit too obscure.
>
>>
>>He will know that it is a 'stored procedure' if it is a sensible
>>response to this enquiry, or if it mentions that it is so. Even you
>>should have been able to work that out.
>
>'Sensible' is in the mind of the beholder... just like the results of
>COND=(4,LT,PREVSTEP) are 'obvious', no?
>
>>
>>There is no indication that he is not a programmer.
>
>There is no indication that he is not the King of England, either... but I
>know he is not because *I* am.
England doesn't have a King, at the moment anyway. I'm guessing you're
American? :->
[snippage]
>>>
>>>There is no indication that he is not a programmer.
>>
>>There is no indication that he is not the King of England, either... but I
>>know he is not because *I* am.
>
>England doesn't have a King, at the moment anyway. I'm guessing you're
>American? :->
England does *so* have a King and it is I... errrrr... Us.... errrr... We!
Who else could sing:
(ahem... a-*hem*! mi mi mi mi...)
God save the gracious Me,
Long live the noble Me,
God save the Me.
Send Me victorious,
Happy and glorious,
Long to reign o'er everybody else
Goo-oooo-ooddd save the Me.
You see? What greater proof is needed? Now if you'll excuse Us We have
to go and march some men up a hill... can't let the King of France get
ahead in that department, you know.
DD
Check out the manual at:
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/dsnaph11/CONTENTS
and for specific examples, check out:
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/dsnaph11/APPENDIX
1.4.6
For "6.2.1 Introduction to stored procedures"
go to
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/dsnaph11/6.2.1#HD
RQSPINT
--
Bill Klein
wmklein <at> ix.netcom.com
"Tim Cordsen" <tim.c...@web.de> wrote in message
news:a3vvqt$2d...@doiftp1.volkswagen.de...
Tim Cordsen wrote:
>
> Just to explain my situation:
>
> I am a programmer, but I was never in the need to know anything about stored
> procedures. Now the project-manager (I am sure that this is not the right
> word, but I don't know the right one) just came to me and said: "find out
> how a stored procedure has to be written and how it works". So I try to find
> out what it looks like and how it works.
> It should only call a transaction on the mainframe and give a string back to
> the calling java program, that's all.
>
Stored procedures can be written in more then one application. I assume
you wish yours to interface with a DB2 table and return information to
the invoking code (there are also Oracle stored procedures)
You will need to code the processing part, compile and bind it
You will need to add the stored procedure to the proper DB2 table with
all the input and output parms properly defined
You will need to GRANT execute permissions to the Stored procedure
You will need to code the calling code properly to send in the input
parms and read back the output parms
Reading the manuals will get you there; this is how I learned to do it,
and I want to let you know it was a long & painful process to carve my
way through the wilderness when other people knew how; but I did not
know who they were.
I would strongly recommend you find a programmer and a DBA who have
worked with Stored procedures. You will need to learn SQL or find some
one who does and some one who has strong JAVA skills & some one who
understands ODBC drivers and connections; you may also need to enlist
the help of someone who understands the RACF or security groups for your
particular shop (for such things as passwords).
I wish you well this was a slow process for me
Susan A
It is the Grand old Duke of York who marches men up and down hills and
vaccillates as to their position when they are half way up or down.
The current Duke of York is in no shape to march anybody anywhere but at
least has the good sense to maintain a lower profile of late. While his
ex-wife is wowing the Americans and endorsing various products (particularly
the "Sarah Ferguson" model human) his sister is currently visiting Antartica
in a campaign to raise money for the restoration of Scott Base. She was in
New Zealand yesterday, passing through Christchurch. The plane had to turn
back to Christchurch due to bad weather in Antarctica and she had a brief
word to NZ TV. Seems a very sensible girl. She's more of a man than the rest
of them put together and would've made an excellent monarch.The system
forbids it of course and Royal promotion is not on merit. Her occasionally
lucid, for the most part away with the Fairies (or Camilla), older brother
must take over the reins and that is why the current incumbent (God Bless
her!) shows no sign of letting go...she takes it far too seriously to let
her idiot son undo what for Her has been a lifetime's work.
Still as Your Majesty is well aware, that's Royalty for you...
Pete
Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------
http://www.usenet.com
We are, at times, subject to being ill-informed by sundry advisors... do
go on!
>
>It is the Grand old Duke of York who marches men up and down hills and
>vaccillates as to their position when they are half way up or down.
Well... it seems as though a sundry advisor *has* misinformed Us... and We
shall muse as to whether this is a mere trifle or something to lose one's
head over.
[snippage of tawdry gossip]
>Still as Your Majesty is well aware, that's Royalty for you...
We know that nepotism plays a hand, aye.
DD
1. If the enquirer does not know what s/he is looking for then how will
they know when they have found it?
2. Bet you don't know the third and fourth verses of God Save Me.
3. As a Scot I object to the implied racial bigotry in God Save Me.
(reference to the fourth verse)
4. I can't count.
In article <a40pug$gaa$1...@panix1.panix.com>, docd...@panix.com writes
--
Alistair Maclean
Life is but a thread, A leash,
At which men strain and yelp
(Throne of Blood, Akira Kurosawa)
ps I'm a Roundhead (not of the phallic form).
Already pointed out earlier in the thread, Mr Maclean.
>
>2. Bet you don't know the third and fourth verses of God Save Me.
Thy choicest gifts in store,
On him be please to pour,
Long may I reign.
May I defend their laws,
And ever give them cause,
To sing with heart and voice,
'Gooo-oooo-oooodddd save the Me!'
From every latent foe,
From the assassin's bow,
God save the Me!
O'er Me thine arm extend,
For Britain's sake defend,
Their father, prince and friend,
Goo-oooo-oooodddd save the Me.
>
>3. As a Scot I object to the implied racial bigotry in God Save Me.
>(reference to the fourth verse)
As the King of England We hope that one might learn the difference between
racism and nationalism.
>
>4. I can't count.
We are forgiving, e'en unto the insignificant... oh, you said 'can't', not
'don't'... We are forgiving, e'en unto the innumerate.
DD
>You see? What greater proof is needed? Now if you'll excuse Us We have
>to go and march some men up a hill... can't let the King of France get
>ahead in that department, you know.
My regrets as to the lost of yer sister Yer Grandee.
As for the King o' France, he's marchin' babes in and out of His
Bedroom.
Reaally!
Actually it was you who 'took it in a certain direction' when you
denigrated Cordsen's programming capabilities based, it appears, on your
'knowledge' of what a stored procedure is.
> without indicating the editing he'd done.
I would be amused to see what 'editing' you are rambling on about.
Ummmm... you mean -
God grant that Marshall Wade,
May by thy mighty aid,
Victory bring,
May he sedition hush,
And like a torrent rush,
Rebellious Scots to Crush,
Goo-oooo-oooodddd save the Me.
- a verse which We have long sinced dropped for both mere chronological
references and the fact that it is about Marshall Wade, not Us? The verse
was common around AD1745 but didn't see much currency after that; the song
didn't become the National Anthem untilAD1790 or so.
>And I
>deliberately pumped up the anger (It should have been Nationalism, not
>Racism) as is common practice in politically correct circles these days.
So... are you, then, 'politically correct' or merely 'becoming your
enemy'?
DD
I see... according to whom?
DD
>>I see... according to whom?
>>
>>DD
>>
>By the final arbiter in all matters, moi.
"Honni soit qui mal y pense", nay?
I see... so you judge a worker with such a judgement.
No... sorry, that was 'arbiter', not Arbeiter.
Carry on.
DD
"The Grand Old Duke of York
He had 10,000 men
He marched them up to the top of the Hill
And he marched them down again.
And when they were up, they were up.
And when they were down, they were down.
But when they were only halfway up,
They were neither up nor down."
Anonymous Nursery Rhyme believed to date from early 18th Century. (Remember,
there has been a Duke of York for around 800 years, so it is difficult to
know exactly WHICH DOY is being referred to...<G>)
Pete.
Doesn't that mean "Honey, don't look now, but your garter's hanging
down..."?
Pete.
Of course, if the DOY could have got his army on to the head of a pin
then quantum physics would apply and they would be both Up and Down (and
all superstates imbetween) at one time.
--
Alistair Maclean
Wherever you find a fathom of water,
there you will find the British.
(Napoleon Bonaparte)