Category Archives: Technical

Cognos Content Store, Technical

Cognos Content Store Query – Report Name, Package and Search Path Information

The Cognos Content Store database has a lot of valuable information, however, it is not always so easy to find this information. The following query can be useful for returning REPORT_NAME, PACKAGE and SEARCH-PATH information from the Cognos Content Store database.

Be aware the database structures can be changed with any any patch, fixpack or hotfix to Cognos and this SQL may fail or return wrong results. There is a better method for returning this information by using the Cognos SDK. More info here.

Content Store version:
Cognos 8 running on an Oracle database (also works on Cognos 10)

--=============================================================
--  COGNOS CONTENT STORE QUERY (Oracle database)
--  Use to find list REPORT_NAME, PACKAGE and SEARCH-PATH
--=============================================================
WITH base_query AS
(select classid,
         cmobjects.cmid,
         cmobjects.pcmid,
         cmobjnames.name as report_name
    from cognos_c84.cmobjects, cognos_c84.cmobjnames
   where cmobjects.cmid = cmobjnames.cmid
     and classid in (10)
     and cmobjnames.mapdlocaleid = 24
  union
  select classid,
         cmobjects.cmid  as cmid,
         cmobjects.pcmid,
         cmobjnames.name as report_name
    from cognos_c84.cmobjects, cognos_c84.cmobjnames
   where cmobjects.cmid = cmobjnames.cmid
     and cmobjects.cmid in
         (select distinct cmobjects.pcmid from cognos_c84.cmobjects)
     and cmobjnames.mapdlocaleid = 24)
, ctebuildpath (cmid,pcmid,report_name,Hlevel,classid,directory_path) AS
(select base_query.cmid,
       base_query.pcmid,
       base_query.report_name,
       1 as Hlevel,
       classid,
       to_char(base_query.report_name) as directory_path
  from base_query
where report_name = 'Public Folders' /* add myfolders to retrieve reports from my folders of users */
union all
select recur.cmid,
       recur.pcmid,
       recur.report_name,
       cte.Hlevel + 1 as Hlevel,
       recur.classid,
       to_char(cte.directory_path || ' > ' || recur.report_name) as directory_path
  from base_query recur, ctebuildpath cte
where cte.cmid = recur.pcmid)
, report_spec AS
(select spec,
       cmid,
       instr(spec,'/package[@name=')+15 as startposition,
       instr(spec, ']/model[@name=')-instr(spec,'/package[@name=')-15 as endposition
  from cognos_c84.cmobjprops7
where spec is not null)
--
SELECT report_name,
       dbms_lob.substr(spec, endposition, startposition) as package,
       directory_path
  FROM ctebuildpath cte, report_spec rep
WHERE cte.classid = 10
   and cte.cmid = rep.cmid
order by directory_path, report_name

Thanks to Cognos with Hari for putting this together on a SQL Server Content Store.


Cognos Content Store, Technical

Cognos Content Store Query – User, Group, and Role Information

The Cognos Content Store database has a lot of valuable information, however, it is not always so easy to find this information. The following query can be useful for returning USER, GROUP, and ROLE information from the Cognos Content Store database.

Be aware the database structures can be changed with any any patch, fixpack or hotsite to Cognos and the SQL may fail or return wrong results. There is a better method for returning this information by using the Cognos SDK. More info here.

Content Store version:
Cognos 8 running on an Oracle database (also works on Cognos 10)

--=============================================================
--  COGNOS CONTENT STORE QUERY (Oracle database)
--  Use to find list USERS, ROLES and GROUPS information
--=============================================================
 
WITH base_query AS
(select classid,
         cmobjects.cmid,
         cmobjects.pcmid,
         cmobjnames.name as groupname
    from cognos_c84.cmobjects , cognos_c84.cmobjnames
   where cmobjects.cmid = cmobjnames.cmid
     and classid in (26,54)
     and cmobjnames.mapdlocaleid = 24
  union
  select classid,
         cmobjects.cmid as cmid,
         cmobjects.pcmid,
         cmobjnames.name as groupname
    from cognos_c84.cmobjects , cognos_c84.cmobjnames
   where cmobjects.cmid = cmobjnames.cmid
     and cmobjects.cmid in
         (select distinct cmobjects.pcmid from cognos_c84.cmobjects)
     and cmobjnames.mapdlocaleid = 24)
