(BIRT) Passing Multiple values in report parameter to a data set

BIRT has finally added a much awaited feature of been able to select multiple values in a report parameter.

However, I have not been able to find a way to use a report parameter that makes use of the "List Box" / "Allow Multiple Values" option in a SQL Query as a bind parameter.

Fortunately, it seems easy enough to use the "multiple value parameter" in the "Filters" section of the data set.
You can use the IN operator and build an expression to reference the report parameter.

Now if only there was an easy way to have multiple values selected by default. Oh well.. I guess there is always another release another day.

Postgresql's inadequate COPY comand

Who else is bugged by PostgreSQL's COPY command? This thing is finicky as hell and it lacks any advanced options available in most DBMS import utilities.

One thing that really bugs me is the utilities inability to accept missing data for fields at the end of the file as NULLs. Ideally, any decent utility would assume NULLs and import the data ( like mysql ) or provide an option to allow NULLs in trailing columns( like Oracle ). Bummer...PostgreSQL does neither. You are stuck writing pre-import utilities in perl/sed/tr/awk..etc to cleanup the data and provide delimiters before your provide it to PostgreSQL COPY. In the end, more time needed to load data in PostgreSQL than any other DBMS.

Text file exports from Oracle

The Oracle Database engine is probably one of the most robust DBMS systems available commericially or otherwise with a rich set of features. However , one area that is poorly addressed in the core database offering is data exports. I know , I know ...the native export tools like exp and datapump offer runtime options and speed unmatched in any system I have see so far .

But..how about exporting table data to text files ? I like to export my production database tables to text files for point in time snapshots ( usually at the end of business every night ). I like to load these tables into mysql and postgresql databases ( and sometimes grudgingly into M$SQL :-) for off line reporting or web applications. Unfortunately, Oracle does not bundle any utility to dump to text files in any efficient manner. There are some enterprising individuals that have written sqlplus scripts to accomplish this, but this method is not efficient ( low on speed and high on system resources used ). Why would Oracle not provide an utility export to text files that I can import into databases it competes with??.....hmmm.. enough said.

Fortunately, there is a solution on the market that does offer the functionality I need. Fastreader ( from WishdomForce ) is available for multiple OS platforms. Its only purpose is to offload data from Oracle and it does that very well.

Fastreader uses a Java GUI for setup . Setup consists of selecting the source Oracle database connection, destination settings, table selection and some performance based settings. You can choose to run the data offload from the GUI or run it from the command line like I do. I have a cron job that runs Fastreader every night. The setup is quite painless and I will literally done in 5 mins.

The offload process is very fast and has a very low overhead. Fastreader uses information in the Oracle data dictionary to direcly access data from oracle datafiles. This virtually eliminates any load on the database buffers and oracle processes. There is obviously a spike in IO but that is inevitable.

When I fist ran this tool, I could not believe how fast it was . Some performance numbers from a Itanium2/Redhat/Oracle 10gR2 ( 2 CPUS/8GB Ram ) system:
14 Million rows ( 11GB ) in 7 mins.
Half a Million rows ( 1GB ) in 4 seconds

You may see better or worse based on the system configuration.

Th guys at WishdomForce are really smart and friendly. When I first contacted them, they did not have a build for my platform yet ( Red Hat Itanium ) . I was surprised when they got back to me with a trail version for Itanium in a week. I am looking forward to evaluating and using they latest data export tool ( Data Sync ) . This promises to offload incremetal data from Oracle to other DBMS platforms in near realtime.

Setting up an SFTP chroot jail using rssh

Setting up a CHROOT JAIL for SFTP has been a convoluted task without the use of RSSH. Typically , you are required to recompile SSH ( ugg! ).
With the use of RSSH ( http://www.pizzashack.org/rssh/ ), the setup is non intrusive and fairly painless. However there are still a few quirks that need to be addressed before it would work. I will describe the steps to setup a CHROOT jail on Red Hat ES4 .

Download, compile and install RSSH source from http://www.pizzashack.org/rssh/ .

1) Make it easy on yourself and select /usr as the install prefix. There are other tools in the distribution ( which you will use further in ) that are hard coded to /usr as the install prefix.
./configure --prefix=/usr
make
install

