Conference orarep::nomahs::sql

3882.0. "Rdb V6.0A ECO6 : RDB-F-EXCESS_TRANS." by svrav1.au.oracle.com::MCHAN () Thu Apr 24 1997 02:47

Rdb V6.0A ECO6

Hi,

One of our customer getting the following error message on their 
COBOL program using SQLMod.

RDB-F-EXCESS_TRANS, exceeded limit of 1 transaction per database attachment
%RDB-F-BAD_TRANS_HANDL, invalid transaction handle
%TRACE-F-TRACEBACK, symbolic stack dump

I did some testing, the following attachments reproduce the problem. You 
will need to create a database with the 'RMU/EXTRACT' supplied, create an 
image with the cobol and SQLMod, and then run BUG.COM.

I believed this is the same reported in Rdb V6.1A release notes.

       2.2.33 Engine transaction left active after SET TRANSACTION fails

              If a non-2pc transaction is started involving more than
              one database, and the first DB starts ok but the second
              one fails for something like LOCK_CONFLICT, then the
              transaction that started on the first DB is not rolled
              back, causing an EXCESS_TRANS error the next time a
              transaction is attempted on that attachment.
                   

It worked fine in Rdb V6.1A ECO1 and Rdb V7 ECO1 but customer unable to
upgrade yet. Any chance this fix will be back ported to next patch
release V6.0A ECOx?

Appreciated for any suggestion.


Thanks,
Michael.


------------------------------------------------------------------------------
$@BUG         
Preparing: select * from bug
Preparing: update bug set f1 = 'Z' where f1 = ?
Preparing: delete from bug
Preparing: select * from bug
Preparing: set transaction read write wait
Preparing: set transaction read only wait
Preparing: set transaction read write wait
Preparing: set transaction read only wait
Preparing: set transaction read only wait reserving bug for shared read
Error calling SQL module:
%RDB-F-EXCESS_TRANS, exceeded limit of 1 transaction per database attachment
%RDB-F-BAD_TRANS_HANDL, invalid transaction handle
%TRACE-F-TRACEBACK, symbolic stack dump follows

module name     routine name                     line       rel PC    abs PC

CHF             COSI_CHF_SIGNAL                   443      00000042  00003984
CHF             COSI$CHF_SIGNAL                   386      00000047  00003941
SQL$GETERR      SQL$SIGNAL_STOP                  4472      00000058  00005340
SQL$EXH         SQL$EXIT_HANDLER                 4335      00000086  00005099
                                                           870108B8  870108B8
TESTER          TESTER                            227      000002AC  00002EAC
------------------------------------------------------------------------------

BUG.COM
=======

$       def bug_db sys$disk:[]bug
$       r/nodeb bug
select * from bug
update bug set f1 = 'Z' where f1 = ?
delete from bug
select * from bug
set transaction read write wait
set transaction read only wait
set transaction read write wait
set transaction read only wait
set transaction read only wait reserving bug for shared read



RMU/EXTRACT of Database
=======================

set verify;
set language ENGLISH;
set default date format 'SQL92';
set quoting rules 'SQL92';
set date format DATE 001, TIME 001;
 --
create database
    filename '$10$DUA2110:[000000.DUMPS.MCHAN.9094630_ALCOA]BUG.RDB'
    dictionary is NOT REQUIRED
    protection is ACL
    number of users 50
    number of cluster nodes 16
    buffer size is 6 blocks
    number of buffers 20
    number of recovery buffers 20
    adjustable lock granularity ENABLED
    global buffers are DISABLED (number is 250, user limit 5)
    carry over locks are ENABLED
    lock timeout interval is 0 seconds
    statistics collection is ENABLED
    system index compression is DISABLED
    No restricted access
    snapshot is ENABLED IMMEDIATE
    -- read write storage area
    locking is row level
    page size is 2 blocks
    allocation is 603 pages
    extent is (minimum 99, maximum 9999, percent growth 20)
    snapshot allocation is 100 pages
    snapshot extent is (minimum 99, maximum 9999, percent growth 20)
; -- end create database
 --
create table BUG (
    F1
        CHAR (1));

commit work;


BUG.COB
=======

Identification division.
program-id. tester.
environment division.
*

INPUT-OUTPUT SECTION.

FILE-CONTROL.
    SELECT the_term
        ASSIGN TO "sys$input"
        FILE STATUS IS ws_the_term_status
        ORGANIZATION IS SEQUENTIAL.

DATA DIVISION.

FILE SECTION.
FD the_term
    record is varying
        from 0 to 1024
        depending on ws_the_term_line_len
    data record is ws_the_term_line.

01 ws_the_term_line PICTURE IS x(1024).

*
working-storage section.
*
COPY "SQL_SQLCA" IN "BUG.TLB".
*
*   Required.
*
    01  ws_program_name                         pic x(31).
    01  WS_LINE_VS.
        05 ws_the_term_line_len                 pic 9(4) comp.
        05 WS_LINE                              PIC X(1024).
    01  WS_STMT_ID                              PIC s9(9) COMP.

    01  ws_the_term_status                              pic x(2).
    01  ws_exit_status                          pic s9(9) comp
                                                    value external 
ss$_normal.
    01  ws_result_status                        pic s9(9) comp
                                                    value external 
ss$_normal.
    01  ws_saved_status                         pic s9(9) comp
                                                    value external 
ss$_normal.
    01  ws_the_status                           pic s9(9) comp
                                                    value external 
ss$_normal.
    01  ws_msg_len                              pic 9(4) comp.
    01  ws_msg                                  pic x(255).

    01  ws_the_term_eof_flag                    Pic x.
        88  ws_the_term_eof                     Value "Y".
        88  ws_not_the_term_eof                 Value "N".

    01  ws_support_variables.

        05  ws_count                            pic s9(9) comp.
        05  ws_ptr                              pic s9(9) comp.
        05  ws_idx                              pic s9(9) comp.
        05  ws_junk                             pic x(255).

