Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    34

    Unanswered: DB2 Store Procedure - Two Phase commit

    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.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You have to give more information - atleast

    DB2 Version and
    Platform

    You say , you are inserting data in another region. Give details of that region also.

    =
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2003
    Posts
    34
    DB2 version is 9 running in IBM Mainframe Z/OS

    I mean copying 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.

    Please let me know if you need additional info.

    Gopu

  4. #4
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    cut and paste your "from table name" statements.
    Dick Brenholtz, Ami in Deutschland

  5. #5
    Join Date
    Jul 2003
    Posts
    34
    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?

  6. #6
    Join Date
    Jul 2003
    Posts
    34
    Can anybody help me on this?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •