Outline

• More on ODBC and JDBC
• Quiz
• Authentication
• Authorization
• Trusted Context

Introduction

• Last week, we learned about how static and dynamic SQL statement are prepared and executed by DB2.
• We looked at how to write both static and dynamic SQL in COBOL and C.
• We then started talking about connecting to DB2 using an API: Either ODBC or JDBC.
• We looked at the processing model of ODBC, the initialization, transaction processing, and termination phases.
• Today, we are going to begin by looking at the transaction processing phase of ODBC in more detail and then look at JDBC.

ODBC Transaction Processing

• Transaction processing is the core task of ODBC.
• During this task, query and modification of data is performed via DB2 ODBC API.
• Notice in the above diagram transaction processing is split into five main steps:
  1. Allocating statement handles. (You can also set the isolation level here)
  2. Preparing and executing SQL statements
  3. Processing results
  4. Committing or rolling back
  5. Optionally, freeing statement handles

DB2 ODBC Runtime Environment

• Because DB2 ODBC supports is provided as a DLL, DB2 ODBC applications do not need to link-edit any DB2 ODBC driver code with the application load module.
• Instead, the linkage to the DB2 ODBC APIs is resolved dynamically at run time by the IBM Language Environment Run-time support.
• DB2 ODBC driver can use either the Call Attachment Facility (CAF) or the Resource Recovery Services Attachment Facility (RRSAF) to connect to the DB2 for z/OS address space.
• When the DB2 ODBC application invokes SQLAllocHandle () (with HandleType set to SQL_HANDLE_ENV), the DB2 ODBC driver DLL is loaded.
• z/OS DB2 ODBC supports access to the local DB2 for z/OS subsystems, and any remote data source that is DB2 using three-part name, or DRDA-1 or DRDA-2 servers.

Quiz

Which of the following statements is true?

1. If you are using JDBC there is always a prepare phase before a query can be executed
2. In Embedded C, host variables are declared in a EXEC SQL BEGIN DECLARE SECTION; -- EXEC SQL END DECLARE SECTION; block.
3. You still need to declare cursors if you are using ODBC.

Java Database Connectivity (JDBC)

• JDBC is a Java API for dynamic SQL.
• It is platform independent in the sense that the JDBC type 4 driver is written in pure Java and the type 2 driver is mainly written in Java with only minimal C calls.
• Preparing a Java program that contains JDBC API calls is the same as preparing any other Java program. You compile the program using the javac command
• There is no pre-compile or bind step needed.
• A DBMS or DB2 Connect product is not required on the remote client system running the JDBC applications.

Four Types of JDBC Drivers

• Type 1 -- implemented as a mapping to another API, such as ODBC
• Type 2 -- (supported by DB2) written partly in the Java programming language and partly in native code.
• Type 3 -- uses a pure Java client and communicates to a server using a database-independent protocol to pass client requests to the data source
• Type 4 -- (supported by DB2) written in pure Java and implements the network protocol for a specific data source.

z/OS DB2 JDBC Driver

• The DB2 Universal JDBC Driver is a single driver that includes JDBC type 2 and JDBC type 4 behaviors, as well as SQLJ support.
• When an application loads the DB2 Universal JDBC Driver, a single driver instance is loaded for type 2 and type 4 implementations.
• The application can make type 2 and type 4 connections using this single driver instance.
• Type 2 and type 4 connections can be made concurrently.
• The type 2 driver is not implemented in pure Java. The connection uses RRSAF, a DB2 attach facility, and the code of RDI, SQLDA part of the code is implemented in C. Therefore type 2 driver can only be used for local DB2 access. Type 2 connectivity requires the availability of C or C++ Dynamic Load Library (DLLs). The type 2 driver for local connections might be slightly faster than the type 4 driver.
• The type 4 driver is implemented in pure Java. Type 4 driver goes through DDF to connect to remote DBMS supporting DRDA.
• Pre-version 8 variants of DB2 used a type 2 driver that require a DB2 client installation. The DB2 Legacy JDBC Driver is contained in the file db2java.zip. This legacy driver is dropped in DB2 V9.

JDBC Processing Flow

• Writing a JDBC application has much in common with writing an SQL application in any other language.
• In general you need to:
  • Import the Java packages that contain JDBC methods.
  • Declare the variables for sending data to or retrieving data from DB2 tables.
  • Connect to a data source.
  • Execute SQL statements.
  • Handle SQL errors and warnings.
  • Disconnect from the data source.

Example JDBC Application

import java.sql.*;
...
//inside some method
String url = "jdbc:db2:", empNO;

Connection con; Statement stmt; ResultSet rs;

Class.forName("com.ibm.db2.jcc.DB2Driver");

