dbis.pm

Perl开发的数据采集工具 基于UNIX

package DBIs;
my $DBIs_VERSION 	= "DBIs v2.0.0.021206(Released)";
my $MPL_VERSION		= "MPL  v2.0.0.0000(Released)";
my $MAP_VERSION		= "MAP  v2.0.0.0000(Released)";
#----------------------------------------------------------------------------
# @(#) Perl Module: DBIs
# Copyright(C) 2001-2003 BOCO Inter-Telecom, All Rights Reserved
#
# Description: This perl program includes Data Collector subroutines.                                                       
# DREATED BY:  Haiqing Wang                                                  
# MODIFY TIME: 2003-03-03 22:00:00
# CREAYEDTIME: 2001-07-02                        
#----------------------------------------------------------------------------
# Subroutines:
#----------------------------------------------------------------------------
# Set_DataCheck
# Write_Dchk_Err
# Set_PwdEncrypt
# Set_WriteOlog
# Set_MsgSend
# Set_MSName
# Set_MQName
# Set_BcpMode
# Set_BcpWnmsDatabase
# Set_NoBcp
# Set_Debug
# Sort_By_Number
# Set_Database_Type
# Set_OSSDB_Type
# Init_Qetxt_Files
# GenerateKey
# Encode
# Pwd_Encrypt
# Decode
# Pwd_Decrypt
# Trim
# DFtime
# GMtime
# FMtime
# CFtime
# Set_Trace_Modes
# Set_Mpl_Version
# Init_Trace
# Trace
# End_Trace
# Connect_DB
# Disonnect_DB
# Commit_DB
# Rollback_DB
# Exec_Sql
# Set_Isdirty_Read
# Find_Bcp_Oldtab
# Bcp
# Exec_Finish
# OnError
# SetError
# Write_OlogDB
# Create_Tmptable_Name
# Get_Column_List
# Debug
# Get_DBMS
# Set_DBMS
# Get_ODBMS
# Set_ODBMS
# Get_DBMS_From_File
# Get_ObjClass_by_NeName
# Read_IniFile
# Init_Variable
# Read_MapIniFile
# Hex2spc
# Int2spc
# Bin2spc
# Int2bin
# Hex2bin
# Bin2int
# Int2hex
# Hex2int
# Sql_Info
# Tab_Info
# Replace_SQL_Standard_Type
# Replace_SQL_Standard2Database_Type
# Net_Ftp_Connect
# Net_Ftp_Disconnect
# Net_Ftp_Get
# Net_Ftp_Put
# Net_Telnet_Connect
# Net_Telnet_Disconnect
# Net_Telnet_Cmd
# Touch_Dir
# Send_Msg
# MQSend
# MQReceive
# FmtMsg
#-----------------------------------------------------------------------
#Modified 2002-04-11(v2.0.0410.01):
#	1.Bcp change to use bind.
#Modified 2002-04-24(v2.0.0410.02):
#	1.And function Int2bin
#Modified 2002-04-27(v2.0.0410.03):
#	1.And Debug of Set_DBMS Trace
#Modified v2.0.0430:
#	1.modify verion of mpl,DBIs and map.
#Modified v2.0.0505:
#	1.add FMtime.
#	2.and GMtime.
#Modified v2.0.0506:
#	1.add Pwd_Encrypt.
#	2.and Pwd_Decrypt.
#	3.and Int2hex.
#	4.pwd encrypt.
#Modified v2.0.0507:
#	1.add DFtime.
#Modified v2.0.0508:
#	1.TempTable add process id.
#Modified v2.0.0516:
#	1.Connect_DB with parameter auto commit.
#Modified v2.0.0518:
#	1.Add Sql Standard type support.
#	2.Add Replace_SQL_Standard_Type().
#	3.Modify Exec_sql() sql statement with Replace_SQL_Standard_Type;
#Modified v2.0.0524:
#	1.use strict
#	2.and Net:FTP
#	2.and Net:TELNET
#Modified v2.0.0601:
#	1.Bcp add print standant type
#	2.oracle number type suport
#Modified v2.0.0.602:
#	1.Pwd encrypt/decrypt update.
#Modified v2.0.0.604:
#	1.Add MQsend/MQreceive
#Modified v2.0.0.609:
#	1.Bcp with buffer flag
#Modified v2.0.0.620:
#	1.Bcp Data type sql_varchar 255 limited
#Modified v2.0.0.0704:
#	1.fix the bug of Bcp Data type SQL_NUMBER(type =2) is float or integer
#Modified v2.0.0.0709:
#	1.add v2.1 msg ctrl
#	2.fix the bug of debug info wrong.
#Modified v2.0.0.0712:
#	1.insert tad_instance_err
#Modified v2.0.0.0715:
#	1.fix the bug of Bcp prepare twice.
#       2.Bcp with counter statement if select sql statement
#	3.Bcp with commit each 1000 rows if oracle database
#	4.BcpBuffer delfault 0
#	5.Msg body add '<![CDATA[string value]]>'
#Modified v2.0.0.0719:
#	1.fix the bug of bcp counter error because of group/order/having
#Modified v2.0.0.0722:
#	1.add bcp mode load but it can not use.
#	2.add attribute set isolation dirty to read
#Modified v2.0.0.0724:
#	1.Bcp add serial id.
#	2.change bcp counter use fetchrow all.
#	3.list all sub function
#Modified v2.0.0.0801:
#	1.modify Set_Isdirty_Read and db_type par.
#	2.Modify Bcp Set_Isdirty_Read
#	3.add Set_OSSDB_Type
#Modified v2.0.0.0814:
#	1.fix the bug of sql type -1 longvarchar without length limited
#Modified v2.0.0.0816:
#	1.Add Bcp dbload(informix)
#Modified v2.0.0.0817:
#	1.Add BcpMode set function
#	2.Add BcpMode from local database
#Modified v2.0.0.0819:
#	1.fix the bug of Bcp with bind because of id not add.
#	2.Modify all trace mode.
#Modified v2.0.0.0820:
#	1.Add set lock mode to wait.
#Modified v2.0.0.0823:
#	1.Modify bcp error ignore it.
#Modified v2.0.0.0904:
#	1.Modify create temptable TMP + Mhhmmss + 2 bit proc id + 3 bit serial
#	2.fix the bug of CFTime D => 'D '.
#Modified v2.0.0.1014:
#	1.add code in OnError for locating error
#	2.log file copied to $DAL_HOME/trace/ERROR_LOG when any errors occur
#Modified v2.0.0.021126:
#	1.Modify Bcp function return bcp num
#Modified v2.0.0.021205:
#	1.Add data collect check error.func:Set_DataCheck,Write_Dchk_Err
#Modified v2.0.0.021206:
#	1.Modified msg format for TManager Tools
#----------------------------------------------------------------------------
# Modified for GP-NPM projects By Zhang Jingjing
# Last Update : 2003-03-05
# 
# New Variable: 
#		$CONFIG_IN_LDAP;
#		$MESSAGE_QUEUE_ID;
#		$TPM_TABLE_NAME;
#		$DAL_LOG_HASH;
#		$MDDB_DSN;
# New Function: 
#		NPM_Set_LDAP_Flag
#		NPM_Set_MSGQueue_ID
#		NPM_Set_TPMTable_Name
#		NPM_Get_DBMS_From_LDAP
#		NPM_Send_Message
#		NPM_Write_DalLog
#		NPM_Set_DalLog
#		NPM_Set_MDDB_DSN
#		NPM_Bak_Insert
#		NPM_Bak_Bcp
# Modified Function: 
# Add code block to send message to message queue specified by $MESSAGE_QUEUE_ID
# and calling of NPM_Write_DalLog
#		Set_DBMS
#		Exec_Sql
#		Connect_DB
#		Disonnect_DB
#		Commit_DB
#		OnError
#		Bcp
#----------------------------------------------------------------------------
use Env 	qw(HOME);
use Env 	qw(DAL_HOME);
use Env 	qw(INFORMIXDIR);
use lib 	"$DAL_HOME/NPM/common/modules";
use DBI;
use IO;
use Time::Local;
use Net::FTP;
use Net::Telnet;
use FileHandle;
#use BocoMQ;
use LDAP_API;
use Data::Dumper;
#use XML::Simple;
#use strict;

