Database Management Systems

Teradata Manager is the administrative tool specifically designed for monitoring, collecting, and analyzing database performance metrics, resource usage, and system health. BTEQ is a query utility, Teradata SQL Assistant is for SQL development, and TDQM is not a standard Teradata performance tool.

Archive/Recovery utility is designed to copy tables (archive) and restore them to another database (recovery). BTEQ is for query processing, Table rebuild is for rebuilding damaged tables, and FastLoad is for high-speed initial data loading into empty tables.

With Partitioned Primary Index (PPI), data is first distributed based on the Primary Index (PI) across AMPs, then partitioned within each AMP based on the partitioning column. The primary distribution mechanism is still the Primary Index hash. Secondary index and primary key don't control distribution in PPI.

MultiLoad is the utility designed for batch processing and block-level loading against already-populated tables. It can handle INSERT, UPDATE, DELETE, and UPSERT operations efficiently. FastLoad is for initial load to empty tables only, TPUMP is for continuous trickle feed, and BulkLoad is not a standard Teradata utility.

The Teradata Index Wizard analyzes a query workload to recommend secondary indexes (USIs and NUSIs) that will improve query performance, rather than primary indexes which are defined during table creation.

Teradata uses a shared-nothing architecture where each node (with its AMPs) has its own memory and disk storage, and doesn't share resources with other nodes. This enables linear scalability. Shared memory (SMP) and shared everything are different architectures where resources are shared across processors.

A Non-Unique Primary Index (NUPI) can group rows with the same index value on the same AMP, which makes query access, aggregations, and joins on those columns highly effective, despite not guaranteeing uniform distribution.

TDQM (Teradata Query Management) is the component that enforces query limits based on predefined thresholds for resource usage. Teradata Manager is an administrative tool but does not directly limit queries. BTEQ is a query utility, and QueryMan is a legacy query interface.

In DataStage Oracle OCI stage, the Array Size property controls the number of rows written in each batch to the database. Increasing this value reduces network round trips between DataStage and Oracle by buffering more rows before sending them, which significantly improves write performance for bulk operations. This is a performance tuning technique specifically for write operations, not reads.

Informatica provides transaction control variables for managing session commits. TC_CONTINUE_TRANSACTION keeps the transaction open, TC_COMMIT_BEFORE commits before processing, and TC_ROLLBACK_BEFORE rolls back before processing. TC_STOP_TRANSACTION is not a valid transaction control variable - the correct options for stopping are handled through transaction settings or workflow conditions, not this variable name.

In Informatica repository, Delete moves objects to the recycle bin from where they can be recovered and restored. Purge permanently removes objects from the repository with no recovery option. The distinction is important for version control - deleted objects remain recoverable until purged, while purged objects are completely gone and cannot be reused.

Turbo Integrator uses ODBC (Open Database Connectivity) as the standard data source type for connecting to external databases. ODBC is a widely-accepted standard for database connectivity that provides a consistent interface across different database systems. Option A (ODBO) is a related but different standard for OLAP databases. Option B (TM1) is the application itself, not a data source type. Option D (Text) refers to flat file sources, not databases.

Subject-based addressing in messaging systems routes messages based on a topic or subject line rather than explicit source or destination addresses. This facilitates decoupled communication between data producers and data consumers. Other options are incorrect because they imply direct identification or addressing between specific producers and consumers.

The 'arraysize' property controls how many rows are fetched per network round-trip when reading from Oracle, which significantly impacts performance. Setting a larger array size reduces network overhead but uses more memory. The 'memsize' property is not a standard Oracle Enterprise stage property, 'partitionsize' is not applicable to Oracle reads, and 'transactsize' relates to write operations not reads.

In IBM InfoSphere DataStage, importing metadata from third-party data modeling tools (such as CA Erwin or Rational Data Architect) is achieved via the Meta Integration Model Bridges (MIMB), represented by importing using bridges.