Last week we have shown you the code for generating the Header and populate the same in OdiSqlUnload file , but the codes seems to be limited to the Database and then separate query needs to be created for each database reading from the System table,which doesn’t makes things so easy.

In order to solve it and to make the codes independent, we have revisited and rewritten the whole codes to generate header.

All you need to is to write the query you have used in the OdiSqlUnload .

import string
import java.sql as sql
import java.lang as lang
import re
sourceConnection = odiRef.getJDBCConnection("SRC")
output_write=open('c:/output.txt','r+')
myStmt = sourceConnection.createStatement()
my_query = "select * FROM ODI_WORK.SNP_TABLE"
my_query=my_query.upper()
if string.find(my_query, '*') > 0:
    myRs = myStmt.executeQuery(my_query)
    md=myRs.getMetaData()
    collect=[]
    i=1
    while (i <= md.getColumnCount()):
        collect.append(md.getColumnName(i))
        i += 1    

    header=','.join(map(string.strip, collect))
elif string.find(my_query,'||') > 0:
    header = my_query[7:string.find(my_query, 'FROM')].replace("||','||",',')
else:
    header = my_query[7:string.find(my_query, 'FROM')]

print header
old=output_write.read()
output_write.seek(0)
output_write.write (header+'\n'+old)

sourceConnection.close()
output_write.close()

Note :
The codes presently support only this type of queries format
select * from table_name (or)
select column1,column2 from table_name (or)
select column1||’,'||column2 from table_name

After running the above script for all columns

( or ) for fewer columns

Usefull?

Look for odiexperts for more tips and tricks.

Thanks for visiting us!

Hi every one…

In the last few posts we have shown how to display the SQL results in Operator , fetch the failed objects errors message in Package and various other techniques. Today we want to cover another small scripts which can enable you fetch details of the Interface that have error  out because of PK , FK ,Not Null constraints etc and goes to the Error table.

This scripts creates a File and dumps all the information by reading from SNP_CHECK_TAB  table and finally file can be added to OdiSendMail and send to the Administrator or Developers so that they can know which interface got error  records, so they can do the needful and also in daily load we fail to see all these smaller details especially we have hundreds of interfaces.

import string
import java.sql as sql
import java.lang as lang
import re
sourceConnection = odiRef.getJDBCConnection("SRC")
output_write=open('c:/snp_check_tab.txt','w')
sqlstring = sourceConnection.createStatement()
#---------------------------------------------------------------------------
output_write.write("The Errored Interface of today's (<%=odiRef.getSysDate( )%>)
run are .... \n")
output_write.write("----------------------------------------------------------- \n\n")
#---------------------------------------------------------------------------
sqlstmt="SELECT 'Errored Interface \t- '||SUBSTR(ORIGIN,INSTR(ORIGIN,')')+1,
LENGTH(ORIGIN))||'\nError Message \t\t- '||ERR_MESS||'\nNo of Errored Records \t- '||
ERR_COUNT AS OUTPUT FROM  ODI_TEMP.SNP_CHECK_TAB WHERE TRUNC(CHECK_DATE)=TRUNC(SYSDATE)"
result=sqlstring.executeQuery(sqlstmt)
rs=[]
while (result.next()):
      rs.append(str(result.getString("output")+'\t'))

res='\n\n'.join(map(string.strip,rs))
print >> output_write, res

sourceConnection.close()
output_write.close()

[Note – In the above scripts please change the File path and the Schema(Work Schema ) name according to your respective Environment ]

Provide the Technology and Schema of your work Schema or required schema which can access the SNP_CHECK_TAB and provide the code in Command on Target

and for every run you will get the sample output as shown below.

Attach the File to OdiSendMail and get the daily Error Interface , Message and Records detail .

Download the Codes

Comments are alwasys welcome!

See you soon…

Hi everyone , few post back we have shown how to get the count in Operator but there are many time you may want to get the count of records by email once the data got loaded. To solve this we have written a simple jython procedure to get and count and attach the file as Email and set up the process

Here is a simple Jython Procedure to  do that  .

import string
import java.sql as sql
import java.lang as lang
import re
sourceConnection = odiRef.getJDBCConnection("SRC")
output_write=open('c:/count_write.txt','w')
sqlstring = sourceConnection.createStatement()
print >> output_write, " The counts are .... '\n'"
#---------------------------------------------------------------
# QUERY 1 COUNT
#---------------------------------------------------------------

sqlstmt="select count(*) as rowcount from all_tables where owner='ODI_TEMP'"
result=sqlstring.executeQuery(sqlstmt)
while result.next():
count=result.getInt("rowcount")
rs='\n'+str(sqlstmt)+'\n\n'+str(count)

print >> output_write ,rs

#---------------------------------------------------------------
# QUERY 2 COUNT
#---------------------------------------------------------------

