You are here

Oracle Application Integration

Is there a way to automatically kill long running SQL statements (Oracle DB only) at the database

Is there a way to automatically kill long running SQL statements at the database after a pre-determined maximum waiting time ? 

these SQLs will be triggered by inbound web service calls, which represent 60% of the expected transaction volume in this customer specific scenario.

the driver for the question is performance

SOLUTION


No, there is nothing like that.
Timeouts are usually related to inactivity time.
They are either set at the client application, who is waiting for a response and decides it waited too long and simply moves on to something else.
Or set at the siebel object manager, but again, they´d be for a session that is open but simply inactive for too long.

The scenario described is different : the session would be actively doing some work that took too long. Even if the client disconnects, it´d continue running at the siebel object manager.

The suggestion for such scenario is : Most of the time the task is slow due to a runaway query (Slow SQL Statement).

 

You may work with your DBA to create an Oracle Profile to limit how many seconds a query can run. E.g: 180 sec. Then associated that profile to the user that runs the integration.

NOTE: you may want to use a user different than SADMIN for this.

This will also require that you log in to server manager command line and issue the command :
srvrmgr> change parameter DSDisableExecuteRetry=True for named subsystem ServerDataSrc

DSDisableExecuteRetry is a generic parameter that is available for all supported databases and all object managers.
But the solution depicted here is Oracle specific as it is composed of Oracle profile + DSDisableExecuteRetry.


This is a feature available in siebel 8.0.x that prevents the object manager from re-sending a query to the database layer if an error, such as network error or sesion kill occurs.

You should also consider an analysis of areas in the application where Long Running queries were triggered and provide a functional solution to users to avoid those queries or simply modifyi the application / integration.

For dedicated client,s add DisableExecuteRetry=TRUE Parameter into the [Siebel] Section of the Client's "uagent.cfg" Configuration file.
Is there a way to automatically kill long running SQL statements (Oracle DB only) at the database

Is there a way to automatically kill long running SQL statements at the database after a pre-determined maximum waiting time ? 

these SQLs will be triggered by inbound web service calls, which represent 60% of the expected transaction volume in this customer specific scenario.

the driver for the question is performance

SOLUTION


No, there is nothing like that.
Timeouts are usually related to inactivity time.
They are either set at the client application, who is waiting for a response and decides it waited too long and simply moves on to something else.
Or set at the siebel object manager, but again, they´d be for a session that is open but simply inactive for too long.

The scenario described is different : the session would be actively doing some work that took too long. Even if the client disconnects, it´d continue running at the siebel object manager.

The suggestion for such scenario is : Most of the time the task is slow due to a runaway query (Slow SQL Statement).

 

You may work with your DBA to create an Oracle Profile to limit how many seconds a query can run. E.g: 180 sec. Then associated that profile to the user that runs the integration.

NOTE: you may want to use a user different than SADMIN for this.

This will also require that you log in to server manager command line and issue the command :
srvrmgr> change parameter DSDisableExecuteRetry=True for named subsystem ServerDataSrc

DSDisableExecuteRetry is a generic parameter that is available for all supported databases and all object managers.
But the solution depicted here is Oracle specific as it is composed of Oracle profile + DSDisableExecuteRetry.


This is a feature available in siebel 8.0.x that prevents the object manager from re-sending a query to the database layer if an error, such as network error or sesion kill occurs.

You should also consider an analysis of areas in the application where Long Running queries were triggered and provide a functional solution to users to avoid those queries or simply modifyi the application / integration.