, ctebuildpath (cmid,pcmid,groupname,Hlevel,classid,directory_path) AS
 (select base_query.cmid,
         base_query.pcmid,
         base_query.groupname,
         1 as Hlevel,classid,
         to_char(base_query.groupname) as directory_path
    from base_query
   where groupname = 'Cognos'
   union all
  select recur.cmid,
         recur.pcmid,
         recur.groupname,
         cte.Hlevel+1 as Hlevel,
         recur.classid,
         to_char(cte.directory_path || ' > ' || recur.groupname) as directory_path
    from base_query recur, ctebuildpath cte
   where cte.cmid = recur.pcmid)
SELECT v_user.first_name,
       v_user.last_name,
       v_user.ldap_id       as user_full_name,
       v_group.name,
       v_group.directory,
       v_group_user.cmid    as GROUP_ID,
       classid,
       v_group_user.refcmid user_id
  FROM cognos_c84.cmreford1 v_group_user
  LEFT OUTER JOIN (SELECT c33.cmid user_id,
                          UPPER(c33.NAME) ldap_id,
                          c1.surname last_name,
                          c1.givenname first_name,
                          c1.email email
                     FROM cognos_c84.cmobjprops1 c1
                     left outer join cognos_c84.cmobjprops33 c33
                       on c33.cmid = c1.cmid
                   UNION
                   SELECT cmobjprops1.cmid user_id,
                          UPPER(cmobjprops33.NAME) ldap_id,
                          cmobjprops1.surname last_name,
                          cmobjprops1.givenname first_name,
                          cmobjprops1.email
                     FROM cognos_c84.cmobjprops1,
                          cognos_c84.cmobjprops33
                    WHERE cmobjprops1.cmid = cmobjprops33.cmid) v_user
    ON v_group_user.refcmid = v_user.user_id
  LEFT OUTER JOIN (SELECT ctebuildpath.cmid cmid,
                          UPPER(ctebuildpath.groupname) name,
                          ctebuildpath.directory_path as directory,
                          classid
                     FROM ctebuildpath) v_group
    ON v_group.cmid = v_group_user.cmid

Thanks to Cognos with Hari for putting this together on a SQL Server Content Store.


Technical

How To: Install Oracle 11g Release 2 on Ubuntu 10.04 (32bit)

For most users Ubuntu comes with most standard features included or easily installed. However I find myself often doing a lot of internet searches to find and fix some of the more advanced features. Fortunately, there are a lot of intelligent individauls that add insight into the software features and capabilities.

Here is my attempt to join those ranks with my “How to install Oracle 11g Release 2 on Ubuntu 10.04” post. I put this together in one place because I had to use a number of different sources to get it all right, and I hope this one source will help others. Good Luck!

PREPARATION

Download and Extract the Oracle binaries:

Follow this link to obtain the Oracle 11g Release 2 binaries. It’s free, but you have to register on the Oracle website to download it

After download is complete

sudo su –
mkdir -p /media/database
cd /media/database
unzip /<path-to-zip-file>/linux_11gR2_database_1of2.zip
unzip /<path-to-zip-file>/linux_11gR2_database_2of2.zip

Ensure Ubuntu is in order:

Install Required Packages

sudo su –

apt-get install build-essential
apt-get install libaio1
apt-get install libaio-dev
apt-get install libmotif3
apt-get install libtool
apt-get install expat
apt-get install alien
apt-get install ksh
apt-get install pdksh
apt-get install unixODBC
apt-get install unixODBC-dev
apt-get install sysstat
apt-get install elfutilsi
apt-get install libelf-dev
apt-get install binutils
apt-get install lesstif2
apt-get install lsb-cxx
apt-get install lsb-rpm
apt-get install vim-runtime
apt-get install vim
apt-get install ssh

Manually install deb package to avoid error “linking …ctx/lib/ins_ctx.mk”:
This download will change version number over time – browse this directory and modify code below to match most recent release
libstdc++5_3.3.6-<##>ubuntu1_i386.deb

cd /tmp
wget http://mirrors.kernel.org/ubuntu/pool/universe/g/gcc-3.3/libstdc++5_3.3.6-24ubuntu1_i386.deb
dpkg-deb -x libstdc++5_3.3.6-24ubuntu1_i386.deb ia-libs
cp ia-libs/usr/lib/i386-linux-gnu/libstdc++.so.5.0.7 /usr/lib

cd /usr/lib
ln -sf libstdc++.so.5.0.7 libstdc++.so.5

cd /tmp
rm *.deb
rm -r ia-libs