if (!$DAL_HOME)
{
	$DAL_HOME = $HOME;
}

#Global variables
my $PROG_NAME;
my $OMC_ID;
my $PM_DCHK = 0;
my $PM_DCHK_STIME;
my $PM_DCHK_ETIME;
my $PM_DCHK_PNAME;
my $COOKDB_TYPE = "INFORMIX";
my $OSSDB_TYPE  = "UNKNOWN";
my $SQL_TYPE_INFO;
my ($conn_dbn,$conn_dbd,$conn_uid,$conn_pwd,$conn_mark,$net_telnet);
my ($olog_dbd,$olog_dbn,$olog_uid,$olog_pwd,$net_ftp);
my $MapiFile 		= $DAL_HOME."/.mapi.ini";
my $Lock2WaitSec	= 60;
my $TraceFileName 	= "";
my $RepFileName 	= "";
my $TraceModes 		= "SRC";
my $TmpTabTimes 	= 0;
my $DeBug		= 0;
my $PwdEncrypt		= 0;
my $BcpBuffer		= 0;
my $BcpMode		= 1;			#0: SQL Statement  1: Bind  
						#2: Load(dbaccess load for informix/sqlload for oracle)
						#3: Dabase Link (from local db to local db)
my $Wnms_Database	= 'mddb';		#$BcpMode = 2 then use it
my $Data_Load_TmpDir	= '';			#$BcpMode = 2 then use it
my $Del_Bcpfile		= 1;			#$BcpMode = 2 then use it

