Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 Store Procedure - Mutli Region Update

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
gopurs

New User


Joined: 04 Apr 2005
Posts: 7

PostPosted: Thu Mar 15, 2012 9:02 pm    Post subject: DB2 Store Procedure - Mutli Region Update
Reply with quote

I have stored procedure which is running in system which will read data from system region and insert data into the unit region table. I am getting an SQLCODE -817 when executing the stored procedure. This looks to be an issue with the two phase commit. Can somebody tell me how can i insert data into another location by sitting in one region in a DB2 Stored procedure? Is there any parameter/options which I need to set to accomplish this?


-817 THE SQL STATEMENT CANNOT BE EXECUTED BECAUSE THE STATEMENT WILL
RESULT IN A PROHIBITED UPDATE OPERATION.

DB2 version is 9 running in IBM Mainframe Z/OS

Selecting data from system region (DB2V) and then inserting into unit region (DB2T) . The stored procedure is created in system (DB2V) and it is bound to unit region (DB2T) also.
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Thu Mar 15, 2012 9:08 pm    Post subject: Reply to: DB2 Store Procedure - Mutli Region Update
Reply with quote

what happened when ...
You looked at the manual
You asked Your support
The Application designers checked the feasibility
...
...
...

nothing we can do from here
we do not belong to Your organization
and asking you to check the definitions of the DB2 instances involved
and report them so that we cannot advice
is not something that should be expected on a forum
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Mar 15, 2012 9:19 pm    Post subject:
Reply with quote

if you are going to post in multiple forums,
at least provide complete info in both:

Quote:

I am using dynamic sql's in my stored procedure because the db2 schema and location are dynamic and needs to be changes based on the requirements of the caller. This stored procedure is called from a COBOL program running unser CICIS or under IMS in batch under Mainframe.

v_sql = 'select col1, col2 from' || source_schema || '.' || table1;
prepare stmt1 from v_sql
fetch cur1 into variables ...

v_insert1 = 'insert into tagert_loc || '.' || target_schema || '.' || table1'
'(col1,col2) values v_col1, v_col2)'

execute immediate v_insert1;

Note: The above is just an illustration of what I am doing in my stored procedure.

The stored procedure is running in system region(DB2V) and the above select is also happening against the system tables in DB2V so both are in the same DB2 region, but the insert happens in the target region which is in UNIT(DB2T) and has a different DB2 location (ABCDB2T) and that is why we give the three part qualifier for the insert statement. We are getting -817 when we try to insert because it is going to insert into a different region. Please help me in rectifying this issue and tell me how can we do two phase commit process from a stored procedure, what are the setup requirements or do we have to set some parameters before running the stored procedure in the main COBOL driver module?


i would suggest that you unload from db2v and load in db2t.

if you are attempting a proof-of-concept,
you, unfortunately, have no concept of how it is done.

what qualifier is used in the bind parms for the SP in db2v?
Back to top
View user's profile Send private message
gopurs

New User


Joined: 04 Apr 2005
Posts: 7

PostPosted: Thu Mar 15, 2012 9:58 pm    Post subject: Reply to: DB2 Store Procedure - Mutli Region Update
Reply with quote

I am only asking how can we do two phase commits from the stored procedure. I cannot use unload and load utility because this can run in online mode also and we are also changing the data values before insert. I am not doing any proof of concept here, I know this can be done and have done before. I am not the first person who is doing the cross region updates.
It has to be some bind options or some parameters which we have to set to Do remote location updates

Please don't discourage our approach.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Mar 15, 2012 10:15 pm    Post subject:
Reply with quote

not trying to discourage you.

your continual misuse of terms makes me suspect of your competence.

when you say remote location,
do you mean another cics region?

the db2 logic has nothing to do with cics regions.

you have multiple db2 dsid's, and you are attempting within one SP to select from one and insert into another.

i asked you a question. what is the qualifier parm in the bind?

if i were you, i would not worry about the 2 phase commit stuff until
i managed to select from one and insert into another within the same SP.

it would be done the same as if you were doing it in batch with a cobol program.

and, if you have done it before, why can't you do it now?
Back to top
View user's profile Send private message
gopurs

New User


Joined: 04 Apr 2005
Posts: 7

PostPosted: Thu Mar 15, 2012 11:01 pm    Post subject:
Reply with quote

By Remote location I mean data sitting in another DB2 subsystem - SSID. Yes you are correct, I have mutlitple DB2 SSID and I am trying to select from one SSID and trying to insert into another DB2 SSID. I am logged into the Db2 subsystem from where I am selecting the data.

We were able to do it when we were running under the same DB2 sub system - SSID but when we are trying under different DB2 SSID then it is failing with SQLCODE -817

I am not worried about two phase commit at this point, but my call to the stored procedure failed with -817 because it is not able to insert the data in the other db2 sub system from the stored procedure.

The DBA has done the bind for us, but I think the Qualifier he used is the qualifier for the Db2 Stored procedure and it is different from the qualifier for the tables in unit or system regions. Both unit and system has different qualifiers and that is passed a parameter to the stored procedure for building the dynamic SQL.
Back to top
View user's profile Send private message
gopurs

New User


Joined: 04 Apr 2005
Posts: 7

PostPosted: Mon Mar 19, 2012 11:16 pm    Post subject:
Reply with quote

Can anybody help on this?
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Tue Mar 20, 2012 12:31 am    Post subject:
Reply with quote

Not me.

Or did you only want responses from people who could help?

Well guess what, that is what you already got.

Unless of course you consider my comments a help to you.

Here is the help....

Do not troll for additional help when none (or little) has been provided.

It only makes you appear desperate and unable to help yourself.
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us