DB2 Sample Programs

Why Db2 sample programs matter

An SQL statement shown in a manual proves the grammar, but an application sample shows the complete route to execution. A COBOL, PL/I, or C program needs host variables and error handling. Static SQL must be processed into a database request module (DBRM). Host-language code must be compiled and link-edited. The DBRM must be bound to a package, and a plan must make the package available at run time. Finally, JCL must select the correct load library, subsystem, plan, input, and output.

Samples make those connections visible. They also preserve details that are easy to omit in a tiny example: SQLCA or SQLSTATE checks, null indicator variables, cursor processing, COMMIT and ROLLBACK, attachment interfaces, compiler options, and expected return codes. Use each sample as a map. Ask what it demonstrates, which parts are required by Db2, and which names merely reflect IBM's installation verification environment.

SDSNSAMP, SDSNIVPD, and related libraries

prefix.SDSNSAMP

SDSNSAMP is the main Db2 sample library installed and maintained by SMP/E. IBM documentation identifies it as the source location for sample programs such as static SQL examples in COBOL, C, and PL/I and the PL/I dynamic SQL program DSNTEP2. It also contains sample jobs, members used to install Db2-supplied JCL procedures, and installation or migration material. The prefix can look like DSN1310, DB2.V13, or another site standard; never guess it from a book.

prefix.SDSNIVPD

SDSNIVPD is the IVP data library. IBM describes it as the SMP/E target library for installation verification procedure input and expected output from sample applications. The generated verification jobs can use those reference members to prove more than “the subsystem started.” They test whether selected programming languages, SQL paths, objects, and runtime services produce the expected behavior.

Libraries you will see around the samples

• SDSNLOAD contains Db2 executable modules and language-interface support used during preparation or execution.
• SDSNDBRM contains DBRMs supplied with Db2; your copied application normally writes its DBRM to a separate project DBRM library.
• SDSNC.H and other include libraries provide C headers or language-specific material needed by selected samples.
• SDSNSPFP, SDSNSPFM, and SDSNCLST support Db2 ISPF panels, messages, and CLISTs rather than serving as ordinary application source libraries.
• NEW.SDSNSAMP is a common description for the writable, tailored output library created by the installation CLIST. It is distinct from the original SMP/E target library.

Library names vary by release and installation. Read the generated JCL and the installation defaults member to find the actual qualifiers. Treat IBM product libraries as read-only, even if your user ID technically has update authority.

A map of important sample programs

IBM has shipped many DSN8-prefixed educational programs and DSNTEJ-prefixed verification jobs across releases. The exact inventory changes, so use the documentation matching your installed Db2 level. A few names help you recognize the main patterns:

• DSN8BC3 demonstrates static SQL in COBOL.
• DSN8BD3 demonstrates static SQL in C.
• DSN8BP3 demonstrates static SQL in PL/I.
• DSNTEP2 is a PL/I dynamic SQL sample often run as a general batch SQL processor.
• DSNTEP4 is a related SQL processor with capabilities and operational details that differ by supported Db2 level; check the matching IBM topic before using it as a replacement for DSNTEP2.
• DSNTIAUL is a sample unload-style program frequently used to select rows into a sequential data set. For production data movement, compare it with supported Db2 utilities and site tooling rather than assuming the sample is the best operational choice.
• DSNTEJ2x jobs prepare and run language-oriented IVP phase 2 samples; the final letter often indicates a language or path.

Other names such as DSN8BD1, DSN8CC0, DSN8ED1, or DSN8ADUT can appear in sample families for specific application, attachment, or utility demonstrations. A similar prefix does not guarantee that two members are interchangeable. Start at the comment block in the source and at the job's prolog: IBM normally records purpose, dependencies, expected objects, and restrictions there.

Sample tables and sample SQL

Many classic examples use an employee-and-project data model. You may see tables named EMP, DEPT, PROJECT, PROJACT, EMPPROJACT, or release-qualified variants in an IBM sample schema. They let one set of programs demonstrate joins, cursors, aggregation, updates, and referential relationships. Do not assume those tables exist in an application database merely because the source compiles.

1
2
3
4
5
6
7
8
9
10
11
12
-- Read-only learning query; substitute your authorized sample schema
SELECT E.EMPNO,
       E.LASTNAME,
       D.DEPTNAME
FROM   DSN8C10.EMP  AS E
LEFT JOIN DSN8C10.DEPT AS D
       ON D.DEPTNO = E.WORKDEPT
WHERE  E.WORKDEPT = 'A00'
ORDER BY E.LASTNAME;
 
-- A safe first customization is to change only the filter
-- and keep the statement read-only until the result is understood.

The qualifier above is illustrative. Your release might create a different owner or sample schema, and your site may omit IVP objects entirely. Query the catalog or ask the DBA for the authorized schema. Begin with SELECT. INSERT, UPDATE, DELETE, MERGE, DDL, utilities, and commands can change shared state and require a separate review.