con = DriverManager.getConnection (url);

stmt = con.createStatement();

rs = stmt.executeQuery("SELECT EMPNO FROM EMPLOYEE");

while (rs.next()) {
  empNo = rs.getString(1);	
  System.out.println("Empno=" + empNO); 
}

rs.close(); 
stmt.close(); 
con.close();
...

Dynamic SQL Interactive Tools

• SQL was originally designed as a query language
• The language was designed for an interactive dialog between a person and a database management system.
• This type of ad-hoc, unplanned queries is also called the decision support. DB2 provides tools for end users to run SQL easily and efficiently for decision support queries.
• Command Line Processor -- The command line processor is a text-oriented interface for processing SQL statements, DB2 commands, and operating system commands. It can accept input either from keyboard or from a file. And it can direct output either to display or to a file.
• SPUFI (SQL processor using file input) facility -- You can enter and execute dynamic SQL statements through a TSO workstation by using SPUFI. Ad-hoc, dynamic SQLs are keyed into a file to be processed quickly.
• Control center -- A GUI tool for administering DB2 instances. It can be launched from the DB2 Developer Workbench, or DB2 command center
• DB2 Query Management Facility (QMF) for Workstation -- You can use DB2 QMF for WebSphere to enter and run SQL statements from your Web browser or use DB2 QMF for TSO/CICS to enter and run SQL statements from TSO or CICS.

Stored Procedure

• A compiled DB2 program
• Stored as a DB2 object
• Executes one or more SQL statements
• Has some manipulative or logical processing
• Client uses SQL CALL statement to invoke stored procedure

DB2 Security

• We are next going to talk about security in DB2 in detail...

Three Main Aspects of Security Connected to DB2

• Data Set Security -- tables, tablespaces both of the user and the catalog, the directory, etc are all stored on disk. RACF is used to make sure can't be accessed by someone unauthroized from under z/OS but not under DB2.
• Authentication -- this is the process by which access to DB2 data is achieved either from iwthin an interactive session or via an application. RACF can be used to authenticate subsystems (CICS, IMS, etc) and individual users.
• Authorization -- RACF, or "DB2 Access Control", can control user's access to DB2 resources.

Authentication and Authorization

• Authentication -- this consists of a primary ID, which identifies who you are, as well as secondary IDs which identify groups you belong to.
• Authorizations -- this says which kinds of DB2 objects you can access.
• Validation is usually performed by RACF.

Authorization ID

• Every process that connects to or signs on to DB2 is represented by a set of one or more DB2 identifiers called authorization ids.
• Authorization ids can be assigned to a process by default procedures or user-written exit routines.
• Primary Authorization ID -- Every process is assigned one mandatory ID called the primary authorization ID. The primary id is usually set to the TSO logon user id or the CICS/IMS sign-on user id. It holds personal privileges and is used by accounting and performance traces.
• Secondary Authorization IDs -- You can have up to 1,012 additional, optional authids called secondary authorization IDs.

Establishing Authorization IDs

• Primary and Secondary IDs are established via RACF.
• The process of getting an authorization occurs during the connection and sign on phases when a so-called exit routine is invoked. (Think of this as like a callback function rather than an exit() call).
• DB2 has a default connection exit routine (DSN3@ATH) and a default sign-on exit routine (DSN3@SGN)
• Both exit points perform crucial steps in the assignment of values to primary IDs, secondary IDs, and SQL IDs.
• You can replace these with your own exit routines.
• Requests from, for example TSO, go through a connection exit routine. This routine establishes the primary authid, the initial value of the CURRENT SQLID, and can provide up to 245 secondary authids.

CICS Subsystem -- Connection Exit

• In the above example, the subsystem ID is called CICSPRD. During the exit DSN3@ATH, RACF can be used to validate that CICSPRD is allowed to access the DB2 subsystem. At this point, RACF also assigns GRP1, GRP2, GRP3, and GRP4 secondary IDs to this CICS subsystem. CICSPRD is further defined as the SQL ID.

CICS User Sign-on Exit

• In the above, the DSN3@SGN sign-on exit validates an individual CICS user's authentication credentials. After RACF authenticates the CICS USER01, RACF also assigns four secondary ID : TEST, PROD, DB2AP, DB2PY to this USER01. The Primary ID and SLQ ID, in this example, are both assigned to the same as the CICS ID, i.e., USER01.

Authorization Related Special Registers

• We next look at authorization in more detail...
• To begin we look at some special register associated with the authorization process.
• Special registers contain values, maintained by the system, that describe and control the environment in which an SQL statement is executed.
• Three important ones are: USER, CURRENT SQLID, and CURRENT SCHEMA.
• We'll discuss these more next day.