IBM DB2 - Basics

Install IBM DB2 and IBM Data Studio on Windows. Download trial installers, install DB2 Connect Server, configure localhost DB2 instance on port 50,000, and create the record rack database for Data Studio.

Learn how to configure the ODBC driver in Windows, including differences between ODBC and JDBC, DSN vs connection string, and 32 bit vs 64 bit drivers.

Learn to create and configure user and system DSNs for IBM DB2 using ODBC, including registry updates and testing connections via DB2 Data Studio.

Discover how schemas act as namespaces, organizing data and defining relationships between tables in a relational database. Create schemas in IBM DB2 using IBM Data Studio and assign ownership.

Create and explore a database table by organizing data into rows and columns, including an employees table in IBM DB2 via SQL scripts or Data Studio.

Learn to perform an insert operation in IBM DB2 by using insert into schema.table with column names and values, execute via IBM Data Studio, and verify the inserted data.

Learn to perform a DB2 update using the syntax update schema.table set column = value where condition, demonstrated by changing an employee address from New York to Sydney.

Master the select operation in IBM DB2 by writing select queries with where conditions, using star for all columns, and inserting records, including in-clauses for multiple ids.

Learn how to perform delete operations in IBM DB2, including the syntax delete from schema.table where condition, with a practical example deleting employee_id 55001 in a data studio session.

Explore how stored procedures encapsulate pre-compiled sql statements in the database and accept parameters for repeated use. Learn to create, deploy, and run db2 procedures returning result sets.

Explore predefined and user defined functions in IBM DB2, using aggregate avg and scalar substring to compute salaries, filter employees, and modify the employees table.

Learn to create and deploy user defined functions in DB2, including scalar and table returning templates, returning integers and result sets, to get employee counts and names.

Explore how to enforce unique keys in DB2 by applying unique constraints, which create a unique index automatically, ensuring non-null values and managing constraints via alter table scripts.

Explore primary keys in IBM DB2 by defining a unique, not-null key for the employee_id, and manage constraints with add and drop operations on the employees table.

Learn how foreign keys create parent–child table relationships, enforce references to a primary key in the parent table, and prevent invalid inserts or deletions in the employees and companies tables using company_id.

Explain how the order by clause sorts results in ascending or descending order using ASC and DSC, with ascending as the default.

Group by clause groups identical data and works with aggregate functions such as count and avg. See examples using the employees table in ibm db2 to count by company_id.

Learn to use the having clause with group by in IBM DB2, filtering aggregated results by conditions such as company_id <= 1002, with practical SQL examples.