COBOL, PL/I, C, and Java concepts

COBOL static SQL

A COBOL sample usually declares host variables in WORKING-STORAGE, includes SQLCA or checks SQLSTATE, embeds EXEC SQL statements, and tests the result after every operation. Fixed-format COBOL columns matter: statements belong in the proper Area A or Area B positions. This reduced example shows the shape, not a complete program:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
       IDENTIFICATION DIVISION.
       PROGRAM-ID. SAMPLE1.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  WS-DEPTNO              PIC X(3) VALUE 'A00'.
       01  WS-DEPTNAME            PIC X(36).
           EXEC SQL
                INCLUDE SQLCA
           END-EXEC.
       PROCEDURE DIVISION.
           EXEC SQL
                SELECT DEPTNAME
                  INTO :WS-DEPTNAME
                  FROM DSN8C10.DEPT
                 WHERE DEPTNO = :WS-DEPTNO
           END-EXEC.
           IF SQLCODE = 0
               DISPLAY WS-DEPTNAME
           ELSE
               DISPLAY 'SQLCODE=' SQLCODE
           END-IF.
           GOBACK.

PL/I and DSNTEP2

PL/I static samples use declarations, host variables, EXEC SQL, and SQL diagnostics much like COBOL, with PL/I syntax and data types. DSNTEP2 is different because it accepts SQL text dynamically. It prepares and executes statements at run time, which makes it useful for batch scripts and also makes input review essential. The output includes statement results and diagnostic information, so retain it when learning from a failure.

C samples

C examples demonstrate SQL host variables, null indicators, SQLCA or SQLSTATE checks, string termination, and the boundary between C data types and SQL types. The SQL precompiler or compiler coprocessor must see host declarations and EXEC SQL text. Ordinary C preprocessor inclusion is not always a substitute for EXEC SQL INCLUDE. Watch compiler code-page options and the sample's include-library concatenation.

Java, JDBC, and SQLJ

A Java JDBC sample follows a different model. Java source loads or resolves a Db2 driver, obtains a connection, uses PreparedStatement for values, executes dynamic SQL, processes a ResultSet, and closes resources. Ordinary JDBC compilation does not create a DBRM. Connection properties, TLS, authentication, package availability for the driver, and DDF configuration still matter. SQLJ samples add translation, compile, customization, and bind steps, so do not apply the basic JDBC workflow to SQLJ.

Prepare a static sample: precompile, compile, link, bind, run

IBM supplies language-specific JCL procedures and DSNTEJ jobs that demonstrate program preparation. Modern compilers can use an integrated Db2 coprocessor, while older or established workflows run a separate precompiler. Conceptually, the sequence is:

1. Copy and inspect. Copy the source and JCL from SDSNSAMP to controlled libraries. Read the prolog, identify required objects, and compare every qualifier with your sandbox.
2. Process SQL. The precompiler, or the compiler's SQL coprocessor, extracts SQL and host-variable information into a DBRM. The separate precompiler also produces modified source that a normal host compiler can understand.
3. Compile. COBOL, PL/I, or C compilation converts the host-language source into object code. Resolve syntax, include, data type, and code-page diagnostics before continuing.
4. Link-edit. The binder combines object code with the correct Db2 language interface and runtime support to create a load module or program object.
5. Bind. BIND PACKAGE converts the DBRM into a package and chooses an access path against the target catalog. A plan includes the package directly or through a collection in its PKLIST.
6. Run and verify. Execute with the intended attachment, subsystem, plan, authorization ID, input, and load libraries. Check job-step return codes, SQLCODE or SQLSTATE, output rows, messages, and expected results.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SOURCE WITH EXEC SQL
        |
        +--> PRECOMPILE or SQL COPROCESSOR --> DBRM --> BIND PACKAGE
        |                                      |          |
        +--> modified/compiled source           |          +--> package in Db2 catalog
                    |                           |
                 COMPILE                       consistency token must match
                    |
                 OBJECT
                    |
                 LINK-EDIT + Db2 language interface
                    |
                 LOAD MODULE
                    |
                 RUN with a plan that can find the package

IBM supplies procedures such as DSNHICOB for Enterprise COBOL, DSNHC for C, and DSNHPLI for PL/I, with installation jobs that place customized versions into a procedure library. Treat the shipped procedure as a starting point. Compiler levels, runtime libraries, binder options, LE settings, DBRM storage, collection names, and attachment interfaces are all site decisions.

Installation verification with sample jobs

Installation verification procedures (IVPs) test an installed or migrated Db2 environment in phases. They can create sample objects, prepare language applications, run them, and compare behavior with expected output. This catches gaps that a simple -DISPLAY command cannot: a missing compiler runtime, wrong procedure library, bad DBRM path, bind authorization problem, missing sample data, or incorrect attachment configuration.

