Tech Blog: Sharing on the Go..

In Oracle SOA Suite , runtime metadata for composite applications, flow instances, and API hits is stored in the SOA Infrastructure Database ( SOADB ). SQL Server users often use NOLOCK to avoid locking rows during queries, but in Oracle, the multi-version concurrency control (MVCC) model inherently prevents readers from blocking writers. However, there are Oracle-specific methods to emulate lock-free or low-lock reads when querying SOADB. This blog will guide you through querying flow instances and API hits data from SOADB and explain how to ensure lock-free behavior. 1. Key Tables in SOADB for Flow Instances and API Hits Flow Instance Data COMPOSITE_INSTANCE : Stores composite-level instance information, such as state, creation time, and last modified time. CUBE_INSTANCE : Provides details about BPEL process-level execution. MEDIATOR_INSTANCE : Contains information about Mediator flows within composite applications. API Hit Data DLV_MESSAGE : Tracks delivery messages, including...

Managing SQL Server Integration Services (SSIS) jobs and their configurations often involves interacting with SQL Server Agent ( msdb ) or the Integration Services Catalog ( SSISDB ). This blog will guide you through accessing, understanding, and updating SSIS-related data step-by-step, with formal design considerations for robust, production-ready implementations. 1. Understanding SSIS Jobs and Data Storage Before diving into T-SQL queries, it’s crucial to understand where SSIS-related data is stored: msdb : Stores SQL Server Agent job details, including schedules and job steps. SSIS jobs are typically defined here as Agent job steps that invoke SSIS packages. SSISDB : Stores deployed SSIS packages and execution metadata. It is introduced in SQL Server 2012 as the Integration Services Catalog. Each database serves different purposes: msdb helps manage job scheduling and orchestration. SSISDB allows for detailed control of SSIS packages, including parameters and execution history. ...