This article describes the integration between SPSS and
Cognos solutions. The integration with Cognos comes in the form of being able
to directly connect to Cognos as a data source for SPSS modeler and also being
able to export the results to Cognos directly to allow Cognos to report on the
results. The benefits of using Cognos as a source is that your data is all in
one place and its formatted and tidied up suitable for SPSS analytics.
Exporting the results to Cognos has the benefit of being able to report on the
results using the familiar.

Cognos reporting formats and various different types of Cognos reports, such as dashboards and active reports. Below figure showcases one such context diagram and its integration with SPSS:

To implement this is reasonably straight forward and
involves selecting the Cognos source node or the Cognos export node and dragging it into your stream as shown below:

You then have to edit the Cognos node and add the Cognos
server IP address details and login credentials. As shown on Figure below –

You also need to make an ODBC connection to the Cognos Data
warehouse from the SPSS server. This needs to have the same name and details as
the Cognos data source. If these details do not match on the Cognos and the
SPSS server the integration will not work.

Below figure shows the details:

The benefits of using Cognos as a source is that your data
is all in one place and its formatted and tidied up suitable for SPSS
analytics. Exporting the results to Cognos has the benefit of being able to
report on the results using the familiar reporting format and various different
types of Cognos reports that the company uses.

Note: To either
export to Cognos or use Cognos as a data source an ODBC connection to the
Cognos data warehouse must be established as well as the Cognos Server
connection.

Summary:

In this way, we can integrate IBM Cognos BI with IBM SPSS product and get reporting data from prediction and analysis from SPSS.

What are database logs? They are minimal records generated by transactional writes to keep track of database changes. In all senses, they are nothing but side products generated out of write operations within transactions; and are not something served out of, in response to users’ data queries. Moreover, DBMS incurs CPU, memory and storage resources owing to generation and maintenance of these logs. An obvious question next to this would be why someone on earth should care for stuffs that eat system resources and do just nothing? If you are a newbie in the field of system administration, and at this point ready to turn off database logging forever; hold on your decision until you read this post completely. Data management has always been a serious affair regardless of the type and/or size of the business dealing with it. Nobody would rejoice investing on something having no significant value add. So, logs are required and important in much greater extent. How? Let’s understand that.

Transaction writes facilitate applications performing changes to database’s data. By fundamental virtue, they steal database consistency at the beginning, and re-establish it at the end. In other words, during execution, transactions make database inconsistent. In addition, like any other system, failure of a DBMS is not uncommon. Situations like power outage, OS or software crash, disk corruption are unavoidable. However, under any circumstances, businesses cannot afford loosing data, and hence DBMS vendors must ensure database recovery across points of failures. Scenarios, wherein DBMS is brought down disgracefully, database is typically left inconsistent owing to abnormal termination of ongoing transactions. Applications, if allowed to connect to the database at this state, would certainly fetch some inaccurate data, which a business can never accept. Therefore, post such failure, DBMS must bring database consistency back before making it available for use. While doing this DBMS would need to refer to the history of transactional changes happened to the database before failure; but, wait…history? Where is it? Relax…DBMS won’t let your business down. Here comes the utility of transaction logs, whose credibility was being questioned sometime back. Recall that they kept on consuming system resources to keep track of the changes your database was undergoing during its good times. Now, it’s their turn to pay back. DBMS would read individual log records stored within log files sequentially, and perform the following.

Redo the effects of committed transactions that could not make their way to database disks before the failure.

At the end, your database will get the lost data back and regain its consistency. Henceforth, applications are guaranteed to see correct data whenever connected to the database.

In another kind of failure situation, your database storage may completely or partially go bad, and you end up restoring a backup image of the database taken prior to the disk crash. Definitely, this will rebuild your database, but its state will be taken back to the backup time. Should you ask DBMS to take the state of your database forward, log records following the backup timestamp will be read sequentially from the log files and re-applied to the restored database.

So far we discussed the importance of transaction logs in a DBMS, but have you ever wondered that they could be even more precious than the actual data stored within your database? Well, let’s face the truth straight away. Yes, DBMS always cares more for transaction logs than user data. It makes sure that log records are saved in log disks before the corresponding user data is made persistent to the database. Why so? Let’s explore.

Transaction writes bring data pages from disks to bufferpool area of database memory and modifies them per user request. Each modification generates log record in log buffer (different from bufferpool, but collocated with it within database memory). Now, it is not a rare scenario, wherein bufferpool gets full of changed data pages (a.k.a. dirty pages) owing to long running transactions and DBMS needs to make space for new transactions. This is called dirty page steal, where new data pages as requested by new transaction replace the old dirty pages pertaining to other ongoing transactions. Those old dirty pages are placed into database disk. However, before that, DBMS must write their corresponding log records into the log files within log disk. If that wasn’t the logic (i.e. dirty pages were made persistent to database storage without having corresponding log records flushed to files), and a crash (like power outage) was encountered, DBMS wouldn’t find the necessary log records to undo uncommitted changes (that were written to disk). As a result, database consistency cannot be re-established. In a nutshell, transaction logs must go to disk before corresponding data changes do so. In DBMS world this mechanism is known as ‘Write Ahead Logging’ (WAL).

