DB2 Precompile & BIND Process

THE PRECOMPILER
The DB2 Precompiler does not need DB2 to run. It carries out three primary tasks as it reads the program serially, top-to-bottom, looking for DB2 delimiters.

First, if the delimiters surrounded an INCLUDE statement, the Precompiler goes to the INCLUDE library named in the job control language data definition statement and pulls the included MEMBERNAME into the program. This function is the same as a COBOL COPY MEMBERNAME, but the timing is different. COBOL COPYBOOKs get copied in at COMPILE time; DB2 INCLUDEs get copied in at precompile time. The only difference between an SQL INCLUDE and a COBOL COPY is timing. The most common item INCLUDEd in a program was (and is) a DCLGEN. DCLGENs are structures that describe a table. One DCLGEN is usually included for each table that the program will access at run time. Each DCLGEN is a two-part structure consisting of a DECLARE TABLE statement, which describes the table in DB2 SQL language, and a COBOL structure that describes the table using an 01-Level COBOL working storage structure (much like a typical copybook for a VSAM file).

Second, if the delimiters surround an SQL statement, the precompiler does a very basic syntax check to make sure that the column and table names are valid (that they're spelled correctly and that the columns and the table exist). Many DBAs and programmers think that this validation is done by reading the DB2 CATALOG, but they're wrong. Remember, the precompiler doesn't need DB2 or its CATALOG. DB2 might not even be installed on the machine. The DB2 Precompiler uses the top part of the DCLGEN to validate the SQL syntax.

The third, and most important, task performed by the DB2 Precompiler is to split the program into two parts: a COBOL and a DB2 part. All of the SQL that the programmer carefully embedded is stripped out of the program and put into its own partitioned data set (PDS) member, called a DBRM. A single program containing two languages, COBOL and SQL, goes into the DB2 Precompiler and two pieces come out. Twins, but fraternal twins — much like Arnold Schwarzenegger and Danny DeVito. Arnold looks just like his COBOL mother, and Danny looks just like his DB2 father. COBOL Arnold, with all of the SQL commented out, goes down one path in life. SQL Danny, containing only SQL, goes down a different path in life.

The twins, separated at birth, have a tendency to lose each other. To help the twins find each other later in life (in other words, at run time), the precompiler engraves each with identical tattoos. The tattoo is carried forward with COBOL Arnold, through compile and link edit, into the LOAD module in the LOAD library. The tattoo is part of the run-time executable code of the LOAD module. The same tattoo is carried forward with SQL Danny through BIND. BIND is to SQL what COMPILE is to COBOL. The purpose of COBOL COMPILE is to come up with run-time code for the COBOL. The purpose of BIND is to come up with run-time executable code for the SQL. Both sets of code bear identical tattoos (timestamps or consistency tokens).

So, the COBOL twin becomes a transportable load module in the COBOL LOADLIB and the SQL becomes a transportable DBRM in the DBRMLIB. Just as the COBOL twin had to be compiled, the DBRM twin has to go through BIND to create the run-time executable code for the DB2 portion of the COBOL program and put that executable code into the "right" DB2 subsystem.

ALL ABOUT BIND

BIND connects to the DB2 in which the program's LOAD module will run, reads the DBRM serially, and then performs three tasks.

The first of the BIND tasks is an authorization check. DB2 must make sure that the programmer has the BIND authority and the SQL authority to perform the requested SQL task (for example, updating the payroll master). When using standard authorization procedures, DB2 won't let you BIND a DBRM if you don't have the authority to execute the SQL that's in the DBRM. This is why you may have the authorization to BIND in development (accessing development tables) but don't have authorization to BIND in production, where the SQL accesses production tables. The second BIND task is a bit redundant. BIND, like precompile, must also check the syntax of the SQL, but the BIND check is more sophisticated. Instead of using the top, DECLARE TABLE portion of the DCLGEN, BIND uses the DB2 CATALOG table information to make sure that the column names are valid, that comparisons are numeric-to-numeric, and so on. This second syntax check occurs because you can't trust the one done by the precompiler because the precompiler check used the DCLGEN. You could have a DCLGEN and not have the DB2 table.

The third, and most important, BIND task is to come up with run-time instructions for the SQL in the DBRM. Each SQL statement is parsed and all of the possible (realistic) methods for retrieving the desired columns and rows from the table are weighed, measured, and evaluated based on possible estimated I/O, CPU, and SORT overhead. A ton of information is used as input to the BIND process, not just CATALOG information put there by running the RUNSTATS utility. BIND input includes, for example:

Indexes (what columns are in the indexes?)
Columns (how long is this column and how much room will it occupy in a SORT record?)
System resources (how big are the system resources, buffer pool, and RIDPOOL?)
Processors (how big are they and how many engines do they have?)
DB2 (what release is running?)
Parameters (what are the values of the BIND parameters?)
After all that input (and more) is weighed and compared, the cheapest, most costeffective access path is chosen, and the runtime instructions for that one path are created. (Interestingly, DB2 BIND sometimes generates instructions for more than one path.) This process is called optimization, and it's repeated for each SQL statement in the DBRM until all access paths are decided and the run-time instructions are created for each. As the optimizer decides on each path, writes are done to DB2.
BIND checks to see if you bound with the parameter EXPLAIN(YES); if so, it writes documentary evidence about the chosen path to the PLAN_TABLE and to the DSN_STATEMNT_TABLE for your edification.

BIND also writes a lot of information to multiple CATALOG tables, documenting the fact that the BIND did occur. In fact, the tattooed DBRM, which is not used at run time, is moved into the CATALOG. Objects chosen by the optimizer are documented in the CATALOG in cross-reference tables. And BIND parameters are recorded in the CATALOG also.

ORACLE Database

Oracle Database Fundamentals In this article we will mainly focus on basic database development using Oracle. We will learn how to create new tables, alter them, insert data into the database, update data, retrieve data, delete data and drop tables. We have lots to do, so let's get started.

We will start with the widely used Oracle example of an employee information database. We can store all the information in a table like the one below where each row will represent an employee, and each column will represent employee attributes.

Oracle Database Fundamentals - Creating Database Tables:

You need to log in to Oracle before executing any SQL statement. SQL is case-insensitive, even with the Oracle username and password!
The main data types used in Oracle are: varchar2(x) which can hold a variable length of string up to x characters long; number, an integer or real value up to 40 decimal digits; and date, which holds a date. For our example department table, the Dept field can be varchar2, while the EmpID and DeptID fields can be numbers. To create the table, the SQL statement is like this:

create table tablename (columnname type, columnname type ...);
create table department ( Dept varchar2(20), EmpID number, DeptID number);

If we execute the above SQL statement a table will be created with the name "department." To view the information of a table, the describe or desc statement can be used, like so:

describe department;
or
desc department;

So the SQL for the employee table would be:

create table employee ( "First Name" varchar2(20), "Last Name" varchar2(20), Address varchar2(60), Phone varchar2(15), Salary number, EmpID number, DeptID number);

Do you find any differences between the column names? The "First Name" and "Last Name" column names have a space in between the words. To use spaces in column names, you need to enclose them with quotation marks ("").
Here are some things to remember:
SQL select statements return column names in upper case.
If you want to mix upper cases and lower cases in the column name, then you need to enclose them with quotation marks ("").
Single quote marks are used to express a string in SQL. 'String' is a string but "Not a String" is not.
Oh no! I forgot to add the "Joining Date" column in the employee table. Don't worry. Oracle tables can be altered to add/delete columns or change column types. To add the "Joining Date" column we need to execute following SQL.

alter table employee add ("Joining Date" date);

We used varchar2 for the Phone column. If we want to change this column type to number then we need to modify the table using the following SQL:

alter table employee modify (Phone number);

To drop a column from a table we need to use the following statement.

alter table tablename drop column columnname;

If it's not specified, then columns are nullable by default, i.e. they can hold null values. To specify a column as not nullable add the words "not null" after the column type in create table or alter table statements, like so:

alter table employee add ("Joining Date" date not null);

Once we tried to create a table of about 1200 columns, but failed, because Oracle only supports 1000 columns in a single table!
Now we have some idea of how to create and alter tables. In the next section we will show you how to insert some data in our tables.
Oracle Database Fundamentals - Inserting Data (Page 3 of 5 )
The syntax for an SQL insert statement is as follows:

insert into tablename values (somevalue, somevalue, ...);
Or
insert into tablename (columnname, columnname, ...) values (somevalue, somevalue, ...);

The difference between above two insert statements is the columnname part. In the first statement values will be inserted in the order of columns as specified during creation. Let's look at an example. If we want to insert the first row of our department table as specified earlier then the insert statement would be:

insert into department values ('Sales',1,1);

Our department database table would look like this after this insertion.

insert into department values (1,'Sales',1);

Now if we use above statement, it will cause an error. Oracle is expecting the first element to be Dept, a varchar2, but it gets a number, 1. We can use the statement below to make Oracle think like us.

insert into department (DeptID, Dept, EmpID) values (1,'Sales',1);

It is better to mention the column names also, since sometimes we don't know the order of the columns.

Oracle Database Fundamentals - Selecting Data :

The syntax for selecting data from an Oracle table is

select columnname, columnname... from tablename;

If we want to select data from our department table we will use

select dept, empid, deptid from department;

This will select all data from a department table. There is a shortcut version of selecting all data from a table, and that is *.

select * from department;

The above two SQL statements are the same. To get the count of records (rows) in a table use the count() function.

select count(*) from department;

To get the maximum value of a column we can use the max() function. Use the min() function to get the minimum.

select max(salary) from employee;
select min(salary) from employee;

If we want to get the information about a particular employee whose first empid is 5 from our employee table, the SQL will be

select * from employee where empid=5;

In SQL we can add a where clause such as where empid=5 to get data that match the condition. There can be more than one where condition combined by 'and' and 'or'.
We want to get the full name of the employees whose deptid is 1 and who earn more than 5000.

select "First Name"' ''Last Name' from employee where deptid=1 and salary>5000;

In Oracle, is used for string concatenation as well as concat() function.
If you see the resulting column name then you can see it is the same as the expression we used in our SQL. We can give an alias for this expression using AS. For example:

select "First Name"' ''Last Name' as 'Full Name' from employee where deptid=1 and salary>5000;
or (without AS, it is the same)
select "First Name"' ''Last Name' 'Full Name' from employee where deptid=1 and salary>5000;

We can also give an alias to a table name in this way. We can sort the output of a SQL select using order by.

select "First Name", 'Last Name', salary from employee where salary>5000 order by salary;

The above SQL will select the first name, last name and salary from the employee table whose salary is more than 5000, and sort the result using salary in ascending order. The default order is ascending (asc).

select "First Name", 'Last Name', salary from employee where salary>5000 order by salary asc;
To put the list in descending order use desc.
select "First Name", 'Last Name', salary from employee where salary>5000 order by salary desc;

Oracle Database Fundamentals - Updating Data :

We can update a table using following statements.

update tablename set columnname=somevalue;
Or
update tablename set columnname=somevalue where conditions;

To update all the records use the first statement. To update particular records, add where conditions at the end of the update statements. To give an increment of 1000 to all our employees use:

update employee set salary=salary+1000;
update employee set salary=60000 where empid=1;

The above statement will change the salary of employee with empid 1 to 60000.

Deleting Data:

Data from tables can be deleted using the following statements.

delete from tablename;
delete from tablename where conditions;
delete tablename;
delete tablename where conditions;

To delete the record of an employee with the first name Tim use:

delete from employee where "First Name"='Tim';

To delete all records from the employee table use:

delete employee;

Dropping a Table:

To drop a table use:

drop table tablename;

To drop an employee table use:

drop table employee;

To commit changes in Oracle Database use commit and to roll back your changes use the rollback command.

DB2 Database

DB2 Middleware and Connectivity:


DB2 is a very open database and provides a variety of options for connecting to DB2 and non-DB2 databases.
DB2 client code is required on workstations for remote users to access a DB2 database or on servers for remote programs or applications to access a DB2 database.
DB2 Connect provides support for applications executing on UNIX and Intel platforms to transparently access DB2 databases on the OS/400, VSE/VM, z/OS, and Linux on zSeries environments. Note that DB2 Connect is not required to access DB2 databases on UNIX or Intel platforms.
DB2 Information Integrator integrates data and content sources across the enterprise, based on the SQL programming model product, which allows DB2 clients to access, join, and update tables from heterogeneous databases, such as Sybase, Informix, and Microsoft SQL Server.
DB2 Information Integrator for Content (EIP) integrates data and content sources across and beyond the enterprise, based on the content programming model.
DB2 Universal Database Clients:


The DB2 product enables clients that are used by applications or workstations to communicate with DB2 servers. There are three types of DB2 clients.
DB2 runtime client enables workstations running a variety of platforms to access DB2 databases. It includes basic connectivity onlynothing more and nothing less. If you need to establish connectivity to a remote DB2 server or DB2 Connect Gateway, which helps you access DB2 on a mainframe or host system, such as DB2 UDB for z/OS, the runtime client provides this.
DB2 administration client provides the ability for workstations from a variety of platforms to access and administer DB2 databases through the Command Center, Control Center, or Configuration Assistant. Additional tools are included for monitoring and general administration: the Replication Center. A DB2 administration client has all the features of the DB2 runtime client and also includes all the DB2 administration tools, documentation, and support for thin clients.
DB2 application development client provides the tools and environment you need to develop applications that access DB2 servers. You can build and run DB2 applications with a DB2 application development client. Of course, because this is a DB2 client, it also gives users the power of connectivity and includes the functions of the preceding two clients.
The DB2 clients are supported on a variety of platforms, including Windows, AIX, HP-UX, Linux, and Solaris.
DB2 Connect :


DB2 Connect provides connectivity to IBM mainframe databases for e-business and other applications running under various UNIX and non-UNIX operating systems. DB2 Connect has several connection solutions. DB2 Connect Personal Edition provides direct connectivity to zSeries or iSeries databases; DB2 Connect Enterprise Edition provides indirect connectivity that allows clients to access host databases through the DB2 Connect server. DB2 Connect Unlimited Edition is an additional solution that makes product selection and licensing easier.
DB2 Connect forwards SQL statements submitted by application programs to DB2 for z/OS, DB2 for VSE and VM, or DB2 for iSeries database servers. DB2 Connect can forward almost any valid SQL statement. DB2 Connect fully supports the common IBM SQL, as well as the DB2 Universal Database for z/OS, DB2 for VSE and VM, and DB2 for iSeries implementations of SQL.
DB2 Connect implements the Distributed Relational Database Architecture (DRDA) to reduce the cost and complexity of accessing data stored in DB2 for iSeries, DB2 UDB for z/OS, DB2 for VSE and VM, and other DRDA-compliant database servers. By fully exploiting DRDA, DB2 Connect offers a well-performing, low-cost solution with the system-management characteristics that customers require. In the DB2 Connect environment, the DB2 Connect workstation can function only as an application requester on behalf of application programs.
DB2 Connect allows clients to access data stored on data servers that implement the DRDA. The target database server for a DB2 Connect installation is known as a DRDA application server. The most commonly accessed DRDA application server is DB2 for z/OS. The database application must request the data from a DRDA application server through a DRDA application requester. DB2 Connect provides the DRDA application requestor functionality. The DRDA application server accessed using DB2 Connect could be any DB2 server on z/OS, VM and VSE, or iSeries.