Database and SQL

Views in SQL can be created with the CHECK OPTION to make them read-only, and can join multiple tables. Views can have ORDER BY in some databases (though with limitations), and GROUP BY is allowed. Column aliases are optional - the view can use original column names.

SQLSTATE 20000-29999 range indicates execution warnings. SQLSTATE 02000 specifically means 'No data found' which corresponds to SQLCODE 100 in DB2 and other databases - this is returned when a SELECT retrieves no rows or cursor reaches end of data.

A table row can be declared as a header row (for titles/column headers), a static row (with fixed content), or a dynamic row (generated from data sources). All three declaration types are valid in table systems.

The LEADING hint explicitly tells the query optimizer to use the specified table as the driving table in the join order. The driving table is the first table accessed in the join execution plan, which can significantly impact performance.

Oracle provides two primary methods for retrieving execution plans: querying the PLAN_TABLE directly (which stores the plan) or using the DBMS_XPLAN.DISPLAY function for formatted output. The utlxplan view is outdated, and plan_table requires specific column selection.

ENABLE ROW MOVEMENT permits rows to move between partitions when their partition key values are updated. Without this clause, updating a partition key would fail because rows cannot naturally change partitions without explicit permission for row movement.

Oracle provides four primary methods for partitioning existing tables: Online Redefinition (using DBMS_REDEFINITION for minimal downtime), Export/Import (traditional data movement), Insert with Subquery (data transfer between tables), and Partition Exchange (metadata-only swap with pre-built partitions).

In DB2 and similar SQL databases, the WITH HOLD option on a cursor prevents the cursor from closing when a COMMIT operation is performed. Without this option, cursors close automatically at commit, requiring them to be reopened. WITH HOLD maintains cursor position across transaction boundaries.

While Update Strategy is the primary transformation for flagging rows, Custom (or Custom Transformation) can also be used to set row flags for insert, update, delete, and reject operations. Custom transformations allow you to write procedural code that can manipulate row flags based on complex business logic that might be difficult to express in Update Strategy expressions alone. This provides flexibility when complex conditional logic determines how rows should be treated.

To join 4 tables using Joiner transformations in Informatica, you need 3 Joiner transformations. Joiner transformations only accept two input sources (master and detail). The process is hierarchical: join Tables A and B (Joiner 1), join that result with Table C (Joiner 2), then join that result with Table D (Joiner 3). Each joiner reduces the number of data streams by one, so for n tables, you need n-1 joiners.

SQL Server uses Intent locks (IS, IX), Shared locks (S), and Exclusive locks (X) as fundamental locking mechanisms. These prevent concurrent access conflicts. 'Delete' is an operation that acquires locks, not a lock type itself.

In COBOL Report Writer functionality, the report name appears in the RD (Report Description) entry, INITIATE statement, and TERMINATE statement. The SELECT clause is part of the Environment Division's FILE-CONTROL paragraph and is used for file selection, not report selection - it does not contain report names.

In Remedy ARS database schema, forms use specific table prefixes: 'T' for the main data table (regular/form table), 'B' for attachment blob data, and 'H' for history/audit trail. Therefore 'T-Regular Table, B-attachment' (Option B) is correct, and 'H-history, T- regualr, B-attachment' (Option C) is also correct as it includes all three table types.

In ARS database, each attachment requires two tables: one for attachment metadata (fields, size, name, etc.) and one for the actual binary/blob data. This separation allows efficient storage and retrieval of attachment information without loading the entire binary content when only metadata is needed.