Mainframe: Code DB2 on COBOL Programs for Absolute Beginners

Create two tables, store and item, with fields and data types: store id integer, store name character(20), item id smallint, item receiving date date, item price decimal(10,2), and item quantity smallint.

Create views in DB2 by defining store_item view as a virtual table that selects store_id from store and item_id and item_name from item, illustrating how views depend on main tables.

Learn how to alter a table by adding new columns such as store_location with varchar(50), renaming columns like store_location to store_LOC, and adjusting data types or lengths, with syntax tips.

Learn how to drop a table in DB2, permanently removing the table and its related views, synonyms, and indexes, and verify that the table no longer exists.

Learn to write select queries to retrieve all or specific columns, with optional where, group by, having, and order by clauses, and use operators and in lists to filter results.

Explore selecting records with between for numeric ranges. Learn how like patterns enable non-numeric comparisons using percent and underscore wildcards, with examples using store id and store name.

Explore how exists and not exists test for at least one row in a subquery. Compute totals with sum and group by to aggregate store balances by store_id.

Learn how the having clause applies to aggregate functions in DB2, why where cannot filter aggregates, and see examples using sum, avg, min, max, and count.

Learn to use the order by clause to sort records in ascending or descending order, with example sorting store_id from t2 and combining with where, group by, having, and aggregates.

Update a table using the sql update statement to set item_id and item_received_date for a specific store_id, and verify changes via a select.

Execute the delete operation with delete from table where condition to remove specific records, using a sample store_id, and compare with select, update, and insert concepts.

Learn about keys and indexes, focusing on unique keys defined as not null and unique in ddl, with employee table illustrating need for a primary or unique index before inserts.

Learn how to enforce data integrity and speed up query performance by creating a unique index on a table, using the syntax create unique index index_name on table_name(column_name).

The lecture defines null, not null, and default at the column level, showing how not null prevents nulls and default supplies a value in the emp_dept table.

Learn how referential integrity keeps data consistent between parent and child tables by using foreign keys that reference primary keys, with cascade, restrict, or set null options.

Explore referential integrity by creating store and items tables, defining primary and foreign keys, and comparing on delete restrict and cascade to manage cascading deletes.

Explore the six steps to create a COBOL DB2 program, including creating tables and optional DCL Gen, and mapping DB2 columns to equivalent COBOL variables.

Learn how precompile separates non sql and sql COBOL statements, compile and link edit the program, bind with DB2 to create a plan or package, then run via SQL.

Demonstrates using jcls to execute a cobol db2 program, including precompile, compile and link steps, and the db2 bind and run jcl.

Learn the structure of a cobol db2 program, including embedded sql blocks, sql code and sql state checks, and how to include table definitions for db2 operations.

Learn to code a COBOL DB2 program that uses embedded SQL inside X SQL END X to create a table, then precompile, bind, and run under a DSN subsystem.

Insert records into a DB2 table with a COBOL program using hard coded values, then run, bind, and verify investor entry, and note host variables discussed in the next section.

Insert records into the investor table on mainframe DB2 by generating SQL and COBOL host variables from JSON data, mapping columns to COBOL variables, and using colon host variables.

Learn dml operations by selecting and updating records, and inserting data from investor to a new company table. Explore creating the issuer table, mapping DB2 columns to COBOL host variables.

Learn to use embedded SQL in COBOL with DB2 to perform select and update operations, map DB2 columns to host variables, and check SQL code results.

Describe a DB2 on COBOL mainframe project that reads input file with i, u, d flags to insert, update, or delete records in emp_dept and depts, displaying results in spool.

Create two DB2 tables with primary and foreign keys and a unique index; prepare a COBOL DB2 program to read input file and perform insert, update, or delete by flag.

Outlines the structure of a cobol db2 program for a project, covering sequential file, host variables, embedded sql for insert update delete, and precompile, compile, link edit, and bind steps.

Recompile, then combine and run the Liberta COBOL project to execute input records, inserting, updating, and deleting employee and department data, and verify results through table queries.

Understand how db2 treats null values as missing data and use a null indicator in cobol db2 programs to handle them.

Learn how to tackle null values in DB2 COBOL programs by using a null indicator to detect nulls, check SQL code, and update records when nulls are present.

Discover how to insert null values in a DB2 COBOL program using a null indicator, by moving minus-1 to the indicator and verifying with select queries.

Learn to process multiple records from a DB2 select using cursors in COBOL programs. Declare, open, fetch into host variables in a loop, and close the cursor to access rows.

Declare and open a DB2 cursor in COBOL, fetch multiple records from the company table, and display each record in a looping paragraph.

Update a specific column in db2 using a cursor in cobol. Declare the cursor for update of the column, fetch records, and use where current of to update each row.