my $Bcptab_Serial	= 0;			#Bcp Table Counter
my $Use_Bcp_Oldtab	= 0;			#0: Not use  1: Use
my $Bcp_Oldtab_Logfile;				#When use bcp old tab,use The log information
my $ErrorRollBack 	= 1;
my $ErrorCommit 	= 0;
my $ErrorExit 		= 1;
my $ErrorDebug 		= 0;
my $NetTimeout		= 60*30;
my @char2hextab 	= ("0", "1", "2", "3", "4", "5", "6", "7", "8", "9","A", "B", "C", "D", "E", "F");
my $net_prompt 		= '_funkyPrompt_';
my $prekey 		= "!#&<>_|~";

#-------------------------------------------------------------------------------
my $CONFIG_IN_LDAP	= 0;
my $NPM_WriteDaLlog_mod	= 1;
my $MESSAGE_QUEUE_ID;
my $TPM_TABLE_NAME;
my $DAL_LOG_HASH;
my $MDDB_DSN;
my $Message_Text;
my $NPM_MESSAGE		=
{
	200     => "Unit NOT found in LDAP",
        201     => "Incorrect database connection information",
        202     => "Can not connect database",
        203     => "Failed when write trace file",
        204     => "Failed when create trace directory",
        210     => "Error when SQL statement prepare",
        211     => "Error when SQL pararmeter bind",
        212     => "Error when SQL executed",
        250	=> "Error when SQL do"
};
#-------------------------------------------------------------------------------

#-------------------------------------------------------------------------------
my $WriteOlog		= 0;
my $MsgSend		= 0;
my $MSName		= "DEFAULT_QM";
my $MQName		= "CTRL.Q";
my $DMMMT_DCMSG;
my $DMMMT_DCMSG_TASK_ID	= 1;
my $DMMMT_DCMSG_INSTANCE_ID;
my $DMMMT_DCMSG_INSTANCE_RDN;
my $DMMMT_DCMSG_INSTANCE_START;
my $DMMMT_DCMSG_TIMESTAMP;
my $DMMMT_DCMSG_TYPE		= 1280;		#_T("DC的消息")
my $DMMMT_DCMSG_ST_INSSTART 	= 1; 		#Instance start
my $DMMMT_DCMSG_ST_INSSTOP  	= 2; 		#Instance stoped
my $DMMMT_DCMSG_ST_INSERROR 	= 3; 		#running error
my $DMMMT_DCMSG_ST_INSINFO  	= 4; 		#running information
#-------------------------------------------------------------------------------

