How to collect output of DB2 query run interactive mode using perl script

[Rishi2Monk]

TiTle: How to collect output of DB2 query run interactive mode using perl script

I need to write a perl script which will run the DB2 query (describe "select * from ") in interactive mode and collect the output of it to get the colname, typename and length from it. Here is what I have tried so far. My main goal is to collect the colname, typename and length from the query 'describe "select * from indic.name"'. Kindly let me know if any other efficient alternative way is there. I can not use DBI module as it was not installed.

use strict;
use warnings;

my @cmd_list = ( 'db2', 'connect to ctinld01 user db2tf0 using db2tf0'
+, 'describe select * from indic.name');

$|++;  # turn off buffering

my $shpid = open( my $sh, '|-', '/bin/bash' )
    or die "Can't open a shell process: $!\n";

for my $cmd ( @cmd_list ) {
    if($cmd eq "describe select * from indic.name")
    {
        open (STDOUT, "| tee -ai logs.txt");
        print $sh "$cmd\n";
        print "It Works!";
        close STDOUT;
    }
    else
    {
        print $sh "$cmd\n";
    }
}
print $sh "exit\n";
close $sh;
waitpid( $shpid, 0 );

print "Shell's all done.  Moving right along...\n";

Comment on How to collect output of DB2 query run interactive mode using perl script Download Code

[Corion]

If you want the information about the column , like its type etc., don't do it by running an external program, but do it by using the catalog methods. What you want is basically:

use DBI;
my $dbh = DBI->connect('dbi:DB2', 'db2tf0' );
my $sth_columns = $dbh->column_info(undef, undef, 'indic.name', undef)
+;
# print out the results

If you really cannot use DBI and your system administrator won't install it, maybe you can simply pipe text into the db2 shell instead of trying to run things interactively:

my @results = qx(echo 'connect to ctinld01 user db2tf0 using db2tf0\nd
+escribe select * from indic.name' | db2 );
print "<$_>" for @results;

According to the db2 manual, you can simply pass the SQL statement(s) to the db2 tool:

db2 "connect ..." "select ..."

[Rishi2Monk]

Thanks for your quick reply. I will check both the ways and update here.

[Rishi2Monk]

Below is the code which I am referring to. Implemented for Informix DB with dbaccess interactive mode. I need to implement similar logic for DB2.

sub get_colinfo
{
    my $table = shift;

    open TINFO, "-|", "echo \"info columns for $table\" | iSQL 2>&1" o
+r die "foo $?";

    my @colinfo = ();

    while (<TINFO>)
    {
        if (/^SQL/)
        {
            # oops, got an error
            print STDERR $_;
            while (<TINFO>)
            {
                print STDERR $_;
            }
            last;
        }
        my @info = split(/\|/, $_);
        my $colname = $info[1];
        my $ntype = $info[2];
        my $collen = $info[3];

        $colinfo[0] = $colname;
        $colinfo[1] = $ntype;
        $colinfo[2] = $collen;
    }
    return \@colinfo;
}