#!/usr/bin/ksh
#***********************************************************************
#
# NAME: tcDBcfg (familyName)
#
# PURPOSE:
#
#    The purpose of this script is to provide a method of collecting
#    useful information of any TeamConnection environment.  This is
#    including (but not limited to) information relating to the
#    operating system, database installation, TeamConnection database
#    and configuration.	 The information gathered by this script can
#    be passed onto technical support to aid in problem determination.
#
# EXAMPLE USAGE:
#
#	tcDBcfg $LOGNAME  2>&1	|  tee tcdbconfig.out
#
# ENVIRONMENT VARIABLES:
#	TC_FAMILY
#
# DESCRIPTION:
#    This command displays the db2level, tclevel, all db2 variables and
#    all tc_ variables, the DB2 "admin", "dbm", and
#    "db" configurations, as well as info for bufferpools, tablespaces,
#    TCPERF% indexes, and current statistics.
#
# NOTE:
# * It must be run from the VA TC family user id.
#
# **********************************************************************
# *
# *	      NOTICE TO USERS OF THE SOURCE CODE EXAMPLES
# *
# * INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THE SOURCE
# * CODE EXAMPLES, BOTH INDIVIDUALLY AND AS ONE OR MORE GROUPS, "AS IS"
# * WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING,
# * BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
# * FITNESS FOR A PARTICULAR PURPOSE.  THE ENTIRE RISK AS TO THE QUALITY
# * AND PERFORMANCE OF THE SOURCE CODE EXAMPLES, BOTH INDIVIDUALLY AND AS
# * ONE OR MORE GROUPS, IS WITH YOU.  SHOULD ANY PART OF THE SOURCE CODE
# * EXAMPLES PROVE DEFECTIVE, YOU (AND NOT IBM OR AN AUTHORIZED DEALER)
# * ASSUME THE ENTIRE COST OF ALL NECESSARY SERVICING, REPAIR OR
# * CORRECTION.
# *
# **********************************************************************
#***********************************************************************
## display command line
print -u2 "# ${0} ${*}"

## check for valid arguments
## if family name is passed, use it
## if no family name is passed, check for TC_FAMILY and use it
## if no TC_FAMILY and no family name passed, put out usage message