sqlstmt="select count(*) as rowcount from all_tables where owner='ODI_WORK'"
result=sqlstring.executeQuery(sqlstmt)
while result.next():
count=result.getInt("rowcount")
rs='\n'+str(sqlstmt)+'\n\n'+str(count)

print >> output_write ,rs

#---------------------------------------------------------------
# QUERY 3 COUNT
#---------------------------------------------------------------

sqlstmt="select count(*) as rowcount from all_tables where owner='SYSTEM'"
result=sqlstring.executeQuery(sqlstmt)
while result.next():
count=result.getInt("rowcount")
rs='\n'+str(sqlstmt)+'\n\n'+str(count)

print >> output_write ,rs

sourceConnection.close()
output_write.close()

and you get the output in similar form.

Just replace the  sql count query according to your requirement.

and provide the  schema in the ‘Command on Source ‘ which have the select permission on the required table for which the counts are being retrieved.

Download the Codes here

This below query provide details about each scenario and its associated session  run details in the operator . This enables to get details about each run whether it was successful , record processed , inserted and updated and duration in hours and other statistical information about each scenario.

SELECT
SS.SCEN_NAME AS SCENARIO_NAME ,
SS.SCEN_VERSION AS SCEN_VERSION ,
SSR.SCEN_RUN_NO AS SESS_NO,
SSR.SESS_DUR AS DUR,
CASE
WHEN SSR.SESS_DUR <61 THEN SSR.SESS_DUR||' SEC'
WHEN SSR.SESS_DUR BETWEEN 61 AND 3600 THEN (SSR.SESS_DUR/60)||' MIN'
WHEN SSR.SESS_DUR > 3600 THEN (TRUNC(SSR.SESS_DUR/3600)||' HR '||TRUNC(MOD(SSR.SESS_DUR,3600)/60)||' MIN')
END AS DURATION,
-- The above CASE statement is to show the duraion in hrs , min and seconds
-- rather than manual calculating when the session run for multiple hours
DECODE(SSR.SESS_STATUS,'D','SUCCESSFUL'
,'E','FAILED')AS STATUS,
SSR.NB_ROW AS ROW_PROCESSED,
SSR.NB_INS AS ROWS_INSERTED,
SSR.NB_UPD AS ROWS_UPDATED,
SSR.NB_DEL AS ROWS_DELETED,
SSR.NB_ERR AS ERROR_RECORD,
CASE
WHEN TO_CHAR(SSR.SESS_END,'MM-DD-YYYY')=TO_CHAR(SSR.SESS_BEG,'MM-DD-YYYY')
THEN TO_CHAR(SSR.SESS_END,'MM-DD-YYYY')
ELSE TO_CHAR(SSR.SESS_BEG,'MM-DD-YYYY')||'-TO-'||TO_CHAR(SSR.SESS_END,'MM-DD-YYYY')
END AS SESSION_RAN_DATE
FROM SNP_SCEN SS
LEFT OUTER JOIN SNP_SCEN_REPORT SSR ON SS.SCEN_NO=SSR.SCEN_NO
WHERE SSR.SCEN_RUN_NO IS NOT NULL
ORDER BY SCENARIO_NAME,SSR.SESS_END

This query provides all the detail of the Variables created under various project and their associated  default properties.

SELECT DISTINCT
CASE
WHEN SNP_VAR.I_PROJECT IS NULL
THEN 'GLOBAL_VARIABLE'
ELSE SNP_PROJECT.PROJECT_NAME
END                 AS PROJECT_NAME,
SNP_VAR.VAR_NAME    AS VARIABLE_NAME,
CASE
WHEN SNP_VAR.VAR_DATATYPE='A' THEN 'ALPHANUMERIC'
WHEN SNP_VAR.VAR_DATATYPE='N' THEN 'NUMERIC'
WHEN SNP_VAR.VAR_DATATYPE='D' THEN 'DATE'
ELSE 'TEXT' END     AS DATATYPE,
CASE WHEN SNP_VAR.VAR_DATATYPE='T' THEN V_TXT.TXT
ELSE SNP_VAR.DEF_v  END      AS DEFAULT_VALUE,
SNP_VAR.lschema_name AS VARIABLE_QUERY_SCHEMA,
SNP_TXT.TXT         AS VARIABLE_QUERY,
S_TXT.TXT           AS DESCRIPTION
FROM SNP_VAR
LEFT OUTER JOIN SNP_PROJECT ON SNP_VAR.I_PROJECT=SNP_PROJECT.I_PROJECT
LEFT OUTER JOIN SNP_TXT ON SNP_TXT.I_TXT=SNP_VAR.I_TXT_VAR_IN
LEFT OUTER JOIN SNP_TXT S_TXT ON S_TXT.I_TXT=SNP_VAR.I_TXT_VAR
LEFT OUTER JOIN SNP_TXT V_TXT ON V_TXT.I_TXT=SNP_VAR.i_txt_var_val_txt
ORDER BY project_name