01  WS_FIELD_COUNT                              PIC S9(4) COMP.
01  WS_FIELDS_GRP.
    05  WS_VALUES OCCURS 200.
        10  WS_VALUE_POS                        PIC S9(4) COMP.
        10  WS_VALUE_LEN                        PIC S9(4) COMP.
        10  WS_VALUE_IS_NUM_FLG                 PIC X.
        10  WS_VALUE                            COMP-2.

procedure division.

DECLARATIVES.
tt_error SECTION.
    USE AFTER STANDARD ERROR PROCEDURE ON the_term.
tt_error_000.
    if (ws_the_term_status not = "10") and (ws_the_term_status not = "46") 
then
        display "Bad Terminal Status: " ws_the_term_status
        exit program
    Else
        Set ws_the_term_eof to true
    end-if
    go to tt_error_exit
    .

tt_error_catch_all.
    Call "SYS$EXIT" Using
        Omitted
    .

tt_error_exit.
    exit
    .

END DECLARATIVES.


000_main_sec section.
000_main.

    perform init_io

    Call "TEST_ST" Using
        SQLCA

    Perform with test after
            Until ws_the_term_eof
        read the_term
        if (ws_the_term_line_len not = 0) and ws_not_the_term_eof then
            Move ws_the_term_line to WS_LINE
            Perform Main_Work
        End-If
    End-Perform

    Call "TEST_COMMIT" Using
        SQLCA

    Perform Exit_Program
    .

main_work.

    Initialize WS_STMT_ID

    Display "Preparing: " WS_LINE(1:ws_the_term_line_len)

    Call "TEST_PREPARE" Using
        By Reference SQLCA
        By Reference WS_STMT_ID
        By Reference WS_LINE_VS
    Perform Abort_If_SQLCODE_Non_Zero
    .

check_status.
    if ws_result_status is failure then
        move ws_result_status to ws_exit_status
        perform exit_program
    end-if
    .

display_failure_status.
    if ws_result_status is failure then
        display "Failure status:"
        move ws_result_status to ws_the_status
        perform display_status
    end-if
    .

display_result_status.
    display "Result status:"
    move ws_result_status to ws_the_status
    perform display_status
    .

display_status.
    move ws_result_status to ws_saved_status
    call "sys$getmsg" using
        by value ws_the_status
        by reference ws_msg_len
        by descriptor ws_msg
        omitted
        omitted
        giving ws_result_status
    perform check_status
    display "    " ws_msg(1:ws_msg_len)
    move ws_saved_status to ws_result_status
    .

Abort_If_SQLCODE_Non_Zero.
    If sqlcode Not = 0 Then
        Call "SQL$GET_ERROR_TEXT" Using
            By descriptor ws_msg
            by reference ws_msg_len
        Display "Error calling SQL module: " ws_program_name
        Display ws_msg(1:ws_msg_len)
        Perform exit_program
    End-If
    .

exit_program.
    exit program
    stop run
    .

init_io.
    open input the_term
    Set ws_not_the_term_eof to true
    .


BUG.TXT - Put into a text library - BUG.TLB under name SQL_SQLCA
================================================================

01      SQLCA   GLOBAL.
        02      SQLCAID PIC X(8) VALUE IS "SQLCA   ".
        02      SQLCABC PIC S9(9) COMP VALUE IS 128.
        02      SQLCODE PIC S9(9) COMP.
        02      SQLERRM.
                03      SQLERRML PIC S9(4) COMP VALUE IS 0.
                03      SQLERRMC PIC X(70).
        02      SQLERRD PIC S9(9) COMP  OCCURS 6 TIMES.
        02      SQLWARN.
                03      SQLWARN0 PIC X.
                03      SQLWARN1 PIC X.
                03      SQLWARN2 PIC X.
                03      SQLWARN3 PIC X.
                03      SQLWARN4 PIC X.
                03      SQLWARN5 PIC X.
                03      SQLWARN6 PIC X.
                03      SQLWARN7 PIC X.
        02      SQLEXT PIC X(8).


TEST_SQL.SQLMOD
===============

MODULE TEST_SQL
LANGUAGE COBOL
AUTHORIZATION BUG_DB
DECLARE GLOBAL bug_db SCHEMA AUTHORIZATION FOR FILENAME BUG_DB

PROCEDURE TEST_PREPARE
    SQLCA
    pSQL_ID                 Integer
    pSQL_STMT               Varchar(1024)
    ;

    PREPARE pSQL_ID FROM pSQL_STMT
    ;

Procedure Test_ST
    SQLCA
    ;

    Set Transaction Read Only Wait
    ;

Procedure Test_Commit
    SQLCA
    ;

    Commit;

------------------------------------------------------------------------------

    Hi Micheal,
    	I suggest we take this off-line, in view of this quote from RDB_70
    -------------------------------------------------------------------------
    Note 190.6                      Status on 6.0-x?                  6 of 7
    NOVA::BALL_A "In need of sustaining - engineer"   9 lines 17-APR-1997 09:23
                            -< End of the line (almost...) >-
    -------------------------------------------------------------------------
        Last time I checked there was one relativley minor problem fixed since  
    we built the 6.0A ECO6 kit.
            Based on this, I would think it very unlikely that we would
    produce another ECO - if someone has a major problem, and they can't
    upgrade, then we would perhaps make special images for them - however, 
    with the current rate of reported problems for 6.0, I would see AE6 as 
    the last cut...
        
        Alan Ball (6.0 P/L) 
    
    ----------------------------------------------------------------------