Database and SQL

TRUNCATE is classified as DDL because it resets table storage and cannot be rolled back, unlike DELETE which is DML. DDL commands implicitly commit and modify database structure or metadata. This distinction is crucial for understanding transaction behavior and data recovery options.

DDL (Data Definition Language) commands define and modify database structures like tables, indexes, and schemas. DML (Data Manipulation Language) manipulates actual data with commands like INSERT, UPDATE, DELETE. DCL (Data Control Language) controls access permissions (GRANT, REVOKE), while TCL (Transaction Control Language) manages transactions (COMMIT, ROLLBACK).

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.