Run only the jobs documented for your exact Db2 release, function level, and scenario. IBM's migration verification instructions can specify selected steps from prior release jobs and warn against running others. “It is a sample” does not mean “it is safe in every subsystem.” Some IVP jobs create, populate, alter, or drop objects. Coordinate them with the installation plan and preserve their output as evidence.

• Confirm prerequisite IVP objects and data exist before running a language phase.
• Check that the compiler and Language Environment levels match the tailored JCL.
• Verify plan, collection, package owner, and qualifier values before BIND.
• Compare each step's return code and SQL diagnostics with documented expectations.
• Record intentional return code 4 or warning cases instead of hiding them.
• Remove or retain sample objects according to the site's post-install plan.

DSNTEP2 compared with SPUFI

Both tools let you run dynamic SQL without writing and binding a new static application. SPUFI is interactive: from ISPF you select an input data set or member, set defaults, execute statements, and browse the output. It is friendly for one person exploring a statement. DSNTEP2 is batch-oriented: JCL supplies SQL input and captures output in the job. That makes DSNTEP2 easier to repeat under change control and easier to integrate into a controlled validation job.

Neither tool makes dangerous SQL safe. Both use the executing authorization context and can change data or objects if permitted. DSNTEP2 is also a sample program, so your installation's prepared module, plan, input conventions, statement terminator, and output limits must be understood. For recurring production automation, review whether a supported utility, stored procedure, application, or administrative tool is more appropriate.

How to customize a sample safely

1. Copy source and JCL into a project or personal sandbox library; never edit the SMP/E-managed original.
2. Save an unchanged baseline and document the IBM release and member name from which you copied it.
3. Replace job card, data set qualifiers, subsystem ID, schema, collection, plan, and load-library names through symbolic parameters where practical.
4. Start with a read-only query that returns a small result. Add one change at a time and retain compiler, binder, and run listings.
5. Use your own package collection and object qualifier so a learning bind cannot replace an installation or shared application package.
6. Test expected failures too: no row, null value, duplicate key, authorization failure, and rollback. A useful sample teaches diagnostics, not only the happy path.

Security and safe learning

Use least privilege. A learner normally needs access to copied source, compiler libraries, a DBRM and load library, an approved package collection, and a private sample schema—not installation SYSADM. Separate the authorization used to bind from the one used to run when your site's model requires it. Avoid embedding passwords in JCL, Java source, properties members, or SYSOUT. For JDBC, use approved credential and TLS mechanisms; for local batch, use the site's attachment and identity standards.

Read every statement before submission. Watch especially for DROP, ALTER, DELETE without a predicate, utilities, Db2 commands, and JCL DISP values that replace or delete data sets. Set small row limits where the tool supports them, use a dedicated sandbox subsystem or schema, and know how to cancel a runaway job. SQL comments are not an approval mechanism. The person running the sample remains responsible for its effects.

Common problems and a troubleshooting order

• Member not found: verify the release-specific member name and actual SDSNSAMP prefix or suffix.
• Precompile or compile errors: inspect SQL delimiters, host variable declarations, COBOL columns, include libraries, and compiler options.
• Bind errors: check DBRM member, qualifier, owner, collection, authorization, object existence, and SQL compatibility.
• Package or consistency-token failure: make sure the load module and package came from the same preparation and that the selected plan finds the intended collection.
• Unexpected SQL result: confirm the sample schema and data version, isolation, input host values, null indicators, and expected-output member.
• JDBC connection failure: separate network and TLS problems from authentication, DDF location, driver, and SQL problems.

Explain It Like I'm Five

Imagine IBM gives you a model airplane kit. SDSNSAMP is the box containing example pieces and instructions. SDSNIVPD contains a picture showing what the finished model should look like. COBOL, PL/I, C, and Java are different kinds of tools for building. Precompile, compile, link, and bind are separate workshop stations that turn instructions into something Db2 can use. You make a copy of the kit before painting it, practice at your own table, and ask permission before using glue near someone else's model.

Exercises

1. Locate your installed SDSNSAMP without changing it. Record its prefix, suffix, Db2 release, and the member comment that identifies one static SQL sample.
2. Copy a read-only DSNTEP2 job and SQL input into sandbox libraries. Replace only the job card, subsystem, and input query, then have an authorized reviewer check it.
3. Draw two parallel preparation flows: static COBOL from EXEC SQL through RUN, and JDBC from Java compile through connection and dynamic prepare.
4. For the sample employee query, explain what fails if the schema is wrong, the table is absent, or SELECT authority is missing. Find the expected SQLSTATE class for each category.
5. Compare a DSNTEP2 batch run with the same SELECT in SPUFI. List differences in input, output, repeatability, and operational control.
6. Review one DSNTEJ2x IVP job and label the SQL processing, compile, link, bind, and run steps. Do not submit it.
7. Create a customization checklist that prevents a copied sample from using a shared package collection or production object qualifier.