if [[ ${#} < 1 ]]
then
  unset tcfamily
  if [[ -n ${TC_FAMILY} ]] ; then
    tcfamily=$(print ${TC_FAMILY} | sed 's/@.*$//')
    tcfamily=${TC_FAMILY}
  else
    print -u2 ' '
    print -u2 '	 Usage: tcDBcfg tcFamily'
    print -u2 ' '
    print -u2 '	   Where tcFamily is the family DB2 database name.'
    print -u2 ' '
    print -u2 '	 This command displays the db2level, tclevel, all db2 variables and '
    print -u2 '	 all tc_ variables, the DB2 "admin", "dbm", and'
    print -u2 '	 "db" configurations, as well as info for bufferpools, tablespaces,'
    print -u2 '	 TCPERF% indexes, and current statistics.'
    print -u2 ' '
    print -u2 '	 Note:	This command must be run using the TeamConnection family user ID.'
    print -u2 ' '
    return 1
  fi
else
tcfamily=$1
fi

  print '+-------------------------------------------------------------+'
  print '|							       |'
  print '| VisualAge TeamConnection and DB2 Configuration information  |'
  print '|							       |'
  print '+-------------------------------------------------------------+'
  print ' '
  print 'Configuration date and time'
    date
  print ' '
  print 'tcDBcfg -------------------------------- TeamConnection version'
  print ' '
    tclevel
  print ' '

  print 'tcDBcfg ----------------------- Connecting to tcFamily database'
  print ' '
    db2 connect to ${tcfamily}
  print ' '

  print 'tcDBcfg --------------------------- Version of created database'
  print ' '
    db2 "select lastSerial,name from FHCSequence where name='TCVERSION'"
  print ' '

  print 'tcDBcfg ------------------ TeamConnection environment variables'
  print ' '
    env | grep -i tc_
  print ' '

  print 'tcDBcfg -------------------------- Operating system and version'
  print ' '
    uname -a
    oslevel
  print ' '

  print 'tcDBcfg -------------------------- Total usable physical memory'
  print ' '
  lsattr -E -l sys0 | grep realmem
  print ' '

  print 'tcDBcfg ---------------------- Total space including swap space'
  print ' '
  df -k
  lsps -a
  print ' '

  print 'tcDBcfg ------------------------ Show current running processes'
  print ' '
  ps -ef | sort
  print ' '

  print 'tcDBcfg --------------------------------------------- User info'
  print ' '
    env | grep -i user
  print ' '

  print 'tcDBcfg ------------------------------------------- DB2 version'
  print ' '
    db2level
  print ' '

  print 'tcDBcfg -------------------------------- DB2 registry variables'
  print ' '
    db2set -all
  print ' '

  print 'tcDBcfg ----------------------------- DB2 environment variables'
  print ' '
    env | grep -i db2
  print ' '

  print 'tcDBcfg ------------------------------- DB2 admin configuration'
  print ' '
    db2 get admin cfg
  print ' '

  print 'tcDBcfg -------------------- DB2 database manager configuration'
  print ' '
    db2 get dbm cfg
  print ' '

  print 'tcDBcfg --------------------- DB2 family database configuration'
  print ' '
    db2 get db cfg for ${tcfamily}
  print ' '

  print 'tcDBcfg --------------------------------------- Bufferpool info'
  print ' '
    db2 "select * from syscat.bufferpools"
  print ' '

  print 'tcDBcfg --------------------------------------- Tablespace info'
  print ' '
    db2 "select definer, tbspace, tbspacetype as type, tbspaceid as id, bufferpoolid as pool, datatype, extentsize, prefetchsize, pagesize, overhead, transferrate from syscat.tablespaces"
  print ' '

  print 'tcDBcfg ---------------------------- Last table statistics time'
  print ' '
    db2 "select tabschema, tabname, stats_time, card from syscat.tables where type='T' and tabschema=(select tabschema from syscat.tables where tabname='FHCFILE') order by tabname"
  print ' '

  print 'tcDBcfg ---------------------------- Last index statistics time'
  print ' '
    db2 "select tabname, indname, stats_time, unique_colcount, colnames from syscat.indexes where tabschema=(select tabschema from syscat.tables where tabname='FHCFILE') order by tabname, indname"
  print ' '

  print 'tcDBcfg -------------------------------- Last package bind time'
  print ' '
    db2 "select pkgschema, pkgname, valid, last_bind_time from syscat.packages where pkgschema=(select pkgschema from syscat.packages where pkgname='TCMOD') order by pkgname"
  print ' '

  print 'tcDBcfg ------------------------------ DB2 reorganization check'
  print ' '
    db2 "reorgchk current statistics on table user"
  print ' '

#  print 'tcDBcfg ---------------------------- TC release characteristics'
#  print ' '
#    db2 "select name, autoprune, lastUpDate from fhcRelease"
#  print ' '

  print 'tcDBcfg ------------------------------------- TC audit.log tail'
  print ' '
    ls -al $TC_DBPATH/audit.log
  print ' '
    tail $TC_DBPATH/audit.log
#    grep -i driverCommit $TC_DBPATH/audit.log
#    grep -i memberCreate $TC_DBPATH/audit.log
  print ' '

  print 'tcDBcfg -------------- Disconnecting from the tcFamily database'
  print ' '
    db2 connect reset
  print ' '

# This stuff needs to be last, to avoid problems with the piping and tee

  print 'tcDBcfg ------------------------- TeamConnection user exit info'
  print ' '
    grep -v "^#" $TC_DBPATH/config/userExit
  print ' '
  print 'tcDBcfg ---------------------------------------- End of tcDBcfg'

exit 0