#Cookdb object class
my %COOKDB_OBJECT_CLASS;
$COOKDB_OBJECT_CLASS{"PLMN"}		= 0	;
$COOKDB_OBJECT_CLASS{"ME"}		= 10	;
$COOKDB_OBJECT_CLASS{"OMC"}		= 100	;
$COOKDB_OBJECT_CLASS{"MSC"}		= 101	;
$COOKDB_OBJECT_CLASS{"HLR"}		= 102	;
$COOKDB_OBJECT_CLASS{"AUC"}		= 104	;
$COOKDB_OBJECT_CLASS{"EIR"}		= 106	;
$COOKDB_OBJECT_CLASS{"BSS"}		= 107	;
$COOKDB_OBJECT_CLASS{"BSC"}		= 200	;
$COOKDB_OBJECT_CLASS{"SITE"}		= 201	;
$COOKDB_OBJECT_CLASS{"TG"}		= 301	;
$COOKDB_OBJECT_CLASS{"BTS"}		= 300	;
$COOKDB_OBJECT_CLASS{"TRX"}		= 400	;
$COOKDB_OBJECT_CLASS{"CHANNEL"}		= 402	;
$COOKDB_OBJECT_CLASS{"RADIO"}		= 403	;
$COOKDB_OBJECT_CLASS{"ADJCELLHO"}	= 406	;
$COOKDB_OBJECT_CLASS{"ADJCELLREL"}	= 407	;
$COOKDB_OBJECT_CLASS{"TRUNKGROUP"}	= 600	;
$COOKDB_OBJECT_CLASS{"LINKSET"}		= 602	;

my %SQL_TYPE_INFORMIX;
%SQL_TYPE_INFORMIX = (
	1	=>"CHAR",
	2	=>"INTEGER",
	3	=>"DECIMAL",
	4	=>"INTEGER",
	5	=>"SMALLINT",
	6	=>"FLOAT",
	7	=>"SMALLFLOAT",
	8	=>"FLOAT",
	9	=>"DATE",
	10	=>"DATETIME HOUR TO SECOND",
	11	=>"DATETIME YEAR TO SECOND",
	93	=>"DATETIME YEAR TO SECOND",
	12	=>"VARCHAR",
	-1	=>"VARCHAR",
	-2	=>"SMALLINT",
	-3	=>"SMALLINT",
	-4	=>"INTEGER",
	-5	=>"SMALLINT",
	-6	=>"SMALLINT",
	-7	=>"SMALLINT",
	-8	=>"CHAR",
	-9	=>"VARCHAR",
	-10	=>"VARCHAR"
	);
my %SQL_TYPE_ORACLE = (
	1	=>"CHAR",
	2	=>"NUMERIC",
	3	=>"NUMERIC",
	4	=>"NUMERIC",
	5	=>"SMALLINT",
	6	=>"FLOAT",
	7	=>"FLOAT",
	8	=>"FLOAT",
	9	=>"DATE",
	10	=>"DATE",
	11	=>"DATE",
	12	=>"VARCHAR2",
	-1	=>"LONG",
	-2	=>"SMALLINT",
	-3	=>"SMALLINT",
	-4	=>"LONG RAW",
	-5	=>"SMALLINT",
	-6	=>"SMALLINT",
	-7	=>"SMALLINT",
	-8	=>"CHAR",
	-9	=>"VARCHAR2",
	-10	=>"VARCHAR2"
	);