For dedicated client,s add DisableExecuteRetry=TRUE Parameter into the [Siebel] Section of the Client's "uagent.cfg" Configuration file.
Oracle Communication Integration Pack FAQs

  Questions and Answers

  1.      Does Oracle Application Integration Architecture integration for Communications supports Usage Monitor Threshold out-of-the-box?
  2.      Does volume-discount implementation via BRM resource attributes (e.g. discounting based on total bandwidth subscribed across accounts) AIA out-of-the-box support this for integration from Siebel to BRM?
     
  3.      Does AIA out-of-the-box support usage of standard order types such as service relocation, account merge/split, service transfer for integration from Siebel to BRM ?
     
  4.      Does AIA support usage of BRM subscription entities out-of-the-box from Siebel to BRM ?
     
  5.      Is there an AIA PIP that support order-to-activate for Oracle OSM and Oracle Siebel ? The participating applications are : Oracle OSM with Oracle Siebel and Oracle BRM.
     
  6.      Can AIA Foundation pack be run on Oracle BEA ? Is this supported ?
     
  7.      Can AIA Comms PIPs - Agent Assisted Billing Care and Order to Bill be run on Oracle BEA ? Is this supported ?
     
  8.      For Siebel to CRM integration, currently Siebel integration ID is used as identifier and BRM account number is overwritten with some long strings. Can AIA support using BRM generated account number and store it back in Siebel ?
     
  9.      Does AIA support adding additional account-level attributes from Siebel to BRM integration out-of-the-box?
     
  10.      The client requirement requires adding additional service-level attributes from Siebel to BRM. Does AIA support this integration out-of-the-box ?
     
  11.      Can we raise a dispute to BRM using Siebel front end via AIA ?
     
  12.      For Siebel to BRM integration, can AIA support account-level adjustments ?
     
  13.      For Siebel to BRM integration, can AIA support integration of bill image between Siebel and BRM ?
     
  14.      For Siebel to BRM, does AIA support bank guarantee capture and integration?
     
  15.      For Siebel to BRM integration, can AIA support credit limit capture and integration ?
     
  16.      The client requirement requires whether AIA support service-level deposit integration out-of-the-box?
     
  17.      Does AIA support usage of BRM Charge Sharing Groups out-of-the-box from Siebel to BRM ?
     
  18.      Can AIA Foundation Pack, Agent Assisted Billing Care PIP and Order 2 Bill PIP be installed on HP Blade Itanium machines running HP-UX 11.23 platform ?
     
  19.      Does AIA support BRM Discount Sharing Groups out-of-the-box from Siebel to BRM ?
     
  20.      Does AIA support BRM custom resource bucket out-of-the-box from Siebel to BRM ?
     
  21.      Does AIA out-of-the-box support billing activation when a service order is only partially completed ?
     
  22.      For Siebel to BRM, does AIA support account-level deposit capture and integration ?
     
  23.      For Siebel to BRM integration, can AIA support disputes and adjustments audit trail/tracking out-of-the-box?
     
  24.      For Siebel to BRM integration, can AIA support payment capture beyond one-time credit card and debit card payment types out-of-the-box ?
     
  25.      For Siebel to BRM integration, can AIA support unbilled event disputes and adjustments out-of-the-box ?
     
  26.      For Siebel to CRM integration, can AIA support viewing invoice-payment allocation for open item accounting out-of-the-box ?

 

Questions and Answers

Does Oracle Application Integration Architecture integration for Communications supports Usage Monitor Threshold out-of-the-box?

This functionality is not yet available, but on the product roadmap.


 

Does volume-discount implementation via BRM resource attributes (e.g. discounting based on total bandwidth subscribed across accounts) AIA out-of-the-box support this for integration from Siebel to BRM?

This would require Discount Sharing Groups, which is not supported OOTB as yet.


Does AIA out-of-the-box support usage of standard order types such as service relocation, account merge/split, service transfer for integration from Siebel to BRM ?


Service relocation is supported (Move-Add/Move-Delete). Since the solution only supports account level balance groups, and BRM does not support transferring services out of acct level balance group, that is not supported. Account Merge/Split is not supported.




Does AIA support usage of BRM subscription entities out-of-the-box from Siebel to BRM ?

Yes. Products of type subscription are supported OOTB. The PIP implementation guide has information on the billing product types supported.




Is there an AIA PIP that support order-to-activate for Oracle OSM and Oracle Siebel ? The participating applications are : Oracle OSM with Oracle Siebel and Oracle BRM.

This information is unknown so far.



Can AIA Foundation pack be run on Oracle BEA ? Is this supported ?


Yes, AIA Foundaton Pack release 2.3 is certified to be used in

