Database and SQL

In ETL tools like DataStage, the Join component defaults to accepting two input streams (left and right links). This binary join operation merges data from two sources based on key values, making the default count equal to 2.

The default count for the Join component is 2. This means that by default, the Join operation combines two input flows. If you need to join more than two flows, you must explicitly change the count parameter to match the number of inputs.

Active card information in CMHDB is stored across multiple tables including Card_Pers_Enhanced (enhanced personal card data) and Card_Runstat (card running statistics/usage data). Both tables contain different aspects of active card information, hence all of the above is correct.

CARD_PERS_ENHANCED (Card Person Enhanced) stores comprehensive demographic details about customers. The ENHANCED suffix indicates expanded profile information. CARD_RUNSTAT contains status data, CARD_ACCT_XREF maps relationships, and CARD_LOOKUP provides reference data.

CMHDB uses Cust-acct-ky (Customer Account Key) as the generated key for card identification. This is a composite key that uniquely identifies customer-account relationships. External-cm-no and Internal-cm-no are identifiers, while pcn-no is a different numbering system.

A weak entity depends on another entity for its existence but has its own partial key, called a discriminator, that distinguishes instances of the weak entity that share the same parent entity. This differs from an ID-dependent entity, which requires the parent's identifier as part of its own composite key. Strong entities exist independently, while ID-independent entities have no dependency.

In IMS DL/I databases, attempting to insert a segment that already exists (based on a unique sequence field) returns the 'II' (Invalid Insert) status code. 'IX' and 'ID' are other DL/I status codes but do not represent duplicate insertion.

DataStage requires key columns (typically primary keys) to identify which rows to update in the target. Without keys defined, the update operation cannot determine which specific rows should be modified.

In Informatica, a Lookup transformation can be used to filter records. For example, if a lookup finds no match (returning NULL values), subsequent transformations (like a Filter) can drop those records based on the lookup's output. Thus, filtering via lookup is possible.

The query uses the equality operator (=) in the WHERE clause to match keys from both tables, which defines an equi-join. It is not a natural join because it explicitly names the joining columns rather than matching implicitly.

Embedded SQL refers to hard-coding SQL statements directly within a host programming language like Java, C, or COBOL. The SQL statements are mixed with the application code and processed by a precompiler before compilation. Dynamic SQL (option B) is different - it generates SQL at runtime.

A UNION query combines results from multiple SELECT statements into a single result set. Key requirements: all queries must have the same number of columns, columns must be in the same order, and data types must be compatible. The 'no more than two' limitation in options A and B is incorrect.

Outer joins are specifically designed to include rows that don't have matching values in the joined table. This includes LEFT JOIN (all from left table), RIGHT JOIN (all from right table), and FULL JOIN (all from both). Inner joins and equi-joins only return matching rows.

Both routines (stored procedures/functions) and triggers contain procedural code - SQL statements with control flow logic like IF/ELSE, loops, and variables. The key difference is invocation: routines must be called explicitly, while triggers execute automatically in response to events. Option A correctly identifies their shared characteristic.

A correlated subquery is one where the inner query references a value from the outer query, making it dependent on the outer query's results. The inner query is executed repeatedly for each row processed by the outer query. This is the opposite of a non-correlated subquery, which runs independently once.