Externalization of log records from memory to disk is initialized when a transaction commits, and/or log buffer gets full, and/or bufferpool gets full. Writing bufferpool data to database disk on the other hand can run asynchronously based upon whether conditions like bufferpool full and/or deactivation of database etc. are met.

Hope this explains the perspective of DBMS treating transaction logs as golden data and why you cannot afford loosing them if data recovery is one of your priorities. I will talk more about this area of database technology in my upcoming posts. Until then, please stay tuned.

Although I work for IBM, the views expressed are my own and not necessarily those of IBM and its affiliates.

With
every DB2 UDB database there are two different databases logging mode,

Circular database mode & Archival
database mode.When the database is created by default it is in circular mode
& as per the business Requirement you can change the mode from circular to
archival logging mode.Also with the database creation there are 3 primary and 2
secondary log files gets created.

Circular logging mode

Circular
logging is a default logging mode when it is used, records stored into the

Log
buffer are written to primary log files in a circular fashion. Redo log entries
are written to the current “active” log file and when that log file becomes
full , it is marked as “unavailable”.DB2 marks next log file in the sequence as
a active log file and continue writing log entries into it. And when that log
file becomes full , then process

gets
repeated. When the transactions are terminated the corresponding log records
are released because they are no longer needed. When all the records stored in
an individual log file are released, that log file is treated as being
“reusable” and it becomes “active log file “ for the next transactions thus its
contents are overwritten by new log entries.

gets back
to the primary log file which is marked as “unavailable” then DB2 databasemanager will allocate secondary log file. Now
the log entries are being written into the secondary log file.As soon as this secondary
log file becomes full,

the DB2
Database Manager will check the primary log file again and if its status

is still
“unavailable”, another secondary log file is allocated and redo entries are
being written into it. This process continues until all the secondary log files
get full,

which is
indicated by the parameter “logsecond” . Once all the secondary log files get
full and there is no primary log file available for writing redo entries then
following error message will generated :

SQL0964C
The transaction log
for the database is full.

Here you
have to increase number of secondary log files .

Ødb2
update db cfg for db_name using logsecond ‘value’

In circular
logging mode as the contents of log file are overwritten you can recover the
database up to the last full database backup performed. and you cannot perform
the point in time recovery with circular logging mode.

Archival logging mode

Like circular logging mode, when
archival logging is used the redo log entries from log buffer are written into
the primary log files. However unlike circular logging these log files are
never reused.when
all records stored in an individual log file are released, that file is marked
as being “archived” rather than as being “reusable” and it can be used again if
it is needed for the roll forward recovery. When the first primary log file
becomes full , next primary log file is allocated so that the desired number of
primary log files “logprimary”is always
available for use. All the log entries related to a single transaction must fit
within the active log space available.In case of log running transaction it
requires more log space other than the primary log file size thus the secondary
log file may be allocated and used.

In
this mode once the log file gets full another primary log file is allocated and the transaction information
gets logged into that. During that period of time the first primary log file is
being archived to another destination configured by the parameter “Logarchmeth1”
and can be reusable for the further log information storage. Same process is
repeated until the disk space is available for the archived destination.

In case of
archival logging mode you can recover your database up to a current state or up
to a particular state by using the point in time recovery options which are
described in later section of this article. Here the archived log files are be
used for the point in time recovery.

b)How to change the database logging
mode:

As
described earlier wherever the DB2 UDB database is created by default it is in
Circular logging mode , if you want to change the mode from circular to
archival mode

Perform
following steps:

Step
1:change the following parameters

i)logretain=recovery
or userexit=on

Ødb2 terminate

Ødb2 force application all

Ødb2 update db cfg for db_name using
Logretain recovery

or

Ødb2 update db cfg for db_name using
Userexit=on

ii)blk_log_dsk_ful=yes

Ødb2 update db cfg for db_name using
blk_log_dsk_ful yes

Setting blk_log_dsk_ful to yes causes applications to hang when DB2 encounters a log
disk full error, thus allowing you to resolve the error and allowing the
transaction to complete. You can resolve a disk full situation by moving old
log files to another file system or by enlarging the file system, so that
hanging applications can complete.

SQL1116N A connection to or activation of
database "db_name" cannot be made because of backup pending:sqlstate=57019