Oracle SOA Suite (10.1.3.4 MLR#3 or higher - 32 bit) , with Oracle WebLogic 9.2.3 as application server, patch 8319296 is required




Can AIA Comms PIPs - Agent Assisted Billing Care and Order to Bill be run on Oracle BEA ? Is this supported ?


Section: Process Integration Packs
Note: WebLogic and Oracle Service Bus are certified with Foundation Pack only.




For Siebel to CRM integration, currently Siebel integration ID is used as identifier and BRM account number is overwritten with some long strings. Can AIA support using BRM generated account number and store it back in Siebel ?


As the billing-care processes are hard-wired to map based on Siebel account number, this become an issue when we want to re-use services for other channels (e.g. IVR)

The solution would need to be extended to support this requirement.




Does AIA support adding additional account-level attributes from Siebel to BRM integration out-of-the-box?


This information is unknown so far.




The client requirement requires adding additional service-level attributes from Siebel to BRM. Does AIA support this integration out-of-the-box ?

This information is unknown so far.



Can we raise a dispute to BRM using Siebel front end via AIA ?

Yes. You can capture disputes in Siebel. Only approved adjustments are sent to BRM by the integration.


Oracle® Application Integration Architecture for Communications 2.0:
Process Integration for Billing Management Implementation Guide
Release 2.0




For Siebel to BRM integration, can AIA support account-level adjustments ?

Adjustments are possible only at header, summary, and event level and not at the Account level.

Oracle® Application Integration Architecture for Communications 2.0:
Process Integration for Billing Management Implementation Guide
Release 2.0

Adjustment Integration Overview
The Adjustment integration between Siebel CRM and Oracle BRM supports the following integration
scenarios:
• QueryAdjustment: enables you to view the adjustments for an invoice in Siebel CRM.
• CreateAdjustment: enables you to create different levels of adjustments (invoice, item, and item details)
for an invoice in Siebel CRM.
The invoice adjustment feature enables CSRs to make adjustments at the invoice header, summary and detail
levels in Siebel CRM. When a customer calls to dispute an item or multiple items on a bill, CSRs first identify
the bill, and then find the disputed items on the bill.
CSRs request adjustments on a variety of levels, as appropriate to the situation. For instance, if the customer
made a 10 minute call that was mistakenly billed as a 30 minute call, CSRs request an adjustment for that
specific call at the call detail level. If, on the other hand, the customer’s plan provided 100 free minutes a
month, but charges started accruing after only 30 minutes, CSRs request an adjustment at the summary level
instead. CSRs can create an adjustment request for one or more lines on a single invoice. The adjustment
request can include the following details per line:
• Account #
• Invoice #
• Request ID
• Requested Date
• Adjustment Amount Requested
• Adjustment Type (e.g. Credit, Debit)
• Reason for Request
• Comments
• Status
• Amount Approved
• Approval Code
• Date Approved

When a CSR clicks the Adjustments tab of the Siebel Billing Profile Portal screen, the adjustment history
information appears. The CSR has to navigate to the Billing Detail view to make adjustments at the header
and item levels. For adjustments at the item detail level, the CSR navigates to the CDR view. Each of the
adjustment requests triggers a separate web services call in the Billing system. The adjustment, if approved,
will be created in the Billing system and will be reflected in the customer’s next bill. If the adjustment is not
approved, the adjustment request is updated with the reason for denial.




For Siebel to BRM integration, can AIA support integration of bill image between Siebel and BRM ?

Not OOTB.

Oracle® Application Integration Architecture for Communications 2.0:
Process Integration for Billing Management Implementation Guide
Release 2.0





For Siebel to BRM, does AIA support bank guarantee capture and integration?

Create Payment feature is for one-time payment and supports only two payment modes, auto-debit and credit card. Payment details are captured in Siebel, but the validation process is handled within Oracle BRM.



For Siebel to BRM integration, can AIA support credit limit capture and integration ?


This information is unknown so far.



The client requirement requires whether AIA support service-level deposit integration out-of-the-box?

Top-Ups are not supported OOTB.



Does AIA support usage of BRM Charge Sharing Groups out-of-the-box from Siebel to BRM ?

AIA for Comms 2.0.1 does not support Charge Sharing Groups OOTB. "Oracle® Application Integration Architecture for Communications 2.0:
Process Integration for Customer Management Implementation Guide
Release 2.0
Solution Assumptions section documents:
5. Sharing groups is not supported for this release.

and

Oracle® Application Integration Architecture for Communications 2.0:
Process Integration for Product Management Implementation Guide
Release 2.0
Solution Assumptions and Constraints section documents:
3. Sharing Groups are not supported out-of-the-box in this release."





Can AIA Foundation Pack, Agent Assisted Billing Care PIP and Order 2 Bill PIP be installed on HP Blade Itanium machines running HP-UX 11.23 platform ?

This information is unknown so far.





Does AIA support BRM Discount Sharing Groups out-of-the-box from Siebel to BRM ?

AIA for Comms 2.0.1 does not support Discount Sharing Groups OOTB.

Oracle® Application Integration Architecture for Communications 2.0:
Process Integration for Customer Management Implementation Guide
Release 2.0
Solution Assumptions section documents:
5. Sharing groups is not supported for this release.

and

Oracle® Application Integration Architecture for Communications 2.0:
Process Integration for Product Management Implementation Guide
Release 2.0
Solution Assumptions and Constraints section documents:
3. Sharing Groups are not supported out-of-the-box in this release.



Does AIA support BRM custom resource bucket out-of-the-box from Siebel to BRM ?

Custom resource buckets are not supported.



Does AIA out-of-the-box support billing activation when a service order is only partially completed ?

AIA Order To Bill provides services to interface the order (basically account level products, and service bundles) to billing. It assumes that only those services that have been activated will be sent to billing.



For Siebel to BRM, does AIA support account-level deposit capture and integration ?

Top-Ups are not supported OOTB.

The Agent Assisted Billing Care PIP does support capturing One-time payments and sending them to BRM.




For Siebel to BRM integration, can AIA support disputes and adjustments audit trail/tracking out-of-the-box?

Siebel/BRM does the audit tracking. Integration only communicates approved adjustments from Siebel to BRM





For Siebel to BRM integration, can AIA support payment capture beyond one-time credit card and debit card payment types out-of-the-box ?

Payment capture beyond one-time credit card and debit card payment types out-of-the-box is not supported.




For Siebel to BRM integration, can AIA support unbilled event disputes and adjustments out-of-the-box ?

Not in 2.0.1. This is scheduled for 2.4





For Siebel to CRM integration, can AIA support viewing invoice-payment allocation for open item accounting out-of-the-box ?


This information is unknown so far.
 

Subscribe to Oracle Application Integration