Gen EDGE

I have my query ca gen 

229 | | += SUMMARIZE EACH GROUP OF sales_document
230 | | | sales_payment
231 | | | WITH THE SAME sales_payment request
232 | | | sales_document document_number
233 | | | sales_payment entity
234 | | | TARGETING grp_out_saldoc_commit_nr FROM THE BEGINNING UNTIL FULL
235 | | | PLACING sales_payment request INTO out_list_salpay sales_payment request
236 | | | PLACING sales_document document_number INTO out_list_saldoc sales_document document_number
237 | | | PLACING sales_payment entity INTO out_list_salpay sales_payment entity
238 | | | SORTED BY DESCENDING sales_document document_number
239 | | | WHERE DESIRED sales_payment belongs DESIRED sales_document
240 | | | AND DESIRED sales_document invoice_status IS EQUAL TO "P"
241 | | | AND DESIRED sales_payment type IS EQUAL TO "DE"
242 | | | AND DESIRED sales_document commitment_number IS EQUAL TO SPACES

The problem is the both tables is very big, one of them has more than 90 million lines... because off that, it always have time out.

TIRM030E:  APPLICATION FAILED - UPDATES HAVE BEEN BACKED OUT                   

TIRM031E:  FAILING PROCEDURE EXIT DATA FOLLOWS                                 

TIRM032E:  LAST OR CURRENT ACTION BLOCK ID   =     1900891396                  

TIRM033E:  LAST OR CURRENT ACTION BLOCK NAME  =      AGENT_INVOICING_SEARCH_CO 

TIRM034E:  LAST OR CURRENT DATABASE STATEMENT =      67                        

TIRM035E:   CURRENT STATEMENT BEING PROCESSED =      0000000067                

TIRM038E:   ** FATAL DATABASE ERROR WAS ENCOUNTERED **                         

TIRM039E:                      DB LAST STATUS =      DB                        

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

ORA-24761: transacção anulada                                                   

SQL-02114: Invalid SQL Cursor usage: trying to CLOSE a CLOSEd cursor           

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

    RS6E                TIRM046E:     TRANSACTION PROCESSING TERMINATED        

TIRM602E:  REMOTE COOPERATIVE PROCESSING WAS ACTIVE                            

TIRM604E:  PROCEDURE STEP AND TRANCODE TRACE BACK                               

RS_SL_AGENT_INVOICING            RS6E   

The tables already has indices wiche has the fields that i am using for my search.

There is other ways to put my query more fast with this parameters?

thanks. 

Hi Mario,

I see ORA-24761 is "transaction rolled back"

What type of Gen Oracle application is being used e.g. C server, EJB via AppServer?

I was going to suggest the "Multiple Row Fetch" option but that is not available for SUMMARIZE EACH and you would have to use READ EACH.  READ EACH Properties Dialog - General - CA Gen - 8.6 - CA Technologies Documentation 

DariusPanahy would probably have SME input on this topic.

Thanks

Lynn

We think it is EJB via AppServer, it is interconnected by linux machines Tuxedo unix... our data base is Oracle.

With such big tables, the first thing to look at is the indexes.

I see that you've a SORTED BY ... DESCENDING. If your index is sorted that way, fine, but if it's sorted ascending, it will be ignored, which will result in very long response times.

Hi there, yes... i took it out my "SORTED BY" and it gain more speed directly from Oracle, but steel, from Ca Gen steel have time out.

Hi Mario,

As it is a Gen EJB via AppServer could the AppServer JTA (Java Transaction Api) timeout and Oracle database distributed_lock_timeout values be relevant?

This Oracle Doc ID has an example of similar symptoms using WebLogic (you will need an Oracle account to view it)

Java.sql.SQLException: ORA-24761: Transaction Rolled Back (Doc ID 1572916.1)

Regards,

Lynn