This is because we have changed the database
logging mode from circular to archival so we must perform full database backup
here, as the backup of circular logging mode cannot be applicable to archival
logging mode and vice versa.

Ødb2 backup database
db_name to d:\db_name\backup

Ødb2 connect to db_name

Step
2: Set the archival destination

To
set the archival destination create a folder “archive” on a disk where you have
sufficient disk space so that the log files will get archived to this location
once it is filled. Keep your archive destination other than log files
destination.

The topics
in this section describe how to package and test a Windows Azure
application, how to create and deploy a hosted service in Windows
Azure, and how to delete a hosted service that is no longer in use.

How to Package your Service

The CSPack Command-Line Tool packages your service to be deployed to the Windows Azure fabric. The cspack.exe
utility generates a service package file that you can upload to Windows
Azure using the Windows Azure Platform Management Portal. By default
the package is named .cspkg, but you can specify a different name if you choose.

CSPack command-line example

The
following command line example creates a package that can be deployed
as a hosted service. It specifies the service definition file to use.
Using the /role option it specifies the directory
where the binaries for the role reside and the DLL where the entry
point of the role is defined. Using the /out option it specifies the
out location for the role binaries and the name of the package file.

How to Deploy a Service in Windows Azure

The
Windows Azure SDK provides an environment and tools for developing
services to be deployed to Windows Azure. You can use the Windows Azure
compute emulator and storage emulator to debug your application and
perform mixed-mode testing. Then use the CSPack Command-Line Tool to
package the application for deployment to the Windows Azure staging or
production environment.

The following figure shows the stages of service development and deployment.

You can
debug your service locally, without connecting to Windows Azure, by
using the compute and storage emulators. The Windows Azure compute
emulator simulates the Windows Azure fabric, letting you run and test
your service locally to ensure that it writes adequate information to
the log. After your service is deployed to the Windows Azure staging or
production environment, logging messages and alerts is the only way to
gather debugging information. You cannot attach a debugger to a
service that is deployed in Windows Azure. For more information about
using the compute emulator to debug your service.

The
storage emulator service simulates the Windows Azure storage services,
letting you run and debug code that calls into the storage services
and, together with the compute emulator, helps you test your service in
the local environment. Once your service is running in the local
development environment, you can change your configuration files to
connect to Windows Azure and test against the production storage
services in mixed mode.

To learn
more about how to configure storage access URIs to access storage
resources in Windows Azure or the storage emulator,

When
your service is connected to the Windows Azure production storage
services it runs in mixed mode, meaning that the service executes in
the compute emulator, but your data is hosted in Windows Azure. Once
local testing is complete, using mixed mode lets you test your service
in a staging environment.

After you debug your service in mixed mode, you are ready to package it for deployment to Windows Azure.

Once
debugging is complete, use the CSPack Command-Line Tool to package your
service for deployment to the Windows Azure staging or production
environment. The cspack.exe utility generates a service
package file that you can upload to Windows Azure by using the Windows
Azure Platform Management Portal. The default package name is .cspkg, but you can specify a different name if you choose.

If you
have installed the Windows Azure Tools for Microsoft Visual Studio, you
can package and deploy your service from within Visual Studio. For
more information, see How to Publish a Windows Azure Application using
the Windows Azure tools.

After
you package your service, you can use the Windows Azure Platform
Management Portal to create a hosted service that you can deploy to the
Windows Azure staging or production environment.

You will need to upload two files:

The service package file that you created with the cspack.exe utility.

The service configuration file, which provides configuration values for your service.

When you
upload the service package and configuration file, you will be
provided with an internal staging URL that you can use to test your
service privately in the Windows Azure staging environment. When you
are ready to put your service into production, swap the service from
the staging URL to the production URL.

How to Create a Hosted Service

Once you have written your Windows Azure application, you must deploy it to a Windows Azure hosted service.

On the ribbon, click New Hosted Service. This will open the Create a New Hosted Service window.

On the Create a new Hosted Service window select a subscription to add the hosted service to from the Choose a Subscription dropdown.

In Enter a name for your service, type a name for this service. This will help you identify this particular service when you have deployed multiple services.

In Enter a URL for your service, type a subdomain name to create the URL at which your service will be available.

If you
have created Affinity groups and you want to assign this service to a
particular group, click the radio button next to the Affinity Group
dropdown. Otherwise leave the default setting of No Affinity.

From Choose a region select the region

If you are not deploying a package to the service at this time, click Do not deploy, and then click OK.

If you are deploying a service at this time follow the procedure in the next section.

When you
deploy a service you can choose to deploy to either the staging
environment or the production environment. A service deployed to the
staging environment is assigned a URL with the following format:
{deploymentid}.cloudapp.net. A service deployed to the production
environment is assigned a URL with the following format:
{hostedservicename}.cloudapp.net. The staging environment is useful as a
test bed for your service prior to going live with it. In addition,
when you are ready to go live, it is faster to swap VIPS to move your
service to the production environment than to deploy it directly there.
For more information on swapping VIPs