Create needed objects:

To avoid error invoking target ‘idg4odbc’ of makefile:

ln -s /usr/bin/basename /bin/basename

To avoid errors when executing the post-install root.sh script:

ln -s /usr/bin/awk /bin/awk

Modify Kernel Parameters:

Make a backup, then modify the sysctl.conf configuration file:

cp /etc/sysctl.conf /etc/sysctl.original
echo “#”>> /etc/sysctl.conf
echo “# Oracle 11gR2 entries”>> /etc/sysctl.conf
echo “fs.aio-max-nr=1048576” >> /etc/sysctl.conf
echo “fs.file-max=6815744” >> /etc/sysctl.conf
echo “kernel.shmall=2097152” >> /etc/sysctl.conf
echo “kernel.shmmni=4096” >> /etc/sysctl.conf
echo “kernel.sem=250 32000 100 128” >> /etc/sysctl.conf
echo “net.ipv4.ip_local_port_range=9000 65500” >> /etc/sysctl.conf
echo “net.core.rmem_default=262144” >> /etc/sysctl.conf
echo “net.core.rmem_max=4194304” >> /etc/sysctl.conf
echo “net.core.wmem_default=262144” >> /etc/sysctl.conf
echo “net.core.wmem_max=1048586” >> /etc/sysctl.conf
echo “kernel.shmmax=2147483648” >> /etc/sysctl.conf

Load new kernel parameters:

sysctl -p

Adjust security limits configuration file:

Make a backup, then modify the limits.conf file:

cp /etc/security/limits.conf /etc/security/limits.conf.original
echo “#”>>/etc/security/limits.conf
echo “# Oracle 11gR2 shell limits:”>>/etc/security/limits.conf
echo “oracle soft nproc 2048”>>/etc/security/limits.conf
echo “oracle hard nproc 16384”>>/etc/security/limits.conf
echo “oracle soft nofile 1024”>>/etc/security/limits.conf
echo “oracle hard nofile 65536”>>/etc/security/limits.conf

Adjust login file:

Make a backup, then modify the login file:

cp /etc/pam.d/login /etc/pam.d/login.original
echo “#”>>/etc/pam.d/login
echo “# Oracle 11gR2 entries:”>>/etc/pam.d/login
echo “session required /lib/security/pam_limits.so”>>/etc/pam.d/login
echo “session required pam_limits.so”>>/etc/pam.d/login

Create Oracle Groups and Accounts:

sudo su –

groupadd oinstall
groupadd dba

useradd -m -g oinstall -G dba oracle
usermod -s /bin/bash oracle

passwd oracle

groupadd nobody
usermod -g nobody nobody

Create Oracle Directories:
i.e. /u01/app for Oracle software and /u02/oradata for database files

mkdir -p /u01/app/oracle
mkdir -p /u01/app/oraInventory
mkdir -p /u02/oradata

chown oracle:oinstall /u01/app/oracle
chown oracle:oinstall /u01/app/oraInventory
chown oracle:oinstall /u02/oradata

chmod 750 /u01/app/oracle
chmod 750 /u01/app/oraInventory
chmod 750 /u02/oradata

Install the Oracle Software

Start the Oracle Universal Installer

Login as the Oracle user with below command – do not use ‘su’ command

ssh -Y oracle@192.168.x.y  (or ssh -Y oracle@localhost or ssh -Y oracle@<server-name>)

cd /media/database
./runInstaller

The following screens are displayed:

    Provide required email address.

    Choose ‘Create and Configure Database’

    Choose ‘Desktop Class’


Defaults work fine here.

I modified the ‘Software Location’ to be more meaningful (ora11gdb instead of dbhome_1)
I modified the ‘Global Database Name’ to be more meaningful (ora11g01 instead of orcl)

You will need to provide a password that complies to Oracle standards

 

    Check the ‘Ignore All’ checkbox

    Click ‘Finish’

    Let Oracle eat…

    …and eat

    …and eat

    Yum!

    Database Config Complete, Click ‘OK’

    Follow Oracle’s provided instructions, Click ‘OK’

    Click ‘Finish’

 

Now we will create a startup script:

sudo su – 

vi /etc/init.d/oracledb

Cut and paste below code into /etc/init.d/oracledb file (change ORACLE_HOME if needed)

