Enterprise Database Systems: AS400, IMS, and Oracle

Oracle's ARCn (Archiver) processes are responsible for archiving filled redo log files. An instance can have up to 10 ARCn processes (ARC0 through ARC9), though typically only one or two are active. This maximum is defined by the LOG_ARCHIVE_MAX_PROCESSES initialization parameter.

When creating a control file (using the CREATE CONTROLFILE command or with CREATE DATABASE), the database must be in NOMOUNT state. In NOMOUNT state, the instance has started with SGA and background processes, but the database is not mounted or open - this is the required state for control file operations.

In IMS (Information Management System), the GN (Get Next) call returns various status codes, but GE is not one of them. The GN call typically returns status codes like GB (segment found), GK (qualified segment found), or GE (end of dataset) is actually associated with GU (Get Unique) calls, not GN calls.

The CHGPF (Change Physical File) command is used to modify a physical file's structure, including adding new fields, without deleting existing records. CHGPFM changes file attributes but not structure. CRTPF creates a new file (which would be empty). The operation preserves existing data while allowing schema evolution.

In AS/400, logical files are views over physical files. When you delete a record through a logical file, the underlying physical file's record is also deleted because logical files don't store data independently - they reference the physical file's data. This is fundamental to how logical files work.

An operational system (also called OLTP - Online Transaction Processing) is used to run day-to-day business operations in real time and is based on current data. This distinguishes it from informational systems (like data warehouses) which support decision-making based on historical data. Operational systems process transactions as they occur.

In a star schema, each dimension table has a one-to-many relationship with the fact table. One dimension record (like one product) relates to many fact records (many sales transactions). This is the fundamental relationship pattern that enables efficient querying in dimensional modeling. Many-to-many and one-to-one don't describe star schema relationships.

Fact tables in dimensional modeling are typically completely normalized - they contain only foreign keys to dimension tables and numeric measures. The normalization prevents update anomalies and ensures efficient querying. Dimension tables, by contrast, are often denormalized for query performance. Fact tables don't contain redundant data.

Reconciled data in a data warehouse context is the atomic layer that serves as the single, authoritative source for all decision support systems. It has been cleansed and integrated from source systems. Option A describes source data, C is too narrow (one system), and D describes data marts or departmental data, not reconciled data.

A data warehouse is organized around major subject areas (customers, products, sales) rather than business applications or functions. This subject-oriented design is a core characteristic. Option A is wrong (end users don't update warehouses), B is wrong (warehouses standardize naming), and D is wrong (warehouses contain historical data).

The %TYPE attribute in PL/SQL declares a variable with the same data type as a specified column in a table. %ROWTYPE declares a record type matching an entire row. AS_COLUMN doesn't exist in PL/SQL. %TYPE is useful for maintaining type compatibility without hardcoding specific types.