#!/usr/bin/perl ######################################################################## # (c) Copyright IBM Corp. 2007 All rights reserved. # # The following sample of source code ("Sample") is owned by International # Business Machines Corporation or one of its subsidiaries ("IBM") and is # copyrighted and licensed, not sold. You may use, copy, modify, and # distribute the Sample in any form without payment to IBM, for the purpose of # assisting you in the development of your applications. # # The Sample code is provided to you on an "AS IS" basis, without warranty of # any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR # IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF # MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do # not allow for the exclusion or limitation of implied warranties, so the above # limitations or exclusions may not apply to you. IBM shall not be liable for # any damages you suffer as a result of using, copying, modifying or # distributing the Sample, even if IBM has been advised of the possibility of # such damages. ######################################################################### # # SOURCE FILE NAME: tbinfo.pl # # SAMPLE: How to get information about a table # # SQL STATEMENTS USED: # SELECT # # ########################################################################## # # For more information on the sample programs, see the README file. # # For information on developing Perl applications, see the Application # Development Guide. # # For information on using SQL statements, see the SQL Reference. # # For the latest information on programming, building, and running DB2 # applications, visit the DB2 Information Center: # http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp ########################################################################### select STDERR; $|=1; select STDOUT; $|=1; use strict; use warnings; use DBI; # access the module for DB2 Sample Utility functions use DB2SampUtil; # check and parse the command line arguments # call the subroutine CmdLineArgChk from DB2SampUtil.pm my ($database, $user, $password) = CmdLineArgChk(@ARGV); # declare return code, statement handler, database handler and local variable my ($rc, $sth, $dbh, $colname, $typename, $length, $scale, $tableName); print "THIS SAMPLE SHOWS HOW TO GET INFORMATION ABOUT A TABLE.\n"; # connect to the database print "\n Connecting to database...\n"; $dbh = DBI->connect($database, $user, $password) || die "Can't connect to $database: $DBI::errstr"; print "\n Connected to database.\n"; $tableName = 'STAFF'; # tableName can be assigned name of any table about # which information is to be obtained print "------------------------------------------------------------------"; # get the table schema name $rc = GetSchemaName(); if($rc != 0) { die "\nError: Getting the table schema name failed\n"; } print "------------------------------------------------------------------"; # get info for table columns $rc = GetColumnInfo(); if($rc != 0) { die "\nError: Getting the column information for the table failed\n"; } # disconnect from the database print "\n Disconnecting from database."; $dbh->disconnect || die "Can't disconnect from database: $DBI::errstr"; print "\n Disconnected from database.\n"; ########################################################################## # Description: How to get the schema name for a table # Input : None # Output : Returns 0 on success, exits otherwise. ########################################################################## sub GetSchemaName { my $sql; print "\nUSE THE SQL STATEMENT:"; print "\n SELECT INTO "; print "\nTO GET A TABLE SCHEMA NAME \n"; print "\n Execute the statement\n"; print " SELECT tabschema \n"; print " FROM syscat.tables \n"; print " WHERE tabname = \'\$tableName\' \n"; print " for\n"; print " tableName = '$tableName'.\n"; $sql = qq(SELECT tabschema FROM syscat.tables WHERE tabname = '$tableName'); # prepare and execute the SQL statement # call the subroutine PrepareExecuteSql() from DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # fetch the schema name into a variable for display my $tabschema = $sth->fetchrow(); print "\n Table Schema name is: $tabschema\n"; # no more data to be fetched from statement handle $sth->finish; return 0; } # GetSchemaName ########################################################################## # Description: How to get the column information for a table # Input : None # Output : Returns 0 on success, exits otherwise. ########################################################################## sub GetColumnInfo { my $sql; print "\nTO GET TABLE COLUMN INFORMATION.\n"; print "\n Get info for '$tableName' table columns:\n"; print " column name data type data size\n"; print " -------------------- -------------- ----------\n"; $sql = qq(SELECT colname, typename, length, scale FROM syscat.columns WHERE tabname='$tableName'); # prepare and execute the SQL statement # call the subroutine PrepareExecuteSql() from DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # fetch each row to display the column information for table 'STAFF' while(($colname, $typename, $length, $scale) = $sth->fetchrow()) { printf(" %-20s %-14s %s", $colname, $typename, $length); if($scale != 0) { print ", $scale"; } print "\n"; } # no more data to be fetched from statement handle $sth->finish; return 0; } # GetColumnInfo