In Configuration file, click Browse, and then click the ServiceConfiguration.cscfg file.

Click OK.

Delete a Hosted Service from Windows Azure

Use the
following procedure to delete a hosted service from Windows Azure by
using the Windows Azure Platform Management Portal. Before you can
delete a service, you must delete each of its current service
deployments.

To
delete a hosted service, you must be either the service administrator
or a co-administrator for the Windows Azure subscription that was used
to deploy the service.

The
items list displays all hosted services for which you are an
administrator, sorted by subscription. Beneath each service, each
service deployment is listed. Before you can delete the service, you
must delete all current deployments of the service.

If needed, delete each service deployment for the service.

To delete a service deployment:

In the items list, expand the subscription, expand the hosted service, and then click the service deployment to select it.

Stop the service deployment if it is running. To do this, click the service deployment to select it. On the ribbon, in the Service Deployment group, click Stop. Then wait until the service deployment's status changes to Stopped.

With the service deployment still selected, on the ribbon, in the Service Deployment group, click Delete. The deletion process might take several minutes to complete.

To delete the service, in the items list, click the service to select it. Then, on the ribbon, in the Service group, click Delete.

Cloud computing has almost become a rage among IT industry and businesses today. It is transforming the way organizations create software, applications and the way they do business. The fundamental need of focusing on core business, controlling IT expenditures and adaptability to changing business ecosystem is driving companies to move to cloud.

Establishing a in-house data warehousing and business intelligence (BI) environment is not a trivial task, organizations have to spend millions of dollars to procure hardware, software and then spend months in installation, configuration and optimization before they could actually start using these systems. In addition to this, top it up with the investment in resources for continuous administration and the periodical hardware upgrades to manage growth and keep the momentum going.

All the above factors combined together make it very compelling for companies to make a radical shift of some of their on-premise analytical data warehouse environments to cloud. It simplifies and speeds up analytics without the need of deploying heavy weight infrastructure and teams. On-demand resource provisioning helps in accommodating real-time workload surges without much manual interference. Imagine having a plethora of compute capacity lying idle in server rooms for once or twice a week reporting vs. having it on cloud and only paying for the required usage.

However, irrespective of these innumerable benefits which a cloud can provide it still involves certain challenges which may make businesses wary of putting their data warehouses on cloud:

Security:

What type of data can be put on public cloud?

How secure it is to put sensitive organizational data on public cloud?

Data Volume:

What volume of data can a cloud environment support? Loading huge amount of data which is very typical of a data warehouse requires high bandwidth, how efficiently can a cloud handle that?

Performance:

Performance of a virtual machine on cloud may not match that of a bare metal server.

This may impact the complex analytics being performed on a data warehouse.

Transaction Latency:

What could be the impact on business due to loss in transaction latency arising out of communication over a network (large distance between datacenter and users and/or lower bandwidth), especially in financial world?

There are several vendors who now provide data warehouse and BI as a service but everyone may not be able to handle the complexity of a data warehouse and analytics ecosystem on cloud.

IBM’s BLU acceleration on cloudis an offering which provides a self-service BI and data warehousing on cloud using best in class security and other features to support even the most complex production environments. It is powered by IBM DB2 with BLU Acceleration a next generation in-memory technology. Columnar data processing and high compression rates combined with an enterprise class BI and DW tools like Data Architect, Cognos and compatibility with R help customers transform their data into insights at speed of thought. Through BLU Acceleration on Cloud now even small organizations who could not afford to establish data warehouses earlier can have access to one of the most advanced analytical environment and make the best out of their data at a very low cost.

BLU Acceleration on cloud is available on IBM Softlayer and Amazon Web Services (AWS). I am really excited to invite you to get hands on experience of the technology through a Free Trial (in Beta). Do let us know your feedback or any queries which you may have on this forum.

Although I work for IBM, the views expressed are my own and not necessarily those of IBM and its affiliates.

PL/SQL
(Procedural Language/Structured Query Language) statements can be
compiled and executed using DB2 interfaces. This support reduces the
complexity of enabling existing PL/SQL solutions so that they will
work with the DB2 data server.

The
supported interfaces include:

*
DB2 command line processor (CLP)

*
DB2 CLPPlus

*
IBM® Data Studio full client

The
DB2 Command Line Processor (CLP) is a command line interface and tool
available with both DB2 servers and DB2 clients from which DB2
commands can be issued, SQL statements executed, and utilities run.
The CLP is essentially a command processor or shell environment that
is customized for working with DB2.The DB2 CLP can be used as a
primary interface for interacting with DB2 instances and databases,
as an alternative to using DB2 graphical user-interfaces, or as an
interface for occasional use. The db2 command starts the
command line processor (CLP).

