DB2 for z/OS
IBM's database on the mainframe. The bank's accounts live in its tables.
Also written Db2, DB2, Db2 for z/OS
Why it matters. Balances, customers and payments are rows in Db2 tables. Most of your questions about them are one short SQL statement.
Try it DSNTEP2
- 1
You're in the SYSIN lines of job DB2TRY. It runs DSNTEP2, the Db2 program that runs SQL in batch. Write the statement that counts the accounts in the table M1B.ACCOUNTS.
SYSIN SELECT COUNT(*) FROM M1B.ACCOUNTS; - 2
Add a second statement: how many accounts are there of each type? Count them, grouped by ACCTTYPE.
SYSIN SELECT ACCTTYPE, COUNT(*) FROM M1B.ACCOUNTS GROUP BY ACCTTYPE; - 3
Submit the job DB2TRY from your DEV JCL library.
SUBMIT 'ZB0001.DEV.JCL(DB2TRY)' - 4
Read the output of the job.
OUTPUT DB2TRY
Remember
- Db2 keeps data in tables: rows and columns.
- On z/OS you reach it with SQL: from SPUFI in ISPF, batch programs such as DSNTEP2, or COBOL programs.
- Db2 returns a SQLCODE after every statement.
The usual mistake
Looking for the accounts as a file you open with a DD
A Db2 table is not a file you open. You ask for its rows with SQL.
SELECT ... FROM M1B.ACCOUNTS, run by DSNTEP2 or SPUFI
On real z/OS
This lesson runs SQL the way a batch job does, through DSNTEP2 (the DSN command runs it under IKJEFT01 in a job). The DSNTEP2 listing in MF/OS is simplified and does not show the real page headers. Real z/OS has no SQL command at READY: you use SPUFI in ISPF, DSNTEP2 or DSNTIAD in batch, QMF, or a program. MF/OS adds an SQL command at READY as a shortcut, and this lesson does not use it. MF/OS runs one user at a time, with no isolation levels, and uses ASCII order for ORDER BY.
Where can I practise DB2 for z/OS?
Right here, at ZedBank. You take a junior job at a simulated bank and use DB2 for z/OS in real work, with Zed beside you. There's nothing to install.
Start at ZedBankRelated
- SQLThe language used to query and change relational data. The same SQL used everywhere else, running against mainframe tables.
- Embedded SQLSQL statements written directly inside a COBOL program, marked off so they can be prepared before compiling.
- BINDPreparing a program's SQL for execution, working out how each statement will access the data and storing the result.
- SQLCODEThe result code DB2 returns after every SQL statement, saying whether it worked and what happened if it did not.
- SubsystemA major piece of software running under z/OS that provides a service to applications, such as a database or a transaction manager.
- IMSAn older IBM transaction manager and hierarchical database, still running some of the highest-volume systems in the world.