|
View previous topic :: View next topic
|
| Author |
Message |
gopurs
New User
Joined: 04 Apr 2005
Posts: 13
|
|
|
|
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 |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
gopurs
New User
Joined: 04 Apr 2005
Posts: 13
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
gopurs
New User
Joined: 04 Apr 2005
Posts: 13
|
|
|
|
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 |
|
|
gopurs
New User
Joined: 04 Apr 2005
Posts: 13
|
|
|
|
| Can anybody help on this? |
|
| Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
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 |
|
|
|
|