#!/bin/bash
#
# /etc/init.d/oracledb
#
# Run-level Startup script for the Oracle Listener and Instances
# It relies on the information on /etc/oratab

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/ora11gdb
export ORACLE_OWNR=oracle
export PATH=$PATH:$ORACLE_HOME/bin

if [ ! -f $ORACLE_HOME/bin/dbstart -o ! -d $ORACLE_HOME ]
then
echo “Oracle startup: cannot start”
exit 1
fi

case “$1” in
start)
# Oracle listener and instance startup
echo -n “Starting Oracle: “
su $ORACLE_OWNR -c “$ORACLE_HOME/bin/lsnrctl start”
su $ORACLE_OWNR -c “$ORACLE_HOME/bin/dbstart $ORACLE_HOME”
touch /var/lock/oracle
echo “Oracle DB Startup OK”
;;
stop)
# Oracle listener and instance shutdown
echo -n “Shutdown Oracle: “
su $ORACLE_OWNR -c “$ORACLE_HOME/bin/lsnrctl stop”
su $ORACLE_OWNR -c “$ORACLE_HOME/bin/dbshut $ORACLE_HOME”
rm -f /var/lock/oracle
echo “Oracle DB Shutdown OK”
;;
reload|restart)
$0 stop
$0 start
;;
*)
echo “Usage: `basename $0` start|stop|restart|reload”
exit 1
esac

exit 0

 

Make startup script executable and tell the OS we want it started at boot time:

chmod a+x /etc/init.d/oracledb
update-rc.d oracledb defaults 99

Example Output
update-rc.d: warning: /etc/init.d/oracledb missing LSB style header
Adding system startup for /etc/init.d/oracledb …
/etc/rc0.d/K99oracledb -> ../init.d/oracledb
/etc/rc1.d/K99oracledb -> ../init.d/oracledb
/etc/rc6.d/K99oracledb -> ../init.d/oracledb
/etc/rc2.d/S99oracledb -> ../init.d/oracledb
/etc/rc3.d/S99oracledb -> ../init.d/oracledb
/etc/rc4.d/S99oracledb -> ../init.d/oracledb
/etc/rc5.d/S99oracledb -> ../init.d/oracledb

Adjust Oracle’s Profile

Add oracle SID to profile as default value

sudo su – oracle

echo “#”>>/home/oracle/.profile
echo “# Set Oracle 11g default entries:”>>/home/oracle/.profile
echo “export ORACLE_SID=ora11g01”>>/home/oracle/.profile
echo “. oraenv”>>/home/oracle/.profile

. oraenv

Test the new Database:

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Aug 27 22:42:15 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select sysdate from dual;

SYSDATE
———
27-AUG-11

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 816054272 bytes
Fixed Size 1339600 bytes
Variable Size 499126064 bytes
Database Buffers 310378496 bytes
Redo Buffers 5210112 bytes

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> select sysdate from dual;

SYSDATE
———
27-AUG-11

SQL> create table sys.delete_me (a number);

Table created.

SQL> insert into sys.delete_me values(25);

1 row created.

SQL> insert into sys.delete_me values(94);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from sys.delete_me;

A
———-
25
94

SQL> drop table sys.delete_me;

Table dropped.

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now we make it start on boot:

vi /etc/oratab

#
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ‘:’, is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, ‘#’, are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , “Y”, or should not,
# “N”, be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
ora11g01:/u01/app/oracle/product/11.1.0/ora11gdb:N

Change the N at the end of the line to Y

ora11g01:/u01/app/oracle/product/11.1.0/ora11gdb:Y

Execute startup script:

/etc/init.d/oracledb stop

Shutdown Oracle:
LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 27-AUG-2011 22:51:21

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
Processing Database instance “ora11g01”: log file /u01/app/oracle/product/11.2.0/ora11gdb/shutdown.log
Oracle Shutdown OK

/etc/init.d/oracledb start

Starting Oracle:
LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 27-AUG-2011 22:51:32

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Starting /u01/app/oracle/product/11.2.0/ora11gdb/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 11.2.0.1.0 – Production
System parameter file is /u01/app/oracle/product/11.2.0/ora11gdb/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/grant-laptop/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 – Production
Start Date 27-AUG-2011 22:51:32
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/ora11gdb/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/grant-laptop/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
The listener supports no services
The command completed successfully
Processing Database instance “ora11g01”: log file /u01/app/oracle/product/11.2.0/ora11gdb/startup.log
Oracle Startup OK

Now after every reboot the database will automatically be started
Also, don’t forget to check out:     https://localhost:1158/em