What is a shared sequence
Use Shared Sequence Watch in Informatica Cloud works best as a clear sequence: define the constraint, compare the realistic options, test the tradeoff, and choose the path with the fewest hidden costs. That order keeps the advice usable instead of decorative. After each step, pause long enough to check whether the recommendation still fits the reader's actual situation. If it depends on perfect timing, unusual access, or a best-case budget, include a simpler fallback.
The simplest way to use this section is to write down the real constraint first, compare each option against it, and choose the path that still works outside ideal conditions.
Create a shared sequence
Use Shared Sequence Watch in Informatica Cloud works best as a clear sequence: define the constraint, compare the realistic options, test the tradeoff, and choose the path with the fewest hidden costs. That order keeps the advice usable instead of decorative. After each step, pause long enough to check whether the recommendation still fits the reader's actual situation. If it depends on perfect timing, unusual access, or a best-case budget, include a simpler fallback.
Configure sequence properties
Setting the correct parameters in the Shared Sequence Watch ensures your data pipeline generates unique identifiers without gaps or collisions. Since these sequences are shared across multiple transformations, precise configuration is critical for data integrity. You will need to define the starting point, the step size, and how the system handles reserved values.
Start value and increment
The Start Value determines the first number the sequence generates. This should align with your existing data or business requirements. For example, if you are migrating from a legacy system with IDs up to 1,000,000, start your new sequence at 1,000,001 to avoid duplicates.
The Increment defines how much the value increases with each generation. An increment of 1 is standard for simple counters. However, if you are generating IDs for multiple parallel processes, you might use a higher increment (e.g., 10) and assign different offset ranges to each process to prevent contention. Think of the increment as the stride length; a longer stride means you skip more numbers, which can help distribute load but may create visible gaps.
Reserved values
Reserved Values allow you to skip specific numbers or ranges. This is useful for reserving IDs for manual overrides, special records, or system-generated flags that shouldn't be part of the automated sequence. For instance, you might reserve values 0-9 for internal test data or specific status codes.
When configuring reserved values, ensure they do not overlap with your start value or increment pattern. If your start value is 100 and you reserve 100-109, the sequence generator will skip ahead to the next available number, potentially causing unexpected jumps in your ID stream. Always verify the reservation logic against your start and increment settings.
Example Configuration
Below is a typical JSON configuration for a Shared Sequence. This example starts at 1,000,000, increments by 1, and reserves no specific values.
{
"sequenceName": "global_customer_id_seq",
"startValue": 1000000,
"increment": 1,
"reservedValues": [],
"maxValue": 999999999
}
Reference shared sequences in multiple mappings
A shared sequence acts as a single source of truth for ID generation across your Informatica Cloud (IICS) environment. Instead of maintaining separate sequence definitions for each data stream, you define the sequence once and reference it wherever needed. This approach ensures consistency and simplifies maintenance. When you update the sequence properties, such as the increment value or cache size, the changes apply to every transformation that uses it.
To use a shared sequence, you must first create it in the Shared Objects folder. Once created, you can reference it in any Sequence Generator transformation within your mappings. The process involves linking the transformation to the shared object rather than creating a new local sequence. This linkage allows multiple mappings to pull from the same counter, ensuring unique values across different workflows.
Add the shared sequence to a mapping
Start by opening the mapping that requires the new ID generation. Drag a Sequence Generator transformation from the palette into the workspace. Double-click the transformation to open its configuration properties. In the Sequence field, you will see options for creating a new sequence or selecting an existing one. Choose the option to browse for an existing shared sequence. Navigate to the Shared Objects folder, select your pre-configured shared sequence, and click OK. The transformation now references the global definition.
Configure output ports and connections
With the shared sequence linked, you need to expose the generated values. The Sequence Generator transformation automatically creates output ports for the current sequence value and the next sequence value. Connect these ports to the target columns in your mapping that require unique identifiers. Ensure that the data types match between the sequence output ports and the target columns. For example, if the target column is an integer, verify that the sequence is configured to generate integer values. This step ensures that the data flows correctly into the target system without type conversion errors.
Validate and test the implementation
Before deploying the mapping, validate the configuration to catch any reference errors. Run a test session with a small dataset to verify that the sequence generates unique values as expected. Check the session logs to confirm that the Sequence Generator transformation is successfully pulling values from the shared object. If you are using the sequence in multiple mappings, test each mapping independently to ensure there are no conflicts. Consistent testing helps identify issues early, preventing data integrity problems in production.
Handle concurrent access and caching
When multiple mappings use the same shared sequence, concurrent access can impact performance. Informatica Cloud caches sequence values to reduce database calls. If you notice gaps in the generated IDs, it is likely due to the cache size. You can adjust the cache size in the shared sequence properties to balance performance and ID continuity. A larger cache improves throughput but may result in more gaps if the service restarts. Choose a cache size that aligns with your volume requirements and tolerance for ID gaps.
Optimize Performance in Shared Sequence Watch
Tuning the Shared Sequence Watch component is less about finding a single magic number and more about balancing throughput against data integrity. By adjusting the cache size and reserved values, you can significantly reduce the number of database calls required to generate unique IDs. This optimization is critical for high-volume data loads where latency accumulates quickly.
The core mechanism relies on pre-fetching a block of sequence numbers into memory. When you increase the cache size, the component retrieves a larger batch of values from the database at once. This reduces the frequency of round-trips to the database, improving overall throughput. However, a larger cache consumes more memory and increases the risk of gaps in the sequence if the integration service restarts unexpectedly.
To illustrate, consider a scenario where your cache is set to 100 but your load averages 500 records per batch. The system will hit the database five times for every batch, creating a bottleneck. Increasing the cache to 500 or higher aligns the fetch cycle with your load profile, minimizing these interruptions.
Managing Reserved Values for Gaps
Reserved values act as a safety net for gaps. If the integration service crashes or restarts, the unallocated values in the current cache block are lost, creating holes in your sequence. By configuring reserved values, you can control how many numbers are "held" or skipped to maintain consistency if needed.
A balanced approach involves setting the cache size high enough to handle peak loads without excessive database calls, while keeping reserved values low enough to preserve sequence continuity. This balance ensures that your ETL processes run efficiently without sacrificing the integrity of your unique identifiers.
Common issues and fixes
Shared Sequence Watch in Informatica Cloud (IICS) can encounter failures that stall your data pipelines. The most frequent problems involve sequence exhaustion due to misconfigured ranges and permission errors when accessing shared resources. Resolving these issues requires checking the underlying database constraints and validating user roles.
Sequence exhaustion
When a sequence reaches its maximum value, it stops incrementing, causing insert operations to fail. This often happens if the cache size is too small or the sequence lacks a cycle. Verify the sequence properties in the database to ensure CYCLE is enabled if you anticipate high-volume usage. Adjust the MAXVALUE or increase the cache size to prevent premature exhaustion.
Permission errors
Accessing a shared sequence requires explicit grants. If a user receives an "insufficient privileges" error, the sharing configuration is incomplete. Right-click the sequence in the database navigator, select Inspect, then go to the Access tab. Add the specific user or role and grant SELECT permission. Without this step, the sequence remains inaccessible to the Informatica service account.
Validation steps
After applying fixes, validate the sequence by running a test load. Check the integration service logs for any remaining errors. If the sequence increments correctly, the issue is resolved. For persistent problems, consult the official Oracle documentation on sharing sequences to verify database-specific constraints.
Frequently asked questions about shared sequences in Informatica Cloud
Shared sequences in Informatica Cloud (IICS) allow you to generate unique identifiers across multiple mappings. Below are common technical questions developers encounter when implementing this feature.
No comments yet. Be the first to share your thoughts!