If you read my blog post on How to find out RTP values for a user-initiated calculation, you might be thinking, “Celvin, that is for Cloud, what about On-Prem.” If you were one among those, then this post is for you.
On-Prem gives you the luxury of looking at the backend tables and figure out what exactly happens behind the scene. If you have got that knowledge, you can apply that same for the cloud. However, the cloud keeps you guessing (table structure can be changed, and you will never know).
If you look at Planning tables, you’ll find that the job status information gets stored in two tables.
- HSP_JOB_STATUS
- HSP_HISTORICAL_JOB_STATUS
A simple query shows us the results, as shown below.
The history table contains the history data from the JOB_STATUS table.
The USER_ID that you see there is coming from HSP_USERS table, to get the user name, you need to join HSP_USERS with CSS_USERS table under Shared Services schema.
That’s it. You got all the information you need to generate a report to find out the RTPs and their values for a user-initiated calculation.
How it works
Joins, UNION ALL and a recursive SQL will do it 🙂
The SQL query to get the current day’s job status.
SELECT A.JOB_NAME,A.START_TIME,A.END_TIME, A.DETAILS,A.ATTRIBUTE_1,A.ATTRIBUTE_2
, B.FIRSTNAME,B.LASTNAME, B.NAME
FROM HSP_JOB_STATUS A
, HSSADMIN.CSS_USERS B
, HSP_USERS C
WHERE A.USER_ID=C.USER_ID
AND C.SID=B.IDENTITY_ID
Now we need to find out the parent job id and its name. As you can see this report doesn’t make much sense.
To get the parent job id, we need to write a recursive query, and here is how it looks like.
SELECT P.JOB_ID, P.PARENT_JOB_ID
,B.NAME,B.FIRSTNAME,B.LASTNAME
,P.JOB_NAME,P.PARENT_NAME,P.START_TIME,P.END_TIME,P.DETAILS
,P.ATTRIBUTE_1,P.ATTRIBUTE_2 FROM
(
SELECT T1.JOB_ID, T1.PARENT_JOB_ID,T1.USER_ID,T1.JOB_NAME,T2.JOB_NAME AS PARENT_NAME,T1.START_TIME,T1.END_TIME,T1.DETAILS,T1.ATTRIBUTE_1,T1.ATTRIBUTE_2
FROM HSP_JOB_STATUS T1
LEFT JOIN HSP_JOB_STATUS T2 ON T1.PARENT_JOB_ID = T2.JOB_ID
START WITH T1.PARENT_JOB_ID IS NULL
CONNECT BY PRIOR T1.JOB_ID = T1.PARENT_JOB_ID
)P
, HSP_USERS A, HSSADMIN.CSS_USERS B
WHERE P.USER_ID=A.USER_ID
AND A.SID=B.IDENTITY_ID
ORDER BY 1
Groovy code in action
Here is the groovy script that helps to create a report like the other post that we looked at.
If you specify a day to return values, then I’m using a UNION ALL to fetch information from the history table too.
There is no restriction in nDays; you can pass any number you wish.
I did run it to generate a report from the past 15 days.
import groovy.sql.Sql
// Read configuration script for the environment info.
def keyConfig = new ConfigSlurper().parse(new File('Y:/groovy_scripts/Env_Info.config').toURL())
planSchema=keyConfig.serverEnvs.onPrem.planSchema
hssSchema=keyConfig.serverEnvs.onPrem.hssSchema
planSchemaPass=new String(keyConfig.serverEnvs.onPrem.planSchemaPass.decodeBase64())
jdbcURL=keyConfig.serverEnvs.onPrem.jdbcURL
def exportJobConsoleReport(reportFile, delimiter, nDays){
Sql sql=Sql.newInstance(jdbcURL, planSchema, planSchemaPass)
println "**Create Job Console Report**"
writeReportFile = new File(reportFile)
if (nDays){
def consReportRows = sql.rows("""
SELECT P.JOB_ID, P.PARENT_JOB_ID
,B.NAME,B.FIRSTNAME,B.LASTNAME
,P.JOB_NAME,P.PARENT_NAME,P.START_TIME,P.END_TIME,P.DETAILS
,P.ATTRIBUTE_1,P.ATTRIBUTE_2 FROM
(
SELECT T1.JOB_ID, T1.PARENT_JOB_ID,T1.USER_ID,T1.JOB_NAME,T2.JOB_NAME AS PARENT_NAME,T1.START_TIME,T1.END_TIME,T1.DETAILS,T1.ATTRIBUTE_1,T1.ATTRIBUTE_2
FROM HSP_JOB_STATUS T1
LEFT JOIN HSP_JOB_STATUS T2 ON T1.PARENT_JOB_ID = T2.JOB_ID
START WITH T1.PARENT_JOB_ID IS NULL
AND T1.START_TIME <= SYSDATE AND T1.START_TIME >= SYSDATE - $nDays
CONNECT BY PRIOR T1.JOB_ID = T1.PARENT_JOB_ID
UNION ALL
SELECT T1.JOB_ID, T1.PARENT_JOB_ID,T1.USER_ID,T1.JOB_NAME,T2.JOB_NAME AS PARENT_NAME,T1.START_TIME,T1.END_TIME,T1.DETAILS,T1.ATTRIBUTE_1,T1.ATTRIBUTE_2
FROM HSP_HISTORICAL_JOB_STATUS T1
LEFT JOIN HSP_HISTORICAL_JOB_STATUS T2 ON T1.PARENT_JOB_ID = T2.JOB_ID
START WITH T1.PARENT_JOB_ID IS NULL
AND T1.START_TIME <= SYSDATE AND T1.START_TIME >= SYSDATE - $nDays
CONNECT BY PRIOR T1.JOB_ID = T1.PARENT_JOB_ID
)P
, HSP_USERS A, HSSADMIN.CSS_USERS B
WHERE P.USER_ID=A.USER_ID
AND A.SID=B.IDENTITY_ID
order by 1
""" as String)
if (consReportRows){
// Print column information into the file
writeReportFile.write(consReportRows[0].keySet().join(delimiter)) // keySet gives us the column names
consReportRows.each{result->
cols = result.size()
// Check whether the column type is CLOB, if it is then get the char stream's text
// Groovy ternary operator in action
vals = (0..<cols).collect{(result[it].getClass().name == "oracle.sql.CLOB" ? result[it].getCharacterStream().text : "${result[it]}")}
writeReportFile.append(System.getProperty("line.separator") + vals.join(delimiter))
}
}
} else {
def consReportRows = sql.rows("""
SELECT P.JOB_ID, P.PARENT_JOB_ID
,B.NAME,B.FIRSTNAME,B.LASTNAME
,P.JOB_NAME,P.PARENT_NAME,P.START_TIME,P.END_TIME,P.DETAILS
,P.ATTRIBUTE_1,P.ATTRIBUTE_2 FROM
(
SELECT T1.JOB_ID, T1.PARENT_JOB_ID,T1.USER_ID,T1.JOB_NAME,T2.JOB_NAME AS PARENT_NAME,T1.START_TIME,T1.END_TIME,T1.DETAILS,T1.ATTRIBUTE_1,T1.ATTRIBUTE_2
FROM HSP_JOB_STATUS T1
LEFT JOIN HSP_JOB_STATUS T2 ON T1.PARENT_JOB_ID = T2.JOB_ID
START WITH T1.PARENT_JOB_ID IS NULL
CONNECT BY PRIOR T1.JOB_ID = T1.PARENT_JOB_ID
)P
, HSP_USERS A, HSSADMIN.CSS_USERS B
WHERE P.USER_ID=A.USER_ID
AND A.SID=B.IDENTITY_ID
order by 1
""" as String)
if (consReportRows){
// Print column information into the file
writeReportFile.write(consReportRows[0].keySet().join(delimiter)) // keySet gives us the column names
consReportRows.each{result->
cols = result.size()
vals = (0..<cols).collect{(result[it].getClass().name == "oracle.sql.CLOB" ? result[it].getCharacterStream().text : "${result[it]}")}
writeReportFile.append(System.getProperty("line.separator") + vals.join(delimiter))
}
}
}
println "**Job completed"
}
println "******"
exportJobConsoleReport("Y:\\groovy_scripts\\ConsoleReportOnPrem.txt", "|" ,"15")
println "******"
I’m using a different approach (sql.eachRows vs. sql.rows) in this script. Line 16
Using sql.rows I’m collecting all the rows and then to get the columns, I’m using the first row; grab the keyset and join them, this happens at line 45.
Line 25 says that if a job that gets initiated won’t have a parent, and that is our starting parent.
Line 26 is where I’m applying the filter for nDays to get past day’s report.
Since the Oracle database stores the DETAILS column as CLOB, we need to check for that and then print the text information.
Line 51 is where this is happening. First, I’m checking whether the column is CLOB; if it is, I’m getting the character stream of the value and then getting the text. All that happens as a one-liner code.