December 2011

Powerful three words

What are the most powerful three letter words?

Other than I Love you




DBIx Query - Insert, Update, Delete, Joins

To create new record

my $data ;
my $product_rs = $schema->resultset('Product');
$data->{product_name} = "Market";
$data->{product_status} = "A";


my $product_rs = $schema->resultset('Product');
$product_rs->create({product_name=>'Market',    product_status=>'A'});

To update the existing record

my $product_id=123;
my $data ;
my $product_rs = $schema->resultset('Product')->find({product_id=>$product_id});
$data->{product_status} = "D";

my $product_rs = $schema->resultset('Product')->find({product_id=>$product_id});

To delete a record

my $product_rs = $schema->resultset('Product')->find({product_id=>$product_id})->delete;

Simple Join

my $orderList = $schema->resultset('Order')->search({'product_id' => $product_id
   , 'product_name' => 'Market'},
    {join =>product,

Join Multiple tables

my $orderList = $schema->resultset('Order')->search({
  'product_id' => $product_id,
   'product_name' => 'Market',
   'customer_name' =>'AA'
   {join => [product, customer],

Multilevel Join

my $custOrderList = schema->resultset('Order')->search({'customer_name' =>'AA'},
{join =>{customer=>cust_address}

Multilevel and multiple tables

my $custOrderList = schema->resultset('Order')->search(
  {'customer_name' =>'AA'},
   {join =>[product, {customer=>cust_address}]

What is deferred embedded SQL?

A deferred embedded SQL statement is neither fully static nor fully dynamic. Like a static statement, it is embedded within an application, but like a dynamic statement, it is prepared during the execution of the application. Although prepared at run time, a deferred embedded SQL statement is processed with bind-time rules such that the authorization ID and qualifier determined at bind time for the plan or package owner are used. Deferred embedded SQL statements are used for DB2 private protocol access to remote data.

What is DB2 ODBC?

DB2 Open Database Connectivity (DB2 ODBC) is an alternative to using embedded static or dynamic SQL.

DB2 ODBC is an application-programming interface in which functions are provided to application programs to process SQL statements. The function calls are available only for C and C++ application programs. Through the interface, the application invokes a C function at execution time to connect to the data source, to issue SQL statements, and to get returned data and status information. Unlike using embedded SQL, no pre-compilation is required. Applications developed using this interface might be executed on a variety of data sources without being compiled against each of the databases. Note that only C and C++ applications can use this interface.

What are JDBC and SQLJ?

JavaSoft(TM) Java Database Connectivity (JDBC) and SQLJ are two methods for accessing DB2 data from the Java programming language. In general, Java applications use JDBC for dynamic SQL and SQLJ for static SQL.

What is a schema?

A schema is a collection of named objects. The objects that a schema can contain include distinct types, functions, stored procedures, and triggers. An object is assigned to a schema when it is created.

The schema name of the object determines the schema to which the object belongs. When a distinct type, function, or trigger is created, it is given a qualified, two-part name. The first part is the schema name (or the qualifier), which is either implicitly or explicitly specified. The second part is the name of the object. When a stored procedure is created, it is given a three-part name. The first part is a location name, which is implicitly or explicitly specified, the second part is the schema name, which is implicitly or explicitly specified, and the third part is the name of the object.

What is an index?

An index is an ordered set of pointers to rows of a base table or an auxiliary table. Each index is based on the values of data in one or more columns. An index is an object that is separate from the data in the table. When you define an index using the CREATE INDEX statement, DB2 builds this structure and maintains it automatically.

Which statement would you use to find out whether DB2 has used index to access the data?

EXPLAIN statement or the EXPLAIN option of bind.

What are the difference types of indexes and how will you create them?

There are two types of indexes: TYPE 1 & TYPE 2. Use the statement CREATE INDEX to create either type. TYPE 2 index comes with DB2V4 onwards. With TYPE 2 index data can be retrieved faster as only the data pages are locked and not the index pages. Hence TYPE 2 index is recommended.

What is the default value of index when you use CREATE INDEX?

The default value depends on the value of LOCKSIZE for the associated table space.
1. If LOCKSIZE is ROW then the default index type is type 2. You can't use row locking with a type 1 index.

2. If LOCKSIZE is not ROW, then the default for CREATE INDEX is the type specified in field DEFAULT INDEX TYPE of installation panel DSNTIPE.

What is a Leaf Page?

Index page that points directly to the data in the table is called a leaf page. The leaf page contains the key of the record and a pointer to the record.

What is a root page?

The opposite of a leaf page; it is the highest-level index page. An index can contain only one root page; all other index pages are associated to the root. Each record of a root page contains a pointer to another index page and the highest of key of that page.

What is B-tree Index.

The simplest DB2 index is the B-tree and the B-tree's top page is called the root page. The root page entries represent the upper range limits of the index and are referenced first in a search.

How many indexes a table can have?

A table can have more than one index

What is the function of UNIQUE WHERE NOT NULL clause?

The clause is uses with CREATE INDEX command. This ensures the uniqueness of the key column on which the index is defined, but it allows null values in the column. Or, DB2 will not allow duplication of non-null values in the key column.

What advantage does clustering index provide?

When a table has a clustering index, an INSERT statement inserts records as nearly as possible in the order of their index values. These clustered inserts can provide a significant performance advantage in some operations, particularly those that involve many records, such as comparisons other than equal, grouping and ordering. Although a table can have several indexes, only one can be a Clustering index.

How many clustering indexes are allowed for a table?

Only one.

How will you specify a clustering index?

To specify a clustering index, use the CLUSTER clause in the CREATE INDEX statement.

You have loaded data using a non-clustering index. Now, You are defining a clustering index for the table. What is its effect on the new inserts?

It does not have any effect on the new insert. The data will still be organized using the non-clustering index. However, when the table space is reorganized by REORG utility, DB2 clusters data in accordance with the clustering index.

What is a partitioning index?

When you store a table in a partitioned table space, you tell DB2 how to divide the data among partitions by using the PART clause of a CREATE INDEX statement. The index that divides the data is called a partitioning index. It is also a clustering index, because the data is clustered by the index key values. Thus, your PART clause must be preceded by the CLUSTER clause.

How does Db2 use multiple table indexes?

DB2 use the multiple indexes to satisfy multiple predicates in a SELECT statement that are joined by an AND or OR.

What are some characteristics of columns that benefit from indexes?

Primary key and foreign key columns; columns that have unique values; columns that have aggregates computed frequently and columns used to test the existence of a value.

What is a composite index and how does it differ from a multiple index?

A multiple index is not one index but two indexes for two different columns of a table. A composite index is one index made up of combined values from two columns in a table. If two columns in a table will often be accessed together a composite index will be efficient.

What is meant by index cardinality?

The number of distinct values for a column is called index cardinality. DB2's RUNSTATS utility analyzes column value redundancy to determine whether to use a tablespace or index scan to search for data.

55.What is a clustered index? How many clustering indexes can be defined for a table?

For a clustered index DB2 maintains rows in the same sequence as the columns in the index for as long as there is free space. DB2 can then process that table in that order efficiently. It is a mandatory index defined on a partitioned table space. Only one clustered index can be defined on a table.

DB2 unique key

A unique key is a key that is constrained so that no two of its values are equal (no duplicates).

Composite Key

A key composed of more than one column is called a composite key.

How can the uniqueness of a key be enforced? When does DB2 enforce this constraint?

The mechanism used to enforce the uniqueness of a key is a unique index. Thus, every unique key is a key of a unique index. Such an index is also said to have the UNIQUE attribute. A unique key can be defined using the UNIQUE clause of the CREATE TABLE statement. A table can have an arbitrary number of unique keys.

When does DB2 enforce the various constraints.

DB2 enforces the constraint during the execution of the LOAD utility and the SQL INSERT and UPDATE statements.

How many primary keys are possible for a table?


Does every table need a primary key?

No, primary keys are optional.

What is a view? Why use it?

A view is a named specification of a result table. The specification is an SQL SELECT statement that is effectively executed whenever the view is referenced in an SQL statement. In other words, a view is a virtual table made up of data from base tables and other views, but not stored separately.

What are the possible reasons to use views instead of tables?

Some of your users might find that no single table contains all the data they need; rather, the data might be scattered among several tables. A view is an alternative way of describing data that exists in one or more tables. To limit access to certain kinds of data. One table might contain more data than the users want to see, or more than they should be authorized to see. For those situations, you can create views. To allow you to alter tables without affecting application programs.

What is a DB2 package?

A package contains control structures used to execute SQL statements. Packages are produced during program preparation. During the program preparation, the pre-compiler generates DBRM (database request module) that contains SQL statements extracted from the source program. From DBRM, the bind operation generates operational form of SQL or internal control structures to access the data. All control structures in a package are derived from the SQL statements embedded in a single source program.

What is a DB2 plan?

An application plan relates an application process to a local instance of DB2, specifies processing options, and contains one or both of the following elements: A list of package names. The bound form of SQL statements taken from one or more DBRMs. Every DB2 application requires an application plan. Plans and packages are created using the DB2 subcommands BIND PLAN and BIND PACKAGE

DB2: What is a distributed operation?

A DB2 application program can use SQL to access data at other database management systems (DBMSs) other than the DB2 at which the application's plan is bound. This DB2 is known as the local DB2. The local DB2 and the other DBMSs are called application servers. Any application server other than the local DB2 is considered a remote server, and access to its data is a distributed operation.

What is meant by local DB2?

The DB2 application server in which the application's plan is bound is known as local DB2.

How many connections are possible for an application program at a time?

At a time, only one connection is possible for an application program.

The local DB2 is connected to a remote a DBMS. The application program issues a SELECT statement. Then it tries to connect to another DBMS. What can you expect?

DB2 will not allow it. An application program can have only one connection at a time and cannot connect to new application server until it executes a commit or rollback operation.

What are the different types of methods by which a local DB2 connect to a remote DBMS?

DB2 provides two methods of accessing data at remote application server

1. DRDA: This protocol allows the application program to connect to DB2 as well as other types of DBMS.

2. DB2 private protocol access: It allows DB2 connect to another DB2 running on a different application server.

What does Remote unit of work mean?

A unit of work is a transaction made up of a related set of SQL statements. Under remote UOW, DB2 on one computer can send multiple, related set of SQL statements to another DBMS on a remote computer. The remote site performs the processing for the statements, but the sending site controls whether to commit or rollback the UOW.

What does an SQL connection mean?

An SQL connection is an association between an application process and a local or remote application server. SQL connections can be managed by the application or by using bind options at any time.

DB2: Normalization and de-normalization

Normalization rules help to avoid redundancies and inconsistencies in the data. It protects against update and delete anomalies and provide smaller tables and fewer total bytes.

The rules of normalization are:

1st Normal Form:- No data item (repeating groups) should not be repeated within a given record.

2nd Normal Form: Each column that is not in the key provides a fact that depends on the entire key.

3rd Normal Form: Each non-key column provides a fact that it’s independent of other non-key columns and depends only on key columns. 4th Normal Form: No row contains two or more independent multi-valued facts about an entity. 5th Normal Form: Sub-relations that cannot be reconstructed.

De-normalization technique

The rules of normalization do not consider performance. De-normalization concentrates on performance, but not on redundancy. What you have to consider is the trade-off--whether duplication, in several tables, of often-requested columns is less expensive than the time it takes to perform joins. This duplication of columns in multiple tables is de-normalization, and increases redundancy.

Is there any advantage to de-normalizing DB2 tables?

Denormalizing DB2 tables reduces the need for processing intensive relational joins and reduces the number of foreign keys.

What is referential integrity?

Referential integrity refers to the consistency that must be maintained between primary and foreign keys.

It is the state in which all values of all foreign keys at a given DB2 are valid. Rules of referential integrity:

Every foreign key value must have a matching primary key value.

An insert into a primary key table can not violate referential integrity and no checking is required.

A deletion of foreign key value can not violate referential integrity and no checking is required.

Changes in primary key values are allowed only for those values that don't have a matching foreign key.

Insert and update rules: The insertion of any given foreign key value (non-null value) or an update to that value is allowed only if the matching value exists in the primary key.

Delete rules for primary key value:

If RESTRICT is specified on foreign key definition, deletion of primary key value is not allowed.

If CASCADE is specified, both primary key and the foreign key values will be deleted.

IF SET NULL is specified, the foreign key value will be set to null, provided nulls are allowed in the foreign key column.

When can an insert of a new primary key value threaten referential integrity?

Never. New primary key values are not a problem. However, the values of foreign key inserts must have corresponding primary key values in their related tables. And updates of primary key values may require changes in foreign key values to maintain referential integrity.

What is the self-referencing constraint?

A single table can include both a primary key and a related foreign key. The limitations to changes in the primary key that the foreign key defines are called self-referencing constraints. The foreign key in a self-referencing table must specify the DELETE CASCADE rule.

What happens if either the DELETE RESTRICT or DELETE SET NULL is specified on a self-referencing table during the execution of an ALTER TABLE statement?

DB2 issues error message defining an invalid constraint is attempted.

What is table check constraint?

A.Table check constraints designate the values that specific columns of a base table can contain, providing you a method of controlling the integrity of data entered into tables. You can create tables with table check constraints using the CREATE TABLE statement, or add the constraints with the ALTER TABLE statement. E.g.,

What is Check integrity and check pending state?

If the check integrity is compromised, or cannot be guaranteed for a table, the table space or partition that contains the table is placed in a check pending state. Check integrity is the condition that exists when each row of a table conforms to the check constraints defined on that table.

What are delete-connected tables?

Tables related with a foreign key are called delete-connected because a deletion in the primary key table can affect or be affected by the contents of the foreign key table.

What is a cycle in DB2 mean?

A.A cycle is a situation in which tables are related to each other through both primary and foreign key relationships. I.e., A set of referential constraints in which each associated table is a descendent of itself.

DB2 Table spaces

The different types of Table spaces.

1. Simple Table Space
2. Segmented Table Space
3. Partitioned Table Space (some partitioned table spaces can also be EA-enabled table spaces)
4. LOB Table Space

How will compress data in a table space or partition?

To compress data in a table space or partition, specify COMPRESS YES on CREATE TABLESPACE or ALTER TABLESPACE, then run LOAD or REORG. When you compress data, bit strings that occur frequently are replaced by shorter strings. Information about the mapping of bit strings to their replacements is stored in a compression dictionary. Computer processing is required to compress data before it is stored and to decompress the data when it is retrieved from storage.

Can you compress data in LOB space?


Reverse search command

In unix and linux box, when we do Ctrl+R in bash, it will provide option to enter the search key to find the recently used commands.

For example you used
$sudo su - admin
$vim .profile
$ls -ltra
$cd /tmp
$ls -ltr

now again want to sudo to admin. Press Ctrl+R and type su, it will start display the recent last commands which have su characters. Then you can select the right one.

The history of these commands stored in .bash_history. You can view the list of commands used using history command also. By default it will store 500 commands. you can change it by
setting HISTSIZE. For example , if you want to store up to 100 commands then

To clear the history, use
history -c

32-bit or 64-bit?

Sometimes we need to know the box is 32-bit or 64-bit. For example we want to install oracle. Before installing, we need to know the box version so that accordingly we could choose the release software file. Here i am just writing on how to get the unix box is 32-bit or 64-bit and oracle, perl and so on.

How to know the unix box is 32-bit or 64-bit?

Use command isainfo -kv to get the 32 bit or 64 bit machine.

How to find whether perl is 32-bit or 64-bit?

file /usr/bin/perl

if it 64-bit then it display ELF 64-bit otherwise ELF 32-bit.

-bash$ file /usr/perl/bin/perl
/usr/perl/bin/perl: ELF 32-bit MSB executable SPARC Version 1, dynamically linked, not stripped

How to find Oracle is 32-bit or 64-bit?

-bash$ file /soft/oracle/product/11.2.0/db/lib/
/soft/oracle/product/11.2.0/db/lib/ ELF 64-bit MSB dynamic lib SPARCV9 Version 1, dynamically linked, not stripped

How to find the python is 32-bit or 64-bit?

-bash$ file /usr/bin/python
/usr/bin/python: ELF 32-bit MSB executable SPARC Version 1, dynamically linked, not stripped, no debugging information available

How to get the Unix version, OS, kernel and machine details?

How to get the release of kernel in Unix?
uname -r

Which OS installed?
uname -0

Which kernel installed?
uname -s


To get kernel version
uname -v

To get processor
uname -p

To get machine name
uname -m

To get platform details
uname -i

To get all details

uname -a

How to add two field in easytrieve / eztrieve / ezytrieve

To add two fields, just give like below.

A = b + c

Sample Program:



GRP-TRN-AMT 09 9 N 2

OUT-REC 01 80 A
OUT-GRP-AMT 09 11 N 2

I W 03 N 0

OUT-REC = ' '
WS-CNT1 = WS-CNT1 + 1