Cognos10 installation for Windows

I was struggling with Cognos BI server installation for my laptop which is running Windows 7. Later I faced similar challenge for Windows 2008 server machine. In both cases I was looking for default out-of-the-box installation.

The place where I got stuck was with configuring the web server. IBM Cognos documentation is barely adequate. So I thought I would post the steps for the installation for everybody's benefit. I installed Apache web server. The key is to edit httpd.conf file for virtual directory mapping.

Include conf/extra/cognos10.conf # added this line in httpd.conf

Contents of included file cognos10.conf:

ScriptAlias /ibmcognos/cgi-bin "C:/Program Files (x86)/ibm/cognos/c10/cgi-bin"
<Directory "C:/Program Files (x86)/ibm/cognos/c10/cgi-bin">
    AllowOverride None
    Options None
    Order Allow,Deny
    Allow from All
</Directory>
Alias /ibmcognos "C:/Program Files (x86)/ibm/cognos/c10/webcontent"
<Directory "C:/Program Files (x86)/ibm/cognos/c10/webcontent">
   Options None
   AllowOverride None
   Order Allow,Deny
   Allow from All
</Directory>

Returning values from SQL*Plus to shell

There are several projects that I worked on where I had to get return values from Oracle stored procedures or PL/SQL block into shell script. If the embedded script returns only one value then shell back-tick can serve the purpose.

#!/usr/bin/bash

ORACLE_HOME=/cygdrive/c/app/abhattacharya/oracle

DW_DB_USER=abhijit

DW_DB_PWD=abhijit

DW_DB_NAME=knut

OUTPUT=`${ORACLE_HOME}/bin/sqlplus -s ${DW_DB_USER}/${DW_DB_PWD}\@${DW_DB_NAME}<<EOF

SET HEADING OFF FEEDBACK OFF VERIFY OFF TIME OFF TIMING OFF

SET SERVEROUTPUT ON SIZE 1000000

WHENEVER SQLERROR EXIT SQLCODE

DECLARE

   l_x INT ;

BEGIN

   l_x := 5 ;

   DBMS_OUTPUT.PUT_LINE(l_x) ;

END ;

/                                                                                                                                                                                                                                                                                                                     

This technique doesn’t work if the embedded code needs to return more than one rows.  Bash shell arrays come handy for that scenario. The following script simulates multiple values being returned using DBMS_OUTPUT statements.

#!/usr/bin/bash

ORACLE_HOME=/cygdrive/c/app/abhattacharya/oracle

DW_DB_USER=abhijit

DW_DB_PWD=abhijit

DW_DB_NAME=knut

OUTPUT_LIST=(`${ORACLE_HOME}/bin/sqlplus -s ${DW_DB_USER}/${DW_DB_PWD}\@${DW_DB_NAME}<<EOF

SET HEADING OFF FEEDBACK OFF VERIFY OFF TIME OFF TIMING OFF

SET SERVEROUTPUT ON SIZE 1000000

WHENEVER SQLERROR EXIT SQLCODE

DECLARE

   l_x INT ;

BEGIN

   l_x := 5 ;

   DBMS_OUTPUT.PUT_LINE(l_x) ;

   DBMS_OUTPUT.PUT_LINE(l_x) ;

END ;

/

The script worked well till I needed another functionality – to return Oracle error code to shell in case PL/SQL fails for violating any business rule. In case Oracle raises an exception shell return status variable “$?” can be used to capture that. But it will not provide SQL error message (SQLERRM). To make the script robust, I decided to handle any exception within PL/SQL exception block and return SQLERRM & SQLCODE as part of DBMS_OUTPUT statement.

To differentiate between an error and successful execution, it is useful to give the output a structure similar to record. We can use the first field to indicate success or failure with additional fields separated by field separator. It is necessary to make sure that the field separator will not appear in the data. I have used ‘~’ as separator here but one field separator that I really like is Ctrl-A. It is quite rare to find Ctrl-A in regular data set. After these changes my script looks like as follows:

#!/usr/bin/bash

ORACLE_HOME=/cygdrive/c/app/abhattacharya/oracle

DW_DB_USER=abhijit

DW_DB_PWD=abhijit

DW_DB_NAME=knut

OUTPUT_LIST=(`${ORACLE_HOME}/bin/sqlplus -s ${DW_DB_USER}/${DW_DB_PWD}\@${DW_DB_NAME}<<EOF

SET HEADING OFF FEEDBACK OFF VERIFY OFF TIME OFF TIMING OFF

SET SERVEROUTPUT ON SIZE 1000000

SET LINESIZE 1000

WHENEVER SQLERROR EXIT SQLCODE

DECLARE

   l_x INT ;

   l_err_code INT := 0 ;

   l_err_msg VARCHAR2(4000) := '';

BEGIN

   l_x := 5 ;

   DBMS_OUTPUT.PUT_LINE(l_err_code || '~' || l_err_msg || '~' || l_x ) ;

   DBMS_OUTPUT.PUT_LINE(l_err_code || '~' || l_err_msg || '~' || l_x) ;

   RAISE_APPLICATION_ERROR(-20001,'Testing-of-error') ;

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE(SQLCODE || '~' || REPLACE(SQLERRM,' ' ,CHR(1)) ) ;

END ;

/

 

Similar technique can be used for returning values from mySQL to shell as well.

Final day in Yellowstone

I am writing this after a break of almost eight months. In between I changed my job. Other than that excuse, I became lazy for a while. It takes sometime to break the moment of inertia.

Anyway, the final day in Yellowstone was most entertaining. We drove to Grand Teton National Park. It is a less visited park so at times provides more opportunity to encounter wild animals. One of the park ranger suggested that we come back after seven in the evening for animal encounter. Would it not be exciting to see a mama grizzly bear with babies up close? We saw one in Yellowstone but at a distance. We had to use binoculars to get a good glimpse. So kids wanted to linger around. We were amazed to see how they could overcome their tiredness and remain energetic. We drove thousand mile to come to Yellowstone from Bay Area. Everyday inside the park we drove anywhere between 150-200 miles and walked to go on trails. Always kids gave us the enthusiasm.

Since we were leaving next day, we decided to leave for West Yellowstone in the late afternoon. Now kids tell me that we took the right decision otherwise we would not have the chance encounter. I will not describe what we saw on our way back. Video is here for the readers to enjoy - specially for animal lovers like us. I posted the same in my Facebook page.Later my elder daughter came up with a nice fictional story as we headed for our hotel.



There was a mama bear who used to live by the side of the busy highway connecting Yellowstone and Grand Teton National Park. She had a baby. She was worried about her baby crossing the road. She taught her baby to look at both sides of the road and to start walking only when motorists came to a stop. One day she wanted to put her beloved baby to ultimate test. She crossed the street, intentionally leaving behind her cub. Standing behind the thicket, she started observing her cub. Her heart was pounding in fear - in case anything happened to her cub. The video contains what happened next....The cub waited patiently by the side of the road several minutes. We noticed the cub and came to a stop. The cub looked at the other side. When everything came to a stand still the cub decided to hop across the street.