Database Management Systems

SCD stands for Slowly Changing Dimension - a dimension in a data warehouse that evolves over time but not with every transaction. Common examples include customer address changes, product price updates, or employee role changes. Different SCD types (1-6) handle these changes differently: Type 1 overwrites old values, Type 2 maintains history with effective dates, Type 3 adds new columns for previous values, and so on.

Same partitioning means data maintains its existing partition arrangement from the input link - no repartitioning occurs. This is efficient when data is already properly distributed. Entire would broadcast to all nodes, Hash uses key-based distribution, and DB2 uses DB2's algorithm.

Entire partitioning sends every row to every partition/node. This is used when all nodes need complete data, such as creating reference lookup files or when every row must be processed everywhere. Same keeps existing partitions, Hash distributes by key, DB2 uses database partitioning.

Range partitioning groups related records together based on key value ranges, ensuring data with similar keys lands in the same partition. Same maintains existing partitions, Entire broadcasts to all, Hash uses hash algorithm. Range is ideal for maintaining data locality.

DB2 partitioning uses the same partitioning algorithm as IBM DB2 database, allowing DataStage to align with DB2's data distribution. This is useful when data comes from or goes to DB2 tables. Same maintains partitions, Entire broadcasts all rows, Hash uses hash function.

Ordered collection reads all records from the first partition completely before moving to the second, and so on sequentially. Same and Entire are partitioning methods. Hash is a partitioning method. Ordered is specifically a collection strategy.

Database stages (Oracle, DB2, etc.) in DataStage are used for reading from or writing to database tables as sources or targets. They are not used as intermediate stages for data passing within a job. For intermediate data processing, use transformer stages, aggregators, or other processing stages.

The Lookup stage can combine data from heterogeneous sources including databases like DB2 or Oracle with sequential files. It performs reference lookups by matching key fields between the main input stream and reference data sources. Join stages require homogeneous inputs, and Merge stages only combine sorted datasets.

The Read Uncommitted isolation level does not issue shared locks and does not honor exclusive locks, allowing transactions to read data that has been modified by other transactions but not yet committed (dirty reads).

DBCC CHECKDB is a comprehensive command that performs database validation including checks done by DBCC CHECKALLOC (allocation structure validation), CHECKTABLE, and CHECKCATALOG. Running CHECKDB already includes allocation checks, so running CHECKALLOC separately is redundant. CHECKDB is the all-in-one integrity check command for production databases.

Relational databases require transaction logging to ensure ACID properties, particularly durability and recovery. Thus, there must be at least one transaction log file (or log group) associated with each database.

The XML data type in SQL Server 2005 can store up to 2 GB per instance, matching the maximum size for LOB types (varchar(max), nvarchar(max), varbinary(max)). This 2 GB limit applies to the XML document stored in the column. XML data is stored in an internal binary representation that preserves Unicode fidelity.

sp_lock is a legacy system stored procedure that returns information about locks. However, it has been deprecated since SQL Server 2005, and the recommended approach is to use the sys.dm_tran_locks DMV, which provides more detailed and current lock information.

Hibernate eliminates the need for complex JDBC code and direct SQL queries by providing object-oriented querying mechanisms like HQL and Criteria API. This simplifies data access and reduces boilerplate code while maintaining database independence.