$SQL_TYPE_INFO->{INFORMIX} = \%SQL_TYPE_INFORMIX;
$SQL_TYPE_INFO->{ORACLE} = \%SQL_TYPE_ORACLE;
my %SQL_STANDARD_TYPE_NUM = (
	"SQL_CHAR"		=>1,
	"SQL_NUMERIC"		=>2,
	"SQL_DECIMAL"		=>3,
	"SQL_INTEGER"		=>4,
	"SQL_SMALLINT"		=>5,
	"SQL_FLOAT"		=>6,
	"SQL_REAL"		=>7,
	"SQL_DOUBLE"		=>8,
	"SQL_DATE"		=>9,
	"SQL_TIME"		=>10,
	"SQL_TIMESTAMP"		=>11,
	"SQL_VARCHAR"		=>12,
	"SQL_LONGVARCHAR"	=>-1,
	"SQL_BINARY"		=>-2,
	"SQL_VARBINARY"		=>-3,
	"SQL_LONGVARBINARY"	=>-4,
	"SQL_BIGINT"		=>-5,
	"SQL_TINYINT"		=>-6,
	"SQL_BIT"		=>-7,
	"SQL_WCHAR"		=>-8,
	"SQL_WVARCHAR"		=>-9,
	"SQL_WLONGVARCHAR"	=>-10,
	"SQL_ALL_TYPES"		=>0
	);
my %SQL_STANDARD_NUM_TYPE = (
	1	=>"SQL_CHAR",
	2	=>"SQL_NUMERIC",
	3	=>"SQL_DECIMAL",
	4	=>"SQL_INTEGER",
	5	=>"SQL_SMALLINT",
	6	=>"SQL_FLOAT",
	7	=>"SQL_REAL",
	8	=>"SQL_DOUBLE",
	9	=>"SQL_DATE",
	10	=>"SQL_TIME",
	11	=>"SQL_TIMESTAMP",
	12	=>"SQL_VARCHAR",
	-1	=>"SQL_LONGVARCHAR",
	-2	=>"SQL_BINARY",
	-3	=>"SQL_VARBINARY",
	-4	=>"SQL_LONGVARBINARY",
	-5	=>"SQL_BIGINT",
	-6	=>"SQL_TINYINT",
	-7	=>"SQL_BIT",
	-8	=>"SQL_WCHAR",
	-9	=>"SQL_WVARCHAR",
	-10	=>"SQL_WLONGVARCHAR",
	0	=>"SQL_ALL_TYPES"
);

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Function: 	Set Global Var Functions
# Description:  Set Global Var Functions
# Input:
# Output: 	
# Return:
#-----------------------------------------------------------------------
# Create Time: 2002-06-18
# Modify Time: 2002-12-05
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sub Set_DataCheck
{
	$PM_DCHK	= shift;
	$OMC_ID		= shift;
	$PM_DCHK_PNAME	= shift;
	$PM_DCHK_STIME	= shift;
	$PM_DCHK_ETIME	= shift;
}

sub Set_PwdEncrypt
{
	$PwdEncrypt = shift;
}

sub Set_WriteOlog
{
	$WriteOlog = shift;
}

sub Set_MsgSend
{
	$MsgSend = shift;
}

sub Set_MSName
{
	$MSName = shift;
}

sub Set_MQName
{
	$MQName = shift;
}

sub Set_BcpMode
{
	$BcpMode = shift;
}

sub Set_BcpWnmsDatabase
{
	$Wnms_Database = shift;
}

sub Set_NoBcp
{
	$Bcp_Oldtab_Logfile = shift;
	$Use_Bcp_Oldtab = 1;
	Trace("*No Bcp with old temp tables!");
}
	
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Function:    Set_Debug
# Description: Set_Debug
# Input:
#     1.1/0
# Output: 	
# Return:
#----------------------------------------------------------------------
# Create Time: 2002-05-29
# Modify Time: 2002-05-29
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sub Set_Debug
{
	my $dflag = shift;
	if ($dflag == 0)
	{
		$DeBug		= 0;
		$ErrorRollBack 	= 1;
		$ErrorCommit 	= 0;
		$ErrorExit 	= 1;
		$ErrorDebug 	= 0;
		$Del_Bcpfile	= 1;
		Trace("*Set no bebug module!\n");
	}
	elsif ($dflag == 1)
	{
		$DeBug		= 0;
		$ErrorRollBack 	= 0;
		$ErrorCommit 	= 0;
		$ErrorExit 	= 0;
		$ErrorDebug 	= 1;
		$Del_Bcpfile	= 0;
		Trace("*Set no bebug module with onerror to debug shell!\n");
	}