#!/bin/bash
# ------------------------------------------------------------------------------------------
# Extract logs from netezza
#
# The script configures IDW services as per the given user inputs
#
# File Information
# 	Name   : nz_query_log.ksh
# 	Created: 2020-04-22 	A
#	Author : Impetus Technologies
#
#	Usage: nz_query_log.ksh </path/query_log_out_fil.log/>
#
# ------------------------------------------------------------------------------------------


# Path of the file where you want to generate file
OUT_FILE_PATH='/export/home/nz/nz_hist_log'


# input Variables
INPUT_START_DT=2020-04-01
INPUT_END_DT=2020-05-31
NZ_USER=admin
NZ_PASSWORD=password
# Provide the comma separated Database name for which log file will be generated. Leave the value of this parameter blank to generate log file for all databases.
LOG_ASSESSMENT_DATABASES=NEWTPCDS,TPDS
HISTDB=HISTDB
HISTUSER=HISTDBOWNER

# Netezza nzsql credentials
export NZ_USER=${NZ_USER}
export NZ_PASSWORD=${NZ_PASSWORD}
export NZ_DATABASE=${HISTDB}

startdt=${INPUT_START_DT}
enddt=${INPUT_END_DT}
hist_db=${HISTDB}
hist_user=${HISTUSER}


# Prepare assessment Databases
assessment_databases_tmp=$(IFS=,; set -- ${LOG_ASSESSMENT_DATABASES}; printf "'%s'," "$@")
assessment_databases=`echo $assessment_databases_tmp | tr '[a-z]' '[A-Z]'`

# WHERE Condition: Bank includes all database.
where_condition_log_database=""

# Check if Database for which log to be generated is provided. Else log will be generated for all the databases.
if [ ! -z ${LOG_ASSESSMENT_DATABASES} ]; then
	where_condition_log_database="AND b.DBNAME IN (${assessment_databases}'IDWDUMMYDB')"
fi
#display input parameter
echo "INPUT start date = "$startdt""
echo "INPUT end date = "$enddt""
echo "INPUT USER = "$hist_user""
echo "INPUT history DB = "$hist_db""

#validate start and end date
if [ -z "$startdt" ] || [ -z "$enddt" ] || [ -z "$hist_user" ] || [ -z "$hist_db" ] ; then
echo "ERROR: One or more INPUT variables are Invalid.";
                exit 2;
fi

# Output File Name
nz_out_file="${OUT_FILE_PATH}/nz_out.log.${startdt}_${enddt}"

#daily frequency logic. For daily, provide same start and end date

#validate start end date
sd=$(date -I -d "$startdt") || echo "Input start dt incorrect" exit 1
ed=$(date -I -d "$enddt")   || echo "Input end dt incorrect"  exit 1


echo "Extraction FOR Start DATE "$startdt" and end DATE "$enddt""

nzsql -e -q -X -A -t -F $"~~" -o ${nz_out_file} <<eof
SELECT  		b.npsid,
				b.npsinstanceid,
				b.opid,
				b.username AS UserName, 
                case 
					when d.clienttype = 0 then 'None'
					when d.clienttype = 1 then 'LibPq client (nzsql)'
					when d.clienttype = 2 then 'ODBC client'
					when d.clienttype = 3 then 'JDBC client'
					when d.clienttype = 4 then 'nzload / nzunload'
					when d.clienttype = 5 then 'Client of the client manager'
					when d.clienttype = 6 then 'nzbackup / nzrestore'
					when d.clienttype = 7 then 'nzreclaim'
					when d.clienttype = 8 then 'Unused'
					when d.clienttype = 9 then 'Internal Netezza tool'
					when d.clienttype = 10 then 'OLE DB client'
				END  AS AppID, 
                d.CLIENTPID AS ClientID, 
                b.submittime AS StartTime, 
				tbl1.ESTIMATEDCOST as AMPCPUTime,
				tbl1.ESTIMATEDDISK as TotalIOCount,
				'0.0' as ParserCPUTime,
				a.finishtime AS firstresptime, 
				b.submittime as firststeptime,
                d.pid AS ProcID, 
                B.CHECKSUM AS QueryID, 
				tbl2.MaxAMPCPUTime as MaxAMPCPUTime,
				tbl2.MaxAMPIO as MaxAMPIO,
	 			tbl2.totalCPU as totalCPU,
       			tbl2.totalIO as totalIO,
                ( ( Cast(( Cast(a.finishtime AS DATE) - Cast(b.submittime AS DATE) ) AS DECIMAL(18, 6) ) * 60 * 60 * 24 ) + 
				( ( Extract(hour FROM a.finishtime) - Extract( hour FROM b.submittime) ) * 60 * 60 ) + 
                ( ( Extract(minute FROM a.finishtime) - Extract( minute FROM b.submittime) ) * 60 ) + 
				( Extract(second FROM a.finishtime) - Extract( second FROM b.submittime) ) ) AS Query_Execution_Time, 
                b.dbname AS schemaName, 
				'-1' AS SQLROWNO,
				case when (ab.usage & 1) <> 0 then  (case when upper(QueryText) like 'MERGE%' then 'MERGE' else 'SELECT' end)
                                        when (ab.usage & 2) <> 0 then 'INSERT'
                                        when (ab.usage & 4) <> 0 then 'DELETE'
                                        when (ab.usage & 8) <> 0 then 'UPDATE'
                                        when (ab.usage & 16) <> 0 then 'TRUNCATE'
                                        when (ab.usage & 32) <> 0 then 'DROP'
                                        when (ab.usage & 64) <> 0 then 'CREATE'
                                        when (ab.usage & 128) <> 0 then 'statsgenerated'
                                        when (ab.usage & 256) <> 0 then 'locked'
                                        when (ab.usage & 512) <> 0 then 'alter'
                                        else 'OTHER'
                                END AS StatementType,
				b.querytext AS QueryText,
				'IDWWM' AS filler