We
will learn to execute PLSQL statements through Interactive input
mode, characterized by the

db2
=> input prompt.

PL/SQL
statement execution is not enabled from these interfaces by default.
PL/SQL statement execution support must be enabled on the DB2 data
server.

Let
us see how to enable PLSQL statements support in DB2 v9.7 and then
write sample PLSQL programs and learn how to compile and execute them
through DB2 CLP command window.

Enable
PLSQL statements in DB2 environments

#
Open a DB2 command window.

#
Start the DB2 database manager.

db2start

#
Set the DB2_COMPATIBILITY_VECTOR registry variable to the hexadecimal
value ORA that enables the compatibility features that you want to
use.

db2set
DB2_COMPATIBILITY_VECTOR=ORA

#
Set the DB2_DEFERRED_PREPARE_SEMANTICS registry variable to YES to
enable deferred prepare support.

db2set
DB2_DEFERRED_PREPARE_SEMANTICS=YES

#
Issue the db2stop command and the db2start command to stop and then
restart the database manager.

db2stop

db2start

After
the above steps are executed successfully we may continue to create
database and database objects like tables, indexes, views, sequences
etc. The steps to create these will not be discussed as the focus
would be on the PLSQL statements creation and execution.

Remember
to execute the below db2 update cfg command immediately after the
database is created and before creating any db objects like tables,
indexes etc

db2
update db cfg for <dbname> using AUTO_REVAL DEFERRED_FORCE

db2
update db cfg for <dbname> using DECFLT_ROUNDING ROUND_HALF_UP

In
this section we shall learn to execute PLSQL procedures using simple
datatype variables, record types and associative arrays as INPUT
variables to the procedures using DB2 CLP.

Create
and Execute a PLSQL procedure

The
below connection steps will be one time setup for the following SQL
statements.

db2
connect to mydb

db2
set schema DEMO

db2
set PATH=SYSTEM PATH, 'DEMO'

db2
-td@

Here
'@' is the statement termination character.

a.
PLSQL procedure with simple datatype variable as IN parameter
using DB2 CLP

db2
=> CREATE TABLE emp (

name
VARCHAR2(10),

salary
NUMBER ) @

db2
=> CREATE OR REPLACE PROCEDURE fill_comp( p_name IN VARCHAR,

p_sal IN NUMBER,

msg OUT VARCHAR)

AS

BEGIN

insert
into emp VALUES (p_name, p_sal) ;

msg
:= 'The procedure inserts values into emp table:' ;

END
fill_comp;

db2=>
CALL fill_comp('Paul',200)@

The SQL command exceuted successfully

The procedure inserts values into emp table

b.
PLSQL procedure with record type as IN parameter

db2=>
CREATE OR REPLACE PACKAGE REC_PKG

AS

TYPE
EMP_OBJ IS RECORD(

NAME VARCHAR(200),

ADDR VARCHAR(200),

PHONE
NUMBER);

PROCEDURE
fill_comp( IN_EMP IN EMP_OBJ, stat OUT VARCHAR2);

END
@

db2
=> CREATE OR REPLACE PACKAGE BODY REC_PKG

AS

PROCEDURE
fill_comp( IN_EMP IN EMP_OBJ,

stat
OUT VARCHAR2)

AS

BEGIN

insert
into employee values(IN_EMP.NAME,

IN_EMP.ADDR,

IN_EMP.PHONE);

stat
:= ' This procedure inserts into employee';

END
fill_comp;

END
REC_PKG @

db2=>
SET SERVEROUTPUT ON@

DECLARE

EMP_VAL
REC_PKG.EMP_OBJ;

status
VARCHAR2(30);

BEGIN

EMP_VAL.NAME
:= 'Paul';

EMP_VAL.ADDR
:= 'Sandiego';

EMP_VAL.PHONE
:= 1234;

REC_PKG.fill_comp(EMP_VAL,status);

DBMS_OUTPUT.PUT_LINE(status);

@

db2=>
The SQL command completed successfully

This
procedure inserts into employee

PLSQL
procedure with Array type as IN parameter

db2=>
CREATE OR REPLACE PACKAGE REC_PKG

AS

TYPE
EMP_OBJ IS RECORD(

NAME VARCHAR(200),

ADDR VARCHAR(200),

PHONE
NUMBER);

TYPE
emp_array is table of EMP_OBJ INDEX BY BINARY_INTEGER;

PROCEDURE
fill_comp( IN_EMP_ARR IN emp_array, stat OUT VARCHAR2);

END
@

db2
=> CREATE OR REPLACE PACKAGE BODY REC_PKG