2) Setup CHROOT JAIL. The tool mkchroot.sh makes setting this quite easy. The concepts behind the tool is described in great detail in the documentation ( same directory as source ).

/mkchroot.sh /my/chroot/jail

I used :
[root]# /opt/SWINSTALL/rrsh/rssh-2.3.2/mkchroot.sh /alcatraz
NOT changing owner of root jail.
NOT changing perms of root jail.
setting up /alcatraz/usr/bin
setting up /alcatraz/usr/libexec/openssh
setting up /alcatraz/usr/libexec
Copying libraries for /usr/bin/scp.
/lib/libcrypto.so.4
/lib/libutil.so.1
/usr/lib/libz.so.1
/lib/libnsl.so.1
................
................
................
lib/libnss1_files-2.3.4.so
lib/libnss1_files.so.1
Setting up /etc in the chroot jail
cp: omitting directory `/etc/ld.so.conf.d'
Chroot jail configuration completed.

NOTE: if you are not using the passwd file for authentication,
you may need to copy some of the /lib/libnss_* files into the jail.

NOTE: you must MANUALLY edit your syslog rc script to start syslogd
with appropriate options to log to /alcatraz/dev/log. In most cases,
you will need to start syslog as:

/sbin/syslogd -a /alcatraz/dev/log

NOTE: we make no guarantee that ANY of this will work for you... if it
doesn't, you're on your own. Sorry!


3) Its is recommended that a dedicated group be created for all the users that will be jailed. So we will
create the group and add a new user to it. Also, it is recommended that the user's home be created under the
chroot jail you just created.

[root]# mkdir /alcatraz/home
[root]# groupadd jailedgroup
[root]# adduser -c 'User is in chroot jail' -d /alcatraz/home/convict -g jailedgroup -s /usr/bin/rssh convict
[root]# passwd convict

4) Configure the rssh options ( /usr/etc/rssh.conf ). To understand all the available options, do a man rssh.conf.
I am interested in only allowing sftp for my user "convict". Some relevent lines

allowsftp
chrootpath = /alcatraz
user=convict:077:00010:/alcatraz

The access bits 00010 indicates that the user is allowed sftp access only( refer to man pages ).


Your chroot jail is ready and you are ready to let the convict in ;-).

*******I was running in a "Connection closed" problem when I tried to connect in.


$ sftp convict@mysftpserver
Connecting to mysftpserver...
convict@mysftpserver's password:
Connection closed

On google of this problem, I saw a few different issues/solutions. Copying all of /lib into the jail seems to fix it.
[root]# cp /lib/* /alcatraz/lib

Record your command line session

The commands you execute on the command line and their outputs are very helpful to debug problems. You might even want to record your sessions as part of an audit policy.

Linux and Unix systems make it very easy using the script command:

script [-a] [-c COMMAND] [-f] [-q] [-t] [file]

DESCRIPTION
Script makes a typescript of everything printed on your terminal. It is useful for students who need a hardcopy record of an interactive session as proof of an
assignment, as the typescript file can be printed out later with lpr(1).

If the argument file is given, script saves all dialogue in file. If no file name is given, the typescript is saved in the file typescript.

Options:

-a Append the output to file or typescript, retaining the prior contents.

-c COMMAND
Run the COMMAND rather than an interactive shell. This makes it easy for a script to capture the output of a program that behaves differently when its std-
out is not a tty.

-f Flush output after each write. This is nice for telecooperation: One person does âmkfifo foo; script -f fooâ and another can supervise real-time what is
being done using âcat fooâ.

-q Be quiet.

-t Output timeing data to standard error. This data contains two fields, separated by a space. The first field indicates how much time elapsed since the previ-
ous output. The second field indicates how many characters were output this time. This information can be used to replay typescripts with realistic typing
and output delays.

Howto use the "screen" utility to keep commands running even when you logout

#screen
#command to run in background
CTRL-a DD ( detach and logout)

To reattach to an existing detached session
#screen -R

An excellent article comparing various RDMS engines

http://troels.arvin.dk/db/rdbms/