FROM   ($hist_db.$hist_user."\$hist_query_epilog_3" a 
        JOIN $hist_db.$hist_user."\$hist_query_prolog_3" b using (npsid, npsinstanceid, opid)) 
		join $hist_db.$hist_user."\$hist_session_prolog_3" d
		on (a.npsid = d.npsid
		and a.npsinstanceid = d.npsinstanceid
		and a.SESSIONID = d.SESSIONID)		
		
		left join (select * 
				from (
				SELECT *
					   , row_number() over(partition by NPSID,NPSINSTANCEID,OPID order by NPSID,NPSINSTANCEID,OPID ) rn
				  FROM $hist_db.$hist_user."\$hist_table_access_3" ) a where rn = 1) ab
		on (a.npsid = ab.npsid 
		and a.npsinstanceid = ab.npsinstanceid 
		and a.opid = ab.opid)
				
		left join $hist_db.$hist_user."\$hist_plan_prolog_3" tbl1
		on (a.npsid = tbl1.npsid
		and a.npsinstanceid = tbl1.npsinstanceid
		and a.opid = tbl1.opid
		)	
		left join (select npsid, npsinstanceid, SESSIONID,opid,
				max(ESTIMATEDCOST) over(order by null) as MaxAMPCPUTime,
				max(ESTIMATEDDISK) over(order by null) as MaxAMPIO,
				sum(ESTIMATEDCOST) over(order by null) as totalCPU,
				sum(ESTIMATEDDISK) over(order by null) as totalIO
				from $hist_db.$hist_user."\$hist_plan_prolog_3"
				where cast(SUBMITTIME as date) between cast('$startdt' as date) and cast('$enddt' as date)
				) tbl2
		on (a.npsid = tbl2.npsid
		and a.npsinstanceid = tbl2.npsinstanceid
		and a.opid = tbl2.opid
		)		
			
WHERE 
  a.status = 0 
  AND Cast(b.submittime AS DATE) between cast('$startdt' as date) and cast('$enddt' as date)   
  AND b.DBNAME not in ('SYSTEM', '${hist_db}')
  ${where_condition_log_database}

UNION ALL

SELECT  		c.npsid,
				c.npsinstanceid,
				c.opid,
				b.username AS UserName, 
                case 
					when d.clienttype = 0 then 'None'
					when d.clienttype = 1 then 'LibPq client (nzsql)'
					when d.clienttype = 2 then 'ODBC client'
					when d.clienttype = 3 then 'JDBC client'
					when d.clienttype = 4 then 'nzload / nzunload'
					when d.clienttype = 5 then 'Client of the client manager'
					when d.clienttype = 6 then 'nzbackup / nzrestore'
					when d.clienttype = 7 then 'nzreclaim'
					when d.clienttype = 8 then 'Unused'
					when d.clienttype = 9 then 'Internal Netezza tool'
					when d.clienttype = 10 then 'OLE DB client'
				END  AS AppID, 
                d.CLIENTPID AS ClientID, 
                b.submittime AS StartTime, 
				NULL as AMPCPUTime,
				NULL as TotalIOCount,
				'0.0' as ParserCPUTime,
				a.finishtime AS firstresptime, 
				b.submittime as firststeptime,
                d.pid AS ProcID, 
                B.CHECKSUM AS QueryID, 
				NULL as MaxAMPCPUTime,
				NULL as MaxAMPIO,
	 			NULL as totalCPU,
       			NULL as totalIO,
                ( ( Cast(( Cast(a.finishtime AS DATE) - Cast(b.submittime AS DATE) ) AS DECIMAL(18, 6) ) * 60 * 60 * 24 ) + 
				( ( Extract(hour FROM a.finishtime) - Extract( hour FROM b.submittime) ) * 60 * 60 ) + 
                ( ( Extract(minute FROM a.finishtime) - Extract( minute FROM b.submittime) ) * 60 ) + 
				( Extract(second FROM a.finishtime) - Extract( second FROM b.submittime) ) ) AS Query_Execution_Time, 
                b.dbname AS schemaName, 
				C.SEQUENCEID AS SQLROWNO,
				NULL AS StatementType,
				c.querytext AS QueryText,
				'IDWWM' AS filler
FROM   ($hist_db.$hist_user."\$hist_query_epilog_3" a 
        JOIN $hist_db.$hist_user."\$hist_query_prolog_3" b using (npsid, npsinstanceid, opid) 
       ) 
       JOIN $hist_db.$hist_user."\$hist_query_overflow_3" AS c 
         ON( b.npsid = c.npsid 
             AND b.npsinstanceid = c.npsinstanceid 
             AND b.opid = c.opid ) 
		join $hist_db.$hist_user."\$hist_session_prolog_3" d
		on (a.npsid = d.npsid
		and a.npsinstanceid = d.npsinstanceid
		and a.SESSIONID = d.SESSIONID
		)
WHERE 
  a.status = 0 
  AND Cast(b.submittime AS DATE) between cast('$startdt' as date) and cast('$enddt' as date)
  AND b.DBNAME not in ('SYSTEM', '${hist_db}')
  ${where_condition_log_database}
;
eof

echo "Extraction ending FOR Start DATE "$startdt" and end DATE "$enddt""