AS

PROCEDURE
fill_comp( IN_EMP_ARR IN emp_array ,

stat
OUT VARCHAR2)

AS

BEGIN

FOR
i IN 1 .. IN_EMP_ARR.COUNT

LOOP

insert
into employee values(IN_EMP_ARR(i).NAME,

IN_EMP_ARR(i).ADDR,

IN_EMP_ARR(i).PHONE);

END
LOOP;

stat
:= ' This procedure inserts into employee from an array';

END
fill_comp;

END
REC_PKG @

db2=>
SET SERVEROUTPUT ON@

DECLARE

EMP_ARR
REC_PKG.emp_array;

status
VARCHAR2(30);

BEGIN

EMP_ARR(1).NAME
:= 'Mike;

EMP_ARR(1).ADDR
:= 'Paris';

EMP_ARR(1).PHONE := 4367;

REC_PKG.fill_comp(EMP_ARR,status);

DBMS_OUTPUT.PUT_LINE(status);

@

db2=>
The SQL command completed successfully

This
procedure inserts into employee from an array

This
topic helps you to compile and execute PLSQL procedures, packages
from DB2 CLP interactive input mode. I have tried to explain through
examples, the most frequently used PLSQL procedure formats and how
to perform runtime testing or execute them in DB2 CLP.

These posts are my opinions and do not necessarily represent IBM’s positions, strategies, or opinions

Let's all face it, Cloud Computing was a Fad in 2010, 2011 it drove influence to key IT Decision makers to conduct Cloud POCs in the Enterprise, and in 2012, the drive to adoption will further increase if the current rate of demand continues.

Today, I delivered a webinar on DB2 and Cloud Computing with Databases in the Cloud on IBM DeveloperWorks.

It was an amazing experience talking to DB2 Users and IT Pros in general.

The focus of this article is to illustrate some of the key benefits and resources available for implementing a Cloud Solution.

DB2 text search indexing is a resource consuming task and it takes times based on number of documents to be indexed, the type and size of the document etc. In this blog, I would like to briefly explain how we can check the progress of ongoing DB2 Text search index updates.

The total number of documents that has to be indexed can be found by checking the number of rows in base table (in this case it is table books) on which index is created or if it is incremental index update that is in progress, then users can check the number of rows in the staging table to get the total number of documents that should be there in collection altogether.

Note: We can get the collection name for any text search index, by querying the SYSIBMTS.TSCOLLECTIONNAMES administrative view.

db2 "select COLLECTIONNAME from SYSIBMTS.TSCOLLECTIONNAMES where INDNAME='MYSCHEMA' and INDSCHEMA='MYTITLEIDX'"

Thus we can check the progress of DB2 Text Search index updates by comparing the number of rows in base table(on which index is created) for initial updates or in staging table for incremental updates with the number of documents in collection got by adminTool.

DB2 Text search event view provides information about indexing status and error events. Events like starting, committing and finishing of index update are also availabe in this event table. Users can get the event view name for a given text search index by querying the SYSIBMTS.TSINDEXES administrative view.

db2 "SELECT EVENTVIEWSCHEMA, EVENTVIEWNAME FROM SYSIBMTS.TSINDEXES WHERE INDSCHEMA='MYSCHEMA' AND INDNAME='MYTITLEIDX'"

As one of the core developer of DB2 Connect CLI team, I got an opportunity to work on supporting generic special registers feature. Idea behind this blog is to spread some of the benefits and usage to help application development community understand it better to leverage the same.

Though focus of this blog would remain CLI centric, similar concept exists in other client drivers like IBM DB2 .NET provider and IBM JDBC driver (aka JCC).

IBM Data Server Driver configuration file (by default named as db2dsdriver.cfg) is catching its popularity among the customers due to its capability of allowing different DSNs and database properties configuration in a central repository manner. In addition, being in XML format, it takes a less of an effort for any user to get used to such configuration files. In DB2 Connect V10.1 Fixpack 2, CLI added new capability to db2dsdriver.cfg by allowing users to set special registers generically.

Before I go deep into the feature explanation, let me begin with answering few basic questions:

What are special registers?

A special register is a storage area that is defined for an application process by the database manager. It is used to store information that can be referenced in SQL statements.

To know more about special registers with examples, refer to the following link:

What is the existing method of setting special registers from client applications?

There are set of special registers which can be set (or updatable) by the client applications. Application can modify such special registers programmatically using “SET” SQL statements. There are few special registers for which DB2 CLI provides connection level keywords. Application can set these keywords either via db2dsdriver.cfg or db2cli.ini configuration files.

Limitations using existing method of setting special registers:

Setting special registers programmatically expects modification of the application source code and recompile each time special register needs added/removed/modified. Also, this needs to be taken care in all impacted application programs.

