Rishi2Monk has asked for the wisdom of the Perl Monks concerning the following question:

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";

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

Replies are listed 'Best First'.
Re: How to collect output of DB2 query run interactive mode using perl script
by Corion (Patriarch) on Aug 01, 2022 at 18:42 UTC

    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

    [download]

    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;

    [download]

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

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

    [download]
[reply]
[d/l]
[select]

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

[reply]
Re: How to collect output of DB2 query run interactive mode using perl script
by Rishi2Monk (Novice) on Aug 01, 2022 at 18:47 UTC

    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;
}

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom