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
 
To execute query held in a stem var

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

New User


Joined: 10 Feb 2009
Posts: 7
Location: India

PostPosted: Mon Feb 16, 2009 5:34 pm    Post subject: To execute query held in a stem var
Reply with quote

hi
I have a query which is held in a stem variable in parts as given below

ARR.1=UPDATE CLIENT_TAB
ARR.2=SET EMP_NO='22222'
ARR.3=WHERE EMP_NO='11111'

im not able to execute this query held in stem vars through REXX
there are syntaxes in IBM manual for executing statements in a single variable like given below

statement = "UPDATE STAFF SET JOB = 'Clerk' WHERE JOB = 'Mgr'"
CALL SQLEXEC 'EXECUTE IMMEDIATE :statement'
IF ( SQLCA.SQLCODE < 0) THEN
SAY 'Update Error: SQLCODE = ' SQLCA.SQLCODE


But Im not able to execute the above syntax for query held in a stem
I tried to do use the below syntax too

CALL SQLEXEC ':ARR.1 :ARR.2 :ARR.3 :ARR.4"

but still it give syntax error
Concateneting the stem variable into a single variable and executing it wont serve my purpose

is there any way to execute a query held in stem varable or in multiple variables??
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10373
Location: italy

PostPosted: Mon Feb 16, 2009 5:42 pm    Post subject: Reply to: To execute query held in a stem var
Reply with quote

Quote:
Concateneting the stem variable into a single variable and executing it wont serve my purpose


why not ?
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8614
Location: Back in jolly old England

PostPosted: Mon Feb 16, 2009 5:45 pm    Post subject:
Reply with quote

Quote:
there are syntaxes in IBM manual for executing statements in a single variable like given below

Perhaps that is the method that will work.

Quote:
Concateneting the stem variable into a single variable and executing it wont serve my purpose

Would you care to elaborate on the reasoning behind this statement.
Back to top
View user's profile Send private message
rohit2219

New User


Joined: 10 Feb 2009
Posts: 7
Location: India

PostPosted: Mon Feb 16, 2009 5:53 pm    Post subject:
Reply with quote

this is because my query is not so small as given below..it may extend to more than 500 letters of code

It can be an insert query which can amount to more than more than 1000 letters and if Im correct a rexx variable can only hold 255 letters (im not sure on this).
Is there any upper limit regarding the letters or bytes which a variable can hold?
If no, then Concateneting the stem variable into a single variable and executing it maybe a temporary solution to my problem
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8614
Location: Back in jolly old England

PostPosted: Mon Feb 16, 2009 5:56 pm    Post subject:
Reply with quote

Then I suggest that you try that method and report back with the results.
Back to top
View user's profile Send private message
rohit2219

New User


Joined: 10 Feb 2009
Posts: 7
Location: India

PostPosted: Mon Feb 16, 2009 5:56 pm    Post subject:
Reply with quote

as of now I maybe able to execute it as given below

statement=ARR.1 || ARR.2| | ARR.3

CALL SQLEXEC 'EXECUTE IMMEDIATE :statement'
IF ( SQLCA.SQLCODE < 0) THEN
SAY 'Update Error: SQLCODE = ' SQLCA.SQLCODE

But what possible problems can arise if the array is bigger??
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8614
Location: Back in jolly old England

PostPosted: Mon Feb 16, 2009 5:58 pm    Post subject:
Reply with quote

If you think about it, you are concatenating the stem variables into one string anyway.

Have you considered writing all of the statements to a FB 80 dataset and then using that as a SYSIN for your query ?
Back to top
View user's profile Send private message
rohit2219

New User


Joined: 10 Feb 2009
Posts: 7
Location: India

PostPosted: Mon Feb 16, 2009 6:08 pm    Post subject:
Reply with quote

Hi expat

Im executing the DB2 statements in rexx using DSNREXX..
How can I execute the query after writing it into a dataset? are you suggesting to use a job to execute this query?thats also one possible solution...
i will try the other method and post the results anyways? But will concatenation help in executing a big query?
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10373
Location: italy

PostPosted: Mon Feb 16, 2009 6:10 pm    Post subject: Reply to: To execute query held in a stem var
Reply with quote

Quote:
Is there any upper limit regarding the letters or bytes which a variable can hold?


why don' t You test it Yourself

Code:
/* rexx */
Trace "O"
string = ""
do   forever
   say right(length(string),8)
   string = string || copies(" ",1024)
end
exit
Back to top
View user's profile Send private message
rohit2219

New User


Joined: 10 Feb 2009
Posts: 7
Location: India

PostPosted: Mon Feb 16, 2009 6:12 pm    Post subject:
Reply with quote

Hi

thanks for the suggestions I will test these and get back with results
Back to top
View user's profile Send private message
rohit2219

New User


Joined: 10 Feb 2009
Posts: 7
Location: India

PostPosted: Sun Feb 22, 2009 10:41 am    Post subject: Reply to: To execute query held in a stem var
Reply with quote

hi

as of now concatenating the stem variables into a single variable seems to work...i concatenated and executed queries with some 200-300 line length..seems to be fine.....hope no probs arise in the long run icon_biggrin.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Feb 22, 2009 11:19 am    Post subject:
Reply with quote

Hello,

Thanks for letting us know it is working icon_smile.gif

You might consider checking the lengths and have a controlled termination rather than a surprise abend. . .
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 query to fetch record which has only ... maxsubrat DB2 3 Mon Dec 11, 2017 5:03 pm
No new posts How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm
No new posts Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm
No new posts Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 4 Tue Nov 07, 2017 8:34 pm
No new posts Query on XMITIP abdulrafi All Other Mainframe Topics 1 Wed Oct 25, 2017 6:54 pm

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