Using special registers which can be set as CLI keywords can be a better approach than former, but with limited list of such keywords, applications do not get complete solution. CLI can be enhanced to support requested special registers as a keywords, however with data server introducing new special registers at each release, this remains an ongoing solution. This expects users to upgrade their client drivers to be able to get newer special register support as keyword.

What is the newer mechanism CLI provides to address above situation?

To overcome the drawbacks of both the above approaches, it was desired to have a more generic solution to be developed. As a result, CLI has introduced a unique section of special registers viz. <specialregisters> in the configuration file db2dsdriver.cfg. This section allows users to specify a list of special registers that they like to configure. Based on the need, <specialregisters> section can be added at a DSN level or a database level or even globally.

During each connection to a given DSN or a database, CLI reads through db2dsdriver.cfg and processes <specialregisters> section in the following manner:

- read each special register name and its value from <specialregisters> section of a given DSN or a database

- “without scanning/interpreting” form a chain of special registers to be sent to the connected data server.

- upon the first SQL of the connection, flow chained special registers to the server

- server will process each special registers of the chain (along with the 1st SQL of the connection) and set it appropriately at the server.

As we can see from the above logical flow, with this feature, CLI has no dependency to know the special registers to validate. It will simply flow the entries from <specialregisters> section to the server and let server do necessary validations. Another benefit we can see here is because flow of the special registers is chained together along with 1st SQL statement of the connection, network trips to set the special registers is saved significantly now.

When server upgrade occurs and user application likes to set newly supported special registers, with this new feature of CLI, all user needs to do is to add that special register in their <specialregisters> section! As we can see, no driver upgrade is needed here in order to use newer special registers.

Illustrating usage of <specialregisters>

Having given some background, I can now proceed with the working of this feature. Let’s begin with adding <specialregisters> section to existing / new db2dsdriver.cfg configuration file:.

1. Special Registers applicable across all DSNs/databases ( residing under global <parameters> section)

CURRENT DEFAULT TRANSFORM GROUP = 'MYSTRUCT2'

CURRENT LOCALE LC_MESSAGES = 'en_CA'"

2. Special Registers applicable for DSN = sample

CURRENT SCHEMA = 'MYSCHEMA'

CURRENT DEGREE = 'ANY'

CURRENT DEFAULT TRANSFORM GROUP = 'MYSTRUCT2'

CURRENT LOCALE LC_MESSAGES = 'en_CA'"

3. Special Registers applicable for database = sample2

CURRENT SCHEMA = 'MYSCHEMA1'

CURRENT DEGREE = 'ANY'

CURRENT DEFAULT TRANSFORM GROUP = 'MYSTRUCT2'

CURRENT LOCALE LC_MESSAGES = 'en_CA'"

The above configured special registers for relevant DSNs/databases come into effect with the first SQL statement given post connection. It is at this point the special register settings are applied at the server.

In the above application logic, "INSERT" is the first SQL statement post connection. Along with this SQL statement, the effective special registers list (as listed in the db2dsdriver.cfg) is formed and these special registers get set at the server. In case any special register setting at server has resulted in any warning or an error, those will be chained to the result of 1st SQL’s response. Application can call SQLGetDiagRec() API to retrieve any warning or error details to diagnose the problem.

Where I cannot use this new feature?

To enable client info properties, it’s not recommended to use <specialregisters> section. Existing mechanism either via CLI keywords or environment/connection level attributes can be used instead.

If application logic desires to set special registers during the connection (not at initial phase of the connection), or if they like to change the special registers in between, then setting special registers programmatically is the only way. New feature is useful only as initial value of the special register for the connection.

In summary, as an application user, one can get below benefits with the new feature:

1. Savings in time and network utilization by reduction in network flows

Reduction in network round trips between client and servers since most optimal DRDA protocol is used while flowing special registers set information to the server.

Moreover by chaining set of special registers along with 1st SQL of the connection saves another network round trip by using piggyback mechanism.

2. Less maintenance and upgrade of the driver:

The new approach avoids necessity of driver level upgrade just to exploit any new server special register. All users need to do is add the new special register entry in the <specialregisters> section to the existing drivers’s db2dsdriver.cfg file (minimal driver level requirement is V10.1 Fixpack 2). Knowing many big organization having thousands of client drivers installed at each workstation, this saving brings lot of relief to them.

3. Centralized maintenance:

Using central configuration method for db2dsdriver.cfg, users can now have much controlled manner to add/remove/edit the special registers for their applications. Also, with flexibility of using <specialregisters> under DSN, database or global level, user can tune their need quite easily.

On Feb 1st, 2012, I gave a seminar on my experience working with IBM Optim Tool. This session was well attended;

In the presentation, I talked about my expriences with the product and the way we customised the tool for our needs. The most intriguing thing about IBM Optim tool is that how well it binds with the backend database. We used DB2 for zOS but I'm sure Optim is as well integrated good enough with other databases.

We were able to implement a comprehensive Archial and Purge Solution in less than 3 months. This is a tremendous achievement keeping in mind that everyday data archival was in the range of about 0.3 million rows!

In DB2 embedded SQL application, users can execute SQL statements from multiple threads using DB2 contexts APIs. Application developers need to use context to switch between the connections and each context is the environment from which an application runs all SQL statements. Each context can be associated with one or more threads within an application. Context switching between the connections is supported in embedded SQL C and C++ applications only. Contexts may be switched between threads in a process but not switched between the processes. Application developers can use multiple contexts to provide support for concurrent transactions in a DB2 embedded SQL C and C++ applications.

How about that ? Too abstract ? Let me be simple and detailed.

A context can be created, attached, detached, and destroyed by using the following APIs in DB2.

sqleBeginCtx Create a context

sqleAttachToCtx Attach a context

sqleDetachFromCtx Detach a context

sqleEndCtx Detach and destroy a context

How to change a session in single thread:

Create contexts as many as sessions, e.g.,

sqleBeginCtx(&ctx1, SQL_CTX_CREATE_ONLY, NULL, &sqlca)

sqleBeginCtx(&ctx2, SQL_CTX_CREATE_ONLY, NULL, &sqlca)

Attach a context that you want to handle, e.g.,

sqleAttachToCtx(ctx1, NULL, &sqlca)

Execute SQL Statements.

To change to another session, detach the current context, and attach another session, e.g.,

sqleDetachFromCtx(ctx1, NULL, &sqlca)

sqleAttachToCtx(ctx2, NULL, &sqlca)

Execute SQLs.

Detach sessions, e.g.,

sqleEndCtx(&ctx1, SQL_CTX_END_ALL, NULL, &sqlca)

sqleEndCtx(&ctx2, SQL_CTX_END_ALL, NULL, &sqlca)

Sample code to change a session in single thread:

void *pContext[2];

sqleBeginCtx(&pContext[0], SQL_CTX_CREATE_ONLY, NULL, &sqlca);

sqleBeginCtx(&pContext[1], SQL_CTX_CREATE_ONLY, NULL, &sqlca);

sqleAttachToCtx(pContext[0], NULL, &sqlca);

EXEC SQL CONNECT TO :dbname USER :user USING :passwd;

EXEC SQL SELECT xxx

EXEC SQL UPDATE xxx

sqleDetachFromCtx(pContext[0], NULL, &sqlca);

sqleAttachToCtx(pContext[1], NULL, &sqlca);

EXEC SQL CONNECT TO :dbname USER :user USING :passwd;

EXEC SQL INSERT xxx

sqleDetachFromCtx(pContext[1], NULL, &sqlca);

sqleAttachToCtx(pContext[0], NULL, &sqlca);

EXEC SQL SELECT xxx

EXEC SQL COMMIT;

EXEC SQL CONNECT RESET;

sqleDetachFromCtx(pContext[0], NULL, &sqlca);

sqleAttachToCtx(pContext[1], NULL, &sqlca);

EXEC SQL UPDATE xxx

EXEC SQL COMMIT;

EXEC SQL CONNECT RESET;

sqleEndCtx(&pContext[0], SQL_CTX_END_ALL, NULL, &sqlca);

sqleEndCtx(&pContext[1], SQL_CTX_END_ALL, NULL, &sqlca);

In the similar way application developers can use the DB2 context APIs in a multi-thread applications as well.

Have a happy context switching in embedded applications. The best thing about IBM DB2 is that you can write very simple to a lot complex applications depending on your need and that is why I like DB2.

2. To check whether the system meets the comprehensive pureScale prerequisites for a specific type of network configuration (Linux operating systems only) and for a specific DB2 version, issue the following command (Linux/Unix)::

Usage: db2prereqcheck command can be run from the image/server location.

Common errors\warnings with db2prereqcheck and the solutions.

Windows:

1. Prior to V10.5 Fixpack4, db2prereqcheck was dependent on the Microsoft VC++ redistributables on the machine.

If the VC++ redistibutables are not present on the machine db2prereqcheck execution fails with error as below.

For the DB2 version 10.1 product, the db2prereqcheck tool displays "Could not load the resource library C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\\db2istring_v101.dll" error when the MSVCR100.dll file is missing in the system.

For the DB2 version 10.5 product, the db2prereqcheck tool displays "Could not load the resource library C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\\db2istring_v105.dll" error when the MSVCR110.dll file is missing in the system.

Resolution is to install the below packages and launch db2prereqcheck from image location again.